[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
998: l_other_manager_warning boolean;
999: l_hourly_salaried_warning boolean;
1000: L_inv_pos_grade_warning boolean := false;
1001: L_inv_job_grade_warning boolean := false;
1002: l_source_type per_all_assignments_f.source_type%TYPE default NULL; --- Fix For Bug #7481310
1003: --
1004: Begin
1005: if g_debug then
1006: l_proc := g_package||'insert_validate';
1105: --- Fix For Bug # 7481310 Starts ---
1106: if p_rec.assignment_type = 'O' then
1107: hr_utility.set_location(l_proc || 'This is an Offer Assignment', 60);
1108:
1109: select source_type into l_source_type from per_all_assignments_f
1110: where person_id = p_rec.person_id and BUSINESS_GROUP_ID = p_rec.business_group_id
1111: and ASSIGNMENT_TYPE = 'A' and VACANCY_ID = p_rec.vacancy_id and
1112: p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1113:
3249: -- |-----------------------< chk_application_id >----------------------------|
3250: -- ---------------------------------------------------------------------------
3251: --
3252: procedure chk_application_id
3253: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3254: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3250: -- ---------------------------------------------------------------------------
3251: --
3252: procedure chk_application_id
3253: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3254: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3258: ,p_effective_date in date
3251: --
3252: procedure chk_application_id
3253: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3254: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3258: ,p_effective_date in date
3259: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3252: procedure chk_application_id
3253: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3254: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3258: ,p_effective_date in date
3259: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3260: ,p_validation_start_date in date
3253: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3254: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3258: ,p_effective_date in date
3259: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3260: ,p_validation_start_date in date
3261: )
3255: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3256: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3257: ,p_application_id in per_all_assignments_f.application_id%TYPE
3258: ,p_effective_date in date
3259: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3260: ,p_validation_start_date in date
3261: )
3262: is
3263: --
3263: --
3264: l_proc varchar2(72) := g_package||'chk_application_id';
3265: l_exists varchar2(1);
3266: l_api_updating boolean;
3267: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3268: --
3269: cursor csr_valid_apl_1 is
3270: select business_group_id
3271: from per_applications
3343: --
3344: hr_utility.set_message(801, 'HR_51212_ASG_INV_APL_ASG_APL');
3345: hr_utility.raise_error;
3346: /* hr_multi_message.add
3347: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3348: );*/
3349: --
3350: end if;
3351: if g_debug then
3358: --
3359: hr_utility.set_message(801, 'HR_51213_ASG_INV_UPD_APL');
3360: hr_utility.raise_error;
3361: /*hr_multi_message.add
3362: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3363: ); */ --
3364: --
3365: end if;
3366: if g_debug then
3386: close csr_valid_apl_1;
3387: hr_utility.set_message(801, 'HR_51210_ASG_INV_APL');
3388: hr_utility.raise_error;
3389: /* hr_multi_message.add
3390: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3391: ,p_associated_column2 =>
3392: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3393: ,p_associated_column3 =>
3394: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3388: hr_utility.raise_error;
3389: /* hr_multi_message.add
3390: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3391: ,p_associated_column2 =>
3392: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3393: ,p_associated_column3 =>
3394: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3395: ); */
3396: --
3390: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3391: ,p_associated_column2 =>
3392: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3393: ,p_associated_column3 =>
3394: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3395: ); */
3396: --
3397: else
3398: close csr_valid_apl_1;
3409: if csr_valid_apl_2%notfound then
3410: close csr_valid_apl_2;
3411: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3412: hr_multi_message.add
3413: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3414: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3415: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3416: );
3417: --
3410: close csr_valid_apl_2;
3411: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3412: hr_multi_message.add
3413: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3414: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3415: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3416: );
3417: --
3418: else
3411: hr_utility.set_message(801, 'HR_51375_ASG_INV_APL_NOT_1_ASG');
3412: hr_multi_message.add
3413: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3414: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3415: ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE'
3416: );
3417: --
3418: else
3419: close csr_valid_apl_2;
3427: If p_business_group_id <> l_business_group_id then
3428: --
3429: hr_utility.set_message(801, 'HR_51214_ASG_INV_APL_BUS_GRP');
3430: hr_multi_message.add
3431: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3432: );
3433: --
3434: end if;
3435: end if;
3446: If p_application_id is not null then
3447: --
3448: hr_utility.set_message(801, 'HR_51211_ASG_INV_E_ASG_APL_ID');
3449: hr_multi_message.add
3450: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICATION_ID'
3451: );
3452: --
3453: end if;
3454: if g_debug then
3468: -- |---------------------< gen_chk_assignment_number >-----------------------|
3469: -- ---------------------------------------------------------------------------
3470: --
3471: procedure gen_chk_assignment_number
3472: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3469: -- ---------------------------------------------------------------------------
3470: --
3471: procedure gen_chk_assignment_number
3472: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3470: --
3471: procedure gen_chk_assignment_number
3472: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3478: ,p_effective_date in date
3471: procedure gen_chk_assignment_number
3472: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3478: ,p_effective_date in date
3479: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3472: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3478: ,p_effective_date in date
3479: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3480: )
3473: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3474: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3478: ,p_effective_date in date
3479: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3480: )
3481: is
3475: ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
3476: ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
3477: ,p_person_id in per_all_assignments_f.person_id%TYPE
3478: ,p_effective_date in date
3479: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3480: )
3481: is
3482: --
3483: l_proc varchar2(72) := g_package||'gen_chk_assignment_number';
3564: -- Raise an error: Assignment Number must be null for
3565: -- applicant, offer and benefit assignments
3566: hr_utility.set_message(801, 'HR_51017_ASG_NUM_NULL_FOR_APL');
3567: hr_multi_message.add
3568: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER');
3569: --
3570: end if;
3571: --
3572: if g_debug then
3578: --
3579: elsif p_assignment_type In ('E','C') then
3580: --
3581: if hr_multi_message.no_exclusive_error
3582: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
3583: --
3584: -- For worker assignments validate or generate the assignment number
3585: --
3586: open csr_get_work_no;
3611: --
3612: close csr_get_work_no;
3613: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3614: hr_multi_message.add
3615: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3616: ,p_associated_column2 =>
3617: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3618: --
3619: end if;
3613: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3614: hr_multi_message.add
3615: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3616: ,p_associated_column2 =>
3617: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3618: --
3619: end if;
3620: --
3621: end if; -- no exclusive error
3633: nvl(per_asg_shd.g_old_rec.assignment_type, hr_api.g_varchar2)
3634: = 'A' and p_assignment_type = 'E') then
3635: --
3636: if hr_multi_message.no_exclusive_error
3637: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
3638: --
3639: -- For employee assignments validate or generate the assignment number
3640: --
3641: open csr_get_work_no;
3667: close csr_get_work_no;
3668: --
3669: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3670: hr_multi_message.add
3671: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3672: ,p_associated_column2 =>
3673: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3674: --
3675: end if;
3669: hr_utility.set_message(801, 'HR_7390_ASG_NO_EMP_NO');
3670: hr_multi_message.add
3671: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
3672: ,p_associated_column2 =>
3673: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE');
3674: --
3675: end if;
3676: --
3677: end if; -- no exclusive error
3692: -- |-----------------------< chk_assignment_category >------------------------|
3693: -- ---------------------------------------------------------------------------
3694: --
3695: PROCEDURE chk_assignment_category
3696: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3697: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3698: ,p_effective_date in date
3699: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3700: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3693: -- ---------------------------------------------------------------------------
3694: --
3695: PROCEDURE chk_assignment_category
3696: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3697: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3698: ,p_effective_date in date
3699: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3700: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3701: ,p_validation_start_date in date
3696: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3697: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3698: ,p_effective_date in date
3699: ,p_assignment_category in per_assignments_f.assignment_category%TYPE
3700: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3701: ,p_validation_start_date in date
3702: ,p_validation_end_date in date) IS
3703: --
3704: l_proc varchar2(72);
3772: --
3773: WHEN app_exception.application_exception THEN
3774: --
3775: IF hr_multi_message.exception_add
3776: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_CATEGORY') THEN
3777: --
3778: if g_debug then
3779: hr_utility.set_location(' Leaving:'|| l_proc, 998);
3780: end if;
3829: -- Declare cursors and local variables
3830: --
3831: -- Out variables
3832: --
3833: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3834: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
3835: l_proc varchar2(72);
3836: --
3837: cursor csr_get_ast_details is
3846: hr_utility.set_location('Entering:'|| l_proc, 1);
3847: end if;
3848: --
3849: if hr_multi_message.no_exclusive_error
3850: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
3851: ) then
3852: --
3853: -- If p_assignment_status_type_id is g_number then derive it's default value.
3854: --
3948: -- ---------------------------------------------------------------------------
3949: --
3950: procedure chk_assignment_status_type_id
3951: (p_rec in per_asg_shd.g_rec_type
3952: ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
3953: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
3954: )
3955: is
3956: --
3949: --
3950: procedure chk_assignment_status_type_id
3951: (p_rec in per_asg_shd.g_rec_type
3952: ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
3953: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
3954: )
3955: is
3956: --
3957: l_api_updating boolean;
3962: l_ast_legislation_code per_business_groups.legislation_code%TYPE;
3963: l_bus_legislation_code per_business_groups.legislation_code%TYPE;
3964: l_active_flag per_assignment_status_types.active_flag%TYPE;
3965: l_ast_active_flag per_assignment_status_types.active_flag%TYPE;
3966: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3967: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
3968: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
3969: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3970: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
3965: l_ast_active_flag per_assignment_status_types.active_flag%TYPE;
3966: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3967: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
3968: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
3969: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3970: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
3971: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
3972: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
3973: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
3967: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
3968: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
3969: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3970: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
3971: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
3972: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
3973: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
3974: --
3975: -- Cursor to check that a assignment status type exists in
3968: l_ast_per_system_status per_assignment_status_types.per_system_status%TYPE;
3969: l_ast_business_group_id per_all_assignments_f.business_group_id%TYPE;
3970: l_old_per_system_status per_assignment_status_types.per_system_status%TYPE;
3971: l_initial_ins_date per_all_assignments_f.effective_start_date%TYPE;
3972: l_apl_asg_min_esd per_all_assignments_f.effective_start_date%TYPE;
3973: l_fir_dt_ast_pss per_assignment_status_types.per_system_status%TYPE;
3974: --
3975: -- Cursor to check that a assignment status type exists in
3976: -- PER_ASSIGNMENT_STATUS_TYPES.
4011: -- status type PER_SYSTEM_STATUS of 'ACTIVE_ASSIGN'
4012: --
4013: cursor csr_get_initial_ins_date is
4014: select nvl(min(asg.effective_start_date), hr_api.g_eot)
4015: from per_all_assignments_f asg
4016: ,per_assignment_status_types ast
4017: where asg.assignment_id = p_rec.assignment_id
4018: and ast.assignment_status_type_id = asg.assignment_status_type_id
4019: and ast.per_system_status = 'ACTIVE_ASSIGN';
4022: -- assignment
4023: --
4024: cursor csr_get_apl_asg_min_esd is
4025: select min(effective_start_date)
4026: from per_all_assignments_f
4027: where assignment_id = p_rec.assignment_id
4028: and assignment_type = 'A';
4029: --
4030: -- Cursor to retrieve the PER SYSTEM STATUS for the first datetracked
4030: -- Cursor to retrieve the PER SYSTEM STATUS for the first datetracked
4031: -- instance of an applicant assignment.
4032: --
4033: cursor csr_get_fir_dt_inst_ast_pss
4034: (c_effective_start_date per_all_assignments_f.effective_start_date%TYPE)
4035: is
4036: select ast.per_system_status
4037: from per_all_assignments_f asg
4038: ,per_assignment_status_types ast
4033: cursor csr_get_fir_dt_inst_ast_pss
4034: (c_effective_start_date per_all_assignments_f.effective_start_date%TYPE)
4035: is
4036: select ast.per_system_status
4037: from per_all_assignments_f asg
4038: ,per_assignment_status_types ast
4039: where asg.assignment_id = p_rec.assignment_id
4040: and asg.effective_start_date = c_effective_start_date
4041: and ast.assignment_status_type_id = asg.assignment_status_type_id;
4214: then
4215: --
4216: hr_utility.set_message(801, 'HR_7946_ASG_INV_TERM_ASS_UPD');
4217: hr_multi_message.add
4218: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4219: );
4220: --
4221: -- fix for bug 4557189. Commented out code.
4222: elsif (
4256: then
4257: --
4258: hr_utility.set_message(801, 'HR_7946_ASG_INV_TERM_ASS_UPD');
4259: hr_multi_message.add
4260: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4261: );
4262: --
4263: end if;
4264: if g_debug then
4291: If l_fir_dt_ast_pss = 'TERM_APL' then
4292: --
4293: hr_utility.set_message(801, 'HR_51275_ASG_INV_F_DT_AST_PSS');
4294: hr_multi_message.add
4295: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4296: );
4297: --
4298: end if;
4299: if g_debug then
4331: if csr_valid_ast%notfound then
4332: close csr_valid_ast;
4333: hr_utility.set_message(801, 'HR_7940_ASG_INV_ASG_STAT_TYPE');
4334: hr_multi_message.add
4335: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4336: );
4337: else
4338: close csr_valid_ast;
4339: end if;
4348: and l_business_group_id <> p_rec.business_group_id then
4349: --
4350: hr_utility.set_message(801, 'HR_51207_ASG_INV_AST_BUS_GRP');
4351: hr_multi_message.add
4352: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4353: );
4354: --
4355: end if;
4356: if g_debug then
4403: If l_active_flag <> 'Y' then
4404: --
4405: hr_utility.set_message(801, 'HR_51206_ASG_INV_AST_ACT_FLG');
4406: hr_multi_message.add
4407: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4408: );
4409: --
4410: end if;
4411: if g_debug then
4430: --
4431: hr_utility.set_message(801, 'HR_7917_ASG_INV_STAT_TYPE');
4432: hr_multi_message.add
4433: (p_associated_column1 =>
4434: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4435: );
4436: --
4437: end if;
4438: if g_debug then
4449: --
4450: hr_utility.set_message(801, 'HR_7942_ASG_INV_STAT_NOT_TERM');
4451: hr_multi_message.add
4452: (p_associated_column1 =>
4453: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4454: );
4455: --
4456: end if;
4457: if g_debug then
4481: --
4482: hr_utility.set_message(800,'HR_7915_ASG_INV_STAT_UPD_DATE');
4483: hr_multi_message.add
4484: (p_associated_column1 =>
4485: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4486: ,p_associated_column2 =>
4487: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4488: );
4489: --
4483: hr_multi_message.add
4484: (p_associated_column1 =>
4485: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4486: ,p_associated_column2 =>
4487: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4488: );
4489: --
4490: end if;
4491: if g_debug then
4506: --
4507: hr_utility.set_message(801, 'HR_7941_ASG_INV_STAT_NOT_ACT');
4508: hr_multi_message.add
4509: (p_associated_column1 =>
4510: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4511: );
4512: --
4513: end if;
4514: --
4583: then
4584: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
4585: hr_multi_message.add
4586: (p_associated_column1 =>
4587: 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID'
4588: );
4589: --
4590: end if;
4591: if g_debug then
4607: -- |-----------------------< chk_assignment_type >---------------------------|
4608: -- ---------------------------------------------------------------------------
4609: --
4610: procedure chk_assignment_type
4611: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4612: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4613: ,p_person_id in per_all_assignments_f.person_id%TYPE
4614: ,p_effective_date in date
4615: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4608: -- ---------------------------------------------------------------------------
4609: --
4610: procedure chk_assignment_type
4611: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4612: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4613: ,p_person_id in per_all_assignments_f.person_id%TYPE
4614: ,p_effective_date in date
4615: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4616: ,p_validation_start_date in date
4609: --
4610: procedure chk_assignment_type
4611: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4612: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4613: ,p_person_id in per_all_assignments_f.person_id%TYPE
4614: ,p_effective_date in date
4615: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4616: ,p_validation_start_date in date
4617: )
4611: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4612: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4613: ,p_person_id in per_all_assignments_f.person_id%TYPE
4614: ,p_effective_date in date
4615: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4616: ,p_validation_start_date in date
4617: )
4618: is
4619: --
4770: exception
4771: when app_exception.application_exception then
4772: if hr_multi_message.exception_add
4773: (p_associated_column1 =>
4774: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4775: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4776: ) then
4777: if g_debug then
4778: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4771: when app_exception.application_exception then
4772: if hr_multi_message.exception_add
4773: (p_associated_column1 =>
4774: 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4775: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4776: ) then
4777: if g_debug then
4778: hr_utility.set_location(' Leaving:'|| l_proc, 110);
4779: end if;
4789: -- |-------------------------< chk_change_reason >---------------------------|
4790: -- ---------------------------------------------------------------------------
4791: --
4792: procedure chk_change_reason
4793: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4794: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4795: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4796: ,p_effective_date in date
4797: ,p_validation_start_date in date
4790: -- ---------------------------------------------------------------------------
4791: --
4792: procedure chk_change_reason
4793: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4794: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4795: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4796: ,p_effective_date in date
4797: ,p_validation_start_date in date
4798: ,p_validation_end_date in date
4791: --
4792: procedure chk_change_reason
4793: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4794: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4795: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4796: ,p_effective_date in date
4797: ,p_validation_start_date in date
4798: ,p_validation_end_date in date
4799: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4795: ,p_change_reason in per_all_assignments_f.change_reason%TYPE
4796: ,p_effective_date in date
4797: ,p_validation_start_date in date
4798: ,p_validation_end_date in date
4799: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4800: )
4801: is
4802: --
4803: l_api_updating boolean;
4943: end if;
4944: exception
4945: when app_exception.application_exception then
4946: if hr_multi_message.exception_add
4947: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.CHANGE_REASON'
4948: ) then
4949: if g_debug then
4950: hr_utility.set_location(' Leaving:'|| l_proc, 90);
4951: end if;
4986: -- Access Status:
4987: -- Internal Table Handler Use Only.
4988: --
4989: procedure chk_contig_ass
4990: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
4991: ,p_person_id in per_all_assignments_f.person_id%TYPE
4992: ,p_effective_date in date
4993: ,p_datetrack_mode in varchar2
4994: )
4987: -- Internal Table Handler Use Only.
4988: --
4989: procedure chk_contig_ass
4990: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
4991: ,p_person_id in per_all_assignments_f.person_id%TYPE
4992: ,p_effective_date in date
4993: ,p_datetrack_mode in varchar2
4994: )
4995: is
5001:
5002: select null
5003: from sys.dual
5004: where exists(select null
5005: from per_all_assignments_f pas
5006: , per_periods_of_service ppos
5007: where pas.effective_start_date <= p_effective_date
5008: and ppos.period_of_service_id = pas.period_of_service_id
5009: and pas.person_id = p_person_id
5008: and ppos.period_of_service_id = pas.period_of_service_id
5009: and pas.person_id = p_person_id
5010: and pas.primary_flag = 'N'
5011: and (exists(select null
5012: from per_all_assignments_f pas2
5013: , per_periods_of_service ppos2
5014: where pas2.effective_end_date =
5015: nvl(ppos2.actual_termination_date, hr_api.g_eot)
5016: and pas.assignment_id = pas2.assignment_id
5051: end if;
5052: exception
5053: when app_exception.application_exception then
5054: if hr_multi_message.exception_add
5055: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5056: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5057: ) then
5058: if g_debug then
5059: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5052: exception
5053: when app_exception.application_exception then
5054: if hr_multi_message.exception_add
5055: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5056: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5057: ) then
5058: if g_debug then
5059: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5060: end if;
5069: -- |---------------------< chk_date_probation_end >--------------------------|
5070: -- ---------------------------------------------------------------------------
5071: --
5072: procedure chk_date_probation_end
5073: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5074: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5075: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5070: -- ---------------------------------------------------------------------------
5071: --
5072: procedure chk_date_probation_end
5073: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5074: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5075: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5078: ,p_validation_start_date in date
5071: --
5072: procedure chk_date_probation_end
5073: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5074: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5075: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5078: ,p_validation_start_date in date
5079: ,p_effective_date in date
5072: procedure chk_date_probation_end
5073: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5074: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5075: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5078: ,p_validation_start_date in date
5079: ,p_effective_date in date
5080: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5073: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5074: ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
5075: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5078: ,p_validation_start_date in date
5079: ,p_effective_date in date
5080: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5081: )
5076: ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5077: ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5078: ,p_validation_start_date in date
5079: ,p_effective_date in date
5080: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5081: )
5082: is
5083: --
5084: l_proc varchar2(72) := g_package||'chk_date_probation_end';
5087: l_min_effective_start_date date;
5088: --
5089: cursor csr_get_min_asg_esd is
5090: select min(effective_start_date)
5091: from per_all_assignments_f
5092: where assignment_id = p_assignment_id;
5093: --
5094: begin
5095: if g_debug then
5160: if p_date_probation_end < l_min_effective_start_date then
5161: --
5162: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5163: hr_multi_message.add
5164: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5165: );
5166: --
5167: end if;
5168: if g_debug then
5177: if p_date_probation_end < p_validation_start_date then
5178: --
5179: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5180: hr_multi_message.add
5181: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5182: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5183: );
5184: --
5185: end if;
5178: --
5179: hr_utility.set_message(801, 'HR_51147_ASG_DPE_BEF_MIN_ESD');
5180: hr_multi_message.add
5181: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END'
5182: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5183: );
5184: --
5185: end if;
5186: if g_debug then
5209: -- |--------------------< chk_default_code_comb_id >-------------------------|
5210: -- ---------------------------------------------------------------------------
5211: --
5212: procedure chk_default_code_comb_id
5213: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5214: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5216: ,p_effective_date in date
5217: ,p_validation_start_date in date
5210: -- ---------------------------------------------------------------------------
5211: --
5212: procedure chk_default_code_comb_id
5213: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5214: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5216: ,p_effective_date in date
5217: ,p_validation_start_date in date
5218: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5211: --
5212: procedure chk_default_code_comb_id
5213: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5214: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5216: ,p_effective_date in date
5217: ,p_validation_start_date in date
5218: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5219: )
5214: ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
5215: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5216: ,p_effective_date in date
5217: ,p_validation_start_date in date
5218: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5219: )
5220: is
5221: --
5222: l_api_updating boolean;
5285: if p_assignment_type not in ('E','A','B','C','O') then
5286: --
5287: hr_utility.set_message(801, 'HR_51177_ASG_INV_ASG_TYP_DCC');
5288: hr_multi_message.add
5289: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5290: );
5291: --
5292: end if;
5293: if g_debug then
5301: if csr_valid_def_cod_comb%notfound then
5302: close csr_valid_def_cod_comb;
5303: hr_utility.set_message(801, 'HR_51148_ASG_INV_DEF_COD_COM');
5304: hr_multi_message.add
5305: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5306: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5307: );
5308: --
5309: else
5302: close csr_valid_def_cod_comb;
5303: hr_utility.set_message(801, 'HR_51148_ASG_INV_DEF_COD_COM');
5304: hr_multi_message.add
5305: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID'
5306: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5307: );
5308: --
5309: else
5310: close csr_valid_def_cod_comb;
5350: -- Access Status:
5351: -- Internal Table Handler Use Only.
5352: --
5353: procedure chk_del_organization_id
5354: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5355: ,p_effective_date in date
5356: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5357: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5358: ,p_org_now_no_manager_warning in out nocopy boolean
5352: --
5353: procedure chk_del_organization_id
5354: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5355: ,p_effective_date in date
5356: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5357: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5358: ,p_org_now_no_manager_warning in out nocopy boolean
5359: )
5360: is
5353: procedure chk_del_organization_id
5354: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5355: ,p_effective_date in date
5356: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
5357: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
5358: ,p_org_now_no_manager_warning in out nocopy boolean
5359: )
5360: is
5361: --
5397: -- |-----------------------< chk_employment_category >-----------------------|
5398: -- ---------------------------------------------------------------------------
5399: --
5400: procedure chk_employment_category
5401: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5402: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5403: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5404: ,p_effective_date in date
5405: ,p_validation_start_date in date
5398: -- ---------------------------------------------------------------------------
5399: --
5400: procedure chk_employment_category
5401: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5402: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5403: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5404: ,p_effective_date in date
5405: ,p_validation_start_date in date
5406: ,p_validation_end_date in date
5399: --
5400: procedure chk_employment_category
5401: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5402: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5403: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5404: ,p_effective_date in date
5405: ,p_validation_start_date in date
5406: ,p_validation_end_date in date
5407: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5403: ,p_employment_category in per_all_assignments_f.employment_category%TYPE
5404: ,p_effective_date in date
5405: ,p_validation_start_date in date
5406: ,p_validation_end_date in date
5407: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5408: )
5409: is
5410: --
5411: l_proc varchar2(72) := g_package||'chk_employment_category';
5546: end if;
5547: exception
5548: when app_exception.application_exception then
5549: if hr_multi_message.exception_add
5550: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY'
5551: ) then
5552: if g_debug then
5553: hr_utility.set_location(' Leaving:'|| l_proc, 90);
5554: end if;
5563: -- |-------------------------< chk_frequency >-------------------------------|
5564: -- ---------------------------------------------------------------------------
5565: --
5566: procedure chk_frequency
5567: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5568: ,p_frequency in per_all_assignments_f.frequency%TYPE
5569: ,p_effective_date in date
5570: ,p_validation_start_date in date
5571: ,p_validation_end_date in date
5564: -- ---------------------------------------------------------------------------
5565: --
5566: procedure chk_frequency
5567: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5568: ,p_frequency in per_all_assignments_f.frequency%TYPE
5569: ,p_effective_date in date
5570: ,p_validation_start_date in date
5571: ,p_validation_end_date in date
5572: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5568: ,p_frequency in per_all_assignments_f.frequency%TYPE
5569: ,p_effective_date in date
5570: ,p_validation_start_date in date
5571: ,p_validation_end_date in date
5572: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5573: )
5574: is
5575: --
5576: l_api_updating boolean;
5665: end if;
5666: exception
5667: when app_exception.application_exception then
5668: if hr_multi_message.exception_add
5669: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
5670: ) then
5671: if g_debug then
5672: hr_utility.set_location(' Leaving:'|| l_proc, 90);
5673: end if;
5705: -- Access Status:
5706: -- Internal Table Handler Use Only.
5707: --
5708: procedure chk_future_primary
5709: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5710: ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5711: ,p_datetrack_mode in varchar2
5712: ,p_effective_date in date
5713: )
5706: -- Internal Table Handler Use Only.
5707: --
5708: procedure chk_future_primary
5709: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5710: ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
5711: ,p_datetrack_mode in varchar2
5712: ,p_effective_date in date
5713: )
5714: is
5717: l_proc varchar2(72) := g_package||'chk_future_primary';
5718: --
5719: cursor csr_chk_fut_prim is
5720: select null
5721: from per_all_assignments_f
5722: where assignment_id = p_assignment_id
5723: and effective_start_date >= p_effective_date
5724: and primary_flag = 'Y';
5725: --
5757: end if;
5758: exception
5759: when app_exception.application_exception then
5760: if hr_multi_message.exception_add
5761: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID'
5762: ) then
5763: if g_debug then
5764: hr_utility.set_location(' Leaving:'|| l_proc, 4);
5765: end if;
5774: -- |---------------------------< chk_grade_id >------------------------------|
5775: -- ---------------------------------------------------------------------------
5776: --
5777: procedure chk_grade_id
5778: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5775: -- ---------------------------------------------------------------------------
5776: --
5777: procedure chk_grade_id
5778: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5776: --
5777: procedure chk_grade_id
5778: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5777: procedure chk_grade_id
5778: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5778: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5786: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5779: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5780: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5786: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5787: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5781: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5786: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5787: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5788: )
5789: is
5782: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5786: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5787: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5788: )
5789: is
5790: --
5783: ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
5784: ,p_effective_date in date
5785: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5786: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
5787: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5788: )
5789: is
5790: --
5791: l_exists varchar2(1);
5791: l_exists varchar2(1);
5792: l_api_updating boolean;
5793: l_business_group_id number(15);
5794: l_proc varchar2(72) := g_package||'chk_grade_id';
5795: l_vac_grade_id per_all_assignments_f.grade_id%TYPE;
5796: --
5797: cursor csr_valid_grade is
5798: select business_group_id
5799: from per_grades
5869: if csr_valid_grade%notfound then
5870: close csr_valid_grade;
5871: hr_utility.set_message(801, 'HR_7393_ASG_INVALID_GRADE');
5872: hr_multi_message.add
5873: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
5874: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5875: );
5876: --
5877: else
5870: close csr_valid_grade;
5871: hr_utility.set_message(801, 'HR_7393_ASG_INVALID_GRADE');
5872: hr_multi_message.add
5873: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
5874: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5875: );
5876: --
5877: else
5878: close csr_valid_grade;
5887: if l_business_group_id <> p_business_group_id then
5888: --
5889: hr_utility.set_message(801, 'HR_7371_ASG_INVALID_BG_GRADE');
5890: hr_multi_message.add
5891: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
5892: );
5893: --
5894: end if;
5895: if g_debug then
5900: -- When grade is null special ceiling step should be null
5901: --
5902: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
5903: hr_multi_message.add
5904: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
5905: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
5906: );
5907: --
5908: end if;
5901: --
5902: hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
5903: hr_multi_message.add
5904: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
5905: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
5906: );
5907: --
5908: end if;
5909: if g_debug then
5921: -- |-----------------------------< chk_job_id >------------------------------|
5922: -- ---------------------------------------------------------------------------
5923: --
5924: procedure chk_job_id
5925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5926: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5927: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5928: ,p_job_id in per_all_assignments_f.job_id%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5922: -- ---------------------------------------------------------------------------
5923: --
5924: procedure chk_job_id
5925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5926: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5927: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5928: ,p_job_id in per_all_assignments_f.job_id%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5930: ,p_effective_date in date
5923: --
5924: procedure chk_job_id
5925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5926: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5927: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5928: ,p_job_id in per_all_assignments_f.job_id%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5930: ,p_effective_date in date
5931: ,p_validation_start_date in date
5924: procedure chk_job_id
5925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5926: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5927: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5928: ,p_job_id in per_all_assignments_f.job_id%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5930: ,p_effective_date in date
5931: ,p_validation_start_date in date
5932: ,p_validation_end_date in date
5925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5926: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5927: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5928: ,p_job_id in per_all_assignments_f.job_id%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5930: ,p_effective_date in date
5931: ,p_validation_start_date in date
5932: ,p_validation_end_date in date
5933: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5929: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5930: ,p_effective_date in date
5931: ,p_validation_start_date in date
5932: ,p_validation_end_date in date
5933: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5934: )
5935: is
5936: --
5937: l_proc varchar2(72) := g_package||'chk_job_id';
5936: --
5937: l_proc varchar2(72) := g_package||'chk_job_id';
5938: l_exists varchar2(1);
5939: l_api_updating boolean;
5940: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
5941: l_vac_job_id per_all_assignments_f.job_id%TYPE;
5942: --
5943: --
5944: -- Bug 33552211 Start Here
5937: l_proc varchar2(72) := g_package||'chk_job_id';
5938: l_exists varchar2(1);
5939: l_api_updating boolean;
5940: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
5941: l_vac_job_id per_all_assignments_f.job_id%TYPE;
5942: --
5943: --
5944: -- Bug 33552211 Start Here
5945: -- Description : Changed the cursor sql query to improve the performance
6032: if csr_valid_job%notfound then
6033: close csr_valid_job;
6034: hr_utility.set_message(801, 'HR_51172_ASG_INV_DT_JOB');
6035: hr_multi_message.add
6036: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6037: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6038: );
6039: --
6040: else
6033: close csr_valid_job;
6034: hr_utility.set_message(801, 'HR_51172_ASG_INV_DT_JOB');
6035: hr_multi_message.add
6036: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6037: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6038: );
6039: --
6040: else
6041: close csr_valid_job;
6050: If p_business_group_id <> l_business_group_id then
6051: --
6052: hr_utility.set_message(801, 'HR_51173_ASG_INV_DT_JOB_BG');
6053: hr_multi_message.add
6054: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6055: );
6056: --
6057: end if;
6058: if g_debug then
6075: -- |----------------------< chk_job_id_grade_id >----------------------------|
6076: -- ---------------------------------------------------------------------------
6077: --
6078: procedure chk_job_id_grade_id
6079: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6080: ,p_job_id in per_all_assignments_f.job_id%TYPE
6081: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6082: ,p_effective_date in date
6083: ,p_validation_start_date in date
6076: -- ---------------------------------------------------------------------------
6077: --
6078: procedure chk_job_id_grade_id
6079: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6080: ,p_job_id in per_all_assignments_f.job_id%TYPE
6081: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6082: ,p_effective_date in date
6083: ,p_validation_start_date in date
6084: ,p_validation_end_date in date
6077: --
6078: procedure chk_job_id_grade_id
6079: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6080: ,p_job_id in per_all_assignments_f.job_id%TYPE
6081: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6082: ,p_effective_date in date
6083: ,p_validation_start_date in date
6084: ,p_validation_end_date in date
6085: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6081: ,p_grade_id in per_all_assignments_f.grade_id%TYPE
6082: ,p_effective_date in date
6083: ,p_validation_start_date in date
6084: ,p_validation_end_date in date
6085: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6086: ,p_inv_job_grade_warning out nocopy boolean
6087: )
6088: is
6089: --
6121: hr_utility.set_location('Entering:'|| l_proc, 10);
6122: end if;
6123: --
6124: if hr_multi_message.no_exclusive_error
6125: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6126: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6127: ) then
6128: --
6129: -- Check mandatory arguments
6122: end if;
6123: --
6124: if hr_multi_message.no_exclusive_error
6125: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
6126: ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
6127: ) then
6128: --
6129: -- Check mandatory arguments
6130: --
6228: -- |--------------------------< chk_location_id >----------------------------|
6229: -- ---------------------------------------------------------------------------
6230: --
6231: procedure chk_location_id
6232: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6233: ,p_location_id in per_all_assignments_f.location_id%TYPE
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6229: -- ---------------------------------------------------------------------------
6230: --
6231: procedure chk_location_id
6232: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6233: ,p_location_id in per_all_assignments_f.location_id%TYPE
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6230: --
6231: procedure chk_location_id
6232: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6233: ,p_location_id in per_all_assignments_f.location_id%TYPE
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6238: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6231: procedure chk_location_id
6232: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6233: ,p_location_id in per_all_assignments_f.location_id%TYPE
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6238: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6239: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6233: ,p_location_id in per_all_assignments_f.location_id%TYPE
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6238: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6239: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6240: )
6241: is
6234: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6238: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6239: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6240: )
6241: is
6242: --
6235: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6236: ,p_effective_date in date
6237: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6238: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6239: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6240: )
6241: is
6242: --
6243: l_exists varchar2(1);
6243: l_exists varchar2(1);
6244: l_api_updating boolean;
6245: l_proc varchar2(72) := g_package||'chk_location_id';
6246: l_inactive_date date;
6247: l_vac_location_id per_all_assignments_f.location_id%TYPE;
6248: -- Bug 4116879 Starts
6249: -- Desc : The fix made for the bug 3895708, not considerd the INSERT scenario.
6250: -- While creating the Assignment per_all_assignments_f is not populated
6251: -- so the sub query fails. Re-write the cursor to implement INSERT too.
6246: l_inactive_date date;
6247: l_vac_location_id per_all_assignments_f.location_id%TYPE;
6248: -- Bug 4116879 Starts
6249: -- Desc : The fix made for the bug 3895708, not considerd the INSERT scenario.
6250: -- While creating the Assignment per_all_assignments_f is not populated
6251: -- so the sub query fails. Re-write the cursor to implement INSERT too.
6252: -- Also, fix for the bug 4105698 is modified to make the cursor
6253: -- compatible with 8i.
6254: cursor csr_valid_location_upd is
6256: from hr_locations_all
6257: where location_id = p_location_id
6258: and (business_group_id= (
6259: select distinct business_group_id
6260: from per_all_assignments_f
6261: where assignment_id= p_assignment_id)
6262: or business_group_id is null);
6263:
6264: -- bug 4318990 added an nvl in the where condition for relaxing the validation
6333: if csr_valid_location_upd%notfound then
6334: close csr_valid_location_upd;
6335: hr_utility.set_message(801, 'HR_7382_ASG_NON_EXIST_LOCATION');
6336: hr_multi_message.add
6337: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID');
6338: --
6339: else
6340: close csr_valid_location_upd;
6341: end if;
6345: if csr_valid_location_ins%notfound then
6346: close csr_valid_location_ins;
6347: hr_utility.set_message(801, 'HR_7382_ASG_NON_EXIST_LOCATION');
6348: hr_multi_message.add
6349: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID');
6350: --
6351: else
6352: close csr_valid_location_ins;
6353: end if;
6365: then
6366: --
6367: hr_utility.set_message(801, 'HR_51215_ASG_INACT_LOCATION');
6368: hr_multi_message.add
6369: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID'
6370: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6371: );
6372: --
6373: end if;
6366: --
6367: hr_utility.set_message(801, 'HR_51215_ASG_INACT_LOCATION');
6368: hr_multi_message.add
6369: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.LOCATION_ID'
6370: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
6371: );
6372: --
6373: end if;
6374: if g_debug then
6388: -- |-------------------------< chk_manager_flag >----------------------------|
6389: -- ---------------------------------------------------------------------------
6390: --
6391: procedure chk_manager_flag
6392: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6393: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6394: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6395: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6396: ,p_effective_date in date
6389: -- ---------------------------------------------------------------------------
6390: --
6391: procedure chk_manager_flag
6392: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6393: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6394: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6395: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6396: ,p_effective_date in date
6397: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6390: --
6391: procedure chk_manager_flag
6392: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6393: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6394: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6395: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6396: ,p_effective_date in date
6397: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6398: ,p_other_manager_warning in out nocopy boolean
6391: procedure chk_manager_flag
6392: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6393: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6394: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6395: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6396: ,p_effective_date in date
6397: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6398: ,p_other_manager_warning in out nocopy boolean
6399: ,p_no_managers_warning in out nocopy boolean
6393: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6394: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6395: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6396: ,p_effective_date in date
6397: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6398: ,p_other_manager_warning in out nocopy boolean
6399: ,p_no_managers_warning in out nocopy boolean
6400: )
6401: is
6555: -- |---------------------< chk_frequency_normal_hours >----------------------|
6556: -- ---------------------------------------------------------------------------
6557: --
6558: procedure chk_frequency_normal_hours
6559: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6560: ,p_frequency in per_all_assignments_f.frequency%TYPE
6561: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6562: ,p_effective_date in date
6563: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6556: -- ---------------------------------------------------------------------------
6557: --
6558: procedure chk_frequency_normal_hours
6559: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6560: ,p_frequency in per_all_assignments_f.frequency%TYPE
6561: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6562: ,p_effective_date in date
6563: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6564: )
6557: --
6558: procedure chk_frequency_normal_hours
6559: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6560: ,p_frequency in per_all_assignments_f.frequency%TYPE
6561: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6562: ,p_effective_date in date
6563: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6564: )
6565: is
6559: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6560: ,p_frequency in per_all_assignments_f.frequency%TYPE
6561: ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
6562: ,p_effective_date in date
6563: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6564: )
6565: is
6566: --
6567: l_api_updating boolean;
6572: hr_utility.set_location('Entering:'|| l_proc, 10);
6573: end if;
6574: --
6575: if hr_multi_message.no_all_inclusive_error
6576: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6577: ) then
6578: --
6579: -- Check mandatory parameters have been set
6580: --
6651: end if;
6652: exception
6653: when app_exception.application_exception then
6654: if hr_multi_message.exception_add
6655: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6656: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS'
6657: ) then
6658: if g_debug then
6659: hr_utility.set_location(' Leaving:'|| l_proc, 80);
6652: exception
6653: when app_exception.application_exception then
6654: if hr_multi_message.exception_add
6655: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.FREQUENCY'
6656: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS'
6657: ) then
6658: if g_debug then
6659: hr_utility.set_location(' Leaving:'|| l_proc, 80);
6660: end if;
6669: -- |-----------------------< chk_organization_id >---------------------------|
6670: -- ---------------------------------------------------------------------------
6671: --
6672: procedure chk_organization_id
6673: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6670: -- ---------------------------------------------------------------------------
6671: --
6672: procedure chk_organization_id
6673: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6671: --
6672: procedure chk_organization_id
6673: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6672: procedure chk_organization_id
6673: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6673: (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6674: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6682: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6675: ,p_organization_id in per_all_assignments_f.organization_id%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6682: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6683: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6676: ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
6677: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6682: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6683: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6684: ,p_org_now_no_manager_warning in out nocopy boolean
6678: ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6682: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6683: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6684: ,p_org_now_no_manager_warning in out nocopy boolean
6685: ,p_other_manager_warning in out nocopy boolean
6686: )
6679: ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
6680: ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
6681: ,p_effective_date in date
6682: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6683: ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
6684: ,p_org_now_no_manager_warning in out nocopy boolean
6685: ,p_other_manager_warning in out nocopy boolean
6686: )
6687: is
6688: --
6689: l_exists varchar2(1);
6690: l_api_updating boolean;
6691: l_proc varchar2(72) := g_package||'chk_organization_id';
6692: l_vac_organization_id per_all_assignments_f.organization_id%TYPE;
6693: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6694: --
6695: -- bugfix 2452613: use full table not secure view for validation
6696: --
6689: l_exists varchar2(1);
6690: l_api_updating boolean;
6691: l_proc varchar2(72) := g_package||'chk_organization_id';
6692: l_vac_organization_id per_all_assignments_f.organization_id%TYPE;
6693: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
6694: --
6695: -- bugfix 2452613: use full table not secure view for validation
6696: --
6697: cursor csr_valid_int_hr_org is
6778: if csr_valid_int_hr_org%notfound then
6779: close csr_valid_int_hr_org;
6780: hr_utility.set_message(801, 'HR_34983_ASG_INVALID_ORG');
6781: hr_multi_message.add
6782: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
6783: );
6784: --
6785: else
6786: close csr_valid_int_hr_org;
6795: If p_business_group_id <> l_business_group_id then
6796: --
6797: hr_utility.set_message(801, 'HR_7376_ASG_INVALID_BG_ORG');
6798: hr_multi_message.add
6799: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
6800: );
6801: --
6802: end if;
6803: if g_debug then
6822: if csr_valid_per_org%notfound then
6823: close csr_valid_per_org;
6824: hr_utility.set_message(801, 'HR_51277_ASG_INV_HR_ORG');
6825: hr_multi_message.add
6826: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
6827: );
6828: --
6829: else
6830: close csr_valid_per_org;
6841: --
6842: If p_assignment_type = 'E' then
6843: --
6844: if hr_multi_message.no_exclusive_error
6845: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.MANAGER_FLAG'
6846: ) then
6847: --
6848: -- Check if manager flag is 'Y'
6849: --
6921: -- Access Status:
6922: -- Internal Table Handler Use Only
6923: --
6924: procedure chk_bargaining_unit_code
6925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6926: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
6927: ,p_effective_date in date
6928: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6929: ,p_validation_start_date in date
6922: -- Internal Table Handler Use Only
6923: --
6924: procedure chk_bargaining_unit_code
6925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6926: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
6927: ,p_effective_date in date
6928: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6929: ,p_validation_start_date in date
6930: ,p_validation_end_date in date
6924: procedure chk_bargaining_unit_code
6925: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
6926: ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
6927: ,p_effective_date in date
6928: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
6929: ,p_validation_start_date in date
6930: ,p_validation_end_date in date
6931: )
6932: is
6969: end if;
6970: exception
6971: when app_exception.application_exception then
6972: if hr_multi_message.exception_add
6973: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.BARGAINING_UNIT_CODE'
6974: ) then
6975: if g_debug then
6976: hr_utility.set_location(' Leaving:'|| l_proc, 40);
6977: end if;
7017: -- Access Status:
7018: -- Internal Table Handler Use Only
7019: --
7020: procedure chk_hourly_salaried_code
7021: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7022: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7023: ,p_effective_date in date
7024: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7025: ,p_validation_start_date in date
7018: -- Internal Table Handler Use Only
7019: --
7020: procedure chk_hourly_salaried_code
7021: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7022: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7023: ,p_effective_date in date
7024: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7025: ,p_validation_start_date in date
7026: ,p_validation_end_date in date
7020: procedure chk_hourly_salaried_code
7021: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7022: ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
7023: ,p_effective_date in date
7024: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7025: ,p_validation_start_date in date
7026: ,p_validation_end_date in date
7027: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7028: ,p_hourly_salaried_warning in out nocopy boolean
7023: ,p_effective_date in date
7024: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7025: ,p_validation_start_date in date
7026: ,p_validation_end_date in date
7027: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7028: ,p_hourly_salaried_warning in out nocopy boolean
7029: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE)
7030: is
7031: --
7025: ,p_validation_start_date in date
7026: ,p_validation_end_date in date
7027: ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
7028: ,p_hourly_salaried_warning in out nocopy boolean
7029: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE)
7030: is
7031: --
7032: l_proc varchar2(72) := g_package||'chk_hourly_salaried_code';
7033: l_hourly_salaried_code varchar2(72);
7076: )
7077: then
7078: hr_utility.set_message(800,'PER_52407_HOUR_SAL_CODE');
7079: hr_multi_message.add
7080: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.HOURLY_SALARIED_CODE'
7081: );
7082: else
7083: --
7084: if hr_multi_message.no_exclusive_error
7081: );
7082: else
7083: --
7084: if hr_multi_message.no_exclusive_error
7085: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
7086: ) then
7087: open csr_hourly_salaried;
7088: fetch csr_hourly_salaried into l_pay_basis;
7089: if csr_hourly_salaried%FOUND then
7127: cursor csr_leg_code is
7128: select pbg.legislation_code
7129: from per_business_groups_perf pbg
7130: where pbg.business_group_id = (select distinct asg.business_group_id from
7131: per_all_assignments_f asg
7132: where asg.assignment_id = p_assignment_id);
7133:
7134: --
7135: -- Declare local variables
7284: -- Access Status:
7285: -- Internal Table Handler Use Only
7286: --
7287: procedure chk_frozen_single_pos
7288: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7289: ,p_position_id in per_all_assignments_f.position_id%TYPE
7290: ,p_effective_date in date
7291: ,p_assignment_type in varchar2 default 'E'
7292: )
7285: -- Internal Table Handler Use Only
7286: --
7287: procedure chk_frozen_single_pos
7288: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7289: ,p_position_id in per_all_assignments_f.position_id%TYPE
7290: ,p_effective_date in date
7291: ,p_assignment_type in varchar2 default 'E'
7292: )
7293: is
7328: if hr_psf_shd.get_availability_status(l_availability_status_id,l_business_group_id) ='FROZEN'
7329: then
7330: hr_utility.set_message(800, 'PER_NO_ASG_FOR_FROZEN_POS');
7331: hr_multi_message.add
7332: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7333: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7334: );
7335: end if;
7336: if (l_position_type = 'SINGLE') then
7329: then
7330: hr_utility.set_message(800, 'PER_NO_ASG_FOR_FROZEN_POS');
7331: hr_multi_message.add
7332: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7333: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7334: );
7335: end if;
7336: if (l_position_type = 'SINGLE') then
7337: l_asg_in_overlap_dates := chk_overlap_dates(p_position_id, p_effective_date);
7343: /*---- End change for the bug 5854568 ----(modified for the bug 6331872)*/
7344: then
7345: hr_utility.set_message(800, 'PER_ASG_EXISTS_FOR_SINGLE_POS');
7346: hr_multi_message.add
7347: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7348: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7349: );
7350: end if;
7351: end if; -- 6397484(forward port of 6356978)
7344: then
7345: hr_utility.set_message(800, 'PER_ASG_EXISTS_FOR_SINGLE_POS');
7346: hr_multi_message.add
7347: (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7348: ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
7349: );
7350: end if;
7351: end if; -- 6397484(forward port of 6356978)
7352: end if;
7359: --
7360: end chk_frozen_single_pos;
7361: --
7362: procedure chk_single_position
7363: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7364: ,p_position_id in per_all_assignments_f.position_id%TYPE
7365: ,p_effective_date in date
7366: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7367: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7360: end chk_frozen_single_pos;
7361: --
7362: procedure chk_single_position
7363: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7364: ,p_position_id in per_all_assignments_f.position_id%TYPE
7365: ,p_effective_date in date
7366: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7367: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7368: )
7362: procedure chk_single_position
7363: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7364: ,p_position_id in per_all_assignments_f.position_id%TYPE
7365: ,p_effective_date in date
7366: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7367: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7368: )
7369: is
7370: --
7363: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
7364: ,p_position_id in per_all_assignments_f.position_id%TYPE
7365: ,p_effective_date in date
7366: ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
7367: ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default 'E'
7368: )
7369: is
7370: --
7371: l_proc varchar2(72) := g_package||'chk_single_position';
7376: hr_utility.set_location('Entering:'|| l_proc, 10);
7377: end if;
7378: --
7379: if hr_multi_message.no_exclusive_error
7380: (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
7381: ) then
7382: --
7383: -- Check mandatory parameters have been set
7384: --
7455: p_except_assignment_id number) return boolean is
7456: l_dummy varchar2(1);
7457: cursor c1 is
7458: select 'x'
7459: from per_all_assignments_f asg, per_assignment_status_types ast
7460: where position_id = p_position_id
7461: and assignment_id <> nvl(p_except_assignment_id, -1)
7462: and ( assignment_type = 'E' /*or assignment_type = 'A'*/) -- change for the bug 5854568(modified for 6331872)
7463: and p_effective_date between effective_start_date and effective_end_date