[Home] [Help]
43: from per_pay_proposals
44: where assignment_id = p_assignment_id;
45: --
46: begin
47: hr_utility.set_location('Entering:'|| l_proc, 1);
48: --
49: -- Check mandatory parameters have been set
50: --
51: hr_api.mandatory_arg_error
60: (p_assignment_id => p_assignment_id
61: ,p_effective_date => p_effective_date
62: ,p_object_version_number => p_object_version_number
63: );
64: hr_utility.set_location(l_proc, 2);
65: --
66: -- Check if the assignment is being inserted or updated.
67: --
68: if ((l_api_updating and
68: if ((l_api_updating and
69: nvl(per_asg_shd.g_old_rec.pay_basis_id, hr_api.g_number)
70: <> nvl(p_pay_basis_id, hr_api.g_number)) or
71: (NOT l_api_updating)) then
72: hr_utility.set_location(l_proc, 3);
73: --
74: -- Check if the pay basis is set
75: --
76: if p_pay_basis_id is not null then
82: --
83: --
84: if p_assignment_type not in ('E','A','B','O') then
85: --
86: hr_utility.set_message(801, 'HR_51176_ASG_INV_ASG_TYP_PBS');
87: hr_multi_message.add
88: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
89: );
90: --
88: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
89: );
90: --
91: end if;
92: hr_utility.set_location(l_proc, 4);
93: --
94: -- Check that the pay basis exists in PER_PAY_BASES.
95: --
96: open csr_chk_pay_basis;
96: open csr_chk_pay_basis;
97: fetch csr_chk_pay_basis into l_business_group_id;
98: if csr_chk_pay_basis%notfound then
99: close csr_chk_pay_basis;
100: hr_utility.set_message(801, 'HR_51168_ASG_INV_PAY_BASIS_ID');
101: hr_multi_message.add
102: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
103: );
104: else
103: );
104: else
105: close csr_chk_pay_basis;
106: end if;
107: hr_utility.set_location(l_proc, 5);
108: --
109: -- Check that the pay basis is in the same business group as the pay
110: -- basis of the assignment.
111: --
110: -- basis of the assignment.
111: --
112: If p_business_group_id <> l_business_group_id then
113: --
114: hr_utility.set_message(801, 'HR_51169_ASG_INV_PAY_BAS_BG');
115: hr_multi_message.add
116: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
117: );
118: --
116: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
117: );
118: --
119: end if;
120: hr_utility.set_location(l_proc, 6);
121: --
122: -- Check if pay basis is being updated
123: --
124: if l_api_updating then
127: --
128: open csr_get_max_pp_chg_date;
129: fetch csr_get_max_pp_chg_date into l_max_pp_chg_date;
130: close csr_get_max_pp_chg_date;
131: hr_utility.set_location(l_proc, 7);
132: --
133: -- Check if any pay proposal change dates exist for the assignment
134: -- and error if a pay proposal change date exists after the validation
135: -- start date for the assignment.
135: -- start date for the assignment.
136: --
137: if l_max_pp_chg_date > p_validation_start_date then
138: --
139: hr_utility.set_message(801, 'HR_51171_ASG_INV_PB_PP_CD');
140: hr_multi_message.add
141: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
142: );
143: --
141: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
142: );
143: --
144: end if;
145: hr_utility.set_location(l_proc, 8);
146: end if;
147: --
148: end if;
149: --
148: end if;
149: --
150: end if;
151: --
152: hr_utility.set_location(' Leaving:'|| l_proc, 9);
153: end chk_pay_basis_id;
154: --
155: -- ---------------------------------------------------------------------------
156: -- |------------------------< chk_payroll_id >-------------------------------|
193: l_date_of_birth per_all_people_f.date_of_birth%type;
194: l_payroll_id_updated boolean;
195: --
196: begin
197: hr_utility.set_location('Entering:'|| l_proc, 10);
198: --
199: open csr_pradd_exists;
200: fetch csr_pradd_exists into l_address_line1;
201: close csr_pradd_exists;
199: open csr_pradd_exists;
200: fetch csr_pradd_exists into l_address_line1;
201: close csr_pradd_exists;
202: --
203: hr_utility.set_location(l_proc, 20);
204: --
205: open csr_get_person_dob;
206: fetch csr_get_person_dob into l_date_of_birth;
207: close csr_get_person_dob;
205: open csr_get_person_dob;
206: fetch csr_get_person_dob into l_date_of_birth;
207: close csr_get_person_dob;
208: --
209: hr_utility.set_location(l_proc, 30);
210: --
211: per_asg_bus2.chk_payroll_id_int
212: (p_assignment_id => p_assignment_id
213: ,p_business_group_id => p_business_group_id
225: );
226: --
227: p_payroll_id_updated:=l_payroll_id_updated;
228: --
229: hr_utility.set_location(' Leaving:'|| l_proc, 300);
230: end chk_payroll_id;
231: --
232: --
233: -- ---------------------------------------------------------------------------
362: --
363: -- End of Bug fix: 2185300.
364: --
365: begin
366: hr_utility.set_location('Entering:'|| l_proc, 10);
367: --
368: -- Initialize payroll updated flag
369: --
370: p_payroll_id_updated := FALSE;
393: (p_api_name => l_proc
394: ,p_argument => 'validation_end_date'
395: ,p_argument_value => p_validation_end_date
396: );
397: hr_utility.set_location(l_proc, 20);
398: --
399: l_api_updating := per_asg_shd.api_updating
400: (p_assignment_id => p_assignment_id
401: ,p_effective_date => p_effective_date
400: (p_assignment_id => p_assignment_id
401: ,p_effective_date => p_effective_date
402: ,p_object_version_number => p_object_version_number
403: );
404: hr_utility.set_location(l_proc, 30);
405: --
406: if (l_api_updating and
407: ((nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
408: <> nvl(p_payroll_id, hr_api.g_number)) or
411: )
412: )
413: or NOT l_api_updating
414: then
415: hr_utility.set_location(l_proc, 40);
416: --
417: if (l_api_updating and
418: nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
419: <> nvl(p_payroll_id, hr_api.g_number)) then
421: -- As payroll id has been updated, set p_payroll_id_updated to true.
422: -- This functionality is required for the /update/delete_assignment
423: -- business processes
424: --
425: hr_utility.set_location(l_proc, 45);
426: p_payroll_id_updated := TRUE;
427: end if;
428: --
429: if p_payroll_id is not null then
435: --
436: --
437: if p_assignment_type not in ('E','A','B','O') then
438: --
439: hr_utility.set_message(801, 'HR_51226_ASG_INV_ASG_TYP_PAY');
440: hr_multi_message.add
441: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
442: );
443: --
441: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
442: );
443: --
444: end if;
445: hr_utility.set_location(l_proc, 50);
446: --
447: -- Check if GEOCODES is installed
448: --
449:
454:
455: if hr_general.chk_geocodes_installed = 'Y'
456: and p_assignment_type = 'E'
457: and ( ( l_legislation_code = 'CA'
458: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
459: p_legislation => 'CA'))
460: OR ( l_legislation_code = 'US'
461: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
462: p_legislation => 'US')))
457: and ( ( l_legislation_code = 'CA'
458: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
459: p_legislation => 'CA'))
460: OR ( l_legislation_code = 'US'
461: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
462: p_legislation => 'US')))
463: then
464: --
465: -- Check if a primary address exists for the person
465: -- Check if a primary address exists for the person
466: -- of the employee assignment
467: --
468: if p_address_line1 is null then
469: hr_utility.set_message(800, 'PER_52990_ASG_PRADD_NE_PAY');
470: hr_multi_message.add
471: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
472: );
473: --
471: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
472: );
473: --
474: end if;
475: hr_utility.set_location(l_proc, 55);
476: --
477: end if;
478: --
479: -- Check that payroll exists and the effective start date of the
484: open csr_payroll_exists;
485: fetch csr_payroll_exists into l_exists;
486: if csr_payroll_exists%notfound then
487: close csr_payroll_exists;
488: hr_utility.set_message(801, 'HR_7370_ASG_INVALID_PAYROLL');
489: hr_multi_message.add
490: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
491: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
492: );
492: );
493: else
494: close csr_payroll_exists;
495: end if;
496: hr_utility.set_location(l_proc, 60);
497: --
498: -- Check that business group of payroll is the
499: -- same as that of the assignment
500: --
501: open csr_get_bus_grp;
502: fetch csr_get_bus_grp into l_business_group_id;
503: if l_business_group_id <> p_business_group_id then
504: close csr_get_bus_grp;
505: hr_utility.set_message(801, 'HR_7373_ASG_INVALID_BG_PAYROLL');
506: hr_multi_message.add
507: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
508: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
509: );
509: );
510: else
511: close csr_get_bus_grp;
512: end if;
513: hr_utility.set_location(l_proc, 70);
514: --
515: -- Check that person to whom the assignment is linked
516: -- has their D.O.B. recorded on per_people_f
517: --
515: -- Check that person to whom the assignment is linked
516: -- has their D.O.B. recorded on per_people_f
517: --
518: if p_assignment_type = 'E' then
519: hr_utility.set_location(l_proc, 75);
520: if p_date_of_birth is null then
521: hr_utility.set_message(801, 'HR_7378_ASG_NO_DATE_OF_BIRTH');
522: hr_multi_message.add
523: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
517: --
518: if p_assignment_type = 'E' then
519: hr_utility.set_location(l_proc, 75);
520: if p_date_of_birth is null then
521: hr_utility.set_message(801, 'HR_7378_ASG_NO_DATE_OF_BIRTH');
522: hr_multi_message.add
523: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
524: );
525: end if;
522: hr_multi_message.add
523: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
524: );
525: end if;
526: hr_utility.set_location(l_proc, 80);
527: end if;
528: end if;
529: end if;
530: --
533: open csr_any_future_changes;
534: fetch csr_any_future_changes into l_exists;
535: l_future_change := csr_any_future_changes%FOUND;
536: close csr_any_future_changes;
537: hr_utility.set_location(l_proc, 90);
538: --
539: if (l_api_updating
540: and (per_asg_shd.g_old_rec.payroll_id is not null
541: and p_payroll_id is not null
545: or p_datetrack_mode = 'FUTURE_CHANGE'
546: or p_datetrack_mode = 'UPDATE_OVERRIDE'))
547: then
548: --
549: hr_utility.set_location(l_proc, 100);
550: --
551: l_invalid_ppm := FALSE;
552: l_working_start_date := p_validation_start_date;
553: --
560: ,l_org_payment_method_id
561: ,l_min_ppm_effective_start_date
562: ,l_max_ppm_effective_end_date;
563: --
564: hr_utility.set_location(l_proc, 110);
565: hr_utility.trace
566: ('p_payroll_id = ' || p_payroll_id);
567: hr_utility.trace
568: ('p_validation_start_date = ' || p_validation_start_date);
561: ,l_min_ppm_effective_start_date
562: ,l_max_ppm_effective_end_date;
563: --
564: hr_utility.set_location(l_proc, 110);
565: hr_utility.trace
566: ('p_payroll_id = ' || p_payroll_id);
567: hr_utility.trace
568: ('p_validation_start_date = ' || p_validation_start_date);
569: hr_utility.trace
563: --
564: hr_utility.set_location(l_proc, 110);
565: hr_utility.trace
566: ('p_payroll_id = ' || p_payroll_id);
567: hr_utility.trace
568: ('p_validation_start_date = ' || p_validation_start_date);
569: hr_utility.trace
570: ('p_validation_end_date = ' || p_validation_end_date);
571: hr_utility.trace
565: hr_utility.trace
566: ('p_payroll_id = ' || p_payroll_id);
567: hr_utility.trace
568: ('p_validation_start_date = ' || p_validation_start_date);
569: hr_utility.trace
570: ('p_validation_end_date = ' || p_validation_end_date);
571: hr_utility.trace
572: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
573: hr_utility.trace
567: hr_utility.trace
568: ('p_validation_start_date = ' || p_validation_start_date);
569: hr_utility.trace
570: ('p_validation_end_date = ' || p_validation_end_date);
571: hr_utility.trace
572: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
573: hr_utility.trace
574: ('l_org_payment_method_id = ' || l_org_payment_method_id);
575: hr_utility.trace
569: hr_utility.trace
570: ('p_validation_end_date = ' || p_validation_end_date);
571: hr_utility.trace
572: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
573: hr_utility.trace
574: ('l_org_payment_method_id = ' || l_org_payment_method_id);
575: hr_utility.trace
576: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
577: hr_utility.trace
571: hr_utility.trace
572: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
573: hr_utility.trace
574: ('l_org_payment_method_id = ' || l_org_payment_method_id);
575: hr_utility.trace
576: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
577: hr_utility.trace
578: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
579: --
573: hr_utility.trace
574: ('l_org_payment_method_id = ' || l_org_payment_method_id);
575: hr_utility.trace
576: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
577: hr_utility.trace
578: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
579: --
580: -- If a PPM has been retrieved, and no invalid PPMs have been identified
581: -- yet and we have not yet reached the validation end date then check the
585: and not l_invalid_ppm
586: and l_working_start_date < p_validation_end_date
587: loop
588: --
589: hr_utility.set_location(l_proc, 120);
590: --
591: -- Get the latest end date for all OPUs that are effective for the
592: -- current working date for the current PPM for the payroll id. As we are
593: -- only interested in OPUs that span the current PPM, setting the current
607: else
608: l_working_end_date := p_validation_end_date;
609: end if;
610: --
611: hr_utility.set_location(l_proc, 130);
612: hr_utility.trace
613: ('l_working_start_date = ' || l_working_start_date);
614: hr_utility.trace
615: ('l_working_end_date = ' || l_working_end_date);
608: l_working_end_date := p_validation_end_date;
609: end if;
610: --
611: hr_utility.set_location(l_proc, 130);
612: hr_utility.trace
613: ('l_working_start_date = ' || l_working_start_date);
614: hr_utility.trace
615: ('l_working_end_date = ' || l_working_end_date);
616: --
610: --
611: hr_utility.set_location(l_proc, 130);
612: hr_utility.trace
613: ('l_working_start_date = ' || l_working_start_date);
614: hr_utility.trace
615: ('l_working_end_date = ' || l_working_end_date);
616: --
617: open csr_get_opus
618: (l_org_payment_method_id
628: hr_api.g_eot);
629: l_max_opu_effective_end_date := nvl(l_cur_opu_effective_end_date,
630: hr_api.g_date);
631: --
632: hr_utility.set_location(l_proc, 140);
633: hr_utility.trace
634: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
635: hr_utility.trace
636: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
629: l_max_opu_effective_end_date := nvl(l_cur_opu_effective_end_date,
630: hr_api.g_date);
631: --
632: hr_utility.set_location(l_proc, 140);
633: hr_utility.trace
634: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
635: hr_utility.trace
636: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
637: hr_utility.trace
631: --
632: hr_utility.set_location(l_proc, 140);
633: hr_utility.trace
634: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
635: hr_utility.trace
636: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
637: hr_utility.trace
638: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
639: --
633: hr_utility.trace
634: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
635: hr_utility.trace
636: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
637: hr_utility.trace
638: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
639: --
640: while csr_get_opus%FOUND
641: and not l_invalid_ppm
643: or l_max_opu_effective_end_date < l_working_end_date
644: )
645: loop
646: --
647: hr_utility.set_location(l_proc, 150);
648: --
649: if l_cur_opu_effective_start_date < l_min_opu_effective_start_date
650: then
651: l_min_opu_effective_start_date := l_cur_opu_effective_start_date;
660: into l_org_pay_method_usage_id
661: ,l_cur_opu_effective_start_date
662: ,l_cur_opu_effective_end_date;
663: --
664: hr_utility.set_location(l_proc, 160);
665: hr_utility.trace
666: ('l_min_opu_effective_start_date = ' ||
667: l_min_opu_effective_start_date);
668: hr_utility.trace
661: ,l_cur_opu_effective_start_date
662: ,l_cur_opu_effective_end_date;
663: --
664: hr_utility.set_location(l_proc, 160);
665: hr_utility.trace
666: ('l_min_opu_effective_start_date = ' ||
667: l_min_opu_effective_start_date);
668: hr_utility.trace
669: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
664: hr_utility.set_location(l_proc, 160);
665: hr_utility.trace
666: ('l_min_opu_effective_start_date = ' ||
667: l_min_opu_effective_start_date);
668: hr_utility.trace
669: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
670: hr_utility.trace
671: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
672: hr_utility.trace
666: ('l_min_opu_effective_start_date = ' ||
667: l_min_opu_effective_start_date);
668: hr_utility.trace
669: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
670: hr_utility.trace
671: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
672: hr_utility.trace
673: ('l_cur_opu_effective_start_date = ' ||
674: l_cur_opu_effective_start_date);
668: hr_utility.trace
669: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
670: hr_utility.trace
671: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
672: hr_utility.trace
673: ('l_cur_opu_effective_start_date = ' ||
674: l_cur_opu_effective_start_date);
675: hr_utility.trace
676: ('l_cur_opu_effective_end_date = ' || l_cur_opu_effective_end_date);
671: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
672: hr_utility.trace
673: ('l_cur_opu_effective_start_date = ' ||
674: l_cur_opu_effective_start_date);
675: hr_utility.trace
676: ('l_cur_opu_effective_end_date = ' || l_cur_opu_effective_end_date);
677: --
678: if l_cur_opu_effective_start_date - 1 > l_max_opu_effective_end_date
679: then
677: --
678: if l_cur_opu_effective_start_date - 1 > l_max_opu_effective_end_date
679: then
680: --
681: hr_utility.set_location(l_proc, 170);
682: --
683: -- We have found a 'hole'.
684: --
685: -- ie. h
690: l_invalid_ppm := TRUE;
691: end if;
692: end loop;
693: --
694: hr_utility.set_location(l_proc, 180);
695: --
696: close csr_get_opus;
697: --
698: if l_min_opu_effective_start_date > l_working_start_date
698: if l_min_opu_effective_start_date > l_working_start_date
699: or l_max_opu_effective_end_date < l_working_end_date
700: then
701: --
702: hr_utility.set_location(l_proc, 190);
703: --
704: l_invalid_ppm := TRUE;
705: else
706: --
703: --
704: l_invalid_ppm := TRUE;
705: else
706: --
707: hr_utility.set_location(l_proc, 200);
708: --
709: fetch csr_get_ppms
710: into l_personal_payment_method_id
711: ,l_org_payment_method_id
718: else
719: l_working_start_date := p_validation_start_date;
720: end if;
721: --
722: hr_utility.set_location(l_proc, 210);
723: hr_utility.trace
724: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
725: hr_utility.trace
726: ('l_org_payment_method_id = ' || l_org_payment_method_id);
719: l_working_start_date := p_validation_start_date;
720: end if;
721: --
722: hr_utility.set_location(l_proc, 210);
723: hr_utility.trace
724: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
725: hr_utility.trace
726: ('l_org_payment_method_id = ' || l_org_payment_method_id);
727: hr_utility.trace
721: --
722: hr_utility.set_location(l_proc, 210);
723: hr_utility.trace
724: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
725: hr_utility.trace
726: ('l_org_payment_method_id = ' || l_org_payment_method_id);
727: hr_utility.trace
728: ('l_min_ppm_effective_start_date = ' ||
729: l_min_ppm_effective_start_date);
723: hr_utility.trace
724: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
725: hr_utility.trace
726: ('l_org_payment_method_id = ' || l_org_payment_method_id);
727: hr_utility.trace
728: ('l_min_ppm_effective_start_date = ' ||
729: l_min_ppm_effective_start_date);
730: hr_utility.trace
731: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
726: ('l_org_payment_method_id = ' || l_org_payment_method_id);
727: hr_utility.trace
728: ('l_min_ppm_effective_start_date = ' ||
729: l_min_ppm_effective_start_date);
730: hr_utility.trace
731: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
732: --
733: end if;
734: end loop;
733: end if;
734: end loop;
735: --
736: close csr_get_ppms;
737: hr_utility.set_location(l_proc, 220);
738: --
739: if l_invalid_ppm
740: then
741: --
738: --
739: if l_invalid_ppm
740: then
741: --
742: hr_utility.set_location(l_proc, 230);
743: --
744: hr_utility.set_message(801, 'HR_7969_ASG_INV_PAYROLL_PPMS');
745: hr_multi_message.add
746: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
740: then
741: --
742: hr_utility.set_location(l_proc, 230);
743: --
744: hr_utility.set_message(801, 'HR_7969_ASG_INV_PAYROLL_PPMS');
745: hr_multi_message.add
746: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
747: );
748: end if;
756: or p_datetrack_mode = 'UPDATE_OVERRIDE'
757: )
758: then
759: --
760: hr_utility.set_location(l_proc, 220);
761: --
762: -- Find any ASAs that arise after the change effective date.
763: --
764: open csr_any_future_asas;
767: --
768: if csr_any_future_asas%FOUND
769: then
770: --
771: hr_utility.set_location(l_proc, 230);
772: --
773: close csr_any_future_asas;
774: --
775: hr_utility.set_message(801, 'HR_7975_ASG_INV_FUTURE_ASA');
771: hr_utility.set_location(l_proc, 230);
772: --
773: close csr_any_future_asas;
774: --
775: hr_utility.set_message(801, 'HR_7975_ASG_INV_FUTURE_ASA');
776: hr_multi_message.add
777: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
778: );
779: else
777: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
778: );
779: else
780: --
781: hr_utility.set_location(l_proc, 240);
782: --
783: close csr_any_future_asas;
784: end if;
785: --
782: --
783: close csr_any_future_asas;
784: end if;
785: --
786: hr_utility.set_location(l_proc, 250);
787: end if;
788: --
789: hr_utility.set_location(' Leaving:'|| l_proc, 300);
790: end chk_payroll_id_int;
785: --
786: hr_utility.set_location(l_proc, 250);
787: end if;
788: --
789: hr_utility.set_location(' Leaving:'|| l_proc, 300);
790: end chk_payroll_id_int;
791: --
792: -- ---------------------------------------------------------------------------
793: -- |-----------------------< chk_people_group_id >---------------------------|
828: and pbg.people_group_structure = to_char(ppg.id_flex_num)
829: and pbg.business_group_id = p_business_group_id;
830: --
831: begin
832: hr_utility.set_location('Entering:'|| l_proc, 10);
833: --
834: --
835: -- Check mandatory parameters have been set
836: --
856: (p_api_name => l_proc
857: ,p_argument => 'validation_end_date'
858: ,p_argument_value => p_validation_end_date
859: );
860: hr_utility.set_location(l_proc, 20);
861: --
862: -- Only proceed with validation if :
863: -- a) The current g_old_rec is current and
864: -- b) The value for people group has changed
867: (p_assignment_id => p_assignment_id
868: ,p_effective_date => p_effective_date
869: ,p_object_version_number => p_object_version_number
870: );
871: hr_utility.set_location(l_proc, 30);
872: --
873: if ((l_api_updating and
874: nvl(per_asg_shd.g_old_rec.people_group_id, hr_api.g_number) <>
875: nvl(p_people_group_id, hr_api.g_number)) or
874: nvl(per_asg_shd.g_old_rec.people_group_id, hr_api.g_number) <>
875: nvl(p_people_group_id, hr_api.g_number)) or
876: (NOT l_api_updating))
877: then
878: hr_utility.set_location(l_proc, 40);
879: --
880: -- Check if people group is set
881: --
882: if p_people_group_id is not null then
886: open csr_valid_people_group;
887: fetch csr_valid_people_group into l_enabled_flag;
888: if csr_valid_people_group%notfound then
889: close csr_valid_people_group;
890: hr_utility.set_message(801, 'HR_7385_ASG_INV_PEOPLE_GROUP');
891: hr_multi_message.add
892: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
893: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
894: );
894: );
895: --
896: else
897: close csr_valid_people_group;
898: hr_utility.set_location(l_proc, 50);
899: --
900: -- Check that the enabled flag is set to 'Y' for the people group.
901: --
902: If l_enabled_flag <> 'Y' then
900: -- Check that the enabled flag is set to 'Y' for the people group.
901: --
902: If l_enabled_flag <> 'Y' then
903: --
904: hr_utility.set_message(801, 'HR_51252_ASG_INV_PGP_ENBD_FLAG');
905: hr_multi_message.add
906: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
907: );
908: --
907: );
908: --
909: end if;
910: end if;
911: hr_utility.set_location(l_proc, 60);
912: --
913: -- Check that the id_flex_num value for the
914: -- people_group_id can be cross referenced to the
915: -- people_group_structure on per_business_groups for
918: open csr_valid_id_flex_num;
919: fetch csr_valid_id_flex_num into l_exists;
920: if csr_valid_id_flex_num%notfound then
921: close csr_valid_id_flex_num;
922: hr_utility.set_message(801, 'HR_7386_ASG_INV_PEOP_GRP_LINK');
923: hr_multi_message.add
924: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
925: );
926: --
926: --
927: else
928: close csr_valid_id_flex_num;
929: end if;
930: hr_utility.set_location(l_proc, 70);
931: --
932: end if;
933: --
934: end if;
931: --
932: end if;
933: --
934: end if;
935: hr_utility.set_location(' Leaving:'|| l_proc, 100);
936: --
937: end chk_people_group_id;
938: --
939: -- ---------------------------------------------------------------------------
954: l_proc varchar2(72) := g_package||'chk_perf_review_period_freq';
955: l_api_updating boolean;
956: --
957: begin
958: hr_utility.set_location('Entering:'|| l_proc, 10);
959: --
960: -- Check mandatory parameters have been set
961: --
962: hr_api.mandatory_arg_error
975: (p_api_name => l_proc
976: ,p_argument => 'validation_end_date'
977: ,p_argument_value => p_validation_end_date
978: );
979: hr_utility.set_location(l_proc, 20);
980: --
981: -- Only proceed with validation if :
982: -- a) The current g_old_rec is current and
983: -- b) The value for performance review period frequency has changed
986: (p_assignment_id => p_assignment_id
987: ,p_effective_date => p_effective_date
988: ,p_object_version_number => p_object_version_number
989: );
990: hr_utility.set_location(l_proc, 30);
991: --
992: if ((l_api_updating and
993: nvl(per_asg_shd.g_old_rec.perf_review_period_frequency,
994: hr_api.g_varchar2) <> nvl(p_perf_review_period_frequency,
995: hr_api.g_varchar2))
996: or
997: (NOT l_api_updating))
998: then
999: hr_utility.set_location(l_proc, 40);
1000: --
1001: -- Check if performance review period frequency is set
1002: --
1003: if p_perf_review_period_frequency is not null then
1006: -- or benefit or offer assignment.
1007: --
1008: if p_assignment_type not in ('E','A','B','O') then
1009: --
1010: hr_utility.set_message(801, 'HR_51178_ASG_INV_ASG_TYP_PRPF');
1011: hr_multi_message.add
1012: (p_associated_column1 =>
1013: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1014: );
1013: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1014: );
1015: --
1016: end if;
1017: hr_utility.set_location(l_proc, 50);
1018: --
1019: -- Check that the performance review period frequency exists in
1020: -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
1021: -- flag set to 'Y' and that the effective start date of the
1030: ,p_lookup_code => p_perf_review_period_frequency
1031: )
1032: then
1033: --
1034: hr_utility.set_message(801, 'HR_51149_ASG_INV_PRP_FREQ');
1035: hr_multi_message.add
1036: (p_associated_column1 =>
1037: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1038: );
1037: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1038: );
1039: --
1040: end if;
1041: hr_utility.set_location(l_proc, 60);
1042: --
1043: end if;
1044: hr_utility.set_location(l_proc, 70);
1045: --
1040: end if;
1041: hr_utility.set_location(l_proc, 60);
1042: --
1043: end if;
1044: hr_utility.set_location(l_proc, 70);
1045: --
1046: end if;
1047: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1048: --
1043: end if;
1044: hr_utility.set_location(l_proc, 70);
1045: --
1046: end if;
1047: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1048: --
1049: end chk_perf_review_period_freq;
1050: --
1051: -- ---------------------------------------------------------------------------
1064: l_proc varchar2(72) := g_package||'chk_perf_review_period';
1065: l_api_updating boolean;
1066: --
1067: begin
1068: hr_utility.set_location('Entering:'|| l_proc, 1);
1069: --
1070: -- Check mandatory parameters have been set
1071: --
1072: hr_api.mandatory_arg_error
1083: (p_assignment_id => p_assignment_id
1084: ,p_effective_date => p_effective_date
1085: ,p_object_version_number => p_object_version_number);
1086: --
1087: hr_utility.set_location(l_proc, 2);
1088: --
1089: if ((l_api_updating and
1090: nvl(per_asg_shd.g_old_rec.perf_review_period,
1091: hr_api.g_number) <> nvl(p_perf_review_period,
1092: hr_api.g_number))
1093: or (NOT l_api_updating))
1094: then
1095: --
1096: hr_utility.set_location(l_proc, 3);
1097: --
1098: -- Check if perf review period is not null
1099: --
1100: if p_perf_review_period is not null then
1103: -- Benefit or Offer assignment.
1104: --
1105: if p_assignment_type not in ('E','A','B','O') then
1106: --
1107: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1108: hr_utility.raise_error;
1109: end if;
1110: hr_utility.set_location(l_proc, 4);
1111: --
1104: --
1105: if p_assignment_type not in ('E','A','B','O') then
1106: --
1107: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1108: hr_utility.raise_error;
1109: end if;
1110: hr_utility.set_location(l_proc, 4);
1111: --
1112: end if;
1106: --
1107: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1108: hr_utility.raise_error;
1109: end if;
1110: hr_utility.set_location(l_proc, 4);
1111: --
1112: end if;
1113: --
1114: end if;
1112: end if;
1113: --
1114: end if;
1115: --
1116: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1117: exception
1118: when app_exception.application_exception then
1119: if hr_multi_message.exception_add
1120: (p_associated_column1 =>
1119: if hr_multi_message.exception_add
1120: (p_associated_column1 =>
1121: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1122: ) then
1123: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1124: raise;
1125: end if;
1126: hr_utility.set_location(' Leaving:'|| l_proc, 7);
1127: end chk_perf_review_period;
1122: ) then
1123: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1124: raise;
1125: end if;
1126: hr_utility.set_location(' Leaving:'|| l_proc, 7);
1127: end chk_perf_review_period;
1128: --
1129: -- ---------------------------------------------------------------------------
1130: -- |-------------------< chk_perf_rp_freq_perf_rp >--------------------------|
1142: l_proc varchar2(72):= g_package||'chk_perf_rp_freq_perf_rp';
1143: l_api_updating boolean;
1144: --
1145: begin
1146: hr_utility.set_location('Entering:'|| l_proc, 1);
1147: --
1148: if hr_multi_message.no_exclusive_error
1149: (p_check_column1 =>
1150: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1181: hr_api.g_number) <> nvl(p_perf_review_period, hr_api.g_number)))
1182: or
1183: (NOT l_api_updating)) then
1184: --
1185: hr_utility.set_location(l_proc, 2);
1186: --
1187: -- Check if perf review period frequency or perf review period is not null.
1188: --
1189: if p_perf_review_period_frequency is not null
1187: -- Check if perf review period frequency or perf review period is not null.
1188: --
1189: if p_perf_review_period_frequency is not null
1190: or p_perf_review_period is not null then
1191: hr_utility.set_location(l_proc, 3);
1192: --
1193: -- Check if perf review period frequency or perf review period are null.
1194: --
1195: if p_perf_review_period_frequency is null
1194: --
1195: if p_perf_review_period_frequency is null
1196: or p_perf_review_period is null then
1197: --
1198: hr_utility.set_message(801, 'HR_51163_ASG_INV_PRPF_PRP_COMB');
1199: hr_utility.raise_error;
1200: --
1201: end if;
1202: --
1195: if p_perf_review_period_frequency is null
1196: or p_perf_review_period is null then
1197: --
1198: hr_utility.set_message(801, 'HR_51163_ASG_INV_PRPF_PRP_COMB');
1199: hr_utility.raise_error;
1200: --
1201: end if;
1202: --
1203: end if;
1204: --
1205: end if;
1206: end if;
1207: --
1208: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1209: --
1210: exception
1211: when app_exception.application_exception then
1212: if hr_multi_message.exception_add
1213: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1214: ,p_associated_column2 =>
1215: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1216: ) then
1217: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1218: raise;
1219: end if;
1220: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1221: end chk_perf_rp_freq_perf_rp;
1216: ) then
1217: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1218: raise;
1219: end if;
1220: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1221: end chk_perf_rp_freq_perf_rp;
1222: --
1223: -- ---------------------------------------------------------------------------
1224: -- |---------------------< chk_period_of_service_id >------------------------|
1251: between date_start
1252: and nvl(actual_termination_date, hr_api.g_eot);
1253: --
1254: begin
1255: hr_utility.set_location('Entering:'|| l_proc, 10);
1256: --
1257: -- Check mandatory parameters have been set
1258: --
1259: hr_api.mandatory_arg_error
1279: ,p_argument => 'validation_end_date'
1280: ,p_argument_value => p_effective_date
1281: );
1282: --
1283: hr_utility.set_location(l_proc, 20);
1284: --
1285: -- Check if the assignment is being updated.
1286: --
1287: l_api_updating := per_asg_shd.api_updating
1288: (p_assignment_id => p_assignment_id
1289: ,p_effective_date => p_effective_date
1290: ,p_object_version_number => p_object_version_number
1291: );
1292: hr_utility.set_location(l_proc, 30);
1293: --
1294: if NOT l_api_updating then
1295: --
1296: hr_utility.set_location(l_proc, 40);
1292: hr_utility.set_location(l_proc, 30);
1293: --
1294: if NOT l_api_updating then
1295: --
1296: hr_utility.set_location(l_proc, 40);
1297: --
1298: -- Check that the assignment is an employee assignment.
1299: --
1300: if p_assignment_type <> 'E' then
1302: -- Check that period of service is not set
1303: --
1304: If p_period_of_service_id is not null then
1305: --
1306: hr_utility.set_message(801, 'HR_51203_ASG_INV_ASG_TYP_PDS');
1307: hr_multi_message.add
1308: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1309: );
1310: --
1308: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1309: );
1310: --
1311: end if;
1312: hr_utility.set_location(l_proc, 50);
1313: --
1314: else
1315: --
1316: -- Check the mandatory parameter period of service for
1320: (p_api_name => l_proc
1321: ,p_argument => 'period_of_service_id'
1322: ,p_argument_value => p_period_of_service_id
1323: );
1324: hr_utility.set_location(l_proc, 60);
1325: --
1326: -- Check if the period of service exists between
1327: -- the period of service date start and actual termination date.
1328: --
1329: open csr_valid_pds;
1330: fetch csr_valid_pds into l_business_group_id, l_actual_termination_date;
1331: if csr_valid_pds%notfound then
1332: close csr_valid_pds;
1333: hr_utility.set_message(801, 'HR_7391_ASG_INV_PERIOD_OF_SERV');
1334: hr_multi_message.add
1335: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1336: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1337: );
1338: --
1339: else
1340: close csr_valid_pds;
1341: end if;
1342: hr_utility.set_location(l_proc, 70);
1343: --
1344: -- Check that the period of service is in the same business group
1345: -- as the business group of the assignment.
1346: --
1345: -- as the business group of the assignment.
1346: --
1347: If p_business_group_id <> l_business_group_id then
1348: --
1349: hr_utility.set_message(801, 'HR_51320_ASG_INV_PDS_BG');
1350: hr_multi_message.add
1351: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1352: );
1353: --
1351: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1352: );
1353: --
1354: end if;
1355: hr_utility.set_location(l_proc, 80);
1356: --
1357: -- Check if the period of service has been closed before the
1358: -- validation end date.
1359: --
1358: -- validation end date.
1359: --
1360: If p_validation_end_date > nvl(l_actual_termination_date, hr_api.g_eot) then
1361: --
1362: hr_utility.set_message(801, 'HR_6434_EMP_ASS_PER_CLOSED');
1363: hr_multi_message.add
1364: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1365: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1366: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
1366: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
1367: );
1368: --
1369: end if;
1370: hr_utility.set_location(l_proc, 90);
1371: --
1372: end if;
1373: --
1374: end if;
1371: --
1372: end if;
1373: --
1374: end if;
1375: hr_utility.set_location(' Leaving:'|| l_proc, 8);
1376: end chk_period_of_service_id;
1377: --
1378: -- ---------------------------------------------------------------------------
1379: -- |--------------------------< chk_person_id >------------------------------|
1398: and ppf.effective_end_date;
1399: --
1400: --
1401: begin
1402: hr_utility.set_location('Entering:'|| l_proc, 1);
1403: --
1404: -- Check mandatory parameters have been set
1405: --
1406: hr_api.mandatory_arg_error
1408: ,p_argument => 'person_id'
1409: ,p_argument_value => p_person_id
1410: );
1411: --
1412: hr_utility.set_location(l_proc, 2);
1413: --
1414: -- Check that person business group is the same as
1415: -- the assignment business group
1416: --
1417: open csr_get_bus_grp;
1418: fetch csr_get_bus_grp into l_business_group_id;
1419: if l_business_group_id <> p_business_group_id then
1420: close csr_get_bus_grp;
1421: hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
1422: hr_utility.raise_error;
1423: end if;
1424: close csr_get_bus_grp;
1425: --
1418: fetch csr_get_bus_grp into l_business_group_id;
1419: if l_business_group_id <> p_business_group_id then
1420: close csr_get_bus_grp;
1421: hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
1422: hr_utility.raise_error;
1423: end if;
1424: close csr_get_bus_grp;
1425: --
1426: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1422: hr_utility.raise_error;
1423: end if;
1424: close csr_get_bus_grp;
1425: --
1426: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1427: exception
1428: when app_exception.application_exception then
1429: if hr_multi_message.exception_add
1430: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1428: when app_exception.application_exception then
1429: if hr_multi_message.exception_add
1430: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1431: ) then
1432: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1433: raise;
1434: end if;
1435: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1436: end chk_person_id;
1431: ) then
1432: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1433: raise;
1434: end if;
1435: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1436: end chk_person_id;
1437: --
1438: -- ---------------------------------------------------------------------------
1439: -- |---------------------< chk_person_referred_by_id >-----------------------|
1467: between effective_start_date
1468: and effective_end_date;
1469: --
1470: begin
1471: hr_utility.set_location('Entering:'|| l_proc, 10);
1472: --
1473: -- Check mandatory parameters have been set
1474: --
1475: hr_api.mandatory_arg_error
1489: ,p_argument => 'validation_end_date'
1490: ,p_argument_value => p_validation_end_date
1491: );
1492: --
1493: hr_utility.set_location(l_proc, 20);
1494: --
1495: -- Only proceed with validation if :
1496: -- a) The current g_old_rec is current and
1497: -- b) The value for person referred by has changed
1499: l_api_updating := per_asg_shd.api_updating
1500: (p_assignment_id => p_assignment_id
1501: ,p_effective_date => p_effective_date
1502: ,p_object_version_number => p_object_version_number);
1503: hr_utility.set_location(l_proc, 30);
1504: --
1505: if ((l_api_updating and
1506: nvl(per_asg_shd.g_old_rec.person_referred_by_id, hr_api.g_number)
1507: <> nvl(p_person_referred_by_id, hr_api.g_number)) or
1505: if ((l_api_updating and
1506: nvl(per_asg_shd.g_old_rec.person_referred_by_id, hr_api.g_number)
1507: <> nvl(p_person_referred_by_id, hr_api.g_number)) or
1508: (NOT l_api_updating)) then
1509: hr_utility.set_location(l_proc, 40);
1510: --
1511: -- Check if person referred by is not null
1512: --
1513: if p_person_referred_by_id is not null then
1515: -- Check that the assignment is not an applicant or Offer assignment.
1516: --
1517: if p_assignment_type in ('E','B','C')then
1518: --
1519: hr_utility.set_message(801, 'HR_51224_ASG_INV_ASG_TYP_PRB');
1520: hr_multi_message.add
1521: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1522: );
1523: --
1521: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1522: );
1523: --
1524: end if;
1525: hr_utility.set_location(l_proc, 50);
1526: --
1527: -- Check that the person referred by is'nt the same as the person
1528: -- of the assignment.
1529: --
1528: -- of the assignment.
1529: --
1530: If p_person_referred_by_id = p_person_id then
1531: --
1532: hr_utility.set_message(801, 'HR_51304_ASG_APL_EQUAL_PRB');
1533: hr_multi_message.add
1534: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1535: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1536: );
1535: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1536: );
1537: --
1538: end if;
1539: hr_utility.set_location(l_proc, 60);
1540: --
1541: -- Check if the person referred by exists where the effective
1542: -- start date of the assignment is between the effective start
1543: -- date and effective end date of the person referred by.
1553: --
1554: if p_assignment_type <> 'O'
1555: then
1556: --
1557: hr_utility.set_message(801, 'HR_51302_ASG_INV_PER_REF_BY');
1558: hr_multi_message.add
1559: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1560: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1561: );
1564: --
1565: else
1566: close csr_val_prb_id;
1567: end if;
1568: hr_utility.set_location(l_proc, 70);
1569: --
1570: -- Check that the person referred by is in the same business group
1571: -- as the business group of the assignment.
1572: --
1573: If (p_business_group_id <> l_business_group_id AND
1574: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
1575: then
1576: --
1577: hr_utility.set_message(801, 'HR_51303_ASG_INV_PER_REF_BY_BG');
1578: hr_multi_message.add
1579: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1580: );
1581: --
1579: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1580: );
1581: --
1582: end if;
1583: hr_utility.set_location(l_proc, 80);
1584: --
1585: -- Check that the person referred by is an employee.
1586: --
1587: -- Bug 3190625
1590: -- no errors shown
1591: if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
1592: ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
1593: nvl(l_current_npw_flag, 'N') = 'Y') ) then
1594: hr_utility.set_message(801, 'HR_51305_ASG_PER_RB_NOT_EMP');
1595: hr_multi_message.add
1596: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID');
1597: --
1598: end if;
1595: hr_multi_message.add
1596: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID');
1597: --
1598: end if;
1599: hr_utility.set_location(l_proc, 90);
1600: --
1601: end if;
1602: --
1603: end if;
1601: end if;
1602: --
1603: end if;
1604: --
1605: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1606: end chk_person_referred_by_id;
1607: --
1608: -- ---------------------------------------------------------------------------
1609: -- |------------------------< chk_position_id >------------------------------|
1645: and ps.shared_type_id = hp.availability_status_id
1646: and ps.system_type_cd = 'ACTIVE' ;
1647: --
1648: begin
1649: hr_utility.set_location('Entering:'|| l_proc, 10);
1650: --
1651: --
1652: -- Check mandatory parameters have been set
1653: --
1673: (p_api_name => l_proc
1674: ,p_argument => 'business_group_id'
1675: ,p_argument_value => p_business_group_id
1676: );
1677: hr_utility.set_location(l_proc, 20);
1678: --
1679: -- Only proceed with validation if :
1680: -- a) The current g_old_rec is current and
1681: -- b) The value for position_id has changed
1690: nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
1691: nvl(p_position_id, hr_api.g_number))
1692: or
1693: (NOT l_api_updating)) then
1694: hr_utility.set_location(l_proc, 30);
1695: --
1696: -- Check that if the value for position_id is not null
1697: -- then it exists date effective in HR_POSITIONS
1698: --
1703: open csr_valid_pos;
1704: fetch csr_valid_pos into l_pos_bus_group_id;
1705: if csr_valid_pos%notfound then
1706: close csr_valid_pos;
1707: hr_utility.set_message(801, 'HR_51000_ASG_INVALID_POS');
1708: hr_multi_message.add
1709: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1710: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1711: );
1711: );
1712: else
1713: close csr_valid_pos;
1714: end if;
1715: hr_utility.set_location(l_proc, 40);
1716: --
1717: -- Check if the business_group_id for the assignment matches
1718: -- the business_group_id in HR_POSITIONS date effectively.
1719: --
1718: -- the business_group_id in HR_POSITIONS date effectively.
1719: --
1720: if l_pos_bus_group_id <> p_business_group_id then
1721: --
1722: hr_utility.set_message(801, 'HR_51009_ASG_INVALID_BG_POS');
1723: hr_multi_message.add
1724: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1725: );
1726: --
1724: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1725: );
1726: --
1727: end if;
1728: hr_utility.set_location(l_proc, 50);
1729: --
1730: end if;
1731: --
1732: --
1731: --
1732: --
1733: end if;
1734: --
1735: hr_utility.set_location('Leaving'||l_proc, 80);
1736: end chk_position_id;
1737: --
1738: ------------------------------------------------------------------------------
1739: -------------------< chk_position_id_grade_id >-------------------------------
1780: and nvl(date_to, hr_api.g_eot);
1781: --
1782: begin
1783: --
1784: hr_utility.set_location('Entering:'|| l_proc, 1);
1785: --
1786: if hr_multi_message.no_exclusive_error
1787: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1788: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
1825: nvl(p_grade_id, hr_api.g_number))))
1826: or
1827: (NOT l_api_updating) then
1828: --
1829: hr_utility.set_location(l_proc, 2);
1830: --
1831: -- Check that position_id and grade_id both contain not null values
1832: --
1833: if p_position_id is not null and p_grade_id is not null then
1856: end if;
1857: --
1858: -- Bug 3566686 Ends Here
1859: --
1860: hr_utility.set_location(l_proc, 3);
1861: --
1862: end if;
1863: --
1864: end if;
1864: end if;
1865: end if;
1866: --
1867: p_inv_pos_grade_warning := l_inv_pos_grade_warning;
1868: hr_utility.set_location('Leaving'||l_proc, 4);
1869: end chk_position_id_grade_id;
1870: --
1871: ------------------------------------------------------------------------------
1872: --------------------------< chk_position_id_org_id >--------------------------
1905: and ps.system_type_cd = 'ACTIVE' ;
1906: --
1907: begin
1908: --
1909: hr_utility.set_location('Entering:'|| l_proc, 1);
1910: --
1911: if hr_multi_message.no_exclusive_error
1912: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1913: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
1951: nvl(p_organization_id, hr_api.g_number))))
1952: or
1953: (NOT l_api_updating) then
1954: --
1955: hr_utility.set_location(l_proc, 2);
1956: --
1957: -- Check if the position is null
1958: --
1959: If p_position_id is not null then
1960: --
1961: -- Check if assignment position_id and organization_id combination
1962: -- matches the combination in HR_POSITIONS.
1963: --
1964: hr_utility.set_location(l_proc, 3);
1965: open csr_valid_pos_org_comb;
1966: fetch csr_valid_pos_org_comb into l_exists;
1967: if csr_valid_pos_org_comb%notfound then
1968: close csr_valid_pos_org_comb;
1965: open csr_valid_pos_org_comb;
1966: fetch csr_valid_pos_org_comb into l_exists;
1967: if csr_valid_pos_org_comb%notfound then
1968: close csr_valid_pos_org_comb;
1969: hr_utility.set_message(801, 'HR_51055_ASG_INV_POS_ORG_COMB');
1970: hr_utility.raise_error;
1971: end if;
1972: close csr_valid_pos_org_comb;
1973: --
1966: fetch csr_valid_pos_org_comb into l_exists;
1967: if csr_valid_pos_org_comb%notfound then
1968: close csr_valid_pos_org_comb;
1969: hr_utility.set_message(801, 'HR_51055_ASG_INV_POS_ORG_COMB');
1970: hr_utility.raise_error;
1971: end if;
1972: close csr_valid_pos_org_comb;
1973: --
1974: end if;
1974: end if;
1975: end if;
1976: end if;
1977: --
1978: hr_utility.set_location('Leaving'||l_proc, 4);
1979: exception
1980: when app_exception.application_exception then
1981: if hr_multi_message.exception_add
1982: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1982: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1983: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
1984: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1985: ) then
1986: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1987: raise;
1988: end if;
1989: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1990: end chk_position_id_org_id;
1985: ) then
1986: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1987: raise;
1988: end if;
1989: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1990: end chk_position_id_org_id;
1991: --
1992: ------------------------------------------------------------------------------
1993: -------------------------< chk_position_id_job_id >---------------------------
2024: and ps.shared_type_id = hp.availability_status_id
2025: and ps.system_type_cd = 'ACTIVE' ;
2026: --
2027: begin
2028: hr_utility.set_location('Entering:'|| l_proc, 10);
2029: --
2030: if hr_multi_message.no_exclusive_error
2031: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2032: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2050: (p_api_name => l_proc
2051: ,p_argument => 'effective_date'
2052: ,p_argument_value => p_effective_date
2053: );
2054: hr_utility.set_location(l_proc, 20);
2055: --
2056: -- Only proceed with validation if :
2057: -- a) The current g_old_rec is current and
2058: -- b) The value for position or job has changed
2060: l_api_updating := per_asg_shd.api_updating
2061: (p_assignment_id => p_assignment_id
2062: ,p_effective_date => p_effective_date
2063: ,p_object_version_number => p_object_version_number);
2064: hr_utility.set_location(l_proc, 30);
2065: --
2066: if (l_api_updating and
2067: ((nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
2068: nvl(p_position_id, hr_api.g_number))
2071: nvl(p_job_id, hr_api.g_number))))
2072: or
2073: (NOT l_api_updating)
2074: then
2075: hr_utility.set_location(l_proc, 40);
2076: --
2077: -- Check if the assignment job and position are not null
2078: --
2079: if p_position_id is not null and p_job_id is not null then
2084: open csr_valid_pos_job_comb;
2085: fetch csr_valid_pos_job_comb into l_exists;
2086: if csr_valid_pos_job_comb%notfound then
2087: close csr_valid_pos_job_comb;
2088: hr_utility.set_message(801, 'HR_51056_ASG_INV_POS_JOB_COMB');
2089: hr_multi_message.add
2090: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2091: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2092: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2098: elsif p_job_id is null and p_position_id is not null then
2099: --
2100: -- Position is not null but job is null
2101: --
2102: hr_utility.set_message(801, 'HR_51057_ASG_JOB_NULL_VALUE');
2103: hr_multi_message.add
2104: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2105: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2106: );
2109: --
2110: end if;
2111: end if;
2112: --
2113: hr_utility.set_location('Leaving'||l_proc, 3);
2114: end chk_position_id_job_id;
2115: --
2116: -- ---------------------------------------------------------------------------
2117: -- |-------------------------< chk_primary_flag >----------------------------|
2231: and pas.primary_flag = 'Y');
2232:
2233: begin
2234: --
2235: hr_utility.set_location('Entering:'|| l_proc, 10);
2236: --
2237: --
2238: -- Check mandatory parameters have been set
2239: --
2260: (p_api_name => l_proc
2261: ,p_argument => 'validation_end_date'
2262: ,p_argument_value => p_validation_end_date
2263: );
2264: hr_utility.set_location(l_proc, 20);
2265: --
2266: -- Only proceed with validation if :
2267: -- a) The current g_old_rec is current and
2268: -- b) The value for primary flag has changed
2271: (p_assignment_id => p_assignment_id
2272: ,p_effective_date => p_effective_date
2273: ,p_object_version_number => p_object_version_number);
2274: --
2275: hr_utility.set_location(l_proc, 30);
2276: --
2277: if ((l_api_updating and
2278: nvl(per_asg_shd.g_old_rec.primary_flag, hr_api.g_varchar2) <>
2279: nvl(p_primary_flag, hr_api.g_varchar2)) or
2285: --
2286: per_asg_shd.constraint_error
2287: (p_constraint_name => 'PER_ASS_PRIMARY_FLAG_CHK');
2288: --
2289: hr_utility.set_location(l_proc, 30);
2290: --
2291: end if;
2292: --
2293: hr_utility.set_location(l_proc, 40);
2289: hr_utility.set_location(l_proc, 30);
2290: --
2291: end if;
2292: --
2293: hr_utility.set_location(l_proc, 40);
2294: --
2295: -- If inserting 'primary' assignment, check that no
2296: -- other primary assignments exist for the person
2297: -- the new assignment is linked to
2302: --
2303: if p_assignment_type = 'A'
2304: or p_assignment_type = 'O' then
2305: --
2306: hr_utility.set_message(801, 'HR_51198_ASG_INV_APL_ASG_PF');
2307: --
2308: hr_multi_message.add
2309: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG');
2310: --
2309: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG');
2310: --
2311: end if;
2312: --
2313: hr_utility.set_location(l_proc, 50);
2314: --
2315: -- Check that the effective end date is the end of time
2316: --
2317: If p_validation_end_date <> hr_api.g_eot then
2315: -- Check that the effective end date is the end of time
2316: --
2317: If p_validation_end_date <> hr_api.g_eot then
2318: --
2319: hr_utility.set_message(801, 'HR_51323_ASG_INV_PRIM_ASG_EED');
2320: --
2321: hr_multi_message.add
2322: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2323: --
2322: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2323: --
2324: end if;
2325: --
2326: hr_utility.set_location(l_proc, 60);
2327: --
2328: if hr_multi_message.no_exclusive_error
2329: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2330: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2328: if hr_multi_message.no_exclusive_error
2329: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2330: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2331: --
2332: hr_utility.set_location(l_proc, 70);
2333: --
2334: if p_assignment_type = 'C' then
2335: --
2336: hr_utility.set_location(l_proc, 80);
2332: hr_utility.set_location(l_proc, 70);
2333: --
2334: if p_assignment_type = 'C' then
2335: --
2336: hr_utility.set_location(l_proc, 80);
2337: --
2338: open csr_cwk_asg_exists;
2339: fetch csr_cwk_asg_exists into l_exists;
2340: --
2341: if csr_cwk_asg_exists%found then
2342: --
2343: close csr_cwk_asg_exists;
2344: --
2345: hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2346: --
2347: hr_multi_message.add
2348: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2349: ,p_associated_column2 =>
2354: close csr_cwk_asg_exists;
2355: --
2356: end if;
2357: --
2358: hr_utility.set_location(l_proc, 90);
2359: --
2360: elsif p_assignment_type = 'E' then
2361: --
2362: hr_utility.set_location(l_proc, 100);
2358: hr_utility.set_location(l_proc, 90);
2359: --
2360: elsif p_assignment_type = 'E' then
2361: --
2362: hr_utility.set_location(l_proc, 100);
2363: --
2364: -- Check if a primary assignment already exists
2365: --
2366: open csr_asg_exists;
2369: if csr_asg_exists%found then
2370: --
2371: close csr_asg_exists;
2372: --
2373: hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2374: --
2375: hr_multi_message.add
2376: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2377: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID');
2381: close csr_asg_exists;
2382: --
2383: end if;
2384: --
2385: hr_utility.set_location(l_proc, 110);
2386: --
2387: end if;
2388: --
2389: end if; -- no exclusive error
2387: end if;
2388: --
2389: end if; -- no exclusive error
2390: --
2391: hr_utility.set_location(l_proc, 120);
2392: --
2393: else
2394: --
2395: hr_utility.set_location(l_proc, 130);
2391: hr_utility.set_location(l_proc, 120);
2392: --
2393: else
2394: --
2395: hr_utility.set_location(l_proc, 130);
2396: --
2397: -- Check if the assignment is an employee assignment or a
2398: -- non payrolled worker assignment.
2399: --
2401: --
2402: -- Check that a primary employee assignment exists during
2403: -- the entire date range of the non-primary assignment.
2404: --
2405: hr_utility.set_location(l_proc, 140);
2406: --
2407: if hr_multi_message.no_exclusive_error
2408: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2409: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2407: if hr_multi_message.no_exclusive_error
2408: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2409: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2410: --
2411: hr_utility.set_location(l_proc, 150);
2412: --
2413: -- Check that the primary cwk assignment exists during
2414: -- the entire date range of the non-primary assignment
2415: --
2414: -- the entire date range of the non-primary assignment
2415: --
2416: if p_assignment_type = 'C' then
2417: --
2418: hr_utility.set_location(l_proc, 160);
2419: --
2420: open csr_ins_non_cwk_prim;
2421: fetch csr_ins_non_cwk_prim into l_exists;
2422: --
2423: if csr_ins_non_cwk_prim%notfound then
2424: --
2425: close csr_ins_non_cwk_prim;
2426: --
2427: hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2428: --
2429: hr_multi_message.add
2430: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2431: ,p_associated_column2 =>
2438: close csr_ins_non_cwk_prim;
2439: --
2440: end if;
2441: --
2442: hr_utility.set_location(l_proc, 170);
2443: --
2444: elsif p_assignment_type = 'E' then
2445: --
2446: hr_utility.set_location(l_proc, 180);
2442: hr_utility.set_location(l_proc, 170);
2443: --
2444: elsif p_assignment_type = 'E' then
2445: --
2446: hr_utility.set_location(l_proc, 180);
2447: --
2448: --
2449: -- 120.10 (START)
2450: --
2478: if csr_ins_non_prim%notfound then
2479: --
2480: close csr_ins_non_prim;
2481: --
2482: hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2483: --
2484: hr_multi_message.add
2485: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2486: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2492: close csr_ins_non_prim;
2493: --
2494: end if;
2495: --
2496: hr_utility.set_location(l_proc, 190);
2497: --
2498: end if;
2499: --
2500: end if; -- no exclusive error
2498: end if;
2499: --
2500: end if; -- no exclusive error
2501: --
2502: hr_utility.set_location(l_proc, 200);
2503: --
2504: end if;
2505: --
2506: end if;
2506: end if;
2507: --
2508: end if;
2509: --
2510: hr_utility.set_location(' Leaving:'|| l_proc, 999);
2511: --
2512: end chk_primary_flag;
2513: --
2514: -- ---------------------------------------------------------------------------
2525: l_proc varchar2(72) := g_package||'chk_probation_period';
2526: l_api_updating boolean;
2527: --
2528: begin
2529: hr_utility.set_location('Entering:'|| l_proc, 10);
2530: --
2531: hr_api.mandatory_arg_error
2532: (p_api_name => l_proc
2533: ,p_argument => 'effective_date'
2532: (p_api_name => l_proc
2533: ,p_argument => 'effective_date'
2534: ,p_argument_value => p_effective_date
2535: );
2536: hr_utility.set_location(l_proc, 20);
2537: --
2538: -- Only proceed with validation if :
2539: -- a) The current g_old_rec is current and
2540: -- b) The value for probation period has changed
2543: (p_assignment_id => p_assignment_id
2544: ,p_effective_date => p_effective_date
2545: ,p_object_version_number => p_object_version_number
2546: );
2547: hr_utility.set_location(l_proc, 30);
2548: --
2549: if ((l_api_updating and
2550: nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number)
2551: <> nvl(p_probation_period, hr_api.g_number))
2551: <> nvl(p_probation_period, hr_api.g_number))
2552: or
2553: (NOT l_api_updating))
2554: then
2555: hr_utility.set_location(l_proc, 40);
2556: --
2557: -- Check that if probation period is set then it's value
2558: -- is in the range 0 to 9999.99
2559: --
2562: if p_probation_period is not null
2563: and p_probation_period not between 0 and 9999.99
2564: then
2565: --
2566: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2567: hr_utility.raise_error;
2568: end if;
2569: hr_utility.set_location(l_proc, 50);
2570: --
2563: and p_probation_period not between 0 and 9999.99
2564: then
2565: --
2566: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2567: hr_utility.raise_error;
2568: end if;
2569: hr_utility.set_location(l_proc, 50);
2570: --
2571: end if;
2565: --
2566: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2567: hr_utility.raise_error;
2568: end if;
2569: hr_utility.set_location(l_proc, 50);
2570: --
2571: end if;
2572: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2573: exception
2568: end if;
2569: hr_utility.set_location(l_proc, 50);
2570: --
2571: end if;
2572: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2573: exception
2574: when app_exception.application_exception then
2575: if hr_multi_message.exception_add
2576: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2574: when app_exception.application_exception then
2575: if hr_multi_message.exception_add
2576: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2577: ) then
2578: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2579: raise;
2580: end if;
2581: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2582: --
2577: ) then
2578: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2579: raise;
2580: end if;
2581: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2582: --
2583: end chk_probation_period;
2584: --
2585: -- ---------------------------------------------------------------------------
2599: l_proc varchar2(72) := g_package||'chk_probation_unit';
2600: l_api_updating boolean;
2601: --
2602: begin
2603: hr_utility.set_location('Entering:'|| l_proc, 10);
2604: --
2605: -- Check mandatory parameters have been set
2606: --
2607: hr_api.mandatory_arg_error
2620: (p_api_name => l_proc
2621: ,p_argument => 'validation_end_date'
2622: ,p_argument_value => p_validation_end_date
2623: );
2624: hr_utility.set_location(l_proc, 20);
2625: --
2626: -- Only proceed with validation if :
2627: -- a) The current g_old_rec is current and
2628: -- b) The value for probation unit has changed
2631: (p_assignment_id => p_assignment_id
2632: ,p_effective_date => p_effective_date
2633: ,p_object_version_number => p_object_version_number
2634: );
2635: hr_utility.set_location(l_proc, 30);
2636: --
2637: if ((l_api_updating and
2638: nvl(per_asg_shd.g_old_rec.probation_unit, hr_api.g_varchar2)
2639: <> nvl(p_probation_unit, hr_api.g_varchar2))
2639: <> nvl(p_probation_unit, hr_api.g_varchar2))
2640: or
2641: (NOT l_api_updating))
2642: then
2643: hr_utility.set_location(l_proc, 40);
2644: --
2645: -- Check if probation unit is not null
2646: --
2647: if p_probation_unit is not null then
2659: ,p_lookup_code => p_probation_unit
2660: )
2661: then
2662: --
2663: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2664: hr_utility.raise_error;
2665: --
2666: end if;
2667: hr_utility.set_location(l_proc, 50);
2660: )
2661: then
2662: --
2663: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2664: hr_utility.raise_error;
2665: --
2666: end if;
2667: hr_utility.set_location(l_proc, 50);
2668: end if;
2663: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2664: hr_utility.raise_error;
2665: --
2666: end if;
2667: hr_utility.set_location(l_proc, 50);
2668: end if;
2669: --
2670: end if;
2671: --
2668: end if;
2669: --
2670: end if;
2671: --
2672: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2673: exception
2674: when app_exception.application_exception then
2675: if hr_multi_message.exception_add
2676: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2674: when app_exception.application_exception then
2675: if hr_multi_message.exception_add
2676: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2677: ) then
2678: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2679: raise;
2680: end if;
2681: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2682: end chk_probation_unit;
2677: ) then
2678: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2679: raise;
2680: end if;
2681: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2682: end chk_probation_unit;
2683: --
2684: -- ---------------------------------------------------------------------------
2685: -- |-------------------< chk_prob_unit_prob_period >-------------------------|
2697: l_proc varchar2(72):= g_package||'chk_prob_unit_prob_period';
2698: l_api_updating boolean;
2699: --
2700: begin
2701: hr_utility.set_location('Entering:'|| l_proc, 1);
2702: --
2703: -- Check mandatory parameters have been set
2704: --
2705: if hr_multi_message.no_exclusive_error
2730: <> nvl(p_probation_period, hr_api.g_number))
2731: or
2732: (NOT l_api_updating)) then
2733: --
2734: hr_utility.set_location(l_proc, 2);
2735: --
2736: -- Check if probation unit or probation period is not null.
2737: --
2738: if p_probation_unit is not null or p_probation_period is not null then
2740: -- Check if probation unit or probation period are null.
2741: --
2742: if p_probation_unit is null or p_probation_period is null then
2743: --
2744: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2745: hr_utility.raise_error;
2746: --
2747: end if;
2748: hr_utility.set_location(l_proc, 3);
2741: --
2742: if p_probation_unit is null or p_probation_period is null then
2743: --
2744: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2745: hr_utility.raise_error;
2746: --
2747: end if;
2748: hr_utility.set_location(l_proc, 3);
2749: --
2744: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2745: hr_utility.raise_error;
2746: --
2747: end if;
2748: hr_utility.set_location(l_proc, 3);
2749: --
2750: end if;
2751: --
2752: end if;
2751: --
2752: end if;
2753: end if;
2754: --
2755: hr_utility.set_location(' Leaving:'|| l_proc, 4);
2756: exception
2757: when app_exception.application_exception then
2758: if hr_multi_message.exception_add
2759: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2758: if hr_multi_message.exception_add
2759: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2760: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2761: ) then
2762: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2763: raise;
2764: end if;
2765: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2766: --
2761: ) then
2762: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2763: raise;
2764: end if;
2765: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2766: --
2767: end chk_prob_unit_prob_period;
2768: --
2769: -- ---------------------------------------------------------------------------
2800: between effective_start_date
2801: and nvl(effective_end_date, hr_api.g_eot);
2802: --
2803: begin
2804: hr_utility.set_location('Entering:'|| l_proc, 10);
2805: --
2806: --
2807: -- Check mandatory parameters have been set
2808: --
2810: (p_api_name => l_proc
2811: ,p_argument => 'effective_date'
2812: ,p_argument_value => p_effective_date
2813: );
2814: hr_utility.set_location(l_proc, 20);
2815: --
2816: hr_api.mandatory_arg_error
2817: (p_api_name => l_proc
2818: ,p_argument => 'validation_start_date'
2817: (p_api_name => l_proc
2818: ,p_argument => 'validation_start_date'
2819: ,p_argument_value => p_validation_start_date
2820: );
2821: hr_utility.set_location(l_proc, 30);
2822: --
2823: hr_api.mandatory_arg_error
2824: (p_api_name => l_proc
2825: ,p_argument => 'validation_end_date'
2824: (p_api_name => l_proc
2825: ,p_argument => 'validation_end_date'
2826: ,p_argument_value => p_validation_end_date
2827: );
2828: hr_utility.set_location(l_proc, 40);
2829: --
2830: -- Only proceed with validation if :
2831: -- a) The current g_old_rec is current and
2832: -- b) The value for recruiter has changed
2834: l_api_updating := per_asg_shd.api_updating
2835: (p_assignment_id => p_assignment_id
2836: ,p_effective_date => p_effective_date
2837: ,p_object_version_number => p_object_version_number);
2838: hr_utility.set_location(l_proc, 50);
2839: --
2840: if ((l_api_updating and
2841: nvl(per_asg_shd.g_old_rec.recruiter_id, hr_api.g_number) <>
2842: nvl(p_recruiter_id, hr_api.g_number)) or
2840: if ((l_api_updating and
2841: nvl(per_asg_shd.g_old_rec.recruiter_id, hr_api.g_number) <>
2842: nvl(p_recruiter_id, hr_api.g_number)) or
2843: (NOT l_api_updating)) then
2844: hr_utility.set_location(l_proc, 60);
2845: --
2846: -- Check if recruiter is not null
2847: --
2848: if p_recruiter_id is not null then
2851: --
2852: if p_assignment_type in ('E','C','B')
2853: then
2854: --
2855: hr_utility.set_message(801, 'HR_51216_ASG_INV_ASG_TYP_REC');
2856: hr_multi_message.add
2857: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2858: );
2859: --
2857: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2858: );
2859: --
2860: end if;
2861: hr_utility.set_location(l_proc, 70);
2862: --
2863: -- Check that the recruiter is'nt the same person as the assignment
2864: -- person.
2865: --
2864: -- person.
2865: --
2866: If p_recruiter_id = p_person_id then
2867: --
2868: hr_utility.set_message(801, 'HR_51289_ASG_APL_EQUAL_RECRUIT');
2869: hr_multi_message.add
2870: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2871: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2872: );
2871: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2872: );
2873: --
2874: end if;
2875: hr_utility.set_location(l_proc, 80);
2876: --
2877: -- Check if the recruiter exists between the effective start date
2878: -- and effective end date of the assignment.
2879: --
2881: fetch csr_val_recruiter
2882: into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
2883: if csr_val_recruiter%notfound then
2884: close csr_val_recruiter;
2885: hr_utility.set_message(801, 'HR_51280_ASG_INV_RECRUIT_ID');
2886: hr_multi_message.add
2887: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2888: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2889: );
2891: else
2892: close csr_val_recruiter;
2893: end if;
2894: --
2895: hr_utility.set_location(l_proc, 90);
2896: --
2897: -- Check that the recruiter is an employee.
2898: --
2899: -- Bug 3190625
2900: -- Condition to check profile value also added
2901: if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
2902: ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
2903: nvl(l_current_npw_flag, 'N') = 'Y') ) then
2904: hr_utility.set_message(801, 'HR_51290_ASG_RECRUIT_NOT_EMP');
2905: hr_multi_message.add
2906: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID');
2907: end if;
2908: hr_utility.set_location(l_proc, 100);
2904: hr_utility.set_message(801, 'HR_51290_ASG_RECRUIT_NOT_EMP');
2905: hr_multi_message.add
2906: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID');
2907: end if;
2908: hr_utility.set_location(l_proc, 100);
2909: --
2910: -- Check that the recruiter is in the same business group
2911: -- as the business group of the applicant assignment.
2912: --
2913: If (p_business_group_id <> l_business_group_id AND
2914: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'N')
2915: then
2916: --
2917: hr_utility.set_message(801, 'HR_51284_ASG_INV_RECRUIT_BG');
2918: hr_multi_message.add
2919: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2920: );
2921: --
2919: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2920: );
2921: --
2922: end if;
2923: hr_utility.set_location(l_proc, 110);
2924: --
2925: end if;
2926: --
2927: end if;
2924: --
2925: end if;
2926: --
2927: end if;
2928: hr_utility.set_location(' Leaving:'||l_proc, 140);
2929: --
2930: end chk_recruiter_id;
2931: --
2932: -- ---------------------------------------------------------------------------
2958: between date_start
2959: and nvl(date_end, hr_api.g_eot);
2960: --
2961: begin
2962: hr_utility.set_location('Entering:'|| l_proc, 10);
2963: --
2964: -- Check mandatory parameters have been set
2965: --
2966: hr_api.mandatory_arg_error
2979: (p_api_name => l_proc
2980: ,p_argument => 'validation_end_date'
2981: ,p_argument_value => p_validation_end_date
2982: );
2983: hr_utility.set_location(l_proc, 20);
2984: --
2985: -- Only proceed with validation if :
2986: -- a) The current g_old_rec is current and
2987: -- b) The value for recruitment activity has changed
2990: (p_assignment_id => p_assignment_id
2991: ,p_effective_date => p_effective_date
2992: ,p_object_version_number => p_object_version_number
2993: );
2994: hr_utility.set_location(l_proc, 30);
2995: --
2996: if ((l_api_updating and
2997: nvl(per_asg_shd.g_old_rec.recruitment_activity_id, hr_api.g_number) <>
2998: nvl(p_recruitment_activity_id, hr_api.g_number)) or
2997: nvl(per_asg_shd.g_old_rec.recruitment_activity_id, hr_api.g_number) <>
2998: nvl(p_recruitment_activity_id, hr_api.g_number)) or
2999: (NOT l_api_updating))
3000: then
3001: hr_utility.set_location(l_proc, 40);
3002: --
3003: -- Check if recruitment activity is not null
3004: --
3005: if p_recruitment_activity_id is not null then
3007: -- Check that the assignment is not an applicant or offer assignment.
3008: --
3009: if p_assignment_type in ('E','C','B') then
3010: --
3011: hr_utility.set_message(801, 'HR_51223_ASG_INV_ASG_TYP_RCAT');
3012: hr_multi_message.add
3013: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3014: );
3015: --
3013: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3014: );
3015: --
3016: end if;
3017: hr_utility.set_location(l_proc, 50);
3018: --
3019: -- Check if the recruitment activity exists where the effective
3020: -- start date of the assignment is between the date start and
3021: -- date end of the recruitment activity.
3028: -- Do not throw an error for Offer Assignment.
3029: --
3030: if p_assignment_type <> 'O'
3031: then
3032: hr_utility.set_message(801, 'HR_51306_ASG_INV_REC_ACT');
3033: hr_multi_message.add
3034: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3035: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3036: );
3040: else
3041: close csr_val_rec_act_id;
3042: end if;
3043: --
3044: hr_utility.set_location(l_proc, 60);
3045: --
3046: -- Check that the recruitment activity is in the same business group
3047: -- as the business group of the assignment.
3048: --
3047: -- as the business group of the assignment.
3048: --
3049: If p_business_group_id <> l_business_group_id then
3050: --
3051: hr_utility.set_message(801, 'HR_51307_ASG_INV_REC_ACT_BG');
3052: hr_multi_message.add
3053: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3054: );
3055: --
3053: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3054: );
3055: --
3056: end if;
3057: hr_utility.set_location(l_proc, 70);
3058: --
3059: end if;
3060: --
3061: end if;
3059: end if;
3060: --
3061: end if;
3062: --
3063: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3064: end chk_recruitment_activity_id;
3065: --
3066: -- ---------------------------------------------------------------------------
3067: -- |-------------------------< chk_ref_int_del >-----------------------------|
3219: from hr_assignment_set_amendments
3220: where assignment_id = p_assignment_id);
3221: --
3222: begin
3223: hr_utility.set_location('Entering:'|| l_proc, 1);
3224: --
3225: -- Check that no child records exist for the
3226: -- assignment on per_events when the assignment is
3227: -- deleted
3229: open csr_per_events;
3230: fetch csr_per_events into l_exists;
3231: if csr_per_events%found then
3232: close csr_per_events;
3233: hr_utility.set_message(801,'HR_7400_ASG_NO_DEL_ASS_EVENTS');
3234: hr_utility.raise_error;
3235: end if;
3236: close csr_per_events;
3237: --
3230: fetch csr_per_events into l_exists;
3231: if csr_per_events%found then
3232: close csr_per_events;
3233: hr_utility.set_message(801,'HR_7400_ASG_NO_DEL_ASS_EVENTS');
3234: hr_utility.raise_error;
3235: end if;
3236: close csr_per_events;
3237: --
3238: hr_utility.set_location(l_proc, 2);
3234: hr_utility.raise_error;
3235: end if;
3236: close csr_per_events;
3237: --
3238: hr_utility.set_location(l_proc, 2);
3239: --
3240: -- Check that no child records exist for the
3241: -- assignment on per_letter_request_lines when
3242: -- the assignment is deleted
3245: /*open csr_per_lett_req_lines;
3246: fetch csr_per_lett_req_lines into l_exists;
3247: if csr_per_lett_req_lines%found then
3248: close csr_per_lett_req_lines;
3249: hr_utility.set_message(801,'HR_7401_ASG_NO_DEL_ASS_LET_REQ');
3250: hr_utility.raise_error;
3251: end if;
3252: close csr_per_lett_req_lines;*/
3253: -- End of 3096114
3246: fetch csr_per_lett_req_lines into l_exists;
3247: if csr_per_lett_req_lines%found then
3248: close csr_per_lett_req_lines;
3249: hr_utility.set_message(801,'HR_7401_ASG_NO_DEL_ASS_LET_REQ');
3250: hr_utility.raise_error;
3251: end if;
3252: close csr_per_lett_req_lines;*/
3253: -- End of 3096114
3254: --
3251: end if;
3252: close csr_per_lett_req_lines;*/
3253: -- End of 3096114
3254: --
3255: hr_utility.set_location(l_proc, 3);
3256: --
3257: -- Check that no child records exist for the
3258: -- assignment on pay_cost_allocations_f when
3259: -- the assignment is deleted
3261: open csr_pay_cost_allocations_f;
3262: fetch csr_pay_cost_allocations_f into l_exists;
3263: if csr_pay_cost_allocations_f%found then
3264: close csr_pay_cost_allocations_f;
3265: hr_utility.set_message(801,'HR_7402_ASG_NO_DEL_COST_ALLOCS');
3266: hr_utility.raise_error;
3267: end if;
3268: close csr_pay_cost_allocations_f;
3269: --
3262: fetch csr_pay_cost_allocations_f into l_exists;
3263: if csr_pay_cost_allocations_f%found then
3264: close csr_pay_cost_allocations_f;
3265: hr_utility.set_message(801,'HR_7402_ASG_NO_DEL_COST_ALLOCS');
3266: hr_utility.raise_error;
3267: end if;
3268: close csr_pay_cost_allocations_f;
3269: --
3270: hr_utility.set_location(l_proc, 4);
3266: hr_utility.raise_error;
3267: end if;
3268: close csr_pay_cost_allocations_f;
3269: --
3270: hr_utility.set_location(l_proc, 4);
3271: --
3272: -- Check that no child records exist for the
3273: -- assignment on pay_personal_payment_methods when
3274: -- the assignment is deleted
3276: open csr_pay_pers_payment_methods;
3277: fetch csr_pay_pers_payment_methods into l_exists;
3278: if csr_pay_pers_payment_methods%found then
3279: close csr_pay_pers_payment_methods;
3280: hr_utility.set_message(801,'HR_7404_ASG_NO_DEL_PER_PAY_MET');
3281: hr_utility.raise_error;
3282: end if;
3283: close csr_pay_pers_payment_methods;
3284: --
3277: fetch csr_pay_pers_payment_methods into l_exists;
3278: if csr_pay_pers_payment_methods%found then
3279: close csr_pay_pers_payment_methods;
3280: hr_utility.set_message(801,'HR_7404_ASG_NO_DEL_PER_PAY_MET');
3281: hr_utility.raise_error;
3282: end if;
3283: close csr_pay_pers_payment_methods;
3284: --
3285: hr_utility.set_location(l_proc, 5);
3281: hr_utility.raise_error;
3282: end if;
3283: close csr_pay_pers_payment_methods;
3284: --
3285: hr_utility.set_location(l_proc, 5);
3286: --
3287: -- Check that no child records exist for the
3288: -- assignment on pay_payroll_actions when
3289: -- the assignment is deleted
3291: open csr_pay_assignment_actions;
3292: fetch csr_pay_assignment_actions into l_exists;
3293: if csr_pay_assignment_actions%found then
3294: close csr_pay_assignment_actions;
3295: hr_utility.set_message(801,'HR_7403_ASG_NO_DEL_PAYROLL_ACT');
3296: hr_utility.raise_error;
3297: end if;
3298: close csr_pay_assignment_actions;
3299: --
3292: fetch csr_pay_assignment_actions into l_exists;
3293: if csr_pay_assignment_actions%found then
3294: close csr_pay_assignment_actions;
3295: hr_utility.set_message(801,'HR_7403_ASG_NO_DEL_PAYROLL_ACT');
3296: hr_utility.raise_error;
3297: end if;
3298: close csr_pay_assignment_actions;
3299: --
3300: hr_utility.set_location(l_proc, 6);
3296: hr_utility.raise_error;
3297: end if;
3298: close csr_pay_assignment_actions;
3299: --
3300: hr_utility.set_location(l_proc, 6);
3301: --
3302: -- Check that no child records exist for the
3303: -- assignment on per_secondary_ass_statuses when
3304: -- the assignment is deleted
3306: open csr_per_secondary_ass_stat;
3307: fetch csr_per_secondary_ass_stat into l_exists;
3308: if csr_per_secondary_ass_stat%found then
3309: close csr_per_secondary_ass_stat;
3310: hr_utility.set_message(801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
3311: hr_utility.raise_error;
3312: end if;
3313: close csr_per_secondary_ass_stat;
3314: --
3307: fetch csr_per_secondary_ass_stat into l_exists;
3308: if csr_per_secondary_ass_stat%found then
3309: close csr_per_secondary_ass_stat;
3310: hr_utility.set_message(801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
3311: hr_utility.raise_error;
3312: end if;
3313: close csr_per_secondary_ass_stat;
3314: --
3315: hr_utility.set_location(l_proc, 7);
3311: hr_utility.raise_error;
3312: end if;
3313: close csr_per_secondary_ass_stat;
3314: --
3315: hr_utility.set_location(l_proc, 7);
3316: --
3317: -- Check that no child records exist for the
3318: -- assignment on per_cobra_cov_enrollments
3319: -- when the assignment is deleted
3321: open csr_per_cobra_cov_enrol;
3322: fetch csr_per_cobra_cov_enrol into l_exists;
3323: if csr_per_cobra_cov_enrol%found then
3324: close csr_per_cobra_cov_enrol;
3325: hr_utility.set_message(801,'HR_7405_ASG_NO_DEL_COB_COV_ENR');
3326: hr_utility.raise_error;
3327: end if;
3328: close csr_per_cobra_cov_enrol;
3329: --
3322: fetch csr_per_cobra_cov_enrol into l_exists;
3323: if csr_per_cobra_cov_enrol%found then
3324: close csr_per_cobra_cov_enrol;
3325: hr_utility.set_message(801,'HR_7405_ASG_NO_DEL_COB_COV_ENR');
3326: hr_utility.raise_error;
3327: end if;
3328: close csr_per_cobra_cov_enrol;
3329: --
3330: hr_utility.set_location(l_proc, 8);
3326: hr_utility.raise_error;
3327: end if;
3328: close csr_per_cobra_cov_enrol;
3329: --
3330: hr_utility.set_location(l_proc, 8);
3331: --
3332: -- Check that no child records exist for the
3333: -- assignment on per_cobra_coverage_benefits_f
3334: -- when the assignment is deleted
3336: open csr_per_cobra_cov_bens;
3337: fetch csr_per_cobra_cov_bens into l_exists;
3338: if csr_per_cobra_cov_bens%found then
3339: close csr_per_cobra_cov_bens;
3340: hr_utility.set_message(801,'HR_7406_ASG_NO_DEL_COB_COV_BEN');
3341: hr_utility.raise_error;
3342: end if;
3343: close csr_per_cobra_cov_bens;
3344: --
3337: fetch csr_per_cobra_cov_bens into l_exists;
3338: if csr_per_cobra_cov_bens%found then
3339: close csr_per_cobra_cov_bens;
3340: hr_utility.set_message(801,'HR_7406_ASG_NO_DEL_COB_COV_BEN');
3341: hr_utility.raise_error;
3342: end if;
3343: close csr_per_cobra_cov_bens;
3344: --
3345: hr_utility.set_location(l_proc, 9);
3341: hr_utility.raise_error;
3342: end if;
3343: close csr_per_cobra_cov_bens;
3344: --
3345: hr_utility.set_location(l_proc, 9);
3346: --
3347: -- Check that no child records exist for the
3348: -- assignment on per_assignment_extra_info when
3349: -- the assignment is deleted
3354: open csr_per_ass_extra_info;
3355: fetch csr_per_ass_extra_info into l_exists;
3356: if csr_per_ass_extra_info%found then
3357: close csr_per_ass_extra_info;
3358: hr_utility.set_message(801,'HR_7409_ASG_NO_DEL_EXTR_INFO');
3359: hr_utility.raise_error;
3360: end if;
3361: close csr_per_ass_extra_info;
3362: end if;
3355: fetch csr_per_ass_extra_info into l_exists;
3356: if csr_per_ass_extra_info%found then
3357: close csr_per_ass_extra_info;
3358: hr_utility.set_message(801,'HR_7409_ASG_NO_DEL_EXTR_INFO');
3359: hr_utility.raise_error;
3360: end if;
3361: close csr_per_ass_extra_info;
3362: end if;
3363: --
3360: end if;
3361: close csr_per_ass_extra_info;
3362: end if;
3363: --
3364: hr_utility.set_location(l_proc, 10);
3365: --
3366: -- Check that no child records exist for the
3367: -- assignment on hr_assignment_set_amendments
3368: -- when the assignment is deleted
3373: open csr_hr_ass_set_amend;
3374: fetch csr_hr_ass_set_amend into l_exists;
3375: if csr_hr_ass_set_amend%found then
3376: close csr_hr_ass_set_amend;
3377: hr_utility.set_message(801,'HR_7410_ASG_NO_DEL_ASS_SET_AMD');
3378: hr_utility.raise_error;
3379: end if;
3380: close csr_hr_ass_set_amend;
3381: end if;
3374: fetch csr_hr_ass_set_amend into l_exists;
3375: if csr_hr_ass_set_amend%found then
3376: close csr_hr_ass_set_amend;
3377: hr_utility.set_message(801,'HR_7410_ASG_NO_DEL_ASS_SET_AMD');
3378: hr_utility.raise_error;
3379: end if;
3380: close csr_hr_ass_set_amend;
3381: end if;
3382: --
3379: end if;
3380: close csr_hr_ass_set_amend;
3381: end if;
3382: --
3383: hr_utility.set_location(' Leaving:'|| l_proc, 11);
3384: end chk_ref_int_del;
3385: --
3386: -- ---------------------------------------------------------------------------
3387: -- |---------------------< chk_sal_review_period_freq >----------------------|
3402: l_proc varchar2(72) := g_package||'chk_sal_review_period_freq';
3403: l_api_updating boolean;
3404: --
3405: begin
3406: hr_utility.set_location('Entering:'|| l_proc, 10);
3407: --
3408: -- Check mandatory parameters have been set
3409: --
3410: hr_api.mandatory_arg_error
3423: (p_api_name => l_proc
3424: ,p_argument => 'validation_end_date'
3425: ,p_argument_value => p_validation_end_date
3426: );
3427: hr_utility.set_location(l_proc, 20);
3428: --
3429: -- Only proceed with validation if :
3430: -- a) The current g_old_rec is current and
3431: -- b) The value for salary review period frequency has changed
3434: (p_assignment_id => p_assignment_id
3435: ,p_effective_date => p_effective_date
3436: ,p_object_version_number => p_object_version_number
3437: );
3438: hr_utility.set_location(l_proc, 30);
3439: --
3440: if ((l_api_updating and
3441: nvl(per_asg_shd.g_old_rec.sal_review_period_frequency,
3442: hr_api.g_varchar2) <> nvl(p_sal_review_period_frequency,
3443: hr_api.g_varchar2))
3444: or
3445: (NOT l_api_updating))
3446: then
3447: hr_utility.set_location(l_proc, 40);
3448: --
3449: -- Check if sal review period frequency is not null
3450: --
3451: if p_sal_review_period_frequency is not null then
3454: -- or benefit or offer assignment.
3455: --
3456: if p_assignment_type not in ('E','A','B','O') then
3457: --
3458: hr_utility.set_message(801, 'HR_51181_ASG_INV_ASG_TYP_SRPF');
3459: hr_multi_message.add
3460: (p_associated_column1 =>
3461: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3462: );
3461: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3462: );
3463: --
3464: end if;
3465: hr_utility.set_location(l_proc, 50);
3466: --
3467: -- Check that the salary review period frequency exists in
3468: -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
3469: -- flag set to 'Y' and that the effective start date of the
3478: ,p_lookup_code => p_sal_review_period_frequency
3479: )
3480: then
3481: --
3482: hr_utility.set_message(801, 'HR_51164_ASG_INV_SRP_FREQ');
3483: hr_multi_message.add
3484: (p_associated_column1 =>
3485: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3486: );
3485: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3486: );
3487: --
3488: end if;
3489: hr_utility.set_location(l_proc, 60);
3490: --
3491: end if;
3492: hr_utility.set_location(l_proc, 70);
3493: --
3488: end if;
3489: hr_utility.set_location(l_proc, 60);
3490: --
3491: end if;
3492: hr_utility.set_location(l_proc, 70);
3493: --
3494: end if;
3495: hr_utility.set_location(' Leaving:'|| l_proc, 80);
3496: end chk_sal_review_period_freq;
3491: end if;
3492: hr_utility.set_location(l_proc, 70);
3493: --
3494: end if;
3495: hr_utility.set_location(' Leaving:'|| l_proc, 80);
3496: end chk_sal_review_period_freq;
3497: --
3498: -- ---------------------------------------------------------------------------
3499: -- |-----------------------< chk_sal_review_period >------------------------|
3511: l_proc varchar2(72) := g_package||'chk_sal_review_period';
3512: l_api_updating boolean;
3513: --
3514: begin
3515: hr_utility.set_location('Entering:'|| l_proc, 1);
3516: --
3517: -- Check mandatory parameters have been set
3518: --
3519: hr_api.mandatory_arg_error
3530: (p_assignment_id => p_assignment_id
3531: ,p_effective_date => p_effective_date
3532: ,p_object_version_number => p_object_version_number);
3533: --
3534: hr_utility.set_location(l_proc, 2);
3535: --
3536: if ((l_api_updating and
3537: nvl(per_asg_shd.g_old_rec.sal_review_period,
3538: hr_api.g_number) <> nvl(p_sal_review_period,
3537: nvl(per_asg_shd.g_old_rec.sal_review_period,
3538: hr_api.g_number) <> nvl(p_sal_review_period,
3539: hr_api.g_number)) or (NOT l_api_updating)) then
3540: --
3541: hr_utility.set_location(l_proc, 3);
3542: --
3543: -- Check if sal review period is not null
3544: --
3545: if p_sal_review_period is not null then
3549: --
3550: if p_assignment_type not in ('E','A','B','O') then
3551:
3552: --
3553: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3554: hr_utility.raise_error;
3555: end if;
3556: hr_utility.set_location(l_proc, 4);
3557: --
3550: if p_assignment_type not in ('E','A','B','O') then
3551:
3552: --
3553: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3554: hr_utility.raise_error;
3555: end if;
3556: hr_utility.set_location(l_proc, 4);
3557: --
3558: end if;
3552: --
3553: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3554: hr_utility.raise_error;
3555: end if;
3556: hr_utility.set_location(l_proc, 4);
3557: --
3558: end if;
3559: --
3560: end if;
3558: end if;
3559: --
3560: end if;
3561: --
3562: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3563: exception
3564: when app_exception.application_exception then
3565: if hr_multi_message.exception_add
3566: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3564: when app_exception.application_exception then
3565: if hr_multi_message.exception_add
3566: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3567: ) then
3568: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3569: raise;
3570: end if;
3571: hr_utility.set_location(' Leaving:'|| l_proc, 7);
3572: end chk_sal_review_period;
3567: ) then
3568: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3569: raise;
3570: end if;
3571: hr_utility.set_location(' Leaving:'|| l_proc, 7);
3572: end chk_sal_review_period;
3573: --
3574: -- ---------------------------------------------------------------------------
3575: -- |---------------------< chk_sal_rp_freq_sal_rp >--------------------------|
3587: l_proc varchar2(72):= g_package||'chk_sal_rp_freq_sal_rp';
3588: l_api_updating boolean;
3589: --
3590: begin
3591: hr_utility.set_location('Entering:'|| l_proc, 1);
3592: --
3593: if hr_multi_message.no_exclusive_error
3594: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3595: ,p_check_column2 =>
3621: hr_api.g_number) <> nvl(p_sal_review_period, hr_api.g_number))
3622: or
3623: (NOT l_api_updating)) then
3624: --
3625: hr_utility.set_location(l_proc, 2);
3626: --
3627: -- Check if sal review period frequency or sal review period is not null.
3628: --
3629: if p_sal_review_period_frequency is not null
3627: -- Check if sal review period frequency or sal review period is not null.
3628: --
3629: if p_sal_review_period_frequency is not null
3630: or p_sal_review_period is not null then
3631: hr_utility.set_location(l_proc, 3);
3632: --
3633: -- Check if sal review period frequency or sal review period are null.
3634: --
3635: if p_sal_review_period_frequency is null
3634: --
3635: if p_sal_review_period_frequency is null
3636: or p_sal_review_period is null then
3637: --
3638: hr_utility.set_message(801, 'HR_51165_ASG_INV_SRPF_SRP_COMB');
3639: hr_utility.raise_error;
3640: --
3641: end if;
3642: --
3635: if p_sal_review_period_frequency is null
3636: or p_sal_review_period is null then
3637: --
3638: hr_utility.set_message(801, 'HR_51165_ASG_INV_SRPF_SRP_COMB');
3639: hr_utility.raise_error;
3640: --
3641: end if;
3642: --
3643: end if;
3644: --
3645: end if;
3646: end if;
3647: --
3648: hr_utility.set_location(' Leaving:'|| l_proc, 4);
3649: exception
3650: when app_exception.application_exception then
3651: if hr_multi_message.exception_add
3652: (p_associated_column1 =>
3653: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3654: ,p_associated_column2 =>
3655: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3656: ) then
3657: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3658: raise;
3659: end if;
3660: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3661: end chk_sal_rp_freq_sal_rp;
3656: ) then
3657: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3658: raise;
3659: end if;
3660: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3661: end chk_sal_rp_freq_sal_rp;
3662: --
3663: -- ---------------------------------------------------------------------------
3664: -- |-----------------------< chk_set_of_books_id >---------------------------|
3690: where set_of_books_id = p_set_of_books_id
3691: and business_group_id = p_business_group_id;
3692: --
3693: begin
3694: hr_utility.set_location('Entering:'|| l_proc, 10);
3695: --
3696: -- Check mandatory parameters have been set
3697: --
3698: hr_api.mandatory_arg_error
3699: (p_api_name => l_proc
3700: ,p_argument => 'effective_date'
3701: ,p_argument_value => p_effective_date
3702: );
3703: hr_utility.set_location(l_proc, 20);
3704: --
3705: -- Only proceed with validation if :
3706: -- a) The current g_old_rec is current and
3707: -- b) The value for set_of_books_id has changed
3710: (p_assignment_id => p_assignment_id
3711: ,p_effective_date => p_effective_date
3712: ,p_object_version_number => p_object_version_number
3713: );
3714: hr_utility.set_location(l_proc, 30);
3715: --
3716: if ((l_api_updating and
3717: nvl(per_asg_shd.g_old_rec.set_of_books_id,
3718: hr_api.g_number) <> nvl(p_set_of_books_id, hr_api.g_number)) or
3717: nvl(per_asg_shd.g_old_rec.set_of_books_id,
3718: hr_api.g_number) <> nvl(p_set_of_books_id, hr_api.g_number)) or
3719: (NOT l_api_updating))
3720: then
3721: hr_utility.set_location(l_proc, 40);
3722: --
3723: -- Check if set of books is set
3724: --
3725: if p_set_of_books_id is not null then
3728: -- or contact or offer assignment.
3729: --
3730: if p_assignment_type not in ('E','A','C','O') then
3731: --
3732: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3733: hr_utility.raise_error;
3734: --
3735: end if;
3736: hr_utility.set_location(l_proc, 50);
3729: --
3730: if p_assignment_type not in ('E','A','C','O') then
3731: --
3732: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3733: hr_utility.raise_error;
3734: --
3735: end if;
3736: hr_utility.set_location(l_proc, 50);
3737: --
3732: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3733: hr_utility.raise_error;
3734: --
3735: end if;
3736: hr_utility.set_location(l_proc, 50);
3737: --
3738: -- Check that the set of books exists in GL_SETS_OF_BOOKS.
3739: --
3740: open csr_valid_sob;
3740: open csr_valid_sob;
3741: fetch csr_valid_sob into l_exists;
3742: if csr_valid_sob%notfound then
3743: close csr_valid_sob;
3744: hr_utility.set_message(801, 'HR_51160_ASG_INV_SET_OF_BOOKS');
3745: hr_utility.raise_error;
3746: --
3747: end if;
3748: close csr_valid_sob;
3741: fetch csr_valid_sob into l_exists;
3742: if csr_valid_sob%notfound then
3743: close csr_valid_sob;
3744: hr_utility.set_message(801, 'HR_51160_ASG_INV_SET_OF_BOOKS');
3745: hr_utility.raise_error;
3746: --
3747: end if;
3748: close csr_valid_sob;
3749: hr_utility.set_location(l_proc, 60);
3745: hr_utility.raise_error;
3746: --
3747: end if;
3748: close csr_valid_sob;
3749: hr_utility.set_location(l_proc, 60);
3750: --
3751: -- Check that the set of books exists in
3752: -- FINANCIALS_SYSTEM_PARAMS_ALL for the assignment business
3753: -- group.
3755: open csr_valid_fsp_bg;
3756: fetch csr_valid_fsp_bg into l_exists;
3757: if csr_valid_fsp_bg%notfound then
3758: close csr_valid_fsp_bg;
3759: hr_utility.set_message(801, 'HR_51316_ASG_INV_FSP_SOB_BG');
3760: hr_utility.raise_error;
3761: --
3762: end if;
3763: close csr_valid_fsp_bg;
3756: fetch csr_valid_fsp_bg into l_exists;
3757: if csr_valid_fsp_bg%notfound then
3758: close csr_valid_fsp_bg;
3759: hr_utility.set_message(801, 'HR_51316_ASG_INV_FSP_SOB_BG');
3760: hr_utility.raise_error;
3761: --
3762: end if;
3763: close csr_valid_fsp_bg;
3764: hr_utility.set_location(l_proc, 70);
3760: hr_utility.raise_error;
3761: --
3762: end if;
3763: close csr_valid_fsp_bg;
3764: hr_utility.set_location(l_proc, 70);
3765: --
3766: end if;
3767: hr_utility.set_location(l_proc, 80);
3768: --
3763: close csr_valid_fsp_bg;
3764: hr_utility.set_location(l_proc, 70);
3765: --
3766: end if;
3767: hr_utility.set_location(l_proc, 80);
3768: --
3769: end if;
3770: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3771: exception
3766: end if;
3767: hr_utility.set_location(l_proc, 80);
3768: --
3769: end if;
3770: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3771: exception
3772: when app_exception.application_exception then
3773: if hr_multi_message.exception_add
3774: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID'
3772: when app_exception.application_exception then
3773: if hr_multi_message.exception_add
3774: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID'
3775: ) then
3776: hr_utility.set_location(' Leaving:'|| l_proc, 110);
3777: raise;
3778: end if;
3779: hr_utility.set_location(' Leaving:'|| l_proc, 120);
3780: end chk_set_of_books_id;
3775: ) then
3776: hr_utility.set_location(' Leaving:'|| l_proc, 110);
3777: raise;
3778: end if;
3779: hr_utility.set_location(' Leaving:'|| l_proc, 120);
3780: end chk_set_of_books_id;
3781: --
3782: -- ---------------------------------------------------------------------------
3783: -- |--------------------< chk_soft_coding_keyflex_id >-----------------------|
3827: where message_name = p_message_name
3828: and application_id = 801;
3829: --
3830: begin
3831: hr_utility.set_location('Entering:'|| l_proc, 10);
3832: --
3833: -- Check mandatory parameters have been set
3834: --
3835: hr_api.mandatory_arg_error
3836: (p_api_name => l_proc
3837: ,p_argument => 'effective_date'
3838: ,p_argument_value => p_effective_date
3839: );
3840: hr_utility.set_location(l_proc, 20);
3841: --
3842: -- Only proceed with validation if :
3843: -- a) The current g_old_rec is current and
3844: -- b) The value for soft coding keyflex has changed
3848: (p_assignment_id => p_assignment_id
3849: ,p_effective_date => p_effective_date
3850: ,p_object_version_number => p_object_version_number
3851: );
3852: hr_utility.set_location(l_proc, 30);
3853: --
3854: if ((l_api_updating and
3855: nvl(per_asg_shd.g_old_rec.soft_coding_keyflex_id, hr_api.g_number) <>
3856: nvl(p_soft_coding_keyflex_id, hr_api.g_number)) or
3863: -- ****** End new code for bug #2182184 ****************************
3864: --
3865: (NOT l_api_updating)) then
3866: --
3867: hr_utility.set_location(l_proc, 40);
3868: --
3869: if p_soft_coding_keyflex_id is not null then
3870: --
3871: -- Check that the assignment is an employee assignment.
3874: --
3875: --
3876: if p_assignment_type not in ('E','A','B','C','O') then
3877: --
3878: hr_utility.set_message(801, 'HR_51227_ASG_INV_ASG_TYP_SCF');
3879: hr_multi_message.add
3880: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3881: );
3882: --
3880: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3881: );
3882: --
3883: end if;
3884: hr_utility.set_location(l_proc, 50);
3885: --
3886: -- Check that soft_coding_keyflex_id exists on
3887: -- hr_soft_coding_keyflex
3888: --
3889: open csr_valid_keyflex;
3890: fetch csr_valid_keyflex into l_exists;
3891: if csr_valid_keyflex%notfound then
3892: close csr_valid_keyflex;
3893: hr_utility.set_message(801, 'HR_7383_ASG_INV_KEYFLEX');
3894: hr_multi_message.add
3895: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3896: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3897: );
3897: );
3898: else
3899: close csr_valid_keyflex;
3900: end if;
3901: hr_utility.set_location(l_proc, 60);
3902: else
3903: --
3904: -- Check that for relevant legislations SCL is mandatory,
3905: -- when payroll_id is populated #909279
3903: --
3904: -- Check that for relevant legislations SCL is mandatory,
3905: -- when payroll_id is populated #909279
3906: --
3907: hr_utility.set_location(l_proc, 45);
3908: if p_payroll_id is not null and
3909: p_assignment_type = 'E' then
3910: open csr_bg;
3911: fetch csr_bg into l_legislation_code;
3910: open csr_bg;
3911: fetch csr_bg into l_legislation_code;
3912: close csr_bg;
3913: --
3914: hr_utility.set_location(l_proc, 55);
3915: open csr_pay_legislation_rules;
3916: fetch csr_pay_legislation_rules into l_exists;
3917: if csr_pay_legislation_rules%found then
3918: close csr_pay_legislation_rules;
3916: fetch csr_pay_legislation_rules into l_exists;
3917: if csr_pay_legislation_rules%found then
3918: close csr_pay_legislation_rules;
3919: if l_legislation_code = 'US' then
3920: hr_utility.set_message(801, 'HR_50001_EMP_ASS_NO_GRE');
3921: else
3922: open csr_tax_unit_message('HR_INV_LEG_ENT_'||l_legislation_code);
3923: fetch csr_tax_unit_message into l_exists;
3924:
3922: open csr_tax_unit_message('HR_INV_LEG_ENT_'||l_legislation_code);
3923: fetch csr_tax_unit_message into l_exists;
3924:
3925: if csr_tax_unit_message%found then
3926: hr_utility.set_message(801, 'HR_INV_LEG_ENT_'||l_legislation_code);
3927: else
3928: hr_utility.set_message(801, 'HR_34024_IP_INV_LEG_ENT');
3929: end if;
3930: close csr_tax_unit_message;
3924:
3925: if csr_tax_unit_message%found then
3926: hr_utility.set_message(801, 'HR_INV_LEG_ENT_'||l_legislation_code);
3927: else
3928: hr_utility.set_message(801, 'HR_34024_IP_INV_LEG_ENT');
3929: end if;
3930: close csr_tax_unit_message;
3931: end if;
3932: hr_multi_message.add
3934: );
3935: else
3936: close csr_pay_legislation_rules;
3937: end if;
3938: hr_utility.set_location(l_proc, 65);
3939: end if;
3940: end if;
3941: --
3942: end if;
3940: end if;
3941: --
3942: end if;
3943: --
3944: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3945: end chk_soft_coding_keyflex_id;
3946: --
3947: -- ---------------------------------------------------------------------------
3948: -- |--------------------< chk_source_organization_id >-----------------------|
3972: between date_from
3973: and nvl(date_to, hr_api.g_eot);
3974: --
3975: begin
3976: hr_utility.set_location('Entering:'|| l_proc, 10);
3977: --
3978: -- Check mandatory parameters have been set
3979: --
3980: hr_api.mandatory_arg_error
3993: (p_api_name => l_proc
3994: ,p_argument => 'validation_end_date'
3995: ,p_argument_value => p_validation_end_date
3996: );
3997: hr_utility.set_location(l_proc, 20);
3998: --
3999: -- Only proceed with validation if :
4000: -- a) The current g_old_rec is current and
4001: -- b) The value for source organization has changed
4004: (p_assignment_id => p_assignment_id
4005: ,p_effective_date => p_effective_date
4006: ,p_object_version_number => p_object_version_number
4007: );
4008: hr_utility.set_location(l_proc, 30);
4009: --
4010: if ((l_api_updating
4011: and nvl(per_asg_shd.g_old_rec.source_organization_id, hr_api.g_number)
4012: <> nvl(p_source_organization_id, hr_api.g_number))
4012: <> nvl(p_source_organization_id, hr_api.g_number))
4013: or
4014: (NOT l_api_updating))
4015: then
4016: hr_utility.set_location(l_proc, 40);
4017: --
4018: -- Check if source organization is set
4019: --
4020: if p_source_organization_id is not null then
4026: -- Check if the employee assignment is being updated
4027: --
4028: If l_api_updating then
4029: --
4030: hr_utility.set_message(801, 'HR_51220_ASG_INV_EASG_U_SORG');
4031: hr_multi_message.add
4032: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4033: );
4034: --
4033: );
4034: --
4035: else -- inserting an employee assignment
4036: --
4037: hr_utility.set_message(801, 'HR_51219_ASG_INV_EASG_I_SORG');
4038: hr_multi_message.add
4039: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4040: );
4041: --
4039: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4040: );
4041: --
4042: end if;
4043: hr_utility.set_location(l_proc, 60);
4044: --
4045: end if;
4046: hr_utility.set_location(l_proc, 70);
4047: --
4042: end if;
4043: hr_utility.set_location(l_proc, 60);
4044: --
4045: end if;
4046: hr_utility.set_location(l_proc, 70);
4047: --
4048: -- Check if the source organization exists where the effective
4049: -- start date of the assignment is between the date from and
4050: -- date to of the source organization.
4052: open csr_val_source_org_id;
4053: fetch csr_val_source_org_id into l_business_group_id;
4054: if csr_val_source_org_id%notfound then
4055: close csr_val_source_org_id;
4056: hr_utility.set_message(801, 'HR_51308_ASG_INV_SOURCE_ORG');
4057: hr_multi_message.add
4058: (p_associated_column1 =>
4059: 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4060: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4062: --
4063: else
4064: close csr_val_source_org_id;
4065: end if;
4066: hr_utility.set_location(l_proc, 80);
4067: --
4068: -- Check that the source organization is in the same business group
4069: -- as the business group of the assignment.
4070: --
4069: -- as the business group of the assignment.
4070: --
4071: If p_business_group_id <> l_business_group_id then
4072: --
4073: hr_utility.set_message(801, 'HR_51309_ASG_INV_SOURCE_ORG_BG');
4074: hr_multi_message.add
4075: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4076: );
4077: --
4075: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4076: );
4077: --
4078: end if;
4079: hr_utility.set_location(l_proc, 90);
4080: --
4081: end if;
4082: hr_utility.set_location(l_proc, 100);
4083: --
4078: end if;
4079: hr_utility.set_location(l_proc, 90);
4080: --
4081: end if;
4082: hr_utility.set_location(l_proc, 100);
4083: --
4084: end if;
4085: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4086: end chk_source_organization_id;
4081: end if;
4082: hr_utility.set_location(l_proc, 100);
4083: --
4084: end if;
4085: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4086: end chk_source_organization_id;
4087: --
4088: -- ---------------------------------------------------------------------------
4089: -- |------------------------< chk_source_type >------------------------------|
4109: from per_recruitment_activities
4110: where recruitment_activity_id = p_recruitment_activity_id;
4111: --
4112: begin
4113: hr_utility.set_location('Entering:'|| l_proc, 10);
4114: --
4115: -- Check mandatory parameters have been set
4116: --
4117: hr_api.mandatory_arg_error
4129: (p_api_name => l_proc
4130: ,p_argument => 'validation_end_date'
4131: ,p_argument_value => p_validation_end_date
4132: );
4133: hr_utility.set_location(l_proc, 20);
4134: --
4135: -- Only proceed with validation if :
4136: -- a) The current g_old_rec is current and
4137: -- b) The value for source type has changed
4140: (p_assignment_id => p_assignment_id
4141: ,p_effective_date => p_effective_date
4142: ,p_object_version_number => p_object_version_number
4143: );
4144: hr_utility.set_location(l_proc, 30);
4145: --
4146: if ((l_api_updating and
4147: nvl(per_asg_shd.g_old_rec.source_type, hr_api.g_varchar2) <>
4148: nvl(p_source_type, hr_api.g_varchar2))
4147: nvl(per_asg_shd.g_old_rec.source_type, hr_api.g_varchar2) <>
4148: nvl(p_source_type, hr_api.g_varchar2))
4149: or (NOT l_api_updating))
4150: then
4151: hr_utility.set_location(l_proc, 40);
4152: --
4153: -- Check if source type is set
4154: --
4155: if p_source_type is not null then
4167: ,p_lookup_code => p_source_type
4168: )
4169: then
4170: --
4171: hr_utility.set_message(801, 'HR_51162_ASG_INV_SOURCE_TYPE');
4172: hr_multi_message.add
4173: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4174: );
4175: --
4173: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4174: );
4175: --
4176: end if;
4177: hr_utility.set_location(l_proc, 50);
4178: --
4179: -- Check if recruitment activity is set
4180: --
4181: If p_recruitment_activity_id is not null then
4185: --
4186: open csr_get_rec_act_type;
4187: fetch csr_get_rec_act_type into l_rec_act_type;
4188: close csr_get_rec_act_type;
4189: hr_utility.set_location(l_proc, 60);
4190: --
4191: If p_source_type <> nvl(l_rec_act_type, hr_api.g_varchar2) then
4192: --
4193: hr_utility.set_message(801, 'HR_51325_ASG_INV_SOU_TYP_RAT');
4189: hr_utility.set_location(l_proc, 60);
4190: --
4191: If p_source_type <> nvl(l_rec_act_type, hr_api.g_varchar2) then
4192: --
4193: hr_utility.set_message(801, 'HR_51325_ASG_INV_SOU_TYP_RAT');
4194: hr_multi_message.add
4195: (p_associated_column1 =>
4196: 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
4197: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4197: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4198: );
4199: --
4200: end if;
4201: hr_utility.set_location(l_proc, 70);
4202: --
4203: end if;
4204: hr_utility.set_location(l_proc, 80);
4205: --
4200: end if;
4201: hr_utility.set_location(l_proc, 70);
4202: --
4203: end if;
4204: hr_utility.set_location(l_proc, 80);
4205: --
4206: end if;
4207: hr_utility.set_location(l_proc, 90);
4208: --
4203: end if;
4204: hr_utility.set_location(l_proc, 80);
4205: --
4206: end if;
4207: hr_utility.set_location(l_proc, 90);
4208: --
4209: end if;
4210: --
4211: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4207: hr_utility.set_location(l_proc, 90);
4208: --
4209: end if;
4210: --
4211: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4212: end chk_source_type;
4213: --
4214: -- ---------------------------------------------------------------------------
4215: -- |-------------------< chk_special_ceiling_step_id >-----------------------|
4287: and psps.effective_start_date between pgs.effective_start_date
4288: and pgs.effective_end_date);
4289: --
4290: begin
4291: hr_utility.set_location('Entering:'|| l_proc, 10);
4292: --
4293: --
4294: -- Check mandatory parameters have been set
4295: --
4315: (p_api_name => l_proc
4316: ,p_argument => 'business_group_id'
4317: ,p_argument_value => p_business_group_id
4318: );
4319: hr_utility.set_location(l_proc, 20);
4320: --
4321: -- Only proceed with validation if :
4322: -- a) The current g_old_rec is current and
4323: -- b) The value for special ceiling step has changed
4326: (p_assignment_id => p_assignment_id
4327: ,p_effective_date => p_effective_date
4328: ,p_object_version_number => p_object_version_number
4329: );
4330: hr_utility.set_location(l_proc, 30);
4331: --
4332: if ((l_api_updating and
4333: nvl(per_asg_shd.g_old_rec.special_ceiling_step_id, hr_api.g_number) <>
4334: nvl(p_special_ceiling_step_id, hr_api.g_number)) or
4332: if ((l_api_updating and
4333: nvl(per_asg_shd.g_old_rec.special_ceiling_step_id, hr_api.g_number) <>
4334: nvl(p_special_ceiling_step_id, hr_api.g_number)) or
4335: (NOT l_api_updating)) then
4336: hr_utility.set_location(l_proc, 40);
4337: --
4338: if p_special_ceiling_step_id is not null then
4339: --
4340: -- Check that the assignment is an employee,applicant or benefits
4341: -- assignment.
4342: --
4343: if p_assignment_type not in ('E','A','B','O') then
4344: --
4345: hr_utility.set_message(801, 'HR_51225_ASG_INV_ASG_TYP_SCS');
4346: hr_multi_message.add
4347: (p_associated_column1 =>
4348: 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4349: );
4348: 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4349: );
4350: --
4351: end if;
4352: hr_utility.set_location(l_proc, 50);
4353: --
4354: -- Check that special_ceiling_step_id exists and is date effective
4355: -- per_grade_spines_f
4356: --
4357: open csr_valid_step;
4358: fetch csr_valid_step into l_exists;
4359: if csr_valid_step%notfound then
4360: close csr_valid_step;
4361: hr_utility.set_message(801, 'HR_7379_ASG_INV_SPEC_CEIL_STEP');
4362: hr_multi_message.add
4363: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4364: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4365: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4367: --
4368: else
4369: close csr_valid_step;
4370: end if;
4371: hr_utility.set_location(l_proc, 60);
4372: --
4373: -- Check that the business group of the special_ceiling_step_id on
4374: -- per_grade_spines is the same as that of the assignment.
4375: --
4376: open csr_get_bus_grp;
4377: fetch csr_get_bus_grp into l_business_group_id;
4378: if l_business_group_id <> p_business_group_id then
4379: close csr_get_bus_grp;
4380: hr_utility.set_message(801, 'HR_7375_ASG_INV_BG_SP_CLG_STEP');
4381: hr_multi_message.add
4382: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4383: );
4384: else
4383: );
4384: else
4385: close csr_get_bus_grp;
4386: end if;
4387: hr_utility.set_location(l_proc, 70);
4388: --
4389: if hr_multi_message.no_exclusive_error
4390: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4391: ) then
4397: open csr_valid_step_grade;
4398: fetch csr_valid_step_grade into l_sequence;
4399: if csr_valid_step_grade%notfound then
4400: close csr_valid_step_grade;
4401: hr_utility.set_message(801, 'HR_7380_ASG_STEP_INV_FOR_GRADE');
4402: hr_multi_message.add
4403: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4404: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4405: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4406: );
4407: else
4408: close csr_valid_step_grade;
4409: end if;
4410: hr_utility.set_location(l_proc, 80);
4411: else
4412: --
4413: -- If the value for special ceiling step is not null
4414: -- then grade id must also be not null
4412: --
4413: -- If the value for special ceiling step is not null
4414: -- then grade id must also be not null
4415: --
4416: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
4417: hr_multi_message.add
4418: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4419: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4420: );
4418: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4419: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4420: );
4421: end if;
4422: hr_utility.set_location(l_proc, 90);
4423: --
4424: -- Check if updating
4425: --
4426: if l_api_updating then
4431: open csr_low_step;
4432: fetch csr_low_step into l_exists;
4433: if csr_low_step%found then
4434: close csr_low_step;
4435: hr_utility.set_message(801, 'HR_7381_ASG_CEIL_STEP_TOO_HIGH');
4436: hr_multi_message.add
4437: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4438: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4439: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4440: );
4441: else
4442: close csr_low_step;
4443: end if;
4444: hr_utility.set_location(l_proc, 110);
4445: --
4446: end if;
4447: hr_utility.set_location(l_proc, 120);
4448: end if;
4443: end if;
4444: hr_utility.set_location(l_proc, 110);
4445: --
4446: end if;
4447: hr_utility.set_location(l_proc, 120);
4448: end if;
4449: hr_utility.set_location(l_proc, 130);
4450: --
4451: end if; -- no exclusive error
4445: --
4446: end if;
4447: hr_utility.set_location(l_proc, 120);
4448: end if;
4449: hr_utility.set_location(l_proc, 130);
4450: --
4451: end if; -- no exclusive error
4452: end if;
4453: --
4450: --
4451: end if; -- no exclusive error
4452: end if;
4453: --
4454: hr_utility.set_location(' Leaving:'|| l_proc, 140);
4455: end chk_special_ceiling_step_id;
4456: --
4457: -- ---------------------------------------------------------------------------
4458: -- |--------------------------< chk_supervisor_id >--------------------------|
4509: between effective_start_date
4510: and effective_end_date;
4511: --
4512: begin
4513: hr_utility.set_location('Entering:'|| l_proc, 10);
4514: --
4515: if hr_multi_message.no_exclusive_error
4516: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4517: ) then
4540: (p_api_name => l_proc
4541: ,p_argument => 'effective_date'
4542: ,p_argument_value => p_effective_date
4543: );
4544: hr_utility.set_location(l_proc, 20);
4545: --
4546: -- Only proceed with validation if :
4547: -- a) The current g_old_rec is current and
4548: -- b) The value for supervisor_id has changed
4551: (p_assignment_id => p_assignment_id
4552: ,p_effective_date => p_effective_date
4553: ,p_object_version_number => p_object_version_number
4554: );
4555: hr_utility.set_location(l_proc, 30);
4556: --
4557: if ((l_api_updating and
4558: nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4559: nvl(p_supervisor_id, hr_api.g_number)) or
4558: nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4559: nvl(p_supervisor_id, hr_api.g_number)) or
4560: (NOT l_api_updating))
4561: then
4562: hr_utility.set_location(l_proc, 40);
4563: --
4564: -- Check if supervisor is not null
4565: --
4566: if p_supervisor_id is not null then
4569: -- assignment.
4570: --
4571: If p_supervisor_id = p_person_id then
4572: --
4573: hr_utility.set_message(801, 'HR_51143_ASG_EMP_EQUAL_SUP');
4574: hr_multi_message.add
4575: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4576: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4577: );
4575: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4576: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4577: );
4578: end if;
4579: hr_utility.set_location(l_proc, 50);
4580: --
4581: -- Fix for bug 4305723 starts here. Check if the party_id is same for
4582: -- employee and supervisor.
4583: --
4590: close csr_party_id;
4591: --
4592: If l_per_party_id = l_sup_party_id
4593: then
4594: hr_utility.set_message(800, 'HR_449603_ASG_SUP_DUP_PER');
4595: hr_multi_message.add
4596: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4597: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4598: );
4599: end if;
4600: --
4601: -- Fix for bug 4305723 ends here.
4602: --
4603: hr_utility.set_location(l_proc, 55);
4604: --
4605: -- Check that supervisor_id exists and that it is date effective within
4606: -- the validation period of the assignment.
4607: --
4610: into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
4611: if csr_valid_supervisor_id%notfound then
4612: close csr_valid_supervisor_id;
4613: --
4614: hr_utility.set_message(801, 'PAY_7599_SYS_SUP_DT_OUTDATE');
4615: hr_multi_message.add
4616: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4617: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4618: );
4618: );
4619: else
4620: close csr_valid_supervisor_id;
4621: end if;
4622: hr_utility.set_location(l_proc, 60);
4623: --
4624: -- Check that the supervisor is in the same business group as the
4625: -- person of the assignment.
4626: --
4627: If (p_business_group_id <> l_business_group_id AND
4628: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
4629: then
4630: --
4631: hr_utility.set_message(801, 'HR_51145_ASG_SUP_BG_NE_EMP_BG');
4632: hr_multi_message.add
4633: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4634: );
4635: end if;
4632: hr_multi_message.add
4633: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4634: );
4635: end if;
4636: hr_utility.set_location(l_proc, 70);
4637: --
4638: -- Check that the supervisor is an employee or a contingent
4639: -- worker where the profile option permits.
4640: --
4650: fetch csr_asg_typ into l_assignment_type;
4651: close csr_asg_typ;
4652: if l_assignment_type in ('C','E','B') then
4653: --
4654: hr_utility.set_message(801, 'HR_51346_ASG_SUP_NOT_EMP');
4655: hr_multi_message.add
4656: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID');
4657: --
4658: end if;
4658: end if;
4659: --Bug#3917021 ends here
4660: --
4661: end if;
4662: hr_utility.set_location(l_proc, 80);
4663: --
4664: end if;
4665: --
4666: end if;
4665: --
4666: end if;
4667: end if;
4668: --
4669: hr_utility.set_location(' Leaving:'|| l_proc, 90);
4670: end chk_supervisor_id;
4671: --
4672: -- ---------------------------------------------------------------------------
4673: -- |-------------------< chk_supervisor_assignment_id >----------------------|
4696: and p_validation_start_date between
4697: paaf.effective_start_date and paaf.effective_end_date;
4698: --
4699: begin
4700: hr_utility.set_location('Entering:'|| l_proc, 10);
4701: --
4702: if hr_multi_message.no_exclusive_error
4703: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4704: ) then
4715: (p_api_name => l_proc
4716: ,p_argument => 'effective_date'
4717: ,p_argument_value => p_effective_date
4718: );
4719: hr_utility.set_location(l_proc, 20);
4720: --
4721: -- Only proceed with validation if :
4722: -- a) The current g_old_rec is current and
4723: -- b) The value for supervisor_id has changed
4726: (p_assignment_id => p_assignment_id
4727: ,p_effective_date => p_effective_date
4728: ,p_object_version_number => p_object_version_number
4729: );
4730: hr_utility.set_location(l_proc, 30);
4731:
4732: --
4733: -- Re-validate if either the supervisor or supervisor assignment
4734: -- has changed.
4741: nvl(p_supervisor_assignment_id, hr_api.g_number))) or
4742: (NOT l_api_updating))
4743: then
4744:
4745: hr_utility.set_location(l_proc, 40);
4746: ----
4747: if ((nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4748: nvl(p_supervisor_id, hr_api.g_number))
4749: and
4766: -- Check that supervisor assignment exists, that it is date effective
4767: -- within the validation period of the assignment and that it belongs
4768: -- to the given supervisor.
4769: --
4770: hr_utility.set_location(l_proc, 50);
4771:
4772: open csr_supervisor_assignment_id;
4773: fetch csr_supervisor_assignment_id into l_assignment_type;
4774: if csr_supervisor_assignment_id%notfound then
4771:
4772: open csr_supervisor_assignment_id;
4773: fetch csr_supervisor_assignment_id into l_assignment_type;
4774: if csr_supervisor_assignment_id%notfound then
4775: hr_utility.set_location(l_proc, 60);
4776: close csr_supervisor_assignment_id;
4777: --
4778: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4779: hr_utility.raise_error;
4774: if csr_supervisor_assignment_id%notfound then
4775: hr_utility.set_location(l_proc, 60);
4776: close csr_supervisor_assignment_id;
4777: --
4778: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4779: hr_utility.raise_error;
4780: /* hr_multi_message.add
4781: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4782: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4775: hr_utility.set_location(l_proc, 60);
4776: close csr_supervisor_assignment_id;
4777: --
4778: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4779: hr_utility.raise_error;
4780: /* hr_multi_message.add
4781: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4782: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4783: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4785: else
4786: close csr_supervisor_assignment_id;
4787: end if;
4788:
4789: hr_utility.set_location(l_proc, 70);
4790:
4791: --
4792: -- Check that the supervisor assignment is an employee or a contingent
4793: -- worker assignment.
4796: or (nvl(l_assignment_type, hr_api.g_varchar2) = 'C' and
4797: nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4798: Then
4799: --
4800: hr_utility.set_location(l_proc, 80);
4801: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4802: hr_utility.raise_error;
4803: /*hr_multi_message.add
4804: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4797: nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4798: Then
4799: --
4800: hr_utility.set_location(l_proc, 80);
4801: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4802: hr_utility.raise_error;
4803: /*hr_multi_message.add
4804: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4805: ); */
4798: Then
4799: --
4800: hr_utility.set_location(l_proc, 80);
4801: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4802: hr_utility.raise_error;
4803: /*hr_multi_message.add
4804: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4805: ); */
4806: --
4804: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4805: ); */
4806: --
4807: end if;
4808: hr_utility.set_location(l_proc, 90);
4809: --
4810: end if;
4811: --
4812: end if;
4811: --
4812: end if;
4813: end if;
4814: --
4815: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4816:
4817: end chk_supervisor_assignment_id;
4818: --
4819: -- ---------------------------------------------------------------------------
4855: --
4856: l_proc varchar2(72) := g_package||'chk_system_pers_type';
4857: --
4858: begin
4859: hr_utility.set_location('Entering:'|| l_proc, 1);
4860: --
4861: -- Check mandatory parameters have been set
4862: --
4863: hr_api.mandatory_arg_error
4877: ,p_argument => 'validation_end_date'
4878: ,p_argument_value => p_validation_end_date
4879: );
4880: --
4881: hr_utility.set_location(l_proc, 2);
4882: --
4883: -- Only trigger validation for the following datetrack modes :
4884: -- - UPDATE_OVERRIDE
4885: -- - ZAP
4910: ,p_effective_date => p_effective_date
4911: );
4912: --
4913: end if;
4914: hr_utility.set_location(' Leaving:'|| l_proc, 4);
4915: --
4916: exception
4917: when app_exception.application_exception then
4918: if hr_multi_message.exception_add
4917: when app_exception.application_exception then
4918: if hr_multi_message.exception_add
4919: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4920: ) then
4921: hr_utility.set_location(' Leaving:'|| l_proc, 5);
4922: raise;
4923: end if;
4924: hr_utility.set_location(' Leaving:'|| l_proc, 6);
4925: --
4920: ) then
4921: hr_utility.set_location(' Leaving:'|| l_proc, 5);
4922: raise;
4923: end if;
4924: hr_utility.set_location(' Leaving:'|| l_proc, 6);
4925: --
4926: end chk_system_pers_type;
4927: --
4928: -- ---------------------------------------------------------------------------
4977: and past.assignment_status_type_id = pas.assignment_status_type_id
4978: and past.per_system_status = 'TERM_ASSIGN';
4979: --
4980: begin
4981: hr_utility.set_location('Entering:'|| l_proc, 1);
4982: --
4983: -- Check whether assignment is terminated in the future
4984: --
4985: if p_datetrack_mode in ('UPDATE_OVERRIDE'
4988: open csr_chk_term_status;
4989: fetch csr_chk_term_status into l_exists;
4990: if csr_chk_term_status%found then
4991: close csr_chk_term_status;
4992: hr_utility.set_message(801, 'HR_7412_ASG_ASS_TERM_IN_FUTURE');
4993: hr_multi_message.add
4994: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATETRACK_MODE'
4995: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4996: );
4998: close csr_chk_term_status;
4999: end if;
5000: end if;
5001: --
5002: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5003: end chk_term_status;
5004: --
5005: -- 70.1 change d start.
5006: --
5016: --
5017: l_proc varchar2(72) := g_package||'chk_time_finish_formatted';
5018: --
5019: begin
5020: hr_utility.set_location('Entering:'|| l_proc, 1);
5021: --
5022: -- Check that time_normal_finish is valid
5023: --
5024: if p_time_normal_finish is not null then
5029: ,p_format => 'TIMES');
5030: --
5031: end if;
5032: --
5033: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5034: exception
5035: when app_exception.application_exception then
5036: if hr_multi_message.exception_add
5037: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH'
5035: when app_exception.application_exception then
5036: if hr_multi_message.exception_add
5037: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH'
5038: ) then
5039: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5040: raise;
5041: end if;
5042: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5043: --
5038: ) then
5039: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5040: raise;
5041: end if;
5042: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5043: --
5044: end chk_time_finish_formatted;
5045: --
5046: --
5068: --
5069: l_proc varchar2(72) := g_package||'chk_time_normal_start_formatted';
5070: --
5071: begin
5072: hr_utility.set_location('Entering:'|| l_proc, 1);
5073: --
5074: -- Check that time_normal_start is valid
5075: --
5076: if p_time_normal_start is not null then
5081: ,p_format => 'TIMES');
5082: --
5083: end if;
5084: --
5085: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5086: exception
5087: when app_exception.application_exception then
5088: if hr_multi_message.exception_add
5089: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START'
5087: when app_exception.application_exception then
5088: if hr_multi_message.exception_add
5089: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START'
5090: ) then
5091: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5092: raise;
5093: end if;
5094: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5095: end chk_time_start_formatted;
5090: ) then
5091: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5092: raise;
5093: end if;
5094: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5095: end chk_time_start_formatted;
5096: --
5097: -- << 2734822 >>
5098: --
5142: and pa.date_end is null;
5143: --
5144: begin
5145: --
5146: hr_utility.set_location('Entering:'|| l_proc, 10);
5147: --
5148: -- Check this for Applicant assignments Only.
5149: --
5150: if p_assignment_type = 'A'
5154: open csr_dup_apl_vacancy;
5155: fetch csr_dup_apl_vacancy into l_application_id;
5156: if csr_dup_apl_vacancy%found then
5157: close csr_dup_apl_vacancy;
5158: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5159: hr_utility.raise_error;
5160: --
5161: end if;
5162: close csr_dup_apl_vacancy;
5155: fetch csr_dup_apl_vacancy into l_application_id;
5156: if csr_dup_apl_vacancy%found then
5157: close csr_dup_apl_vacancy;
5158: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5159: hr_utility.raise_error;
5160: --
5161: end if;
5162: close csr_dup_apl_vacancy;
5163: hr_utility.set_location(l_proc, 20);
5159: hr_utility.raise_error;
5160: --
5161: end if;
5162: close csr_dup_apl_vacancy;
5163: hr_utility.set_location(l_proc, 20);
5164: --
5165: end if;
5166: exception
5167: when app_exception.application_exception then
5168: if hr_multi_message.exception_add
5169: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5170: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5171: ) then
5172: hr_utility.set_location(' Leaving:'|| l_proc, 30);
5173: raise;
5174: end if;
5175: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5176: --
5171: ) then
5172: hr_utility.set_location(' Leaving:'|| l_proc, 30);
5173: raise;
5174: end if;
5175: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5176: --
5177: end chk_dup_apl_vacancy;
5178: --
5179: --
5211: from per_vacancies
5212: where vacancy_id = p_vacancy_id;
5213: --
5214: begin
5215: hr_utility.set_location('Entering:'|| l_proc, 10);
5216: --
5217: -- Check mandatory parameters have been set
5218: --
5219: hr_api.mandatory_arg_error
5232: (p_api_name => l_proc
5233: ,p_argument => 'validation_end_date'
5234: ,p_argument_value => p_validation_end_date
5235: );
5236: hr_utility.set_location(l_proc, 20);
5237: --
5238: -- Only proceed with validation if :
5239: -- a) The current g_old_rec is current and
5240: -- b) The value for vacancy has changed
5243: (p_assignment_id => p_assignment_id
5244: ,p_effective_date => p_effective_date
5245: ,p_object_version_number => p_object_version_number
5246: );
5247: hr_utility.set_location(l_proc, 30);
5248: --
5249: if ((l_api_updating and
5250: nvl(per_asg_shd.g_old_rec.vacancy_id, hr_api.g_number) <>
5251: nvl(p_vacancy_id, hr_api.g_number)) or
5249: if ((l_api_updating and
5250: nvl(per_asg_shd.g_old_rec.vacancy_id, hr_api.g_number) <>
5251: nvl(p_vacancy_id, hr_api.g_number)) or
5252: (NOT l_api_updating)) then
5253: hr_utility.set_location(l_proc, 40);
5254: --
5255: -- Check if vacancy is not null
5256: --
5257: if p_vacancy_id is not null then
5264: -- Check if the employee assignment is being updated
5265: --
5266: If l_api_updating then
5267: --
5268: hr_utility.set_message(801, 'HR_51222_ASG_INV_EASG_U_VAC');
5269: hr_multi_message.add
5270: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5271: );
5272: --
5271: );
5272: --
5273: else -- inserting a non employee
5274: --
5275: hr_utility.set_message(801, 'HR_51221_ASG_INV_EASG_I_VAC');
5276: hr_multi_message.add
5277: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5278: );
5279: --
5277: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5278: );
5279: --
5280: end if;
5281: hr_utility.set_location(l_proc, 50);
5282: --
5283: end if;
5284: hr_utility.set_location(l_proc, 60);
5285: --
5280: end if;
5281: hr_utility.set_location(l_proc, 50);
5282: --
5283: end if;
5284: hr_utility.set_location(l_proc, 60);
5285: --
5286: if p_assignment_type = 'O'
5287: then
5288: --
5294: if csr_val_vacancy_id_offer%notfound
5295: then
5296: --
5297: close csr_val_vacancy_id;
5298: hr_utility.set_message(800, 'HR_52591_CEL_INVL_VAC_ID');
5299: hr_utility.raise_error;
5300: --
5301: end if;
5302: --
5295: then
5296: --
5297: close csr_val_vacancy_id;
5298: hr_utility.set_message(800, 'HR_52591_CEL_INVL_VAC_ID');
5299: hr_utility.raise_error;
5300: --
5301: end if;
5302: --
5303: else
5310: open csr_val_vacancy_id;
5311: fetch csr_val_vacancy_id into l_business_group_id;
5312: if csr_val_vacancy_id%notfound then
5313: close csr_val_vacancy_id;
5314: hr_utility.set_message(801, 'HR_51297_ASG_INV_VACANCY');
5315: hr_utility.raise_error;
5316: /* hr_multi_message.add
5317: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5318: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5311: fetch csr_val_vacancy_id into l_business_group_id;
5312: if csr_val_vacancy_id%notfound then
5313: close csr_val_vacancy_id;
5314: hr_utility.set_message(801, 'HR_51297_ASG_INV_VACANCY');
5315: hr_utility.raise_error;
5316: /* hr_multi_message.add
5317: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5318: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5319: ); */
5322: close csr_val_vacancy_id;
5323: end if;
5324: --
5325: end if;
5326: hr_utility.set_location(l_proc, 70);
5327: --
5328: -- Check that the vacancy is in the same business group
5329: -- as the business group of the assignment.
5330: --
5329: -- as the business group of the assignment.
5330: --
5331: If p_business_group_id <> l_business_group_id then
5332: --
5333: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5334: hr_utility.raise_error;
5335: --
5336: end if;
5337: hr_utility.set_location(l_proc, 80);
5330: --
5331: If p_business_group_id <> l_business_group_id then
5332: --
5333: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5334: hr_utility.raise_error;
5335: --
5336: end if;
5337: hr_utility.set_location(l_proc, 80);
5338: --
5333: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5334: hr_utility.raise_error;
5335: --
5336: end if;
5337: hr_utility.set_location(l_proc, 80);
5338: --
5339: end if;
5340: hr_utility.set_location(l_proc, 90);
5341: --
5336: end if;
5337: hr_utility.set_location(l_proc, 80);
5338: --
5339: end if;
5340: hr_utility.set_location(l_proc, 90);
5341: --
5342: end if;
5343: --
5344: hr_utility.set_location(' Leaving:'|| l_proc, 100);
5340: hr_utility.set_location(l_proc, 90);
5341: --
5342: end if;
5343: --
5344: hr_utility.set_location(' Leaving:'|| l_proc, 100);
5345: end chk_vacancy_id;
5346: --
5347: -- ---------------------------------------------------------------------------
5348: -- |----------------------< gen_assignment_sequence >------------------------|
5364: where person_id = p_person_id
5365: and assignment_type = p_assignment_type;
5366: --
5367: begin
5368: hr_utility.set_location('Entering:'|| l_proc, 1);
5369: --
5370: if hr_multi_message.no_exclusive_error
5371: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5372: ) then
5384: ,p_argument => 'person_id'
5385: ,p_argument_value => p_person_id
5386: );
5387: --
5388: hr_utility.set_location(l_proc, 2);
5389: --
5390: -- Generate next assignment sequence
5391: --
5392: open csr_get_ass_seq;
5393: fetch csr_get_ass_seq into l_assignment_sequence;
5394: close csr_get_ass_seq;
5395: p_assignment_sequence := l_assignment_sequence;
5396: end if;
5397: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5398: --
5399: end gen_assignment_sequence;
5400: --
5401: -- ---------------------------------------------------------------------------
5447: and p_effective_date between pas.effective_start_date
5448: and pas.effective_end_date;
5449: --
5450: begin
5451: hr_utility.set_location('Entering:'|| l_proc, 1);
5452: --
5453: -- Check mandatory parameters have been set
5454: --
5455: hr_api.mandatory_arg_error
5477: l_other_manager_exists := csr_other_manager_in_org%found;
5478: --
5479: close csr_other_manager_in_org;
5480: --
5481: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5482: --
5483: return l_other_manager_exists;
5484: end other_managers_in_org;
5485: --
5501: l_proc varchar2(72) := g_package||'gen_date_probation_end';
5502: l_api_updating boolean;
5503: --
5504: begin
5505: hr_utility.set_location('Entering:'|| l_proc, 10);
5506: --
5507: -- Check mandatory parameters have been set
5508: --
5509: hr_api.mandatory_arg_error
5516: (p_api_name => l_proc
5517: ,p_argument => 'validation_start_date'
5518: ,p_argument_value => p_validation_start_date
5519: );
5520: hr_utility.set_location(l_proc, 20);
5521: --
5522: -- Only proceed with generation if :
5523: -- a) The current g_old_rec is current and
5524: -- b) One or more of the values for date probation end, probation period or
5528: (p_assignment_id => p_assignment_id
5529: ,p_effective_date => p_effective_date
5530: ,p_object_version_number => p_object_version_number
5531: );
5532: hr_utility.set_location(l_proc, 30);
5533: --
5534: if NOT l_api_updating
5535: or
5536: (l_api_updating and
5542: or
5543: (nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number) <>
5544: nvl(p_probation_period, hr_api.g_number))))
5545: then
5546: hr_utility.set_location(l_proc, 40);
5547: --
5548: -- Check if probation unit and probation period are both not null.
5549: --
5550: if p_probation_unit is not null and p_probation_period is not null then
5559: If p_probation_unit = 'D' then
5560: --
5561: p_date_probation_end := p_validation_start_date
5562: + (p_probation_period-1);
5563: hr_utility.set_location(l_proc, 50);
5564: --
5565: elsif p_probation_unit = 'W' then
5566: --
5567: p_date_probation_end := p_validation_start_date
5565: elsif p_probation_unit = 'W' then
5566: --
5567: p_date_probation_end := p_validation_start_date
5568: + ((p_probation_period*7)-1);
5569: hr_utility.set_location(l_proc, 60);
5570: --
5571: elsif p_probation_unit = 'M' then
5572: --
5573: p_date_probation_end := add_months(p_validation_start_date,
5571: elsif p_probation_unit = 'M' then
5572: --
5573: p_date_probation_end := add_months(p_validation_start_date,
5574: p_probation_period)-1;
5575: hr_utility.set_location(l_proc, 70);
5576: --
5577: elsif p_probation_unit = 'Y' then
5578: --
5579: p_date_probation_end := add_months(p_validation_start_date,
5577: elsif p_probation_unit = 'Y' then
5578: --
5579: p_date_probation_end := add_months(p_validation_start_date,
5580: 12*p_probation_period)-1;
5581: hr_utility.set_location(l_proc, 80);
5582: --
5583: end if;
5584: --
5585: else
5586: --
5587: -- Nullify date probation end
5588: --
5589: p_date_probation_end := null;
5590: hr_utility.set_location(l_proc, 9);
5591: end if;
5592: --
5593: end if;
5594: --
5593: end if;
5594: --
5595: end if;
5596: --
5597: hr_utility.set_location(' Leaving:'|| l_proc, 10);
5598: end gen_date_probation_end;
5599: --
5600: -- ---------------------------------------------------------------------------
5601: -- |---------------------< chk_internal_address_line >-----------------------|
5613: l_proc varchar2(72) := g_package||'chk_internal_address_line';
5614: l_api_updating boolean;
5615: --
5616: begin
5617: hr_utility.set_location('Entering:'|| l_proc, 10);
5618: --
5619: -- Check mandatory parameters have been set
5620: --
5621: hr_api.mandatory_arg_error
5622: (p_api_name => l_proc
5623: ,p_argument => 'effective_date'
5624: ,p_argument_value => p_effective_date
5625: );
5626: hr_utility.set_location(l_proc, 20);
5627: --
5628: -- Only proceed with validation if :
5629: -- a) The current g_old_rec is current and
5630: -- b) The value for internal address line has changed
5632: l_api_updating := per_asg_shd.api_updating
5633: (p_assignment_id => p_assignment_id
5634: ,p_effective_date => p_effective_date
5635: ,p_object_version_number => p_object_version_number);
5636: hr_utility.set_location(l_proc, 30);
5637: --
5638: if ((l_api_updating and
5639: nvl(per_asg_shd.g_old_rec.internal_address_line, hr_api.g_varchar2)
5640: <> nvl(p_internal_address_line, hr_api.g_varchar2))
5639: nvl(per_asg_shd.g_old_rec.internal_address_line, hr_api.g_varchar2)
5640: <> nvl(p_internal_address_line, hr_api.g_varchar2))
5641: or
5642: (NOT l_api_updating)) then
5643: hr_utility.set_location(l_proc, 40);
5644: --
5645: -- Check if internal address line is not null
5646: --
5647: if p_internal_address_line is not null then
5650: -- assignment.
5651: --
5652: if p_assignment_type not in ('E','A','B','C','O') then
5653: --
5654: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5655: hr_utility.raise_error;
5656: --
5657: end if;
5658: hr_utility.set_location(l_proc, 50);
5651: --
5652: if p_assignment_type not in ('E','A','B','C','O') then
5653: --
5654: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5655: hr_utility.raise_error;
5656: --
5657: end if;
5658: hr_utility.set_location(l_proc, 50);
5659: --
5654: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5655: hr_utility.raise_error;
5656: --
5657: end if;
5658: hr_utility.set_location(l_proc, 50);
5659: --
5660: end if;
5661: --
5662: end if;
5660: end if;
5661: --
5662: end if;
5663: --
5664: hr_utility.set_location(' Leaving:'|| l_proc, 60);
5665: exception
5666: when app_exception.application_exception then
5667: if hr_multi_message.exception_add
5668: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE'
5666: when app_exception.application_exception then
5667: if hr_multi_message.exception_add
5668: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE'
5669: ) then
5670: hr_utility.set_location(' Leaving:'|| l_proc, 70);
5671: raise;
5672: end if;
5673: hr_utility.set_location(' Leaving:'|| l_proc, 80);
5674: --
5669: ) then
5670: hr_utility.set_location(' Leaving:'|| l_proc, 70);
5671: raise;
5672: end if;
5673: hr_utility.set_location(' Leaving:'|| l_proc, 80);
5674: --
5675: end chk_internal_address_line;
5676: --
5677: --
5691: l_proc varchar2(72) := g_package||'chk_applicant_rank';
5692: l_api_updating boolean;
5693: --
5694: begin
5695: hr_utility.set_location('Entering:'|| l_proc, 10);
5696: --
5697: --
5698: l_api_updating := per_asg_shd.api_updating
5699: (p_assignment_id => p_assignment_id
5705: nvl(per_asg_shd.g_old_rec.applicant_rank, hr_api.g_number) <>
5706: nvl(p_applicant_rank, hr_api.g_number)) or
5707: (NOT l_api_updating)) then
5708:
5709: hr_utility.set_location(l_proc, 20);
5710: --
5711: -- Check if applicant_rank is not null
5712: --
5713: if p_applicant_rank IS NOT NULL then
5711: -- Check if applicant_rank is not null
5712: --
5713: if p_applicant_rank IS NOT NULL then
5714: --
5715: hr_utility.set_location(l_proc, 30);
5716: --
5717: -- applicant rank must be between 0 and 100
5718: --
5719: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5717: -- applicant rank must be between 0 and 100
5718: --
5719: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5720: --
5721: hr_utility.set_location(l_proc, 40);
5722: --
5723: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5724: hr_utility.raise_error;
5725: --
5719: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5720: --
5721: hr_utility.set_location(l_proc, 40);
5722: --
5723: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5724: hr_utility.raise_error;
5725: --
5726: end if;
5727: --
5720: --
5721: hr_utility.set_location(l_proc, 40);
5722: --
5723: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5724: hr_utility.raise_error;
5725: --
5726: end if;
5727: --
5728: hr_utility.set_location(l_proc, 50);
5724: hr_utility.raise_error;
5725: --
5726: end if;
5727: --
5728: hr_utility.set_location(l_proc, 50);
5729: --
5730: -- Check that when inserting, the assignment is an applicant or offer assignment
5731: --
5732: if p_assignment_type in ('E','C','B') then
5729: --
5730: -- Check that when inserting, the assignment is an applicant or offer assignment
5731: --
5732: if p_assignment_type in ('E','C','B') then
5733: hr_utility.set_location(l_proc, 60);
5734: --
5735: -- Check if the employee assignment is being updated
5736: --
5737: if l_api_updating then
5737: if l_api_updating then
5738: --
5739: -- non applicant, rank can only be updated to null
5740: --
5741: hr_utility.set_message(800, 'HR_289950_APP_RANK_INV_UPD');
5742: hr_utility.raise_error;
5743: --
5744: else -- inserting a non applicant
5745: --
5738: --
5739: -- non applicant, rank can only be updated to null
5740: --
5741: hr_utility.set_message(800, 'HR_289950_APP_RANK_INV_UPD');
5742: hr_utility.raise_error;
5743: --
5744: else -- inserting a non applicant
5745: --
5746: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5742: hr_utility.raise_error;
5743: --
5744: else -- inserting a non applicant
5745: --
5746: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5747: hr_utility.raise_error;
5748: --
5749: end if;
5750: --
5743: --
5744: else -- inserting a non applicant
5745: --
5746: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5747: hr_utility.raise_error;
5748: --
5749: end if;
5750: --
5751: end if;
5752: --
5753: end if;
5754: --
5755: end if;
5756: hr_utility.set_location('Leaving:'|| l_proc, 70);
5757: exception
5758: when app_exception.application_exception then
5759: if hr_multi_message.exception_add
5760: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICANT_RANK'
5788: and rownum = 1;
5789: --
5790: begin
5791: --
5792: hr_utility.set_location('Entering:'|| l_proc, 10);
5793: --
5794: l_api_updating := per_asg_shd.api_updating
5795: (p_assignment_id => p_assignment_id
5796: ,p_effective_date => p_effective_date
5801: nvl(per_asg_shd.g_old_rec.posting_content_id, hr_api.g_number) <>
5802: nvl(p_posting_content_id, hr_api.g_number)) or
5803: (NOT l_api_updating)) then
5804:
5805: hr_utility.set_location(l_proc, 20);
5806: --
5807: -- Check if posting_content_id is not null
5808: --
5809: if p_posting_content_id IS NOT NULL then
5817: l_posting_content_id := null;
5818: end if;
5819: --
5820: close irc_exists;
5821: hr_utility.set_location(l_proc, 30);
5822: --
5823: if (l_posting_content_id <> p_posting_content_id) then
5824: --
5825: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
5821: hr_utility.set_location(l_proc, 30);
5822: --
5823: if (l_posting_content_id <> p_posting_content_id) then
5824: --
5825: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
5826: hr_utility.raise_error;
5827: --
5828: end if;
5829: --
5822: --
5823: if (l_posting_content_id <> p_posting_content_id) then
5824: --
5825: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
5826: hr_utility.raise_error;
5827: --
5828: end if;
5829: --
5830: -- Check that when inserting, the assignment is an applicant or offer assignment
5829: --
5830: -- Check that when inserting, the assignment is an applicant or offer assignment
5831: --
5832: if p_assignment_type in ('E','C','B') then
5833: hr_utility.set_location(l_proc, 40);
5834: --
5835: -- Check if the employee assignment is being updated
5836: --
5837: if l_api_updating then
5837: if l_api_updating then
5838: --
5839: -- non applicant/offer, posting_content_id can only be updated to null
5840: --
5841: hr_utility.set_message(800, 'HR_289951_POSTING_CONT_INV_UPD');
5842: hr_utility.raise_error;
5843: --
5844: else -- inserting a non applicant
5845: --
5838: --
5839: -- non applicant/offer, posting_content_id can only be updated to null
5840: --
5841: hr_utility.set_message(800, 'HR_289951_POSTING_CONT_INV_UPD');
5842: hr_utility.raise_error;
5843: --
5844: else -- inserting a non applicant
5845: --
5846: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
5842: hr_utility.raise_error;
5843: --
5844: else -- inserting a non applicant
5845: --
5846: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
5847: hr_utility.raise_error;
5848: --
5849: end if;
5850: --
5843: --
5844: else -- inserting a non applicant
5845: --
5846: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
5847: hr_utility.raise_error;
5848: --
5849: end if;
5850: --
5851: end if;
5852: --
5853: end if;
5854: --
5855: end if;
5856: hr_utility.set_location('Leaving: '||l_proc, 50);
5857: exception
5858: when app_exception.application_exception then
5859: if hr_multi_message.exception_add
5860: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSTING_CONTENT_ID'