[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function allOrdinaryConditionsDeleted(conditionIdListIn in ame_util.idList,
deletedListIn in ame_util.stringList) return boolean as
conditionId integer;
deletedOrdinaryConditionCount integer;
deletedOrdinaryConditionCount := 0;
/* get a count of the deleted ordinary conditions */
for i in 1..deletedListIn.count loop
if(deletedListIn(i) like 'con%') then
conditionId := to_number(substrb(deletedListIn(i),4,(lengthb(deletedListIn(i)))));
deletedOrdinaryConditionCount := (deletedOrdinaryConditionCount + 1);
/* verify if all ordinary conditions were deleted */
if(ordinaryConditionCount = deletedOrdinaryConditionCount) then
/* all ordinary conditions were deleted */
return(true);
routineNamein => 'allOrdinaryConditionsDeleted',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end allOrdinaryConditionsDeleted;
select count(*)
into ruleCount
from ame_rule_usages
where
item_id = applicationIdIn 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 distinct(ame_action_types.action_type_id) action_type_id
from ame_action_types,
ame_actions,
ame_action_usages
where
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = ruleIdIn and
sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
/* Verify if both action types are now selected. */
for i in 1..actionTypeIds.count loop
if(actionTypeIds(i) = nonFinalAuthActionTypeId) then
nonFinalAuthority := true;
select start_date, end_date, priority
from ame_rule_usages
where rule_id = ruleIdIn and
item_id = applicationIdIn and
( processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond,processingDateIn ) or
(processingDateIn < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)))
order by start_date desc;
function deletedAllExceptionConditions(conditionIdListIn in ame_util.idList,
deletedListIn in ame_util.stringList) return boolean as
conditionId integer;
for i in 1..deletedListIn.count loop
if(deletedListIn(i) like 'con%') then
conditionId := to_number(substrb(deletedListIn(i),4,(lengthb(deletedListIn(i)))));
/* all exception conditions were deleted */
return(true);
routineNamein => 'deletedAllExceptionConditions',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end deletedAllExceptionConditions;
select count(*)
into descriptionCount
from ame_rules
where upper(description) = upper(descriptionIn) and
/* allows for future start date */
((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 distinct(ame_action_types.action_type_id) action_type_id
from ame_action_types,
ame_actions,
ame_action_usages
where
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = ruleIdIn and
sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
select approver_category
into approverCategory
from
ame_rule_usages
where
rule_id = ruleIdIn and
item_id = applicationIdIn and
start_date = usageStartDateIn 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 count(condition_id)
into conditionCount
from ame_condition_usages
where
rule_id = ruleIdIn and
/* allows for future start date */
((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 item_class_id
into itemClassId
from ame_rules
where
rule_id = ruleIdIn 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 item_class_id
into itemClassId
from ame_rules
where
rule_id = ruleIdIn and
rownum < 2 and /* for efficiency */
(processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
select ame_conditions.condition_id
into conditionId
from ame_conditions,
ame_condition_usages
where
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_conditions.condition_type = ame_util.listModConditionType 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)));
select name
into organizationName
from hr_organization_units
where
sysdate >= date_from and
organization_id = organizationIdIn and
(date_to is null or sysdate < date_to);
select to_char(priority)
into priority
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id = applicationIdIn and
usageStartDateIn between start_date and
nvl(end_date - ame_util.oneSecond, usageStartDateIn);
select item_id
into itemId
from ame_rule_usages
where
rule_id = ruleIdIn 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 rule_type
into ruleType
from ame_rules
where
rule_id = ruleIdIn 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 rule_type
into ruleType
from ame_rules
where
rule_id = ruleIdIn and
rownum < 2 and /* for efficiency */
(processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
select description
into description
from ame_rules
where
rule_id = ruleIdIn and
rownum < 2 and /* for efficiency */
((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 description
into description
from ame_rules
where
rule_id = ruleIdIn and
rownum < 2 and /* for efficiency */
(processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
select end_date
into endDate
from ame_rules
where
rule_id = ruleIdIn 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 end_date
into usageEndDate
from ame_rule_usages
where
item_id = applicationIdIn and
rule_id = ruleIdIn and
effectiveDate between start_date and
nvl(end_date - ame_util.oneSecond, effectiveDate);
select start_date
into usageStartDate
from ame_rule_usages
where
item_id = applicationIdIn and
rule_id = ruleIdIn and
effectiveDate between start_date and
nvl(end_date - ame_util.oneSecond, effectiveDate);
select end_date
into endDate
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id = applicationIdIn and
creation_date = processingDateIn;
select rule_id
from ame_rules
where
rule_type = typeIn 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 stripe_set_id
into stripeSetId
from ame_rule_stripe_sets
where
rule_id = ruleIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into ruleCount
from ame_rules
where upper(rule_key) = upper(ruleKeyIn) and
rownum < 2;
select to_char(db.dbid)
into databaseId
from v$database db, v$instance instance
where upper(db.name) = upper(instance.instance_name);
select ame_rule_keys_s.nextval into ruleKeyId from dual;
select rule_key
into ruleKey
from ame_rules
where
rule_id = ruleIdIn 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 rule_key
into ruleKey
from ame_rules
where
rule_id = ruleIdIn and
(processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn)) ;
select min(start_date)
into ruleStartDate
from ame_rule_usages
where
rule_id = ruleIdIn 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(nvl(end_date,to_date('31/12/4712','DD/MM/YYYY')))
into ruleEndDate
from ame_rule_usages
where
rule_id = ruleIdIn 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 start_date
into startDate
from ame_rules
where
rule_id = ruleIdIn 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 item_class_id
from ame_conditions,
ame_attributes,
ame_condition_usages
where
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_conditions.attribute_id = ame_attributes.attribute_id and
ame_condition_usages.rule_id = ruleIdIn and
ame_attributes.item_class_id <> headerItemClassIdIn 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)) and
(sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate));
select start_date
into startDate
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id = applicationIdIn and
creation_date = processingDateIn;
select rule_type
into ruleType
from ame_rules
where
rule_id = ruleIdIn 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 start_date
into startDate
from ame_rules
where
rule_id = ruleIdIn 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 rule_type
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select ame_action_type_usages.rule_type
from ame_action_type_usages,
ame_action_types,
ame_actions
where
ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_actions.action_id = actionIdIn and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
select count(*)
into conditionCount
from ame_conditions,
ame_condition_usages
where
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_conditions.condition_type = ame_util.listModConditionType 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)));
select count(distinct ame_action_types.action_type_id)
into subOrListModActionCount
from ame_action_usages,
ame_actions,
ame_action_types,
ame_action_type_usages
where ame_action_usages.rule_id = ruleIdIn and
ame_action_usages.action_id = ame_actions.action_id and
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_action_type_usages.action_type_id = ame_action_types.action_type_id and
ame_action_type_usages.rule_type in (ame_util.substitutionRuleType,
ame_util.listModRuleType) and
sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
function isAtLeastOneICAttrSelected(itemClassIdIn in integer,
attributeIdsIn in ame_util.idList) return boolean as
begin
for i in 1..attributeIdsIn.count loop
if(ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdsIn(i)) =
itemClassIdIn) then
return(true);
routineNamein => 'isAtLeastOneICAttrSelected',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end isAtLeastOneICAttrSelected;
function isAtLeastOneICCondSelected(itemClassIdIn in integer,
conditionIdsIn in ame_util.idList) return boolean as
attributeId integer;
routineNamein => 'isAtLeastOneICCondSelected',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end isAtLeastOneICCondSelected;
select count(*)
into useCount
from ame_rule_usages
where
rule_id = ruleIdIn 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 count(*)
into useCount
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id <> applicationIdIn 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)));
function lastConditionDeleted(conditionIdListIn in ame_util.idList,
deletedListIn in ame_util.stringList) return boolean as
conditionCount integer;
deleteCount integer;
deleteCount := 0;
for i in 1..deletedListIn.count loop
if(deletedListIn(i)) like 'con%' then
deleteCount := deleteCount + 1;
if(conditionCount = deleteCount) then
return(true);
routineNamein => 'lastConditionDeleted',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end lastConditionDeleted;
startDateToInsert date;
endDateToInsert date;
startDateToInsert := trunc(startDateIn); /* Truncate future start dates. */
startDateToInsert := processingDate; /* Don't truncate start dates that are for today. */
endDateToInsert := null;
endDateToInsert := trunc(endDateIn);
startDateToInsert := startDateIn;
endDateToInsert := endDateIn;
/* misc preparation for inserts */
if(lengthb(ruleKeyIn) > 100) then
raise ruleKeyLengthException;
select count(*)
into ruleCount
from ame_rules
where
((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 min(rule_id) - 1
into ruleId
from ame_rules
where
((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_rules_s.nextval into ruleId from dual;
select count(*)
into tempCount
from ame_rules
where
rule_id = ruleId and
created_by = ame_util.seededDataCreatedById;
/* inserts */
insert into ame_rules(rule_id,
rule_type,
rule_key,
action_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
description,
item_class_id)
values(ruleId,
typeIn,
ruleKeyIn,
null,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
startDateToInsert,
endDateToInsert,
descriptionIn,
itemClassIdIn);
insert into ame_condition_usages(rule_id,
condition_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(ruleId,
conditionIdsIn(tempIndex),
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
startDateToInsert,
endDateToInsert);
insert into ame_action_usages(rule_id,
action_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(ruleId,
actionIdsIn(tempIndex),
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
startDateToInsert,
endDateToInsert);
updateRuleStripeSets(applicationIdIn => applicationIdIn,
ruleIdIn => ruleId,
conditionIdsIn => conditionIdsIn);
select count(*)
into useCount
from ame_rule_stripe_sets
where
rule_id = ruleIdIn and
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
insert into ame_rule_stripe_sets(rule_id,
stripe_set_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
security_group_id,
start_date,
end_date)
values(ruleIdIn,
stripeSetIdIn,
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
null,
sysdate,
null);
ame_admin_pkg.updateStripingAttUseCount(applicationIdIn => applicationIdIn);
updateParentObjectIn in boolean default false) return boolean as
cursor startDateCursor is
select start_date
from ame_rules
where
rule_id = ruleIdIn 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)))
for update;
startDateToInsert date;
endDateToInsert date;
startDateToInsert := trunc(startDateIn); /* Truncate future start dates. */
startDateToInsert := processingDate; /* Don't truncate start dates that are for today. */
endDateToInsert := null;
endDateToInsert := processingDate;
endDateToInsert := trunc(endDateIn);
if(endDateToInsert = startDateToInsert) then
raise invalidDateException;
select count(*)
into useCount
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id = itemIdIn and
trunc(start_date) = startDateIn and
nvl(end_date, processingDate) = nvl(endDateIn, processingDate) 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 count(*)
into tempCount
from ame_rule_usages
where
rule_id = ruleIdIn and
item_id = itemIdIn and
created_by = ame_util.seededDataCreatedById;
insert into ame_rule_usages(item_id,
rule_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
priority,
approver_category)
values(itemIdIn,
ruleIdIn,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
startDateToInsert,
endDateToInsert,
priorityIn,
approvalCategory);
if updateParentObjectIn then
endDate := processingDate;
update ame_action_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
action_id = actionIdList(i) and
((processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate)) or
(processingDate < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
update ame_condition_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
condition_id = conditionIdList(i) and
((processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate)) or
(processingDate < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
update ame_rules
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
((processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate)) or
(processingDate < start_date and
start_date < nvl(end_date,start_date + ame_util.oneSecond)));
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select distinct(ame_action_types.action_type_id) action_type_id
from ame_action_types,
ame_actions,
ame_action_usages
where
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = ruleIdIn and
sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
select count(*)
into conditionCount
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.condition_type = ame_util.ordinaryConditionType 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 ame_rules.rule_id
from ame_rules,
ame_rule_usages
where
item_id = applicationIdIn and
rule_type = typeIn and
(item_class_id is null or
item_class_id = itemClassIdIn) and
ame_rules.rule_id = ame_rule_usages.rule_id and
((sysdate between ame_rules.start_date and
nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_rules.start_date and
ame_rules.start_date < nvl(ame_rules.end_date,
ame_rules.start_date + ame_util.oneSecond))) 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)));
conditionIdList2.delete;
actionIdList2.delete;
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 count(*)
into tempCount
from ame_conditions,
ame_attributes,
ame_condition_usages
where
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_conditions.attribute_id = ame_attributes.attribute_id and
ame_condition_usages.rule_id = ruleIdIn and
ame_attributes.item_class_id <> headerItemClassId 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)) and
(sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate));
deleteListIn in ame_util.stringList default ame_util.emptyStringList,
descriptionIn in varchar2 default null,
applicationIdIn in integer default null,
parentVersionStartDateIn in date,
finalizeIn in boolean default false,
processingDateIn in date default null) as
cursor ruleStartDateCursor is
select start_date
from ame_rules
where
rule_id = ruleIdIn 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)))
for update;
if(deleteListIn.count > 0) then
for i in 1..deleteListIn.count loop
if(deleteListIn(i) like 'act%') then
actionCount2 := (actionCount2 + 1);
for i in 1..deleteListIn.count loop
if(deleteListIn(i)) like 'con%' then
conditionId := to_number(substrb(deleteListIn(i),4,(lengthb(deleteListIn(i)))));
/* there is a match so delete from the condition list */
newConditionIdList.delete(j);
/* reindex those conditions that fall above the deleted condition */
newConditionIdList(k-1) := newConditionIdList(k);
which now leaves a duplicate so delete the duplicate */
newConditionIdList.delete(conditionCount);
actionId := to_number(substrb(deleteListIn(i),4,(lengthb(deleteListIn(i)))));
/* there is a match so delete from the action list */
newActionIdList.delete(j);
/* reindex those actions that fall above the deleted action */
newActionIdList(k-1) := newActionIdList(k);
which now leaves a duplicate so delete the duplicate */
newActionIdList.delete(actionCount);
update ame_condition_usages
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn 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)));
update ame_action_usages
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn 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)));
update ame_rules
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn 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)));
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select item_id
from ame_rule_usages
where
rule_id = ruleIdIn 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))) ;
ame_attribute_pkg.updateUseCount(attributeIdIn => attributeIds(i),
applicationIdIn => applicationIdIn,
finalizeIn => finalizeIn);
ame_attribute_pkg.updateUseCount(attributeIdIn => attributeIdsIn(i),
applicationIdIn => applicationIdIn,
finalizeIn => finalizeIn);
update ame_rule_stripe_sets
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where
rule_id = ruleIdIn and
stripe_set_id = oldStripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
insert into ame_rule_stripe_sets(rule_id,
stripe_set_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
security_group_id,
start_date,
end_date)
values(ruleIdIn,
newStripeSetIdIn,
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
null,
sysdate,
null);
select start_date
from ame_rules
where
rule_id = ruleIdIn 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)))
for update;
select rule_id
from ame_rule_usages
where
item_id = applicationIdIn and
rule_id = ruleIdIn and
start_date = oldStartDateIn and
nvl(end_date, sysdate) = nvl(oldEndDateIn, sysdate)
for update;
update ame_rule_usages
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
item_id = applicationIdIn and
start_date = oldStartDateIn and
nvl(end_date, endDate) = nvl(oldEndDateIn, endDate) ;
updateParentObjectIn => true);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select stripe_set_id
into stripeSetId
from ame_rule_stripe_sets
where
rule_id = ruleIdIn and
stripe_set_id in
(select stripe_set_id
from ame_stripe_sets
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date))) and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
update ame_rule_stripe_sets
set end_date = sysdate
where
rule_id = ruleIdIn and
stripe_set_id = stripeSetId and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
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 ame_actions.action_id,
ame_actions.parameter,
ame_actions.parameter_two,
ame_action_types.name,
ame_action_types.dynamic_description,
ame_action_types.description_query
from ame_actions,
ame_action_types,
ame_action_usages
where
ame_actions.action_type_id = ame_action_types.action_type_id and
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = ruleIdIn and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.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)))
order by ame_actions.created_by, ame_actions.description;
select ame_actions.action_id,
ame_actions.parameter,
ame_actions.parameter_two,
ame_actions.description,
ame_action_types.dynamic_description,
ame_action_types.description_query
from ame_actions,
ame_action_types
where
ame_actions.action_type_id = ame_action_types.action_type_id and
ame_actions.action_type_id = actionTypeIdIn and
ame_actions.action_id not in
(select 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)))) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
order by ame_actions.created_by, ame_actions.description;
select
r.rule_id rule_id,
r.rule_key rule_key,
r.rule_type rule_type,
r.description rule_description,
u.start_date usage_start_date,
u.end_date usage_end_date,
u.priority priority,
r.item_class_id,
u.approver_category
from
ame_rule_usages u,
ame_rules r,
ame_item_class_usages i
where
u.rule_id = r.rule_id and
r.item_class_id = i.item_class_id and
u.item_id = applicationIdIn and
i.application_id = applicationIdIn and
((sysdate between r.start_date and
nvl(r.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < r.start_date and
r.start_date < nvl(r.end_date,
r.start_date + ame_util.oneSecond))) and
((sysdate between u.start_date and
nvl(u.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < u.start_date and
u.start_date < nvl(u.end_date,
u.start_date + ame_util.oneSecond))) and
(i.start_date <= sysdate and
(i.end_date is null or sysdate < i.end_date))
order by i.item_class_order_number, rule_type, rule_description, usage_start_date;
select
r.rule_id rule_id,
r.rule_key rule_key,
r.rule_type rule_type,
r.description rule_description,
u.start_date usage_start_date,
u.end_date usage_end_date,
u.priority priority,
null item_class_id,
u.approver_category
from
ame_rule_usages u,
ame_rules r
where
u.rule_id = r.rule_id and
r.rule_type in (ame_util.substitutionRuleType, ame_util.listModRuleType) and
u.item_id = applicationIdIn and
((sysdate between r.start_date and
nvl(r.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < r.start_date and
r.start_date < nvl(r.end_date,
r.start_date + ame_util.oneSecond))) and
((sysdate between u.start_date and
nvl(u.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < u.start_date and
u.start_date < nvl(u.end_date,
u.start_date + ame_util.oneSecond)))
order by rule_type, rule_description, usage_start_date;
select
distinct(r.rule_id) rule_id,
r.description
from
ame_rules r,
ame_rule_usages u
where
u.rule_id = r.rule_id and
(r.item_class_id is null or
r.item_class_id = itemClassIdIn) and
u.item_id = applicationIdToShareIn and
((sysdate between r.start_date and
nvl(r.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < r.start_date and
r.start_date < nvl(r.end_date,
r.start_date + ame_util.oneSecond))) and
((sysdate between u.start_date and
nvl(u.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < u.start_date and
u.start_date < nvl(u.end_date,
u.start_date + ame_util.oneSecond)))
order by r.description;
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_conditions.attribute_id attribute_id
from
ame_conditions,
ame_condition_usages
where
ame_conditions.condition_type = ame_util.ordinaryConditionType and
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 attribute_id;
select ame_conditions.attribute_id attribute_id
from
ame_conditions,
ame_condition_usages
where
ame_conditions.condition_type in (ame_util.ordinaryConditionType, ame_util.exceptionConditionType) and
ame_condition_usages.rule_id = ruleIdIn and
ame_condition_usages.condition_id = ame_conditions.condition_id 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)))
union
select ame_mandatory_attributes.attribute_id attribute_id
from
ame_mandatory_attributes,
ame_action_usages,
ame_actions
where
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 = ruleIdIn and
(ame_mandatory_attributes.start_date <= sysdate and
(ame_mandatory_attributes.end_date is null or sysdate < ame_mandatory_attributes.end_date)) 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))) and
(ame_actions.start_date <= sysdate and
(ame_actions.end_date is null or sysdate < ame_actions.end_date));
select application_id,
application_name
from ame_rule_usages,
ame_calling_apps
where
ame_rule_usages.item_id = ame_calling_apps.application_id and
ame_rule_usages.rule_id = ruleIdIn and
(ame_rule_usages.start_date <= sysdate and
(ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date)) and
(ame_calling_apps.start_date <= sysdate and
(ame_calling_apps.end_date is null or sysdate < ame_calling_apps.end_date))
order by application_name;
select item_id,
priority
from ame_rule_usages
where rule_id = ruleIdIn 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 rule_id
from ame_rule_stripe_sets
where
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select stripe_set_id
from ame_rule_stripe_sets
where
rule_id = ruleIdIn and
effectiveRuleDateIn between
(start_date + ame_util.oneSecond) and
nvl(end_date - ame_util.oneSecond, effectiveRuleDateIn);
select to_char(ame_item_classes.item_class_id),
name
from ame_item_classes,
ame_item_class_usages
where
ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
ame_item_class_usages.application_id = applicationIdIn and
ame_item_classes.item_class_id <> itemClassIdIn and
sysdate between ame_item_classes.start_date and
nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_item_class_usages.start_date and
nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
order by ame_item_classes.item_class_id;
select
ame_conditions.condition_id condition_id
from
ame_conditions,
ame_condition_usages
where
ame_conditions.condition_type = conditionTypeIn and
ame_condition_usages.rule_id = ruleIdIn and
ame_condition_usages.condition_id = ame_conditions.condition_id 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_conditions.condition_id condition_id
from
ame_conditions,
ame_condition_usages
where
ame_conditions.condition_type = conditionTypeIn and
ame_condition_usages.rule_id = ruleIdIn and
ame_condition_usages.condition_id = ame_conditions.condition_id 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;
update ame_rules
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn 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)));
update ame_rule_stripe_sets
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where
stripe_set_id = stripeSetIdsIn(i) and
(end_date is null or sysdate < end_date);
update ame_rule_stripe_sets
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where
rule_id = ruleIdIn and
(end_date is null or sysdate < end_date);
select start_date
from ame_rules
where
rule_id = ruleIdIn 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)))
for update;
update ame_rule_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
item_id = itemIdIn and
start_date = usageStartDateIn and
start_date < nvl(end_date, start_date + ame_util.onesecond);
update ame_condition_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
rule_id = ruleIdIn and
condition_id = conditionIdList(i) 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)));
update ame_action_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
rule_id = ruleIdIn and
action_id = actionIdList(i) 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)));
ame_admin_pkg.updateStripingAttUseCount(applicationIdIn => itemIdIn);
update ame_rules
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = endDate
where
rule_id = ruleIdIn and
((processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate)) or
(processingDate < start_date and
start_date < nvl(end_date, start_date + ame_util.oneSecond)));
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
procedure updateRuleStripeSets(applicationIdIn in integer,
ruleIdIn in integer,
conditionIdsIn in ame_util.idList,
finalizeIn in boolean default false) as
*/
/* This procedure updates the rule's stripe set in each transaction type that uses striping. */
/*
cursor applicationIdCursor is
select application_id
from ame_calling_apps
where
start_date <= sysdate and
(end_date is null or sysdate < end_date);
routineNameIn => 'updateRuleStripeSets',
exceptionNumberIn => sqlcode,
exceptionStringIn => '(rule ID ' ||
ruleIdIn||
') ' ||
sqlerrm);
end updateRuleStripeSets;