[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
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_pay_proposal_id IS NULL OR pay_proposal_id <> p_pay_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);
l_comp_update boolean := false;
l_prop_update boolean := false;
select sum(change_amount_n)
from per_pay_proposal_components
where pay_proposal_id = c_pay_proposal_id;
select pet.element_type_id,
piv.input_value_id
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 l_trunc_date BETWEEN pet.effective_start_date
and pet.effective_end_date
and piv.input_value_id = ppb.input_value_id
and l_trunc_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 l_trunc_date BETWEEN asg.effective_start_date
AND asg.effective_end_date;
select proposed_salary_n
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date < l_trunc_date
order by change_date desc;
select change_date,
proposed_salary_n,
multiple_components,
approved,
proposal_reason,
object_version_number
from per_pay_proposals
where assignment_id = p_assignment_id
and pay_proposal_id = p_pay_proposal_id
and business_group_id = p_business_group_id;
select component_reason,
change_amount_n,
change_percentage,
approved,
object_version_number
from per_pay_proposal_components
where component_id = c_component_id
and business_group_id = p_business_group_id;
select min(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_change_date;
-- This means that the record need to be inserted
-- Note that the proposal gets approved automatically,
-- because it is the first proposal.
--
hr_utility.set_location(l_proc, 30);
-- insert an unapproved single component salary proposal
-- record in per_pay_proposal_table using the row_handler
--
--vkodedal 05-Oct-2007 ER to satisfy satutory requirement
--Retain auto approve first proposal functionality if profile is null or set to Yes
l_approved :='N';
-- Now we insert an element entry for this proposal
-- by calling the insert_element_entry_api.
--
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 => l_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 => l_pay_proposal_id
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => p_proposed_salary
);
-- check that the change_date has not been updated
-- This extra check is done here since the update api has
-- no change_date parameter. And since the usermay change
-- the change date we need to do this validation before
-- calling the update routine
--
if (l_trunc_date <> l_change_date) then
hr_utility.set_location(l_proc,60);
-- update the salary proposal
--
per_pyp_upd.upd
(p_pay_proposal_id => l_pay_proposal_id
,p_proposal_reason => p_proposal_reason
,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
,p_proposed_salary_n => p_proposed_salary
,p_forced_ranking => p_forced_ranking
,p_validate => false
,p_object_version_number => l_pyp_object_version_number
,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_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
);
-- insert an unapproved single component salary proposal record in
-- per_pay_proposals table using the row_handler
--
end_date_salary_proposal(p_assignment_id => p_assignment_id
,p_date_to => p_change_date-1);
update_last_change_date(p_assignment_id, p_change_date);
l_comp_update := false;
-- We need to update the record.Set the update flag to true.
--
--
l_component_sum
:= l_component_sum + l_change_amount;
l_comp_update := true;
-- changes has taken place, hence we need to update or delete
-- the record as appropriate.
--
if (l_change_amount_in IS NULL) then
if(l_change_percentage_in IS NOT NULL AND
l_change_percentage_in <> 0) then
--
-- calculate the component_sum from the change_percentage
-- and update the record.
--
hr_utility.set_location(l_proc,145);
-- Set the l_comp_update and l_prop_update to true.
-- update the record and set the approved flag to Y
--
l_prop_update := true;
l_comp_update := true;
-- be deleted
--
per_ppc_del.del
(p_component_id => l_component_id_in
,p_object_version_number => l_object_version_number_in
,p_validate => false
);
l_prop_update := true;
-- first sum up the component and then update the component
--
l_component_sum
:= l_component_sum + l_change_amount_in;
-- Set the comp_update and prop_update to true
--
l_comp_update := true;
l_prop_update := true;
-- Now update the component if the l_comp_update is true
--
if (l_comp_update) then
per_ppc_upd.upd
(p_component_id => l_component_id_in
,p_component_reason => l_component_reason_in
,p_change_amount_n => l_change_amount_in
,p_change_percentage => l_change_percentage_in
,p_approved => l_approved_in
,p_object_version_number => l_object_version_number_in
,p_validate => false
);
-- component_id is null. Therefore, we are inserting a new
-- record in the db.
--
if (l_change_amount_in IS NOT NULL OR
(l_change_percentage_in IS NOT NULL AND
l_change_percentage_in <> 0)) then
l_comp_update := true;
l_prop_update := true;
if (l_prop_update) then
per_pyp_upd.upd
(p_pay_proposal_id => l_pay_proposal_id
,p_proposal_reason => p_proposal_reason
,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
,p_proposed_salary_n => l_proposed_salary
,p_forced_ranking => p_forced_ranking
,p_object_version_number => l_pyp_object_version_number
,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_validate => false
);