[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_last_change_date(p_assignment_id in number
,p_change_date in date) is
cursor csr_next_proposal is
select pay_proposal_id, rowid
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date=(
select min(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_change_date);
l_proc varchar2(30):= 'update_last_change_date';
update per_pay_proposals
set last_change_date = l_last_change_date,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where rowid=l_row_id;
update per_pay_proposals
set last_change_date = l_last_change_date
where rowid=l_row_id;
hr_utility.set_location('Updated successfuly:',35);
end update_last_change_date;
select pay_proposal_id,date_to
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date =(select max(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date < p_date_to+1 and approved = 'N');
hr_utility.set_location('About to update',45);
update per_pay_proposals
set date_to = p_date_to,
-- added by vkodedal fix for 6831216
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
-- end of fix
where assignment_id = p_assignment_id
and pay_proposal_id = l_pay_proposal_id;
hr_utility.set_location('Updated successfuly:',85);
select pay_proposal_id, date_to
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date =(select max(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date < p_date_to+1 and approved = 'Y');
select pay_proposal_id,object_version_number, business_group_id
from per_pay_proposals
where assignment_id = p_assignment_id
--added for the bug 7673294 to exclude the calling proposal
and (p_proposal_id IS NULL OR pay_proposal_id <> p_proposal_id)
and change_date < p_date_to+1
and approved = 'N';
hr_utility.set_location('About to update',45);
update per_pay_proposals
set date_to = p_date_to,
-- added by vkodedal fix for 6831216
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
-- end of fix
where assignment_id = p_assignment_id
and pay_proposal_id = l_pay_proposal_id;
hr_utility.set_location('Updated successfuly:',85);
if( HR_MAINTAIN_PROPOSAL_API.g_deleted_from_oa = 'N')
then
hr_utility.set_location('Within delete from OA',90);
hr_maintain_proposal_api.delete_salary_proposal
(p_pay_proposal_id => a.pay_proposal_id
,p_business_group_id => a.business_group_id
,p_object_version_number => a.object_version_number
,p_salary_warning => l_del_warn);
select pay_proposal_id,date_to
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date =(select max(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date < p_date_to+1);
select approved
from per_pay_proposals
where pay_proposal_id = nvl(p_proposal_id, pay_proposal_id)
and assignment_id = p_assignment_id
and change_date = p_date_to +1 ;
hr_utility.set_location('About to update',45);
update per_pay_proposals
set date_to = p_date_to,
-- added by vkodedal fix for 6831216
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
-- end of fix
where assignment_id = p_assignment_id
and pay_proposal_id = l_pay_proposal_id;
hr_utility.set_location('Updated successfuly:',85);
select assignment_id, change_date, date_to,last_change_date, approved
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
select pay_basis_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_date between effective_start_date and effective_end_date;
l_deleted_proposal_date date;
fetch c_pay_proposal into l_assignment_id, l_deleted_proposal_date, l_date_to,l_last_change_date, l_approved;
open c_asg_pay_basis_id(l_assignment_id , l_deleted_proposal_date);
open c_asg_pay_basis_id(l_assignment_id , l_deleted_proposal_date-1);
update per_pay_proposals
set date_to = l_date_to,
-- added by vkodedal fix for 6831216
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
-- end of fix
where assignment_id = l_assignment_id
and (l_deleted_proposal_date -1) between change_date and date_to
and approved = 'N';
update per_pay_proposals
set date_to = l_date_to,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where assignment_id = l_assignment_id
and approved = 'Y'
and change_date = ( select max(change_date)
from per_pay_proposals
where assignment_id = l_assignment_id
and change_date < l_deleted_proposal_date
and approved = 'Y' );
update per_pay_proposals
set last_change_date = l_last_change_date
where assignment_id = l_assignment_id
and change_date =
(select min(t.change_date)
from per_pay_proposals t
where t.assignment_id = l_assignment_id
and t.change_date > l_deleted_proposal_date
);
select pet.element_type_id,
piv.input_value_id,
ppb.pay_annualization_factor,
ppb.pay_basis
from
pay_element_types_f pet,
pay_input_values_f piv,
per_pay_bases ppb,
per_all_assignments_f asg
where
pet.element_type_id = piv.element_type_id
and p_change_date BETWEEN pet.effective_start_date
and pet.effective_end_date
and piv.input_value_id = ppb.input_value_id
and p_change_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
and ppb.pay_basis_id = asg.pay_basis_id
and asg.assignment_id = p_assignment_id
and p_change_date BETWEEN asg.effective_start_date
AND asg.effective_end_date;
select min(effective_start_date)
from pay_element_entries_f
where element_entry_id=p_element_entry_id;
select max(effective_end_date)
from pay_element_entries_f
where element_entry_id=p_element_entry_id;
select 1
from pay_element_entries_f
where effective_start_date=p_change_date
and element_entry_id=p_element_entry_id;
select piv.input_value_id
from pay_input_values_f piv
, hr_lookups hrl
where piv.element_type_id=p_element_type_id
and piv.name=hrl.meaning
and p_change_date between
piv.effective_start_date
and piv.effective_end_date
and hrl.lookup_type='NAME_TRANSLATIONS'
and hrl.lookup_code='PAYROLL_VALUE'
and p_change_date between
nvl(hrl.start_date_active,p_change_date)
and nvl(hrl.end_date_active,p_change_date);
select effective_end_date
from pay_element_entries_f
where effective_start_date=p_change_date
and element_entry_id=p_element_entry_id;
select min(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_change_date;
select element_entry_id,effective_start_date,effective_end_date
from pay_element_entries_f
where effective_end_date between p_change_date and l_date_to-1
and element_entry_id = p_element_entry_id;
hr_entry_api.insert_element_entry
(p_effective_start_date =>l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_entry_id => p_element_entry_id
,p_assignment_id => p_assignment_id
,p_element_link_id => l_element_link_id
,p_creator_type => 'SP'
,p_entry_type => 'E'
,p_creator_id => p_pay_proposal_id
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_entry_value
,p_input_value_id2 => l_input_value_id2
,p_entry_value2 => l_entry_value2
);
hr_entry_api.update_element_entry
(p_dt_update_mode => get_dt_mode(p_change_date,p_element_entry_id)
,p_session_date => p_change_date
,p_check_for_update => 'Y'
,p_element_entry_id => p_element_entry_id
,p_creator_type => 'SP'
,p_creator_id => p_pay_proposal_id
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_entry_value
,p_input_value_id2 => l_input_value_id2
,p_entry_value2 => l_entry_value2
);
UPDATE per_pay_proposals
SET date_to = l_last_date
WHERE pay_proposal_id = p_pay_proposal_id;
hr_entry_api.delete_element_entry
('DELETE_NEXT_CHANGE',
i.effective_end_date,
p_element_entry_id);
hr_entry_api.update_element_entry
(p_dt_update_mode => get_dt_mode(l_date_to+1,p_element_entry_id)
,p_session_date => l_date_to+1
,p_check_for_update => 'Y'
,p_element_entry_id => p_element_entry_id
,p_creator_type => 'SP'
,p_creator_id => p_pay_proposal_id
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => 0
,p_input_value_id2 => l_input_value_id2
,p_entry_value2 => 0
);
Procedure insert_salary_proposal(
p_pay_proposal_id out nocopy number,
p_assignment_id in number,
p_business_group_id in number,
p_change_date in date, -- Bug 918219
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_date_to in date,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number out nocopy number,
p_multiple_components in varchar2, -- 918219
p_approved in varchar2, -- 918219
p_validate in boolean,
p_element_entry_id in out nocopy number,
p_inv_next_sal_date_warning out nocopy boolean,
p_proposed_salary_warning out nocopy boolean,
p_approved_warning out nocopy boolean,
p_payroll_warning out nocopy boolean ) is
--
-- changed by schowdhu for bug #7693247 16-jan-2009
-- included p_change_date as input param
Cursor next_change_date(p_change_date DATE)
IS
select min(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_change_date
and approved = p_approved;
l_proc varchar2(72) := g_package||'insert_salary_proposal';
savepoint insert_salary_proposal;
hr_maintain_proposal_bk1.insert_salary_proposal_b
(
p_assignment_id => p_assignment_id,
p_business_group_id => p_business_group_id,
p_change_date => l_change_date,
p_comments => p_comments,
p_next_sal_review_date => l_next_sal_review_date,
p_proposal_reason => p_proposal_reason,
p_proposed_salary_n => p_proposed_salary_n,
p_forced_ranking => p_forced_ranking,
p_date_to => l_date_to,
p_performance_review_id => p_performance_review_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_multiple_components => p_multiple_components,
p_approved => p_approved,
p_element_entry_id => p_element_entry_id
);
(p_module_name => 'INSERT_SALARY_PROPOSAL'
,p_hook_type => 'BP'
);
update_last_change_date(p_assignment_id, p_change_date);
hr_maintain_proposal_bk1.insert_salary_proposal_a
(
p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => p_assignment_id,
p_business_group_id => p_business_group_id,
p_change_date => l_change_date,
p_comments => p_comments,
p_next_sal_review_date => l_next_sal_review_date,
p_proposal_reason => p_proposal_reason,
p_proposed_salary_n => p_proposed_salary_n,
p_forced_ranking => p_forced_ranking,
p_date_to => l_date_to,
p_performance_review_id => p_performance_review_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_object_version_number => l_object_version_number,
p_multiple_components => p_multiple_components,
p_approved => p_approved,
p_element_entry_id => l_element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
(p_module_name => 'INSERT_SALARY_PROPOSAL'
,p_hook_type => 'AP'
);
ROLLBACK TO insert_salary_proposal;
ROLLBACK TO insert_salary_proposal;
end insert_salary_proposal;
Procedure insert_salary_proposal(
p_pay_proposal_id out nocopy number,
p_assignment_id in number,
p_business_group_id in number,
p_change_date in date, -- Bug 918219
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number out nocopy number,
p_multiple_components in varchar2, -- 918219
p_approved in varchar2, -- 918219
p_validate in boolean,
p_element_entry_id in out nocopy number,
p_inv_next_sal_date_warning out nocopy boolean,
p_proposed_salary_warning out nocopy boolean,
p_approved_warning out nocopy boolean,
p_payroll_warning out nocopy boolean ) is
--
--
begin
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => p_pay_proposal_id
,p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_change_date => p_change_date
,p_comments => p_comments
,p_next_sal_review_date => p_next_sal_review_date
,p_proposal_reason => p_proposal_reason
,p_proposed_salary_n => p_proposed_salary_n
,p_forced_ranking => p_forced_ranking
,p_date_to => null
,p_performance_review_id => p_performance_review_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_object_version_number => p_object_version_number
,p_multiple_components => p_multiple_components
,p_approved => p_approved
,p_validate => p_validate
,p_element_entry_id => p_element_entry_id
,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
,p_proposed_salary_warning => p_proposed_salary_warning
,p_approved_warning => p_approved_warning
,p_payroll_warning => p_payroll_warning
);
end insert_salary_proposal;
Procedure update_salary_proposal(
p_pay_proposal_id in number,
p_change_date in date,
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_date_to in date,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number in out nocopy number,
p_multiple_components in varchar2,
p_approved in varchar2,
p_validate in boolean,
p_inv_next_sal_date_warning out nocopy boolean,
p_proposed_salary_warning out nocopy boolean,
p_approved_warning out nocopy boolean,
p_payroll_warning out nocopy boolean) is
--
l_proc varchar2(72) := g_package||'update_salary_proposal';
select assignment_id
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
select pee.element_entry_id
, pyp.assignment_id
from pay_element_entries_f pee
, per_pay_proposals pyp
where pyp.pay_proposal_id=p_pay_proposal_id
and pee.assignment_id=pyp.assignment_id
and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
pee.effective_start_date and pee.effective_end_date
and pee.creator_type='SP';
select min(change_date)
from per_pay_proposals pro
where assignment_id = l_assignment_id
and change_date > p_change_date
and approved = p_approved;
savepoint update_salary_proposal;
hr_maintain_proposal_bk2.update_salary_proposal_b
(
p_pay_proposal_id => p_pay_proposal_id,
p_change_date => l_change_date,
p_comments => p_comments,
p_next_sal_review_date => l_next_sal_review_date,
p_proposal_reason => p_proposal_reason,
p_proposed_salary_n => p_proposed_salary_n,
p_forced_ranking => p_forced_ranking,
p_date_to => l_date_to,
p_performance_review_id => p_performance_review_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_object_version_number => p_object_version_number,
p_multiple_components => p_multiple_components,
p_approved => p_approved
);
(p_module_name => 'UPDATE_SALARY_PROPOSAL'
,p_hook_type => 'BP'
);
select pyp.assignment_id
into l_assignment_id
from per_pay_proposals pyp
where pyp.pay_proposal_id=p_pay_proposal_id;
hr_maintain_proposal_bk2.update_salary_proposal_a
(
p_pay_proposal_id => p_pay_proposal_id,
p_change_date => l_change_date,
p_comments => p_comments,
p_next_sal_review_date => l_next_sal_review_date,
p_proposal_reason => p_proposal_reason,
p_proposed_salary_n => p_proposed_salary_n,
p_forced_ranking => p_forced_ranking,
p_date_to => l_date_to,
p_performance_review_id => p_performance_review_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_object_version_number => l_object_version_number,
p_multiple_components => p_multiple_components,
p_approved => p_approved,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
(p_module_name => 'UPDATE_SALARY_PROPOSAL'
,p_hook_type => 'AP'
);
ROLLBACK TO update_salary_proposal;
ROLLBACK TO update_salary_proposal;
end update_salary_proposal;
Procedure update_salary_proposal(
p_pay_proposal_id in number,
p_change_date in date,
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number in out nocopy number,
p_multiple_components in varchar2,
p_approved in varchar2,
p_validate in boolean,
p_inv_next_sal_date_warning out nocopy boolean,
p_proposed_salary_warning out nocopy boolean,
p_approved_warning out nocopy boolean,
p_payroll_warning out nocopy boolean) is
--
--
Cursor csr_date_to
IS
select date_to
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
hr_maintain_proposal_api.update_salary_proposal
(p_pay_proposal_id => p_pay_proposal_id
,p_change_date => p_change_date
,p_comments => p_comments
,p_next_sal_review_date => p_next_sal_review_date
,p_proposal_reason => p_proposal_reason
,p_proposed_salary_n => p_proposed_salary_n
,p_forced_ranking => p_forced_ranking
,p_date_to => l_date_to
,p_performance_review_id => p_performance_review_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_object_version_number => p_object_version_number
,p_multiple_components => p_multiple_components
,p_approved => p_approved
,p_validate => p_validate
,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
,p_proposed_salary_warning => p_proposed_salary_warning
,p_approved_warning => p_approved_warning
,p_payroll_warning => p_payroll_warning
);
end update_salary_proposal;
select pee.element_entry_id
from pay_element_entries_f pee
where pee.assignment_id=l_assignment_id
and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
pee.effective_start_date and pee.effective_end_date
and pee.creator_type='SP';
select date_to
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
update_last_change_date(l_assignment_id, l_change_date);
/* procedure to delete a complete salary proposal, including it's components
*/
Procedure delete_salary_proposal(p_pay_proposal_id in number
,p_business_group_id in number
,p_object_version_number in number
,p_validate in boolean
,p_salary_warning out nocopy boolean) is
--
l_salary_warning boolean;
l_proc varchar2(72) := g_package||'delete_salary_proposal';
v_delete_next_change boolean;
select ppc.component_id
, ppc.object_version_number
from per_pay_proposal_components ppc
where ppc.pay_proposal_id=p_pay_proposal_id
and ppc.business_group_id=p_business_group_id;
select pee.element_entry_id
, pro.change_date
from per_pay_proposals_v2 pro
, pay_element_entries_f pee
where pro.pay_proposal_id=p_pay_proposal_id
and pro.assignment_id=pee.assignment_id
and pee.creator_type='SP'
and pro.change_date between pee.effective_start_date
and pee.effective_end_date;
select count(*)
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and effective_start_date > p_change_date
and creator_id <> p_pay_proposal_id; --vkodedal 01-aug-2010 10062799
select approved, last_change_date, assignment_id
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
savepoint delete_salary_proposal;
hr_maintain_proposal_bk4.delete_salary_proposal_b
(
p_pay_proposal_id => p_pay_proposal_id
,p_business_group_id => p_business_group_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_SALARY_PROPOSAL'
,p_hook_type => 'BP'
);
select 'Y'
into v_zap
from sys.dual
where exists
(select 1
from sys.dual
where l_change_date =
(select min(effective_start_date)
from pay_element_entries_f
where element_entry_id = l_element_entry_id));
v_delete_next_change := TRUE;
update_last_change_date(l_assignment_id, l_last_change_date);
hr_utility.set_message(800,'PER_SAL_FIRST_PROPOSAL_DELETE');
hr_entry_api.delete_element_entry
('ZAP',
l_change_date,
l_element_entry_id);
elsif (v_delete_next_change = TRUE)
then
hr_utility.set_location(l_proc,35);
hr_entry_api.delete_element_entry
('DELETE_NEXT_CHANGE',
l_change_date - 1,
l_element_entry_id);
hr_maintain_proposal_bk4.delete_salary_proposal_a
(
p_pay_proposal_id => p_pay_proposal_id
,p_business_group_id => p_business_group_id
,p_object_version_number => p_object_version_number
,p_salary_warning => l_salary_warning
);
(p_module_name => 'DELETE_SALARY_PROPOSAL'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_salary_proposal;
ROLLBACK TO delete_salary_proposal;
end delete_salary_proposal;
procedure insert_proposal_component(
p_component_id out nocopy number,
p_pay_proposal_id in number,
p_business_group_id in number,
p_approved in varchar2,
p_component_reason in varchar2,
p_change_amount_n in number,
p_change_percentage in number,
p_comments in varchar2,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_validation_strength in varchar2,
p_object_version_number out nocopy number,
p_validate in boolean
) is
--
l_proc varchar2(72) := g_package||'insert_proposal_component';
savepoint insert_proposal_components;
hr_maintain_proposal_bk5.insert_proposal_component_b
(
p_pay_proposal_id => p_pay_proposal_id,
p_business_group_id => p_business_group_id,
p_approved => p_approved,
p_component_reason => p_component_reason,
p_change_amount_n => p_change_amount_n,
p_change_percentage => p_change_percentage,
p_comments => p_comments,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_validation_strength => p_validation_strength
);
(p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
,p_hook_type => 'BP'
);
hr_maintain_proposal_bk5.insert_proposal_component_a
(
p_component_id => l_component_id,
p_pay_proposal_id => p_pay_proposal_id,
p_business_group_id => p_business_group_id,
p_approved => p_approved,
p_component_reason => p_component_reason,
p_change_amount_n => p_change_amount_n,
p_change_percentage => p_change_percentage,
p_comments => p_comments,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_validation_strength => p_validation_strength,
p_object_version_number => l_object_version_number
);
(p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
,p_hook_type => 'AP'
);
ROLLBACK TO insert_proposal_components;
ROLLBACK TO insert_proposal_components;
end insert_proposal_component;
procedure update_proposal_component(
p_component_id in number,
p_approved in varchar2,
p_component_reason in varchar2,
p_change_amount_n in number,
p_change_percentage in number,
p_comments in varchar2,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_validation_strength in varchar2,
p_object_version_number in out nocopy number,
p_validate in boolean
) is
--
l_proc varchar2(72) := g_package||'update_proposal_component';
savepoint update_proposal_components;
hr_maintain_proposal_bk6.update_proposal_component_b
(
p_component_id => p_component_id,
p_approved => p_approved,
p_component_reason => p_component_reason,
p_change_amount_n => p_change_amount_n,
p_change_percentage => p_change_percentage,
p_comments => p_comments,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_validation_strength => p_validation_strength,
p_object_version_number => p_object_version_number
);
(p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
,p_hook_type => 'BP'
);
hr_maintain_proposal_bk6.update_proposal_component_a
(
p_component_id => p_component_id,
p_approved => p_approved,
p_component_reason => p_component_reason,
p_change_amount_n => p_change_amount_n,
p_change_percentage => p_change_percentage,
p_comments => p_comments,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_validation_strength => p_validation_strength,
p_object_version_number => l_object_version_number
);
(p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
,p_hook_type => 'AP'
);
ROLLBACK TO update_proposal_components;
ROLLBACK TO update_proposal_components;
end update_proposal_component;
Procedure delete_proposal_component(
p_component_id in number,
p_validation_strength in varchar2,
p_object_version_number in number,
p_validate in boolean) is
--
l_proc varchar2(72) := g_package||'delete_proposal_component';
savepoint delete_proposal_components;
hr_maintain_proposal_bk7.delete_proposal_component_b
(
p_component_id => p_component_id,
p_validation_strength => p_validation_strength,
p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
,p_hook_type => 'BP'
);
hr_maintain_proposal_bk7.delete_proposal_component_a
(
p_component_id => p_component_id,
p_validation_strength => p_validation_strength,
p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_proposal_components;
ROLLBACK TO delete_proposal_components;
end delete_proposal_component;
select 1 from per_pay_proposals
where assignment_id=p_assignment_id;
select asg.assignment_type
from per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and p_change_date between asg.effective_start_date
and asg.effective_end_date;
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pyp_rec.pay_proposal_id
,p_assignment_id => l_pyp_rec.assignment_id
,p_business_group_id => l_pyp_rec.business_group_id
,p_change_date => l_pyp_rec.change_date
,p_comments => l_pyp_rec.comments
,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
,p_proposal_reason => l_pyp_rec.proposal_reason
,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
,p_forced_ranking => l_pyp_rec.forced_ranking
,p_date_to => l_pyp_rec.date_to
,p_performance_review_id => l_pyp_rec.performance_review_id
,p_attribute_category => l_pyp_rec.attribute_category
,p_attribute1 => l_pyp_rec.attribute1
,p_attribute2 => l_pyp_rec.attribute2
,p_attribute3 => l_pyp_rec.attribute3
,p_attribute4 => l_pyp_rec.attribute4
,p_attribute5 => l_pyp_rec.attribute5
,p_attribute6 => l_pyp_rec.attribute6
,p_attribute7 => l_pyp_rec.attribute7
,p_attribute8 => l_pyp_rec.attribute8
,p_attribute9 => l_pyp_rec.attribute9
,p_attribute10 => l_pyp_rec.attribute10
,p_attribute11 => l_pyp_rec.attribute11
,p_attribute12 => l_pyp_rec.attribute12
,p_attribute13 => l_pyp_rec.attribute13
,p_attribute14 => l_pyp_rec.attribute14
,p_attribute15 => l_pyp_rec.attribute15
,p_attribute16 => l_pyp_rec.attribute16
,p_attribute17 => l_pyp_rec.attribute17
,p_attribute18 => l_pyp_rec.attribute18
,p_attribute19 => l_pyp_rec.attribute19
,p_attribute20 => l_pyp_rec.attribute20
,p_object_version_number => l_pyp_rec.object_version_number
,p_multiple_components => nvl(l_pyp_rec.multiple_components,'N')
,p_approved => nvl(l_pyp_rec.approved,'N')
,p_validate => FALSE
,p_element_entry_id => l_element_entry_id
,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
,p_proposed_salary_warning => l_proposed_salary_warning
,p_approved_warning => l_approved_warning
,p_payroll_warning => l_payroll_warning
);
hr_maintain_proposal_api.update_salary_proposal
(p_pay_proposal_id => l_pyp_rec.pay_proposal_id
,p_change_date => l_pyp_rec.change_date
,p_comments => l_pyp_rec.comments
,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
,p_proposal_reason => l_pyp_rec.proposal_reason
,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
,p_forced_ranking => l_pyp_rec.forced_ranking
,p_date_to => l_pyp_rec.date_to
,p_performance_review_id => l_pyp_rec.performance_review_id
,p_attribute_category => l_pyp_rec.attribute_category
,p_attribute1 => l_pyp_rec.attribute1
,p_attribute2 => l_pyp_rec.attribute2
,p_attribute3 => l_pyp_rec.attribute3
,p_attribute4 => l_pyp_rec.attribute4
,p_attribute5 => l_pyp_rec.attribute5
,p_attribute6 => l_pyp_rec.attribute6
,p_attribute7 => l_pyp_rec.attribute7
,p_attribute8 => l_pyp_rec.attribute8
,p_attribute9 => l_pyp_rec.attribute9
,p_attribute10 => l_pyp_rec.attribute10
,p_attribute11 => l_pyp_rec.attribute11
,p_attribute12 => l_pyp_rec.attribute12
,p_attribute13 => l_pyp_rec.attribute13
,p_attribute14 => l_pyp_rec.attribute14
,p_attribute15 => l_pyp_rec.attribute15
,p_attribute16 => l_pyp_rec.attribute16
,p_attribute17 => l_pyp_rec.attribute17
,p_attribute18 => l_pyp_rec.attribute18
,p_attribute19 => l_pyp_rec.attribute19
,p_attribute20 => l_pyp_rec.attribute20
,p_object_version_number => l_pyp_rec.object_version_number
,p_multiple_components => l_pyp_rec.multiple_components
,p_approved => l_pyp_rec.approved
,p_validate => FALSE
,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
,p_proposed_salary_warning => l_proposed_salary_warning
,p_approved_warning => l_approved_warning
,p_payroll_warning => l_payroll_warning
);
select date_to
from per_pay_proposals
where pay_proposal_id = p_pay_proposal_id;
/* Procedure to delete salary proposals and components
of an assignment before a given date.
Parameters:
p_assignment_id Assignment Id
p_date Date
*/
Procedure delete_salary_history( p_assignment_id in number
,p_date in date) is
--
l_salary_warning boolean;
l_proc varchar2(72) := g_package||'delete_salary_history';
select pay_proposal_id, business_group_id, object_version_number
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date <
(select max(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date <= p_date
);
select ppc.component_id
, ppc.object_version_number
from per_pay_proposal_components ppc
where ppc.pay_proposal_id=p_pay_proposal_id
and ppc.business_group_id=p_business_group_id;
savepoint delete_salary_history;
update BEN_CWB_PERSON_RATES
set PAY_PROPOSAL_ID = null
where PAY_PROPOSAL_ID = r_del_sp.pay_proposal_id;
delete per_pay_proposals
where pay_proposal_id = r_del_sp.pay_proposal_id;
ROLLBACK TO delete_salary_proposal;