[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_ame_attribute
(p_validate in boolean default false
,p_language_code in varchar2 default hr_api.userenv_lang
,p_attribute_id in number
,p_description in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_atr_object_version_number number;
l_proc varchar2(72) := g_package||'update_ame_attribute';
savepoint update_ame_attribute;
ame_attribute_bk3.update_ame_attribute_b
(p_attribute_id => p_attribute_id
,p_description => p_description
,p_object_version_number => p_object_version_number
);
(p_module_name => 'update_ame_attribute'
,p_hook_type => 'BP'
);
,p_datetrack_mode => 'UPDATE'
,p_attribute_id => p_attribute_id
,p_object_version_number => l_atr_object_version_number
,p_description => p_description
,p_start_date => l_atr_start_date
,p_end_date => l_atr_end_date
);
ame_attribute_bk3.update_ame_attribute_a
(p_attribute_id => p_attribute_id
,p_description => p_description
,p_object_version_number => l_atr_object_version_number
,p_start_date => l_atr_start_date
,p_end_date => l_atr_end_date
);
(p_module_name => 'update_ame_attribute'
,p_hook_type => 'AP'
);
rollback to update_ame_attribute;
rollback to update_ame_attribute;
end update_ame_attribute;
procedure update_ame_attribute_usage
(p_validate in boolean default false
,p_attribute_id in number
,p_application_id in number
,p_is_static in varchar2 default ame_util.booleanTrue
,p_query_string in varchar2 default null
,p_value_set_id in number default null
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_atu_object_version_number number;
l_proc varchar2(72) := g_package||'update_ame_attribute_usage';
savepoint update_ame_attribute_usage;
ame_attribute_bk4.update_ame_attribute_usage_b
(p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_is_static => p_is_static
,p_query_string => p_query_string
,p_value_set_id => p_value_set_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'update_ame_attribute_usage'
,p_hook_type => 'BP'
);
,p_datetrack_mode => 'UPDATE'
,p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_object_version_number=> l_atu_object_version_number
,p_query_string => p_query_string
,p_is_static => p_is_static
,p_value_set_id => p_value_set_id
,p_start_date => l_atu_start_date
,p_end_date => l_atu_end_date
);
ame_attribute_bk4.update_ame_attribute_usage_a
(p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_is_static => p_is_static
,p_query_string => p_query_string
,p_value_set_id => p_value_set_id
,p_object_version_number => l_atu_object_version_number
,p_start_date => l_atu_start_date
,p_end_date => l_atu_end_date
);
(p_module_name => 'update_ame_attribute_usage'
,p_hook_type => 'AP'
);
rollback to update_ame_attribute_usage;
rollback to update_ame_attribute_usage;
end update_ame_attribute_usage;
procedure delete_ame_attribute_usage
(p_validate in boolean default false
,p_attribute_id in number
,p_application_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_atr_object_version_number number;
l_proc varchar2(72) := g_package||'delete_ame_attribute_usage';
select condition_id, object_version_number
from ame_conditions
where attribute_id = p_attribute_id
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond,sysdate);
select null
from ame_mandatory_attributes
where attribute_id = p_attribute_id and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
savepoint delete_ame_attribute_usage;
ame_attribute_bk5.delete_ame_attribute_usage_b
(p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_ame_attribute_usage'
,p_hook_type => 'BP'
);
,p_datetrack_mode => 'DELETE'
,p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_object_version_number=> l_atu_object_version_number
,p_start_date => l_atu_start_date
,p_end_date => l_atu_end_date
);
select count(*)
into l_usage_count
from ame_attribute_usages
where attribute_id = p_attribute_id
and l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,sysdate);
/* if this is a REQUIRED attribute, do not delete its conditions and the attribute itself
otherwise, delete all the conditions and the attribute itself, as no other usage exists
*/
open c_Sel2;
/*Its not a required attribute, so select all the conditions to delete */
for con_rec in c_Sel1
loop
ame_condition_api.delete_ame_condition
(p_condition_id => con_rec.condition_id
,p_object_version_number => con_rec.object_version_number
,p_start_date => l_con_start_date
,p_end_date => l_con_end_date
);
select object_version_number
into l_atr_object_version_number
from ame_attributes
where attribute_id = p_attribute_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,sysdate);
,p_datetrack_mode => 'DELETE'
,p_attribute_id => p_attribute_id
,p_object_version_number=> l_atr_object_version_number
,p_start_date => l_atr_start_date
,p_end_date => l_atr_end_date
);
ame_attribute_bk5.delete_ame_attribute_usage_a
(p_attribute_id => p_attribute_id
,p_application_id => p_application_id
,p_object_version_number => l_atu_object_version_number
,p_start_date => l_atu_start_date
,p_end_date => l_atu_end_date
);
(p_module_name => 'delete_ame_attribute_usage'
,p_hook_type => 'AP'
);
rollback to delete_ame_attribute_usage;
rollback to delete_ame_attribute_usage;
end delete_ame_attribute_usage;
select rule_id
from ame_rule_usages
where
ame_rule_usages.item_id = applicationIdIn and
((sysdate between ame_rule_usages.start_date and
nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_rule_usages.start_date and
ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
ame_rule_usages.start_date + ame_util.oneSecond)));
select count(*)
into tempCount
from
ame_conditions,
ame_condition_usages
where
ame_conditions.attribute_id = attributeIdIn and
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_condition_usages.rule_id = tempRule.rule_id and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
((sysdate between ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_condition_usages.start_date and
ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
ame_condition_usages.start_date + ame_util.oneSecond)));
select count(*)
into tempCount
from
ame_mandatory_attributes,
ame_actions,
ame_action_usages
where
ame_mandatory_attributes.attribute_id = attributeIdIn and
ame_mandatory_attributes.action_type_id = ame_actions.action_type_id and
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = tempRule.rule_id and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
((sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_action_usages.start_date and
ame_action_usages.start_date < nvl(ame_action_usages.end_date,
ame_action_usages.start_date + ame_util.oneSecond)));
procedure updateUseCount(p_attribute_id in integer
,p_application_id in integer
,p_atu_object_version_number in integer) as
useCount integer;
end updateUseCount;