[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into attributeCount
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_id
into itemClassId
from ame_attributes
where
name = attributeNameIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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)));
ame_attribute_usages table is not yet updated so we need
to verify if striping is on and if so, check whether:
1. It's a striping attribute that has been set to null or
(isStripingAttributeChangeIn)
2. It's an attribute that is becoming a striping attribute.
(isBecomingStripingAttributeIn) */
/*
if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn) and
(isStripingAttribute(applicationIdIn => applicationIdIn,
attributeIdIn => attributeIdIn) or
isBecomingStripingAttributeIn = ame_util.booleanTrue) and
isStripingAttributeChangeIn = ame_util.booleanFalse) then
select count(*)
into ruleCount
from ame_rule_usages
where
ame_rule_usages.item_id = applicationIdIn and
(ame_rule_usages.start_date <= sysdate and
(ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date));
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)));
select
ame_action_types.name
from
ame_action_types,
ame_mandatory_attributes
where
ame_action_types.action_type_id = ame_mandatory_attributes.action_type_id and
ame_mandatory_attributes.attribute_id = attributeIdIn and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select approver_type_id
into approverTypeId
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
select count(*)
into attributeConditionCnt
from ame_attributes,
ame_conditions
where
ame_attributes.attribute_id = ame_conditions.attribute_id and
ame_attributes.attribute_id = attributeIdIn and
ame_conditions.condition_type = conditionTypeIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
select count(*)
into attributeConditionInUseCnt
from ame_attributes,
ame_conditions,
ame_rules,
ame_condition_usages
where
ame_attributes.attribute_id = ame_conditions.attribute_id and
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_rules.rule_id = ame_condition_usages.rule_id and
ame_rules.rule_id = ruleIdIn and
ame_attributes.attribute_id = attributeIdIn and
ame_conditions.condition_type = conditionTypeIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) 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))) 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)));
select ame_attributes.name
from ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = actionTypeIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by ame_attributes.name;
select description
into description
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select attribute_id
into attributeId
from ame_attributes
where
name = upper(attributeNameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_id
into itemClassId
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select line_item
into lineItem
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select name
into name
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select query_string
into queryString
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select is_static
into staticUsage
from ame_attribute_usages
where attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select use_count
into useCount
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select user_editable
into userEditable
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select attribute_type
into attributeType
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into attributeCount
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id <> applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select
to_number(value_1),
to_number(value_2),
to_number(value_3),
to_number(value_4),
to_number(value_5)
into
stripingAttributeIds(1),
stripingAttributeIds(2),
stripingAttributeIds(3),
stripingAttributeIds(4),
stripingAttributeIds(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into useCount
from
ame_conditions
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select use_count
into useCount
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into lineItemCount
from ame_attributes
where
attribute_id = attributeIdIn and
line_item = ame_util.booleanTrue and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into mandatoryCount
from ame_mandatory_attributes
where action_type_id = -1 and
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_id
into itemClassId
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into requiredCount
from ame_mandatory_attributes
where
action_type_id <> -1 and
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select created_by
into createdByValue
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select is_striping_attribute
into isStripingAttribute
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into tempCount
from ame_attributes
where
name = upper(nameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select attribute_id
into attributeId
from ame_attributes
where
(attributeIdIn is null or attribute_id <> attributeIdIn) and
name = attributeName and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_attributes_s.nextval into attributeId from dual;
select count(*)
into tempCount
from ame_attributes
where
attribute_id = attributeId and
created_by = ame_util.seededDataCreatedById;
insert into ame_attributes(attribute_id,
name,
attribute_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
description,
line_item,
approver_type_id,
item_class_id)
values(attributeId,
attributeName,
typeIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
nvl(newStartDateIn, processingDate),
null,
descriptionIn,
null,
approverTypeIdIn,
itemClassIdIn);
select user_editable
into isEditable
from ame_attribute_usages
where attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
update ame_attributes
set
last_updated_by = currentUserId,
last_update_date = endDateIn,
last_update_login = currentUserId,
end_date = endDateIn
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select start_date
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select count(*)
into tempCount
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.attribute_id = attributeIdIn and
ame_attribute_usages.application_id = applicationIdIn and
ame_attribute_usages.is_static = staticUsageIn and
ame_attribute_usages.query_string = queryString and
(nameIn is null or name = upper(nameIn)) and
(attributeType is null or upper(attribute_type) = upper(attributeType)) and
(descriptionIn is null or description = descriptionIn) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) ;
/* Get current values as necessary for update. */
if(nameIn is null) then
name := getName(attributeIdIn => attributeIdIn);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
update ame_attribute_usages
set
last_updated_by = currentUserId,
last_update_date = endDateIn,
last_update_login = currentUserId,
end_date = endDateIn
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
updateParentObjectIn => true,
queryStringIn => queryString,
newStartDateIn => newStartDateIn,
finalizeIn => finalizeIn);
errorMessage := 'The select clause of a currency attribute''s ' ||
'usage must select three values: ' ||
'amount, currency code, and conversion-type code ';
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
use_count > 0 and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = -1 and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
use_count > 0 and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_attributes.item_class_id = itemClassIdIn and
ame_mandatory_attributes.action_type_id = -1 and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select attribute_id, name
from ame_attributes
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by name;
select attribute_id
from ame_attribute_usages
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by attribute_id;
select
ame_attributes.attribute_id id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.item_class_id = itemClassIdIn and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.attribute_id not in (select attribute_id
from ame_mandatory_attributes
where action_type_id = ame_util.mandAttActionTypeId and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.attribute_type = ame_util.stringAttributeType and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select to_number(value_1),
to_number(value_2),
to_number(value_3),
to_number(value_4),
to_number(value_5)
into
attributeIdList(1),
attributeIdList(2),
attributeIdList(3),
attributeIdList(4),
attributeIdList(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
when no_data_found then */ /* striping is not on, user needs to select
from entire string attribute list */
/*
tempIndex := 1;
/* the distinct below is necessary to select a distinct list of attribute
attribute names that are used within a condition */
select distinct
ame_attributes.attribute_id id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages,
ame_conditions
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_conditions.attribute_id = ame_attributes.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
order by name;
/* the distinct below is necessary to select a distinct list of attribute
attribute names that are used within a condition */
select distinct
ame_attributes.attribute_id id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages,
ame_conditions
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_conditions.attribute_id = ame_attributes.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
order by name;
/* the distinct below is necessary to select a distinct list of attribute
attribute names that are used within a condition */
select distinct
ame_attributes.attribute_id id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages,
ame_conditions
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_conditions.attribute_id = ame_attributes.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
ame_conditions.condition_type = conditionTypeIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
order by name;
select condition_id
from ame_conditions
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes
where
ame_attributes.attribute_id not in
(select attribute_id from ame_mandatory_attributes
where
(action_type_id = actionTypeIdIn or
action_type_id = ame_util.mandAttActionTypeId) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select distinct(ame_attributes.attribute_id),
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.item_class_id = headerItemClassIdIn and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.attribute_id not in
(select attribute_id from ame_mandatory_attributes
where
action_type_id = -1 and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.attribute_type,
ame_attributes.name,
ame_attributes.start_date
from
ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.attribute_type,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.attribute_type,
ame_attributes.name,
ame_attributes.start_date
from
ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
ame_util.restrictiveItemEvalAttribute) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.item_class_id = itemClassIdIn and
ame_attribute_usages.application_id = applicationIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.item_class_id = itemClassIdIn and
ame_attribute_usages.application_id = applicationIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select ame_attributes.attribute_id,
ame_attributes.name
from ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = actionTypeIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
(ame_attribute_usages.start_date <= sysdate and
(ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date))
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
ame_attributes.attribute_id not in
(select attribute_id from ame_mandatory_attributes
where action_type_id = -1 and
(ame_mandatory_attributes.start_date <= sysdate and
(ame_mandatory_attributes.end_date is null or sysdate < ame_mandatory_attributes.end_date))) and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
(ame_attribute_usages.start_date <= sysdate and
(ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date))
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
ame_attributes.attribute_id not in
(select attribute_id from ame_mandatory_attributes
where action_type_id = -1 and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages,
ame_item_classes
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attributes.item_class_id = ame_item_classes.item_class_id and
ame_item_classes.item_class_id = headerItemClassIdIn and
ame_attribute_usages.application_id = applicationIdIn and
nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
ame_attributes.attribute_id not in
(select attribute_id from ame_mandatory_attributes
where action_type_id = -1 and
sysdate between ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_item_classes.start_date and
nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
order by ame_attributes.name;
select
ame_attributes.attribute_id attribute_id
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.attribute_type = ame_util.stringAttributeType and
ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by ame_attributes.attribute_id;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes
where
item_class_id = itemClassIdIn and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
minus
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes
where
item_class_id = itemClassIdIn and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
minus
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
minus
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name name
from
ame_attributes
where
approver_type_id not in (perApproverTypeIdIn, fndUserApproverTypeIdIn) and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
select
ame_attributes.attribute_id,
ame_attributes.name,
ame_attributes.attribute_type
from
ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
sysdate between ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
order by name;
updateParentObjectIn in boolean,
queryStringIn in varchar2 default null,
newStartDateIn in date default null,
finalizeIn in boolean default false,
parentVersionStartDateIn in date default null,
createdByIn in integer default null) as
cursor startDateCursor is
select start_date
from ame_attributes
where
attribute_id = attributeIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date))
for update;
select count(*)
into tempCount
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
gets updated (which is after the call to calculateUseCount below).
calculateUse check to see if striping is on. */
/*
AME_STRIPING
if(isStripingAttribute(applicationIdIn => applicationIdIn,
attributeIdIn => attributeIdIn) and
isStripingAttributeIn is null) then */ /* existing striping attribute
has been set to null */
/*
isStripingAttributeChange := ame_util.booleanTrue;
select count(*)
into tempCount2
from ame_attribute_usages
where
attribute_id = attributeIdIn and
applicationI_id = applicationIdIn and
created_by = ame_util.seededDataCreatedById;
insert into ame_attribute_usages(attribute_id,
application_id,
query_string,
use_count,
is_static,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
user_editable,
is_striping_attribute)
values(attributeIdIn,
applicationIdIn,
queryString,
useCount,
staticUsageIn,
createdBy,
sysdate,
currentUserId,
sysdate,
currentUserId,
nvl(newStartDateIn, sysdate),
null,
ame_util.booleanTrue,
isStripingAttributeIn);
raise an error, if not then insert
*/
/*
If any version of the object has created_by = 1, all versions,
including the new version, should. This is a failsafe way to check
whether previous versions of an already end-dated object had
created_by = 1.
*/
currentUserId := ame_util.getCurrentUserId;
select count(*)
into tempCount2
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
created_by = ame_util.seededDataCreatedById;
insert into ame_attribute_usages(attribute_id,
application_id,
query_string,
use_count,
is_static,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
user_editable)
values(attributeIdIn,
applicationIdIn,
queryString,
useCount,
staticUsageIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
startDate2,
null,
ame_util.booleanTrue);
if(updateParentObjectIn) then
attributeDescription := getDescription(attributeIdIn => attributeIdIn);
update ame_attributes
set
last_updated_by = currentUserId,
last_update_date = startDate2,
last_update_login = currentUserId,
end_date = startDate2
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
/* select every application having a rule that uses the approval type */
cursor getApplicationId (actionTypeIdIn in integer) is
/* the distinct is necessary because of the possibility that multiple rules
within an application will use the specified approval type */
select distinct ame_rule_usages.item_id
from ame_action_usages,
ame_actions,
ame_rule_usages
where
ame_action_usages.action_id = ame_actions.action_id and
ame_action_usages.rule_id = ame_rule_usages.rule_id and
ame_actions.action_type_id = actionTypeIdIn 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_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 application_id
from ame_attribute_usages
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into applicationCount
from ame_attribute_usages
where attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into tempCount1
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = getAppRec.item_id and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_mandatory_attributes
where
attribute_id = attributeIdIn and
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
insert into ame_mandatory_attributes
(attribute_id,
action_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values
(attributeIdIn,
actionTypeIdIn,
createdBy,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null);
/* Call updateUseCount after creating the new attribute requirements. */
tempIndex := 1;
updateUseCount(attributeIdIn => attributeIdIn,
applicationIdIn => tempApplication.application_id,
finalizeIn => commitData);
update ame_string_values
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
condition_id in (select condition_id
from ame_conditions
where processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) and
attribute_id = attributeIdIn) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update ame_conditions
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
attribute_id = attributeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update ame_attributes
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
attribute_id = attributeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
select application_id
from ame_attribute_usages
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
update ame_mandatory_attributes
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
attribute_id = attributeIdIn and
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
/* Call updateUseCount after removing the attribute requirements. */
for tempApplication in applicationCursor(attributeIdIn => attributeIdIn) loop
updateUseCount(attributeIdIn => attributeIdIn,
applicationIdIn => tempApplication.application_id,
finalizeIn => finalizeIn);
allowAttributeUsageDeleteIn in boolean default false,
finalizeIn in boolean default false,
deleteConditionsIn in boolean default false,
itemClassIdIn in integer) as
cursor startDateCursor is
select start_date
from ame_attributes
where
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
if not allowAttributeUsageDeleteIn then
if(isMandatory(attributeIdIn => attributeIdIn)) then
raise isMandatoryException;
/* Not active, either not mandatory or allowed to delete, so delete the usage. */
update ame_attribute_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
(3) is not mandatory delete the attribute (not just the usage).
*/
if(hasUsage(attributeIdIn => attributeIdIn,
applicationIdIn => applicationIdIn) or
isRequired(attributeIdIn => attributeIdIn) or
isMandatory(attributeIdIn => attributeIdIn)) then
attributeName := getName(attributeIdIn => attributeIdIn);
update ame_attributes
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
attribute_id = attributeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select ame_attribute_usages.attribute_id,
ame_attribute_usages.is_static,
ame_attribute_usages.query_string,
ame_attribute_usages.end_date,
ame_attributes.line_item
from ame_attributes,
ame_attribute_usages
where
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
(ame_attribute_usages.start_date <= sysdate and
(ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date)) and
(ame_attributes.start_date <= sysdate and
(ame_attributes.end_date is null or sysdate < ame_attributes.end_date));
deleteStripeSetIdZeroIn => lastStripingAttributeIn);
procedure updateUseCount(attributeIdIn in integer,
applicationIdIn in integer,
finalizeIn in boolean default true) as
currentUserId integer;
update ame_attribute_usages
set use_count = useCount
where
application_id = applicationIdIn and
attribute_id = attributeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
routineNameIn => 'updateUseCount',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateUseCount',
exceptionNumberIn => sqlcode,
exceptionStringIn => '(attribute ID ' ||
attributeIdIn||
') ' ||
sqlerrm);
end updateUseCount;
procedure updateUseCount2(ruleIdIn in integer,
applicationIdIn in integer) as
attributeIds ame_util.idList;
update ame_attribute_usages
set use_count = useCount - 1
where
application_id = applicationIdIn and
attribute_id = attributeIds(i) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
routineNameIn => 'updateUseCount2',
exceptionNumberIn => sqlcode,
exceptionStringIn => '(rule ID ' ||
ruleIdIn||
') ' ||
sqlerrm);
end updateUseCount2;