[Home] [Help]
19: -- ---------------------------------------------------------------------------
20: --
21: procedure set_security_group_id
22: (
23: p_assignment_id in per_all_assignments_f.assignment_id%TYPE
24: ,p_associated_column1 in varchar2 default null
25: ) is
26: --
27: -- Declare cursor
29: cursor csr_sec_grp is
30: select pbg.security_group_id, pbg.legislation_code
31: from per_business_groups_perf pbg
32: where pbg.business_group_id = (select distinct asg.business_group_id from
33: per_all_assignments_f asg
34: where asg.assignment_id = p_assignment_id);
35:
36: --
37: -- Local variables
1071: l_other_manager_warning boolean;
1072: l_hourly_salaried_warning boolean;
1073: L_inv_pos_grade_warning boolean := false;
1074: L_inv_job_grade_warning boolean := false;
1075: l_source_type per_all_assignments_f.source_type%TYPE default NULL; --- Fix For Bug #7481310
1076: --
1077: Begin
1078: if g_debug then
1079: l_proc := g_package||'insert_validate';
1182: -- if p_rec.vacancy_id is not null then -- fix for the bug#11905251
1183:
1184: if p_rec.assignment_type = 'O' and p_rec.vacancy_id is not null then
1185:
1186: select source_type into l_source_type from per_all_assignments_f
1187: where person_id = p_rec.person_id and BUSINESS_GROUP_ID = p_rec.business_group_id
1188: and ASSIGNMENT_TYPE = 'A' and VACANCY_ID = p_rec.vacancy_id and
1189: p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1190:
1193: /* fix for the bug#11905251 starts
1194: --- Fix For Bug # 10335332 Starts ---
1195: else
1196: SELECT paf.source_type into l_source_type
1197: FROM per_all_assignments_f paf
1198: ,PER_VAC_LINKED_ASSIGNMENTS ivla
1199: WHERE paf.assignment_type = 'A'
1200: AND trunc (SYSDATE) BETWEEN effective_start_date
1201: AND effective_end_date
3371: -- |-----------------------< chk_application_id >----------------------------|
3372: -- ---------------------------------------------------------------------------
3373: --
3374: procedure chk_application_id
3375: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3376: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3372: -- ---------------------------------------------------------------------------
3373: --
3374: procedure chk_application_id
3375: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3376: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3380: ,p_effective_date in date
3373: --
3374: procedure chk_application_id
3375: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3376: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3380: ,p_effective_date in date
3381: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3374: procedure chk_application_id
3375: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3376: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3380: ,p_effective_date in date
3381: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3382: ,p_validation_start_date in date
3375: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3376: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3380: ,p_effective_date in date
3381: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3382: ,p_validation_start_date in date
3383: )
3377: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3378: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3379: ,p_application_id in per_all_assignments_f.application_id%TYPE
3380: ,p_effective_date in date
3381: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3382: ,p_validation_start_date in date
3383: )
3384: is
3385: --
3385: --
3386: l_proc varchar2(72) := g_package||'chk_application_id';
3387: l_exists varchar2(1);
3388: l_api_updating boolean;
3389: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3390: --
3391: cursor csr_valid_apl_1 is
3392: select business_group_id
3393: from per_applications
3465: --
3466: hr_utility.set_message(801, 'HR_51212_ASG_INV_APL_ASG_APL');
3467: hr_utility.raise_error;
3468: /* hr_multi_message.add
3469: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3470: );*/
3471: --
3472: end if;
3473: if g_debug then
3480: --
3481: hr_utility.set_message(801, 'HR_51213_ASG_INV_UPD_APL');
3482: hr_utility.raise_error;
3483: /*hr_multi_message.add
3484: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3485: ); */ --
3486: --
3487: end if;
3488: if g_debug then
3508: close csr_valid_apl_1;
3509: hr_utility.set_message(801, 'HR_51210_ASG_INV_APL');
3510: hr_utility.raise_error;
3511: /* hr_multi_message.add
3512: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3513: ,p_associated_column2 =>
3514: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3515: ,p_associated_column3 =>
3516: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3510: hr_utility.raise_error;
3511: /* hr_multi_message.add
3512: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3513: ,p_associated_column2 =>
3514: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3515: ,p_associated_column3 =>
3516: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3517: ); */
3518: --
3512: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3513: ,p_associated_column2 =>
3514: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3515: ,p_associated_column3 =>
3516: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3517: ); */
3518: --
3519: else
3520: close csr_valid_apl_1;
3531: if csr_valid_apl_2%notfound then
3532: close csr_valid_apl_2;
3533: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3534: hr_multi_message.add
3535: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3536: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3537: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3538: );
3539: --
3532: close csr_valid_apl_2;
3533: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3534: hr_multi_message.add
3535: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3536: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3537: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3538: );
3539: --
3540: else
3533: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3534: hr_multi_message.add
3535: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3536: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3537: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3538: );
3539: --
3540: else
3541: close csr_valid_apl_2;
3549: If p_business_group_id <> l_business_group_id then
3550: --
3551: hr_utility.set_message(801, 'HR_51214_ASG_INV_APL_BUS_GRP');
3552: hr_multi_message.add
3553: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3554: );
3555: --
3556: end if;
3557: end if;
3568: If p_application_id is not null then
3569: --
3570: hr_utility.set_message(801, 'HR_51211_ASG_INV_E_ASG_APL_ID');
3571: hr_multi_message.add
3572: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3573: );
3574: --
3575: end if;
3576: if g_debug then
3590: -- |---------------------< gen_chk_assignment_number >-----------------------|
3591: -- ---------------------------------------------------------------------------
3592: --
3593: procedure gen_chk_assignment_number
3594: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3591: -- ---------------------------------------------------------------------------
3592: --
3593: procedure gen_chk_assignment_number
3594: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3592: --
3593: procedure gen_chk_assignment_number
3594: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3600: ,p_effective_date in date
3593: procedure gen_chk_assignment_number
3594: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3600: ,p_effective_date in date
3601: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3594: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3600: ,p_effective_date in date
3601: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3602: )
3595: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3596: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3600: ,p_effective_date in date
3601: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3602: )
3603: is
3597: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3598: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3599: ,p_person_id in per_all_assignments_f.person_id%TYPE
3600: ,p_effective_date in date
3601: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3602: )
3603: is
3604: --
3605: l_proc varchar2(72) := g_package||'gen_chk_assignment_number';
3686: -- Raise an error: Assignment Number must be null for
3687: -- applicant, offer and benefit assignments
3688: hr_utility.set_message(801, 'HR_51017_ASG_NUM_NULL_FOR_APL');
3689: hr_multi_message.add
3690: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER');
3691: --
3692: end if;
3693: --
3694: if g_debug then
3700: --
3701: elsif p_assignment_type In ('E','C') then
3702: --
3703: if hr_multi_message.no_exclusive_error
3704: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
3705: --
3706: -- For worker assignments validate or generate the assignment number
3707: --
3708: open csr_get_work_no;
3733: --
3734: close csr_get_work_no;
3735: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3736: hr_multi_message.add
3737: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3738: ,p_associated_column2 =>
3739: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3740: --
3741: end if;
3735: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3736: hr_multi_message.add
3737: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3738: ,p_associated_column2 =>
3739: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3740: --
3741: end if;
3742: --
3743: end if; -- no exclusive error
3755: nvl(per_asg_shd.g_old_rec.assignment_type, hr_api.g_varchar2)
3756: = 'A' and p_assignment_type = 'E') then
3757: --
3758: if hr_multi_message.no_exclusive_error
3759: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
3760: --
3761: -- For employee assignments validate or generate the assignment number
3762: --
3763: open csr_get_work_no;
3789: close csr_get_work_no;
3790: --
3791: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3792: hr_multi_message.add
3793: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3794: ,p_associated_column2 =>
3795: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3796: --
3797: end if;
3791: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3792: hr_multi_message.add
3793: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3794: ,p_associated_column2 =>
3795: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3796: --
3797: end if;
3798: --
3799: end if; -- no exclusive error
3814: -- |-----------------------< chk_assignment_category >------------------------|
3815: -- ---------------------------------------------------------------------------
3816: --
3817: PROCEDURE chk_assignment_category
3818: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3819: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3820: ,p_effective_date in date
3821: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3822: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3815: -- ---------------------------------------------------------------------------
3816: --
3817: PROCEDURE chk_assignment_category
3818: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3819: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3820: ,p_effective_date in date
3821: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3822: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3823: ,p_validation_start_date in date
3818: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3819: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3820: ,p_effective_date in date
3821: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3822: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3823: ,p_validation_start_date in date
3824: ,p_validation_end_date in date) IS
3825: --
3826: l_proc varchar2(72);
3894: --
3895: WHEN app_exception.application_exception THEN
3896: --
3897: IF hr_multi_message.exception_add
3898: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_CATEGORY') THEN
3899: --
3900: if g_debug then
3901: hr_utility.set_location(' Leaving:'|| l_proc, 998);
3902: end if;
3951: -- Declare cursors and local variables
3952: --
3953: -- Out variables
3954: --
3955: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3956: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
3957: l_proc varchar2(72);
3958: --
3959: cursor csr_get_ast_details is
3968: hr_utility.set_location('Entering:'|| l_proc, 1);
3969: end if;
3970: --
3971: if hr_multi_message.no_exclusive_error
3972: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
3973: ) then
3974: --
3975: -- If p_assignment_status_type_id is g_number then derive it's default value.
3976: --
4070: -- ---------------------------------------------------------------------------
4071: --
4072: procedure chk_assignment_status_type_id
4073: (p_rec in per_asg_shd.g_rec_type
4074: ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
4075: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4076: )
4077: is
4078: --
4071: --
4072: procedure chk_assignment_status_type_id
4073: (p_rec in per_asg_shd.g_rec_type
4074: ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
4075: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4076: )
4077: is
4078: --
4079: l_api_updating boolean;
4084: l_ast_legislation_code per_business_groups.legislation_code%TYPE;
4085: l_bus_legislation_code per_business_groups.legislation_code%TYPE;
4086: l_active_flag per_assignment_status_types.active_flag%TYPE;
4087: l_ast_active_flag per_assignment_status_types.active_flag%TYPE;
4088: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
4089: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4090: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
4091: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
4092: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
4087: l_ast_active_flag per_assignment_status_types.active_flag%TYPE;
4088: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
4089: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4090: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
4091: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
4092: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
4093: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
4094: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
4095: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
4089: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4090: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
4091: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
4092: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
4093: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
4094: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
4095: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
4096: --
4097: -- Cursor to check that a assignment status type exists in
4090: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
4091: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
4092: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
4093: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
4094: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
4095: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
4096: --
4097: -- Cursor to check that a assignment status type exists in
4098: -- PER_ASSIGNMENT_STATUS_TYPES.
4133: -- status type PER_SYSTEM_STATUS of 'ACTIVE_ASSIGN'
4134: --
4135: cursor csr_get_initial_ins_date is
4136: select nvl(min(asg.effective_start_date), hr_api.g_eot)
4137: from per_all_assignments_f asg
4138: ,per_assignment_status_types ast
4139: where asg.assignment_id = p_rec.assignment_id
4140: and ast.assignment_status_type_id = asg.assignment_status_type_id
4141: and ast.per_system_status = 'ACTIVE_ASSIGN';
4144: -- assignment
4145: --
4146: cursor csr_get_apl_asg_min_esd is
4147: select min(effective_start_date)
4148: from per_all_assignments_f
4149: where assignment_id = p_rec.assignment_id
4150: and assignment_type = 'A';
4151: --
4152: -- Cursor to retrieve the PER SYSTEM STATUS for the first datetracked
4152: -- Cursor to retrieve the PER SYSTEM STATUS for the first datetracked
4153: -- instance of an applicant assignment.
4154: --
4155: cursor csr_get_fir_dt_inst_ast_pss
4156: (c_effective_start_date per_all_assignments_f.effective_start_date%TYPE)
4157: is
4158: select ast.per_system_status
4159: from per_all_assignments_f asg
4160: ,per_assignment_status_types ast
4155: cursor csr_get_fir_dt_inst_ast_pss
4156: (c_effective_start_date per_all_assignments_f.effective_start_date%TYPE)
4157: is
4158: select ast.per_system_status
4159: from per_all_assignments_f asg
4160: ,per_assignment_status_types ast
4161: where asg.assignment_id = p_rec.assignment_id
4162: and asg.effective_start_date = c_effective_start_date
4163: and ast.assignment_status_type_id = asg.assignment_status_type_id;
4340: then
4341: --
4342: hr_utility.set_message(801, 'HR_7946_ASG_INV_TERM_ASS_UPD');
4343: hr_multi_message.add
4344: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4345: );
4346: --
4347: -- fix for bug 4557189. Commented out code.
4348: elsif (
4382: then
4383: --
4384: hr_utility.set_message(801, 'HR_7946_ASG_INV_TERM_ASS_UPD');
4385: hr_multi_message.add
4386: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4387: );
4388: --
4389: end if;
4390: if g_debug then
4417: If l_fir_dt_ast_pss = 'TERM_APL' then
4418: --
4419: hr_utility.set_message(801, 'HR_51275_ASG_INV_F_DT_AST_PSS');
4420: hr_multi_message.add
4421: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4422: );
4423: --
4424: end if;
4425: if g_debug then
4457: if csr_valid_ast%notfound then
4458: close csr_valid_ast;
4459: hr_utility.set_message(801, 'HR_7940_ASG_INV_ASG_STAT_TYPE');
4460: hr_multi_message.add
4461: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4462: );
4463: else
4464: close csr_valid_ast;
4465: end if;
4474: and l_business_group_id <> p_rec.business_group_id then
4475: --
4476: hr_utility.set_message(801, 'HR_51207_ASG_INV_AST_BUS_GRP');
4477: hr_multi_message.add
4478: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4479: );
4480: --
4481: end if;
4482: if g_debug then
4529: If l_active_flag <> 'Y' then
4530: --
4531: hr_utility.set_message(801, 'HR_51206_ASG_INV_AST_ACT_FLG');
4532: hr_multi_message.add
4533: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4534: );
4535: --
4536: end if;
4537: if g_debug then
4556: --
4557: hr_utility.set_message(801, 'HR_7917_ASG_INV_STAT_TYPE');
4558: hr_multi_message.add
4559: (p_associated_column1 =>
4560: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4561: );
4562: --
4563: end if;
4564: if g_debug then
4575: --
4576: hr_utility.set_message(801, 'HR_7942_ASG_INV_STAT_NOT_TERM');
4577: hr_multi_message.add
4578: (p_associated_column1 =>
4579: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4580: );
4581: --
4582: end if;
4583: if g_debug then
4607: --
4608: hr_utility.set_message(800,'HR_7915_ASG_INV_STAT_UPD_DATE');
4609: hr_multi_message.add
4610: (p_associated_column1 =>
4611: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4612: ,p_associated_column2 =>
4613: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4614: );
4615: --
4609: hr_multi_message.add
4610: (p_associated_column1 =>
4611: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4612: ,p_associated_column2 =>
4613: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4614: );
4615: --
4616: end if;
4617: if g_debug then
4632: --
4633: hr_utility.set_message(801, 'HR_7941_ASG_INV_STAT_NOT_ACT');
4634: hr_multi_message.add
4635: (p_associated_column1 =>
4636: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4637: );
4638: --
4639: end if;
4640: --
4709: then
4710: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
4711: hr_multi_message.add
4712: (p_associated_column1 =>
4713: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4714: );
4715: --
4716: end if;
4717: if g_debug then
4733: -- |-----------------------< chk_assignment_type >---------------------------|
4734: -- ---------------------------------------------------------------------------
4735: --
4736: procedure chk_assignment_type
4737: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4738: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4739: ,p_person_id in per_all_assignments_f.person_id%TYPE
4740: ,p_effective_date in date
4741: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4734: -- ---------------------------------------------------------------------------
4735: --
4736: procedure chk_assignment_type
4737: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4738: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4739: ,p_person_id in per_all_assignments_f.person_id%TYPE
4740: ,p_effective_date in date
4741: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4742: ,p_validation_start_date in date
4735: --
4736: procedure chk_assignment_type
4737: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4738: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4739: ,p_person_id in per_all_assignments_f.person_id%TYPE
4740: ,p_effective_date in date
4741: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4742: ,p_validation_start_date in date
4743: )
4737: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4738: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4739: ,p_person_id in per_all_assignments_f.person_id%TYPE
4740: ,p_effective_date in date
4741: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4742: ,p_validation_start_date in date
4743: )
4744: is
4745: --
4896: exception
4897: when app_exception.application_exception then
4898: if hr_multi_message.exception_add
4899: (p_associated_column1 =>
4900: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4901: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4902: ) then
4903: if g_debug then
4904: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4897: when app_exception.application_exception then
4898: if hr_multi_message.exception_add
4899: (p_associated_column1 =>
4900: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4901: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4902: ) then
4903: if g_debug then
4904: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4905: end if;
4915: -- |-------------------------< chk_change_reason >---------------------------|
4916: -- ---------------------------------------------------------------------------
4917: --
4918: procedure chk_change_reason
4919: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4920: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4921: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4922: ,p_effective_date in date
4923: ,p_validation_start_date in date
4916: -- ---------------------------------------------------------------------------
4917: --
4918: procedure chk_change_reason
4919: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4920: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4921: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4922: ,p_effective_date in date
4923: ,p_validation_start_date in date
4924: ,p_validation_end_date in date
4917: --
4918: procedure chk_change_reason
4919: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4920: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4921: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4922: ,p_effective_date in date
4923: ,p_validation_start_date in date
4924: ,p_validation_end_date in date
4925: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4921: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4922: ,p_effective_date in date
4923: ,p_validation_start_date in date
4924: ,p_validation_end_date in date
4925: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4926: )
4927: is
4928: --
4929: l_api_updating boolean;
5069: end if;
5070: exception
5071: when app_exception.application_exception then
5072: if hr_multi_message.exception_add
5073: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.CHANGE_REASON'
5074: ) then
5075: if g_debug then
5076: hr_utility.set_location(' Leaving:'|| l_proc, 90);
5077: end if;
5112: -- Access Status:
5113: -- Internal Table Handler Use Only.
5114: --
5115: procedure chk_contig_ass
5116: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5117: ,p_person_id in per_all_assignments_f.person_id%TYPE
5118: ,p_effective_date in date
5119: ,p_datetrack_mode in varchar2
5120: )
5113: -- Internal Table Handler Use Only.
5114: --
5115: procedure chk_contig_ass
5116: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5117: ,p_person_id in per_all_assignments_f.person_id%TYPE
5118: ,p_effective_date in date
5119: ,p_datetrack_mode in varchar2
5120: )
5121: is
5127:
5128: select null
5129: from sys.dual
5130: where exists(select null
5131: from per_all_assignments_f pas
5132: , per_periods_of_service ppos
5133: where pas.effective_start_date <= p_effective_date
5134: and ppos.period_of_service_id = pas.period_of_service_id
5135: and pas.person_id = p_person_id
5134: and ppos.period_of_service_id = pas.period_of_service_id
5135: and pas.person_id = p_person_id
5136: and pas.primary_flag = 'N'
5137: and (exists(select null
5138: from per_all_assignments_f pas2
5139: , per_periods_of_service ppos2
5140: where pas2.effective_end_date =
5141: nvl(ppos2.actual_termination_date, hr_api.g_eot)
5142: and pas.assignment_id = pas2.assignment_id
5177: end if;
5178: exception
5179: when app_exception.application_exception then
5180: if hr_multi_message.exception_add
5181: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5182: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5183: ) then
5184: if g_debug then
5185: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5178: exception
5179: when app_exception.application_exception then
5180: if hr_multi_message.exception_add
5181: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5182: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5183: ) then
5184: if g_debug then
5185: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5186: end if;
5195: -- |---------------------< chk_date_probation_end >--------------------------|
5196: -- ---------------------------------------------------------------------------
5197: --
5198: procedure chk_date_probation_end
5199: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5200: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5201: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5196: -- ---------------------------------------------------------------------------
5197: --
5198: procedure chk_date_probation_end
5199: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5200: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5201: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5204: ,p_validation_start_date in date
5197: --
5198: procedure chk_date_probation_end
5199: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5200: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5201: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5204: ,p_validation_start_date in date
5205: ,p_effective_date in date
5198: procedure chk_date_probation_end
5199: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5200: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5201: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5204: ,p_validation_start_date in date
5205: ,p_effective_date in date
5206: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5199: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5200: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5201: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5204: ,p_validation_start_date in date
5205: ,p_effective_date in date
5206: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5207: )
5202: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5203: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5204: ,p_validation_start_date in date
5205: ,p_effective_date in date
5206: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5207: )
5208: is
5209: --
5210: l_proc varchar2(72) := g_package||'chk_date_probation_end';
5213: l_min_effective_start_date date;
5214: --
5215: cursor csr_get_min_asg_esd is
5216: select min(effective_start_date)
5217: from per_all_assignments_f
5218: where assignment_id = p_assignment_id;
5219: --
5220: begin
5221: if g_debug then
5286: if p_date_probation_end < l_min_effective_start_date then
5287: --
5288: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5289: hr_multi_message.add
5290: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5291: );
5292: --
5293: end if;
5294: if g_debug then
5303: if p_date_probation_end < p_validation_start_date then
5304: --
5305: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5306: hr_multi_message.add
5307: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5308: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5309: );
5310: --
5311: end if;
5304: --
5305: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5306: hr_multi_message.add
5307: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5308: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5309: );
5310: --
5311: end if;
5312: if g_debug then
5335: -- |--------------------< chk_default_code_comb_id >-------------------------|
5336: -- ---------------------------------------------------------------------------
5337: --
5338: procedure chk_default_code_comb_id
5339: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5340: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5341: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5342: ,p_effective_date in date
5343: ,p_validation_start_date in date
5336: -- ---------------------------------------------------------------------------
5337: --
5338: procedure chk_default_code_comb_id
5339: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5340: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5341: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5342: ,p_effective_date in date
5343: ,p_validation_start_date in date
5344: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5337: --
5338: procedure chk_default_code_comb_id
5339: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5340: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5341: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5342: ,p_effective_date in date
5343: ,p_validation_start_date in date
5344: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5345: )
5340: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5341: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5342: ,p_effective_date in date
5343: ,p_validation_start_date in date
5344: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5345: )
5346: is
5347: --
5348: l_api_updating boolean;
5411: if p_assignment_type not in ('E','A','B','C','O') then
5412: --
5413: hr_utility.set_message(801, 'HR_51177_ASG_INV_ASG_TYP_DCC');
5414: hr_multi_message.add
5415: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5416: );
5417: --
5418: end if;
5419: if g_debug then
5427: if csr_valid_def_cod_comb%notfound then
5428: close csr_valid_def_cod_comb;
5429: hr_utility.set_message(801, 'HR_51148_ASG_INV_DEF_COD_COM');
5430: hr_multi_message.add
5431: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5432: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5433: );
5434: --
5435: else
5428: close csr_valid_def_cod_comb;
5429: hr_utility.set_message(801, 'HR_51148_ASG_INV_DEF_COD_COM');
5430: hr_multi_message.add
5431: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5432: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5433: );
5434: --
5435: else
5436: close csr_valid_def_cod_comb;
5476: -- Access Status:
5477: -- Internal Table Handler Use Only.
5478: --
5479: procedure chk_del_organization_id
5480: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5481: ,p_effective_date in date
5482: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5483: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5484: ,p_org_now_no_manager_warning in out nocopy boolean
5478: --
5479: procedure chk_del_organization_id
5480: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5481: ,p_effective_date in date
5482: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5483: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5484: ,p_org_now_no_manager_warning in out nocopy boolean
5485: )
5486: is
5479: procedure chk_del_organization_id
5480: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5481: ,p_effective_date in date
5482: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5483: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5484: ,p_org_now_no_manager_warning in out nocopy boolean
5485: )
5486: is
5487: --
5523: -- |-----------------------< chk_employment_category >-----------------------|
5524: -- ---------------------------------------------------------------------------
5525: --
5526: procedure chk_employment_category
5527: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5528: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5529: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5530: ,p_effective_date in date
5531: ,p_validation_start_date in date
5524: -- ---------------------------------------------------------------------------
5525: --
5526: procedure chk_employment_category
5527: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5528: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5529: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5530: ,p_effective_date in date
5531: ,p_validation_start_date in date
5532: ,p_validation_end_date in date
5525: --
5526: procedure chk_employment_category
5527: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5528: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5529: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5530: ,p_effective_date in date
5531: ,p_validation_start_date in date
5532: ,p_validation_end_date in date
5533: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5529: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5530: ,p_effective_date in date
5531: ,p_validation_start_date in date
5532: ,p_validation_end_date in date
5533: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5534: )
5535: is
5536: --
5537: l_proc varchar2(72) := g_package||'chk_employment_category';
5672: end if;
5673: exception
5674: when app_exception.application_exception then
5675: if hr_multi_message.exception_add
5676: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY'
5677: ) then
5678: if g_debug then
5679: hr_utility.set_location(' Leaving:'|| l_proc, 90);
5680: end if;
5689: -- |-------------------------< chk_frequency >-------------------------------|
5690: -- ---------------------------------------------------------------------------
5691: --
5692: procedure chk_frequency
5693: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5694: ,p_frequency in per_all_assignments_f.frequency%TYPE
5695: ,p_effective_date in date
5696: ,p_validation_start_date in date
5697: ,p_validation_end_date in date
5690: -- ---------------------------------------------------------------------------
5691: --
5692: procedure chk_frequency
5693: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5694: ,p_frequency in per_all_assignments_f.frequency%TYPE
5695: ,p_effective_date in date
5696: ,p_validation_start_date in date
5697: ,p_validation_end_date in date
5698: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5694: ,p_frequency in per_all_assignments_f.frequency%TYPE
5695: ,p_effective_date in date
5696: ,p_validation_start_date in date
5697: ,p_validation_end_date in date
5698: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5699: )
5700: is
5701: --
5702: l_api_updating boolean;
5791: end if;
5792: exception
5793: when app_exception.application_exception then
5794: if hr_multi_message.exception_add
5795: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
5796: ) then
5797: if g_debug then
5798: hr_utility.set_location(' Leaving:'|| l_proc, 90);
5799: end if;
5831: -- Access Status:
5832: -- Internal Table Handler Use Only.
5833: --
5834: procedure chk_future_primary
5835: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5836: ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5837: ,p_datetrack_mode in varchar2
5838: ,p_effective_date in date
5839: )
5832: -- Internal Table Handler Use Only.
5833: --
5834: procedure chk_future_primary
5835: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5836: ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5837: ,p_datetrack_mode in varchar2
5838: ,p_effective_date in date
5839: )
5840: is
5843: l_proc varchar2(72) := g_package||'chk_future_primary';
5844: --
5845: cursor csr_chk_fut_prim is
5846: select null
5847: from per_all_assignments_f
5848: where assignment_id = p_assignment_id
5849: and effective_start_date >= p_effective_date
5850: and primary_flag = 'Y';
5851: --
5883: end if;
5884: exception
5885: when app_exception.application_exception then
5886: if hr_multi_message.exception_add
5887: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID'
5888: ) then
5889: if g_debug then
5890: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5891: end if;
5900: -- |---------------------------< chk_grade_id >------------------------------|
5901: -- ---------------------------------------------------------------------------
5902: --
5903: procedure chk_grade_id
5904: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5901: -- ---------------------------------------------------------------------------
5902: --
5903: procedure chk_grade_id
5904: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5902: --
5903: procedure chk_grade_id
5904: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5903: procedure chk_grade_id
5904: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5904: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5912: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5905: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5906: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5912: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5913: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5907: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5912: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5913: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5914: )
5915: is
5908: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5912: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5913: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5914: )
5915: is
5916: --
5909: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5910: ,p_effective_date in date
5911: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5912: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5913: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5914: )
5915: is
5916: --
5917: l_exists varchar2(1);
5917: l_exists varchar2(1);
5918: l_api_updating boolean;
5919: l_business_group_id number(15);
5920: l_proc varchar2(72) := g_package||'chk_grade_id';
5921: l_vac_grade_id per_all_assignments_f.grade_id%TYPE;
5922: --
5923: cursor csr_valid_grade is
5924: select business_group_id
5925: from per_grades
5995: if csr_valid_grade%notfound then
5996: close csr_valid_grade;
5997: hr_utility.set_message(801, 'HR_7393_ASG_INVALID_GRADE');
5998: hr_multi_message.add
5999: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6000: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6001: );
6002: --
6003: else
5996: close csr_valid_grade;
5997: hr_utility.set_message(801, 'HR_7393_ASG_INVALID_GRADE');
5998: hr_multi_message.add
5999: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6000: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6001: );
6002: --
6003: else
6004: close csr_valid_grade;
6013: if l_business_group_id <> p_business_group_id then
6014: --
6015: hr_utility.set_message(801, 'HR_7371_ASG_INVALID_BG_GRADE');
6016: hr_multi_message.add
6017: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6018: );
6019: --
6020: end if;
6021: if g_debug then
6026: -- When grade is null special ceiling step should be null
6027: --
6028: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
6029: hr_multi_message.add
6030: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6031: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
6032: );
6033: --
6034: end if;
6027: --
6028: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
6029: hr_multi_message.add
6030: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6031: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
6032: );
6033: --
6034: end if;
6035: if g_debug then
6047: -- |-----------------------------< chk_job_id >------------------------------|
6048: -- ---------------------------------------------------------------------------
6049: --
6050: procedure chk_job_id
6051: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6052: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6053: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6054: ,p_job_id in per_all_assignments_f.job_id%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6048: -- ---------------------------------------------------------------------------
6049: --
6050: procedure chk_job_id
6051: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6052: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6053: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6054: ,p_job_id in per_all_assignments_f.job_id%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6056: ,p_effective_date in date
6049: --
6050: procedure chk_job_id
6051: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6052: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6053: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6054: ,p_job_id in per_all_assignments_f.job_id%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6056: ,p_effective_date in date
6057: ,p_validation_start_date in date
6050: procedure chk_job_id
6051: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6052: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6053: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6054: ,p_job_id in per_all_assignments_f.job_id%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6056: ,p_effective_date in date
6057: ,p_validation_start_date in date
6058: ,p_validation_end_date in date
6051: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6052: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6053: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6054: ,p_job_id in per_all_assignments_f.job_id%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6056: ,p_effective_date in date
6057: ,p_validation_start_date in date
6058: ,p_validation_end_date in date
6059: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6055: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6056: ,p_effective_date in date
6057: ,p_validation_start_date in date
6058: ,p_validation_end_date in date
6059: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6060: )
6061: is
6062: --
6063: l_proc varchar2(72) := g_package||'chk_job_id';
6062: --
6063: l_proc varchar2(72) := g_package||'chk_job_id';
6064: l_exists varchar2(1);
6065: l_api_updating boolean;
6066: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6067: l_vac_job_id per_all_assignments_f.job_id%TYPE;
6068: --
6069: --
6070: -- Bug 33552211 Start Here
6063: l_proc varchar2(72) := g_package||'chk_job_id';
6064: l_exists varchar2(1);
6065: l_api_updating boolean;
6066: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6067: l_vac_job_id per_all_assignments_f.job_id%TYPE;
6068: --
6069: --
6070: -- Bug 33552211 Start Here
6071: -- Description : Changed the cursor sql query to improve the performance
6158: if csr_valid_job%notfound then
6159: close csr_valid_job;
6160: hr_utility.set_message(801, 'HR_51172_ASG_INV_DT_JOB');
6161: hr_multi_message.add
6162: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6163: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6164: );
6165: --
6166: else
6159: close csr_valid_job;
6160: hr_utility.set_message(801, 'HR_51172_ASG_INV_DT_JOB');
6161: hr_multi_message.add
6162: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6163: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6164: );
6165: --
6166: else
6167: close csr_valid_job;
6176: If p_business_group_id <> l_business_group_id then
6177: --
6178: hr_utility.set_message(801, 'HR_51173_ASG_INV_DT_JOB_BG');
6179: hr_multi_message.add
6180: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6181: );
6182: --
6183: end if;
6184: if g_debug then
6201: -- |----------------------< chk_job_id_grade_id >----------------------------|
6202: -- ---------------------------------------------------------------------------
6203: --
6204: procedure chk_job_id_grade_id
6205: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6206: ,p_job_id in per_all_assignments_f.job_id%TYPE
6207: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6208: ,p_effective_date in date
6209: ,p_validation_start_date in date
6202: -- ---------------------------------------------------------------------------
6203: --
6204: procedure chk_job_id_grade_id
6205: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6206: ,p_job_id in per_all_assignments_f.job_id%TYPE
6207: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6208: ,p_effective_date in date
6209: ,p_validation_start_date in date
6210: ,p_validation_end_date in date
6203: --
6204: procedure chk_job_id_grade_id
6205: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6206: ,p_job_id in per_all_assignments_f.job_id%TYPE
6207: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6208: ,p_effective_date in date
6209: ,p_validation_start_date in date
6210: ,p_validation_end_date in date
6211: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6207: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6208: ,p_effective_date in date
6209: ,p_validation_start_date in date
6210: ,p_validation_end_date in date
6211: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6212: ,p_inv_job_grade_warning out nocopy boolean
6213: )
6214: is
6215: --
6246:
6247: -- Bug13041074 fix
6248: cursor csr_assignment_type is
6249: select assignment_type
6250: from per_all_assignments_f
6251: where assignment_id = p_assignment_id
6252: and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
6253:
6254: begin
6256: hr_utility.set_location('Entering:'|| l_proc, 10);
6257: end if;
6258: --
6259: if hr_multi_message.no_exclusive_error
6260: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6261: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6262: ) then
6263: --
6264: -- Check mandatory arguments
6257: end if;
6258: --
6259: if hr_multi_message.no_exclusive_error
6260: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6261: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6262: ) then
6263: --
6264: -- Check mandatory arguments
6265: --
6366: if l_assignment_type = 'O' and p_inv_job_grade_warning then
6367:
6368: fnd_message.set_name('PER', 'HR_JOB_GRADE_CHK');
6369: hr_multi_message.add
6370: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6371: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6372: );
6373: end if;
6374: -- End of fix for the issue in the bug#8857145
6367:
6368: fnd_message.set_name('PER', 'HR_JOB_GRADE_CHK');
6369: hr_multi_message.add
6370: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6371: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6372: );
6373: end if;
6374: -- End of fix for the issue in the bug#8857145
6375:
6381: -- Start changes for bug 11063182
6382: -- Overloaded procedure with parameter p_business_group_id
6383: --
6384: procedure chk_location_id
6385: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6386: ,p_location_id in per_all_assignments_f.location_id%TYPE
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6382: -- Overloaded procedure with parameter p_business_group_id
6383: --
6384: procedure chk_location_id
6385: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6386: ,p_location_id in per_all_assignments_f.location_id%TYPE
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6383: --
6384: procedure chk_location_id
6385: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6386: ,p_location_id in per_all_assignments_f.location_id%TYPE
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6391: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6384: procedure chk_location_id
6385: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6386: ,p_location_id in per_all_assignments_f.location_id%TYPE
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6391: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6392: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6386: ,p_location_id in per_all_assignments_f.location_id%TYPE
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6391: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6392: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6393: ) is
6394: begin
6387: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6391: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6392: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6393: ) is
6394: begin
6395:
6388: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6389: ,p_effective_date in date
6390: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6391: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6392: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6393: ) is
6394: begin
6395:
6396: per_asg_bus1.chk_location_id
6413: -- |--------------------------< chk_location_id >----------------------------|
6414: -- ---------------------------------------------------------------------------
6415: --
6416: procedure chk_location_id
6417: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6418: ,p_location_id in per_all_assignments_f.location_id%TYPE
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6414: -- ---------------------------------------------------------------------------
6415: --
6416: procedure chk_location_id
6417: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6418: ,p_location_id in per_all_assignments_f.location_id%TYPE
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6415: --
6416: procedure chk_location_id
6417: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6418: ,p_location_id in per_all_assignments_f.location_id%TYPE
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6416: procedure chk_location_id
6417: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6418: ,p_location_id in per_all_assignments_f.location_id%TYPE
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6424: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6418: ,p_location_id in per_all_assignments_f.location_id%TYPE
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6424: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6425: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE -- added for bug 11063182
6426: )
6419: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6424: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6425: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE -- added for bug 11063182
6426: )
6427: is
6420: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6424: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6425: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE -- added for bug 11063182
6426: )
6427: is
6428: --
6421: ,p_effective_date in date
6422: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6423: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6424: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6425: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE -- added for bug 11063182
6426: )
6427: is
6428: --
6429: l_exists varchar2(1);
6429: l_exists varchar2(1);
6430: l_api_updating boolean;
6431: l_proc varchar2(72) := g_package||'chk_location_id';
6432: l_inactive_date date;
6433: l_vac_location_id per_all_assignments_f.location_id%TYPE;
6434: -- Bug 4116879 Starts
6435: -- Desc : The fix made for the bug 3895708, not considerd the INSERT scenario.
6436: -- While creating the Assignment per_all_assignments_f is not populated
6437: -- so the sub query fails. Re-write the cursor to implement INSERT too.
6432: l_inactive_date date;
6433: l_vac_location_id per_all_assignments_f.location_id%TYPE;
6434: -- Bug 4116879 Starts
6435: -- Desc : The fix made for the bug 3895708, not considerd the INSERT scenario.
6436: -- While creating the Assignment per_all_assignments_f is not populated
6437: -- so the sub query fails. Re-write the cursor to implement INSERT too.
6438: -- Also, fix for the bug 4105698 is modified to make the cursor
6439: -- compatible with 8i.
6440: cursor csr_valid_location_upd is
6442: from hr_locations_all
6443: where location_id = p_location_id
6444: and (business_group_id= (
6445: select distinct business_group_id
6446: from per_all_assignments_f
6447: where assignment_id= p_assignment_id)
6448: or business_group_id is null);
6449:
6450: -- bug 4318990 added an nvl in the where condition for relaxing the validation
6519: if csr_valid_location_upd%notfound then
6520: close csr_valid_location_upd;
6521: hr_utility.set_message(801, 'HR_7382_ASG_NON_EXIST_LOCATION');
6522: hr_multi_message.add
6523: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID');
6524: --
6525: else
6526: close csr_valid_location_upd;
6527: end if;
6531: if csr_valid_location_ins%notfound then
6532: close csr_valid_location_ins;
6533: hr_utility.set_message(801, 'HR_7382_ASG_NON_EXIST_LOCATION');
6534: hr_multi_message.add
6535: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID');
6536: --
6537: else
6538: close csr_valid_location_ins;
6539: end if;
6551: then
6552: --
6553: hr_utility.set_message(801, 'HR_51215_ASG_INACT_LOCATION');
6554: hr_multi_message.add
6555: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID'
6556: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6557: );
6558: --
6559: end if;
6552: --
6553: hr_utility.set_message(801, 'HR_51215_ASG_INACT_LOCATION');
6554: hr_multi_message.add
6555: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID'
6556: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6557: );
6558: --
6559: end if;
6560: if g_debug then
6574: -- |-------------------------< chk_manager_flag >----------------------------|
6575: -- ---------------------------------------------------------------------------
6576: --
6577: procedure chk_manager_flag
6578: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6579: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6580: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6581: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6582: ,p_effective_date in date
6575: -- ---------------------------------------------------------------------------
6576: --
6577: procedure chk_manager_flag
6578: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6579: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6580: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6581: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6582: ,p_effective_date in date
6583: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6576: --
6577: procedure chk_manager_flag
6578: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6579: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6580: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6581: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6582: ,p_effective_date in date
6583: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6584: ,p_other_manager_warning in out nocopy boolean
6577: procedure chk_manager_flag
6578: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6579: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6580: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6581: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6582: ,p_effective_date in date
6583: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6584: ,p_other_manager_warning in out nocopy boolean
6585: ,p_no_managers_warning in out nocopy boolean
6579: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6580: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6581: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6582: ,p_effective_date in date
6583: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6584: ,p_other_manager_warning in out nocopy boolean
6585: ,p_no_managers_warning in out nocopy boolean
6586: )
6587: is
6741: -- |---------------------< chk_frequency_normal_hours >----------------------|
6742: -- ---------------------------------------------------------------------------
6743: --
6744: procedure chk_frequency_normal_hours
6745: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6746: ,p_frequency in per_all_assignments_f.frequency%TYPE
6747: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6748: ,p_effective_date in date
6749: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6742: -- ---------------------------------------------------------------------------
6743: --
6744: procedure chk_frequency_normal_hours
6745: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6746: ,p_frequency in per_all_assignments_f.frequency%TYPE
6747: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6748: ,p_effective_date in date
6749: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6750: )
6743: --
6744: procedure chk_frequency_normal_hours
6745: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6746: ,p_frequency in per_all_assignments_f.frequency%TYPE
6747: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6748: ,p_effective_date in date
6749: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6750: )
6751: is
6745: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6746: ,p_frequency in per_all_assignments_f.frequency%TYPE
6747: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6748: ,p_effective_date in date
6749: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6750: )
6751: is
6752: --
6753: l_api_updating boolean;
6758: hr_utility.set_location('Entering:'|| l_proc, 10);
6759: end if;
6760: --
6761: if hr_multi_message.no_all_inclusive_error
6762: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6763: ) then
6764: --
6765: -- Check mandatory parameters have been set
6766: --
6837: end if;
6838: exception
6839: when app_exception.application_exception then
6840: if hr_multi_message.exception_add
6841: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6842: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS'
6843: ) then
6844: if g_debug then
6845: hr_utility.set_location(' Leaving:'|| l_proc, 80);
6838: exception
6839: when app_exception.application_exception then
6840: if hr_multi_message.exception_add
6841: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6842: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS'
6843: ) then
6844: if g_debug then
6845: hr_utility.set_location(' Leaving:'|| l_proc, 80);
6846: end if;
6855: -- |-----------------------< chk_organization_id >---------------------------|
6856: -- ---------------------------------------------------------------------------
6857: --
6858: procedure chk_organization_id
6859: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6856: -- ---------------------------------------------------------------------------
6857: --
6858: procedure chk_organization_id
6859: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6857: --
6858: procedure chk_organization_id
6859: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6858: procedure chk_organization_id
6859: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6859: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6860: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6868: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6861: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6868: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6869: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6862: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6863: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6868: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6869: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6870: ,p_org_now_no_manager_warning in out nocopy boolean
6864: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6868: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6869: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6870: ,p_org_now_no_manager_warning in out nocopy boolean
6871: ,p_other_manager_warning in out nocopy boolean
6872: )
6865: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6866: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6867: ,p_effective_date in date
6868: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6869: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6870: ,p_org_now_no_manager_warning in out nocopy boolean
6871: ,p_other_manager_warning in out nocopy boolean
6872: )
6873: is
6874: --
6875: l_exists varchar2(1);
6876: l_api_updating boolean;
6877: l_proc varchar2(72) := g_package||'chk_organization_id';
6878: l_vac_organization_id per_all_assignments_f.organization_id%TYPE;
6879: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6880: --
6881: -- bugfix 2452613: use full table not secure view for validation
6882: --
6875: l_exists varchar2(1);
6876: l_api_updating boolean;
6877: l_proc varchar2(72) := g_package||'chk_organization_id';
6878: l_vac_organization_id per_all_assignments_f.organization_id%TYPE;
6879: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6880: --
6881: -- bugfix 2452613: use full table not secure view for validation
6882: --
6883: cursor csr_valid_int_hr_org is
6964: if csr_valid_int_hr_org%notfound then
6965: close csr_valid_int_hr_org;
6966: hr_utility.set_message(801, 'HR_34983_ASG_INVALID_ORG');
6967: hr_multi_message.add
6968: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
6969: );
6970: --
6971: else
6972: close csr_valid_int_hr_org;
6981: If p_business_group_id <> l_business_group_id then
6982: --
6983: hr_utility.set_message(801, 'HR_7376_ASG_INVALID_BG_ORG');
6984: hr_multi_message.add
6985: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
6986: );
6987: --
6988: end if;
6989: if g_debug then
7008: if csr_valid_per_org%notfound then
7009: close csr_valid_per_org;
7010: hr_utility.set_message(801, 'HR_51277_ASG_INV_HR_ORG');
7011: hr_multi_message.add
7012: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
7013: );
7014: --
7015: else
7016: close csr_valid_per_org;
7027: --
7028: If p_assignment_type = 'E' then
7029: --
7030: if hr_multi_message.no_exclusive_error
7031: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.MANAGER_FLAG'
7032: ) then
7033: --
7034: -- Check if manager flag is 'Y'
7035: --
7107: -- Access Status:
7108: -- Internal Table Handler Use Only
7109: --
7110: procedure chk_bargaining_unit_code
7111: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7112: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
7113: ,p_effective_date in date
7114: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7115: ,p_validation_start_date in date
7108: -- Internal Table Handler Use Only
7109: --
7110: procedure chk_bargaining_unit_code
7111: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7112: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
7113: ,p_effective_date in date
7114: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7115: ,p_validation_start_date in date
7116: ,p_validation_end_date in date
7110: procedure chk_bargaining_unit_code
7111: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7112: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
7113: ,p_effective_date in date
7114: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7115: ,p_validation_start_date in date
7116: ,p_validation_end_date in date
7117: )
7118: is
7155: end if;
7156: exception
7157: when app_exception.application_exception then
7158: if hr_multi_message.exception_add
7159: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.BARGAINING_UNIT_CODE'
7160: ) then
7161: if g_debug then
7162: hr_utility.set_location(' Leaving:'|| l_proc, 40);
7163: end if;
7203: -- Access Status:
7204: -- Internal Table Handler Use Only
7205: --
7206: procedure chk_hourly_salaried_code
7207: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7208: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7209: ,p_effective_date in date
7210: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7211: ,p_validation_start_date in date
7204: -- Internal Table Handler Use Only
7205: --
7206: procedure chk_hourly_salaried_code
7207: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7208: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7209: ,p_effective_date in date
7210: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7211: ,p_validation_start_date in date
7212: ,p_validation_end_date in date
7206: procedure chk_hourly_salaried_code
7207: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7208: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7209: ,p_effective_date in date
7210: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7211: ,p_validation_start_date in date
7212: ,p_validation_end_date in date
7213: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7214: ,p_hourly_salaried_warning in out nocopy boolean
7209: ,p_effective_date in date
7210: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7211: ,p_validation_start_date in date
7212: ,p_validation_end_date in date
7213: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7214: ,p_hourly_salaried_warning in out nocopy boolean
7215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE)
7216: is
7217: --
7211: ,p_validation_start_date in date
7212: ,p_validation_end_date in date
7213: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7214: ,p_hourly_salaried_warning in out nocopy boolean
7215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE)
7216: is
7217: --
7218: l_proc varchar2(72) := g_package||'chk_hourly_salaried_code';
7219: l_hourly_salaried_code varchar2(72);
7262: )
7263: then
7264: hr_utility.set_message(800,'PER_52407_HOUR_SAL_CODE');
7265: hr_multi_message.add
7266: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.HOURLY_SALARIED_CODE'
7267: );
7268: else
7269: --
7270: if hr_multi_message.no_exclusive_error
7267: );
7268: else
7269: --
7270: if hr_multi_message.no_exclusive_error
7271: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
7272: ) then
7273: open csr_hourly_salaried;
7274: fetch csr_hourly_salaried into l_pay_basis;
7275: if csr_hourly_salaried%FOUND then
7313: cursor csr_leg_code is
7314: select pbg.legislation_code
7315: from per_business_groups_perf pbg
7316: where pbg.business_group_id = (select distinct asg.business_group_id from
7317: per_all_assignments_f asg
7318: where asg.assignment_id = p_assignment_id);
7319:
7320: --
7321: -- Declare local variables
7437: select
7438: paaf.assignment_number,
7439: paaf.effective_start_date,
7440: paaf.effective_end_date
7441: from per_all_assignments_f paaf, per_assignment_status_types past
7442: where paaf.assignment_status_type_id = past.assignment_status_type_id
7443: and paaf.assignment_type in ('E','C') -- Added For Bug#13860076
7444: --and paaf.assignment_type = 'E' -- Commented For Bug#13860076
7445: and past.per_system_status <> 'TERM_ASSIGN'
7451: -- Changes start for the bug 13571263
7452:
7453: CURSOR csr_is_apl_asg IS
7454: SELECT ASSIGNMENT_TYPE
7455: FROM per_all_assignments_f
7456: WHERE assignment_id = p_assignment_id
7457: AND p_assignment_start_date between effective_start_date and effective_end_date;
7458:
7459: l_asg_type per_all_assignments_f.ASSIGNMENT_TYPE%type;
7455: FROM per_all_assignments_f
7456: WHERE assignment_id = p_assignment_id
7457: AND p_assignment_start_date between effective_start_date and effective_end_date;
7458:
7459: l_asg_type per_all_assignments_f.ASSIGNMENT_TYPE%type;
7460:
7461: -- Changes end for the bug 13571263
7462: --
7463:
7464: l_ol_effective_start_date date;
7465: l_ol_effective_end_date date;
7466:
7467: l_position_type hr_all_positions_f.position_type%type;
7468: l_asg_number per_all_assignments_f.assignment_number%type;
7469: l_asg_effective_start_date date;
7470: l_asg_effective_end_date date;
7471: -- End changes for bug 11828317
7472:
7575: --
7576: -- Start changes for bug 10220040
7577:
7578: procedure chk_frozen_single_pos
7579: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7580: ,p_position_id in per_all_assignments_f.position_id%TYPE
7581: ,p_effective_date in date
7582: ,p_assignment_type in varchar2 default 'E'
7583: ) is
7576: -- Start changes for bug 10220040
7577:
7578: procedure chk_frozen_single_pos
7579: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7580: ,p_position_id in per_all_assignments_f.position_id%TYPE
7581: ,p_effective_date in date
7582: ,p_assignment_type in varchar2 default 'E'
7583: ) is
7584: begin
7626: -- Access Status:
7627: -- Internal Table Handler Use Only
7628: --
7629: procedure chk_frozen_single_pos
7630: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7631: ,p_position_id in per_all_assignments_f.position_id%TYPE
7632: ,p_effective_date in date
7633: ,p_effective_end_date in date -- parameter added for bug 10220040
7634: ,p_assignment_type in varchar2 default 'E'
7627: -- Internal Table Handler Use Only
7628: --
7629: procedure chk_frozen_single_pos
7630: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7631: ,p_position_id in per_all_assignments_f.position_id%TYPE
7632: ,p_effective_date in date
7633: ,p_effective_end_date in date -- parameter added for bug 10220040
7634: ,p_assignment_type in varchar2 default 'E'
7635: )
7671: if hr_psf_shd.get_availability_status(l_availability_status_id,l_business_group_id) ='FROZEN'
7672: then
7673: hr_utility.set_message(800, 'PER_NO_ASG_FOR_FROZEN_POS');
7674: hr_multi_message.add
7675: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7676: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7677: );
7678: end if;
7679: if (l_position_type = 'SINGLE') then
7672: then
7673: hr_utility.set_message(800, 'PER_NO_ASG_FOR_FROZEN_POS');
7674: hr_multi_message.add
7675: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7676: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7677: );
7678: end if;
7679: if (l_position_type = 'SINGLE') then
7680: if p_assignment_type in ('E','C') then -- added for bug 12830757
7688: /*---- End change for the bug 5854568 ----(modified for the bug 6331872)*/
7689: then
7690: hr_utility.set_message(800, 'PER_ASG_EXISTS_FOR_SINGLE_POS');
7691: hr_multi_message.add
7692: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7693: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7694: );
7695: end if;
7696: end if; -- 6397484(forward port of 6356978)
7689: then
7690: hr_utility.set_message(800, 'PER_ASG_EXISTS_FOR_SINGLE_POS');
7691: hr_multi_message.add
7692: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7693: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7694: );
7695: end if;
7696: end if; -- 6397484(forward port of 6356978)
7697: end if;
7707:
7708: -- Start changes for bug 10220040
7709:
7710: procedure chk_single_position
7711: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7712: ,p_position_id in per_all_assignments_f.position_id%TYPE
7713: ,p_effective_date in date
7714: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7715: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7708: -- Start changes for bug 10220040
7709:
7710: procedure chk_single_position
7711: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7712: ,p_position_id in per_all_assignments_f.position_id%TYPE
7713: ,p_effective_date in date
7714: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7715: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7716: ) is
7710: procedure chk_single_position
7711: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7712: ,p_position_id in per_all_assignments_f.position_id%TYPE
7713: ,p_effective_date in date
7714: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7715: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7716: ) is
7717: begin
7718:
7711: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7712: ,p_position_id in per_all_assignments_f.position_id%TYPE
7713: ,p_effective_date in date
7714: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7715: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7716: ) is
7717: begin
7718:
7719: per_asg_bus1.chk_single_position
7730: -- End changes for bug 10220040
7731:
7732: --
7733: procedure chk_single_position
7734: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7735: ,p_position_id in per_all_assignments_f.position_id%TYPE
7736: ,p_effective_date in date
7737: ,p_effective_end_date in date -- Added for bug 10220040
7738: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7731:
7732: --
7733: procedure chk_single_position
7734: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7735: ,p_position_id in per_all_assignments_f.position_id%TYPE
7736: ,p_effective_date in date
7737: ,p_effective_end_date in date -- Added for bug 10220040
7738: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7739: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7734: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7735: ,p_position_id in per_all_assignments_f.position_id%TYPE
7736: ,p_effective_date in date
7737: ,p_effective_end_date in date -- Added for bug 10220040
7738: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7739: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7740: )
7741: is
7742: --
7735: ,p_position_id in per_all_assignments_f.position_id%TYPE
7736: ,p_effective_date in date
7737: ,p_effective_end_date in date -- Added for bug 10220040
7738: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7739: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7740: )
7741: is
7742: --
7743: l_proc varchar2(72) := g_package||'chk_single_position';
7748: hr_utility.set_location('Entering:'|| l_proc, 10);
7749: end if;
7750: --
7751: if hr_multi_message.no_exclusive_error
7752: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7753: ) then
7754: --
7755: -- Check mandatory parameters have been set
7756: --
7835: p_except_assignment_id number) return boolean is
7836: l_dummy varchar2(1);
7837: cursor c1 is
7838: select 'x'
7839: from per_all_assignments_f asg, per_assignment_status_types ast
7840: where position_id = p_position_id
7841: and assignment_id <> nvl(p_except_assignment_id, -1)
7842: and assignment_type in ('E','C') -- For Bug#13860076, commented below statement and added this condition
7843: --and ( assignment_type = 'E' /*or assignment_type = 'A'*/) -- change for the bug 5854568 (6332332)