[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: -- ---------------------------------------------------------------------------
372: l_style per_addresses.style%TYPE;
373: -- end of fix 10040024
374: --
375: begin
376: hr_utility.set_location('Entering:'|| l_proc, 10);
377: --
378: -- Initialize payroll updated flag
379: --
380: p_payroll_id_updated := FALSE;
403: (p_api_name => l_proc
404: ,p_argument => 'validation_end_date'
405: ,p_argument_value => p_validation_end_date
406: );
407: hr_utility.set_location(l_proc, 20);
408: --
409: l_api_updating := per_asg_shd.api_updating
410: (p_assignment_id => p_assignment_id
411: ,p_effective_date => p_effective_date
410: (p_assignment_id => p_assignment_id
411: ,p_effective_date => p_effective_date
412: ,p_object_version_number => p_object_version_number
413: );
414: hr_utility.set_location(l_proc, 30);
415: --
416: if (l_api_updating and
417: ((nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
418: <> nvl(p_payroll_id, hr_api.g_number)) or
421: )
422: )
423: or NOT l_api_updating
424: then
425: hr_utility.set_location(l_proc, 40);
426: --
427: if (l_api_updating and
428: nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
429: <> nvl(p_payroll_id, hr_api.g_number)) then
431: -- As payroll id has been updated, set p_payroll_id_updated to true.
432: -- This functionality is required for the /update/delete_assignment
433: -- business processes
434: --
435: hr_utility.set_location(l_proc, 45);
436: p_payroll_id_updated := TRUE;
437: end if;
438: --
439: if p_payroll_id is not null then
445: --
446: --
447: if p_assignment_type not in ('E','A','B','O') then
448: --
449: hr_utility.set_message(801, 'HR_51226_ASG_INV_ASG_TYP_PAY');
450: hr_multi_message.add
451: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
452: );
453: --
451: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
452: );
453: --
454: end if;
455: hr_utility.set_location(l_proc, 50);
456: --
457: -- Check if GEOCODES is installed
458: --
459:
464:
465: if hr_general.chk_geocodes_installed = 'Y'
466: and p_assignment_type = 'E'
467: and ( ( l_legislation_code = 'CA'
468: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
469: p_legislation => 'CA'))
470: OR ( l_legislation_code = 'US'
471: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
472: p_legislation => 'US')))
467: and ( ( l_legislation_code = 'CA'
468: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
469: p_legislation => 'CA'))
470: OR ( l_legislation_code = 'US'
471: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
472: p_legislation => 'US')))
473: then
474: --
475: -- Check if a primary address exists for the person
475: -- Check if a primary address exists for the person
476: -- of the employee assignment
477: --
478: if p_address_line1 is null then
479: hr_utility.set_message(800, 'PER_52990_ASG_PRADD_NE_PAY');
480: hr_multi_message.add
481: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
482: );
483: --
485:
486: -- start10040024
487: -- BUG 12783536 starts - Base bug : 12331548
488: IF (l_legislation_code = 'US'
489: and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
490: p_legislation => 'US')) THEN
491: open csr_add_style;
492: fetch csr_add_style into l_style;
493: close csr_add_style;
493: close csr_add_style;
494: -- if l_style <> 'US' then
495: if l_style not in ('US','US_GLB_FED') then --10409507
496: if hr_general2.is_legislation_install('PAY','US') then --11933456
497: hr_utility.set_location(l_proc, 54);
498: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
499: hr_multi_message.add
500: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
501: );
494: -- if l_style <> 'US' then
495: if l_style not in ('US','US_GLB_FED') then --10409507
496: if hr_general2.is_legislation_install('PAY','US') then --11933456
497: hr_utility.set_location(l_proc, 54);
498: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
499: hr_multi_message.add
500: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
501: );
502:
506: -- BUG 12783536 ends
507: --
508: -- end 10040024
509: --
510: hr_utility.set_location(l_proc, 55);
511: --
512: end if;
513: --
514: -- Check that payroll exists and the effective start date of the
519: open csr_payroll_exists;
520: fetch csr_payroll_exists into l_exists;
521: if csr_payroll_exists%notfound then
522: close csr_payroll_exists;
523: hr_utility.set_message(801, 'HR_7370_ASG_INVALID_PAYROLL');
524: hr_multi_message.add
525: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
526: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
527: );
527: );
528: else
529: close csr_payroll_exists;
530: end if;
531: hr_utility.set_location(l_proc, 60);
532: --
533: -- Check that business group of payroll is the
534: -- same as that of the assignment
535: --
536: open csr_get_bus_grp;
537: fetch csr_get_bus_grp into l_business_group_id;
538: if l_business_group_id <> p_business_group_id then
539: close csr_get_bus_grp;
540: hr_utility.set_message(801, 'HR_7373_ASG_INVALID_BG_PAYROLL');
541: hr_multi_message.add
542: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
543: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
544: );
544: );
545: else
546: close csr_get_bus_grp;
547: end if;
548: hr_utility.set_location(l_proc, 70);
549: --
550: -- Check that person to whom the assignment is linked
551: -- has their D.O.B. recorded on per_people_f
552: --
550: -- Check that person to whom the assignment is linked
551: -- has their D.O.B. recorded on per_people_f
552: --
553: if p_assignment_type = 'E' then
554: hr_utility.set_location(l_proc, 75);
555: if p_date_of_birth is null then
556: hr_utility.set_message(801, 'HR_7378_ASG_NO_DATE_OF_BIRTH');
557: hr_multi_message.add
558: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
552: --
553: if p_assignment_type = 'E' then
554: hr_utility.set_location(l_proc, 75);
555: if p_date_of_birth is null then
556: hr_utility.set_message(801, 'HR_7378_ASG_NO_DATE_OF_BIRTH');
557: hr_multi_message.add
558: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
559: );
560: end if;
557: hr_multi_message.add
558: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
559: );
560: end if;
561: hr_utility.set_location(l_proc, 80);
562: end if;
563: end if;
564: end if;
565: --
568: open csr_any_future_changes;
569: fetch csr_any_future_changes into l_exists;
570: l_future_change := csr_any_future_changes%FOUND;
571: close csr_any_future_changes;
572: hr_utility.set_location(l_proc, 90);
573: --
574: if (l_api_updating
575: and (per_asg_shd.g_old_rec.payroll_id is not null
576: and p_payroll_id is not null
581: or p_datetrack_mode = 'UPDATE_OVERRIDE'
582: or p_datetrack_mode = 'UPDATE_CHANGE_INSERT')) --added for bug 8404508
583: then
584: --
585: hr_utility.set_location(l_proc, 100);
586: --
587: l_invalid_ppm := FALSE;
588: l_working_start_date := p_validation_start_date;
589: --
596: ,l_org_payment_method_id
597: ,l_min_ppm_effective_start_date
598: ,l_max_ppm_effective_end_date;
599: --
600: hr_utility.set_location(l_proc, 110);
601: hr_utility.trace
602: ('p_payroll_id = ' || p_payroll_id);
603: hr_utility.trace
604: ('p_validation_start_date = ' || p_validation_start_date);
597: ,l_min_ppm_effective_start_date
598: ,l_max_ppm_effective_end_date;
599: --
600: hr_utility.set_location(l_proc, 110);
601: hr_utility.trace
602: ('p_payroll_id = ' || p_payroll_id);
603: hr_utility.trace
604: ('p_validation_start_date = ' || p_validation_start_date);
605: hr_utility.trace
599: --
600: hr_utility.set_location(l_proc, 110);
601: hr_utility.trace
602: ('p_payroll_id = ' || p_payroll_id);
603: hr_utility.trace
604: ('p_validation_start_date = ' || p_validation_start_date);
605: hr_utility.trace
606: ('p_validation_end_date = ' || p_validation_end_date);
607: hr_utility.trace
601: hr_utility.trace
602: ('p_payroll_id = ' || p_payroll_id);
603: hr_utility.trace
604: ('p_validation_start_date = ' || p_validation_start_date);
605: hr_utility.trace
606: ('p_validation_end_date = ' || p_validation_end_date);
607: hr_utility.trace
608: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
609: hr_utility.trace
603: hr_utility.trace
604: ('p_validation_start_date = ' || p_validation_start_date);
605: hr_utility.trace
606: ('p_validation_end_date = ' || p_validation_end_date);
607: hr_utility.trace
608: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
609: hr_utility.trace
610: ('l_org_payment_method_id = ' || l_org_payment_method_id);
611: hr_utility.trace
605: hr_utility.trace
606: ('p_validation_end_date = ' || p_validation_end_date);
607: hr_utility.trace
608: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
609: hr_utility.trace
610: ('l_org_payment_method_id = ' || l_org_payment_method_id);
611: hr_utility.trace
612: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
613: hr_utility.trace
607: hr_utility.trace
608: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
609: hr_utility.trace
610: ('l_org_payment_method_id = ' || l_org_payment_method_id);
611: hr_utility.trace
612: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
613: hr_utility.trace
614: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
615: --
609: hr_utility.trace
610: ('l_org_payment_method_id = ' || l_org_payment_method_id);
611: hr_utility.trace
612: ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
613: hr_utility.trace
614: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
615: --
616: -- If a PPM has been retrieved, and no invalid PPMs have been identified
617: -- yet and we have not yet reached the validation end date then check the
621: and not l_invalid_ppm
622: and l_working_start_date < p_validation_end_date
623: loop
624: --
625: hr_utility.set_location(l_proc, 120);
626: --
627: -- Get the latest end date for all OPUs that are effective for the
628: -- current working date for the current PPM for the payroll id. As we are
629: -- only interested in OPUs that span the current PPM, setting the current
643: else
644: l_working_end_date := p_validation_end_date;
645: end if;
646: --
647: hr_utility.set_location(l_proc, 130);
648: hr_utility.trace
649: ('l_working_start_date = ' || l_working_start_date);
650: hr_utility.trace
651: ('l_working_end_date = ' || l_working_end_date);
644: l_working_end_date := p_validation_end_date;
645: end if;
646: --
647: hr_utility.set_location(l_proc, 130);
648: hr_utility.trace
649: ('l_working_start_date = ' || l_working_start_date);
650: hr_utility.trace
651: ('l_working_end_date = ' || l_working_end_date);
652: --
646: --
647: hr_utility.set_location(l_proc, 130);
648: hr_utility.trace
649: ('l_working_start_date = ' || l_working_start_date);
650: hr_utility.trace
651: ('l_working_end_date = ' || l_working_end_date);
652: --
653: open csr_get_opus
654: (l_org_payment_method_id
664: hr_api.g_eot);
665: l_max_opu_effective_end_date := nvl(l_cur_opu_effective_end_date,
666: hr_api.g_date);
667: --
668: hr_utility.set_location(l_proc, 140);
669: hr_utility.trace
670: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
671: hr_utility.trace
672: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
665: l_max_opu_effective_end_date := nvl(l_cur_opu_effective_end_date,
666: hr_api.g_date);
667: --
668: hr_utility.set_location(l_proc, 140);
669: hr_utility.trace
670: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
671: hr_utility.trace
672: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
673: hr_utility.trace
667: --
668: hr_utility.set_location(l_proc, 140);
669: hr_utility.trace
670: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
671: hr_utility.trace
672: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
673: hr_utility.trace
674: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
675: --
669: hr_utility.trace
670: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
671: hr_utility.trace
672: ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
673: hr_utility.trace
674: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
675: --
676: while csr_get_opus%FOUND
677: and not l_invalid_ppm
679: or l_max_opu_effective_end_date < l_working_end_date
680: )
681: loop
682: --
683: hr_utility.set_location(l_proc, 150);
684: --
685: if l_cur_opu_effective_start_date < l_min_opu_effective_start_date
686: then
687: l_min_opu_effective_start_date := l_cur_opu_effective_start_date;
696: into l_org_pay_method_usage_id
697: ,l_cur_opu_effective_start_date
698: ,l_cur_opu_effective_end_date;
699: --
700: hr_utility.set_location(l_proc, 160);
701: hr_utility.trace
702: ('l_min_opu_effective_start_date = ' ||
703: l_min_opu_effective_start_date);
704: hr_utility.trace
697: ,l_cur_opu_effective_start_date
698: ,l_cur_opu_effective_end_date;
699: --
700: hr_utility.set_location(l_proc, 160);
701: hr_utility.trace
702: ('l_min_opu_effective_start_date = ' ||
703: l_min_opu_effective_start_date);
704: hr_utility.trace
705: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
700: hr_utility.set_location(l_proc, 160);
701: hr_utility.trace
702: ('l_min_opu_effective_start_date = ' ||
703: l_min_opu_effective_start_date);
704: hr_utility.trace
705: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
706: hr_utility.trace
707: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
708: hr_utility.trace
702: ('l_min_opu_effective_start_date = ' ||
703: l_min_opu_effective_start_date);
704: hr_utility.trace
705: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
706: hr_utility.trace
707: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
708: hr_utility.trace
709: ('l_cur_opu_effective_start_date = ' ||
710: l_cur_opu_effective_start_date);
704: hr_utility.trace
705: ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
706: hr_utility.trace
707: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
708: hr_utility.trace
709: ('l_cur_opu_effective_start_date = ' ||
710: l_cur_opu_effective_start_date);
711: hr_utility.trace
712: ('l_cur_opu_effective_end_date = ' || l_cur_opu_effective_end_date);
707: ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
708: hr_utility.trace
709: ('l_cur_opu_effective_start_date = ' ||
710: l_cur_opu_effective_start_date);
711: hr_utility.trace
712: ('l_cur_opu_effective_end_date = ' || l_cur_opu_effective_end_date);
713: --
714: if l_cur_opu_effective_start_date - 1 > l_max_opu_effective_end_date
715: then
713: --
714: if l_cur_opu_effective_start_date - 1 > l_max_opu_effective_end_date
715: then
716: --
717: hr_utility.set_location(l_proc, 170);
718: --
719: -- We have found a 'hole'.
720: --
721: -- ie. h
726: l_invalid_ppm := TRUE;
727: end if;
728: end loop;
729: --
730: hr_utility.set_location(l_proc, 180);
731: --
732: close csr_get_opus;
733: --
734: if l_min_opu_effective_start_date > l_working_start_date
734: if l_min_opu_effective_start_date > l_working_start_date
735: or l_max_opu_effective_end_date < l_working_end_date
736: then
737: --
738: hr_utility.set_location(l_proc, 190);
739: --
740: l_invalid_ppm := TRUE;
741: else
742: --
739: --
740: l_invalid_ppm := TRUE;
741: else
742: --
743: hr_utility.set_location(l_proc, 200);
744: --
745: fetch csr_get_ppms
746: into l_personal_payment_method_id
747: ,l_org_payment_method_id
754: else
755: l_working_start_date := p_validation_start_date;
756: end if;
757: --
758: hr_utility.set_location(l_proc, 210);
759: hr_utility.trace
760: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
761: hr_utility.trace
762: ('l_org_payment_method_id = ' || l_org_payment_method_id);
755: l_working_start_date := p_validation_start_date;
756: end if;
757: --
758: hr_utility.set_location(l_proc, 210);
759: hr_utility.trace
760: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
761: hr_utility.trace
762: ('l_org_payment_method_id = ' || l_org_payment_method_id);
763: hr_utility.trace
757: --
758: hr_utility.set_location(l_proc, 210);
759: hr_utility.trace
760: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
761: hr_utility.trace
762: ('l_org_payment_method_id = ' || l_org_payment_method_id);
763: hr_utility.trace
764: ('l_min_ppm_effective_start_date = ' ||
765: l_min_ppm_effective_start_date);
759: hr_utility.trace
760: ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
761: hr_utility.trace
762: ('l_org_payment_method_id = ' || l_org_payment_method_id);
763: hr_utility.trace
764: ('l_min_ppm_effective_start_date = ' ||
765: l_min_ppm_effective_start_date);
766: hr_utility.trace
767: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
762: ('l_org_payment_method_id = ' || l_org_payment_method_id);
763: hr_utility.trace
764: ('l_min_ppm_effective_start_date = ' ||
765: l_min_ppm_effective_start_date);
766: hr_utility.trace
767: ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
768: --
769: end if;
770: end loop;
769: end if;
770: end loop;
771: --
772: close csr_get_ppms;
773: hr_utility.set_location(l_proc, 220);
774: --
775: if l_invalid_ppm
776: then
777: --
774: --
775: if l_invalid_ppm
776: then
777: --
778: hr_utility.set_location(l_proc, 230);
779: --
780: hr_utility.set_message(801, 'HR_7969_ASG_INV_PAYROLL_PPMS');
781: hr_multi_message.add
782: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
776: then
777: --
778: hr_utility.set_location(l_proc, 230);
779: --
780: hr_utility.set_message(801, 'HR_7969_ASG_INV_PAYROLL_PPMS');
781: hr_multi_message.add
782: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
783: );
784: end if;
796: or p_datetrack_mode = 'UPDATE_CHANGE_INSERT' -- added for bug 8404508
797: )
798: then
799: --
800: hr_utility.set_location(l_proc, 220);
801: --
802: -- Find any ASAs that arise after the change effective date.
803: --
804: open csr_any_future_asas;
807: --
808: if csr_any_future_asas%FOUND
809: then
810: --
811: hr_utility.set_location(l_proc, 230);
812: --
813: close csr_any_future_asas;
814: --
815: hr_utility.set_message(801, 'HR_7975_ASG_INV_FUTURE_ASA');
811: hr_utility.set_location(l_proc, 230);
812: --
813: close csr_any_future_asas;
814: --
815: hr_utility.set_message(801, 'HR_7975_ASG_INV_FUTURE_ASA');
816: hr_multi_message.add
817: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
818: );
819: else
817: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
818: );
819: else
820: --
821: hr_utility.set_location(l_proc, 240);
822: --
823: close csr_any_future_asas;
824: end if;
825: --
822: --
823: close csr_any_future_asas;
824: end if;
825: --
826: hr_utility.set_location(l_proc, 250);
827: end if;
828: --
829: hr_utility.set_location(' Leaving:'|| l_proc, 300);
830: end chk_payroll_id_int;
825: --
826: hr_utility.set_location(l_proc, 250);
827: end if;
828: --
829: hr_utility.set_location(' Leaving:'|| l_proc, 300);
830: end chk_payroll_id_int;
831: --
832: -- ---------------------------------------------------------------------------
833: -- |-----------------------< chk_people_group_id >---------------------------|
868: and pbg.people_group_structure = to_char(ppg.id_flex_num)
869: and pbg.business_group_id = p_business_group_id;
870: --
871: begin
872: hr_utility.set_location('Entering:'|| l_proc, 10);
873: --
874: --
875: -- Check mandatory parameters have been set
876: --
896: (p_api_name => l_proc
897: ,p_argument => 'validation_end_date'
898: ,p_argument_value => p_validation_end_date
899: );
900: hr_utility.set_location(l_proc, 20);
901: --
902: -- Only proceed with validation if :
903: -- a) The current g_old_rec is current and
904: -- b) The value for people group has changed
907: (p_assignment_id => p_assignment_id
908: ,p_effective_date => p_effective_date
909: ,p_object_version_number => p_object_version_number
910: );
911: hr_utility.set_location(l_proc, 30);
912: --
913: if ((l_api_updating and
914: nvl(per_asg_shd.g_old_rec.people_group_id, hr_api.g_number) <>
915: nvl(p_people_group_id, hr_api.g_number)) or
914: nvl(per_asg_shd.g_old_rec.people_group_id, hr_api.g_number) <>
915: nvl(p_people_group_id, hr_api.g_number)) or
916: (NOT l_api_updating))
917: then
918: hr_utility.set_location(l_proc, 40);
919: --
920: -- Check if people group is set
921: --
922: if p_people_group_id is not null then
926: open csr_valid_people_group;
927: fetch csr_valid_people_group into l_enabled_flag;
928: if csr_valid_people_group%notfound then
929: close csr_valid_people_group;
930: hr_utility.set_message(801, 'HR_7385_ASG_INV_PEOPLE_GROUP');
931: hr_multi_message.add
932: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
933: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
934: );
934: );
935: --
936: else
937: close csr_valid_people_group;
938: hr_utility.set_location(l_proc, 50);
939: --
940: -- Check that the enabled flag is set to 'Y' for the people group.
941: --
942: If l_enabled_flag <> 'Y' then
940: -- Check that the enabled flag is set to 'Y' for the people group.
941: --
942: If l_enabled_flag <> 'Y' then
943: --
944: hr_utility.set_message(801, 'HR_51252_ASG_INV_PGP_ENBD_FLAG');
945: hr_multi_message.add
946: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
947: );
948: --
947: );
948: --
949: end if;
950: end if;
951: hr_utility.set_location(l_proc, 60);
952: --
953: -- Check that the id_flex_num value for the
954: -- people_group_id can be cross referenced to the
955: -- people_group_structure on per_business_groups for
958: open csr_valid_id_flex_num;
959: fetch csr_valid_id_flex_num into l_exists;
960: if csr_valid_id_flex_num%notfound then
961: close csr_valid_id_flex_num;
962: hr_utility.set_message(801, 'HR_7386_ASG_INV_PEOP_GRP_LINK');
963: hr_multi_message.add
964: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
965: );
966: --
966: --
967: else
968: close csr_valid_id_flex_num;
969: end if;
970: hr_utility.set_location(l_proc, 70);
971: --
972: end if;
973: --
974: end if;
971: --
972: end if;
973: --
974: end if;
975: hr_utility.set_location(' Leaving:'|| l_proc, 100);
976: --
977: end chk_people_group_id;
978: --
979: -- ---------------------------------------------------------------------------
994: l_proc varchar2(72) := g_package||'chk_perf_review_period_freq';
995: l_api_updating boolean;
996: --
997: begin
998: hr_utility.set_location('Entering:'|| l_proc, 10);
999: --
1000: -- Check mandatory parameters have been set
1001: --
1002: hr_api.mandatory_arg_error
1015: (p_api_name => l_proc
1016: ,p_argument => 'validation_end_date'
1017: ,p_argument_value => p_validation_end_date
1018: );
1019: hr_utility.set_location(l_proc, 20);
1020: --
1021: -- Only proceed with validation if :
1022: -- a) The current g_old_rec is current and
1023: -- b) The value for performance review period frequency has changed
1026: (p_assignment_id => p_assignment_id
1027: ,p_effective_date => p_effective_date
1028: ,p_object_version_number => p_object_version_number
1029: );
1030: hr_utility.set_location(l_proc, 30);
1031: --
1032: if ((l_api_updating and
1033: nvl(per_asg_shd.g_old_rec.perf_review_period_frequency,
1034: hr_api.g_varchar2) <> nvl(p_perf_review_period_frequency,
1035: hr_api.g_varchar2))
1036: or
1037: (NOT l_api_updating))
1038: then
1039: hr_utility.set_location(l_proc, 40);
1040: --
1041: -- Check if performance review period frequency is set
1042: --
1043: if p_perf_review_period_frequency is not null then
1046: -- or benefit or offer assignment.
1047: --
1048: if p_assignment_type not in ('E','A','B','O') then
1049: --
1050: hr_utility.set_message(801, 'HR_51178_ASG_INV_ASG_TYP_PRPF');
1051: hr_multi_message.add
1052: (p_associated_column1 =>
1053: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1054: );
1053: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1054: );
1055: --
1056: end if;
1057: hr_utility.set_location(l_proc, 50);
1058: --
1059: -- Check that the performance review period frequency exists in
1060: -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
1061: -- flag set to 'Y' and that the effective start date of the
1070: ,p_lookup_code => p_perf_review_period_frequency
1071: )
1072: then
1073: --
1074: hr_utility.set_message(801, 'HR_51149_ASG_INV_PRP_FREQ');
1075: hr_multi_message.add
1076: (p_associated_column1 =>
1077: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1078: );
1077: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1078: );
1079: --
1080: end if;
1081: hr_utility.set_location(l_proc, 60);
1082: --
1083: end if;
1084: hr_utility.set_location(l_proc, 70);
1085: --
1080: end if;
1081: hr_utility.set_location(l_proc, 60);
1082: --
1083: end if;
1084: hr_utility.set_location(l_proc, 70);
1085: --
1086: end if;
1087: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1088: --
1083: end if;
1084: hr_utility.set_location(l_proc, 70);
1085: --
1086: end if;
1087: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1088: --
1089: end chk_perf_review_period_freq;
1090: --
1091: -- ---------------------------------------------------------------------------
1104: l_proc varchar2(72) := g_package||'chk_perf_review_period';
1105: l_api_updating boolean;
1106: --
1107: begin
1108: hr_utility.set_location('Entering:'|| l_proc, 1);
1109: --
1110: -- Check mandatory parameters have been set
1111: --
1112: hr_api.mandatory_arg_error
1123: (p_assignment_id => p_assignment_id
1124: ,p_effective_date => p_effective_date
1125: ,p_object_version_number => p_object_version_number);
1126: --
1127: hr_utility.set_location(l_proc, 2);
1128: --
1129: if ((l_api_updating and
1130: nvl(per_asg_shd.g_old_rec.perf_review_period,
1131: hr_api.g_number) <> nvl(p_perf_review_period,
1132: hr_api.g_number))
1133: or (NOT l_api_updating))
1134: then
1135: --
1136: hr_utility.set_location(l_proc, 3);
1137: --
1138: -- Check if perf review period is not null
1139: --
1140: if p_perf_review_period is not null then
1143: -- Benefit or Offer assignment.
1144: --
1145: if p_assignment_type not in ('E','A','B','O') then
1146: --
1147: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1148: hr_utility.raise_error;
1149: end if;
1150: hr_utility.set_location(l_proc, 4);
1151: --
1144: --
1145: if p_assignment_type not in ('E','A','B','O') then
1146: --
1147: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1148: hr_utility.raise_error;
1149: end if;
1150: hr_utility.set_location(l_proc, 4);
1151: --
1152: end if;
1146: --
1147: hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1148: hr_utility.raise_error;
1149: end if;
1150: hr_utility.set_location(l_proc, 4);
1151: --
1152: end if;
1153: --
1154: end if;
1152: end if;
1153: --
1154: end if;
1155: --
1156: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1157: exception
1158: when app_exception.application_exception then
1159: if hr_multi_message.exception_add
1160: (p_associated_column1 =>
1159: if hr_multi_message.exception_add
1160: (p_associated_column1 =>
1161: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1162: ) then
1163: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1164: raise;
1165: end if;
1166: hr_utility.set_location(' Leaving:'|| l_proc, 7);
1167: end chk_perf_review_period;
1162: ) then
1163: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1164: raise;
1165: end if;
1166: hr_utility.set_location(' Leaving:'|| l_proc, 7);
1167: end chk_perf_review_period;
1168: --
1169: -- ---------------------------------------------------------------------------
1170: -- |-------------------< chk_perf_rp_freq_perf_rp >--------------------------|
1182: l_proc varchar2(72):= g_package||'chk_perf_rp_freq_perf_rp';
1183: l_api_updating boolean;
1184: --
1185: begin
1186: hr_utility.set_location('Entering:'|| l_proc, 1);
1187: --
1188: if hr_multi_message.no_exclusive_error
1189: (p_check_column1 =>
1190: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1221: hr_api.g_number) <> nvl(p_perf_review_period, hr_api.g_number)))
1222: or
1223: (NOT l_api_updating)) then
1224: --
1225: hr_utility.set_location(l_proc, 2);
1226: --
1227: -- Check if perf review period frequency or perf review period is not null.
1228: --
1229: if p_perf_review_period_frequency is not null
1227: -- Check if perf review period frequency or perf review period is not null.
1228: --
1229: if p_perf_review_period_frequency is not null
1230: or p_perf_review_period is not null then
1231: hr_utility.set_location(l_proc, 3);
1232: --
1233: -- Check if perf review period frequency or perf review period are null.
1234: --
1235: if p_perf_review_period_frequency is null
1234: --
1235: if p_perf_review_period_frequency is null
1236: or p_perf_review_period is null then
1237: --
1238: hr_utility.set_message(801, 'HR_51163_ASG_INV_PRPF_PRP_COMB');
1239: hr_utility.raise_error;
1240: --
1241: end if;
1242: --
1235: if p_perf_review_period_frequency is null
1236: or p_perf_review_period is null then
1237: --
1238: hr_utility.set_message(801, 'HR_51163_ASG_INV_PRPF_PRP_COMB');
1239: hr_utility.raise_error;
1240: --
1241: end if;
1242: --
1243: end if;
1244: --
1245: end if;
1246: end if;
1247: --
1248: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1249: --
1250: exception
1251: when app_exception.application_exception then
1252: if hr_multi_message.exception_add
1253: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1254: ,p_associated_column2 =>
1255: 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1256: ) then
1257: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1258: raise;
1259: end if;
1260: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1261: end chk_perf_rp_freq_perf_rp;
1256: ) then
1257: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1258: raise;
1259: end if;
1260: hr_utility.set_location(' Leaving:'|| l_proc, 6);
1261: end chk_perf_rp_freq_perf_rp;
1262: --
1263: -- ---------------------------------------------------------------------------
1264: -- |---------------------< chk_period_of_service_id >------------------------|
1291: between date_start
1292: and nvl(actual_termination_date, hr_api.g_eot);
1293: --
1294: begin
1295: hr_utility.set_location('Entering:'|| l_proc, 10);
1296: --
1297: -- Check mandatory parameters have been set
1298: --
1299: hr_api.mandatory_arg_error
1319: ,p_argument => 'validation_end_date'
1320: ,p_argument_value => p_effective_date
1321: );
1322: --
1323: hr_utility.set_location(l_proc, 20);
1324: --
1325: -- Check if the assignment is being updated.
1326: --
1327: l_api_updating := per_asg_shd.api_updating
1328: (p_assignment_id => p_assignment_id
1329: ,p_effective_date => p_effective_date
1330: ,p_object_version_number => p_object_version_number
1331: );
1332: hr_utility.set_location(l_proc, 30);
1333: --
1334: if NOT l_api_updating then
1335: --
1336: hr_utility.set_location(l_proc, 40);
1332: hr_utility.set_location(l_proc, 30);
1333: --
1334: if NOT l_api_updating then
1335: --
1336: hr_utility.set_location(l_proc, 40);
1337: --
1338: -- Check that the assignment is an employee assignment.
1339: --
1340: if p_assignment_type <> 'E' then
1342: -- Check that period of service is not set
1343: --
1344: If p_period_of_service_id is not null then
1345: --
1346: hr_utility.set_message(801, 'HR_51203_ASG_INV_ASG_TYP_PDS');
1347: hr_multi_message.add
1348: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1349: );
1350: --
1348: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1349: );
1350: --
1351: end if;
1352: hr_utility.set_location(l_proc, 50);
1353: --
1354: else
1355: --
1356: -- Check the mandatory parameter period of service for
1360: (p_api_name => l_proc
1361: ,p_argument => 'period_of_service_id'
1362: ,p_argument_value => p_period_of_service_id
1363: );
1364: hr_utility.set_location(l_proc, 60);
1365: --
1366: -- Check if the period of service exists between
1367: -- the period of service date start and actual termination date.
1368: --
1369: open csr_valid_pds;
1370: fetch csr_valid_pds into l_business_group_id, l_actual_termination_date;
1371: if csr_valid_pds%notfound then
1372: close csr_valid_pds;
1373: hr_utility.set_message(801, 'HR_7391_ASG_INV_PERIOD_OF_SERV');
1374: hr_multi_message.add
1375: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1376: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1377: );
1378: --
1379: else
1380: close csr_valid_pds;
1381: end if;
1382: hr_utility.set_location(l_proc, 70);
1383: --
1384: -- Check that the period of service is in the same business group
1385: -- as the business group of the assignment.
1386: --
1385: -- as the business group of the assignment.
1386: --
1387: If p_business_group_id <> l_business_group_id then
1388: --
1389: hr_utility.set_message(801, 'HR_51320_ASG_INV_PDS_BG');
1390: hr_multi_message.add
1391: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1392: );
1393: --
1391: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1392: );
1393: --
1394: end if;
1395: hr_utility.set_location(l_proc, 80);
1396: --
1397: -- Check if the period of service has been closed before the
1398: -- validation end date.
1399: --
1398: -- validation end date.
1399: --
1400: If p_validation_end_date > nvl(l_actual_termination_date, hr_api.g_eot) then
1401: --
1402: hr_utility.set_message(801, 'HR_6434_EMP_ASS_PER_CLOSED');
1403: hr_multi_message.add
1404: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1405: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1406: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
1406: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
1407: );
1408: --
1409: end if;
1410: hr_utility.set_location(l_proc, 90);
1411: --
1412: end if;
1413: --
1414: end if;
1411: --
1412: end if;
1413: --
1414: end if;
1415: hr_utility.set_location(' Leaving:'|| l_proc, 8);
1416: end chk_period_of_service_id;
1417: --
1418: -- ---------------------------------------------------------------------------
1419: -- |--------------------------< chk_person_id >------------------------------|
1438: and ppf.effective_end_date;
1439: --
1440: --
1441: begin
1442: hr_utility.set_location('Entering:'|| l_proc, 1);
1443: --
1444: -- Check mandatory parameters have been set
1445: --
1446: hr_api.mandatory_arg_error
1448: ,p_argument => 'person_id'
1449: ,p_argument_value => p_person_id
1450: );
1451: --
1452: hr_utility.set_location(l_proc, 2);
1453: --
1454: -- Check that person business group is the same as
1455: -- the assignment business group
1456: --
1457: open csr_get_bus_grp;
1458: fetch csr_get_bus_grp into l_business_group_id;
1459: if l_business_group_id <> p_business_group_id then
1460: close csr_get_bus_grp;
1461: hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
1462: hr_utility.raise_error;
1463: end if;
1464: close csr_get_bus_grp;
1465: --
1458: fetch csr_get_bus_grp into l_business_group_id;
1459: if l_business_group_id <> p_business_group_id then
1460: close csr_get_bus_grp;
1461: hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
1462: hr_utility.raise_error;
1463: end if;
1464: close csr_get_bus_grp;
1465: --
1466: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1462: hr_utility.raise_error;
1463: end if;
1464: close csr_get_bus_grp;
1465: --
1466: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1467: exception
1468: when app_exception.application_exception then
1469: if hr_multi_message.exception_add
1470: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1468: when app_exception.application_exception then
1469: if hr_multi_message.exception_add
1470: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1471: ) then
1472: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1473: raise;
1474: end if;
1475: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1476: end chk_person_id;
1471: ) then
1472: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1473: raise;
1474: end if;
1475: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1476: end chk_person_id;
1477: --
1478: -- ---------------------------------------------------------------------------
1479: -- |---------------------< chk_person_referred_by_id >-----------------------|
1507: between effective_start_date
1508: and effective_end_date;
1509: --
1510: begin
1511: hr_utility.set_location('Entering:'|| l_proc, 10);
1512: --
1513: -- Check mandatory parameters have been set
1514: --
1515: hr_api.mandatory_arg_error
1529: ,p_argument => 'validation_end_date'
1530: ,p_argument_value => p_validation_end_date
1531: );
1532: --
1533: hr_utility.set_location(l_proc, 20);
1534: --
1535: -- Only proceed with validation if :
1536: -- a) The current g_old_rec is current and
1537: -- b) The value for person referred by has changed
1539: l_api_updating := per_asg_shd.api_updating
1540: (p_assignment_id => p_assignment_id
1541: ,p_effective_date => p_effective_date
1542: ,p_object_version_number => p_object_version_number);
1543: hr_utility.set_location(l_proc, 30);
1544: --
1545: if ((l_api_updating and
1546: nvl(per_asg_shd.g_old_rec.person_referred_by_id, hr_api.g_number)
1547: <> nvl(p_person_referred_by_id, hr_api.g_number)) or
1545: if ((l_api_updating and
1546: nvl(per_asg_shd.g_old_rec.person_referred_by_id, hr_api.g_number)
1547: <> nvl(p_person_referred_by_id, hr_api.g_number)) or
1548: (NOT l_api_updating)) then
1549: hr_utility.set_location(l_proc, 40);
1550: --
1551: -- Check if person referred by is not null
1552: --
1553: if p_person_referred_by_id is not null then
1555: -- Check that the assignment is not an applicant or Offer assignment.
1556: --
1557: if p_assignment_type in ('E','B','C')then
1558: --
1559: hr_utility.set_message(801, 'HR_51224_ASG_INV_ASG_TYP_PRB');
1560: hr_multi_message.add
1561: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1562: );
1563: --
1561: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1562: );
1563: --
1564: end if;
1565: hr_utility.set_location(l_proc, 50);
1566: --
1567: -- Check that the person referred by is'nt the same as the person
1568: -- of the assignment.
1569: --
1568: -- of the assignment.
1569: --
1570: If p_person_referred_by_id = p_person_id then
1571: --
1572: hr_utility.set_message(801, 'HR_51304_ASG_APL_EQUAL_PRB');
1573: hr_multi_message.add
1574: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1575: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1576: );
1575: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1576: );
1577: --
1578: end if;
1579: hr_utility.set_location(l_proc, 60);
1580: --
1581: -- Check if the person referred by exists where the effective
1582: -- start date of the assignment is between the effective start
1583: -- date and effective end date of the person referred by.
1593: --
1594: if p_assignment_type <> 'O'
1595: then
1596: --
1597: hr_utility.set_message(801, 'HR_51302_ASG_INV_PER_REF_BY');
1598: hr_multi_message.add
1599: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1600: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1601: );
1604: --
1605: else
1606: close csr_val_prb_id;
1607: end if;
1608: hr_utility.set_location(l_proc, 70);
1609: --
1610: -- Check that the person referred by is in the same business group
1611: -- as the business group of the assignment.
1612: --
1613: If (p_business_group_id <> l_business_group_id AND
1614: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
1615: then
1616: --
1617: hr_utility.set_message(801, 'HR_51303_ASG_INV_PER_REF_BY_BG');
1618: hr_multi_message.add
1619: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1620: );
1621: --
1619: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1620: );
1621: --
1622: end if;
1623: hr_utility.set_location(l_proc, 80);
1624: --
1625: -- Check that the person referred by is an employee.
1626: --
1627: -- Bug 3190625
1630: -- no errors shown
1631: if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
1632: ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
1633: nvl(l_current_npw_flag, 'N') = 'Y') ) then
1634: hr_utility.set_message(801, 'HR_51305_ASG_PER_RB_NOT_EMP');
1635: hr_multi_message.add
1636: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID');
1637: --
1638: end if;
1635: hr_multi_message.add
1636: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID');
1637: --
1638: end if;
1639: hr_utility.set_location(l_proc, 90);
1640: --
1641: end if;
1642: --
1643: end if;
1641: end if;
1642: --
1643: end if;
1644: --
1645: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1646: end chk_person_referred_by_id;
1647: --
1648: -- ---------------------------------------------------------------------------
1649: -- |------------------------< chk_position_id >------------------------------|
1685: and ps.shared_type_id = hp.availability_status_id
1686: and ps.system_type_cd = 'ACTIVE' ;
1687: --
1688: begin
1689: hr_utility.set_location('Entering:'|| l_proc, 10);
1690: --
1691: --
1692: -- Check mandatory parameters have been set
1693: --
1713: (p_api_name => l_proc
1714: ,p_argument => 'business_group_id'
1715: ,p_argument_value => p_business_group_id
1716: );
1717: hr_utility.set_location(l_proc, 20);
1718: --
1719: -- Only proceed with validation if :
1720: -- a) The current g_old_rec is current and
1721: -- b) The value for position_id has changed
1730: nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
1731: nvl(p_position_id, hr_api.g_number))
1732: or
1733: (NOT l_api_updating)) then
1734: hr_utility.set_location(l_proc, 30);
1735: --
1736: -- Check that if the value for position_id is not null
1737: -- then it exists date effective in HR_POSITIONS
1738: --
1743: open csr_valid_pos;
1744: fetch csr_valid_pos into l_pos_bus_group_id;
1745: if csr_valid_pos%notfound then
1746: close csr_valid_pos;
1747: hr_utility.set_message(801, 'HR_51000_ASG_INVALID_POS');
1748: hr_multi_message.add
1749: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1750: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1751: );
1751: );
1752: else
1753: close csr_valid_pos;
1754: end if;
1755: hr_utility.set_location(l_proc, 40);
1756: --
1757: -- Check if the business_group_id for the assignment matches
1758: -- the business_group_id in HR_POSITIONS date effectively.
1759: --
1758: -- the business_group_id in HR_POSITIONS date effectively.
1759: --
1760: if l_pos_bus_group_id <> p_business_group_id then
1761: --
1762: hr_utility.set_message(801, 'HR_51009_ASG_INVALID_BG_POS');
1763: hr_multi_message.add
1764: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1765: );
1766: --
1764: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1765: );
1766: --
1767: end if;
1768: hr_utility.set_location(l_proc, 50);
1769: --
1770: end if;
1771: --
1772: --
1771: --
1772: --
1773: end if;
1774: --
1775: hr_utility.set_location('Leaving'||l_proc, 80);
1776: end chk_position_id;
1777: --
1778: ------------------------------------------------------------------------------
1779: -------------------< chk_position_id_grade_id >-------------------------------
1820: and nvl(date_to, hr_api.g_eot);
1821: --
1822: begin
1823: --
1824: hr_utility.set_location('Entering:'|| l_proc, 1);
1825: --
1826: if hr_multi_message.no_exclusive_error
1827: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1828: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
1865: nvl(p_grade_id, hr_api.g_number))))
1866: or
1867: (NOT l_api_updating) then
1868: --
1869: hr_utility.set_location(l_proc, 2);
1870: --
1871: -- Check that position_id and grade_id both contain not null values
1872: --
1873: if p_position_id is not null and p_grade_id is not null then
1896: end if;
1897: --
1898: -- Bug 3566686 Ends Here
1899: --
1900: hr_utility.set_location(l_proc, 3);
1901: --
1902: end if;
1903: --
1904: end if;
1904: end if;
1905: end if;
1906: --
1907: p_inv_pos_grade_warning := l_inv_pos_grade_warning;
1908: hr_utility.set_location('Leaving'||l_proc, 4);
1909: end chk_position_id_grade_id;
1910: --
1911: ------------------------------------------------------------------------------
1912: --------------------------< chk_position_id_org_id >--------------------------
1945: and ps.system_type_cd = 'ACTIVE' ;
1946: --
1947: begin
1948: --
1949: hr_utility.set_location('Entering:'|| l_proc, 1);
1950: --
1951: if hr_multi_message.no_exclusive_error
1952: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1953: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
1991: nvl(p_organization_id, hr_api.g_number))))
1992: or
1993: (NOT l_api_updating) then
1994: --
1995: hr_utility.set_location(l_proc, 2);
1996: --
1997: -- Check if the position is null
1998: --
1999: If p_position_id is not null then
2000: --
2001: -- Check if assignment position_id and organization_id combination
2002: -- matches the combination in HR_POSITIONS.
2003: --
2004: hr_utility.set_location(l_proc, 3);
2005: open csr_valid_pos_org_comb;
2006: fetch csr_valid_pos_org_comb into l_exists;
2007: if csr_valid_pos_org_comb%notfound then
2008: close csr_valid_pos_org_comb;
2005: open csr_valid_pos_org_comb;
2006: fetch csr_valid_pos_org_comb into l_exists;
2007: if csr_valid_pos_org_comb%notfound then
2008: close csr_valid_pos_org_comb;
2009: hr_utility.set_message(801, 'HR_51055_ASG_INV_POS_ORG_COMB');
2010: hr_utility.raise_error;
2011: end if;
2012: close csr_valid_pos_org_comb;
2013: --
2006: fetch csr_valid_pos_org_comb into l_exists;
2007: if csr_valid_pos_org_comb%notfound then
2008: close csr_valid_pos_org_comb;
2009: hr_utility.set_message(801, 'HR_51055_ASG_INV_POS_ORG_COMB');
2010: hr_utility.raise_error;
2011: end if;
2012: close csr_valid_pos_org_comb;
2013: --
2014: end if;
2014: end if;
2015: end if;
2016: end if;
2017: --
2018: hr_utility.set_location('Leaving'||l_proc, 4);
2019: exception
2020: when app_exception.application_exception then
2021: if hr_multi_message.exception_add
2022: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2022: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2023: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
2024: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2025: ) then
2026: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2027: raise;
2028: end if;
2029: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2030: end chk_position_id_org_id;
2025: ) then
2026: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2027: raise;
2028: end if;
2029: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2030: end chk_position_id_org_id;
2031: --
2032: ------------------------------------------------------------------------------
2033: -------------------------< chk_position_id_job_id >---------------------------
2064: and ps.shared_type_id = hp.availability_status_id
2065: and ps.system_type_cd = 'ACTIVE' ;
2066: --
2067: begin
2068: hr_utility.set_location('Entering:'|| l_proc, 10);
2069: --
2070: if hr_multi_message.no_exclusive_error
2071: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2072: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2090: (p_api_name => l_proc
2091: ,p_argument => 'effective_date'
2092: ,p_argument_value => p_effective_date
2093: );
2094: hr_utility.set_location(l_proc, 20);
2095: --
2096: -- Only proceed with validation if :
2097: -- a) The current g_old_rec is current and
2098: -- b) The value for position or job has changed
2100: l_api_updating := per_asg_shd.api_updating
2101: (p_assignment_id => p_assignment_id
2102: ,p_effective_date => p_effective_date
2103: ,p_object_version_number => p_object_version_number);
2104: hr_utility.set_location(l_proc, 30);
2105: --
2106: if (l_api_updating and
2107: ((nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
2108: nvl(p_position_id, hr_api.g_number))
2111: nvl(p_job_id, hr_api.g_number))))
2112: or
2113: (NOT l_api_updating)
2114: then
2115: hr_utility.set_location(l_proc, 40);
2116: --
2117: -- Check if the assignment job and position are not null
2118: --
2119: if p_position_id is not null and p_job_id is not null then
2124: open csr_valid_pos_job_comb;
2125: fetch csr_valid_pos_job_comb into l_exists;
2126: if csr_valid_pos_job_comb%notfound then
2127: close csr_valid_pos_job_comb;
2128: hr_utility.set_message(801, 'HR_51056_ASG_INV_POS_JOB_COMB');
2129: hr_multi_message.add
2130: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2131: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2132: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2138: elsif p_job_id is null and p_position_id is not null then
2139: --
2140: -- Position is not null but job is null
2141: --
2142: hr_utility.set_message(801, 'HR_51057_ASG_JOB_NULL_VALUE');
2143: hr_multi_message.add
2144: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2145: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2146: );
2149: --
2150: end if;
2151: end if;
2152: --
2153: hr_utility.set_location('Leaving'||l_proc, 3);
2154: end chk_position_id_job_id;
2155: --
2156: -- ---------------------------------------------------------------------------
2157: -- |-------------------------< chk_primary_flag >----------------------------|
2271: and pas.primary_flag = 'Y');
2272:
2273: begin
2274: --
2275: hr_utility.set_location('Entering:'|| l_proc, 10);
2276: --
2277: --
2278: -- Check mandatory parameters have been set
2279: --
2300: (p_api_name => l_proc
2301: ,p_argument => 'validation_end_date'
2302: ,p_argument_value => p_validation_end_date
2303: );
2304: hr_utility.set_location(l_proc, 20);
2305: --
2306: -- Only proceed with validation if :
2307: -- a) The current g_old_rec is current and
2308: -- b) The value for primary flag has changed
2311: (p_assignment_id => p_assignment_id
2312: ,p_effective_date => p_effective_date
2313: ,p_object_version_number => p_object_version_number);
2314: --
2315: hr_utility.set_location(l_proc, 30);
2316: --
2317: if ((l_api_updating and
2318: nvl(per_asg_shd.g_old_rec.primary_flag, hr_api.g_varchar2) <>
2319: nvl(p_primary_flag, hr_api.g_varchar2)) or
2325: --
2326: per_asg_shd.constraint_error
2327: (p_constraint_name => 'PER_ASS_PRIMARY_FLAG_CHK');
2328: --
2329: hr_utility.set_location(l_proc, 30);
2330: --
2331: end if;
2332: --
2333: hr_utility.set_location(l_proc, 40);
2329: hr_utility.set_location(l_proc, 30);
2330: --
2331: end if;
2332: --
2333: hr_utility.set_location(l_proc, 40);
2334: --
2335: -- If inserting 'primary' assignment, check that no
2336: -- other primary assignments exist for the person
2337: -- the new assignment is linked to
2342: --
2343: if p_assignment_type = 'A'
2344: or p_assignment_type = 'O' then
2345: --
2346: hr_utility.set_message(801, 'HR_51198_ASG_INV_APL_ASG_PF');
2347: --
2348: hr_multi_message.add
2349: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG');
2350: --
2349: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG');
2350: --
2351: end if;
2352: --
2353: hr_utility.set_location(l_proc, 50);
2354: --
2355: -- Check that the effective end date is the end of time
2356: --
2357: If p_validation_end_date <> hr_api.g_eot then
2355: -- Check that the effective end date is the end of time
2356: --
2357: If p_validation_end_date <> hr_api.g_eot then
2358: --
2359: hr_utility.set_message(801, 'HR_51323_ASG_INV_PRIM_ASG_EED');
2360: --
2361: hr_multi_message.add
2362: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2363: --
2362: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2363: --
2364: end if;
2365: --
2366: hr_utility.set_location(l_proc, 60);
2367: --
2368: if hr_multi_message.no_exclusive_error
2369: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2370: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2368: if hr_multi_message.no_exclusive_error
2369: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2370: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2371: --
2372: hr_utility.set_location(l_proc, 70);
2373: --
2374: if p_assignment_type = 'C' then
2375: --
2376: hr_utility.set_location(l_proc, 80);
2372: hr_utility.set_location(l_proc, 70);
2373: --
2374: if p_assignment_type = 'C' then
2375: --
2376: hr_utility.set_location(l_proc, 80);
2377: --
2378: open csr_cwk_asg_exists;
2379: fetch csr_cwk_asg_exists into l_exists;
2380: --
2381: if csr_cwk_asg_exists%found then
2382: --
2383: close csr_cwk_asg_exists;
2384: --
2385: hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2386: --
2387: hr_multi_message.add
2388: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2389: ,p_associated_column2 =>
2394: close csr_cwk_asg_exists;
2395: --
2396: end if;
2397: --
2398: hr_utility.set_location(l_proc, 90);
2399: --
2400: elsif p_assignment_type = 'E' then
2401: --
2402: hr_utility.set_location(l_proc, 100);
2398: hr_utility.set_location(l_proc, 90);
2399: --
2400: elsif p_assignment_type = 'E' then
2401: --
2402: hr_utility.set_location(l_proc, 100);
2403: --
2404: -- Check if a primary assignment already exists
2405: --
2406: open csr_asg_exists;
2409: if csr_asg_exists%found then
2410: --
2411: close csr_asg_exists;
2412: --
2413: hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2414: --
2415: hr_multi_message.add
2416: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2417: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID');
2421: close csr_asg_exists;
2422: --
2423: end if;
2424: --
2425: hr_utility.set_location(l_proc, 110);
2426: --
2427: end if;
2428: --
2429: end if; -- no exclusive error
2427: end if;
2428: --
2429: end if; -- no exclusive error
2430: --
2431: hr_utility.set_location(l_proc, 120);
2432: --
2433: else
2434: --
2435: hr_utility.set_location(l_proc, 130);
2431: hr_utility.set_location(l_proc, 120);
2432: --
2433: else
2434: --
2435: hr_utility.set_location(l_proc, 130);
2436: --
2437: -- Check if the assignment is an employee assignment or a
2438: -- non payrolled worker assignment.
2439: --
2441: --
2442: -- Check that a primary employee assignment exists during
2443: -- the entire date range of the non-primary assignment.
2444: --
2445: hr_utility.set_location(l_proc, 140);
2446: --
2447: if hr_multi_message.no_exclusive_error
2448: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2449: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2447: if hr_multi_message.no_exclusive_error
2448: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2449: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2450: --
2451: hr_utility.set_location(l_proc, 150);
2452: --
2453: -- Check that the primary cwk assignment exists during
2454: -- the entire date range of the non-primary assignment
2455: --
2454: -- the entire date range of the non-primary assignment
2455: --
2456: if p_assignment_type = 'C' then
2457: --
2458: hr_utility.set_location(l_proc, 160);
2459: --
2460: open csr_ins_non_cwk_prim;
2461: fetch csr_ins_non_cwk_prim into l_exists;
2462: --
2463: if csr_ins_non_cwk_prim%notfound then
2464: --
2465: close csr_ins_non_cwk_prim;
2466: --
2467: hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2468: --
2469: hr_multi_message.add
2470: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2471: ,p_associated_column2 =>
2478: close csr_ins_non_cwk_prim;
2479: --
2480: end if;
2481: --
2482: hr_utility.set_location(l_proc, 170);
2483: --
2484: elsif p_assignment_type = 'E' then
2485: --
2486: hr_utility.set_location(l_proc, 180);
2482: hr_utility.set_location(l_proc, 170);
2483: --
2484: elsif p_assignment_type = 'E' then
2485: --
2486: hr_utility.set_location(l_proc, 180);
2487: --
2488: --
2489: -- 120.10 (START)
2490: --
2518: if csr_ins_non_prim%notfound then
2519: --
2520: close csr_ins_non_prim;
2521: --
2522: hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2523: --
2524: hr_multi_message.add
2525: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2526: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2532: close csr_ins_non_prim;
2533: --
2534: end if;
2535: --
2536: hr_utility.set_location(l_proc, 190);
2537: --
2538: end if;
2539: --
2540: end if; -- no exclusive error
2538: end if;
2539: --
2540: end if; -- no exclusive error
2541: --
2542: hr_utility.set_location(l_proc, 200);
2543: --
2544: end if;
2545: --
2546: end if;
2546: end if;
2547: --
2548: end if;
2549: --
2550: hr_utility.set_location(' Leaving:'|| l_proc, 999);
2551: --
2552: end chk_primary_flag;
2553: --
2554: -- ---------------------------------------------------------------------------
2565: l_proc varchar2(72) := g_package||'chk_probation_period';
2566: l_api_updating boolean;
2567: --
2568: begin
2569: hr_utility.set_location('Entering:'|| l_proc, 10);
2570: --
2571: hr_api.mandatory_arg_error
2572: (p_api_name => l_proc
2573: ,p_argument => 'effective_date'
2572: (p_api_name => l_proc
2573: ,p_argument => 'effective_date'
2574: ,p_argument_value => p_effective_date
2575: );
2576: hr_utility.set_location(l_proc, 20);
2577: --
2578: -- Only proceed with validation if :
2579: -- a) The current g_old_rec is current and
2580: -- b) The value for probation period has changed
2583: (p_assignment_id => p_assignment_id
2584: ,p_effective_date => p_effective_date
2585: ,p_object_version_number => p_object_version_number
2586: );
2587: hr_utility.set_location(l_proc, 30);
2588: --
2589: if ((l_api_updating and
2590: nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number)
2591: <> nvl(p_probation_period, hr_api.g_number))
2591: <> nvl(p_probation_period, hr_api.g_number))
2592: or
2593: (NOT l_api_updating))
2594: then
2595: hr_utility.set_location(l_proc, 40);
2596: --
2597: -- Check that if probation period is set then it's value
2598: -- is in the range 0 to 9999.99
2599: --
2602: if p_probation_period is not null
2603: and p_probation_period not between 0 and 9999.99
2604: then
2605: --
2606: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2607: hr_utility.raise_error;
2608: end if;
2609: hr_utility.set_location(l_proc, 50);
2610: --
2603: and p_probation_period not between 0 and 9999.99
2604: then
2605: --
2606: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2607: hr_utility.raise_error;
2608: end if;
2609: hr_utility.set_location(l_proc, 50);
2610: --
2611: end if;
2605: --
2606: hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2607: hr_utility.raise_error;
2608: end if;
2609: hr_utility.set_location(l_proc, 50);
2610: --
2611: end if;
2612: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2613: exception
2608: end if;
2609: hr_utility.set_location(l_proc, 50);
2610: --
2611: end if;
2612: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2613: exception
2614: when app_exception.application_exception then
2615: if hr_multi_message.exception_add
2616: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2614: when app_exception.application_exception then
2615: if hr_multi_message.exception_add
2616: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2617: ) then
2618: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2619: raise;
2620: end if;
2621: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2622: --
2617: ) then
2618: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2619: raise;
2620: end if;
2621: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2622: --
2623: end chk_probation_period;
2624: --
2625: -- ---------------------------------------------------------------------------
2639: l_proc varchar2(72) := g_package||'chk_probation_unit';
2640: l_api_updating boolean;
2641: --
2642: begin
2643: hr_utility.set_location('Entering:'|| l_proc, 10);
2644: --
2645: -- Check mandatory parameters have been set
2646: --
2647: hr_api.mandatory_arg_error
2660: (p_api_name => l_proc
2661: ,p_argument => 'validation_end_date'
2662: ,p_argument_value => p_validation_end_date
2663: );
2664: hr_utility.set_location(l_proc, 20);
2665: --
2666: -- Only proceed with validation if :
2667: -- a) The current g_old_rec is current and
2668: -- b) The value for probation unit has changed
2671: (p_assignment_id => p_assignment_id
2672: ,p_effective_date => p_effective_date
2673: ,p_object_version_number => p_object_version_number
2674: );
2675: hr_utility.set_location(l_proc, 30);
2676: --
2677: if ((l_api_updating and
2678: nvl(per_asg_shd.g_old_rec.probation_unit, hr_api.g_varchar2)
2679: <> nvl(p_probation_unit, hr_api.g_varchar2))
2679: <> nvl(p_probation_unit, hr_api.g_varchar2))
2680: or
2681: (NOT l_api_updating))
2682: then
2683: hr_utility.set_location(l_proc, 40);
2684: --
2685: -- Check if probation unit is not null
2686: --
2687: if p_probation_unit is not null then
2699: ,p_lookup_code => p_probation_unit
2700: )
2701: then
2702: --
2703: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2704: hr_utility.raise_error;
2705: --
2706: end if;
2707: hr_utility.set_location(l_proc, 50);
2700: )
2701: then
2702: --
2703: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2704: hr_utility.raise_error;
2705: --
2706: end if;
2707: hr_utility.set_location(l_proc, 50);
2708: end if;
2703: hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2704: hr_utility.raise_error;
2705: --
2706: end if;
2707: hr_utility.set_location(l_proc, 50);
2708: end if;
2709: --
2710: end if;
2711: --
2708: end if;
2709: --
2710: end if;
2711: --
2712: hr_utility.set_location(' Leaving:'|| l_proc, 60);
2713: exception
2714: when app_exception.application_exception then
2715: if hr_multi_message.exception_add
2716: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2714: when app_exception.application_exception then
2715: if hr_multi_message.exception_add
2716: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2717: ) then
2718: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2719: raise;
2720: end if;
2721: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2722: end chk_probation_unit;
2717: ) then
2718: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2719: raise;
2720: end if;
2721: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2722: end chk_probation_unit;
2723: --
2724: -- ---------------------------------------------------------------------------
2725: -- |-------------------< chk_prob_unit_prob_period >-------------------------|
2737: l_proc varchar2(72):= g_package||'chk_prob_unit_prob_period';
2738: l_api_updating boolean;
2739: --
2740: begin
2741: hr_utility.set_location('Entering:'|| l_proc, 1);
2742: --
2743: -- Check mandatory parameters have been set
2744: --
2745: if hr_multi_message.no_exclusive_error
2770: <> nvl(p_probation_period, hr_api.g_number))
2771: or
2772: (NOT l_api_updating)) then
2773: --
2774: hr_utility.set_location(l_proc, 2);
2775: --
2776: -- Check if probation unit or probation period is not null.
2777: --
2778: if p_probation_unit is not null or p_probation_period is not null then
2780: -- Check if probation unit or probation period are null.
2781: --
2782: if p_probation_unit is null or p_probation_period is null then
2783: --
2784: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2785: hr_utility.raise_error;
2786: --
2787: end if;
2788: hr_utility.set_location(l_proc, 3);
2781: --
2782: if p_probation_unit is null or p_probation_period is null then
2783: --
2784: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2785: hr_utility.raise_error;
2786: --
2787: end if;
2788: hr_utility.set_location(l_proc, 3);
2789: --
2784: hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2785: hr_utility.raise_error;
2786: --
2787: end if;
2788: hr_utility.set_location(l_proc, 3);
2789: --
2790: end if;
2791: --
2792: end if;
2791: --
2792: end if;
2793: end if;
2794: --
2795: hr_utility.set_location(' Leaving:'|| l_proc, 4);
2796: exception
2797: when app_exception.application_exception then
2798: if hr_multi_message.exception_add
2799: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2798: if hr_multi_message.exception_add
2799: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2800: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2801: ) then
2802: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2803: raise;
2804: end if;
2805: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2806: --
2801: ) then
2802: hr_utility.set_location(' Leaving:'|| l_proc, 5);
2803: raise;
2804: end if;
2805: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2806: --
2807: end chk_prob_unit_prob_period;
2808: --
2809: -- ---------------------------------------------------------------------------
2840: between effective_start_date
2841: and nvl(effective_end_date, hr_api.g_eot);
2842: --
2843: begin
2844: hr_utility.set_location('Entering:'|| l_proc, 10);
2845: --
2846: --
2847: -- Check mandatory parameters have been set
2848: --
2850: (p_api_name => l_proc
2851: ,p_argument => 'effective_date'
2852: ,p_argument_value => p_effective_date
2853: );
2854: hr_utility.set_location(l_proc, 20);
2855: --
2856: hr_api.mandatory_arg_error
2857: (p_api_name => l_proc
2858: ,p_argument => 'validation_start_date'
2857: (p_api_name => l_proc
2858: ,p_argument => 'validation_start_date'
2859: ,p_argument_value => p_validation_start_date
2860: );
2861: hr_utility.set_location(l_proc, 30);
2862: --
2863: hr_api.mandatory_arg_error
2864: (p_api_name => l_proc
2865: ,p_argument => 'validation_end_date'
2864: (p_api_name => l_proc
2865: ,p_argument => 'validation_end_date'
2866: ,p_argument_value => p_validation_end_date
2867: );
2868: hr_utility.set_location(l_proc, 40);
2869: --
2870: -- Only proceed with validation if :
2871: -- a) The current g_old_rec is current and
2872: -- b) The value for recruiter has changed
2874: l_api_updating := per_asg_shd.api_updating
2875: (p_assignment_id => p_assignment_id
2876: ,p_effective_date => p_effective_date
2877: ,p_object_version_number => p_object_version_number);
2878: hr_utility.set_location(l_proc, 50);
2879: --
2880: if ((l_api_updating and
2881: nvl(per_asg_shd.g_old_rec.recruiter_id, hr_api.g_number) <>
2882: nvl(p_recruiter_id, hr_api.g_number)) or
2880: if ((l_api_updating and
2881: nvl(per_asg_shd.g_old_rec.recruiter_id, hr_api.g_number) <>
2882: nvl(p_recruiter_id, hr_api.g_number)) or
2883: (NOT l_api_updating)) then
2884: hr_utility.set_location(l_proc, 60);
2885: --
2886: -- Check if recruiter is not null
2887: --
2888: if p_recruiter_id is not null then
2891: --
2892: if p_assignment_type in ('E','C','B')
2893: then
2894: --
2895: hr_utility.set_message(801, 'HR_51216_ASG_INV_ASG_TYP_REC');
2896: hr_multi_message.add
2897: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2898: );
2899: --
2897: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2898: );
2899: --
2900: end if;
2901: hr_utility.set_location(l_proc, 70);
2902: --
2903: -- Check that the recruiter is'nt the same person as the assignment
2904: -- person.
2905: --
2904: -- person.
2905: --
2906: If p_recruiter_id = p_person_id then
2907: --
2908: hr_utility.set_message(801, 'HR_51289_ASG_APL_EQUAL_RECRUIT');
2909: hr_multi_message.add
2910: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2911: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2912: );
2911: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2912: );
2913: --
2914: end if;
2915: hr_utility.set_location(l_proc, 80);
2916: --
2917: -- Check if the recruiter exists between the effective start date
2918: -- and effective end date of the assignment.
2919: --
2921: fetch csr_val_recruiter
2922: into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
2923: if csr_val_recruiter%notfound then
2924: close csr_val_recruiter;
2925: hr_utility.set_message(801, 'HR_51280_ASG_INV_RECRUIT_ID');
2926: hr_multi_message.add
2927: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2928: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2929: );
2931: else
2932: close csr_val_recruiter;
2933: end if;
2934: --
2935: hr_utility.set_location(l_proc, 90);
2936: --
2937: -- Check that the recruiter is an employee.
2938: --
2939: -- Bug 3190625
2940: -- Condition to check profile value also added
2941: if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
2942: ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
2943: nvl(l_current_npw_flag, 'N') = 'Y') ) then
2944: hr_utility.set_message(801, 'HR_51290_ASG_RECRUIT_NOT_EMP');
2945: hr_multi_message.add
2946: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID');
2947: end if;
2948: hr_utility.set_location(l_proc, 100);
2944: hr_utility.set_message(801, 'HR_51290_ASG_RECRUIT_NOT_EMP');
2945: hr_multi_message.add
2946: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID');
2947: end if;
2948: hr_utility.set_location(l_proc, 100);
2949: --
2950: -- Check that the recruiter is in the same business group
2951: -- as the business group of the applicant assignment.
2952: --
2953: If (p_business_group_id <> l_business_group_id AND
2954: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'N')
2955: then
2956: --
2957: hr_utility.set_message(801, 'HR_51284_ASG_INV_RECRUIT_BG');
2958: hr_multi_message.add
2959: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2960: );
2961: --
2959: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2960: );
2961: --
2962: end if;
2963: hr_utility.set_location(l_proc, 110);
2964: --
2965: end if;
2966: --
2967: end if;
2964: --
2965: end if;
2966: --
2967: end if;
2968: hr_utility.set_location(' Leaving:'||l_proc, 140);
2969: --
2970: end chk_recruiter_id;
2971: --
2972: -- ---------------------------------------------------------------------------
2998: between date_start
2999: and nvl(date_end, hr_api.g_eot);
3000: --
3001: begin
3002: hr_utility.set_location('Entering:'|| l_proc, 10);
3003: --
3004: -- Check mandatory parameters have been set
3005: --
3006: hr_api.mandatory_arg_error
3019: (p_api_name => l_proc
3020: ,p_argument => 'validation_end_date'
3021: ,p_argument_value => p_validation_end_date
3022: );
3023: hr_utility.set_location(l_proc, 20);
3024: --
3025: -- Only proceed with validation if :
3026: -- a) The current g_old_rec is current and
3027: -- b) The value for recruitment activity has changed
3030: (p_assignment_id => p_assignment_id
3031: ,p_effective_date => p_effective_date
3032: ,p_object_version_number => p_object_version_number
3033: );
3034: hr_utility.set_location(l_proc, 30);
3035: --
3036: if ((l_api_updating and
3037: nvl(per_asg_shd.g_old_rec.recruitment_activity_id, hr_api.g_number) <>
3038: nvl(p_recruitment_activity_id, hr_api.g_number)) or
3037: nvl(per_asg_shd.g_old_rec.recruitment_activity_id, hr_api.g_number) <>
3038: nvl(p_recruitment_activity_id, hr_api.g_number)) or
3039: (NOT l_api_updating))
3040: then
3041: hr_utility.set_location(l_proc, 40);
3042: --
3043: -- Check if recruitment activity is not null
3044: --
3045: if p_recruitment_activity_id is not null then
3047: -- Check that the assignment is not an applicant or offer assignment.
3048: --
3049: if p_assignment_type in ('E','C','B') then
3050: --
3051: hr_utility.set_message(801, 'HR_51223_ASG_INV_ASG_TYP_RCAT');
3052: hr_multi_message.add
3053: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3054: );
3055: --
3053: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3054: );
3055: --
3056: end if;
3057: hr_utility.set_location(l_proc, 50);
3058: --
3059: -- Check if the recruitment activity exists where the effective
3060: -- start date of the assignment is between the date start and
3061: -- date end of the recruitment activity.
3068: -- Do not throw an error for Offer Assignment.
3069: --
3070: if p_assignment_type <> 'O'
3071: then
3072: hr_utility.set_message(801, 'HR_51306_ASG_INV_REC_ACT');
3073: hr_multi_message.add
3074: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3075: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3076: );
3080: else
3081: close csr_val_rec_act_id;
3082: end if;
3083: --
3084: hr_utility.set_location(l_proc, 60);
3085: --
3086: -- Check that the recruitment activity is in the same business group
3087: -- as the business group of the assignment.
3088: --
3087: -- as the business group of the assignment.
3088: --
3089: If p_business_group_id <> l_business_group_id then
3090: --
3091: hr_utility.set_message(801, 'HR_51307_ASG_INV_REC_ACT_BG');
3092: hr_multi_message.add
3093: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3094: );
3095: --
3093: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3094: );
3095: --
3096: end if;
3097: hr_utility.set_location(l_proc, 70);
3098: --
3099: end if;
3100: --
3101: end if;
3099: end if;
3100: --
3101: end if;
3102: --
3103: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3104: end chk_recruitment_activity_id;
3105: --
3106: -- ---------------------------------------------------------------------------
3107: -- |-------------------------< chk_ref_int_del >-----------------------------|
3259: from hr_assignment_set_amendments
3260: where assignment_id = p_assignment_id);
3261: --
3262: begin
3263: hr_utility.set_location('Entering:'|| l_proc, 1);
3264: --
3265: -- Check that no child records exist for the
3266: -- assignment on per_events when the assignment is
3267: -- deleted
3269: open csr_per_events;
3270: fetch csr_per_events into l_exists;
3271: if csr_per_events%found then
3272: close csr_per_events;
3273: hr_utility.set_message(801,'HR_7400_ASG_NO_DEL_ASS_EVENTS');
3274: hr_utility.raise_error;
3275: end if;
3276: close csr_per_events;
3277: --
3270: fetch csr_per_events into l_exists;
3271: if csr_per_events%found then
3272: close csr_per_events;
3273: hr_utility.set_message(801,'HR_7400_ASG_NO_DEL_ASS_EVENTS');
3274: hr_utility.raise_error;
3275: end if;
3276: close csr_per_events;
3277: --
3278: hr_utility.set_location(l_proc, 2);
3274: hr_utility.raise_error;
3275: end if;
3276: close csr_per_events;
3277: --
3278: hr_utility.set_location(l_proc, 2);
3279: --
3280: -- Check that no child records exist for the
3281: -- assignment on per_letter_request_lines when
3282: -- the assignment is deleted
3285: /*open csr_per_lett_req_lines;
3286: fetch csr_per_lett_req_lines into l_exists;
3287: if csr_per_lett_req_lines%found then
3288: close csr_per_lett_req_lines;
3289: hr_utility.set_message(801,'HR_7401_ASG_NO_DEL_ASS_LET_REQ');
3290: hr_utility.raise_error;
3291: end if;
3292: close csr_per_lett_req_lines;*/
3293: -- End of 3096114
3286: fetch csr_per_lett_req_lines into l_exists;
3287: if csr_per_lett_req_lines%found then
3288: close csr_per_lett_req_lines;
3289: hr_utility.set_message(801,'HR_7401_ASG_NO_DEL_ASS_LET_REQ');
3290: hr_utility.raise_error;
3291: end if;
3292: close csr_per_lett_req_lines;*/
3293: -- End of 3096114
3294: --
3291: end if;
3292: close csr_per_lett_req_lines;*/
3293: -- End of 3096114
3294: --
3295: hr_utility.set_location(l_proc, 3);
3296: --
3297: -- Check that no child records exist for the
3298: -- assignment on pay_cost_allocations_f when
3299: -- the assignment is deleted
3301: open csr_pay_cost_allocations_f;
3302: fetch csr_pay_cost_allocations_f into l_exists;
3303: if csr_pay_cost_allocations_f%found then
3304: close csr_pay_cost_allocations_f;
3305: hr_utility.set_message(801,'HR_7402_ASG_NO_DEL_COST_ALLOCS');
3306: hr_utility.raise_error;
3307: end if;
3308: close csr_pay_cost_allocations_f;
3309: --
3302: fetch csr_pay_cost_allocations_f into l_exists;
3303: if csr_pay_cost_allocations_f%found then
3304: close csr_pay_cost_allocations_f;
3305: hr_utility.set_message(801,'HR_7402_ASG_NO_DEL_COST_ALLOCS');
3306: hr_utility.raise_error;
3307: end if;
3308: close csr_pay_cost_allocations_f;
3309: --
3310: hr_utility.set_location(l_proc, 4);
3306: hr_utility.raise_error;
3307: end if;
3308: close csr_pay_cost_allocations_f;
3309: --
3310: hr_utility.set_location(l_proc, 4);
3311: --
3312: -- Check that no child records exist for the
3313: -- assignment on pay_personal_payment_methods when
3314: -- the assignment is deleted
3316: open csr_pay_pers_payment_methods;
3317: fetch csr_pay_pers_payment_methods into l_exists;
3318: if csr_pay_pers_payment_methods%found then
3319: close csr_pay_pers_payment_methods;
3320: hr_utility.set_message(801,'HR_7404_ASG_NO_DEL_PER_PAY_MET');
3321: hr_utility.raise_error;
3322: end if;
3323: close csr_pay_pers_payment_methods;
3324: --
3317: fetch csr_pay_pers_payment_methods into l_exists;
3318: if csr_pay_pers_payment_methods%found then
3319: close csr_pay_pers_payment_methods;
3320: hr_utility.set_message(801,'HR_7404_ASG_NO_DEL_PER_PAY_MET');
3321: hr_utility.raise_error;
3322: end if;
3323: close csr_pay_pers_payment_methods;
3324: --
3325: hr_utility.set_location(l_proc, 5);
3321: hr_utility.raise_error;
3322: end if;
3323: close csr_pay_pers_payment_methods;
3324: --
3325: hr_utility.set_location(l_proc, 5);
3326: --
3327: -- Check that no child records exist for the
3328: -- assignment on pay_payroll_actions when
3329: -- the assignment is deleted
3331: open csr_pay_assignment_actions;
3332: fetch csr_pay_assignment_actions into l_exists;
3333: if csr_pay_assignment_actions%found then
3334: close csr_pay_assignment_actions;
3335: hr_utility.set_message(801,'HR_7403_ASG_NO_DEL_PAYROLL_ACT');
3336: hr_utility.raise_error;
3337: end if;
3338: close csr_pay_assignment_actions;
3339: --
3332: fetch csr_pay_assignment_actions into l_exists;
3333: if csr_pay_assignment_actions%found then
3334: close csr_pay_assignment_actions;
3335: hr_utility.set_message(801,'HR_7403_ASG_NO_DEL_PAYROLL_ACT');
3336: hr_utility.raise_error;
3337: end if;
3338: close csr_pay_assignment_actions;
3339: --
3340: hr_utility.set_location(l_proc, 6);
3336: hr_utility.raise_error;
3337: end if;
3338: close csr_pay_assignment_actions;
3339: --
3340: hr_utility.set_location(l_proc, 6);
3341: --
3342: -- Check that no child records exist for the
3343: -- assignment on per_secondary_ass_statuses when
3344: -- the assignment is deleted
3346: open csr_per_secondary_ass_stat;
3347: fetch csr_per_secondary_ass_stat into l_exists;
3348: if csr_per_secondary_ass_stat%found then
3349: close csr_per_secondary_ass_stat;
3350: hr_utility.set_message(801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
3351: hr_utility.raise_error;
3352: end if;
3353: close csr_per_secondary_ass_stat;
3354: --
3347: fetch csr_per_secondary_ass_stat into l_exists;
3348: if csr_per_secondary_ass_stat%found then
3349: close csr_per_secondary_ass_stat;
3350: hr_utility.set_message(801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
3351: hr_utility.raise_error;
3352: end if;
3353: close csr_per_secondary_ass_stat;
3354: --
3355: hr_utility.set_location(l_proc, 7);
3351: hr_utility.raise_error;
3352: end if;
3353: close csr_per_secondary_ass_stat;
3354: --
3355: hr_utility.set_location(l_proc, 7);
3356: --
3357: -- Check that no child records exist for the
3358: -- assignment on per_cobra_cov_enrollments
3359: -- when the assignment is deleted
3361: open csr_per_cobra_cov_enrol;
3362: fetch csr_per_cobra_cov_enrol into l_exists;
3363: if csr_per_cobra_cov_enrol%found then
3364: close csr_per_cobra_cov_enrol;
3365: hr_utility.set_message(801,'HR_7405_ASG_NO_DEL_COB_COV_ENR');
3366: hr_utility.raise_error;
3367: end if;
3368: close csr_per_cobra_cov_enrol;
3369: --
3362: fetch csr_per_cobra_cov_enrol into l_exists;
3363: if csr_per_cobra_cov_enrol%found then
3364: close csr_per_cobra_cov_enrol;
3365: hr_utility.set_message(801,'HR_7405_ASG_NO_DEL_COB_COV_ENR');
3366: hr_utility.raise_error;
3367: end if;
3368: close csr_per_cobra_cov_enrol;
3369: --
3370: hr_utility.set_location(l_proc, 8);
3366: hr_utility.raise_error;
3367: end if;
3368: close csr_per_cobra_cov_enrol;
3369: --
3370: hr_utility.set_location(l_proc, 8);
3371: --
3372: -- Check that no child records exist for the
3373: -- assignment on per_cobra_coverage_benefits_f
3374: -- when the assignment is deleted
3376: open csr_per_cobra_cov_bens;
3377: fetch csr_per_cobra_cov_bens into l_exists;
3378: if csr_per_cobra_cov_bens%found then
3379: close csr_per_cobra_cov_bens;
3380: hr_utility.set_message(801,'HR_7406_ASG_NO_DEL_COB_COV_BEN');
3381: hr_utility.raise_error;
3382: end if;
3383: close csr_per_cobra_cov_bens;
3384: --
3377: fetch csr_per_cobra_cov_bens into l_exists;
3378: if csr_per_cobra_cov_bens%found then
3379: close csr_per_cobra_cov_bens;
3380: hr_utility.set_message(801,'HR_7406_ASG_NO_DEL_COB_COV_BEN');
3381: hr_utility.raise_error;
3382: end if;
3383: close csr_per_cobra_cov_bens;
3384: --
3385: hr_utility.set_location(l_proc, 9);
3381: hr_utility.raise_error;
3382: end if;
3383: close csr_per_cobra_cov_bens;
3384: --
3385: hr_utility.set_location(l_proc, 9);
3386: --
3387: -- Check that no child records exist for the
3388: -- assignment on per_assignment_extra_info when
3389: -- the assignment is deleted
3394: open csr_per_ass_extra_info;
3395: fetch csr_per_ass_extra_info into l_exists;
3396: if csr_per_ass_extra_info%found then
3397: close csr_per_ass_extra_info;
3398: hr_utility.set_message(801,'HR_7409_ASG_NO_DEL_EXTR_INFO');
3399: hr_utility.raise_error;
3400: end if;
3401: close csr_per_ass_extra_info;
3402: end if;
3395: fetch csr_per_ass_extra_info into l_exists;
3396: if csr_per_ass_extra_info%found then
3397: close csr_per_ass_extra_info;
3398: hr_utility.set_message(801,'HR_7409_ASG_NO_DEL_EXTR_INFO');
3399: hr_utility.raise_error;
3400: end if;
3401: close csr_per_ass_extra_info;
3402: end if;
3403: --
3400: end if;
3401: close csr_per_ass_extra_info;
3402: end if;
3403: --
3404: hr_utility.set_location(l_proc, 10);
3405: --
3406: -- Check that no child records exist for the
3407: -- assignment on hr_assignment_set_amendments
3408: -- when the assignment is deleted
3413: open csr_hr_ass_set_amend;
3414: fetch csr_hr_ass_set_amend into l_exists;
3415: if csr_hr_ass_set_amend%found then
3416: close csr_hr_ass_set_amend;
3417: hr_utility.set_message(801,'HR_7410_ASG_NO_DEL_ASS_SET_AMD');
3418: hr_utility.raise_error;
3419: end if;
3420: close csr_hr_ass_set_amend;
3421: end if;
3414: fetch csr_hr_ass_set_amend into l_exists;
3415: if csr_hr_ass_set_amend%found then
3416: close csr_hr_ass_set_amend;
3417: hr_utility.set_message(801,'HR_7410_ASG_NO_DEL_ASS_SET_AMD');
3418: hr_utility.raise_error;
3419: end if;
3420: close csr_hr_ass_set_amend;
3421: end if;
3422: --
3419: end if;
3420: close csr_hr_ass_set_amend;
3421: end if;
3422: --
3423: hr_utility.set_location(' Leaving:'|| l_proc, 11);
3424: end chk_ref_int_del;
3425: --
3426: -- ---------------------------------------------------------------------------
3427: -- |---------------------< chk_sal_review_period_freq >----------------------|
3442: l_proc varchar2(72) := g_package||'chk_sal_review_period_freq';
3443: l_api_updating boolean;
3444: --
3445: begin
3446: hr_utility.set_location('Entering:'|| l_proc, 10);
3447: --
3448: -- Check mandatory parameters have been set
3449: --
3450: hr_api.mandatory_arg_error
3463: (p_api_name => l_proc
3464: ,p_argument => 'validation_end_date'
3465: ,p_argument_value => p_validation_end_date
3466: );
3467: hr_utility.set_location(l_proc, 20);
3468: --
3469: -- Only proceed with validation if :
3470: -- a) The current g_old_rec is current and
3471: -- b) The value for salary review period frequency has changed
3474: (p_assignment_id => p_assignment_id
3475: ,p_effective_date => p_effective_date
3476: ,p_object_version_number => p_object_version_number
3477: );
3478: hr_utility.set_location(l_proc, 30);
3479: --
3480: if ((l_api_updating and
3481: nvl(per_asg_shd.g_old_rec.sal_review_period_frequency,
3482: hr_api.g_varchar2) <> nvl(p_sal_review_period_frequency,
3483: hr_api.g_varchar2))
3484: or
3485: (NOT l_api_updating))
3486: then
3487: hr_utility.set_location(l_proc, 40);
3488: --
3489: -- Check if sal review period frequency is not null
3490: --
3491: if p_sal_review_period_frequency is not null then
3494: -- or benefit or offer assignment.
3495: --
3496: if p_assignment_type not in ('E','A','B','O') then
3497: --
3498: hr_utility.set_message(801, 'HR_51181_ASG_INV_ASG_TYP_SRPF');
3499: hr_multi_message.add
3500: (p_associated_column1 =>
3501: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3502: );
3501: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3502: );
3503: --
3504: end if;
3505: hr_utility.set_location(l_proc, 50);
3506: --
3507: -- Check that the salary review period frequency exists in
3508: -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
3509: -- flag set to 'Y' and that the effective start date of the
3518: ,p_lookup_code => p_sal_review_period_frequency
3519: )
3520: then
3521: --
3522: hr_utility.set_message(801, 'HR_51164_ASG_INV_SRP_FREQ');
3523: hr_multi_message.add
3524: (p_associated_column1 =>
3525: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3526: );
3525: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3526: );
3527: --
3528: end if;
3529: hr_utility.set_location(l_proc, 60);
3530: --
3531: end if;
3532: hr_utility.set_location(l_proc, 70);
3533: --
3528: end if;
3529: hr_utility.set_location(l_proc, 60);
3530: --
3531: end if;
3532: hr_utility.set_location(l_proc, 70);
3533: --
3534: end if;
3535: hr_utility.set_location(' Leaving:'|| l_proc, 80);
3536: end chk_sal_review_period_freq;
3531: end if;
3532: hr_utility.set_location(l_proc, 70);
3533: --
3534: end if;
3535: hr_utility.set_location(' Leaving:'|| l_proc, 80);
3536: end chk_sal_review_period_freq;
3537: --
3538: -- ---------------------------------------------------------------------------
3539: -- |-----------------------< chk_sal_review_period >------------------------|
3551: l_proc varchar2(72) := g_package||'chk_sal_review_period';
3552: l_api_updating boolean;
3553: --
3554: begin
3555: hr_utility.set_location('Entering:'|| l_proc, 1);
3556: --
3557: -- Check mandatory parameters have been set
3558: --
3559: hr_api.mandatory_arg_error
3570: (p_assignment_id => p_assignment_id
3571: ,p_effective_date => p_effective_date
3572: ,p_object_version_number => p_object_version_number);
3573: --
3574: hr_utility.set_location(l_proc, 2);
3575: --
3576: if ((l_api_updating and
3577: nvl(per_asg_shd.g_old_rec.sal_review_period,
3578: hr_api.g_number) <> nvl(p_sal_review_period,
3577: nvl(per_asg_shd.g_old_rec.sal_review_period,
3578: hr_api.g_number) <> nvl(p_sal_review_period,
3579: hr_api.g_number)) or (NOT l_api_updating)) then
3580: --
3581: hr_utility.set_location(l_proc, 3);
3582: --
3583: -- Check if sal review period is not null
3584: --
3585: if p_sal_review_period is not null then
3589: --
3590: if p_assignment_type not in ('E','A','B','O') then
3591:
3592: --
3593: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3594: hr_utility.raise_error;
3595: end if;
3596: hr_utility.set_location(l_proc, 4);
3597: --
3590: if p_assignment_type not in ('E','A','B','O') then
3591:
3592: --
3593: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3594: hr_utility.raise_error;
3595: end if;
3596: hr_utility.set_location(l_proc, 4);
3597: --
3598: end if;
3592: --
3593: hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3594: hr_utility.raise_error;
3595: end if;
3596: hr_utility.set_location(l_proc, 4);
3597: --
3598: end if;
3599: --
3600: end if;
3598: end if;
3599: --
3600: end if;
3601: --
3602: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3603: exception
3604: when app_exception.application_exception then
3605: if hr_multi_message.exception_add
3606: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3604: when app_exception.application_exception then
3605: if hr_multi_message.exception_add
3606: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3607: ) then
3608: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3609: raise;
3610: end if;
3611: hr_utility.set_location(' Leaving:'|| l_proc, 7);
3612: end chk_sal_review_period;
3607: ) then
3608: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3609: raise;
3610: end if;
3611: hr_utility.set_location(' Leaving:'|| l_proc, 7);
3612: end chk_sal_review_period;
3613: --
3614: -- ---------------------------------------------------------------------------
3615: -- |---------------------< chk_sal_rp_freq_sal_rp >--------------------------|
3627: l_proc varchar2(72):= g_package||'chk_sal_rp_freq_sal_rp';
3628: l_api_updating boolean;
3629: --
3630: begin
3631: hr_utility.set_location('Entering:'|| l_proc, 1);
3632: --
3633: if hr_multi_message.no_exclusive_error
3634: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3635: ,p_check_column2 =>
3661: hr_api.g_number) <> nvl(p_sal_review_period, hr_api.g_number))
3662: or
3663: (NOT l_api_updating)) then
3664: --
3665: hr_utility.set_location(l_proc, 2);
3666: --
3667: -- Check if sal review period frequency or sal review period is not null.
3668: --
3669: if p_sal_review_period_frequency is not null
3667: -- Check if sal review period frequency or sal review period is not null.
3668: --
3669: if p_sal_review_period_frequency is not null
3670: or p_sal_review_period is not null then
3671: hr_utility.set_location(l_proc, 3);
3672: --
3673: -- Check if sal review period frequency or sal review period are null.
3674: --
3675: if p_sal_review_period_frequency is null
3674: --
3675: if p_sal_review_period_frequency is null
3676: or p_sal_review_period is null then
3677: --
3678: hr_utility.set_message(801, 'HR_51165_ASG_INV_SRPF_SRP_COMB');
3679: hr_utility.raise_error;
3680: --
3681: end if;
3682: --
3675: if p_sal_review_period_frequency is null
3676: or p_sal_review_period is null then
3677: --
3678: hr_utility.set_message(801, 'HR_51165_ASG_INV_SRPF_SRP_COMB');
3679: hr_utility.raise_error;
3680: --
3681: end if;
3682: --
3683: end if;
3684: --
3685: end if;
3686: end if;
3687: --
3688: hr_utility.set_location(' Leaving:'|| l_proc, 4);
3689: exception
3690: when app_exception.application_exception then
3691: if hr_multi_message.exception_add
3692: (p_associated_column1 =>
3693: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3694: ,p_associated_column2 =>
3695: 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3696: ) then
3697: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3698: raise;
3699: end if;
3700: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3701: end chk_sal_rp_freq_sal_rp;
3696: ) then
3697: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3698: raise;
3699: end if;
3700: hr_utility.set_location(' Leaving:'|| l_proc, 6);
3701: end chk_sal_rp_freq_sal_rp;
3702: --
3703: -- ---------------------------------------------------------------------------
3704: -- |-----------------------< chk_set_of_books_id >---------------------------|
3730: where set_of_books_id = p_set_of_books_id
3731: and business_group_id = p_business_group_id;
3732: --
3733: begin
3734: hr_utility.set_location('Entering:'|| l_proc, 10);
3735: --
3736: -- Check mandatory parameters have been set
3737: --
3738: hr_api.mandatory_arg_error
3739: (p_api_name => l_proc
3740: ,p_argument => 'effective_date'
3741: ,p_argument_value => p_effective_date
3742: );
3743: hr_utility.set_location(l_proc, 20);
3744: --
3745: -- Only proceed with validation if :
3746: -- a) The current g_old_rec is current and
3747: -- b) The value for set_of_books_id has changed
3750: (p_assignment_id => p_assignment_id
3751: ,p_effective_date => p_effective_date
3752: ,p_object_version_number => p_object_version_number
3753: );
3754: hr_utility.set_location(l_proc, 30);
3755: --
3756: if ((l_api_updating and
3757: nvl(per_asg_shd.g_old_rec.set_of_books_id,
3758: hr_api.g_number) <> nvl(p_set_of_books_id, hr_api.g_number)) or
3757: nvl(per_asg_shd.g_old_rec.set_of_books_id,
3758: hr_api.g_number) <> nvl(p_set_of_books_id, hr_api.g_number)) or
3759: (NOT l_api_updating))
3760: then
3761: hr_utility.set_location(l_proc, 40);
3762: --
3763: -- Check if set of books is set
3764: --
3765: if p_set_of_books_id is not null then
3768: -- or contact or offer assignment.
3769: --
3770: if p_assignment_type not in ('E','A','C','O') then
3771: --
3772: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3773: hr_utility.raise_error;
3774: --
3775: end if;
3776: hr_utility.set_location(l_proc, 50);
3769: --
3770: if p_assignment_type not in ('E','A','C','O') then
3771: --
3772: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3773: hr_utility.raise_error;
3774: --
3775: end if;
3776: hr_utility.set_location(l_proc, 50);
3777: --
3772: hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3773: hr_utility.raise_error;
3774: --
3775: end if;
3776: hr_utility.set_location(l_proc, 50);
3777: --
3778: -- Check that the set of books exists in GL_SETS_OF_BOOKS.
3779: --
3780: open csr_valid_sob;
3780: open csr_valid_sob;
3781: fetch csr_valid_sob into l_exists;
3782: if csr_valid_sob%notfound then
3783: close csr_valid_sob;
3784: hr_utility.set_message(801, 'HR_51160_ASG_INV_SET_OF_BOOKS');
3785: hr_utility.raise_error;
3786: --
3787: end if;
3788: close csr_valid_sob;
3781: fetch csr_valid_sob into l_exists;
3782: if csr_valid_sob%notfound then
3783: close csr_valid_sob;
3784: hr_utility.set_message(801, 'HR_51160_ASG_INV_SET_OF_BOOKS');
3785: hr_utility.raise_error;
3786: --
3787: end if;
3788: close csr_valid_sob;
3789: hr_utility.set_location(l_proc, 60);
3785: hr_utility.raise_error;
3786: --
3787: end if;
3788: close csr_valid_sob;
3789: hr_utility.set_location(l_proc, 60);
3790: --
3791: -- Check that the set of books exists in
3792: -- FINANCIALS_SYSTEM_PARAMS_ALL for the assignment business
3793: -- group.
3795: open csr_valid_fsp_bg;
3796: fetch csr_valid_fsp_bg into l_exists;
3797: if csr_valid_fsp_bg%notfound then
3798: close csr_valid_fsp_bg;
3799: hr_utility.set_message(801, 'HR_51316_ASG_INV_FSP_SOB_BG');
3800: hr_utility.raise_error;
3801: --
3802: end if;
3803: close csr_valid_fsp_bg;
3796: fetch csr_valid_fsp_bg into l_exists;
3797: if csr_valid_fsp_bg%notfound then
3798: close csr_valid_fsp_bg;
3799: hr_utility.set_message(801, 'HR_51316_ASG_INV_FSP_SOB_BG');
3800: hr_utility.raise_error;
3801: --
3802: end if;
3803: close csr_valid_fsp_bg;
3804: hr_utility.set_location(l_proc, 70);
3800: hr_utility.raise_error;
3801: --
3802: end if;
3803: close csr_valid_fsp_bg;
3804: hr_utility.set_location(l_proc, 70);
3805: --
3806: end if;
3807: hr_utility.set_location(l_proc, 80);
3808: --
3803: close csr_valid_fsp_bg;
3804: hr_utility.set_location(l_proc, 70);
3805: --
3806: end if;
3807: hr_utility.set_location(l_proc, 80);
3808: --
3809: end if;
3810: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3811: exception
3806: end if;
3807: hr_utility.set_location(l_proc, 80);
3808: --
3809: end if;
3810: hr_utility.set_location(' Leaving:'|| l_proc, 100);
3811: exception
3812: when app_exception.application_exception then
3813: if hr_multi_message.exception_add
3814: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID'
3812: when app_exception.application_exception then
3813: if hr_multi_message.exception_add
3814: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID'
3815: ) then
3816: hr_utility.set_location(' Leaving:'|| l_proc, 110);
3817: raise;
3818: end if;
3819: hr_utility.set_location(' Leaving:'|| l_proc, 120);
3820: end chk_set_of_books_id;
3815: ) then
3816: hr_utility.set_location(' Leaving:'|| l_proc, 110);
3817: raise;
3818: end if;
3819: hr_utility.set_location(' Leaving:'|| l_proc, 120);
3820: end chk_set_of_books_id;
3821: --
3822: -- ---------------------------------------------------------------------------
3823: -- |--------------------< chk_soft_coding_keyflex_id >-----------------------|
3867: where message_name = p_message_name
3868: and application_id = 801;
3869: --
3870: begin
3871: hr_utility.set_location('Entering:'|| l_proc, 10);
3872: --
3873: -- Check mandatory parameters have been set
3874: --
3875: hr_api.mandatory_arg_error
3876: (p_api_name => l_proc
3877: ,p_argument => 'effective_date'
3878: ,p_argument_value => p_effective_date
3879: );
3880: hr_utility.set_location(l_proc, 20);
3881: --
3882: -- Only proceed with validation if :
3883: -- a) The current g_old_rec is current and
3884: -- b) The value for soft coding keyflex has changed
3888: (p_assignment_id => p_assignment_id
3889: ,p_effective_date => p_effective_date
3890: ,p_object_version_number => p_object_version_number
3891: );
3892: hr_utility.set_location(l_proc, 30);
3893: --
3894: if ((l_api_updating and
3895: nvl(per_asg_shd.g_old_rec.soft_coding_keyflex_id, hr_api.g_number) <>
3896: nvl(p_soft_coding_keyflex_id, hr_api.g_number)) or
3903: -- ****** End new code for bug #2182184 ****************************
3904: --
3905: (NOT l_api_updating)) then
3906: --
3907: hr_utility.set_location(l_proc, 40);
3908: --
3909: if p_soft_coding_keyflex_id is not null then
3910: --
3911: -- Check that the assignment is an employee assignment.
3914: --
3915: --
3916: if p_assignment_type not in ('E','A','B','C','O') then
3917: --
3918: hr_utility.set_message(801, 'HR_51227_ASG_INV_ASG_TYP_SCF');
3919: hr_multi_message.add
3920: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3921: );
3922: --
3920: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3921: );
3922: --
3923: end if;
3924: hr_utility.set_location(l_proc, 50);
3925: --
3926: -- Check that soft_coding_keyflex_id exists on
3927: -- hr_soft_coding_keyflex
3928: --
3929: open csr_valid_keyflex;
3930: fetch csr_valid_keyflex into l_exists;
3931: if csr_valid_keyflex%notfound then
3932: close csr_valid_keyflex;
3933: hr_utility.set_message(801, 'HR_7383_ASG_INV_KEYFLEX');
3934: hr_multi_message.add
3935: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3936: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3937: );
3937: );
3938: else
3939: close csr_valid_keyflex;
3940: end if;
3941: hr_utility.set_location(l_proc, 60);
3942: else
3943: --
3944: -- Check that for relevant legislations SCL is mandatory,
3945: -- when payroll_id is populated #909279
3943: --
3944: -- Check that for relevant legislations SCL is mandatory,
3945: -- when payroll_id is populated #909279
3946: --
3947: hr_utility.set_location(l_proc, 45);
3948: if p_payroll_id is not null and
3949: p_assignment_type = 'E' then
3950: open csr_bg;
3951: fetch csr_bg into l_legislation_code;
3950: open csr_bg;
3951: fetch csr_bg into l_legislation_code;
3952: close csr_bg;
3953: --
3954: hr_utility.set_location(l_proc, 55);
3955: open csr_pay_legislation_rules;
3956: fetch csr_pay_legislation_rules into l_exists;
3957: if csr_pay_legislation_rules%found then
3958: close csr_pay_legislation_rules;
3956: fetch csr_pay_legislation_rules into l_exists;
3957: if csr_pay_legislation_rules%found then
3958: close csr_pay_legislation_rules;
3959: if l_legislation_code = 'US' then
3960: hr_utility.set_message(801, 'HR_50001_EMP_ASS_NO_GRE');
3961: else
3962: open csr_tax_unit_message('HR_INV_LEG_ENT_'||l_legislation_code);
3963: fetch csr_tax_unit_message into l_exists;
3964:
3962: open csr_tax_unit_message('HR_INV_LEG_ENT_'||l_legislation_code);
3963: fetch csr_tax_unit_message into l_exists;
3964:
3965: if csr_tax_unit_message%found then
3966: hr_utility.set_message(801, 'HR_INV_LEG_ENT_'||l_legislation_code);
3967: else
3968: hr_utility.set_message(801, 'HR_34024_IP_INV_LEG_ENT');
3969: end if;
3970: close csr_tax_unit_message;
3964:
3965: if csr_tax_unit_message%found then
3966: hr_utility.set_message(801, 'HR_INV_LEG_ENT_'||l_legislation_code);
3967: else
3968: hr_utility.set_message(801, 'HR_34024_IP_INV_LEG_ENT');
3969: end if;
3970: close csr_tax_unit_message;
3971: end if;
3972: hr_multi_message.add
3974: );
3975: else
3976: close csr_pay_legislation_rules;
3977: end if;
3978: hr_utility.set_location(l_proc, 65);
3979: end if;
3980: end if;
3981: --
3982: end if;
3980: end if;
3981: --
3982: end if;
3983: --
3984: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3985: end chk_soft_coding_keyflex_id;
3986: --
3987: -- ---------------------------------------------------------------------------
3988: -- |--------------------< chk_source_organization_id >-----------------------|
4012: between date_from
4013: and nvl(date_to, hr_api.g_eot);
4014: --
4015: begin
4016: hr_utility.set_location('Entering:'|| l_proc, 10);
4017: --
4018: -- Check mandatory parameters have been set
4019: --
4020: hr_api.mandatory_arg_error
4033: (p_api_name => l_proc
4034: ,p_argument => 'validation_end_date'
4035: ,p_argument_value => p_validation_end_date
4036: );
4037: hr_utility.set_location(l_proc, 20);
4038: --
4039: -- Only proceed with validation if :
4040: -- a) The current g_old_rec is current and
4041: -- b) The value for source organization has changed
4044: (p_assignment_id => p_assignment_id
4045: ,p_effective_date => p_effective_date
4046: ,p_object_version_number => p_object_version_number
4047: );
4048: hr_utility.set_location(l_proc, 30);
4049: --
4050: if ((l_api_updating
4051: and nvl(per_asg_shd.g_old_rec.source_organization_id, hr_api.g_number)
4052: <> nvl(p_source_organization_id, hr_api.g_number))
4052: <> nvl(p_source_organization_id, hr_api.g_number))
4053: or
4054: (NOT l_api_updating))
4055: then
4056: hr_utility.set_location(l_proc, 40);
4057: --
4058: -- Check if source organization is set
4059: --
4060: if p_source_organization_id is not null then
4066: -- Check if the employee assignment is being updated
4067: --
4068: If l_api_updating then
4069: --
4070: hr_utility.set_message(801, 'HR_51220_ASG_INV_EASG_U_SORG');
4071: hr_multi_message.add
4072: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4073: );
4074: --
4073: );
4074: --
4075: else -- inserting an employee assignment
4076: --
4077: hr_utility.set_message(801, 'HR_51219_ASG_INV_EASG_I_SORG');
4078: hr_multi_message.add
4079: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4080: );
4081: --
4079: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4080: );
4081: --
4082: end if;
4083: hr_utility.set_location(l_proc, 60);
4084: --
4085: end if;
4086: hr_utility.set_location(l_proc, 70);
4087: --
4082: end if;
4083: hr_utility.set_location(l_proc, 60);
4084: --
4085: end if;
4086: hr_utility.set_location(l_proc, 70);
4087: --
4088: -- Check if the source organization exists where the effective
4089: -- start date of the assignment is between the date from and
4090: -- date to of the source organization.
4092: open csr_val_source_org_id;
4093: fetch csr_val_source_org_id into l_business_group_id;
4094: if csr_val_source_org_id%notfound then
4095: close csr_val_source_org_id;
4096: hr_utility.set_message(801, 'HR_51308_ASG_INV_SOURCE_ORG');
4097: hr_multi_message.add
4098: (p_associated_column1 =>
4099: 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4100: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4102: --
4103: else
4104: close csr_val_source_org_id;
4105: end if;
4106: hr_utility.set_location(l_proc, 80);
4107: --
4108: -- Check that the source organization is in the same business group
4109: -- as the business group of the assignment.
4110: --
4109: -- as the business group of the assignment.
4110: --
4111: If p_business_group_id <> l_business_group_id then
4112: --
4113: hr_utility.set_message(801, 'HR_51309_ASG_INV_SOURCE_ORG_BG');
4114: hr_multi_message.add
4115: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4116: );
4117: --
4115: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4116: );
4117: --
4118: end if;
4119: hr_utility.set_location(l_proc, 90);
4120: --
4121: end if;
4122: hr_utility.set_location(l_proc, 100);
4123: --
4118: end if;
4119: hr_utility.set_location(l_proc, 90);
4120: --
4121: end if;
4122: hr_utility.set_location(l_proc, 100);
4123: --
4124: end if;
4125: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4126: end chk_source_organization_id;
4121: end if;
4122: hr_utility.set_location(l_proc, 100);
4123: --
4124: end if;
4125: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4126: end chk_source_organization_id;
4127: --
4128: -- ---------------------------------------------------------------------------
4129: -- |------------------------< chk_source_type >------------------------------|
4149: from per_recruitment_activities
4150: where recruitment_activity_id = p_recruitment_activity_id;
4151: --
4152: begin
4153: hr_utility.set_location('Entering:'|| l_proc, 10);
4154: --
4155: -- Check mandatory parameters have been set
4156: --
4157: hr_api.mandatory_arg_error
4169: (p_api_name => l_proc
4170: ,p_argument => 'validation_end_date'
4171: ,p_argument_value => p_validation_end_date
4172: );
4173: hr_utility.set_location(l_proc, 20);
4174: --
4175: -- Only proceed with validation if :
4176: -- a) The current g_old_rec is current and
4177: -- b) The value for source type has changed
4180: (p_assignment_id => p_assignment_id
4181: ,p_effective_date => p_effective_date
4182: ,p_object_version_number => p_object_version_number
4183: );
4184: hr_utility.set_location(l_proc, 30);
4185: --
4186: if ((l_api_updating and
4187: nvl(per_asg_shd.g_old_rec.source_type, hr_api.g_varchar2) <>
4188: nvl(p_source_type, hr_api.g_varchar2))
4187: nvl(per_asg_shd.g_old_rec.source_type, hr_api.g_varchar2) <>
4188: nvl(p_source_type, hr_api.g_varchar2))
4189: or (NOT l_api_updating))
4190: then
4191: hr_utility.set_location(l_proc, 40);
4192: --
4193: -- Check if source type is set
4194: --
4195: if p_source_type is not null then
4207: ,p_lookup_code => p_source_type
4208: )
4209: then
4210: --
4211: hr_utility.set_message(801, 'HR_51162_ASG_INV_SOURCE_TYPE');
4212: hr_multi_message.add
4213: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4214: );
4215: --
4213: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4214: );
4215: --
4216: end if;
4217: hr_utility.set_location(l_proc, 50);
4218: --
4219: -- Check if recruitment activity is set
4220: --
4221: If p_recruitment_activity_id is not null then
4225: --
4226: open csr_get_rec_act_type;
4227: fetch csr_get_rec_act_type into l_rec_act_type;
4228: close csr_get_rec_act_type;
4229: hr_utility.set_location(l_proc, 60);
4230: --
4231: If p_source_type <> nvl(l_rec_act_type, hr_api.g_varchar2) then
4232: --
4233: hr_utility.set_message(801, 'HR_51325_ASG_INV_SOU_TYP_RAT');
4229: hr_utility.set_location(l_proc, 60);
4230: --
4231: If p_source_type <> nvl(l_rec_act_type, hr_api.g_varchar2) then
4232: --
4233: hr_utility.set_message(801, 'HR_51325_ASG_INV_SOU_TYP_RAT');
4234: hr_multi_message.add
4235: (p_associated_column1 =>
4236: 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
4237: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4237: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4238: );
4239: --
4240: end if;
4241: hr_utility.set_location(l_proc, 70);
4242: --
4243: end if;
4244: hr_utility.set_location(l_proc, 80);
4245: --
4240: end if;
4241: hr_utility.set_location(l_proc, 70);
4242: --
4243: end if;
4244: hr_utility.set_location(l_proc, 80);
4245: --
4246: end if;
4247: hr_utility.set_location(l_proc, 90);
4248: --
4243: end if;
4244: hr_utility.set_location(l_proc, 80);
4245: --
4246: end if;
4247: hr_utility.set_location(l_proc, 90);
4248: --
4249: end if;
4250: --
4251: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4247: hr_utility.set_location(l_proc, 90);
4248: --
4249: end if;
4250: --
4251: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4252: end chk_source_type;
4253: --
4254: -- ---------------------------------------------------------------------------
4255: -- |-------------------< chk_special_ceiling_step_id >-----------------------|
4327: and psps.effective_start_date between pgs.effective_start_date
4328: and pgs.effective_end_date);
4329: --
4330: begin
4331: hr_utility.set_location('Entering:'|| l_proc, 10);
4332: --
4333: --
4334: -- Check mandatory parameters have been set
4335: --
4355: (p_api_name => l_proc
4356: ,p_argument => 'business_group_id'
4357: ,p_argument_value => p_business_group_id
4358: );
4359: hr_utility.set_location(l_proc, 20);
4360: --
4361: -- Only proceed with validation if :
4362: -- a) The current g_old_rec is current and
4363: -- b) The value for special ceiling step has changed
4366: (p_assignment_id => p_assignment_id
4367: ,p_effective_date => p_effective_date
4368: ,p_object_version_number => p_object_version_number
4369: );
4370: hr_utility.set_location(l_proc, 30);
4371: --
4372: if ((l_api_updating and
4373: nvl(per_asg_shd.g_old_rec.special_ceiling_step_id, hr_api.g_number) <>
4374: nvl(p_special_ceiling_step_id, hr_api.g_number)) or
4372: if ((l_api_updating and
4373: nvl(per_asg_shd.g_old_rec.special_ceiling_step_id, hr_api.g_number) <>
4374: nvl(p_special_ceiling_step_id, hr_api.g_number)) or
4375: (NOT l_api_updating)) then
4376: hr_utility.set_location(l_proc, 40);
4377: --
4378: if p_special_ceiling_step_id is not null then
4379: --
4380: -- Check that the assignment is an employee,applicant or benefits
4381: -- assignment.
4382: --
4383: if p_assignment_type not in ('E','A','B','O') then
4384: --
4385: hr_utility.set_message(801, 'HR_51225_ASG_INV_ASG_TYP_SCS');
4386: hr_multi_message.add
4387: (p_associated_column1 =>
4388: 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4389: );
4388: 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4389: );
4390: --
4391: end if;
4392: hr_utility.set_location(l_proc, 50);
4393: --
4394: -- Check that special_ceiling_step_id exists and is date effective
4395: -- per_grade_spines_f
4396: --
4397: open csr_valid_step;
4398: fetch csr_valid_step into l_exists;
4399: if csr_valid_step%notfound then
4400: close csr_valid_step;
4401: hr_utility.set_message(801, 'HR_7379_ASG_INV_SPEC_CEIL_STEP');
4402: hr_multi_message.add
4403: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4404: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4405: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4407: --
4408: else
4409: close csr_valid_step;
4410: end if;
4411: hr_utility.set_location(l_proc, 60);
4412: --
4413: -- Check that the business group of the special_ceiling_step_id on
4414: -- per_grade_spines is the same as that of the assignment.
4415: --
4416: open csr_get_bus_grp;
4417: fetch csr_get_bus_grp into l_business_group_id;
4418: if l_business_group_id <> p_business_group_id then
4419: close csr_get_bus_grp;
4420: hr_utility.set_message(801, 'HR_7375_ASG_INV_BG_SP_CLG_STEP');
4421: hr_multi_message.add
4422: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4423: );
4424: else
4423: );
4424: else
4425: close csr_get_bus_grp;
4426: end if;
4427: hr_utility.set_location(l_proc, 70);
4428: --
4429: if hr_multi_message.no_exclusive_error
4430: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4431: ) then
4437: open csr_valid_step_grade;
4438: fetch csr_valid_step_grade into l_sequence;
4439: if csr_valid_step_grade%notfound then
4440: close csr_valid_step_grade;
4441: hr_utility.set_message(801, 'HR_7380_ASG_STEP_INV_FOR_GRADE');
4442: hr_multi_message.add
4443: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4444: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4445: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4446: );
4447: else
4448: close csr_valid_step_grade;
4449: end if;
4450: hr_utility.set_location(l_proc, 80);
4451: else
4452: --
4453: -- If the value for special ceiling step is not null
4454: -- then grade id must also be not null
4452: --
4453: -- If the value for special ceiling step is not null
4454: -- then grade id must also be not null
4455: --
4456: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
4457: hr_multi_message.add
4458: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4459: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4460: );
4458: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4459: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4460: );
4461: end if;
4462: hr_utility.set_location(l_proc, 90);
4463: --
4464: -- Check if updating
4465: --
4466: if l_api_updating then
4471: open csr_low_step;
4472: fetch csr_low_step into l_exists;
4473: if csr_low_step%found then
4474: close csr_low_step;
4475: hr_utility.set_message(801, 'HR_7381_ASG_CEIL_STEP_TOO_HIGH');
4476: hr_multi_message.add
4477: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4478: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4479: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4480: );
4481: else
4482: close csr_low_step;
4483: end if;
4484: hr_utility.set_location(l_proc, 110);
4485: --
4486: end if;
4487: hr_utility.set_location(l_proc, 120);
4488: end if;
4483: end if;
4484: hr_utility.set_location(l_proc, 110);
4485: --
4486: end if;
4487: hr_utility.set_location(l_proc, 120);
4488: end if;
4489: hr_utility.set_location(l_proc, 130);
4490: --
4491: end if; -- no exclusive error
4485: --
4486: end if;
4487: hr_utility.set_location(l_proc, 120);
4488: end if;
4489: hr_utility.set_location(l_proc, 130);
4490: --
4491: end if; -- no exclusive error
4492: end if;
4493: --
4490: --
4491: end if; -- no exclusive error
4492: end if;
4493: --
4494: hr_utility.set_location(' Leaving:'|| l_proc, 140);
4495: end chk_special_ceiling_step_id;
4496: --
4497: -- ---------------------------------------------------------------------------
4498: -- |--------------------------< chk_supervisor_id >--------------------------|
4559: --
4560: -- Fix for Bug#8310023 ends here
4561: --
4562: begin
4563: hr_utility.set_location('Entering:'|| l_proc, 10);
4564: --
4565: if hr_multi_message.no_exclusive_error
4566: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4567: ) then
4590: (p_api_name => l_proc
4591: ,p_argument => 'effective_date'
4592: ,p_argument_value => p_effective_date
4593: );
4594: hr_utility.set_location(l_proc, 20);
4595: --
4596: -- Only proceed with validation if :
4597: -- a) The current g_old_rec is current and
4598: -- b) The value for supervisor_id has changed
4601: (p_assignment_id => p_assignment_id
4602: ,p_effective_date => p_effective_date
4603: ,p_object_version_number => p_object_version_number
4604: );
4605: hr_utility.set_location(l_proc, 30);
4606: --
4607: if ((l_api_updating and
4608: nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4609: nvl(p_supervisor_id, hr_api.g_number)) or
4608: nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4609: nvl(p_supervisor_id, hr_api.g_number)) or
4610: (NOT l_api_updating))
4611: then
4612: hr_utility.set_location(l_proc, 40);
4613: --
4614: -- Check if supervisor is not null
4615: --
4616: if p_supervisor_id is not null then
4619: -- assignment.
4620: --
4621: If p_supervisor_id = p_person_id then
4622: --
4623: hr_utility.set_message(801, 'HR_51143_ASG_EMP_EQUAL_SUP');
4624: hr_multi_message.add
4625: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4626: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4627: );
4625: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4626: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4627: );
4628: end if;
4629: hr_utility.set_location(l_proc, 50);
4630: --
4631: -- Fix for bug 4305723 starts here. Check if the party_id is same for
4632: -- employee and supervisor.
4633: --
4640: close csr_party_id;
4641: --
4642: If l_per_party_id = l_sup_party_id
4643: then
4644: hr_utility.set_message(800, 'HR_449603_ASG_SUP_DUP_PER');
4645: hr_multi_message.add
4646: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4647: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4648: );
4649: end if;
4650: --
4651: -- Fix for bug 4305723 ends here.
4652: --
4653: hr_utility.set_location(l_proc, 55);
4654: --
4655: -- Fix for Bug#8310023 starts here
4656: -- Finding the installation type ( Shared HR or Full HR )
4657: --
4666: fetch csr_valid_supervisor_sh_hr
4667: into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
4668: if csr_valid_supervisor_sh_hr%notfound then
4669: close csr_valid_supervisor_sh_hr;
4670: hr_utility.set_message(801, 'PER_50501_INV_SUPERVISOR');
4671: else
4672: close csr_valid_supervisor_sh_hr;
4673: end if;
4674: else
4682: into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
4683: if csr_valid_supervisor_id%notfound then
4684: close csr_valid_supervisor_id;
4685: --
4686: hr_utility.set_message(801, 'PAY_7599_SYS_SUP_DT_OUTDATE');
4687: hr_multi_message.add
4688: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4689: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4690: );
4691: else
4692: close csr_valid_supervisor_id;
4693: end if;
4694: end if;
4695: hr_utility.set_location(l_proc, 60);
4696: --
4697: -- Check that the supervisor is in the same business group as the
4698: -- person of the assignment.
4699: --
4700: If (p_business_group_id <> l_business_group_id AND
4701: nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
4702: then
4703: --
4704: hr_utility.set_message(801, 'HR_51145_ASG_SUP_BG_NE_EMP_BG');
4705: hr_multi_message.add
4706: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4707: );
4708: end if;
4705: hr_multi_message.add
4706: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4707: );
4708: end if;
4709: hr_utility.set_location(l_proc, 70);
4710: --
4711: -- Check that the supervisor is an employee or a contingent
4712: -- worker where the profile option permits.
4713: --
4723: fetch csr_asg_typ into l_assignment_type;
4724: close csr_asg_typ;
4725: if l_assignment_type in ('C','E','B') then
4726: --
4727: hr_utility.set_message(801, 'HR_51346_ASG_SUP_NOT_EMP');
4728: hr_multi_message.add
4729: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID');
4730: --
4731: end if;
4731: end if;
4732: --Bug#3917021 ends here
4733: --
4734: end if;
4735: hr_utility.set_location(l_proc, 80);
4736: --
4737: end if;
4738: --
4739: end if;
4738: --
4739: end if;
4740: end if;
4741: --
4742: hr_utility.set_location(' Leaving:'|| l_proc, 90);
4743: end chk_supervisor_id;
4744: --
4745: -- ---------------------------------------------------------------------------
4746: -- |-------------------< chk_supervisor_assignment_id >----------------------|
4769: and p_validation_start_date between
4770: paaf.effective_start_date and paaf.effective_end_date;
4771: --
4772: begin
4773: hr_utility.set_location('Entering:'|| l_proc, 10);
4774: --
4775: if hr_multi_message.no_exclusive_error
4776: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4777: ) then
4788: (p_api_name => l_proc
4789: ,p_argument => 'effective_date'
4790: ,p_argument_value => p_effective_date
4791: );
4792: hr_utility.set_location(l_proc, 20);
4793: --
4794: -- Only proceed with validation if :
4795: -- a) The current g_old_rec is current and
4796: -- b) The value for supervisor_id has changed
4799: (p_assignment_id => p_assignment_id
4800: ,p_effective_date => p_effective_date
4801: ,p_object_version_number => p_object_version_number
4802: );
4803: hr_utility.set_location(l_proc, 30);
4804:
4805: --
4806: -- Re-validate if either the supervisor or supervisor assignment
4807: -- has changed.
4814: nvl(p_supervisor_assignment_id, hr_api.g_number))) or
4815: (NOT l_api_updating))
4816: then
4817:
4818: hr_utility.set_location(l_proc, 40);
4819: ----
4820: if ((nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4821: nvl(p_supervisor_id, hr_api.g_number))
4822: and
4839: -- Check that supervisor assignment exists, that it is date effective
4840: -- within the validation period of the assignment and that it belongs
4841: -- to the given supervisor.
4842: --
4843: hr_utility.set_location(l_proc, 50);
4844:
4845: open csr_supervisor_assignment_id;
4846: fetch csr_supervisor_assignment_id into l_assignment_type;
4847: if csr_supervisor_assignment_id%notfound then
4844:
4845: open csr_supervisor_assignment_id;
4846: fetch csr_supervisor_assignment_id into l_assignment_type;
4847: if csr_supervisor_assignment_id%notfound then
4848: hr_utility.set_location(l_proc, 60);
4849: close csr_supervisor_assignment_id;
4850: --
4851: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4852: hr_utility.raise_error;
4847: if csr_supervisor_assignment_id%notfound then
4848: hr_utility.set_location(l_proc, 60);
4849: close csr_supervisor_assignment_id;
4850: --
4851: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4852: hr_utility.raise_error;
4853: /* hr_multi_message.add
4854: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4855: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4848: hr_utility.set_location(l_proc, 60);
4849: close csr_supervisor_assignment_id;
4850: --
4851: hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4852: hr_utility.raise_error;
4853: /* hr_multi_message.add
4854: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4855: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4856: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4858: else
4859: close csr_supervisor_assignment_id;
4860: end if;
4861:
4862: hr_utility.set_location(l_proc, 70);
4863:
4864: --
4865: -- Check that the supervisor assignment is an employee or a contingent
4866: -- worker assignment.
4869: or (nvl(l_assignment_type, hr_api.g_varchar2) = 'C' and
4870: nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4871: Then
4872: --
4873: hr_utility.set_location(l_proc, 80);
4874: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4875: hr_utility.raise_error;
4876: /*hr_multi_message.add
4877: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4870: nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4871: Then
4872: --
4873: hr_utility.set_location(l_proc, 80);
4874: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4875: hr_utility.raise_error;
4876: /*hr_multi_message.add
4877: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4878: ); */
4871: Then
4872: --
4873: hr_utility.set_location(l_proc, 80);
4874: hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4875: hr_utility.raise_error;
4876: /*hr_multi_message.add
4877: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4878: ); */
4879: --
4877: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4878: ); */
4879: --
4880: end if;
4881: hr_utility.set_location(l_proc, 90);
4882: --
4883: end if;
4884: --
4885: end if;
4884: --
4885: end if;
4886: end if;
4887: --
4888: hr_utility.set_location(' Leaving:'|| l_proc, 100);
4889:
4890: end chk_supervisor_assignment_id;
4891: --
4892: -- ---------------------------------------------------------------------------
4928: --
4929: l_proc varchar2(72) := g_package||'chk_system_pers_type';
4930: --
4931: begin
4932: hr_utility.set_location('Entering:'|| l_proc, 1);
4933: --
4934: -- Check mandatory parameters have been set
4935: --
4936: hr_api.mandatory_arg_error
4950: ,p_argument => 'validation_end_date'
4951: ,p_argument_value => p_validation_end_date
4952: );
4953: --
4954: hr_utility.set_location(l_proc, 2);
4955: --
4956: -- Only trigger validation for the following datetrack modes :
4957: -- - UPDATE_OVERRIDE
4958: -- - ZAP
4988: );
4989: end if;
4990: --
4991: end if;
4992: hr_utility.set_location(' Leaving:'|| l_proc, 4);
4993: --
4994: exception
4995: when app_exception.application_exception then
4996: if hr_multi_message.exception_add
4995: when app_exception.application_exception then
4996: if hr_multi_message.exception_add
4997: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4998: ) then
4999: hr_utility.set_location(' Leaving:'|| l_proc, 5);
5000: raise;
5001: end if;
5002: hr_utility.set_location(' Leaving:'|| l_proc, 6);
5003: --
4998: ) then
4999: hr_utility.set_location(' Leaving:'|| l_proc, 5);
5000: raise;
5001: end if;
5002: hr_utility.set_location(' Leaving:'|| l_proc, 6);
5003: --
5004: end chk_system_pers_type;
5005: --
5006: -- ---------------------------------------------------------------------------
5055: and past.assignment_status_type_id = pas.assignment_status_type_id
5056: and past.per_system_status = 'TERM_ASSIGN';
5057: --
5058: begin
5059: hr_utility.set_location('Entering:'|| l_proc, 1);
5060: --
5061: -- Check whether assignment is terminated in the future
5062: --
5063: if p_datetrack_mode in ('UPDATE_OVERRIDE'
5066: open csr_chk_term_status;
5067: fetch csr_chk_term_status into l_exists;
5068: if csr_chk_term_status%found then
5069: close csr_chk_term_status;
5070: hr_utility.set_message(801, 'HR_7412_ASG_ASS_TERM_IN_FUTURE');
5071: hr_multi_message.add
5072: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATETRACK_MODE'
5073: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5074: );
5076: close csr_chk_term_status;
5077: end if;
5078: end if;
5079: --
5080: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5081: end chk_term_status;
5082: --
5083: -- 70.1 change d start.
5084: --
5094: --
5095: l_proc varchar2(72) := g_package||'chk_time_finish_formatted';
5096: --
5097: begin
5098: hr_utility.set_location('Entering:'|| l_proc, 1);
5099: --
5100: -- Check that time_normal_finish is valid
5101: --
5102: if p_time_normal_finish is not null then
5107: ,p_format => 'TIMES');
5108: --
5109: end if;
5110: --
5111: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5112: exception
5113: when app_exception.application_exception then
5114: if hr_multi_message.exception_add
5115: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH'
5113: when app_exception.application_exception then
5114: if hr_multi_message.exception_add
5115: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH'
5116: ) then
5117: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5118: raise;
5119: end if;
5120: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5121: --
5116: ) then
5117: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5118: raise;
5119: end if;
5120: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5121: --
5122: end chk_time_finish_formatted;
5123: --
5124: --
5146: --
5147: l_proc varchar2(72) := g_package||'chk_time_normal_start_formatted';
5148: --
5149: begin
5150: hr_utility.set_location('Entering:'|| l_proc, 1);
5151: --
5152: -- Check that time_normal_start is valid
5153: --
5154: if p_time_normal_start is not null then
5159: ,p_format => 'TIMES');
5160: --
5161: end if;
5162: --
5163: hr_utility.set_location(' Leaving:'|| l_proc, 2);
5164: exception
5165: when app_exception.application_exception then
5166: if hr_multi_message.exception_add
5167: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START'
5165: when app_exception.application_exception then
5166: if hr_multi_message.exception_add
5167: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START'
5168: ) then
5169: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5170: raise;
5171: end if;
5172: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5173: end chk_time_start_formatted;
5168: ) then
5169: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5170: raise;
5171: end if;
5172: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5173: end chk_time_start_formatted;
5174: --
5175: -- << 2734822 >>
5176: --
5202: )
5203: is
5204: l_proc varchar2(72) := g_package||'chk_dup_apl_vacancy';
5205: begin
5206: hr_utility.set_location('Entering: old'|| l_proc, 10);
5207:
5208: per_asg_bus2.chk_dup_apl_vacancy
5209: (p_person_id => p_person_id
5210: ,p_business_group_id => p_business_group_id
5216: ,p_validation_end_date => hr_api.g_eot
5217: ,p_datetrack_mode => 'INSERT'
5218: );
5219:
5220: hr_utility.set_location('Leaving: old'|| l_proc, 100);
5221: end;
5222: --
5223: -- End changes for bug 8687386
5224: --
5306: -- End changes for bug 8687386
5307: --
5308: begin
5309: --
5310: hr_utility.set_location('Entering:'|| l_proc, 10);
5311: --
5312: -- Check this for Applicant assignments Only.
5313: --
5314: if (p_assignment_type = 'A'
5320: /* open csr_dup_apl_vacancy;
5321: fetch csr_dup_apl_vacancy into l_application_id;
5322: if csr_dup_apl_vacancy%found then
5323: close csr_dup_apl_vacancy;
5324: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5325: hr_utility.raise_error;
5326: --
5327: end if;
5328: close csr_dup_apl_vacancy;
5321: fetch csr_dup_apl_vacancy into l_application_id;
5322: if csr_dup_apl_vacancy%found then
5323: close csr_dup_apl_vacancy;
5324: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5325: hr_utility.raise_error;
5326: --
5327: end if;
5328: close csr_dup_apl_vacancy;
5329: hr_utility.set_location(l_proc, 20);
5325: hr_utility.raise_error;
5326: --
5327: end if;
5328: close csr_dup_apl_vacancy;
5329: hr_utility.set_location(l_proc, 20);
5330: */
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5327: end if;
5328: close csr_dup_apl_vacancy;
5329: hr_utility.set_location(l_proc, 20);
5330: */
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5328: close csr_dup_apl_vacancy;
5329: hr_utility.set_location(l_proc, 20);
5330: */
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5329: hr_utility.set_location(l_proc, 20);
5330: */
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5337: hr_utility.set_location('p_validation_end_date: '||p_validation_end_date ,10);
5330: */
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5337: hr_utility.set_location('p_validation_end_date: '||p_validation_end_date ,10);
5338:
5331: hr_utility.set_location('p_person_id: '||p_person_id ,10);
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5337: hr_utility.set_location('p_validation_end_date: '||p_validation_end_date ,10);
5338:
5339: if P_validation_end_date = to_date('31/12/4712','dd/mm/yyyy') then
5332: hr_utility.set_location('p_vacancy_id: '||p_vacancy_id ,10);
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5337: hr_utility.set_location('p_validation_end_date: '||p_validation_end_date ,10);
5338:
5339: if P_validation_end_date = to_date('31/12/4712','dd/mm/yyyy') then
5340: --
5333: hr_utility.set_location('p_effective_date: '||p_effective_date ,10);
5334: hr_utility.set_location('p_assignment_type: '||p_assignment_type ,10);
5335: hr_utility.set_location('p_assignment_id: '||p_assignment_id ,10);
5336: hr_utility.set_location('p_validation_start_date: '||p_validation_start_date ,10);
5337: hr_utility.set_location('p_validation_end_date: '||p_validation_end_date ,10);
5338:
5339: if P_validation_end_date = to_date('31/12/4712','dd/mm/yyyy') then
5340: --
5341: open csr_dup_apl_vac_with_eot;
5342: fetch csr_dup_apl_vac_with_eot into l_application_id;
5343: if csr_dup_apl_vac_with_eot%found then
5344: --
5345: close csr_dup_apl_vac_with_eot;
5346: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5347: hr_utility.raise_error;
5348: --
5349: end if;
5350: close csr_dup_apl_vac_with_eot;
5343: if csr_dup_apl_vac_with_eot%found then
5344: --
5345: close csr_dup_apl_vac_with_eot;
5346: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5347: hr_utility.raise_error;
5348: --
5349: end if;
5350: close csr_dup_apl_vac_with_eot;
5351: hr_utility.set_location(l_proc, 20);
5347: hr_utility.raise_error;
5348: --
5349: end if;
5350: close csr_dup_apl_vac_with_eot;
5351: hr_utility.set_location(l_proc, 20);
5352: --
5353: else
5354: --
5355: open csr_dup_apl_vac_without_eot;
5356: fetch csr_dup_apl_vac_without_eot into l_application_id;
5357: if csr_dup_apl_vac_without_eot%found then
5358: --
5359: close csr_dup_apl_vac_without_eot;
5360: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5361: hr_utility.raise_error;
5362: --
5363: end if;
5364: close csr_dup_apl_vac_without_eot;
5357: if csr_dup_apl_vac_without_eot%found then
5358: --
5359: close csr_dup_apl_vac_without_eot;
5360: hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5361: hr_utility.raise_error;
5362: --
5363: end if;
5364: close csr_dup_apl_vac_without_eot;
5365: hr_utility.set_location(l_proc, 30);
5361: hr_utility.raise_error;
5362: --
5363: end if;
5364: close csr_dup_apl_vac_without_eot;
5365: hr_utility.set_location(l_proc, 30);
5366: --
5367: -- End changes for bug 8687386
5368: end if;
5369:
5374: if hr_multi_message.exception_add
5375: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5376: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5377: ) then
5378: hr_utility.set_location(' Leaving:'|| l_proc, 30);
5379: raise;
5380: end if;
5381: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5382: --
5377: ) then
5378: hr_utility.set_location(' Leaving:'|| l_proc, 30);
5379: raise;
5380: end if;
5381: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5382: --
5383: end chk_dup_apl_vacancy;
5384: --
5385: -- ---------------------------------------------------------------------------
5416: from per_vacancies
5417: where vacancy_id = p_vacancy_id;
5418: --
5419: begin
5420: hr_utility.set_location('Entering:'|| l_proc, 10);
5421: --
5422: -- Check mandatory parameters have been set
5423: --
5424: hr_api.mandatory_arg_error
5437: (p_api_name => l_proc
5438: ,p_argument => 'validation_end_date'
5439: ,p_argument_value => p_validation_end_date
5440: );
5441: hr_utility.set_location(l_proc, 20);
5442: --
5443: -- Only proceed with validation if :
5444: -- a) The current g_old_rec is current and
5445: -- b) The value for vacancy has changed
5448: (p_assignment_id => p_assignment_id
5449: ,p_effective_date => p_effective_date
5450: ,p_object_version_number => p_object_version_number
5451: );
5452: hr_utility.set_location(l_proc, 30);
5453: --
5454: if ((l_api_updating and
5455: nvl(per_asg_shd.g_old_rec.vacancy_id, hr_api.g_number) <>
5456: nvl(p_vacancy_id, hr_api.g_number)) or
5454: if ((l_api_updating and
5455: nvl(per_asg_shd.g_old_rec.vacancy_id, hr_api.g_number) <>
5456: nvl(p_vacancy_id, hr_api.g_number)) or
5457: (NOT l_api_updating)) then
5458: hr_utility.set_location(l_proc, 40);
5459: --
5460: -- Check if vacancy is not null
5461: --
5462: if p_vacancy_id is not null then
5469: -- Check if the employee assignment is being updated
5470: --
5471: If l_api_updating then
5472: --
5473: hr_utility.set_message(801, 'HR_51222_ASG_INV_EASG_U_VAC');
5474: hr_multi_message.add
5475: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5476: );
5477: --
5476: );
5477: --
5478: else -- inserting a non employee
5479: --
5480: hr_utility.set_message(801, 'HR_51221_ASG_INV_EASG_I_VAC');
5481: hr_multi_message.add
5482: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5483: );
5484: --
5482: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5483: );
5484: --
5485: end if;
5486: hr_utility.set_location(l_proc, 50);
5487: --
5488: end if;
5489: hr_utility.set_location(l_proc, 60);
5490: --
5485: end if;
5486: hr_utility.set_location(l_proc, 50);
5487: --
5488: end if;
5489: hr_utility.set_location(l_proc, 60);
5490: --
5491: if p_assignment_type = 'O'
5492: then
5493: --
5499: if csr_val_vacancy_id_offer%notfound
5500: then
5501: --
5502: close csr_val_vacancy_id;
5503: hr_utility.set_message(800, 'HR_52591_CEL_INVL_VAC_ID');
5504: hr_utility.raise_error;
5505: --
5506: end if;
5507: --
5500: then
5501: --
5502: close csr_val_vacancy_id;
5503: hr_utility.set_message(800, 'HR_52591_CEL_INVL_VAC_ID');
5504: hr_utility.raise_error;
5505: --
5506: end if;
5507: --
5508: else
5515: open csr_val_vacancy_id;
5516: fetch csr_val_vacancy_id into l_business_group_id;
5517: if csr_val_vacancy_id%notfound then
5518: close csr_val_vacancy_id;
5519: hr_utility.set_message(801, 'HR_51297_ASG_INV_VACANCY');
5520: hr_utility.raise_error;
5521: /* hr_multi_message.add
5522: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5523: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5516: fetch csr_val_vacancy_id into l_business_group_id;
5517: if csr_val_vacancy_id%notfound then
5518: close csr_val_vacancy_id;
5519: hr_utility.set_message(801, 'HR_51297_ASG_INV_VACANCY');
5520: hr_utility.raise_error;
5521: /* hr_multi_message.add
5522: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5523: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5524: ); */
5527: close csr_val_vacancy_id;
5528: end if;
5529: --
5530: end if;
5531: hr_utility.set_location(l_proc, 70);
5532: --
5533: -- Check that the vacancy is in the same business group
5534: -- as the business group of the assignment.
5535: --
5534: -- as the business group of the assignment.
5535: --
5536: If p_business_group_id <> l_business_group_id then
5537: --
5538: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5539: hr_utility.raise_error;
5540: --
5541: end if;
5542: hr_utility.set_location(l_proc, 80);
5535: --
5536: If p_business_group_id <> l_business_group_id then
5537: --
5538: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5539: hr_utility.raise_error;
5540: --
5541: end if;
5542: hr_utility.set_location(l_proc, 80);
5543: --
5538: hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5539: hr_utility.raise_error;
5540: --
5541: end if;
5542: hr_utility.set_location(l_proc, 80);
5543: --
5544: end if;
5545: hr_utility.set_location(l_proc, 90);
5546: --
5541: end if;
5542: hr_utility.set_location(l_proc, 80);
5543: --
5544: end if;
5545: hr_utility.set_location(l_proc, 90);
5546: --
5547: end if;
5548: --
5549: hr_utility.set_location(' Leaving:'|| l_proc, 100);
5545: hr_utility.set_location(l_proc, 90);
5546: --
5547: end if;
5548: --
5549: hr_utility.set_location(' Leaving:'|| l_proc, 100);
5550: end chk_vacancy_id;
5551: --
5552: -- ---------------------------------------------------------------------------
5553: -- |----------------------< gen_assignment_sequence >------------------------|
5569: where person_id = p_person_id
5570: and assignment_type = p_assignment_type;
5571: --
5572: begin
5573: hr_utility.set_location('Entering:'|| l_proc, 1);
5574: --
5575: if hr_multi_message.no_exclusive_error
5576: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5577: ) then
5589: ,p_argument => 'person_id'
5590: ,p_argument_value => p_person_id
5591: );
5592: --
5593: hr_utility.set_location(l_proc, 2);
5594: --
5595: -- Generate next assignment sequence
5596: --
5597: open csr_get_ass_seq;
5598: fetch csr_get_ass_seq into l_assignment_sequence;
5599: close csr_get_ass_seq;
5600: p_assignment_sequence := l_assignment_sequence;
5601: end if;
5602: hr_utility.set_location(' Leaving:'|| l_proc, 3);
5603: --
5604: end gen_assignment_sequence;
5605: --
5606: -- ---------------------------------------------------------------------------
5652: and p_effective_date between pas.effective_start_date
5653: and pas.effective_end_date;
5654: --
5655: begin
5656: hr_utility.set_location('Entering:'|| l_proc, 1);
5657: --
5658: -- Check mandatory parameters have been set
5659: --
5660: hr_api.mandatory_arg_error
5682: l_other_manager_exists := csr_other_manager_in_org%found;
5683: --
5684: close csr_other_manager_in_org;
5685: --
5686: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5687: --
5688: return l_other_manager_exists;
5689: end other_managers_in_org;
5690: --
5706: l_proc varchar2(72) := g_package||'gen_date_probation_end';
5707: l_api_updating boolean;
5708: --
5709: begin
5710: hr_utility.set_location('Entering:'|| l_proc, 10);
5711: --
5712: -- Check mandatory parameters have been set
5713: --
5714: hr_api.mandatory_arg_error
5721: (p_api_name => l_proc
5722: ,p_argument => 'validation_start_date'
5723: ,p_argument_value => p_validation_start_date
5724: );
5725: hr_utility.set_location(l_proc, 20);
5726: --
5727: -- Only proceed with generation if :
5728: -- a) The current g_old_rec is current and
5729: -- b) One or more of the values for date probation end, probation period or
5733: (p_assignment_id => p_assignment_id
5734: ,p_effective_date => p_effective_date
5735: ,p_object_version_number => p_object_version_number
5736: );
5737: hr_utility.set_location(l_proc, 30);
5738: --
5739: if NOT l_api_updating
5740: or
5741: (l_api_updating and
5747: or
5748: (nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number) <>
5749: nvl(p_probation_period, hr_api.g_number))))
5750: then
5751: hr_utility.set_location(l_proc, 40);
5752: --
5753: -- Check if probation unit and probation period are both not null.
5754: --
5755: if p_probation_unit is not null and p_probation_period is not null then
5764: If p_probation_unit = 'D' then
5765: --
5766: p_date_probation_end := p_validation_start_date
5767: + (p_probation_period-1);
5768: hr_utility.set_location(l_proc, 50);
5769: --
5770: elsif p_probation_unit = 'W' then
5771: --
5772: p_date_probation_end := p_validation_start_date
5770: elsif p_probation_unit = 'W' then
5771: --
5772: p_date_probation_end := p_validation_start_date
5773: + ((p_probation_period*7)-1);
5774: hr_utility.set_location(l_proc, 60);
5775: --
5776: elsif p_probation_unit = 'M' then
5777: --
5778: p_date_probation_end := add_months(p_validation_start_date,
5776: elsif p_probation_unit = 'M' then
5777: --
5778: p_date_probation_end := add_months(p_validation_start_date,
5779: p_probation_period)-1;
5780: hr_utility.set_location(l_proc, 70);
5781: --
5782: elsif p_probation_unit = 'Y' then
5783: --
5784: p_date_probation_end := add_months(p_validation_start_date,
5782: elsif p_probation_unit = 'Y' then
5783: --
5784: p_date_probation_end := add_months(p_validation_start_date,
5785: 12*p_probation_period)-1;
5786: hr_utility.set_location(l_proc, 80);
5787: --
5788: end if;
5789: --
5790: else
5791: --
5792: -- Nullify date probation end
5793: --
5794: p_date_probation_end := null;
5795: hr_utility.set_location(l_proc, 9);
5796: end if;
5797: --
5798: end if;
5799: --
5798: end if;
5799: --
5800: end if;
5801: --
5802: hr_utility.set_location(' Leaving:'|| l_proc, 10);
5803: end gen_date_probation_end;
5804: --
5805: -- ---------------------------------------------------------------------------
5806: -- |---------------------< chk_internal_address_line >-----------------------|
5818: l_proc varchar2(72) := g_package||'chk_internal_address_line';
5819: l_api_updating boolean;
5820: --
5821: begin
5822: hr_utility.set_location('Entering:'|| l_proc, 10);
5823: --
5824: -- Check mandatory parameters have been set
5825: --
5826: hr_api.mandatory_arg_error
5827: (p_api_name => l_proc
5828: ,p_argument => 'effective_date'
5829: ,p_argument_value => p_effective_date
5830: );
5831: hr_utility.set_location(l_proc, 20);
5832: --
5833: -- Only proceed with validation if :
5834: -- a) The current g_old_rec is current and
5835: -- b) The value for internal address line has changed
5837: l_api_updating := per_asg_shd.api_updating
5838: (p_assignment_id => p_assignment_id
5839: ,p_effective_date => p_effective_date
5840: ,p_object_version_number => p_object_version_number);
5841: hr_utility.set_location(l_proc, 30);
5842: --
5843: if ((l_api_updating and
5844: nvl(per_asg_shd.g_old_rec.internal_address_line, hr_api.g_varchar2)
5845: <> nvl(p_internal_address_line, hr_api.g_varchar2))
5844: nvl(per_asg_shd.g_old_rec.internal_address_line, hr_api.g_varchar2)
5845: <> nvl(p_internal_address_line, hr_api.g_varchar2))
5846: or
5847: (NOT l_api_updating)) then
5848: hr_utility.set_location(l_proc, 40);
5849: --
5850: -- Check if internal address line is not null
5851: --
5852: if p_internal_address_line is not null then
5855: -- assignment.
5856: --
5857: if p_assignment_type not in ('E','A','B','C','O') then
5858: --
5859: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5860: hr_utility.raise_error;
5861: --
5862: end if;
5863: hr_utility.set_location(l_proc, 50);
5856: --
5857: if p_assignment_type not in ('E','A','B','C','O') then
5858: --
5859: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5860: hr_utility.raise_error;
5861: --
5862: end if;
5863: hr_utility.set_location(l_proc, 50);
5864: --
5859: hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5860: hr_utility.raise_error;
5861: --
5862: end if;
5863: hr_utility.set_location(l_proc, 50);
5864: --
5865: end if;
5866: --
5867: end if;
5865: end if;
5866: --
5867: end if;
5868: --
5869: hr_utility.set_location(' Leaving:'|| l_proc, 60);
5870: exception
5871: when app_exception.application_exception then
5872: if hr_multi_message.exception_add
5873: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE'
5871: when app_exception.application_exception then
5872: if hr_multi_message.exception_add
5873: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE'
5874: ) then
5875: hr_utility.set_location(' Leaving:'|| l_proc, 70);
5876: raise;
5877: end if;
5878: hr_utility.set_location(' Leaving:'|| l_proc, 80);
5879: --
5874: ) then
5875: hr_utility.set_location(' Leaving:'|| l_proc, 70);
5876: raise;
5877: end if;
5878: hr_utility.set_location(' Leaving:'|| l_proc, 80);
5879: --
5880: end chk_internal_address_line;
5881: --
5882: --
5896: l_proc varchar2(72) := g_package||'chk_applicant_rank';
5897: l_api_updating boolean;
5898: --
5899: begin
5900: hr_utility.set_location('Entering:'|| l_proc, 10);
5901: --
5902: --
5903: l_api_updating := per_asg_shd.api_updating
5904: (p_assignment_id => p_assignment_id
5910: nvl(per_asg_shd.g_old_rec.applicant_rank, hr_api.g_number) <>
5911: nvl(p_applicant_rank, hr_api.g_number)) or
5912: (NOT l_api_updating)) then
5913:
5914: hr_utility.set_location(l_proc, 20);
5915: --
5916: -- Check if applicant_rank is not null
5917: --
5918: if p_applicant_rank IS NOT NULL then
5916: -- Check if applicant_rank is not null
5917: --
5918: if p_applicant_rank IS NOT NULL then
5919: --
5920: hr_utility.set_location(l_proc, 30);
5921: --
5922: -- applicant rank must be between 0 and 100
5923: --
5924: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5922: -- applicant rank must be between 0 and 100
5923: --
5924: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5925: --
5926: hr_utility.set_location(l_proc, 40);
5927: --
5928: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5929: hr_utility.raise_error;
5930: --
5924: if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5925: --
5926: hr_utility.set_location(l_proc, 40);
5927: --
5928: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5929: hr_utility.raise_error;
5930: --
5931: end if;
5932: --
5925: --
5926: hr_utility.set_location(l_proc, 40);
5927: --
5928: hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5929: hr_utility.raise_error;
5930: --
5931: end if;
5932: --
5933: hr_utility.set_location(l_proc, 50);
5929: hr_utility.raise_error;
5930: --
5931: end if;
5932: --
5933: hr_utility.set_location(l_proc, 50);
5934: --
5935: -- Check that when inserting, the assignment is an applicant or offer assignment
5936: --
5937: if p_assignment_type in ('E','C','B') then
5934: --
5935: -- Check that when inserting, the assignment is an applicant or offer assignment
5936: --
5937: if p_assignment_type in ('E','C','B') then
5938: hr_utility.set_location(l_proc, 60);
5939: --
5940: -- Check if the employee assignment is being updated
5941: --
5942: if l_api_updating then
5942: if l_api_updating then
5943: --
5944: -- non applicant, rank can only be updated to null
5945: --
5946: hr_utility.set_message(800, 'HR_289950_APP_RANK_INV_UPD');
5947: hr_utility.raise_error;
5948: --
5949: else -- inserting a non applicant
5950: --
5943: --
5944: -- non applicant, rank can only be updated to null
5945: --
5946: hr_utility.set_message(800, 'HR_289950_APP_RANK_INV_UPD');
5947: hr_utility.raise_error;
5948: --
5949: else -- inserting a non applicant
5950: --
5951: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5947: hr_utility.raise_error;
5948: --
5949: else -- inserting a non applicant
5950: --
5951: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5952: hr_utility.raise_error;
5953: --
5954: end if;
5955: --
5948: --
5949: else -- inserting a non applicant
5950: --
5951: hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5952: hr_utility.raise_error;
5953: --
5954: end if;
5955: --
5956: end if;
5957: --
5958: end if;
5959: --
5960: end if;
5961: hr_utility.set_location('Leaving:'|| l_proc, 70);
5962: exception
5963: when app_exception.application_exception then
5964: if hr_multi_message.exception_add
5965: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICANT_RANK'
5993: and rownum = 1;
5994: --
5995: begin
5996: --
5997: hr_utility.set_location('Entering:'|| l_proc, 10);
5998: --
5999: l_api_updating := per_asg_shd.api_updating
6000: (p_assignment_id => p_assignment_id
6001: ,p_effective_date => p_effective_date
6006: nvl(per_asg_shd.g_old_rec.posting_content_id, hr_api.g_number) <>
6007: nvl(p_posting_content_id, hr_api.g_number)) or
6008: (NOT l_api_updating)) then
6009:
6010: hr_utility.set_location(l_proc, 20);
6011: --
6012: -- Check if posting_content_id is not null
6013: --
6014: if p_posting_content_id IS NOT NULL then
6022: l_posting_content_id := null;
6023: end if;
6024: --
6025: close irc_exists;
6026: hr_utility.set_location(l_proc, 30);
6027: --
6028: if (l_posting_content_id <> p_posting_content_id) then
6029: --
6030: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
6026: hr_utility.set_location(l_proc, 30);
6027: --
6028: if (l_posting_content_id <> p_posting_content_id) then
6029: --
6030: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
6031: hr_utility.raise_error;
6032: --
6033: end if;
6034: --
6027: --
6028: if (l_posting_content_id <> p_posting_content_id) then
6029: --
6030: hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
6031: hr_utility.raise_error;
6032: --
6033: end if;
6034: --
6035: -- Check that when inserting, the assignment is an applicant or offer assignment
6034: --
6035: -- Check that when inserting, the assignment is an applicant or offer assignment
6036: --
6037: if p_assignment_type in ('E','C','B') then
6038: hr_utility.set_location(l_proc, 40);
6039: --
6040: -- Check if the employee assignment is being updated
6041: --
6042: if l_api_updating then
6042: if l_api_updating then
6043: --
6044: -- non applicant/offer, posting_content_id can only be updated to null
6045: --
6046: hr_utility.set_message(800, 'HR_289951_POSTING_CONT_INV_UPD');
6047: hr_utility.raise_error;
6048: --
6049: else -- inserting a non applicant
6050: --
6043: --
6044: -- non applicant/offer, posting_content_id can only be updated to null
6045: --
6046: hr_utility.set_message(800, 'HR_289951_POSTING_CONT_INV_UPD');
6047: hr_utility.raise_error;
6048: --
6049: else -- inserting a non applicant
6050: --
6051: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
6047: hr_utility.raise_error;
6048: --
6049: else -- inserting a non applicant
6050: --
6051: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
6052: hr_utility.raise_error;
6053: --
6054: end if;
6055: --
6048: --
6049: else -- inserting a non applicant
6050: --
6051: hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
6052: hr_utility.raise_error;
6053: --
6054: end if;
6055: --
6056: end if;
6057: --
6058: end if;
6059: --
6060: end if;
6061: hr_utility.set_location('Leaving: '||l_proc, 50);
6062: exception
6063: when app_exception.application_exception then
6064: if hr_multi_message.exception_add
6065: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSTING_CONTENT_ID'