The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into useCount
from ame_actions
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description_query
into descriptionQuery
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select dynamic_description
into dynamicDescription
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_type_id
into actionTypeId
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_type_id
into actionTypeId
from ame_action_types
where
upper(name) = upper(actionTypeNameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description
into description
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select max(ame_action_type_config.order_number)
into orderNumber
from ame_action_type_config,
ame_action_type_usages,
ame_action_types
where
ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_usages.rule_type = ruleTypeIn and
ame_action_type_config.application_id = applicationIdIn and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
if(actionTypeIdIn = ame_util.nullInsertionActionTypeId) then
return('no action type');
select name
into name
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_action_types.name
into name
from ame_actions,
ame_action_types
where
ame_actions.action_type_id = ame_action_types.action_type_id and
action_id = actionIdIn and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate);
select procedure_name
into procedureName
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select order_number
into orderNumber
from ame_action_type_config
where
action_type_id = actionTypeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select created_by
into createdBy
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select rule_type
into ruleType
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select chain_ordering_mode
into chainOrderingMode
from ame_action_type_config
where
action_type_id = actionTypeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_action_type_config
where
action_type_id = actionTypeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description
into description
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description
into description
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description_query,
parameter,
parameter_two
into descriptionQuery,
parameterOne,
parameterTwo
from ame_actions,
ame_action_types
where
ame_actions.action_type_id = ame_action_types.action_type_id and
action_id = actionIdIn and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate);
select action_type_id
into actionTypeId
from ame_action_types
where name = ame_util.groupChainApprovalTypeName and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_id
into actionId
from ame_actions
where
action_type_id = actionTypeIdIn and
((parameterIn is null and parameter is null) or parameter = parameterIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select parameter
into parameter
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select parameter_two
into parameterTwo
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_type_id
into actionTypeId
from ame_action_types
where name = ame_util.preApprovalTypeName and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_type_id
into actionTypeId
from ame_action_types
where name = ame_util.postApprovalTypeName and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select voting_regime
into votingRegime
from ame_action_type_config
where
action_type_id = actionTypeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
The following select checks that the rule is current, but
not that the action is current. This is intentional. The
assumption is that the rest of the application will never
discover a historical action and try to check whether it
is in use. It now does check the start date
of rules to capture future rule start dates.
*/
select count(*)
into useCount
from ame_rules,
ame_action_usages
where
ame_rules.rule_id = ame_action_usages.rule_id and
ame_action_usages.action_id = actionIdIn and
((sysdate between ame_rules.start_date and
nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_rules.start_date and
ame_rules.start_date < nvl(ame_rules.end_date,ame_rules.start_date + ame_util.oneSecond))) and
((sysdate between ame_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 count(*)
into tempCount
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
rule_type = ame_util.authorityRuleType and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select created_by
into createdByValue
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select action_type_id
into actionTypeId
from ame_action_types
where
(actionTypeIdIn is null or action_type_id <> actionTypeIdIn) and
upper(name) = upper(nameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_action_types_s.nextval into actionTypeId from dual;
select count(*)
into tempCount
from ame_action_types
where
action_type_id = actionTypeId and
created_by = ame_util.seededDataCreatedById;
insert into ame_action_types(action_type_id,
name,
procedure_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
description,
dynamic_description,
description_query)
values(actionTypeId,
nameIn,
procedureNameIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
nvl(newStartDateIn, processingDate),
null,
descriptionIn,
dynamicDescriptionIn,
descriptionQueryIn);
updateParentObjectIn in boolean,
descriptionIn in varchar2 default null,
parameterIn in varchar2 default null,
parameterTwoIn in varchar2 default null,
newStartDateIn in date default null,
finalizeIn in boolean default false,
parentVersionStartDateIn in date default null,
actionIdIn in integer default null,
processingDateIn in date default null) return integer as
cursor startDateCursor is
select start_date
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select count(*)
into actionCount
from ame_actions
where
(actionIdIn is null or action_id <> actionIdIn) and
((parameterIn is null and parameter is null) or parameter = parameterIn) and
((parameterTwoIn is null and parameter_two is null) or parameter_two = parameterTwoIn) and
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select ame_actions_s.nextval into actionId from dual;
select count(*)
into tempCount
from ame_actions
where
action_id = actionId and
created_by = ame_util.seededDataCreatedById;
insert into ame_actions(action_id,
action_type_id,
parameter,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
description,
parameter_two)
values(actionId,
actionTypeIdIn,
parameterIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
nvl(newStartDateIn, startDate),
null,
descriptionIn,
parameterTwoIn);
if(updateParentObjectIn) then
endDate := startDate ;
update ame_action_types
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select count(*)
into tempCount
from ame_action_type_config,
ame_action_type_usages
where
ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_config.application_id = applicationIdIn and
ame_action_type_config.order_number = orderNumberIn and
ame_action_type_usages.rule_type = ruleType and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
select count(*)
into tempCount
from ame_mandatory_attributes
where
action_type_id = actionTypeIdIn and
attribute_id = attributeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
deleteListIn in ame_util.stringList default ame_util.emptyStringList,
finalizeIn in boolean default false) as
cursor startDateCursor is
select start_date
from ame_action_types
where action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
deleteCount integer;
update ame_action_types
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
action_type_id = actionTypeIdIn and
processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn) ;
deleteCount := deleteListIn.count;
if(deleteCount > 0) then
for i in 1..deleteCount loop
if(deleteListIn(i)) like 'req%' then
attributeId := substrb(deleteListIn(i),4,(lengthb(deleteListIn(i))));
elsif(deleteListIn(i)) like 'appr%' then
approverTypeIdList(tempIndex2) :=
to_number(substrb(deleteListIn(i),5,(lengthb(deleteListIn(i)))));
actionIdList(tempIndex) := deleteListIn(i);
ame_action_pkg.getChildVersionStartDate(actionIdIn => deleteListIn(i)));
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select start_date
from ame_action_types
where action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_actions
where action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select count(*)
into tempCount
from ame_actions
where
action_id = actionIdIn and
action_type_id = actionTypeIdIn and
(descriptionIn is null or description = descriptionIn) and
/* parameterIn is null means "set parameter to null,"
because parameter defaults to null */
((parameterIn is null and parameter is null) or upper(parameter) = upper(parameterIn)) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
Always update to parameterIn, even if it's null.
*/
currentUserId := ame_util.getCurrentUserId;
update ame_actions
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
action_id = actionIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
updateParentObjectIn => true,
finalizeIn => false,
parameterIn => parameterIn,
parameterTwoIn => parameterTwoIn,
newStartDateIn => newStartDate,
actionIdIn => actionIdIn,
processingDateIn => processingDate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
deleteListIn in ame_util.stringList default ame_util.emptyStringList,
finalizeIn in boolean default false) as
cursor startDateCursor is
select start_date
from ame_action_types
where action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_action_type_config
where
action_type_id = actionTypeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
deleteListIn => deleteListIn,
processingDateIn => processingDate,
finalizeIn => false);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
updateOnlyATModified boolean;
updateOnlyATModified := false;
updateOnlyATModified := true; /* Order number not modified. */
update ame_action_type_config
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
actionTypeIdIn,
votingRegimeIn,
newOrderNumber,
chainOrderingModeIn,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
if(updateOnlyATModified) then
update ame_action_type_config
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
actionTypeIdIn,
votingRegimeIn,
orderNumberIn,
chainOrderingModeIn,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
select ame_action_type_config.action_type_id,
ame_action_type_config.order_number
from ame_action_type_config,
ame_action_type_usages
where
ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_config.application_id = applicationIdIn and
ame_action_type_config.order_number > orderNumberIn and
ame_action_type_usages.rule_type = ruleTypeIn and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
order by order_number;
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
action_type_id = actionTypeIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
actionTypeIds(i),
votingRegime,
(orderNumbers(i) - 1),
chainOrderingMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select
action_id,
description,
parameter
from ame_actions
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by description;
select
action_id,
parameter
from ame_actions
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by description;
select
ame_action_types.name,
ame_action_types.description_query,
ame_actions.action_id,
ame_actions.description,
ame_actions.parameter,
ame_actions.parameter_two,
ame_actions.created_by
from ame_action_types,
ame_actions
where
ame_action_types.action_type_id = ame_actions.action_type_id and
ame_action_types.action_type_id = actionTypeIdIn and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate)
order by ame_actions.created_by, ame_actions.description;
select ame_actions.action_id,
ame_actions.parameter,
ame_actions.parameter_two,
ame_actions.description,
ame_action_types.dynamic_description,
ame_action_types.description_query
from ame_actions,
ame_action_types
where
ame_actions.action_type_id = ame_action_types.action_type_id and
ame_actions.action_type_id = actionTypeIdIn and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate)
order by ame_actions.created_by, ame_actions.description;
select
action_type_id,
name
from ame_action_types
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by name;
select
action_type_id,
name
from ame_action_types
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by name;
select ame_action_types.action_type_id,
ame_action_types.name,
ame_action_types.description,
ame_action_type_usages.rule_type,
ame_action_type_config.order_number
from ame_action_types,
ame_action_type_usages,
ame_action_type_config
where
ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
ame_action_types.action_type_id = ame_action_type_config.action_type_id and
ame_action_type_config.application_id = applicationIdIn and
ame_action_type_usages.rule_type <> ame_util.exceptionRuleType and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate)
order by ame_action_type_usages.rule_type,
ame_action_type_config.order_number,
ame_action_types.name;
select
action_type_id,
description
from ame_action_types
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by description;
select rule_type
from ame_action_type_usages
where action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by rule_type;
select action_type_id,
rule_type
from ame_action_type_usages
where
/* There are two rows in ame_action_type_usages for list creation rules
and list exception rules. Only grab on row, so here we're
eliminating the exception rule. */
rule_type <> ame_util.exceptionRuleType and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by rule_type, action_type_id;
select
ame_approver_type_usages.approver_type_id,
ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_name
from ame_approver_types,
ame_approver_type_usages
where
ame_approver_types.approver_type_id = ame_approver_type_usages.approver_type_id and
ame_approver_type_usages.action_type_id = actionTypeIdIn and
sysdate between
ame_approver_types.start_date and
nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between
ame_approver_type_usages.start_date and
nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
order by approver_name;
select
ame_action_types.action_type_id action_type_id,
ame_action_types.description description
from
ame_action_types,
ame_action_type_usages
where
ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
((allowProductionsIn = ame_util.yes and ame_action_type_usages.rule_type = ame_util.productionRuleType) or
ame_action_type_usages.rule_type = ruleTypeIn) and
(allowAllApproverTypesIn = ame_util.yes or
ame_action_types.action_type_id in (
select distinct action_type_id
from ame_approver_type_usages
where
(approver_type_id = ame_util.anyApproverType or
approver_type_id in
(select approver_type_id
from ame_approver_types
where
orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
sysdate between
ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate
between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
order by upper(description);
select count(*)
into lineItemClassCount
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.lineItemItemClassName 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
ame_action_types.action_type_id action_type_id,
ame_action_types.description description
from
ame_action_types,
ame_action_type_usages
where
ame_action_types.action_type_id = ame_action_type_usages.action_type_id and
((allowProductionsIn = ame_util.yes and
ame_action_type_usages.rule_type = ame_util.productionRuleType) or
(subOrListModActsForCombRuleIn = ame_util.no and
ame_action_type_usages.rule_type in
(ame_util.authorityRuleType,
ame_util.preListGroupRuleType,
ame_util.postListGroupRuleType)) or
(subOrListModActsForCombRuleIn = ame_util.yes and
ame_action_type_usages.rule_type in
(ame_util.listModRuleType,
ame_util.substitutionRuleType))) and
(allowAllApproverTypesIn = ame_util.yes or
ame_action_types.action_type_id in (
select distinct action_type_id
from ame_approver_type_usages
where
(approver_type_id = ame_util.anyApproverType or
approver_type_id in
(select approver_type_id
from ame_approver_types
where
orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate))) and
sysdate between
ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate
between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
order by upper(description);
select count(*)
into lineItemClassCount
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.lineItemItemClassName 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 ame_action_type_config.action_type_id,
ame_action_type_config.order_number
from ame_action_type_config,
ame_action_type_usages
where
ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_config.application_id = applicationIdIn and
ame_action_type_config.action_type_id <> actionTypeIdIn and
ame_action_type_config.order_number >= orderNumberIn and
ame_action_type_usages.rule_type = ruleTypeIn and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate)
order by order_number;
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
action_type_id = actionTypeIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
actionTypeIds(i),
votingRegime,
(orderNumbers(i) + 1),
chainOrderingMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select application_id
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_id;
select count(*)
into tempCount
from ame_action_type_config
where
application_id = applicationIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select (nvl(max(order_number), 0) + 1)
into orderNumber
from ame_action_type_config,
ame_action_type_usages
where
ame_action_type_config.action_type_id = ame_action_type_usages.action_type_id and
ame_action_type_config.application_id = applicationIds(i) and
ame_action_type_usages.rule_type = ruleTypeIn and
sysdate between ame_action_type_config.start_date and
nvl(ame_action_type_config.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_type_usages.start_date and
nvl(ame_action_type_usages.end_date - ame_util.oneSecond, sysdate);
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,
actionTypeIdIn,
votingRegime,
orderNumber,
chainOrderingMode,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select count(*)
into tempCount
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
created_by = ame_util.seededDataCreatedById;
/* chain of authority so insert two rows,
one for list-creation and one for list-exception */
for i in 1 .. 2 loop
insert into ame_action_type_usages
(action_type_id,
rule_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values (actionTypeIdIn,
i,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
insert into ame_action_type_usages
(action_type_id,
rule_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(actionTypeIdIn,
ruleTypeIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select start_date
from ame_action_types
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select application_id
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_id;
select order_number
into orderNumber
from ame_action_type_config
where
application_id = applicationIds(i) and
action_type_id = actionTypeIdIn 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 */
decrementActionTypeOrdNumbers(applicationIdIn => applicationIds(i),
actionTypeIdIn => actionTypeIdIn,
orderNumberIn => orderNumber,
finalizeIn => false);
update ame_action_types
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_type_id = actionTypeIdIn 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
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_approver_type_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_mandatory_attributes
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select start_date
from ame_actions
where
action_id = actionIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
deleteCount integer;
deleteCount := actionIdIn.count;
for i in 1..deleteCount loop
open startDateCursor2(actionIdIn(i));
update ame_actions
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_id = actionIdIn(i) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update ame_action_types
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_type_id = actionTypeIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
update ame_action_type_usages
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
action_type_id = actionTypeIdIn and
rule_type = ruleTypeIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
select rule_type
from ame_action_type_usages
where
action_type_id = actionTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by 1;