[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
updateDependentGroups updates ame_approval_group_members for the group with
group ID groupIdIn, and all groups depending on it (explicitly or implicitly).
If deleteGroupIn is true, updateDependentGroups also removes (end-dates) the
group with ID groupIdIn from dependent group's item lists.
*/
procedure updateDependentGroups(groupIdIn in integer,
deleteGroupIn in boolean default false);
select created_by
into createdByValue
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select nvl(max(order_number), 0)
into orderNumber
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select nvl(max(order_number), 0)
into orderNumber
from ame_approval_group_config
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select order_number
into orderNumber
from ame_approval_group_config
where
approval_group_id = approvalGroupIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select description
into description
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select approval_group_id
into approvalGroupId
from ame_approval_groups
where
upper(name) = upper(nameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select approval_group_id
into approvalGroupId
from ame_approval_group_items
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select approval_group_item_id
into itemId
from ame_approval_group_items
where
upper(parameter) = upper(parameterIn) and
(upper(parameter_name) = upper(parameterNameIn)) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select parameter
into parameter
from ame_approval_group_items
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select parameter_name
into parameterName
from ame_approval_group_items
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
if(approvalGroupIdIn = ame_util.nullInsertionGroupOrChainId) then
return('''no approval group'' and ''no chain of authority''');
select name
into name
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
nvl(effectiveDateIn, sysdate) between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select query_string
into queryString
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
nvl(effectiveDateIn, sysdate) between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select is_static
into isStatic
from ame_approval_groups
where approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_approval_group_config
where
approval_group_id = approvalGroupIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select start_date
into startDate
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select order_number
into orderNumber
from ame_approval_group_items
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into orderCount
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
within the select list. */
if(newGroupMemberIn) then
return(orderCount + 1);
select voting_regime
into votingRegime
from ame_approval_group_config
where
approval_group_id = approvalGroupIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select
parameter,
parameter_name
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select count(*)
into tempCount
from ame_approval_groups
where ame_approval_groups.approval_group_id = approvalGroupIdIn
and (nameIn is null or upper(name) = upper(nameIn))
and (descriptionIn is null or upper(description) = upper(descriptionIn))
and is_static = isStaticIn
and (queryStringIn is null or upper(query_string) = upper(queryStringIn))
and sysdate between ame_approval_groups.start_date
and nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate);
groupHasBeenUpdated boolean;
then group has been updated. */
if(not oldOrderNumberUnique and orderNumberUniqueIn = ame_util.yes) then
groupHasBeenUpdated := true;
groupHasBeenUpdated := false;
select count(*)
into tempCount
from
ame_approval_groups,
ame_approval_group_config
where
ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
ame_approval_groups.approval_group_id = approvalGroupIdIn and
ame_approval_group_config.application_id = applicationIdIn and
ame_approval_group_config.voting_regime = votingRegimeIn and
ame_approval_group_config.order_number = orderNumberIn and
(nameIn is null or upper(name) = upper(nameIn)) and
(descriptionIn is null or upper(description) = upper(descriptionIn)) and
is_static = isStaticIn and
(queryStringIn is null or upper(query_string) = upper(queryStringIn)) and
sysdate between ame_approval_groups.start_date and
nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_approval_group_config.start_date and
nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate);
if(tempCount = 0 or groupHasBeenUpdated) then
return(true);
select count(*)
into useCount
from
ame_actions,
ame_action_types,
ame_action_usages
where
ame_actions.parameter = to_char(approvalGroupIdIn) and
ame_action_usages.action_id = ame_actions.action_id and
ame_actions.action_type_id = ame_action_types.action_type_id and
ame_action_types.name in (ame_util.preApprovalTypeName,
ame_util.postApprovalTypeName,
ame_util.groupChainApprovalTypeName) and
sysdate between ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
((sysdate between ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
(sysdate < ame_action_usages.start_date and
ame_action_usages.start_date < nvl(ame_action_usages.end_date,
ame_action_usages.start_date + ame_util.oneSecond)));
select is_static
into isStatic
from ame_approval_groups
where approval_group_id = approvalGroupIdIn and
nvl(effectiveDateIn, sysdate) between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into tempCount
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
order_number = orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
updateActionIn in boolean default false) return integer as
approvalGroupId integer;
select approval_group_id
into approvalGroupId
from ame_approval_groups
where
(approvalGroupIdIn is null or approval_group_id <> approvalGroupIdIn) and
upper(name) = upper(nameIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select ame_approval_groups_s.nextval into approvalGroupId from dual;
select count(*)
into tempCount
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
created_by = ame_util.seededDataCreatedById;
/* insert into ame_actions proper values for the approval group */
if(approvalGroupIdIn is null or updateActionIn) then
for i in 1..3 loop
if i = 1 then
actionTypeId := ame_action_pkg.getPreApprovalActionTypeId;
if(updateActionIn) then
select action_id into actionId
from ame_actions
where
parameter = to_char(approvalGroupId) and
action_type_id = actionTypeId and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update of end_date;
update ame_actions
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
parameter = to_char(approvalGroupId) and
action_type_id = actionTypeId and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
updateParentObjectIn => true,
parameterIn => approvalGroupId,
newStartDateIn => processingDate,
finalizeIn => true,
parentVersionStartDateIn => parentVersionStartDate,
actionIdIn => actionId);
updateParentObjectIn => true,
parameterIn => approvalGroupId,
newStartDateIn => processingDate,
parentVersionStartDateIn => parentVersionStartDate,
finalizeIn => true);
insert into ame_approval_groups(approval_group_id,
name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date,
description,
query_string,
is_static)
values(approvalGroupId,
nameIn,
createdBy,
processingDate,
currentUserId,
processingDate,
currentUserId,
nvl(newStartDateIn, processingDate),
null,
descriptionIn,
queryStringIn,
isStaticIn);
updateDependentGroups(groupIdIn => approvalGroupId);
select start_date
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
updateOnlyGIModified boolean;
select count(*)
into tempCount
from ame_approval_group_items
where
(approvalGroupItemIdIn is null or approval_group_item_id <> approvalGroupItemIdIn) and
approval_group_id = approvalGroupIdIn and
upper(parameter) = upper(parameterIn) and
(upper(parameter_name) = upper(parameterNameIn)) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_approval_group_items_s.nextval into approvalGroupItemId from dual;
select count(*)
into tempCount2
from ame_approval_group_items
where
approval_group_item_id = approvalGroupItemId and
created_by = ame_util.seededDataCreatedById;
updateOnlyGIModified := false;
updateOnlyGIModified := true; /* Order number not modified. */
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = newEndDateIn,
last_update_login = currentUserId,
end_date = newEndDateIn
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_approval_group_items(approval_group_item_id,
approval_group_id,
parameter_name,
parameter,
order_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(approvalGroupItemId,
approvalGroupIdIn,
parameterNameIn,
parameterIn,
newOrderNumber,
createdBy,
newStartDateIn,
currentUserId,
newStartDateIn,
currentUserId,
newStartDateIn,
null);
updateOnlyGIModified := true;
if(updateOnlyGIModified) then
if(approvalGroupItemIdIn is not null) then
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = newEndDateIn,
last_update_login = currentUserId,
end_date = newEndDateIn
where
approval_group_item_id = approvalGroupItemIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_approval_group_items(approval_group_item_id,
approval_group_id,
parameter_name,
parameter,
order_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(approvalGroupItemId,
approvalGroupIdIn,
parameterNameIn,
parameterIn,
newOrderNumberIn,
createdBy,
nvl(newStartDateIn, processingDate),
currentUserId,
nvl(newStartDateIn, processingDate),
currentUserId,
nvl(newStartDateIn, processingDate),
null);
updateDependentGroups(groupIdIn => approvalGroupIdIn);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select count(*)
into tempCount
from ame_approval_group_config
where
application_id = applicationIdIn and
order_number = orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
updateActionIn in boolean,
newVersionStartDateIn in date,
finalizeIn in boolean default false) as
approvalGroupId integer;
update ame_approval_groups
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
updateActionIn => updateActionIn,
finalizeIn => false);
/* The new function calls updateDependentGroups, so we don't have to do it here. */
if(finalizeIn) then
commit;
select start_date
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select start_date
from ame_approval_group_config
where
approval_group_id = approvalGroupIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
updateAction boolean;
/* Get current values as necessary for update. */
if(nameIn is null) then
name := getName(approvalGroupIdIn => approvalGroupIdIn);
update the ame_actions table. */
if(nameIn <> getName(approvalGroupIdIn => approvalGroupIdIn)) then
updateAction := true;
updateAction := false;
updateActionIn => updateAction,
finalizeIn => false);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
updateOnlyAGModified boolean;
updateOnlyAGModified := false;
updateOnlyAGModified := true;
update ame_approval_group_config
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
approvalGroupIdIn,
votingRegimeIn,
newOrderNumber,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
if(updateOnlyAGModified) then
update ame_approval_group_config
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
application_id = applicationIdIn and
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
approvalGroupIdIn,
votingRegimeIn,
orderNumberIn,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
currentUserId,
newStartDate,
null);
select start_date
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
update ame_approval_groups
set
last_updated_by = currentUserId,
last_update_date = endDate,
last_update_login = currentUserId,
end_date = endDate
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select approval_group_item_id, order_number
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
order_number > orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_item_id = approvalGroupItemIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_approval_group_items(approval_group_item_id,
approval_group_id,
parameter_name,
parameter,
order_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(approvalGroupItemIds(i),
approvalGroupIdIn,
parameterName,
parameter,
(orderNumbers(i) - 1),
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select approval_group_id, order_number
from ame_approval_group_config
where
application_id = applicationIdIn and
order_number > orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
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
approval_group_id = approvalGroupIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
approvalGroupIds(i),
votingRegime,
(orderNumbers(i) - 1),
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select
approval_group_id,
name
from ame_approval_groups
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select approval_group_item_id
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
select approval_group_id
from ame_approval_groups
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select ame_approval_groups.approval_group_id
from ame_approval_groups,
ame_approval_group_config
where
ame_approval_groups.approval_group_id = ame_approval_group_config.approval_group_id and
ame_approval_group_config.application_id = applicationIdIn and
sysdate between ame_approval_groups.start_date and
nvl(ame_approval_groups.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_approval_group_config.start_date and
nvl(ame_approval_group_config.end_date - ame_util.oneSecond, sysdate)
order by ame_approval_group_config.order_number;
select
parameter,
parameter_name
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
select
is_static,
query_string
into
isStatic,
queryString
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
memberTypesOut.delete;
memberIdsOut.delete;
select approval_group_item_id, parameter
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
parameter_name <> ame_util.approverOamGroupId and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
select
parameter,
parameter_name,
order_number
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
effectiveDateIn between start_date and nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
recursionParameterNames.delete;
recursionParameters.delete;
recursionOrderNumbers.delete;
recursionQueries.delete;
select order_number
from ame_approval_group_items
where approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
select approval_group_Item_id, order_number
from ame_approval_group_items
where
approval_group_id = approvalGroupIdIn and
approval_group_item_id <> approvalGroupItemIdIn and
order_number >= orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_item_id = approvalGroupItemIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_approval_group_items(approval_group_item_id,
approval_group_id,
parameter_name,
parameter,
order_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(approvalGroupItemIds(i),
approvalGroupIdIn,
parameterName,
parameter,
(orderNumbers(i) + 1),
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select approval_group_id, order_number
from ame_approval_group_config
where
application_id = applicationIdIn and
approval_group_id <> approvalGroupIdIn and
order_number >= orderNumberIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by order_number;
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
approval_group_id = approvalGroupIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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(applicationIdIn,
approvalGroupIds(i),
votingRegime,
(orderNumbers(i) + 1),
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_approval_group_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_approval_group_config
where
application_id = applicationIds(i) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
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,
approvalGroupIdIn,
votingRegimeIn,
orderNumber,
currentUserId,
processingDate,
currentUserId,
processingDate,
currentUserId,
processingDate,
null);
select start_date
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn 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_approval_group_config
where
application_id = applicationIds(i) and
approval_group_id = approvalGroupIdIn 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 */
decrementGroupOrderNumbers(applicationIdIn => applicationIds(i),
orderNumberIn => orderNumber,
finalizeIn => false);
update ame_approval_groups
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_id = approvalGroupIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_id = approvalGroupIdIn 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
approval_group_id = approvalGroupIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update ame_actions
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
parameter = to_char(approvalGroupIdIn) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
update those groups in ame_approval_group_members.
*/
updateDependentGroups(groupIdIn => approvalGroupIdIn,
deleteGroupIn => true);
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
select start_date
from ame_approval_groups
where
approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update;
select order_number, approval_group_item_id
from ame_approval_group_items
where approval_group_id = approvalGroupIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
for update of order_number
order by order_number;
/* subtract 1 from the order number for those above the one being deleted */
decrementGroupItemOrderNumbers(approvalGroupIdIn => approvalGroupIdIn,
orderNumberIn => itemOrderNumber,
finalizeIn => false);
update ame_approval_group_items
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_item_id = approvalGroupItemList(i) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
update ame_approval_groups
set
last_updated_by = currentUserId,
last_update_date = processingDate,
last_update_login = currentUserId,
end_date = processingDate
where
approval_group_id = approvalGroupIdIn and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate);
/* new calls updateDependentGroups, so don't do it here. */
commit;
messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
delete from ame_approval_group_members
where approval_group_id = groupIdIn;
/* Only insert members that aren't already there. */
memberIndex := 0;
select count(*)
into tempCount
from ame_approval_group_members
where
approval_group_id = groupIdIn and
parameter_name = parameterNames(i) and
parameter = parameters(i);
insert into ame_approval_group_members(
approval_group_id,
parameter_name,
parameter,
orig_system,
orig_system_id,
query_string,
order_number,
approval_group_members_id)
values(
groupIdIn,
parameterNames(i),
parameters(i),
origSystem,
origSystemId,
queryStrings(i),
orderNumbers(i),
ame_approval_group_members_s.nextval);
procedure updateDependentGroups(groupIdIn in integer,
deleteGroupIn in boolean default false) as
cursor dependentGroupCursor(groupIdIn in integer) is
select distinct approval_group_id
from ame_approval_group_items
where
parameter_name = ame_util.approverOamGroupId and
parameter = to_char(groupIdIn) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
groupsToUpdate ame_util.idList;
The following loop treats groupsToUpdate as a first-in, first-out queue.
We enter the loop with the group identified by groupIdIn as the first
(and so far only) group in the queue. The loop updates the next group
in the queue and adds all of the groups that contain it to the end of
the queue. In this fashion all of a given group's dependents are updated
before any of their dependents are updated, etc.
*/
processingDate := sysdate;
groupsToUpdate(1) := groupIdIn;
if(deleteGroupIn and currentGroup > 1) then
/*
Delete the target group (groupIdIn) from the item list of the current group.
(Don't do it for currentGroup = 1 because the group is never a member of itself.)
The call to setGroupMembers below updates ame_approval_group_members for dependent
groups.
*/
update ame_approval_group_items
set end_date = processingDate
where
approval_group_id = groupsToUpdate(currentGroup) and
parameter_name = ame_util.approverOamGroupId and
parameter = to_char(groupIdIn) and
processingDate between start_date and
nvl(end_date - ame_util.oneSecond, processingDate) ;
not deleteGroupIn) then
setGroupMembers(groupIdIn => groupsToUpdate(currentGroup));
for tempGroup in dependentGroupCursor(groupIdIn => groupsToUpdate(currentGroup)) loop
upperLimit := upperLimit + 1;
groupsToUpdate(upperLimit) := tempGroup.approval_group_id;
routineNameIn => 'updateDependentGroups',
exceptionNumberIn => sqlcode,
exceptionStringIn => ame_util.getMessage(applicationShortNameIn => 'PER',
messageNameIn => 'AME_400354_APPR_GRP_NAME_ERR',
tokenNameOneIn => 'NAME',
tokenValueOneIn => getName(approvalGroupIdIn => groupIdIn))
|| ' ' || sqlerrm);
end updateDependentGroups;