[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_assignment_rate_value
(p_validate in boolean default false
,p_grade_rule_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_currency_code in varchar2 default hr_api.g_varchar2
,p_value in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_return_status out nocopy varchar2
) is
--
--
-- Variables for IN/OUT parameters
l_object_version_number number;
l_proc varchar2(72) := g_package ||'update_assignment_rate_value';
savepoint update_assignment_rate_value;
hr_rate_values_api.update_assignment_rate_value
(p_validate => p_validate
,p_grade_rule_id => p_grade_rule_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_currency_code => p_currency_code
,p_value => p_value
,p_object_version_number => p_object_version_number
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
rollback to update_assignment_rate_value;
rollback to update_assignment_rate_value;
end update_assignment_rate_value;
PROCEDURE update_rate_value
(p_validate in number default hr_api.g_false_num
,p_grade_rule_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_currency_code in varchar2 default hr_api.g_varchar2
,p_maximum in varchar2 default hr_api.g_varchar2
,p_mid_value in varchar2 default hr_api.g_varchar2
,p_minimum in varchar2 default hr_api.g_varchar2
,p_sequence in number default hr_api.g_number
,p_value in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_return_status out nocopy varchar2
) is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := g_package ||'update_rate_value';
savepoint update_rate_value_swi;
hr_rate_values_api.update_rate_value
(p_validate => l_validate
,p_grade_rule_id => p_grade_rule_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_currency_code => p_currency_code
,p_maximum => p_maximum
,p_mid_value => p_mid_value
,p_minimum => p_minimum
,p_sequence => p_sequence
,p_value => p_value
,p_object_version_number => p_object_version_number
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
rollback to update_rate_value_swi;
rollback to update_rate_value_swi;
end update_rate_value;
PROCEDURE delete_rate_value
(p_validate in boolean default false
,p_grade_rule_id in number
,p_datetrack_mode in varchar2
,p_effective_date in date
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_return_status out nocopy varchar2
) is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := g_package ||'delete_rate_value';
savepoint delete_rate_value_swi;
hr_rate_values_api.delete_rate_value
(p_validate => l_validate
,p_grade_rule_id => p_grade_rule_id
,p_datetrack_mode => p_datetrack_mode
,p_effective_date => p_effective_date
,p_object_version_number => p_object_version_number
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
rollback to delete_rate_value_swi;
rollback to delete_rate_value_swi;
end delete_rate_value;
PROCEDURE delete_rate_value
(p_validate in number default hr_api.g_false_num
,p_grade_rule_id in number
,p_datetrack_mode in varchar2
,p_effective_date in date
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_return_status out nocopy varchar2
) is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := g_package ||'delete_rate_value';
savepoint delete_rate_value_swi;
hr_rate_values_api.delete_rate_value
(p_validate => l_validate
,p_grade_rule_id => p_grade_rule_id
,p_datetrack_mode => p_datetrack_mode
,p_effective_date => p_effective_date
,p_object_version_number => p_object_version_number
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
rollback to delete_rate_value_swi;
rollback to delete_rate_value_swi;
end delete_rate_value;
select name, number_value from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
and name = 'P_PO_LINE_ID';
select currency_code,
value,
effective_end_date
from pay_grade_rules_f pgr
where pgr.grade_rule_id = nvl(grade_id, -1)
and pgr.rate_type = 'A'
and pgr.business_group_id = business_gp_id
and pgr.grade_or_spinal_point_id = asg_id
and effective_date between pgr.effective_start_date
and pgr.effective_end_date;
l_record_status := g_insert_delete;
l_record_status := g_insert_only;
l_record_status := g_delete_only;
l_record_status := g_update_delete;
l_record_status := g_update_only;
delete from hr_api_transaction_values
where transaction_step_id = l_asg_rate_rec.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_step_id = l_asg_rate_rec.transaction_step_id;
hr_new_user_reg_ss.process_selected_transaction
(p_item_type => p_item_type
,p_item_key => p_item_key);
hr_new_user_reg_ss.process_selected_transaction
(p_item_type => p_item_type
,p_item_key => p_item_key);
SELECT oo.*,
rownum row_index
FROM (
SELECT a.varchar2_value rate_name,
b.number_value rate_id,
c.varchar2_value rate_basis_name,
d.varchar2_value currency_name,
e.varchar2_value value,
f.date_value effective_start_date,
decode(trunc(g.date_value), trunc(hr_api.g_eot), null, g.date_value) effective_end_date,
h.number_value object_version_number,
i.number_value grade_rule_id,
j.varchar2_value currency_code,
to_char(s.transaction_step_id) transaction_step_id
FROM hr_api_transaction_steps s,
hr_api_transaction_values a, hr_api_transaction_values b,
hr_api_transaction_values c, hr_api_transaction_values d,
hr_api_transaction_values e, hr_api_transaction_values f,
hr_api_transaction_values g, hr_api_transaction_values h,
hr_api_transaction_values i, hr_api_transaction_values j
WHERE s.item_type = p_item_type
AND s.item_key = p_item_key
AND s.api_name = g_package||'.PROCESS_API'
AND a.transaction_step_id = s.transaction_step_id
AND a.name = 'P_RATE_NAME'
AND b.transaction_step_id = s.transaction_step_id
AND b.name = 'P_RATE_ID'
AND c.transaction_step_id = s.transaction_step_id
AND c.name = 'P_RATE_BASIS_NAME'
AND d.transaction_step_id = s.transaction_step_id
AND d.name = 'P_CURRENCY_NAME'
AND e.transaction_step_id = s.transaction_step_id
AND e.name = 'P_VALUE'
AND f.transaction_step_id = s.transaction_step_id
AND f.name = 'P_EFFECTIVE_START_DATE'
AND g.transaction_step_id = s.transaction_step_id
AND g.name = 'P_EFFECTIVE_END_DATE'
AND h.transaction_step_id = s.transaction_step_id
AND h.name = 'P_OBJECT_VERSION_NUMBER'
AND i.transaction_step_id = s.transaction_step_id
AND i.name = 'P_GRADE_RULE_ID'
AND j.transaction_step_id = s.transaction_step_id
AND j.name = 'P_CURRENCY_CODE'
UNION
SELECT o.* from (
SELECT pgr.rate_name,
pgr.rate_id,
pgr.rate_basis_name,
pgr.currency_name,
pgr.value,
pgr.effective_start_date,
decode(trunc(pgr.effective_end_date), trunc(hr_api.g_eot), null, pgr.effective_end_date) effective_end_date,
pgr.object_version_number,
pgr.grade_rule_id,
pgr.currency_code,
NULL transaction_step_id
FROM PAY_GRADE_RULES_V pgr
WHERE pgr.rate_type = 'A'
AND pgr.assignment_id = to_number(p_assignment_id)
AND pgr.business_group_id = to_number(p_business_gp_id)
AND pgr.grade_rule_id NOT IN (SELECT nvl(a.number_value, -1)
FROM hr_api_transaction_steps s,
hr_api_transaction_values a
WHERE s.item_type = p_item_type
AND s.item_key = p_item_key
AND s.api_name = g_package||'.PROCESS_API'
AND a.transaction_step_id = s.transaction_step_id
AND a.name = 'P_GRADE_RULE_ID')
ORDER BY pgr.rate_name ) o
) oo
ORDER BY grade_rule_id desc;
g_asg_rate_table.delete;
select effective_start_date,
effective_end_date
from pay_grade_rules_v
where grade_rule_id = l_grade_rule_id;
l_rec_update_mode VARCHAR2(15) := 'UPDATE';
if (p_record_status = g_update_delete or
p_record_status = g_insert_delete or
p_record_status = g_delete_only)
then
hr_multi_message.enable_message_list;
if (p_record_status = g_update_delete or
p_record_status = g_insert_delete)
then
l_validate := false;
if (p_record_status = g_insert_only or
p_record_status = g_insert_delete)
then
create_assignment_rate_value
(p_validate => l_validate
,p_effective_date => p_asg_rate_rec.effective_start_date
,p_business_group_id => p_asg_rate_rec.business_group_id
,p_rate_id => p_asg_rate_rec.rate_id
,p_assignment_id => p_asg_rate_rec.assignment_id
,p_rate_type => 'A'
,p_currency_code => p_asg_rate_rec.currency_code
,p_value => p_asg_rate_rec.value
,p_grade_rule_id => l_grade_rule_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_return_status => l_return_status
);
elsif (p_record_status = g_update_delete or
p_record_status = g_update_only)
then
if (l_rec_start_date is not null and
trunc(l_rec_start_date)
= trunc(p_asg_rate_rec.effective_start_date))
then
l_rec_update_mode := 'CORRECTION';
update_assignment_rate_value
(p_validate => l_validate
,p_grade_rule_id => l_grade_rule_id
,p_effective_date => p_asg_rate_rec.effective_start_date
,p_datetrack_mode => l_rec_update_mode
,p_currency_code => p_asg_rate_rec.currency_code
,p_value => p_asg_rate_rec.value
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_return_status => l_return_status
);
if (p_record_status = g_update_delete or
p_record_status = g_insert_delete or
p_record_status = g_delete_only)
then
begin
savepoint record_delete_point;
delete_rate_value
(p_validate => l_validate
,p_grade_rule_id => l_grade_rule_id
,p_datetrack_mode => 'FUTURE_CHANGE'
,p_effective_date => p_asg_rate_rec.effective_start_date
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_return_status => l_return_status
);
delete_rate_value
(p_validate => l_validate
,p_grade_rule_id => l_grade_rule_id
,p_datetrack_mode => 'DELETE'
,p_effective_date => p_asg_rate_rec.effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_return_status => l_return_status
);
rollback to record_delete_point;
rollback to record_delete_point;
if (p_record_status = g_update_delete or
p_record_status = g_insert_delete)
then
if p_validate = true
then
rollback to record_enddate_enabled;
if (p_record_status = g_update_delete or
p_record_status = g_insert_delete)
then
rollback to record_enddate_enabled;
PROCEDURE delete_transaction_step
(p_transaction_step_id in VARCHAR2
) is
begin
delete from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id;
delete from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id;
end delete_transaction_step;