[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select substr(hoi.ORG_INFORMATION3,1)
from hr_organization_information hoi
where hoi.org_information_context = 'Benefits Defaults'
and hoi.organization_id = p_business_group_id;
select per.business_group_id
from per_all_people_f per
where per.person_id = c_person_id
and c_eff_date between per.effective_start_date
and per.effective_end_date;
select null
from per_all_assignments_f asg,
per_assignment_status_types ast
where asg.person_id = c_person_id
and asg.assignment_type <> 'C'
and asg.assignment_status_type_id = ast.assignment_status_type_id
and c_eff_date between asg.effective_start_date
and asg.effective_end_date
and ast.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
and asg.primary_flag = 'Y';
select pay.period_type
from pay_all_payrolls_f pay
where pay.payroll_id = c_payroll_id
and c_eff_date
between pay.effective_start_date and pay.effective_end_date;
select to_number(ori.ORG_INFORMATION2)
from hr_organization_information ori
where ori.organization_id = c_bgp_id
and ori.ORG_INFORMATION_CONTEXT = 'Benefits Defaults';
select null
from gl_code_combinations
where code_combination_id = l_default_code_comb_id
and enabled_flag = 'Y'
and l_effective_date
between nvl(start_date_active,l_effective_date)
and nvl(end_date_active,l_effective_date);
select null
from hr_locations_all
where location_id = l_location_id
and l_effective_date <= nvl(inactive_date,l_effective_date);
select null
from per_all_people_f
where person_id = l_supervisor_id
and current_employee_flag = 'Y'
and l_effective_date
between effective_start_date
and effective_end_date;
select null
from per_assignment_status_types
where assignment_status_type_id = p_assignment_status_type_id
and active_flag = 'Y';
select assignment_status_type_id
into l_assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and default_flag = 'Y'
and business_group_id is null;
p_dt_mode => 'INSERT',
p_validation_start_date => l_asg_esd,
p_validation_end_date => l_asg_eed,
p_entries_changed => l_entries_changed,
p_old_hire_date => null,
p_old_people_group_id => null,
p_new_people_group_id => p_people_group_id
);
procedure update_ben_asg
(p_validate in boolean default false
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
--
,p_grade_id in number default hr_api.g_number
,p_position_id in number default hr_api.g_number
,p_job_id in number default hr_api.g_number
,p_payroll_id in number default hr_api.g_number
,p_location_id in number default hr_api.g_number
,p_special_ceiling_step_id in out nocopy number
,p_organization_id in number default hr_api.g_number
,p_people_group_id in number default hr_api.g_number
,p_pay_basis_id in number default hr_api.g_number
,p_employment_category in varchar2 default hr_api.g_varchar2
--
,p_supervisor_id in number default hr_api.g_number
,p_change_reason in varchar2 default hr_api.g_varchar2
,p_comments in varchar2 default hr_api.g_varchar2
,p_date_probation_end in date default hr_api.g_date
,p_default_code_comb_id in number default hr_api.g_number
,p_frequency in varchar2 default hr_api.g_varchar2
,p_internal_address_line in varchar2 default hr_api.g_varchar2
,p_manager_flag in varchar2 default hr_api.g_varchar2
,p_normal_hours in number default hr_api.g_number
,p_perf_review_period in number default hr_api.g_number
,p_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
,p_probation_period in number default hr_api.g_number
,p_probation_unit in varchar2 default hr_api.g_varchar2
,p_sal_review_period in number default hr_api.g_number
,p_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
,p_set_of_books_id in number default hr_api.g_number
,p_source_type in varchar2 default hr_api.g_varchar2
,p_time_normal_finish in varchar2 default hr_api.g_varchar2
,p_time_normal_start in varchar2 default hr_api.g_varchar2
,p_bargaining_unit_code in varchar2 default hr_api.g_varchar2
,p_labour_union_member_flag in varchar2 default hr_api.g_varchar2
,p_hourly_salaried_code in varchar2 default hr_api.g_varchar2
,p_ass_attribute_category in varchar2 default hr_api.g_varchar2
,p_ass_attribute1 in varchar2 default hr_api.g_varchar2
,p_ass_attribute2 in varchar2 default hr_api.g_varchar2
,p_ass_attribute3 in varchar2 default hr_api.g_varchar2
,p_ass_attribute4 in varchar2 default hr_api.g_varchar2
,p_ass_attribute5 in varchar2 default hr_api.g_varchar2
,p_ass_attribute6 in varchar2 default hr_api.g_varchar2
,p_ass_attribute7 in varchar2 default hr_api.g_varchar2
,p_ass_attribute8 in varchar2 default hr_api.g_varchar2
,p_ass_attribute9 in varchar2 default hr_api.g_varchar2
,p_ass_attribute10 in varchar2 default hr_api.g_varchar2
,p_ass_attribute11 in varchar2 default hr_api.g_varchar2
,p_ass_attribute12 in varchar2 default hr_api.g_varchar2
,p_ass_attribute13 in varchar2 default hr_api.g_varchar2
,p_ass_attribute14 in varchar2 default hr_api.g_varchar2
,p_ass_attribute15 in varchar2 default hr_api.g_varchar2
,p_ass_attribute16 in varchar2 default hr_api.g_varchar2
,p_ass_attribute17 in varchar2 default hr_api.g_varchar2
,p_ass_attribute18 in varchar2 default hr_api.g_varchar2
,p_ass_attribute19 in varchar2 default hr_api.g_varchar2
,p_ass_attribute20 in varchar2 default hr_api.g_varchar2
,p_ass_attribute21 in varchar2 default hr_api.g_varchar2
,p_ass_attribute22 in varchar2 default hr_api.g_varchar2
,p_ass_attribute23 in varchar2 default hr_api.g_varchar2
,p_ass_attribute24 in varchar2 default hr_api.g_varchar2
,p_ass_attribute25 in varchar2 default hr_api.g_varchar2
,p_ass_attribute26 in varchar2 default hr_api.g_varchar2
,p_ass_attribute27 in varchar2 default hr_api.g_varchar2
,p_ass_attribute28 in varchar2 default hr_api.g_varchar2
,p_ass_attribute29 in varchar2 default hr_api.g_varchar2
,p_ass_attribute30 in varchar2 default hr_api.g_varchar2
,p_title in varchar2 default hr_api.g_varchar2
,p_age in number default hr_api.g_number
,p_adjusted_service_date in date default hr_api.g_date
,p_original_hire_date in date default hr_api.g_date
,p_salary in varchar2 default hr_api.g_varchar2
,p_original_person_type in varchar2 default hr_api.g_varchar2
,p_termination_date in date default hr_api.g_date
,p_termination_reason in varchar2 default hr_api.g_varchar2
,p_leave_of_absence_date in date default hr_api.g_date
,p_absence_type in varchar2 default hr_api.g_varchar2
,p_absence_reason in varchar2 default hr_api.g_varchar2
,p_date_of_hire in date default hr_api.g_date
--
,p_called_from in varchar2 default hr_api.g_varchar2
--
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_ben_asg';
l_payroll_id_updated boolean;
select asg.assignment_type
, asg.business_group_id
, asg.soft_coding_keyflex_id
, asg.payroll_id
from per_all_assignments_f asg
where asg.assignment_id = c_assignment_id
and asg.assignment_type = 'B'
and c_eff_date between asg.effective_start_date
and asg.effective_end_date;
select aei.assignment_extra_info_id
, aei.object_version_number
, aei.aei_information5
from per_assignment_extra_info aei
where aei.assignment_id = c_assignment_id
and aei.aei_information_category = 'BEN_DERIVED';
select to_number(ori.ORG_INFORMATION2)
from hr_organization_information ori
where ori.organization_id = c_bgp_id
and ori.ORG_INFORMATION_CONTEXT = 'Benefits Defaults';
savepoint update_ben_asg;
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_boolean_dummy2
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_boolean_dummy2
);
p_dt_mode => p_datetrack_update_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date,
p_entries_changed => l_entries_changed,
p_old_hire_date => null,
p_old_people_group_id => null,
p_new_people_group_id => p_people_group_id
);
if l_payroll_id_updated
then
--
hr_utility.set_location(l_proc, 50);
hr_assignment_extra_info_api.update_assignment_extra_info
(p_assignment_extra_info_id => l_assignment_extra_info_id
,p_object_version_number => l_assignment_extra_info_ovn
,p_aei_information_category => 'BEN_DERIVED'
,p_aei_information1 => l_char_age -- p_age
,p_aei_information2 => l_char_adjusted_service_date -- fnd_date.date_to_canonical(p_adjusted_service_date)
,p_aei_information3 => l_char_original_hire_date -- fnd_date.date_to_canonical(p_original_hire_date)
,p_aei_information4 => l_payroll_changed
,p_aei_information5 => l_orig_payroll_id
,p_aei_information6 => p_salary
,p_aei_information7 => p_original_person_type
,p_aei_information8 => l_char_termination_date -- fnd_date.date_to_canonical(p_termination_date)
,p_aei_information9 => p_termination_reason
,p_aei_information10 => l_char_leave_of_absence_date -- fnd_date.date_to_canonical(p_leave_of_absence_date)
,p_aei_information11 => p_absence_type
,p_aei_information12 => p_absence_reason
,p_aei_information13 => l_char_date_of_hire -- fnd_date.date_to_canonical(p_date_of_hire)
);
ROLLBACK TO update_ben_asg;
ROLLBACK TO update_ben_asg;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end update_ben_asg;
procedure delete_ben_asg
(p_validate in boolean default false
,p_datetrack_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
---
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_ben_asg';
select ptu.person_type_usage_id,
ptu.object_version_number
from per_all_assignments_f asg,
per_person_type_usages_f ptu,
per_person_types pet
where ptu.person_id = asg.person_id
and asg.assignment_type <> 'C'
and ptu.person_type_id = pet.person_type_id
and c_eff_date
between ptu.effective_start_date and ptu.effective_end_date
and c_eff_date
between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = c_assignment_id
and pet.SYSTEM_PERSON_TYPE
in('SRVNG_SPS'
,'FRMR_SPS'
,'SRVNG_FMLY_MMBR'
,'FRMR_FMLY_MMBR'
);
select pen.PRTT_ENRT_RSLT_ID,
pen.object_version_number
from BEN_PRTT_ENRT_RSLT_F pen
where c_eff_date
between pen.effective_start_date and pen.effective_end_date
and pen.assignment_id = c_assignment_id;
select asg.business_group_id
into l_business_group_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.assignment_type <> 'C'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date;
savepoint delete_ben_asg;
delete from per_person_type_usages_f ptu
where ptu.person_id in
(select asg.person_id
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
)
and ptu.person_type_id in
(select pet.person_type_id
from per_person_types pet
where pet.SYSTEM_PERSON_TYPE
in('SRVNG_SPS'
,'FRMR_SPS'
,'SRVNG_FMLY_MMBR'
,'FRMR_FMLY_MMBR'
)
);
delete from per_bookings chd
where chd.event_id in
(select par.event_id
from per_events par
where par.assignment_id = p_assignment_id
);
delete from per_events par
where par.assignment_id = p_assignment_id;
delete from per_pay_proposal_components chd
where chd.pay_proposal_id in
(select par.pay_proposal_id
from per_pay_proposals par
where par.assignment_id = p_assignment_id
);
delete from per_pay_proposals par
where par.assignment_id = p_assignment_id;
delete from pay_element_entry_values_f chd
where chd.ELEMENT_ENTRY_ID in
(select par.ELEMENT_ENTRY_ID
from pay_element_entries_f par
where par.assignment_id = p_assignment_id
);
delete from pay_element_entries_f par
where par.assignment_id = p_assignment_id;
delete from ben_le_clsn_n_rstr
where assignment_id = p_assignment_id;
delete from ben_prtt_enrt_rslt_f
where assignment_id = p_assignment_id;
delete from per_assignment_budget_values_f
where assignment_id = p_assignment_id;
delete from per_assignment_extra_info
where assignment_id = p_assignment_id;
delete from per_assign_proposal_answers
where assignment_id = p_assignment_id;
delete from per_letter_request_lines
where assignment_id = p_assignment_id;
delete from per_mm_assignments
where assignment_id = p_assignment_id;
delete from per_quickpaint_result_text
where assignment_id = p_assignment_id;
delete from per_secondary_ass_statuses
where assignment_id = p_assignment_id;
delete from per_spinal_point_placements_f
where assignment_id = p_assignment_id;
delete from hr_assignment_set_amendments
where assignment_id = p_assignment_id;
delete from pay_cost_allocations_f
where assignment_id = p_assignment_id;
delete from pay_personal_payment_methods_f
where assignment_id = p_assignment_id;
delete from pay_assignment_latest_balances
where assignment_id = p_assignment_id;
delete from pay_assignment_link_usages_f
where assignment_id = p_assignment_id;
elsif p_datetrack_mode = hr_api.g_delete then
--
-- End date related information
--
-- - PTUs
--
for dets in c_getbenasgptudets
(c_assignment_id => p_assignment_id
,c_eff_date => p_effective_date
)
loop
--
l_ovn := dets.object_version_number;
hr_per_type_usage_internal.delete_person_type_usage
(p_person_type_usage_id => dets.person_type_usage_id
,p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE'
,p_object_version_number => l_ovn
--
,p_effective_start_date => l_dummy_date1
,p_effective_end_date => l_dummy_date2
);
ben_PRTT_ENRT_RESULT_api.update_PRTT_ENRT_RESULT
(p_prtt_enrt_rslt_id => dets.prtt_enrt_rslt_id
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE'
--
,p_effective_start_date => l_dummy_date1
,p_effective_end_date => l_dummy_date2
);
ROLLBACK TO delete_ben_asg;
ROLLBACK TO delete_ben_asg;
/* Inserted for nocopy changes */
p_object_version_number := l_object_version_number;
end delete_ben_asg;