[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select condition_type
into l_condition_type
from ame_conditions
where ame_conditions.condition_id = p_condition_id
and p_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, p_effective_date);
select start_date
,end_date
,priority
from ame_rule_usages
where rule_id =p_rule_id
and item_id = p_application_id
and (p_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, p_effective_date)
or
(p_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)))
order by start_date desc;
select application_name
from ame_calling_apps
where application_id = applicationIdIn
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
select ame_actions.action_type_id
from ame_actions, ame_action_usages
where ame_action_usages.rule_id = p_rule_id
and ame_action_usages.action_id = ame_actions.action_id
and (p_effective_date between ame_action_usages.start_date
and nvl(ame_action_usages.end_date - ame_util.oneSecond, p_effective_date)
or
(p_effective_date < 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)))
and p_effective_date between ame_actions.start_date
and nvl(ame_actions.end_date - ame_util.oneSecond, p_effective_date) ;
select rule_type
into l_rule_type
from ame_rules
where rule_id =p_rule_id
and (p_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, p_effective_date )
or
(p_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select count(*)
into l_count
from ame_approver_type_usages
where approver_type_id not in (
select approver_type_id
from ame_approver_types
where orig_system in (ame_util.perOrigSystem
,ame_util.fndUserOrigSystem)
and sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate))
and action_type_id = actionTypeIds(i)
and sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into l_count
from ame_action_type_usages
where rule_type = ame_util.productionRuleType
and action_type_id = actionTypeIds(i)
and p_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, p_effective_date);
select min(start_date)
into p_rul_start_date
from ame_rule_usages
where rule_id = p_rule_id
and (sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate )
or
(sysdate < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select max(end_date)
into p_rul_end_date
from ame_rule_usages
where rule_id = p_rule_id
and (sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate )
or
(sysdate < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select ame_conditions.condition_id condition_id
,ame_conditions.condition_type condition_type
from ame_conditions
,ame_condition_usages
where ame_conditions.condition_id = ame_condition_usages.condition_id
and ame_condition_usages.rule_id = ruleIdIn
and (ame_conditions.start_date <= sysdate
and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
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)))
order by condition_type;
select ame_action_usages.action_id
from ame_action_usages
where rule_id = ruleIdIn
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)));
select rule_id
from ame_rules
where rule_type = typeIn
and (item_class_id is null or item_class_id = itemClassIdIn)
and ((sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate))
or
(sysdate < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
conditionIdList2.delete;
actionIdList2.delete;
select atyu.rule_type
from ame_action_type_usages atyu
,ame_actions act
where act.action_id = p_action_id
and act.action_type_id = atyu.action_type_id
and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate)
and sysdate between atyu.start_date and nvl(atyu.end_date-(1/86400),sysdate);
select condition_type
,attribute_id
from ame_conditions
where condition_id = p_condition_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date);
select attribute_id
,use_count
,start_date
,end_date
,object_version_number
from ame_attribute_usages
where attribute_id in (
select ame_conditions.attribute_id
from ame_conditions
,ame_condition_usages
where ame_condition_usages.rule_id = p_rule_id
and (l_effective_date between ame_condition_usages.start_date
and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
or
(l_effective_date < 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)))
and ame_condition_usages.condition_id = ame_conditions.condition_id
and l_effective_date between ame_conditions.start_date
and nvl(ame_conditions.end_date - ame_util.oneSecond, l_effective_date)
)
and application_id = p_application_id
and l_effective_date between ame_attribute_usages.start_date
and nvl(ame_attribute_usages.end_date - ame_util.oneSecond, l_effective_date);
select attribute_id, use_count, start_date, end_date, object_version_number
from ame_attribute_usages
where attribute_id = p_attribute_id
and application_id = p_application_id
and l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date);
select man.attribute_id
from ame_mandatory_attributes man
,ame_action_usages acu
,ame_actions act
where man.action_type_id = act.action_type_id
and acu.action_id = act.action_id
and acu.rule_id = p_rule_id
and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
);
select action_id
,start_date
,end_date
,object_version_number
from ame_action_usages
where rule_id = p_rule_id
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date)
or
(l_effective_date < start_date and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select condition_id
,start_date
,end_date
,object_version_number
from ame_condition_usages
where rule_id = p_rule_id
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date)
or
(l_effective_date < start_date and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select null
from ame_item_class_usages
where application_id =p_application_id
and item_class_id = p_item_class_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date);
select item_class_id
,rule_type
,start_date
,end_date
,object_version_number
into l_item_class_id
,l_rule_type
,l_rul_start_date
,l_rul_end_date
,l_rul_object_version_number
from ame_rules
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select name
into l_item_class_name
from ame_item_classes
where item_class_id = l_item_class_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date);
,p_datetrack_mode => hr_api.g_update
,p_action_id => tempActions.action_id
,p_effective_date => l_effective_date
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_condition_id => tempConditions.condition_id
,p_effective_date => l_effective_date
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_effective_date => l_effective_date
,p_object_version_number => l_rul_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
,p_application_id => p_application_id
,p_atu_object_version_number => l_atu_object_version_number);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
,p_application_id => p_application_id
,p_atu_object_version_number => l_atu_object_version_number);
select condition_type
,attribute_id
from ame_conditions
where condition_id = p_condition_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date);
select rule_type
,start_date
,end_date
,item_class_id
from ame_rules
where rule_id = p_rule_id
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date) or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select item_id
from ame_rule_usages
where rule_id = p_rule_id
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select use_count
,start_date
,end_date
,object_version_number
from ame_attribute_usages
where attribute_id = l_attribute_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date )
and application_id = p_application_id;
select application_name
from ame_calling_apps
where application_id = applicationIdIn
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
select distinct item_id
from ame_rule_usages
where rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select count(*)
into lm_count
from ame_rules rul
,ame_condition_usages cnu
,ame_conditions cnd
where rul.rule_id = p_rule_id
and cnu.rule_id = rul.rule_id
and cnd.condition_id = cnu.condition_id
and cnd.condition_type = 'post'
and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
);
ame_attribute_api.updateUseCount(p_attribute_id => l_attribute_id
,p_application_id => tempApplications.item_id
,p_atu_object_version_number => l_atu_object_version_number);
select aatu.action_type_id
,aatu.rule_type
from ame_action_type_usages aatu
,ame_actions aa
where aa.action_id = p_action_id
and l_effective_date between aa.start_date
and nvl(aa.end_date - ame_util.oneSecond, l_effective_date)
and aa.action_type_id = aatu.action_type_id
and l_effective_date between aatu.start_date
and nvl(aatu.end_date - ame_util.oneSecond, l_effective_date);
select rule_type, start_date, end_date, item_class_id
from ame_rules
where rule_id = p_rule_id
and ( l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select attribute_id
from ame_mandatory_attributes ama
where ama.attribute_id not in (select attribute_id
from ame_attribute_usages
where application_id = p_application_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
and l_effective_date between ama.start_date
and nvl(ama.end_date - ame_util.oneSecond, l_effective_date)
and action_type_id = p_action_type_id;
select man.attribute_id
from ame_mandatory_attributes man
,ame_action_usages acu
,ame_actions act
where man.action_type_id = act.action_type_id
and acu.action_id = act.action_id
and acu.action_id = actionIdIn
and acu.rule_id = ruleIdIn
and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
);
select item_id
from ame_rule_usages
where rule_id = ruleIdIn
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date)
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select application_id
,use_count
,start_date
,end_date
,object_version_number
from ame_attribute_usages
where attribute_id = attributeIdIn
and application_id = applicationIdIn
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date);
select distinct item_id
from ame_rule_usages
where rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select name
into l_aty_name
from ame_action_types
where action_type_id = l_action_type_id
and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
select item_class_id
into l_head_item_class_id
from ame_item_classes
where name = 'header'
and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttribute.attribute_id
,p_application_id => tempApplication.item_id
,p_atu_object_version_number => l_atu_object_version_number);
procedure update_ame_rule
(p_validate in boolean default false
,p_language_code in varchar2 default hr_api.userenv_lang
,p_rule_id in number
,p_description in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_start_date in out nocopy date
,p_end_date in out nocopy date
,p_effective_date in date default null
) is
--
-- Declare cursors and local variables
--
l_rul_object_version_number number;
l_proc varchar2(72) := g_package||'update_ame_rule';
savepoint update_ame_rule;
ame_rule_bk3.update_ame_rule_b
(p_rule_id => p_rule_id
,p_description => p_description
,p_object_version_number => p_object_version_number
,p_start_date => p_start_date
,p_end_date => p_end_date
);
(p_module_name => 'update_ame_rule'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_update
,p_rule_id => p_rule_id
,p_object_version_number => l_rul_object_version_number
,p_description => p_description
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
ame_rule_bk3.update_ame_rule_a
(p_rule_id => p_rule_id
,p_description => p_description
,p_object_version_number => l_rul_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
(p_module_name => 'update_ame_rule'
,p_hook_type => 'AP'
);
rollback to update_ame_rule;
rollback to update_ame_rule;
end update_ame_rule;
procedure update_ame_rule_usage
(p_validate in boolean default false
,p_rule_id in number
,p_application_id in number
,p_priority in number default hr_api.g_number
,p_approver_category in varchar2 default hr_api.g_varchar2
,p_old_start_date in date
,p_object_version_number in out nocopy number
,p_start_date in out nocopy date
,p_end_date in out nocopy date
,p_effective_date in date default null
) is
--
-- Declare cursors and local variables
--
l_date_changed varchar2(10);
l_proc varchar2(72) := g_package||'update_ame_rule_usage';
select start_date
,end_date
,object_version_number
from ame_rules
where rule_id = p_rule_id
and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select action_id
,start_date
,end_date
,object_version_number
from ame_action_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select condition_id
,start_date
,end_date
,object_version_number
from ame_condition_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
delete_ame_rule_usage(p_validate => p_validate
,p_rule_id => p_rule_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
,p_start_date => p_start_date
,p_end_date => p_end_date
);
savepoint update_ame_rule_usage;
ame_rule_bk4.update_ame_rule_usage_b
(p_rule_id => p_rule_id
,p_application_id => p_application_id
,p_priority => p_priority
,p_approver_category => p_approver_category
,p_old_start_date => p_old_start_date
,p_object_version_number => p_object_version_number
,p_start_date => p_start_date
,p_end_date => p_end_date
);
(p_module_name => 'update_ame_rule_usage'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_update
,p_rule_id => p_rule_id
,p_item_id => p_application_id
,p_old_start_date => p_old_start_date
,p_object_version_number=> l_rlu_object_version_number
,p_priority => p_priority
,p_approver_category => p_approver_category
,p_start_date => l_rlu_start_date
,p_end_date => l_rlu_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_effective_date => l_effective_date
,p_object_version_number => l_rul_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_action_id => tempActions.action_id
,p_effective_date => l_effective_date
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_acu_start_date
,p_end_date => l_acu_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_condition_id => tempConditions.condition_id
,p_effective_date => l_effective_date
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_cnu_start_date
,p_end_date => l_cnu_end_date
);
ame_rule_bk4.update_ame_rule_usage_a
(p_rule_id => p_rule_id
,p_application_id => p_application_id
,p_priority => p_priority
,p_approver_category => p_approver_category
,p_old_start_date => p_old_start_date
,p_object_version_number => p_object_version_number
,p_start_date => p_start_date
,p_end_date => p_end_date
);
(p_module_name => 'update_ame_rule_usage'
,p_hook_type => 'AP'
);
rollback to update_ame_rule_usage;
rollback to update_ame_rule_usage;
end update_ame_rule_usage;
procedure delete_ame_rule_usage
(p_validate in boolean default false
,p_rule_id in number
,p_application_id in number
,p_object_version_number in out nocopy number
,p_start_date in out nocopy date
,p_end_date in out nocopy date
) is
--+
--+ Declare cursors and local variables
--+
l_atu_object_version_number number;
l_proc varchar2(72) := g_package||'delete_ame_rule_usage';
select action_id, start_date, end_date, object_version_number
from ame_action_usages
where rule_id = p_rule_id
and ( l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select start_date
,end_date
,object_version_number
from ame_rules
where rule_id = p_rule_id
and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select condition_id
,start_date
,end_date
,object_version_number
from ame_condition_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select ame_conditions.attribute_id attribute_id
from ame_conditions
,ame_condition_usages
where ame_condition_usages.rule_id = p_rule_id
and (l_effective_date between ame_condition_usages.start_date
and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
or
(l_effective_date < 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)))
and ame_condition_usages.condition_id = ame_conditions.condition_id
and ame_conditions.condition_type <> ame_util.listModConditionType
and l_effective_date between ame_conditions.start_date
and nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date);
select man.attribute_id
from ame_mandatory_attributes man
,ame_action_usages acu
,ame_actions act
where man.action_type_id = act.action_type_id
and acu.action_id = act.action_id
and acu.rule_id = p_rule_id
and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
);
select attribute_id, use_count, start_date, end_date, object_version_number
from ame_attribute_usages
where attribute_id = p_attribute_id
and application_id = p_application_id
and l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date);
savepoint delete_ame_rule_usage;
ame_rule_bk5.delete_ame_rule_usage_b
(p_rule_id => p_rule_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
,p_start_date => p_start_date
,p_end_date => p_end_date
);
(p_module_name => 'delete_ame_rule_usage'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_item_id => p_application_id
,p_object_version_number => l_rlu_object_version_number
,p_start_date => l_rlu_start_date
,p_end_date => l_rlu_end_date
);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
,p_application_id => p_application_id
,p_atu_object_version_number => l_atu_object_version_number);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
,p_application_id => p_application_id
,p_atu_object_version_number => l_atu_object_version_number);
select count(*)
into l_usage_count
from ame_rule_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_action_id => tempActions.action_id
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_acu_start_date
,p_end_date => l_acu_end_date
);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_condition_id => tempConditions.condition_id
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_cnu_start_date
,p_end_date => l_cnu_end_date
);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_object_version_number => l_rul_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
ame_rule_bk5.delete_ame_rule_usage_a
(p_rule_id => p_rule_id
,p_application_id => p_application_id
,p_object_version_number => l_rlu_object_version_number
,p_start_date => l_rlu_start_date
,p_end_date => l_rlu_end_date
);
(p_module_name => 'delete_ame_rule_usage'
,p_hook_type => 'AP'
);
rollback to delete_ame_rule_usage;
rollback to delete_ame_rule_usage;
end delete_ame_rule_usage;
procedure delete_ame_rule_condition
(p_validate in boolean default false
,p_rule_id in number
,p_condition_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
,p_effective_date in date default null
) is
--+
--+ Declare cursors
--+
l_effective_date date;
select application_name
from ame_calling_apps
where application_id = applicationIdIn
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
select count(*)
from ame_conditions, ame_condition_usages
where ame_condition_usages.condition_id <> p_condition_id
and ame_condition_usages.rule_id = p_rule_id
and ( l_effective_date between ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < 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)))
and ame_condition_usages.condition_id = ame_conditions.condition_id
and ame_conditions.condition_type = p_condition_type
and l_effective_date between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date );
select rule_type, start_date, end_date, item_class_id
from ame_rules
where rule_id = p_rule_id
and ( l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select item_id
from ame_rule_usages
where rule_id = p_rule_id
and ( l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date ) or
(l_effective_date < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select condition_type
from ame_conditions
where condition_id = conditionIdIn
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date);
select use_count, start_date, end_date, object_version_number
from ame_attribute_usages
where attribute_id = l_attribute_id
and l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date )
and application_id = p_application_id;
select attribute_id
from ame_conditions
where condition_id = p_condition_id
and l_effective_date between start_date and
nvl(end_date - ame_util.oneSecond,l_effective_date ) ;
l_proc varchar2(72) := g_package||'delete_ame_rule_condition ';
savepoint delete_ame_rule_condition ;
ame_rule_bk8.delete_ame_rule_condition_b
(p_rule_id => p_rule_id
,p_condition_id => p_condition_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_ame_rule_condition'
,p_hook_type => 'BP'
);
conditionIdList.delete(i);
conditionIdList.delete(l_condition_count);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_condition_id => p_condition_id
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_cnu_start_date
,p_end_date => l_cnu_end_date
);
ame_attribute_api.updateUseCount(p_attribute_id => l_attribute_id
,p_application_id => tempApplications.item_id
,p_atu_object_version_number => l_atu_object_version_number);
ame_rule_bk8.delete_ame_rule_condition_a
(p_rule_id => p_rule_id
,p_condition_id => p_condition_id
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_cnu_start_date
,p_end_date => l_cnu_end_date
);
(p_module_name => 'delete_ame_rule_condition'
,p_hook_type => 'AP'
);
rollback to delete_ame_rule_condition;
rollback to delete_ame_rule_condition;
end delete_ame_rule_condition;
procedure delete_ame_rule_action
(p_validate in boolean default false
,p_rule_id in number
,p_action_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
,p_effective_date in date default null
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_ame_rule_action ';
select count(*)
from ame_action_usages acu
where rule_id = p_rule_id
and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)))
and action_id <> p_action_id;
select rule_type
,start_date
,end_date
,item_class_id
from ame_rules
where rule_id = p_rule_id
and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select man.attribute_id
from ame_mandatory_attributes man
,ame_actions act
where man.action_type_id = act.action_type_id
and act.action_id = actionIdIn
and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date);
select item_id
from ame_rule_usages
where rule_id = ruleIdIn
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date)
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select application_id
,use_count
,start_date
,end_date
,object_version_number
from ame_attribute_usages
where attribute_id = attributeIdIn
and application_id = applicationIdIn
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date);
savepoint delete_ame_rule_action ;
ame_rule_bk9.delete_ame_rule_action_b
(p_rule_id => p_rule_id
,p_action_id => p_action_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_ame_rule_action '
,p_hook_type => 'BP'
);
actionIdList.delete(i);
actionIdList.delete(l_count);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_action_id => p_action_id
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_acu_start_date
,p_end_date => l_acu_end_date
);
ame_attribute_api.updateUseCount(p_attribute_id => tempAttribute.attribute_id
,p_application_id => tempApplication.item_id
,p_atu_object_version_number => l_atu_object_version_number);
ame_rule_bk9.delete_ame_rule_action_a
(p_rule_id => p_rule_id
,p_action_id => p_action_id
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_acu_start_date
,p_end_date => l_acu_end_date
);
(p_module_name => 'delete_ame_rule_action '
,p_hook_type => 'AP'
);
rollback to delete_ame_rule_action ;
rollback to delete_ame_rule_action ;
end delete_ame_rule_action ;
select condition_type
,attribute_id
from ame_conditions
where condition_id = p_condition_id
and l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date);
select rule_type
,start_date
,end_date
,item_class_id
from ame_rules
where rule_id = p_rule_id
and (l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond,l_effective_date) or
(l_effective_date < start_date
and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
select distinct item_id
from ame_rule_usages
where rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select cnu.condition_id
into l_old_condition_id
from ame_condition_usages cnu
,ame_conditions cnd
where cnu.rule_id = p_rule_id
and cnu.condition_id = cnd.condition_id
and cnd.condition_type = ame_util.listModConditionType
and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
);
select object_version_number
,start_date
,end_date
into l_cnu_object_version_number
,l_cnu_start_date
,l_cnu_end_date
from ame_condition_usages
where condition_id = l_old_condition_id
and rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
,p_datetrack_mode => hr_api.g_delete
,p_rule_id => p_rule_id
,p_condition_id => l_old_condition_id
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_cnu_start_date
,p_end_date => l_new_cnu_start_date
);