[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select item_id_query
into itemIdQuery
from
ame_item_class_usages
where
application_id = applicationIdIn and
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select start_date
into startDate
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into stripeSetCount
from ame_stripe_sets
where
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select ame_conditions.condition_id
into conditionId
from ame_conditions,
ame_string_values
where
ame_conditions.condition_id = ame_string_values.condition_id and
ame_conditions.condition_type = ame_util.ordinaryConditionType and
ame_conditions.attribute_id = attributeIdIn and
ame_string_values.string_value = stringValueIn and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_string_values.start_date and
nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate) ;
select application_id
into appId
from ame_calling_apps
where
fnd_application_id = fndAppIdIn and
((transaction_type_id is null and transactionTypeIdIn is null) or
transaction_type_id = transactionTypeIdIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select application_id
into appId
from ame_calling_apps
where
application_name = nameIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select application_name
into applicationName
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
function getAttributeQuery(selectClauseIn in varchar2) return ame_util.queryCursor as
queryCursor ame_util.queryCursor;
sqlStatement := selectClauseIn;
select count(*)
into ruleCount
from ame_rules,
ame_rule_usages,
ame_rule_stripe_sets
where
ame_rule_usages.item_id = applicationIdIn and
ame_rules.rule_id = ame_rule_stripe_sets.rule_id and
ame_rules.rule_id = ame_rule_usages.rule_id and
(ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date) and
(ame_rule_stripe_sets.end_date is null or sysdate < ame_rule_stripe_sets.end_date) and
(ame_rules.end_date is null or sysdate < ame_rules.end_date);
select ltrim(fnd_application_vl.application_name)
into description
from fnd_application_vl
where application_id = fndAppIdIn;
select ltrim(fnd_application_vl.application_name)
into description
from
fnd_application_vl,
ame_calling_apps
where
fnd_application_vl.application_id = ame_calling_apps.fnd_application_id and
ame_calling_apps.application_id = applicationIdIn and
sysdate between ame_calling_apps.start_date and
nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
order by fnd_application_vl.application_name;
select fnd_application_id
into fndApplicationId
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into itemClassCount
from ame_item_classes
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select item_class_id
into itemId
from ame_item_classes
where
upper(name) = upper(itemClassNameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select item_id_query
into itemIdQuery
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select name
into itemName
from ame_item_classes
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select line_item_id_query
into lineItemQueryString
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select nvl(max(item_class_order_number), 0)
into orderNumber
from ame_item_class_usages
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_order_number
into itemClassOrderNumber
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into tempCount
from ame_item_class_usages
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select item_class_par_mode
into itemClassParMode
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select start_date
into startDate
from ame_item_classes
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_sublist_mode
into itemClassSublistMode
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select ame_item_classes.item_class_id
into itemId
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.name <> ame_util.headerItemClassName 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);
select transaction_type_id
into tempTransactionTypeId
from ame_calling_apps
where application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into lineItemCount
from
ame_attribute_usages,
ame_attributes
where
ame_attributes.line_item = ame_util.booleanTrue and
ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_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 count(*)
into stripeCount
from ame_stripe_sets
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into attributeUsageCount
from
ame_attribute_usages,
ame_attributes
where
ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = itemClassIdIn and
sysdate between ame_attribute_usages.start_date and
nvl(ame_attribute_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 count(*)
into ruleUsageCount
from
ame_rule_usages,
ame_rules
where
ame_rule_usages.rule_id = ame_rules.rule_id and
ame_rule_usages.item_id = applicationIdIn and
ame_rules.item_class_id = itemClassIdIn and
sysdate between ame_rule_usages.start_date and
nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_rules.start_date and
nvl(ame_rules.end_date - ame_util.oneSecond, sysdate);
select count(*)
into appCount
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into useCount
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select created_by
into createdByValue
from ame_calling_apps
where application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into isStripingOn
from ame_config_vars
where application_id = applicationIdIn and
variable_name = ame_util.useRuleStripingConfigVar and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select max(stripe_set_id + 1) into stripeSetId from ame_stripe_sets;
insert into ame_stripe_sets(application_id,
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
security_group_id)
values(applicationIdIn,
stripeSetId,
attributeValuesIn(1),
attributeValuesIn(2),
attributeValuesIn(3),
attributeValuesIn(4),
attributeValuesIn(5),
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null,
null);
select stripe_set_id
into stripeSetId
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id <> 0 and
((value_1 is null and attributeValuesIn(1) is null) or value_1 = attributeValuesIn(1)) and
((value_2 is null and attributeValuesIn(2) is null) or value_2 = attributeValuesIn(2)) and
((value_3 is null and attributeValuesIn(3) is null) or value_3 = attributeValuesIn(3)) and
((value_4 is null and attributeValuesIn(4) is null) or value_4 = attributeValuesIn(4)) and
((value_5 is null and attributeValuesIn(5) is null) or value_5 = attributeValuesIn(5)) and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into tempCount
from ame_item_classes
where
upper(name) = upper(itemClassNameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select item_class_id
into itemClassId
from ame_item_classes
where
(itemClassIdIn is null or item_class_id <> itemClassIdIn) and
name = itemClassName and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into itemClassCount
from ame_item_classes
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into tempCount
from ame_item_classes
where
item_class_id = itemClassId and
created_by = ame_util.seededDataCreatedById;
insert into ame_item_classes(item_class_id,
name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(itemClassId,
itemClassName,
createdBy,
newStartDateIn,
currentUserId,
newStartDateIn,
currentUserId,
newStartDateIn,
null);
select count(*)
into tempCount
from ame_item_class_usages
where
application_id = applicationIdIn and
item_class_order_number = orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into itemClassCount
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.name <> ame_util.headerItemClassName 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);
select count(*)
into tempCount
from ame_config_vars
where
application_id = applicationIdIn and
variable_name = variableNameIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select
ame_condition_usages.rule_id,
ame_string_values.string_value
from
ame_condition_usages,
ame_rule_usages,
ame_string_values
where
ame_rule_usages.item_id = applicationIdIn and
ame_rule_usages.rule_id = ame_condition_usages.rule_id and
ame_string_values.condition_id = ame_condition_usages.condition_id and
ame_condition_usages.condition_id in
(select condition_id
from ame_conditions
where
attribute_id = attributeIdIn and
*/
/* The condition only has one string value. */
/*
(select count(*) from ame_string_values
where
ame_string_values.condition_id = ame_conditions.condition_id and
sysdate between ame_string_values.start_date and
nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate)
) = 1 and
sysdate between ame_conditions.start_date and
nvl(ame_conditions.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))) 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_string_values.start_date and
nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate)
order by rule_id; */
select rule_id
from ame_rule_usages
where
item_id = applicationIdIn and
rule_id not in
(select ame_condition_usages.rule_id
from
ame_condition_usages,
ame_rule_usages ru2
where
ru2.item_id = applicationIdIn and
ru2.rule_id = ame_condition_usages.rule_id and
ame_condition_usages.condition_id in
(select condition_id
from ame_conditions
where
attribute_id = attributeIdIn and
*/
/* The condition only has one string value. */
/*
(select count(*)
from ame_string_values
where
ame_string_values.condition_id = ame_conditions.condition_id and
(ame_string_values.start_date <= sysdate and
(ame_string_values.end_date is null or sysdate < ame_string_values.end_date))) = 1 and
(ame_conditions.start_date <= sysdate and
(ame_conditions.end_date is null or sysdate < ame_conditions.end_date))) and
(ru2.start_date <= sysdate and
(ru2.end_date is null or sysdate < ru2.end_date)) and
(ame_condition_usages.start_date <= sysdate and
(ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date))) and
(ame_rule_usages.start_date <= sysdate and
(ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date));
select
value_1,
value_2,
value_3,
value_4,
value_5
from
ame_stripe_sets,
ame_rule_stripe_sets
where
ame_stripe_sets.stripe_set_id = ame_rule_stripe_sets.stripe_set_id and
ame_stripe_sets.application_id = applicationIdIn and
ame_rule_stripe_sets.rule_id = ruleIdIn and
*/
/*
Here we want a <= comparison for the end dates, because what we're querying for
stripe sets that were end-dated at tempDate, which gets passed to this cursor as
stripeSetDateIn.
*/
/*
(ame_stripe_sets.start_date <= stripeSetDateIn and stripeSetDateIn <= ame_stripe_sets.end_date) and
(ame_rule_stripe_sets.start_date <= stripeSetDateIn and stripeSetDateIn <= ame_rule_stripe_sets.end_date);
select count(*)
into tempIndex
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
is_striping_attribute = ame_util.booleanTrue and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into stripingAttributeCount
from ame_attribute_usages
where
application_id = applicationIdIn and
is_striping_attribute = ame_util.booleanTrue and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
stripe sets, with end_date = tempDate. (The order of update statements matters!)
*/
/*
getStripingAttributeIds(applicationIdIn => applicationIdIn,
stripingAttributeIdsOut => stripingAttributeIds);
update ame_rule_stripe_sets
set end_date = tempDate
where
stripe_set_id in
(select stripe_set_id
from ame_stripe_sets
where
application_id = applicationIdIn and
(start_date <= tempDate and
(end_date is null or tempDate < end_date))) and
(start_date <= tempDate and
(end_date is null or tempDate < end_date));
update ame_stripe_sets
set end_date = tempDate
where
application_id = applicationIdIn and
(start_date <= tempDate and
(end_date is null or tempDate < end_date));
select start_date
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
update ame_calling_apps
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
select count(*)
into tempCount
from ame_calling_apps
where
application_id = applicationIdIn and
created_by = ame_util.seededDataCreatedById;
/* Perform update. */
insert into ame_calling_apps(fnd_application_id,
application_name,
application_id,
transaction_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
line_item_id_query)
values(fndApplicationId,
transactionTypeDescriptionIn,
applicationIdIn,
transactionTypeIdIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
update ame_item_classes
set
last_updated_by = currentUserId,
last_update_date = endDateIn,
last_update_login = currentUserId,
end_date = endDateIn
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select start_date
from ame_item_classes
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_item_class_usages
where
item_class_id = itemClassIdIn 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_item_classes,
ame_item_class_usages
where
ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
ame_item_classes.item_class_id = itemClassIdIn and
ame_item_class_usages.application_id = applicationIdIn and
ame_item_class_usages.item_id_query = itemClassIdQueryIn and
ame_item_class_usages.item_class_par_mode = itemClassParModeIn and
ame_item_class_usages.item_class_sublist_mode = itemClassSublistModeIn and
ame_item_class_usages.item_class_order_number = orderNumberIn 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) ;
/* Get current values as necessary for update. */
newStartAndEndDate := sysdate;
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
updateOnlyICModified boolean;
updateOnlyICModified := false;
updateOnlyICModified := true;
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_item_class_usages(application_id,
item_class_id,
item_id_query,
item_class_order_number,
item_class_par_mode,
item_class_sublist_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationIdIn,
itemClassIdIn,
itemClassIdQueryIn,
newOrderNumber,
itemClassParModeIn,
itemClassSublistModeIn,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
if(updateOnlyICModified) then
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_item_class_usages(application_id,
item_class_id,
item_id_query,
item_class_order_number,
item_class_par_mode,
item_class_sublist_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationIdIn,
itemClassIdIn,
itemClassIdQueryIn,
orderNumberIn,
itemClassParModeIn,
itemClassSublistModeIn,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
select count(*)
into tempCount
from ame_calling_apps
where
fnd_application_id = fndAppIdIn and
transaction_type_id is null and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_calling_apps
where
fnd_application_id = fndAppIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_calling_apps
where
fnd_application_id = fndAppIdIn and
transaction_type_id = transTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_calling_apps
where
upper(application_name) = upper(transTypeDescIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into useCount
from ame_rule_stripe_sets
where
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
update ame_stripe_sets
set end_date = sysdate
where
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
delete from ame_exceptions_log
where
application_id = applicationIdIn and
transaction_id = transactionIdIn;
update ame_config_vars
set end_date = sysdate
where
application_id = applicationIdIn and
variable_name = variableNameIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
delete from ame_exceptions_log
where application_id = applicationIdIn;
delete from ame_exceptions_log
where
application_id is null and
transaction_id is null;
select item_class_id, item_class_order_number
from ame_item_class_usages
where
application_id = applicationIdIn and
item_class_order_number > orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by item_class_order_number;
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
item_class_id = itemClassIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_item_class_usages(application_id,
item_class_id,
item_id_query,
item_class_order_number,
item_class_par_mode,
item_class_sublist_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationIdIn,
itemClassIds(i),
itemClassIdQuery,
(orderNumbers(i) - 1),
itemClassParMode,
itemClassSublistMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
attribute must be selected for the input transaction type, before this
procedure is called.
*/
/*
cursor ruleCursor(applicationIdIn in integer) is
*/
/* This cursor fetches the IDs of all rules used by the input transaction type. */
/*
select rule_id
from ame_rule_usages
where
item_id = applicationIdIn and
(start_date <= sysdate and (end_date is null or sysdate < end_date));
select
ame_conditions.attribute_id,
ame_conditions.condition_id
from
ame_conditions,
ame_condition_usages
where
ame_condition_usages.rule_id = ruleIdIn and
ame_condition_usages.condition_id = ame_conditions.condition_id and
ame_conditions.condition_type = ame_util.ordinaryConditionType and
(ame_conditions.attribute_id = stripingAttribute1IdIn or
ame_conditions.attribute_id = stripingAttribute2IdIn or
ame_conditions.attribute_id = stripingAttribute3IdIn or
ame_conditions.attribute_id = stripingAttribute4IdIn or
ame_conditions.attribute_id = stripingAttribute5IdIn) and
(select count(*)
from ame_string_values sv2
where sv2.condition_id = ame_conditions.condition_id and
(sv2.start_date <= sysdate and (sv2.end_date is null or sysdate < sv2.end_date))) = 1 and
(ame_conditions.start_date <= sysdate and
(ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
(ame_condition_usages.start_date <= sysdate and
(ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date));
select distinct
ame_conditions.condition_id,
ame_string_values.string_value
from
ame_conditions,
ame_condition_usages,
ame_rule_usages,
ame_string_values
where
ame_conditions.attribute_id = stripingAttributeIdIn and
ame_conditions.condition_type = ame_util.ordinaryConditionType and
ame_string_values.condition_id = ame_conditions.condition_id and
ame_condition_usages.condition_id = ame_conditions.condition_id and
ame_condition_usages.rule_id = ame_rule_usages.rule_id and
ame_rule_usages.item_id = applicationIdIn and
(select count(*)
from ame_string_values sv2
where sv2.condition_id = ame_conditions.condition_id and
(sv2.start_date <= sysdate and (sv2.end_date is null or sysdate < sv2.end_date))) = 1 and
(ame_conditions.start_date <= sysdate and
(ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
(ame_condition_usages.start_date <= sysdate and
(ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date)) and
(ame_rule_usages.start_date <= sysdate and
(ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date)) and
(ame_string_values.start_date <= sysdate and
(ame_string_values.end_date is null or sysdate < ame_string_values.end_date));
tempConditionIds.delete;
tempConditionValues.delete;
tempAttributeIds.delete;
tempConditionIds.delete;
errorMessage := 'At least one striping attribute must be selected to enable rule striping. ';
select to_number(value_1),
to_number(value_2),
to_number(value_3),
to_number(value_4),
to_number(value_5)
into
attributeId1,
attributeId2,
attributeId3,
attributeId4,
attributeId5
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select
variable_name,
description
from ame_config_vars
where
application_id is null and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by variable_name;
select
item_class_id,
name
from
ame_item_classes
where
(start_date <= sysdate and
(end_date is null or sysdate < end_date))
minus
select
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
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 name;
select
application_id,
substrb(ltrim(application_name),1,99) application_name
from fnd_application_vl
order by application_name;
select ame_item_classes.item_class_id,
ame_item_classes.name,
ame_item_class_usages.item_class_order_number
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
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_class_usages.item_class_order_number;
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))
order by stripe_set_id;
'select stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5' ||
' from ame_stripe_sets where application_id = ' ||
applicationIdIn ||
constraintValues ||
' and stripe_set_id <> 0 and start_date <= sysdate and (end_date is null or sysdate < end_date)';
attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
select
to_number(value_1),
to_number(value_2),
to_number(value_3),
to_number(value_4),
to_number(value_5)
into
valueColumns(1),
valueColumns(2),
valueColumns(3),
valueColumns(4),
valueColumns(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select
to_number(value_1),
to_number(value_2),
to_number(value_3),
to_number(value_4),
to_number(value_5)
into
valueColumns(1),
valueColumns(2),
valueColumns(3),
valueColumns(4),
valueColumns(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select value_1,
value_2,
value_3,
value_4,
value_5
into
attributeIds(1),
attributeIds(2),
attributeIds(3),
attributeIds(4),
attributeIds(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
tempValueList.delete;
'select distinct(nvl(value_' ||
i || ', ''NULL''))' ||
' from ame_stripe_sets where application_id = ' ||
applicationIdIn ||
' and stripe_set_id <> 0 and start_date <= sysdate and (end_date is null or sysdate < end_date)';
attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
select value_1,
value_2,
value_3,
value_4,
value_5
into
attributeIds(1),
attributeIds(2),
attributeIds(3),
attributeIds(4),
attributeIds(5)
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
tempValueList.delete;
'select value_' ||
i ||
' from ame_stripe_sets where application_id = ' ||
applicationIdIn ||
' and stripe_set_id = ' || stripeSetIdIn || ' and start_date <= sysdate and (end_date is null or sysdate < end_date)';
attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
select
value_1,
value_2,
value_3,
value_4,
value_5
into
stripeValue1Out,
stripeValue2Out,
stripeValue3Out,
stripeValue4Out,
stripeValue5Out
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and (end_date is null or sysdate < end_date));
select *
from ame_exceptions_log
where
application_id = applicationIdIn and
transaction_id = transactionIdIn
order by log_id desc;
select *
from ame_exceptions_log
where application_id = applicationIdIn
order by log_id desc;
select *
from ame_exceptions_log
where application_id = applicationIdIn
order by
package_name,
routine_name;
select
fnd_application_id,
application_id,
created_by,
application_name,
transaction_type_id
from
ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_name;
select ame_item_classes.item_class_id,
ame_item_classes.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
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_class_usages.item_class_order_number;
select ame_item_classes.item_class_id,
ame_item_classes.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.name <> ame_util.headerItemClassName 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_class_usages.item_class_order_number;
select ame_item_classes.item_class_id,
ame_item_classes.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
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_class_usages.item_class_order_number,
ame_item_classes.name;
select ame_item_classes.item_class_id,
ame_item_classes.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.name <> ame_util.headerItemClassName 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_class_usages.item_class_order_number;
select ame_item_classes.item_class_id
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
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_class_usages.item_class_order_number;
select
log_id,
package_name,
routine_name,
exception_number,
exception_string
from ame_exceptions_log
where
transaction_id is null and
application_id is null
order by log_id desc;
select
wf_item_activity_statuses.item_key,
wf_item_activity_statuses.error_name,
wf_item_activity_statuses.error_message,
wf_item_activity_statuses.error_stack
from
wf_item_activity_statuses,
wf_items
where
wf_item_activity_statuses.item_type = workflowItemType and
wf_item_activity_statuses.activity_status = 'ERROR' and
wf_item_activity_statuses.error_stack like (wf_core.newline || 'AME_%') and
wf_item_activity_statuses.item_type = wf_items.item_type and
wf_item_activity_statuses.item_key = wf_items.item_key and
wf_items.end_date is null;
select
wf_item_activity_statuses.item_key, /* We need to select this column to use the same cursor. */
wf_item_activity_statuses.error_name,
wf_item_activity_statuses.error_message,
wf_item_activity_statuses.error_stack
from
wf_item_activity_statuses,
wf_items
where
wf_item_activity_statuses.item_type = workflowItemType and
wf_item_activity_statuses.item_key = workflowItemKey and
wf_item_activity_statuses.activity_status = 'ERROR' and
wf_item_activity_statuses.error_stack like (wf_core.newline || 'AME_%') and
wf_item_activity_statuses.item_type = wf_items.item_type and
wf_item_activity_statuses.item_key = wf_items.item_key and
wf_items.end_date is null;
select item_class_id, item_class_order_number
from ame_item_class_usages
where
application_id = applicationIdIn and
item_class_id <> itemClassIdIn and
item_class_order_number >= orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by item_class_order_number;
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
item_class_id = itemClassIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_item_class_usages(application_id,
item_class_id,
item_id_query,
item_class_order_number,
item_class_par_mode,
item_class_sublist_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationIdIn,
itemClassIds(i),
itemClassIdQuery,
(orderNumbers(i) + 1),
itemClassParMode,
itemClassSublistMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
updateParentObjectIn in boolean,
newStartDateIn in date,
finalizeIn in boolean default false,
parentVersionStartDateIn in date default null) as
cursor startDateCursor is
select start_date
from ame_item_classes
where
item_class_id = itemClassIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date))
for update;
lastUpdatedBy integer;
select count(*)
into tempCount
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select count(*)
into tempCount2
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
created_by = ame_util.seededDataCreatedById;
lastUpdatedBy := currentUserId;
lastUpdatedBy := ame_util.seededDataCreatedById;
insert into ame_item_class_usages(application_id,
item_class_id,
item_id_query,
item_class_order_number,
item_class_par_mode,
item_class_sublist_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationIdIn,
itemClassIdIn,
itemClassIdQueryIn,
orderNumber,
itemClassParModeIn,
itemClassSublistModeIn,
createdBy,
newStartDateIn,
lastUpdatedBy,
newStartDateIn,
currentUserId,
newStartDateIn,
null);
if(updateParentObjectIn) then
name := getItemClassName(itemClassIdIn => itemClassIdIn);
update ame_item_classes
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select count(*)
into attributeCount
from ame_stripe_sets
where
application_id = applicationIdIn and
((value_1 is null and newStripedAttributesSetIn(1) is null) or value_1 = newStripedAttributesSetIn(1)) and
((value_2 is null and newStripedAttributesSetIn(2) is null) or value_2 = newStripedAttributesSetIn(2)) and
((value_3 is null and newStripedAttributesSetIn(3) is null) or value_3 = newStripedAttributesSetIn(3)) and
((value_4 is null and newStripedAttributesSetIn(4) is null) or value_4 = newStripedAttributesSetIn(4)) and
((value_5 is null and newStripedAttributesSetIn(5) is null) or value_5 = newStripedAttributesSetIn(5)) and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
insert into ame_stripe_sets(application_id,
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
security_group_id)
values(applicationIdIn,
0,
newStripedAttributesSetIn(1),
newStripedAttributesSetIn(2),
newStripedAttributesSetIn(3),
newStripedAttributesSetIn(4),
newStripedAttributesSetIn(5),
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null,
null);
select
to_char(ame_attributes.start_date) start_date
from
ame_attributes,
ame_mandatory_attributes
where
ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
ame_attributes.attribute_id = attributeIdIn 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)
for update;
select ame_applications_s.nextval
into applicationId
from dual;
insert into ame_calling_apps(fnd_application_id,
application_name,
application_id,
transaction_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
line_item_id_query)
values(fndApplicationIdIn,
transTypeDescIn,
applicationId,
transactionTypeIdIn,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
/* verifies that the mandatory attribute has not been updated */
/* the versionStartDate is in sync with the start date due to how the data
was retrieved for each */
if(versionStartDates(tempIndex) = startDate) then
attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIds(i));
updateParentObjectIn => true,
finalizeIn => false);
updateParentObjectIn => true,
finalizeIn => false);
insert into ame_action_type_config(application_id,
action_type_id,
voting_regime,
order_number,
chain_ordering_mode,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationId,
actionTypeIds(i),
ame_util.serializedVoting,
orderNumber,
ame_util.sequentialChainsMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
insert into ame_approval_group_config(application_id,
approval_group_id,
voting_regime,
order_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(applicationId,
groupList(i),
ame_util.serializedVoting,
i,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
deleteStripeSetIdZeroIn in boolean,
commitIn in boolean default false) as
cursor getAllStripeSetsCursor(applicationIdIn in integer) is
select stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5
from ame_stripe_sets
where
application_id = applicationIdIn and
stripe_set_id <> 0 and
(start_date <= sysdate and
(end_date is null or sysdate < end_date))
order by stripe_set_id;
if(deleteStripeSetIdZeroIn) then
update ame_stripe_sets
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
update ame_stripe_sets
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where
application_id = applicationIdIn and
stripe_set_id = getAllStripeSetsRec.stripe_set_id and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5
from ame_stripe_sets
where
stripe_set_id <> 0 and
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date))
order by
value_1,
value_2,
value_3,
value_4,
value_5; */
select rule_id
from ame_rule_stripe_sets
where
ame_rule_stripe_sets.stripe_set_id = stripeSetIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
insertRow boolean;
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
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
/* Don't update upperLimit below. */
/* End date all current stripe sets, and the zero-ID row containing the striping-attribute IDs. */
/*
update ame_stripe_sets
set end_date = sysdate
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
/* Insert a new zero-ID row, compacted. */
/*
if(columnIndex < 5) then
for i in columnIndex .. 4 loop
stripingAttributeIds(i) := stripingAttributeIds(i + 1);
insert into ame_stripe_sets(
application_id,
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date) values(
applicationIdIn,
0,
to_number(stripingAttributeIds(1)),
to_number(stripingAttributeIds(2)),
to_number(stripingAttributeIds(3)),
to_number(stripingAttributeIds(4)),
to_number(stripingAttributeIds(5)),
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null);
/* Initialize lastStripingAttValues so the first stripe set will always get inserted. */
/* lastStripeSetId := null; */
inserted, the ith row must be inserted.
*/
/*
insertRow := false;
insertRow := true;
if((not insertRow) and
columnIndex <> 2 and
((stripingAttributeValues2(i) is null and lastStripingAttValues(2) is not null) or
((stripingAttributeValues2(i) is not null and lastStripingAttValues(2) is null)) or
(stripingAttributeValues2(i) <> lastStripingAttValues(2)))) then
insertRow := true;
if((not insertRow) and
columnIndex <> 3 and
((stripingAttributeValues3(i) is null and lastStripingAttValues(3) is not null) or
((stripingAttributeValues3(i) is not null and lastStripingAttValues(3) is null)) or
(stripingAttributeValues3(i) <> lastStripingAttValues(3)))) then
insertRow := true;
if((not insertRow) and
columnIndex <> 4 and
((stripingAttributeValues4(i) is null and lastStripingAttValues(4) is not null) or
((stripingAttributeValues4(i) is not null and lastStripingAttValues(4) is null)) or
(stripingAttributeValues4(i) <> lastStripingAttValues(4)))) then
insertRow := true;
if((not insertRow) and
columnIndex <> 5 and
((stripingAttributeValues5(i) is null and lastStripingAttValues(5) is not null) or
((stripingAttributeValues5(i) is not null and lastStripingAttValues(5) is null)) or
(stripingAttributeValues5(i) <> lastStripingAttValues(5)))) then
insertRow := true;
/* Now insert the ith row if it's new. Remember to update the last-row-inserted data. */
/* if(insertRow) then */
/* Update lastStripeSetId and lastStripingAttValues. */
/*
lastStripeSetId := stripeSetIds(i);
/* Move the rules in this stripe set into the most recently inserted stripe set. */
/*
open stripeSetRuleCursor(stripeSetIdIn => stripeSetIds(i));
update ame_rule_stripe_sets
set end_date = sysdate
where
stripe_set_id = stripeSetIds(i) 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,
start_date,
end_date) values(
ruleIds(i),
lastStripeSetId,
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null);
/* Delete this stripe set. */
/*
stripeSetIds.delete(i);
stripeSetIds.delete(i, upperLimit);
stripingAttributeValues1.delete(i, upperLimit);
stripingAttributeValues2.delete(i, upperLimit);
stripingAttributeValues3.delete(i, upperLimit);
stripingAttributeValues4.delete(i, upperLimit);
stripingAttributeValues5.delete(i, upperLimit);
/* Bulk insert the stripe sets. */
/*
forall i in 1 .. stripeSetIds.count
insert into ame_stripe_sets(
application_id,
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date) values(
applicationIdIn,
stripeSetIds(i),
stripingAttributeValues1(i),
stripingAttributeValues2(i),
stripingAttributeValues3(i),
stripingAttributeValues4(i),
stripingAttributeValues5(i),
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null);
errorMessage := 'The attribute you selected to remove was not ' ||
'found within the current stripe set. Please ' ||
'contact your systems administrator.';
select start_date
from ame_item_classes
where
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_item_class_usages
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select item_class_order_number
into orderNumber
from ame_item_class_usages
where
application_id = applicationIdIn and
item_class_id = itemClassIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
/* subtract 1 from the order number for those above the one being deleted */
decrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
orderNumberIn => orderNumber,
finalizeIn => false);
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
item_class_id = itemClassIdIn and
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_item_classes
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
item_class_id = itemClassIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select start_date
from ame_calling_apps
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select rule.rule_id rule_id,
start_date
from ame_rule_usages rule
where
item_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
allowAttributeUsageDeleteIn => true,
finalizeIn => false,
itemClassIdIn => itemClassId);
update ame_calling_apps
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_config_vars
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_action_type_config
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_approval_group_config
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_item_class_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
application_id = applicationIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
procedure updateStripingAttIds(applicationIdIn in integer,
stripedAttributesIn in ame_util.stringList) as
currentUserId integer;
select count(*)
into stripeCount
from ame_stripe_sets
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
update ame_stripe_sets
set end_date = sysdate
where
application_id = applicationIdIn and
(start_date <= sysdate and
(end_date is null or sysdate < end_date));
select max(stripe_set_id + 1) into stripeSetId from ame_stripe_sets;
insert into ame_stripe_sets(application_id,
stripe_set_id,
value_1,
value_2,
value_3,
value_4,
value_5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
security_group_id)
values(applicationIdIn,
0,
to_char(newStripingAttributeIds(1)),
to_char(newStripingAttributeIds(2)),
to_char(newStripingAttributeIds(3)),
to_char(newStripingAttributeIds(4)),
to_char(newStripingAttributeIds(5)),
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
null,
null);
routineNameIn => 'updateStripingAttIds',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateStripingAttIds;
procedure updateStripingAttUseCount(applicationIdIn in integer) as
attributeCount integer;
routineNameIn => 'updateStripingAttUseCount',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateStripingAttUseCount;