[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
set engIsTestTransaction to false (via a call to updateTransactionState), and it should
set engIsLocalTransaction to true. The API should set both engIsTestTransaction and
engIsLocalTransaction to false. The ame_util.runtimeException checks engIsLocalTransaction
to determine whether to log an exception to the Workflow context stack.
*/
engIsLocalTransaction boolean := true;
a value of true in the absence of a call to updateTransactionState, before the
call to ame_util.runtimeException. That is, unless a call to updateTransactionState
sets this variable's value, we assume the transaction is a test transaction, to
avoid writing spurious exceptions to the Workflow context stack.
*/
engIsTestTransaction boolean := true;
updateTransactionState sets the following variables. They indicate which parts of its
cycle the engine should or should not execute.
*/
engPrepareItemData boolean;
engUpdateCurrentApproverList boolean;
engUpdateOldApproverList boolean;
engDeletedApproverList ame_util.approversTable2; /* kept compact */
engInsertedApproverList ame_util.approversTable2; /* kept compact */
/* lists related to approver insertion and deletion */
engInsertionOrderTypeList ame_util.stringList; /* corresponds in order to engInsertedApproverList */
engInsertionParameterList ame_util.longestStringList; /* corresponds in order to engInsertedApproverList */
engInsertionIsSpecialForwardee ame_util.charList; /* ame_util.booleanTrue if insertee is a special forwardee */
engInsApproverIndex ame_util.idList; /*maintain the approver index for the insertion*/
1. deletes a row in the compact engApp lists if the rule represented by the row
is of insufficient relative priority.
2. re-compacts the same lists (preserving the above lexicographic ordering).
At this point, a rule with several actions still appears just once in the engApp
lists. The procedure fetchApplicableActions
1. populates engAppActionTypeIds, engAppParameters, and engAppParameterTwos.
2. splits out the actions in a rule so that the rule has one row in the engApp
lists for each of the rule's actions.
3. converts each combination rule's rule type to the rule type that corresponds
with the rule's action type.
4. re-sorts the lists lexicographically by these values:
rule's item-class order number
rule's item-class ID
item ID of the item to which the rule applies
rule type
action-type order number
action-type ID.
fetchApplicableActions deletes engAppPriorities because priorities have already been
processed; so engAppPriorities should not be referenced after fetchApplicableActions
1. deletes from the engApp variables any list-creation (authority) rules
suppressed by the exception rules.
2. re-compacts the lists (preserving the above lexicographic ordering).
3. converts the exception rules to list-creation rules.
processExceptions occurs in the engine algorithm after applicable actions have been
fetched, so that the actions of any list-creation rules suppressed by exception
rules will have been fetched unnecessarily. The assumption here is that exception
rules are deprecated, so if performance becomes an issue, exception rules should be
converted to list-creation rules with appropriate priorities.
*/
engAppActionTypeIds ame_util.idList;
needs to be called a second time after adhoc insertions and surrogate processing */
engRepeatSubstitutions boolean;
engStInsertionIndexes ame_util.idList;
engInsertionOrderList ame_util.idList;
engInsertionReasonList ame_util.stringList;
engInsertionDateList ame_util.dateList;
procedure fetchDeletedApprovers;
procedure fetchInsertedApprovers;
procedure insertIntoTransApprovalHistory
(transactionIdIn ame_trans_approval_history.transaction_id%type
,applicationIdIn ame_trans_approval_history.application_id%type
,orderNumberIn ame_trans_approval_history.order_number%type
,nameIn ame_trans_approval_history.name%type
,appCategoryIn ame_trans_approval_history.approver_category%type
,itemClassIn ame_trans_approval_history.item_class%type
,itemIdIn ame_trans_approval_history.item_id%type
,actionTypeIdIn ame_trans_approval_history.action_type_id%type
,authorityIn ame_trans_approval_history.authority%type
,statusIn ame_trans_approval_history.status%type
,grpOrChainIdIn ame_trans_approval_history.group_or_chain_id%type
,occurrenceIn ame_trans_approval_history.occurrence%type
,apiInsertionIn ame_trans_approval_history.api_insertion%type
,memberOrderNumberIn ame_trans_approval_history.member_order_number%type
,notificationIdIn ame_trans_approval_history.notification_id%type
,userCommentsIn ame_trans_approval_history.user_comments%type
,dateClearedIn ame_trans_approval_history.date_cleared%type
,historyTypeIn varchar2);
procedure processAdHocInsertions;
procedure populateInsertionIndexes(indexIn in integer
,insertionOrderIn in integer);
fetchInsertionsIn in boolean,
fetchDeletionsIn in boolean,
fetchAttributeValuesIn in boolean,
fetchInactiveAttValuesIn in boolean,
processProductionActionsIn in boolean,
processProductionRulesIn in boolean,
updateCurrentApproverListIn in boolean,
updateOldApproverListIn in boolean,
processPrioritiesIn in boolean,
prepareItemDataIn in boolean,
prepareRuleIdsIn in boolean,
prepareRuleDescsIn in boolean,
prepareApproverTreeIn in boolean default false,
transactionIdIn in varchar2,
ameApplicationIdIn in integer default null,
fndApplicationIdIn in integer default null,
transactionTypeIdIn in varchar2 default null);
procedure setInsertedApprovalStatus(currentApproverIndexIn in integer
,approvalStatusIn in varchar2);
procedure updateOldApproverList;
function insertionExists(orderTypeIn in varchar2,
parameterIn in varchar2) return boolean as
begin
for i in 1 .. engInsertionOrderTypeList.count loop
if(engInsertionOrderTypeList(i) = orderTypeIn and
engInsertionParameterList(i) = parameterIn) then
return(true);
routineNameIn => 'insertionExists',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertionExists;
select application_id
into ameApplicationId
from ame_calling_apps
where
fnd_application_id = fndApplicationIdIn and
((transactionTypeIdIn is null and transaction_type_id is null) or
(transaction_type_id = transactionTypeIdIn)) and
/* Don't use engEffectiveRuleDate here. */
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
if(actionTypeIdIn = ame_util.nullInsertionActionTypeId or actionTypeIdIn = -2 ) then
return(null);
If we're not trying to match an inserted approver with nullInsertionActionTypeId and
nullInsertionGroupOrChainId, and one of the input arguments is null, match the most
recently added group or chain.
*/
for i in reverse 1 .. engStApproversCount loop
if(engStApprovers(i).action_type_id <> ame_util.nullInsertionActionTypeId and
engStApprovers(i).group_or_chain_id <> ame_util.nullInsertionGroupOrChainId and
engStApprovers(i).item_class is not null and
engStApprovers(i).item_id is not null and
engStApprovers(i).action_type_id is not null and
engStApprovers(i).group_or_chain_id is not null) then
itemClass := engStApprovers(i).item_class;
One of the input arguments is null, and all approvers in the list have nullInsertionActionTypeId and
nullInsertionGroupOrChainId. So these are the only action-type ID and group-or-chain ID we can match.
In this case, match the item class and item ID of the most recent insertion.
*/
itemClass := engStApprovers(engStApproversCount).item_class;
select state
into tempState
from ame_temp_handler_states
where
handler_name = handlerName and
((application_id is null and engAmeApplicationId is null) or
(application_id = engAmeApplicationId)) and
((parameter is null and parameterIn is null) or
(parameter = parameterIn)) and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
select attribute_value_1 into tempValue from ame_test_trans_att_values
where application_id = engAmeApplicationId
and transaction_id = engTransactionId
and attribute_id = attributeIdIn
and item_class_id = itemClassIdIn
and item_id = itemIdIn;
select attribute_value_1
into tempValue
from ame_test_trans_att_values
where application_id = engAmeApplicationId
and transaction_id = engTransactionId
and attribute_id = attributeIdIn
and item_class_id = getItemClassId(ame_util.headerItemClassName)
and item_id = engTransactionId;
function getNextInsertionOrder return integer as
maxInsertionOrderNumber number;
select max(insertion_order)
into maxInsertionOrderNumber
from ame_temp_insertions
where transaction_id = engTransactionId
and application_id = engAmeApplicationId;
if maxinsertionOrderNumber is null
then
return 1;
return (maxInsertionOrderNumber + 1);
routineNameIn => 'getNextInsertionOrder',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end getNextInsertionOrder;
approverIn.api_insertion is null or
approverIn.authority is null or
approverIn.action_type_id is null or
approverIn.group_or_chain_id is null or
approverIn.occurrence is null or
approverIn.source is null or
approverIn.item_class is null or
approverIn.item_id is null or
approverIn.item_class_order_number is null or
approverIn.item_order_number is null or
approverIn.sub_list_order_number is null or
approverIn.action_type_order_number is null or
approverIn.group_or_chain_order_number is null or
approverIn.member_order_number is null) then
raise nullFieldException;
/* insert the approver into the tree.In the way if any of other nodes */
/* are missing create them. */
previousTreeNodeIndex := 1;
select count(*)
into tempApproverTypeCount
from ame_approver_types
where orig_system = approverIn.orig_system
and sysdate between start_date and end_date;
delete
from ame_temp_handler_states
where
handler_name = handlerName and
application_id = engAmeApplicationId and
((parameter is null and parameterIn is null) or
(parameter = parameterIn));
engAppItemClassIds.delete(tempFirstIndex, tempLastIndex);
engAppItemIds.delete(tempFirstIndex, tempLastIndex);
engAppApproverCategories.delete(tempFirstIndex, tempLastIndex);
engAppRuleIds.delete(tempFirstIndex, tempLastIndex);
engRuleAppliedYN.delete(tempFirstIndex, tempLastIndex);
engAppRuleTypes.delete(tempFirstIndex, tempLastIndex);
engAppRuleItemClassIds.delete(tempFirstIndex, tempLastIndex);
engAppAppItemIds.delete(tempFirstIndex, tempLastIndex);
engAppPriorities.delete(tempFirstIndex, tempLastIndex);
engAppActionTypeIds.delete(tempFirstIndex, tempLastIndex);
engAppParameters.delete(tempFirstIndex, tempLastIndex);
engAppParameterTwos.delete(tempFirstIndex, tempLastIndex);
deletes them in an unpredictable order, and does not re-compact the lists; so the
tempPendingRuleIds.delete;
/* Delete this condition from currentConditionRuleCounts. */
currentConditionRuleCounts.delete(tempConditionId);
tempIndex2 indexes the current currentAC variables, which may be deleted. tempIndex3
indexes tempPendingRuleIds.
*/
tempIndex := currentACUsageConditionIds.first;
currentACUsageConditionIds.delete(tempIndex2);
currentACUsageRuleIds.delete(tempIndex2);
indexes the current currentAC variable, which may be deleted; and tempIndex3
currentACUsageConditionIds.delete(tempIndex2);
currentACUsageRuleIds.delete(tempIndex2);
deletes them in an unpredictable order, and does not re-compact the lists; so the
tempPendingRuleIds.delete;
/* Delete this condition from currentConditionRuleCounts. */
currentConditionRuleCounts.delete(tempConditionId);
tempIndex2 indexes the current currentAC variables, which may be deleted. tempIndex3
indexes tempPendingRuleIds.
*/
tempIndex := currentACUsageConditionIds.first;
currentACUsageConditionIds.delete(tempIndex2);
currentACUsageRuleIds.delete(tempIndex2);
indexes the current currentAC variable (which may be deleted).
*/
for pendingRuleIndex in 1 .. tempPendingRuleIds.count loop
tempIndex := currentACUsageConditionIds.first;
currentACUsageConditionIds.delete(tempIndex2);
currentACUsageRuleIds.delete(tempIndex2);
select
ame_rules.rule_id rule_id,
nvl(ame_rules.item_class_id,
headerItemClassIdIn) item_class_id,
ame_rules.rule_type rule_type,
ame_rule_usages.priority priority,
ame_rule_usages.approver_category approver_category
from
ame_rules,
ame_rule_usages,
ame_item_class_usages
where
ame_rules.rule_id = ame_rule_usages.rule_id and
ame_rule_usages.item_id = engAmeApplicationId and
nvl(ame_rules.item_class_id, headerItemClassIdIn) = ame_item_class_usages.item_class_id and
ame_item_class_usages.application_id = engAmeApplicationId and
(processPrioritiesIn = ame_util.booleanFalse or
(ame_rules.rule_type = ame_util.combinationRuleType and
(combinationRulePriorityModeIn <> ame_util.absoluteRulePriority or
combinationRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.authorityRuleType and
(authorityRulePriorityModeIn <> ame_util.absoluteRulePriority or
authorityRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.exceptionRuleType and
(exceptionRulePriorityModeIn <> ame_util.absoluteRulePriority or
exceptionRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.listModRuleType and
(listModRulePriorityModeIn <> ame_util.absoluteRulePriority or
listModRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.substitutionRuleType and
(substRulePriorityModeIn <> ame_util.absoluteRulePriority or
substRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.preListGroupRuleType and
(preRulePriorityModeIn <> ame_util.absoluteRulePriority or
preRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.postListGroupRuleType and
(postRulePriorityModeIn <> ame_util.absoluteRulePriority or
postRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.productionRuleType and
(productionRulePriorityModeIn <> ame_util.absoluteRulePriority or
productionRuleThresholdIn >= ame_rule_usages.priority))) and
(processProductionRulesIn = ame_util.booleanTrue or
ame_rules.rule_type <> ame_util.productionRuleType) and
not exists (select *
from
ame_conditions,
ame_condition_usages
where
ame_conditions.condition_type <> ame_util.listModConditionType and
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_condition_usages.rule_id = ame_rules.rule_id and
engEffectiveRuleDate between
ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
/* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
rownum < 2) and
engEffectiveRuleDate between
ame_rules.start_date and
nvl(ame_rules.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_rule_usages.start_date and
nvl(ame_rule_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_item_class_usages.start_date and
nvl(ame_item_class_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate)
order by
ame_item_class_usages.item_class_order_number,
item_class_id,
ame_rules.rule_type,
ame_rules.rule_id;
select
condition_id,
string_value
from ame_string_values
where
condition_id in
(select ame_condition_usages.condition_id
from
ame_attributes,
ame_conditions,
ame_condition_usages,
ame_rule_usages
where
ame_attributes.attribute_type = ame_util.stringAttributeType and
ame_attributes.attribute_id = ame_conditions.attribute_id and
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_condition_usages.rule_id = ame_rule_usages.rule_id and
ame_rule_usages.item_id = engAmeApplicationId and
engEffectiveRuleDate between
ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_rule_usages.start_date and
nvl(ame_rule_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate)) and
engEffectiveRuleDate between
ame_string_values.start_date and
nvl(ame_string_values.end_date - ame_util.oneSecond, engEffectiveRuleDate)
order by condition_id;
select
ame_conditions.condition_id condition_id,
ame_conditions.condition_type condition_type,
ame_conditions.attribute_id attribute_id,
ame_conditions.parameter_one parameter_one,
ame_conditions.parameter_two parameter_two,
ame_conditions.parameter_three parameter_three,
ame_conditions.include_lower_limit,
ame_conditions.include_upper_limit,
ame_condition_usages.rule_id rule_id,
ame_rules.rule_type rule_type,
nvl(ame_rules.item_class_id,
headerItemClassIdIn) rule_item_class,
ame_rule_usages.priority priority,
ame_rule_usages.approver_category
from
ame_attributes,
ame_conditions,
ame_condition_usages,
ame_rules,
ame_rule_usages
where
ame_attributes.attribute_id = ame_conditions.attribute_id and
ame_conditions.condition_type <> ame_util.listModConditionType and
ame_conditions.condition_id = ame_condition_usages.condition_id and
ame_condition_usages.rule_id = ame_rules.rule_id and
ame_rules.rule_id = ame_rule_usages.rule_id and
ame_rule_usages.item_id = engAmeApplicationId and
(processPrioritiesIn = ame_util.booleanFalse or
(ame_rules.rule_type = ame_util.combinationRuleType and
(combinationRulePriorityModeIn <> ame_util.absoluteRulePriority or
combinationRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.authorityRuleType and
(authorityRulePriorityModeIn <> ame_util.absoluteRulePriority or
authorityRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.exceptionRuleType and
(exceptionRulePriorityModeIn <> ame_util.absoluteRulePriority or
exceptionRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.listModRuleType and
(listModRulePriorityModeIn <> ame_util.absoluteRulePriority or
listModRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.substitutionRuleType and
(substRulePriorityModeIn <> ame_util.absoluteRulePriority or
substRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.preListGroupRuleType and
(preRulePriorityModeIn <> ame_util.absoluteRulePriority or
preRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.postListGroupRuleType and
(postRulePriorityModeIn <> ame_util.absoluteRulePriority or
postRuleThresholdIn >= ame_rule_usages.priority)) or
(ame_rules.rule_type = ame_util.productionRuleType and
(productionRulePriorityModeIn <> ame_util.absoluteRulePriority or
productionRuleThresholdIn >= ame_rule_usages.priority))) and
(processProductionRulesIn = ame_util.booleanTrue or
ame_rules.rule_type <> ame_util.productionRuleType) and
engEffectiveRuleDate between
ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_rules.start_date and
nvl(ame_rules.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_rule_usages.start_date and
nvl(ame_rule_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate)
order by
rule_item_class,
ame_rules.rule_type,
ame_rules.rule_id,
ame_attributes.item_class_id;
select
ame_actions.action_type_id,
ame_actions.parameter,
ame_actions.parameter_two
from
ame_actions,
ame_action_usages
where
ame_actions.action_id = ame_action_usages.action_id and
ame_action_usages.rule_id = ruleIdIn and
engEffectiveRuleDate between
ame_actions.start_date and
nvl(ame_actions.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_action_usages.start_date and
nvl(ame_action_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate);
/* Delete priorities, to make sure they don't get used from here on. */
engAppPriorities.delete;
tempActionTypeIds.delete;
tempParameters.delete;
tempParameterTwos.delete;
engAppLMSubItemClassIds.delete;
engAppLMSubItemIds.delete;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name attribute_name,
ame_attributes.attribute_type attribute_type,
ame_attributes.item_class_id item_class_id,
ame_attribute_usages.query_string,
ame_attribute_usages.is_static
from
ame_attributes,
ame_attribute_usages,
ame_item_class_usages
where
ame_attributes.name not in (ame_util.workflowItemKeyAttribute, ame_util.workflowItemTypeAttribute) and
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_item_class_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = ame_item_class_usages.item_class_id and
(fetchInactivesIn = ame_util.booleanTrue or
ame_attribute_usages.use_count > 0 or
ame_attributes.attribute_id in
(select ame_attributes2.attribute_id
from
ame_attributes ame_attributes2,
ame_mandatory_attributes
where
ame_attributes2.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = -1 and
engEffectiveRuleDate between
ame_attributes2.start_date and
nvl(ame_attributes2.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_mandatory_attributes.start_date and
nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, engEffectiveRuleDate))) and
engEffectiveRuleDate between
ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_item_class_usages.start_date and
nvl(ame_item_class_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate)
/*
The order-by conditions are all necessary. The second is to break
item_class_order_number ties in a determinate order. The third is
to display attribute names in alphabetical order on the test tab.
Compare the itemClassUsageCursor cursor in the procedure updateTransactionState.
*/
order by
ame_item_class_usages.item_class_order_number,
ame_item_class_usages.item_class_id,
ame_attributes.name;
select
attribute_value_1,
attribute_value_2,
attribute_value_3
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn
order by item_id;
select attribute_value_1
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn
order by item_id;
select attribute_value_1
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn and
item_class_id = getItemClassId(ame_util.headerItemClassName) and
item_id = engTransactionId;
in a select statement.
*/
if(fetchInactivesIn) then
fetchInactives := ame_util.booleanTrue;
tempAttributeValues1.delete;
tempAttributeValues2.delete;
tempAttributeValues3.delete;
tempAttributeValues1.delete;
tempAttributeValues2.delete;
tempAttributeValues3.delete;
select
ame_attributes.attribute_id attribute_id,
ame_attributes.name attribute_name,
ame_attributes.attribute_type attribute_type,
ame_attributes.item_class_id item_class_id,
ame_attribute_usages.query_string,
ame_attribute_usages.is_static
from
ame_attributes,
ame_attribute_usages,
ame_item_class_usages
where
ame_attributes.name not in (ame_util.workflowItemKeyAttribute, ame_util.workflowItemTypeAttribute) and
ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
ame_attribute_usages.application_id = applicationIdIn and
ame_item_class_usages.application_id = applicationIdIn and
ame_attributes.item_class_id = ame_item_class_usages.item_class_id and
(ame_attribute_usages.use_count = 0 and
ame_attributes.attribute_id not in
(select ame_attributes2.attribute_id
from
ame_attributes ame_attributes2,
ame_mandatory_attributes
where
ame_attributes2.attribute_id = ame_mandatory_attributes.attribute_id and
ame_mandatory_attributes.action_type_id = -1 and
sysdate between
ame_attributes2.start_date and
nvl(ame_attributes2.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))) and
sysdate between
ame_attributes.start_date and
nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
sysdate between
ame_attribute_usages.start_date and
nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
sysdate between
ame_item_class_usages.start_date and
nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate) and
-- Condition to check whether the attribute is used in the rule
exists
(
select 1
from ame_conditions,
ame_condition_usages,
ame_rules,
ame_rule_usages
where ame_conditions.attribute_id = ame_attributes.attribute_id
and ame_conditions.condition_id = ame_condition_usages.condition_id
and ame_condition_usages.rule_id = ame_rules.rule_id
and ame_rules.rule_id = ame_rule_usages.rule_id
and ame_rule_usages.item_id = applicationIdIn
and ((engEffectiveRuleDate between ame_rules.start_date
and nvl(ame_rules.end_date - (1/86400), engEffectiveRuleDate)))
and ((engEffectiveRuleDate between ame_rule_usages.start_date
and nvl(ame_rule_usages.end_date - (1/86400), engEffectiveRuleDate)))
and engEffectiveRuleDate between ame_conditions.start_date
and nvl(ame_conditions.end_date - (1/86400), engEffectiveRuleDate)
and ((engEffectiveRuleDate between ame_condition_usages.start_date
and nvl(ame_condition_usages.end_date - (1/86400), engEffectiveRuleDate)))
union
select 1
from ame_mandatory_attributes,
ame_actions,
ame_action_usages,
ame_rules,
ame_rule_usages
where ame_mandatory_attributes.attribute_id = ame_attributes.attribute_id
and ame_mandatory_attributes.action_type_id =
ame_actions.action_type_id
and ame_actions.action_id = ame_action_usages.action_id
and ame_action_usages.rule_id = ame_rules.rule_id
and ame_rules.rule_id = ame_rule_usages.rule_id
and ame_rule_usages.item_id = applicationIdIn
and ((engEffectiveRuleDate between ame_rules.start_date
and nvl(ame_rules.end_date - (1/86400), engEffectiveRuleDate)))
and ((engEffectiveRuleDate between ame_rule_usages.start_date
and nvl(ame_rule_usages.end_date - (1/86400), engEffectiveRuleDate)))
and engEffectiveRuleDate between ame_mandatory_attributes.start_date
and nvl(ame_mandatory_attributes.end_date - (1/86400), engEffectiveRuleDate)
and engEffectiveRuleDate between ame_actions.start_date
and nvl(ame_actions.end_date - (1/86400), engEffectiveRuleDate)
and ((engEffectiveRuleDate between ame_action_usages.start_date
and nvl(ame_action_usages.end_date - (1/86400), engEffectiveRuleDate)))
)
/*
The order-by conditions are all necessary. The second is to break
item_class_order_number ties in a determinate order. The third is
to display attribute names in alphabetical order on the test tab.
Compare the itemClassUsageCursor cursor in the procedure updateTransactionState.
*/
order by
ame_item_class_usages.item_class_order_number,
ame_item_class_usages.item_class_id,
ame_attributes.name;
select
attribute_value_1,
attribute_value_2,
attribute_value_3
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn
order by item_id;
select attribute_value_1
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn
order by item_id;
select attribute_value_1
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
attribute_id = attributeIdIn and
item_class_id = getItemClassId(ame_util.headerItemClassName) and
item_id = engTransactionId;
tempAttributeValues1.delete;
tempAttributeValues2.delete;
tempAttributeValues3.delete;
tempAttributeValues1.delete;
tempAttributeValues2.delete;
tempAttributeValues3.delete;
select
ame_action_type_config.action_type_id,
ame_action_type_config.voting_regime,
ame_action_type_config.order_number,
ame_action_type_config.chain_ordering_mode,
ame_action_types.name,
ame_action_types.procedure_name,
ame_action_type_usages.rule_type
from
ame_action_type_config,
ame_action_types,
ame_action_type_usages
where
ame_action_type_config.application_id = engAmeApplicationId and
ame_action_types.action_type_id = ame_action_type_config.action_type_id and
ame_action_type_usages.action_type_id = ame_action_types.action_type_id and
/*
Only action types for list-creation and exception rules have two action-type usages;
select
decode(nvl(application_id,0),0,0,-1) application_id,
/* modified from
application_id to
decode(nvl(application_id,0),0,0,-1) application_id
for the bug 5614208 */
variable_name,
variable_value
from ame_config_vars
where
(application_id = 0 or application_id is null or application_id = engAmeApplicationId) and
/* Config vars can impact the approver list, so use engEffectiveRuleDate here. */
engEffectiveRuleDate between
start_date and
nvl(end_date - ame_util.oneSecond, engEffectiveRuleDate)
order by
variable_name,
application_id;
procedure fetchDeletedApprovers as
cursor deletedApproversCursor(applicationIdIn in integer,
transactionIdIn in varchar2) is
select
name,
item_class,
item_id,
approver_category,
action_type_id,
group_or_chain_id,
occurrence,
effective_date,
reason
from ame_temp_deletions
where
application_id = applicationIdIn and
transaction_id = transactionIdIn;
open deletedApproversCursor(applicationIdIn => engAmeApplicationId,
transactionIdIn => engTransactionId);
fetch deletedApproversCursor bulk collect
into
approverNames,
itemClasses,
itemIds,
approverCategories,
actionTypeIds,
groupOrChainIds,
occurrences,
engSuppressionDateList,
engSupperssionReasonList;
close deletedApproversCursor;
engDeletedApproverList(i).name := approverNames(i);
engDeletedApproverList(i).item_class := itemClasses(i);
engDeletedApproverList(i).item_id := itemIds(i);
engDeletedApproverList(i).approver_category := approverCategories(i);
engDeletedApproverList(i).action_type_id := actionTypeIds(i);
engDeletedApproverList(i).group_or_chain_id := groupOrChainIds(i);
engDeletedApproverList(i).occurrence := occurrences(i);
if(deletedApproversCursor%isopen) then
close deletedApproversCursor;
routineNameIn => 'fetchDeletedApprovers',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end fetchDeletedApprovers;
procedure fetchInsertedApprovers as
cursor insertedApproversCursor(applicationIdIn in integer,
transactionIdIn in varchar2) is
select
name,
item_class,
item_id,
nvl(approver_category,ame_util.approvalApproverCategory) approver_category,
api_insertion,
authority,
order_type,
parameter,
special_forwardee,
insertion_order,
effective_date,
reason,
approval_status
from ame_temp_insertions
where
application_id = applicationIdIn and
transaction_id = transactionIdIn
order by insertion_order;
approverApiInsertions ame_util.charList;
open insertedApproversCursor(applicationIdIn => engAmeApplicationId,
transactionIdIn => engTransactionId);
fetch insertedApproversCursor bulk collect
into
approverNames,
approverItemClasses,
approverItemIds,
approverCategories,
approverApiInsertions,
approverAuthorities,
engInsertionOrderTypeList,
engInsertionParameterList,
engInsertionIsSpecialForwardee,
engInsertionOrderList,
engInsertionDateList,
engInsertionReasonList,
approvalStatuses;
close insertedApproversCursor;
engInsertedApproverList(i).name := approverNames(i);
engInsertedApproverList(i).item_class := approverItemClasses(i);
engInsertedApproverList(i).item_id := approverItemIds(i);
engInsertedApproverList(i).approver_category := approverCategories(i);
engInsertedApproverList(i).api_insertion := approverApiInsertions(i);
engInsertedApproverList(i).authority := approverAuthorities(i);
engInsertedApproverList(i).approval_status := approvalStatuses(i);
origSystemOut => engInsertedApproverList(i).orig_system,
origSystemIdOut =>engInsertedApproverList(i).orig_system_id);
if(insertedApproversCursor%isopen) then
close insertedApproversCursor;
routineNameIn => 'fetchInsertedApprovers',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end fetchInsertedApprovers;
select
ame_item_classes.item_class_id,
ame_item_classes.name,
ame_item_class_usages.item_id_query,
ame_item_class_usages.item_class_order_number,
ame_item_class_usages.item_class_par_mode,
ame_item_class_usages.item_class_sublist_mode
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
engEffectiveRuleDate between
ame_item_classes.start_date and
nvl(ame_item_classes.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_item_class_usages.start_date and
nvl(ame_item_class_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate)
/*
The order-by conditions are both critical. The second is to break
item_class_order_number ties in a determinate order. Compare the
attributeCursor cursor in the procedure fetchAttributeValues.
*/
order by
ame_item_class_usages.item_class_order_number,
ame_item_class_usages.item_class_id;
select distinct item_id
from ame_test_trans_att_values
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
item_class_id = itemClassIdIn
order by item_id;
itemIds.delete;
select
fnd_application_id,
transaction_type_id
into
fndApplicationIdOut,
transactionTypeIdOut
from ame_calling_apps
where
application_id = applicationIdIn and
/* Don't use engEffectiveRuleDate here. */
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
select
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
approval_status,
action_type_id,
group_or_chain_id,
occurrence
from ame_temp_old_approver_lists
where
application_id = applicationIdIn and
transaction_id = transactionIdIn
order by order_number;
approverApiInsertions ame_util.charList;
approverApiInsertions,
approverAuthorities,
approverStatuses,
actionTypeIds,
groupOrChainIds,
occurrences;
engOldApproverList(i).api_insertion := approverApiInsertions(i);
select
orig_system,
orig_system_id,
parameter,
upper(parameter_name),
query_string,
order_number,
decode(parameter_name,
ame_util.approverOamGroupId, null,
ame_approver_type_pkg.getWfRolesName(orig_system, orig_system_id)) name,
decode(parameter_name,
ame_util.approverOamGroupId, null,
ame_approver_type_pkg.getApproverDisplayName2(orig_system, orig_system_id)) display_name
from ame_approval_group_members
where
approval_group_id = groupIdIn
order by order_number;
/* This is not the first time this query is being executed, so find and delete all old approvers in this group. */
for n in 1 .. engGroupMemberGroupIds.count loop
if( engGroupMemberGroupIds(n) = groupIdIn ) then
-- delete all occurrences of this
engGroupMemberGroupIds(n) := null;
engGroupMemberGroupIds.delete(outputIndex, engGroupMemberGroupIds.count);
tempGroupMembers.delete; -- for bug 4616570
select
order_number,
voting_regime
from ame_approval_group_config
where
application_id = engAmeApplicationId and
approval_group_id = groupIdIn and
engEffectiveRuleDate between
start_date and
nvl(end_date - ame_util.oneSecond, engEffectiveRuleDate);
approversOut(i).api_insertion := engStApprovers(i).api_insertion;
approversOut(i).api_insertion := engStApprovers(i).api_insertion;
parameter ame_temp_insertions.parameter%type;
for i in 1 .. engInsertedApproverList.count loop
if(engInsertionParameterList(i) = parameter) then
nameOut := engInsertedApproverList(i).name;
ame_approver_type_pkg.getOrigSystemIdAndDisplayName(nameIn => engInsertedApproverList(i).name,
origSystemOut => origSystemOut,
origSystemIdOut => origSystemIdOut,
displayNameOut => displayNameOut);
sourceOut := ame_util.otherInsertion;
setDeviationReasonDate(engInsertionReasonList(i),engInsertionDateList(i));
procedure getHandlerCOAInsertion(nameIn in varchar2,
itemClassIn in varchar2,
itemIdIn in varchar2,
actionTypeIdIn in integer,
groupOrChainIdIn in integer,
occurrenceIn in integer,
approvalStatusIn in varchar2,
nameOut out nocopy varchar2,
origSystemOut out nocopy varchar2,
origSystemIdOut out nocopy integer,
displayNameOut out nocopy varchar2,
sourceOut out nocopy varchar2) as
parameter ame_temp_insertions.parameter%type;
for i in 1 .. engInsertedApproverList.count loop
if(engInsertedApproverList(i).api_insertion = ame_util.apiAuthorityInsertion and
engInsertionOrderTypeList(i) = ame_util.afterApprover and
engInsertionParameterList(i) = parameter) then
nameOut := engInsertedApproverList(i).name;
ame_approver_type_pkg.getOrigSystemIdAndDisplayName(nameIn => engInsertedApproverList(i).name,
origSystemOut => origSystemOut,
origSystemIdOut => origSystemIdOut,
displayNameOut => displayNameOut);
if(engInsertionIsSpecialForwardee(i) = ame_util.booleanTrue) then
sourceOut := ame_util.specialForwardInsertion;
sourceOut := ame_util.forwardInsertion;
sourceOut := ame_util.approveAndForwardInsertion;
sourceOut := ame_util.otherInsertion;
setDeviationReasonDate(engInsertionReasonList(i),engInsertionDateList(i));
routineNameIn => 'getHandlerCOAInsertion',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end getHandlerCOAInsertion;
itemIdsOut.delete;
procedure getInsertions
(positionIn in number
,orderTypeIn in varchar2 default null
,coaInsertionsYNIn in varchar2 default ame_util.booleanTrue
,availableInsertionsOut out nocopy ame_util.insertionsTable2
) as
engStApproversCount integer;
availableInsertionsIndex integer; /* pre-increment */
tempInsertionDoesNotExist boolean;
tempParameter ame_temp_insertions.parameter%type;
availableInsertionsIndex := 0;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.absoluteOrder
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := null;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := null;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.absoluteOrder;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.absoluteOrderDescription || positionIn || '. ';
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.absoluteOrder
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := null;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := null;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.absoluteOrder;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.authorityApprover;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.absoluteOrderDescription || positionIn || '. ';
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.absoluteOrder
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := null;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := null;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.absoluteOrder;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.absoluteOrderDescription || positionIn || '. ';
/* Attribute to the insertee the relevant properties of the approver at positionIn. */
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.absoluteOrder
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := engStApprovers(positionIn).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.absoluteOrder;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := engStApprovers(positionIn).authority;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.absoluteOrderDescription || positionIn || '. ';
those of the approver at positionIn (if any), add a second available insertion, attributing
to the insertee the relevant properties of engStApprovers(positionIn - 1).
*/
if (positionIn = engStApproversCount + 1 or
(positionIn > 1 and
(engStApprovers(positionIn).group_or_chain_id <> engStApprovers(positionIn - 1).group_or_chain_id or
engStApprovers(positionIn).action_type_id <> engStApprovers(positionIn - 1).action_type_id or
engStApprovers(positionIn).item_id <> engStApprovers(positionIn - 1).item_id or
engStApprovers(positionIn).item_class <> engStApprovers(positionIn - 1).item_class
))) then
/* Attribute to the insertee the relevant properties of the approver at positionIn - 1. */
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.absoluteOrder
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn - 1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn - 1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id :=
engStApprovers(positionIn - 1).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn - 1).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.absoluteOrder;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := engStApprovers(positionIn - 1).authority;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.absoluteOrderDescription || positionIn || '. ';
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.afterApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn - 1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn - 1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id :=
engStApprovers(positionIn - 1).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn - 1).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.afterApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := engStApprovers(positionIn - 1).authority;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.afterApproverDescription || prevApproverDescription;
engStApprovers(positionIn).api_insertion <> ame_util.apiInsertion and
engStApprovers(positionIn).approver_category = ame_util.approvalApproverCategory and
coaInsertionsYNIn = ame_util.booleanTrue and
(not ame_engine.insertionExists
(orderTypeIn => ame_util.afterApprover
,parameterIn => tempParameter
))) then /* COA */
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn - 1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn - 1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id :=
engStApprovers(positionIn - 1).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn - 1).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.afterApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiAuthorityInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := engStApprovers(positionIn - 1).authority;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.afterApproverDescription || prevApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.beforeApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := engStApprovers(positionIn).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.beforeApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := engStApprovers(positionIn).authority;
availableInsertionsOut(availableInsertionsIndex).description :=
ame_util.beforeApproverDescription || nextApproverDescription;
if (coaInsertionsYNIn = ame_util.booleanTrue and
(orderTypeIn is null or
orderTypeIn = ame_util.firstAuthority)) then
if (positionIn < engStApproversCount + 1 and
engStApprovers(positionIn).authority = ame_util.authorityApprover and
engStApprovers(positionIn).api_insertion <> ame_util.apiInsertion) then
tempBoolean := true; /* tempBoolean remains true if no previous authority is found. */
engStApprovers(i).api_insertion <> ame_util.apiInsertion) then
tempBoolean := false;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstAuthority
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id :=
engStApprovers(positionIn).action_type_id;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstAuthority;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiAuthorityInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.authorityApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstAuthorityDescription;
Assume that in the case of an entirely empty approver list, we allow insertion of a first
post-approver into the header item's list only. Otherwise, we only allow insertion of a
first post-approver into a non-empty item list. Here is the case analysis:
if(the approver list is empty) then
allow a first-post-approver insertion for the header
elsif(positionIn is after the end of the approver list) then
if(the last approver in the list is not a post-approver) then
allow a first-post-approver insertion for the last approver's item
end if
elsif(positionIn = 1) then
if(the first approver in the list is a post-approver_ then
allow a first-post-approver insertion for the first approver's item
end if
else
if(the engStApprovers at positionIn - 1 and positionIn are for the same item) then
if(the first approver is not a post-approver and
the second approver is a post-approver) then
allow a first-post-approver insertion for the engStApprovers' item
end if
else
if(the second approver is a post-approver) then
allow a first-post-approver insertion for the second approver's item
end if
if(the first approver is not a post-approver) then
allow a first-post-approver insertion for the first approver's item
end if
end if
end if
*/
if (orderTypeIn is null or
orderTypeIn = ame_util.firstPostApprover) then
if (engStApproversCount = 0) then
tempParameter := ame_util.firstPostParameter ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
engTransactionId;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(engStApproversCount).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(engStApproversCount).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id :=
ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id :=
engStApprovers(positionIn).group_or_chain_id;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn - 1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn - 1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPostApproverDescription;
Assume that in the case of an entirely empty approver list, we allow insertion of a first
pre-approver into the header item's list only. Otherwise, we only allow insertion of a
first pre-approver into a non-empty item list. Here is the case analysis:
if(the approver list is empty) then
allow a first-pre-approver insertion for the header item
elsif(positionIn = 1) then
allow a first-pre-approver insertion for the first approver's item
elsif(positionIn < engStApproversCount + 1) then
if(the engStApprovers at positionIn - 1 and positionIn are for different items) then
allow a first-pre-approver insertion for the second approver's item
end if
end if
*/
if (orderTypeIn is null or
orderTypeIn = ame_util.firstPreApprover) then
if (engStApproversCount = 0) then
tempParameter := ame_util.firstPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
engTransactionId;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPreApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPreApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.firstPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.firstPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.firstPreApproverDescription;
Assume that in the case of an entirely empty approver list, we allow insertion of a last
post-approver into the header item's list only. Otherwise, we only allow insertion of a
last post-approver into a non-empty item list. Here is the case analysis:
if(the approver list is empty) then
allow last-post-approver insertion for the header item
elsif(positionIn = engStApproversCount + 1) then
allow last-post-approver insertion for the last approver's item
elsif(positionIn > 1) then
if(the engStApprovers at positionIn - 1 and positionIn are for different items) then
allow last-post-approver insertion for the former approver's item
end if
end if
*/
if (orderTypeIn is null or
orderTypeIn = ame_util.lastPostApprover) then
if (engStApproversCount = 0) then
tempParameter := ame_util.lastPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
engTransactionId;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(engStApproversCount).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(engStApproversCount).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPostApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPostApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn - 1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn - 1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPostApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.postApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPostApproverDescription;
Assume that in the case of an entirely empty approver list, we allow insertion of a last
pre-approver into the header item's list only. Otherwise, we only allow insertion of a
last pre-approver into a non-empty item list. Here is the case analysis:
if(the approver list is empty) then
allow last-pre-approver insertion for the header item
elsif(positionIn = 1) then
if(the approver at position 1 is not a pre-approver) then
allow last-pre-approver insertion for the item of the first approver
end if
elsif(positionIn <= engStApproversCount) then
if(the engStApprovers at positionIn - 1 and positionIn are for the same item) then
if(the approver at positionIn - 1 is a pre-approver and
the approver at positionIn is not a pre-approver) then
allow last-pre-approver insertion for the engStApprovers' item
end if
else
if(the approver at positionIn is not a pre-approver) then
allow last-pre-approver insertion for the item of the approver at positionIn
end if
end if
end if
*/
if (orderTypeIn is null or
orderTypeIn = ame_util.lastPreApprover) then
if (engStApproversCount = 0) then
tempParameter := ame_util.lastPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
engTransactionId;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPreApprover
,parameterIn => tempParameter)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := ame_util.headerItemClassName;
availableInsertionsOut(availableInsertionsIndex).item_id := engTransactionId;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPreApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(1).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(1).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPreApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPreApproverDescription;
if (not ame_engine.insertionExists
(orderTypeIn => ame_util.lastPreApprover
,parameterIn => tempParameter
)) then
availableInsertionsIndex := availableInsertionsIndex + 1;
availableInsertionsOut(availableInsertionsIndex).item_class := engStApprovers(positionIn).item_class;
availableInsertionsOut(availableInsertionsIndex).item_id := engStApprovers(positionIn).item_id;
availableInsertionsOut(availableInsertionsIndex).action_type_id := ame_util.nullInsertionActionTypeId;
availableInsertionsOut(availableInsertionsIndex).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
availableInsertionsOut(availableInsertionsIndex).order_type := ame_util.lastPreApprover;
availableInsertionsOut(availableInsertionsIndex).parameter := tempParameter;
availableInsertionsOut(availableInsertionsIndex).api_insertion := ame_util.apiInsertion;
availableInsertionsOut(availableInsertionsIndex).authority := ame_util.preApprover;
availableInsertionsOut(availableInsertionsIndex).description := ame_util.lastPreApproverDescription;
,messageNameIn => 'AME_400418_INVALID_INSERTION'
);
,routineNameIn => 'getInsertions'
,exceptionNumberIn => errorCode
,exceptionStringIn => errorMessage
);
,routineNameIn => 'getInsertions'
,exceptionNumberIn => sqlcode
,exceptionStringIn => sqlerrm
);
end getInsertions;
/* Select the values, as they were not cached. */
select
ame_conditions.parameter_one,
ame_conditions.parameter_two
into
parameterOne,
parameterTwo
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.listModConditionType and
engEffectiveRuleDate between
ame_conditions.start_date and
nvl(ame_conditions.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
engEffectiveRuleDate between
ame_condition_usages.start_date and
nvl(ame_condition_usages.end_date - ame_util.oneSecond, engEffectiveRuleDate) and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
ame_engine.updateTransactionState(isTestTransactionIn => false
,isLocalTransactionIn => false
,fetchConfigVarsIn => true
,fetchOldApproversIn => true
,fetchInsertionsIn => true
,fetchDeletionsIn => true
,fetchAttributeValuesIn => true
,fetchInactiveAttValuesIn => false
,processProductionActionsIn => tempProcessProductionActions
,processProductionRulesIn => tempProcessProductionRules
,updateCurrentApproverListIn => true
,updateOldApproverListIn => true
,processPrioritiesIn => true
,prepareItemDataIn => tempPrepareItemData
,prepareRuleIdsIn => false
,prepareRuleDescsIn => false
,prepareApproverTreeIn => true
,transactionIdIn => transactionIdIn
,ameApplicationIdIn => null
,fndApplicationIdIn => applicationIdIn
,transactionTypeIdIn => transactionTypeIn);
setInsertedApprovalStatus(currentApproverIndexIn => currentTreeNode.approver_index
,approvalStatusIn => ame_util.notifiedStatus );
update ame_temp_old_approver_lists
set approval_status = ame_util.notifiedByRepeatedStatus
where application_id = ameApplicationId
and transaction_id = transactionIdIn
and name = engStApprovers(engStRepeatedAppIndexes(x)).name --nextApproversOut(i).name
and item_class = engStApprovers(engStRepeatedAppIndexes(x)).item_class
and item_id = engStApprovers(engStRepeatedAppIndexes(x)).item_id
and action_type_id = engStApprovers(engStRepeatedAppIndexes(x)).action_type_id
and group_or_chain_id = engStApprovers(engStRepeatedAppIndexes(x)).group_or_chain_id
and occurrence = engStApprovers(engStRepeatedAppIndexes(x)).occurrence
and (approval_status is null or approval_status = ame_util.repeatedStatus);
setInsertedApprovalStatus(currentApproverIndexIn => engStRepeatedAppIndexes(x)
,approvalStatusIn => ame_util.notifiedByRepeatedStatus);
setInsertedApprovalStatus(currentApproverIndexIn => engStApproversTree(currentTreeNode.sibling_index).approver_index
,approvalStatusIn =>ame_util.notifiedStatus );
update ame_temp_old_approver_lists
set approval_status = ame_util.notifiedByRepeatedStatus
where application_id = ameApplicationId
and transaction_id = transactionIdIn
and name = engStApprovers(engStRepeatedAppIndexes(x)).name --nextApproversOut(i).name
and item_class = engStApprovers(engStRepeatedAppIndexes(x)).item_class
and item_id = engStApprovers(engStRepeatedAppIndexes(x)).item_id
and action_type_id = engStApprovers(engStRepeatedAppIndexes(x)).action_type_id
and group_or_chain_id = engStApprovers(engStRepeatedAppIndexes(x)).group_or_chain_id
and occurrence = engStApprovers(engStRepeatedAppIndexes(x)).occurrence
and (approval_status is null or approval_status = ame_util.repeatedStatus);
setInsertedApprovalStatus(currentApproverIndexIn => engStRepeatedAppIndexes(x)
,approvalStatusIn => ame_util.notifiedByRepeatedStatus);
update ame_temp_old_approver_lists
set approval_status = ame_util.notifiedStatus
where item_class = nextApproversOut(i).item_class
and item_id = nextApproversOut(i).item_id
and name = nextApproversOut(i).name
and action_type_id = nextApproversOut(i).action_type_id
and group_or_chain_id = nextApproversOut(i).group_or_chain_id
and occurrence = nextApproversOut(i).occurrence
and transaction_id = transactionIdIn
and application_id = ameApplicationId;
/* Insert into Approval Notification History Table */
insertIntoTransApprovalHistory
(transactionIdIn => transactionIdIn
,applicationIdIn => ameApplicationId
,orderNumberIn => nextApproversOut(i).approver_order_number
,nameIn => nextApproversOut(i).name
,appCategoryIn => nextApproversOut(i).approver_category
,itemClassIn => nextApproversOut(i).item_class
,itemIdIn => nextApproversOut(i).item_id
,actionTypeIdIn => nextApproversOut(i).action_type_id
,authorityIn => nextApproversOut(i).authority
,statusIn => ame_util.notifiedStatus
,grpOrChainIdIn => nextApproversOut(i).group_or_chain_id
,occurrenceIn => nextApproversOut(i).occurrence
,apiInsertionIn => nextApproversOut(i).api_insertion
,memberOrderNumberIn => nextApproversOut(i).member_order_number
,notificationIdIn => null
,userCommentsIn => null
,dateClearedIn => null
,historyTypeIn => 'APPROVERPRESENT');
nextApproversOut.delete;
tempRuleIdList.delete;
itemIndexesOut.delete;
itemItemClassesOut.delete;
itemIdsOut.delete;
itemSourcesOut.delete;
tempRuleIdList.delete;
tempRuleIdList.delete;
prodIndexesOut.delete;
productionNamesOut.delete;
productionValuesOut.delete;
tempRuleIdList.delete;
tempRuleIdList.delete;
engStRuleDescriptions.delete;
engStRuleIds.delete;
engStRuleIndexes.delete;
select count(*)
into tempCount
from ame_temp_transactions
where
application_id = ameApplicationIdIn and
transaction_id = transactionIdIn and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
insert into ame_temp_transactions(
application_id,
transaction_id,
row_timestamp) values(
ameApplicationIdIn,
transactionIdIn,
sysdate); /* Don't use engEffectiveRuleDate here. */
updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => true,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => true,
updateOldApproverListIn => true,
processPrioritiesIn => true,
prepareItemDataIn => true,
prepareRuleIdsIn => true,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => ameApplicationIdIn);
fetchInsertionsIn => approverListStageIn > 1,
fetchDeletionsIn => approverListStageIn > 2,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => true,
processProductionRulesIn => true,
updateCurrentApproverListIn => true,
updateOldApproverListIn => true,
processPrioritiesIn => true,
prepareItemDataIn => true,
prepareRuleIdsIn => true,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => ameApplicationIdIn);
processAdHocInsertions;
fetchInsertionsIn in boolean default true,
fetchDeletionsIn in boolean default true,
fetchAttributeValuesIn in boolean default true,
fetchInactiveAttValuesIn in boolean default false) as
begin
setContext(isTestTransactionIn => false,
isLocalTransactionIn => true,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => true,
updateOldApproverListIn => true,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => ameApplicationIdIn,
fndApplicationIdIn => null,
transactionTypeIdIn => transactionTypeIdIn);
procedure updateDeviationList( sourceIndexIn in number
,targetIndexIn in number) as
begin
if engDeviationResultList.exists(sourceIndexIn) then
engDeviationResultList(targetIndexIn) := engDeviationResultList(sourceIndexIn);
engDeviationResultList.delete(sourceIndexIn);
routineNameIn => 'updateDeviationList',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateDeviationList;
procedure insertApprover(indexIn in integer,
approverIn in ame_util.approverRecord2,
adjustMemberOrderNumbersIn in boolean default false,
approverLocationIn in boolean default ame_util.lastAmongEquals,
inserteeIndexIn in number default null,
currentInsIndex in integer default null) as
engStApproversCount integer;
If the status is not available in ame_temp_insertions get it from
ame_temp_old_approver_lists
*/
if engStApprovers(indexIn).approval_status is null then
engStApprovers(indexIn).approval_status := getHandlerApprovalStatus(approverIn => engStApprovers(indexIn));
select voting_regime
into tempVotingRegime
from ame_approval_group_config
where approval_group_id = engStApprovers(previousIndex).group_or_chain_id
and application_id = engAmeApplicationId
and sysdate between start_date and nvl(end_Date - (1/86400), sysdate);
if inserteeIndexIn is not null then
engTempReason := engInsertionReasonList(inserteeIndexIn);
engTempDate := engInsertionDateList(inserteeIndexIn);
/* is true update the member order number in the tree */
/* Add the approver to tree */
if adjustMemberOrderNumbersIn then
approverTreeIndex := engStApproversTree.first;
if inserteeIndexIn is not null then
engTempReason := engInsertionReasonList(inserteeIndexIn);
engTempDate := engInsertionDateList(inserteeIndexIn);
routineNameIn => 'insertApprover',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'insertApprover',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertApprover;
procedure insertApprovers(firstIndexIn in integer,
approversIn in ame_util.approversTable2) as
approversInCount integer;
/* Insert the new approvers to the tree */
if engPrepareApproverTree then
/* Approvers Tree is sparse */
loopIndex := engStApproversTree.last;
routineNameIn => 'insertApprovers',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'insertApprovers',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertApprovers;
procedure insertIntoTransApprovalHistory
(transactionIdIn ame_trans_approval_history.transaction_id%type
,applicationIdIn ame_trans_approval_history.application_id%type
,orderNumberIn ame_trans_approval_history.order_number%type
,nameIn ame_trans_approval_history.name%type
,appCategoryIn ame_trans_approval_history.approver_category%type
,itemClassIn ame_trans_approval_history.item_class%type
,itemIdIn ame_trans_approval_history.item_id%type
,actionTypeIdIn ame_trans_approval_history.action_type_id%type
,authorityIn ame_trans_approval_history.authority%type
,statusIn ame_trans_approval_history.status%type
,grpOrChainIdIn ame_trans_approval_history.group_or_chain_id%type
,occurrenceIn ame_trans_approval_history.occurrence%type
,apiInsertionIn ame_trans_approval_history.api_insertion%type
,memberorderNumberIn ame_trans_approval_history.member_order_number%type
,notificationIdIn ame_trans_approval_history.notification_id%type
,userCommentsIn ame_trans_approval_history.user_comments%type
,dateClearedIn ame_trans_approval_history.date_cleared%type
,historyTypeIn varchar2) as
tempTransHistoryId ame_trans_approval_history.trans_history_id%type;
tempApiInsertion ame_trans_approval_history.api_insertion%type;
select ame_trans_approval_history_s.nextval
into tempTransHistoryId
from dual;
insert into AME_TRANS_APPROVAL_HISTORY
(TRANS_HISTORY_ID
,TRANSACTION_ID
,APPLICATION_ID
,ROW_TIMESTAMP
,ORDER_NUMBER
,NAME
,APPROVER_CATEGORY
,ITEM_CLASS
,ITEM_ID
,ACTION_TYPE_ID
,AUTHORITY
,STATUS
,GROUP_OR_CHAIN_ID
,OCCURRENCE
,API_INSERTION
,MEMBER_ORDER_NUMBER
,NOTIFICATION_ID
,USER_COMMENTS
,DATE_CLEARED
)select tempTransHistoryId
,transactionIdIn
,applicationIdIn
,sysdate
,approvers(i).approver_order_number
,approvers(i).name
,approvers(i).approver_category
,approvers(i).item_class
,approvers(i).item_id
,approvers(i).action_type_id
,approvers(i).authority
,ame_util.beatByFirstResponderStatus
,approvers(i).group_or_chain_id
,approvers(i).occurrence
,approvers(i).api_insertion
,approvers(i).member_order_number
,notificationIdIn
,null
,null
from dual;
select ame_trans_approval_history_s.nextval
into tempTransHistoryId
from dual;
insert into AME_TRANS_APPROVAL_HISTORY
(TRANS_HISTORY_ID
,TRANSACTION_ID
,APPLICATION_ID
,ROW_TIMESTAMP
,ORDER_NUMBER
,NAME
,APPROVER_CATEGORY
,ITEM_CLASS
,ITEM_ID
,ACTION_TYPE_ID
,AUTHORITY
,STATUS
,GROUP_OR_CHAIN_ID
,OCCURRENCE
,API_INSERTION
,MEMBER_ORDER_NUMBER
,NOTIFICATION_ID
,USER_COMMENTS
,DATE_CLEARED
)values
(tempTransHistoryId
,transactionIdIn
,applicationIdIn
,sysdate
,orderNumberIn
,nameIn
,appCategoryIn
,itemClassIn
,itemIdIn
,actionTypeIdIn
,authorityIn
,statusIn
,grpOrChainIdIn
,occurrenceIn
,apiInsertionIn
,memberorderNumberIn
,notificationIdIn
,userCommentsIn
,dateClearedIn);
select atah.item_class item_class
,atah.item_id item_id
,atah.order_number order_number
,atah.authority authority
,atah.action_type_id action_type_id
,atah.group_or_chain_id group_or_chain_id
,atah.occurrence occurrence
,atah.api_insertion api_insertion
,atah.member_order_number member_order_number
into tempItemClass
,tempItemId
,tempOrderNumber
,tempAuthority
,tempActionTypeId
,tempGroupOrChainId
,tempOccurrence
,tempApiInsertion
,tempMemberOrderNumber
from ame_trans_approval_history atah
,fnd_lookups lookup
,fnd_lookups lookup2
,ame_approval_groups apg
where atah.date_cleared is null
and atah.transaction_id = transactionIdIn
and atah.application_id = applicationIdIn
and atah.name = tempName
and atah.trans_history_id =
(select max(b.trans_history_id)
from ame_trans_approval_history b
where atah.transaction_id = b.transaction_id
and atah.application_id = b.application_id
and atah.name = b.name
and atah.approver_category = b.approver_category
and atah.item_class = b.item_class
and atah.item_id = b.item_id
and atah.action_type_id = b.action_type_id
and atah.authority = b.authority
and atah.group_or_chain_id = b.group_or_chain_id
and atah.occurrence = b.occurrence
and b.date_cleared is null )
and lookup.lookup_type = 'AME_SUBLIST_TYPES'
and lookup.lookup_code = atah.authority
and lookup2.lookup_type = 'AME_APPROVAL_STATUS'
and lookup2.lookup_code = atah.status
and apg.approval_group_id(+) = atah.group_or_chain_id
and sysdate between nvl(apg.start_date,sysdate) and nvl(apg.end_date,sysdate);
tempApiInsertion := null;
if apiInsertionIn is not null then
tempApiInsertion := apiInsertionIn;
insert into AME_TRANS_APPROVAL_HISTORY
(TRANS_HISTORY_ID
,TRANSACTION_ID
,APPLICATION_ID
,ROW_TIMESTAMP
,ORDER_NUMBER
,NAME
,APPROVER_CATEGORY
,ITEM_CLASS
,ITEM_ID
,ACTION_TYPE_ID
,AUTHORITY
,STATUS
,GROUP_OR_CHAIN_ID
,OCCURRENCE
,API_INSERTION
,MEMBER_ORDER_NUMBER
,NOTIFICATION_ID
,USER_COMMENTS
,DATE_CLEARED
) values
(tempTransHistoryId
,transactionIdIn
,applicationIdIn
,sysdate
,tempOrderNumber
,nameIn
,appCategoryIn
,tempItemClass
,tempItemId
,tempActionTypeId
,tempAuthority
,statusIn
,tempGroupOrChainId
,tempOccurrence
,tempApiInsertion
,tempMemberOrderNumber
,notificationIdIn
,userCommentsIn
,dateClearedIn
);
routineNameIn => 'insertIntoTransApprovalHistory',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertIntoTransApprovalHistory;
select 'Y'
from ame_temp_trans_locks
where fnd_application_id = fndApplicationIdIn
and transaction_id = transactionIdIn
and transaction_type_id = transactionTypeIdIn;
The ame_temp_trans_locks_pk unique index will prevent the following insert from occurring
if another row has already been inserted into ame_temp_trans_locks with the same
fnd_application_id, transaction_type_id, and transaction_id values (even though the other
insert is not committed).
*/
tempTransIsLocked := null;
insert into ame_temp_trans_locks(fnd_application_id,
transaction_type_id,
transaction_id,
row_timestamp) values(
fndApplicationIdIn,
transactionTypeIdIn,
transactionIdIn,
sysdate);
select count(*)
into tempCount
from ame_temp_transactions
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId and
rownum < 2; /* Avoids second fetch otherwise required by ANSI standard to check for too many rows. */
select ame_temp_transactions_s.nextval into tempTransSeqId from dual;
insert into ame_temp_transactions(
application_id,
transaction_id,
row_timestamp,
temp_transactions_id
) values(
engAmeApplicationId,
engTransactionId,
sysdate,
tempTransSeqId); /* Don't use engEffectiveRuleDate here. */
/* Update the temp variables when the item changes. */
if(engStApprovers(i).item_id <> tempItemId or
engStApprovers(i).item_class <> tempItemClass) then
tempItemId := engStApprovers(i).item_id;
/* Update the process-complete engSt variables as appropriate. */
-- check for pending/yet to be notified approvers
if(engStItemAppProcessCompleteYN(tempItemIndex) <> ame_util2.completeFullyRejected and
((engStApprovers(i).approver_category = ame_util.approvalApproverCategory and
(engStApprovers(i).approval_status is null or
engStApprovers(i).approval_status in (ame_util.nullStatus
,ame_util.notifiedStatus
,ame_util.repeatedStatus
,ame_util.notifiedByRepeatedStatus))) or
(engStApprovers(i).approver_category = ame_util.fyiApproverCategory and
(engStApprovers(i).approval_status is null or
engStApprovers(i).approval_status = ame_util.nullStatus)))) then
if engStItemAppProcessCompleteYN(tempItemIndex) in (ame_util2.completeNoApprovers
,ame_util2.completeFullyApproved) then
engStItemAppProcessCompleteYN(tempItemIndex) := ame_util2.notCompleted;
procedure processAdHocInsertions as
displacedInserteeIndexes ame_util.idList;
tempItemClass ame_temp_insertions.item_class%type;
tempItemId ame_temp_insertions.item_id%type;
tempOrderType ame_temp_insertions.order_type%type;
for each inserted approver:
orig_system
orig_system_id
display_name
action_type_id
group_or_chain_id
occurrence
source
approval_status
item_class_order_number
item_order_number
sub_list_order_number
action_type_order_number
group_or_chain_order_number
member_order_number
The first three of these fields get populated at the beginning of the outermost loop below.
The other fields get populated just before the actual insertion occurs. This procedure must
therefore decide how to set the order-number fields. The procedure attempts to set the
order numbers consistent with the order relation of the insertion, where the order relation
anchors the insertion to the approver preceeding or following the insertion in engStApprovers.
See the comments near specific insertApprover calls below.
*/
engStApproversCount := engStApprovers.count;
for i in 1 .. engInsertedApproverList.count loop
if(engInsertedApproverList(i).authority <> ame_util.authorityApprover or
engInsertedApproverList(i).api_insertion = ame_util.apiInsertion) then
ame_approver_type_pkg.getOrigSystemIdAndDisplayName(nameIn => engInsertedApproverList(i).name,
origSystemOut => engInsertedApproverList(i).orig_system,
origSystemIdOut => engInsertedApproverList(i).orig_system_id,
displayNameOut => engInsertedApproverList(i).display_name);
parameterFields.delete;
parseFields(stringIn => engInsertionParameterList(i),
fieldsOut => parameterFields);
if(engInsertionOrderTypeList(i) = ame_util.absoluteOrder) then
tempIndex := engInsertionParameterList(i);
engInsertedApproverList(i).source := ame_util.otherInsertion;
engStApprovers(tempIndex - 1).authority = engInsertedApproverList(i).authority and
engStApprovers(tempIndex - 1).item_class = engInsertedApproverList(i).item_class and
engStApprovers(tempIndex - 1).item_id = engInsertedApproverList(i).item_id
) then
/* Group the insertion with the preceeding approver in engStApprovers. */
engInsertedApproverList(i).action_type_id := engStApprovers(tempIndex - 1).action_type_id;
engInsertedApproverList(i).group_or_chain_id := engStApprovers(tempIndex - 1).group_or_chain_id;
engInsertedApproverList(i).occurrence :=
getHandlerOccurrence(nameIn => engInsertedApproverList(i).name,
itemClassIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id,
actionTypeIdIn => engInsertedApproverList(i).action_type_id,
groupOrChainIdIn => engInsertedApproverList(i).group_or_chain_id);
engInsertedApproverList(i).item_class_order_number :=
engStApprovers(tempIndex - 1).item_class_order_number;
engInsertedApproverList(i).item_order_number := engStApprovers(tempIndex - 1).item_order_number;
engInsertedApproverList(i).sub_list_order_number := engStApprovers(tempIndex - 1).sub_list_order_number;
engInsertedApproverList(i).action_type_order_number :=
engStApprovers(tempIndex - 1).action_type_order_number;
engInsertedApproverList(i).group_or_chain_order_number :=
engStApprovers(tempIndex - 1).group_or_chain_order_number;
/* Group the insertion with the following approver in engStApprovers. */
engInsertedApproverList(i).authority := engStApprovers(tempIndex).authority;
engInsertedApproverList(i).action_type_id := engStApprovers(tempIndex).action_type_id;
engInsertedApproverList(i).group_or_chain_id := engStApprovers(tempIndex).group_or_chain_id;
engInsertedApproverList(i).occurrence :=
getHandlerOccurrence(nameIn => engInsertedApproverList(i).name,
itemClassIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id,
actionTypeIdIn => engInsertedApproverList(i).action_type_id,
groupOrChainIdIn => engInsertedApproverList(i).group_or_chain_id);
engInsertedApproverList(i).item_class_order_number := engStApprovers(tempIndex).item_class_order_number;
engInsertedApproverList(i).item_order_number := engStApprovers(tempIndex).item_order_number;
engInsertedApproverList(i).sub_list_order_number := engStApprovers(tempIndex).sub_list_order_number;
engInsertedApproverList(i).action_type_order_number := engStApprovers(tempIndex).action_type_order_number;
engInsertedApproverList(i).group_or_chain_order_number :=
engStApprovers(tempIndex).group_or_chain_order_number;
engInsertedApproverList(i).action_type_id := ame_util.nullInsertionActionTypeId;
engInsertedApproverList(i).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
engInsertedApproverList(i).occurrence := 1;
engInsertedApproverList(i).item_class_order_number :=
getItemClassOrderNumber(itemClassIdIn =>
getItemClassId(itemClassNameIn => engInsertedApproverList(i).item_class));
engInsertedApproverList(i).item_order_number :=
getItemOrderNumber(itemClassNameIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id);
engInsertedApproverList(i).sub_list_order_number :=
getSublistOrderNum(itemClassNameIn => ame_util.headerItemClassName,
authorityIn => ame_util.postApprover);
engInsertedApproverList(i).action_type_order_number := getNullActionTypeOrderNumber;
engInsertedApproverList(i).group_or_chain_order_number := 1;
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(absolute order) ::: ' || engInsertedApproverList(i).name
);
insertApprover(indexIn => tempIndex,
approverIn => engInsertedApproverList(i),
adjustMemberOrderNumbersIn => true,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => tempIndex
,insertionOrderIn => engInsertionOrderList(i));
elsif(engInsertionOrderTypeList(i) in (ame_util.afterApprover,
ame_util.beforeApprover)) then
tempIndex := 1; /* post-increment */
the insertion parameter.
*/
if(engStApprovers(tempIndex).name = parameterFields(1) and
engStApprovers(tempIndex).occurrence = parameterFields(6) and
engStApprovers(tempIndex).group_or_chain_id = parameterFields(5) and
engStApprovers(tempIndex).action_type_id = parameterFields(4) and
engStApprovers(tempIndex).item_id = parameterFields(3) and
engStApprovers(tempIndex).item_class = parameterFields(2)) then
tempBoolean := true;
if(engInsertionOrderTypeList(i) = ame_util.afterApprover) then
tempIndex2 := tempIndex + 1;
engInsertedApproverList(i).action_type_id := engStApprovers(tempIndex).action_type_id;
engInsertedApproverList(i).group_or_chain_id := engStApprovers(tempIndex).group_or_chain_id;
engInsertedApproverList(i).occurrence :=
getHandlerOccurrence(nameIn => engInsertedApproverList(i).name,
itemClassIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id,
actionTypeIdIn => engInsertedApproverList(i).action_type_id,
groupOrChainIdIn => engInsertedApproverList(i).group_or_chain_id);
if(engInsertionIsSpecialForwardee(i) = ame_util.booleanTrue) then
engInsertedApproverList(i).source := ame_util.specialForwardInsertion;
if(engInsertionOrderTypeList(i) = ame_util.afterApprover and
engStApprovers(tempIndex).approval_status = ame_util.forwardStatus) then
engInsertedApproverList(i).source := ame_util.forwardInsertion;
elsif(engInsertionOrderTypeList(i) = ame_util.afterApprover and
engStApprovers(tempIndex).approval_status = ame_util.approveAndForwardStatus) then
engInsertedApproverList(i).source := ame_util.approveAndForwardInsertion;
engInsertedApproverList(i).source := ame_util.otherInsertion;
engInsertedApproverList(i).item_class_order_number := engStApprovers(tempIndex).item_class_order_number;
engInsertedApproverList(i).item_order_number := engStApprovers(tempIndex).item_order_number;
engInsertedApproverList(i).sub_list_order_number := engStApprovers(tempIndex).sub_list_order_number;
engInsertedApproverList(i).action_type_order_number := engStApprovers(tempIndex).action_type_order_number;
engInsertedApproverList(i).group_or_chain_order_number :=
engStApprovers(tempIndex).group_or_chain_order_number;
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(after/before approver) ::: ' || engInsertedApproverList(i).name
);
insertApprover(indexIn => tempIndex2,
approverIn => engInsertedApproverList(i),
adjustMemberOrderNumbersIn => true,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => tempIndex2
,insertionOrderIn => engInsertionOrderList(i));
ame_api3.getAvailableInsertions for these order types.
*/
if(engStApproversCount = 0) then
/*
The four first/last pre/post order types can only occur in an empty list if the insertion
is for the header item. Treat any other case of these order types as displaced, here.
In the code blocks below for these four order types, require that engStApproversCount > 0.
*/
if(parameterFields(3) = ame_util.headerItemClassName and
parameterFields(2) = engTransactionId) then
engInsertedApproverList(i).occurrence := 1;
engInsertedApproverList(i).item_class_order_number :=
getItemClassOrderNumber(itemClassIdIn =>
getItemClassId(itemClassNameIn => ame_util.headerItemClassName));
engInsertedApproverList(i).item_order_number := 1;
engInsertedApproverList(i).sub_list_order_number :=
getSublistOrderNum(itemClassNameIn => ame_util.headerItemClassName,
authorityIn => engInsertedApproverList(i).authority);
engInsertedApproverList(i).action_type_order_number := getNullActionTypeOrderNumber;
engInsertedApproverList(i).group_or_chain_order_number := 1;
engInsertedApproverList(i).member_order_number := 1;
insertApprover(indexIn => 1,
approverIn => engInsertedApproverList(i),
adjustMemberOrderNumbersIn => false,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => 1
,insertionOrderIn => engInsertionOrderList(i));
engInsertedApproverList(i).occurrence := 1;
engInsertedApproverList(i).item_class_order_number :=
getItemClassOrderNumber(itemClassIdIn =>
getItemClassId(itemClassNameIn => engInsertedApproverList(i).item_class));
engInsertedApproverList(i).item_order_number := 1;
engInsertedApproverList(i).sub_list_order_number :=
getSublistOrderNum(itemClassNameIn => engInsertedApproverList(i).item_class,
authorityIn => engInsertedApproverList(i).authority);
engInsertedApproverList(i).action_type_order_number := getNullActionTypeOrderNumber;
engInsertedApproverList(i).group_or_chain_order_number := 1;
engInsertedApproverList(i).member_order_number := 1;
displacedInserteeIndexes(displacedInserteeIndexes.count + 1) := i;
if(engInsertionOrderTypeList(i) = ame_util.firstPostApprover) then
/*
Insert at tempIndex if it's non-null, after checking the possible cases.
Recall that the case where engStApproversCount = 0 is handled at the
start of the procedure, that the item id is in parameterFields(3), and
that the item-class ID is in parameterFields(2). Here are the cases
requiring insertion (always at j + 1).
1. j = 0,
post-approver for the right item at j + 1 = 1
2. j > 0,
j < engStApproversCount,
non-post-approver for the right item at j,
post-approver for the right item at j + 1
3. j > 0,
j < engStApproversCount,
non-post-approver for the right item at j,
any approver for the wrong item at j + 1
4. j > 0,
j = engStApproversCount,
non-post-approver for the right item at j
5. j > 0,
j < engStApproversCount,
any approver for the wrong item at j,
post-approver for the right item at j + 1
Finally, note that the way the code is written, case 4 must be checked
before cases 2, 3, and 5, to ensure that for these cases,
j + 1 <= engStApproversCount.
*/
/* The zero lower limit is intentional. */
for j in 0 .. engStApproversCount loop
tempIndex2 := j + 1;
elsif(engInsertionOrderTypeList(i) = ame_util.firstPreApprover) then
/*
Insert at tempIndex if it's non-null, after checking the possible cases.
Recall that the case where engStApproversCount = 0 is handled at the
start of the procedure. Here are the cases requiring insertion (always
at j).
1. j = 1,
any approver for the right item at j = 1
2. j > 1,
any approver for the wrong item at j - 1,
any approver for the right item at j
*/
for j in 1 .. engStApproversCount loop
if(j = 1) then
if(engStApprovers(1).item_id = parameterFields(3) and
engStApprovers(1).item_class = parameterFields(2)) then /* case 1 */
tempIndex := 1;
elsif(engInsertionOrderTypeList(i) = ame_util.lastPostApprover) then
/*
Insert at tempIndex if it's non-null, after checking the possible cases.
Recall that the case where engStApproversCount = 0 is handled at the
start of the procedure. Here are the cases requiring insertion (always
at j + 1).
1. j = engStApproversCount,
any approver for the right item at j
2. j < engStApproversCount,
any approver for right item at j,
any approver for wrong item at j + 1
*/
for j in 1 .. engStApproversCount loop
if(j = engStApproversCount) then
if(engStApprovers(j).item_id = parameterFields(3) and
engStApprovers(j).item_class = parameterFields(2)) then
tempIndex := engStApproversCount + 1;
else /* engInsertionOrderTypeList(i) = ame_util.lastPreApprover */
/*
Insert at tempIndex if it's non-null, after checking the possible cases.
Recall that the case where engStApproversCount = 0 is handled at the
start of the procedure. Here are the cases (always inserting at j):
1. j = 1,
non-pre-approver for the right item at 1
2. j > 1,
j <= engStApproversCount,
the approver at j - 1 is for the right item,
the approver at j is for the right item,
the approver at j - 1 is a pre-approver,
the approver at j is not a pre-approver
3. j > 1,
j <= engStApproversCount,
any approver at j - 1 for the wrong item,
the approver at j is for the right item,
the approver at j is not a pre-approver
*/
for j in 1 .. engStApproversCount loop
if(j = 1) then
if(engStApprovers(1).item_id = parameterFields(3) and
engStApprovers(1).item_class = parameterFields(2) and
engStApprovers(1).authority <> ame_util.preApprover) then /* case 1 */
tempIndex := 1;
/* Set the remaining fields in the insertee's approverRecord2. */
engInsertedApproverList(i).source := ame_util.otherInsertion;
engInsertedApproverList(i).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
/* engInsertedApproverList(i).action_type_id := ame_util.nullInsertionActionTypeId; */
engInsertedApproverList(i).occurrence :=
getHandlerOccurrence(nameIn => engInsertedApproverList(i).name,
itemClassIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id,
actionTypeIdIn => engInsertedApproverList(i).action_type_id,
groupOrChainIdIn => engInsertedApproverList(i).group_or_chain_id);
engInsertedApproverList(i).item_class_order_number :=
getItemClassOrderNumber(itemClassIdIn =>
getItemClassId(itemClassNameIn => engInsertedApproverList(i).item_class));
engInsertedApproverList(i).item_order_number :=
getItemOrderNumber(itemClassNameIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id);
if engInsertionOrderTypeList(i) = ame_util.firstPreApprover or
engInsertionOrderTypeList(i) = ame_util.lastPreApprover then
engInsertedApproverList(i).sub_list_order_number :=
getSublistOrderNum(itemClassNameIn => engInsertedApproverList(i).item_class,
authorityIn => ame_util.preApprover);
elsif engInsertionOrderTypeList(i) = ame_util.firstPostApprover or
engInsertionOrderTypeList(i) = ame_util.lastPostApprover then
engInsertedApproverList(i).sub_list_order_number :=
getSublistOrderNum(itemClassNameIn => engInsertedApproverList(i).item_class,
authorityIn => ame_util.postApprover);
/* engInsertedApproverList(i).action_type_order_number := getNullActionTypeOrderNumber; */
engInsertedApproverList(i).action_type_order_number := 1;
engInsertedApproverList(i).group_or_chain_order_number := 1;
engInsertedApproverList(i).group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
/* engInsertedApproverList(i).action_type_id := ame_util.nullInsertionActionTypeId;*/
engInsertedApproverList(i).occurrence :=
getHandlerOccurrence(nameIn => engInsertedApproverList(i).name,
itemClassIn => engInsertedApproverList(i).item_class,
itemIdIn => engInsertedApproverList(i).item_id,
actionTypeIdIn => engInsertedApproverList(i).action_type_id,
groupOrChainIdIn => engInsertedApproverList(i).group_or_chain_id);
engInsertedApproverList(i).item_class_order_number :=
engStApprovers(tempAnchorIndex).item_class_order_number;
engInsertedApproverList(i).item_order_number :=
engStApprovers(tempAnchorIndex).item_order_number;
engInsertedApproverList(i).sub_list_order_number :=
engStApprovers(tempAnchorIndex).sub_list_order_number;
/*engInsertedApproverList(i).action_type_order_number := */
/* engStApprovers(tempAnchorIndex).action_type_order_number; */
if engInsertionOrderTypeList(i) = ame_util.firstPreApprover or
engInsertionOrderTypeList(i) = ame_util.firstPostApprover then
engInsertedApproverList(i).action_type_order_number :=
engStApprovers(tempAnchorIndex).action_type_order_number - 1;
elsif engInsertionOrderTypeList(i) = ame_util.lastPreApprover or
engInsertionOrderTypeList(i) = ame_util.lastPostApprover then
engInsertedApproverList(i).action_type_order_number :=
engStApprovers(tempAnchorIndex).action_type_order_number + 1;
engInsertedApproverList(i).group_or_chain_order_number :=
engStApprovers(tempAnchorIndex).group_or_chain_order_number;
/* Perform the insertion if the proper position has been located. */
if(tempIndex is null) then
displacedInserteeIndexes(displacedInserteeIndexes.count + 1) := i;
if engInsertionOrderTypeList(i) = ame_util.firstPreApprover or
engInsertionOrderTypeList(i) = ame_util.firstPostApprover then
engInsertedApproverList(i).action_type_id := -1;
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(first pre/first post approver) ::: ' || engInsertedApproverList(i).name
);
insertApprover(indexIn => tempIndex,
approverIn => engInsertedApproverList(i),
adjustMemberOrderNumbersIn => true,
approverLocationIn => ame_util.firstAmongEquals,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => tempIndex
,insertionOrderIn => engInsertionOrderList(i));
elsif engInsertionOrderTypeList(i) = ame_util.lastPreApprover or
engInsertionOrderTypeList(i) = ame_util.lastPostApprover then
engInsertedApproverList(i).action_type_id := -2;
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(last pre/last post approver) ::: ' || engInsertedApproverList(i).name
);
insertApprover(indexIn => tempIndex,
approverIn => engInsertedApproverList(i),
adjustMemberOrderNumbersIn => true,
approverLocationIn => ame_util.lastAmongEquals,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => tempIndex
,insertionOrderIn => engInsertionOrderList(i));
Insert any displaced approvers at the end of their items' lists, if possible; and
for i in 1 .. displacedInserteeIndexes.count loop
parameterFields.delete;
parseFields(stringIn => engInsertionParameterList(displacedInserteeIndexes(i)),
fieldsOut => parameterFields);
/* Set tempBoolean false if displaced approver i is inserted at the end of its item's list. */
tempBoolean := true;
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(displaced) ::: ' || engInsertedApproverList(displacedInserteeIndexes(i)).name
);
insertApprover(indexIn => j + 1,
approverIn => engInsertedApproverList(displacedInserteeIndexes(i)),
adjustMemberOrderNumbersIn => true,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => j + 1
,insertionOrderIn => engInsertionOrderList(displacedInserteeIndexes(i)));
,'ame_engine.processAdhocInsertions'
,'Adhoc Insertion approver(displaced) ::: ' || engInsertedApproverList(displacedInserteeIndexes(i)).name
);
insertApprover(indexIn => engStApproversCount + 1,
approverIn => engInsertedApproverList(displacedInserteeIndexes(i)),
adjustMemberOrderNumbersIn => false,
inserteeIndexIn => i,
currentInsIndex => i);
populateInsertionIndexes(indexIn => engStApproversCount + 1
,insertionOrderIn => engInsertionOrderList(displacedInserteeIndexes(i)));
routineNameIn => 'processAdHocInsertions',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end processAdHocInsertions;
procedure populateInsertionIndexes(indexIn in integer
,insertionOrderIn in integer) as
firstIndex integer;
firstIndex := engStInsertionIndexes.first;
if(engStInsertionIndexes.exists(firstIndex)) then
lastIndex := engStInsertionIndexes.last;
while engStInsertionIndexes.exists(lastIndex) and lastIndex >= indexIn loop
tempValue := engStInsertionIndexes(lastIndex);
engStInsertionIndexes(lastIndex + 1) := tempValue;
engStInsertionIndexes.delete(lastIndex);
lastIndex := engStInsertionIndexes.prior(lastIndex + 1);
engStInsertionIndexes(indexIn) := insertionOrderIn;
engStInsertionIndexes(indexIn) := insertionOrderIn;
end populateInsertionIndexes;
for i in 1 .. engDeletedApproverList.count loop
for j in 1 .. engStApprovers.count loop
if(engStApprovers(j).name = engDeletedApproverList(i).name and
engStApprovers(j).occurrence = engDeletedApproverList(i).occurrence and
engStApprovers(j).group_or_chain_id = engDeletedApproverList(i).group_or_chain_id and
engStApprovers(j).action_type_id = engDeletedApproverList(i).action_type_id and
engStApprovers(j).item_id = engDeletedApproverList(i).item_id and
engStApprovers(j).item_class = engDeletedApproverList(i).item_class) then
engStApprovers(j).approval_status := ame_util.suppressedStatus;
tempExcOrdCondAttributeIds.delete;
copy it at each iteration, so we can freely delete entries in the
copy.
*/
tempAttributeIdsToMatch.delete;
tempAttributeIdsToMatch.delete(tempAttributeId);
exception's ordinary conditions' attributes were matched; so, delete the authority
engAppItemClassIds.delete(j);
engAppItemIds.delete(j);
engAppRuleIds.delete(j);
engRuleAppliedYN.delete(j);
engAppRuleTypes.delete(j);
engAppActionTypeIds.delete(j);
engAppParameters.delete(j);
engAppParameterTwos.delete(j);
engAppRuleItemClassIds.delete(j);
engAppAppItemIds.delete(j);
/* Re-compact the engApp lists if any authority rules got deleted. */
if(authorityRuleSuppressed) then
compactEngAppLists(compactPrioritiesIn => false,
compactActionTypeIdsIn => true,
compactParametersIn => true);
per-item approver responses. See the ame_api2.updateApprovalStatus code for details.
Also, don't suppress special forwardees. See bug 3401298 for details.
*/
if(engStApprovers(j).approval_status is null and
engStApprovers(j).source not like ame_util.specialForwardInsertion || '%') then
engStApprovers(j).approval_status := ame_util.repeatedStatus;
repeatedIndexesList.delete;
/* Update the current action-type state variables. */
engAppHandlerFirstIndex := tempIndex;
/* Update the current state variables. */
currentFirstRuleIndexes.delete;
currentLastRuleIndexes.delete;
ruleDeleted boolean;
ruleDeleted := false;
tempThresholds.delete;
engAppRuleIds.delete(i);
engRuleAppliedYN.delete(i);
engAppItemClassIds.delete(i);
engAppItemIds.delete(i);
engAppPriorities.delete(i);
engAppApproverCategories.delete(i);
engAppRuleTypes.delete(i);
engAppRuleItemClassIds.delete(i);
engAppAppItemIds.delete(i);
ruleDeleted := true;
engAppRuleIds.delete(oldTempIndex);
engRuleAppliedYN.delete(oldTempIndex);
engAppItemClassIds.delete(oldTempIndex);
engAppItemIds.delete(oldTempIndex);
engAppPriorities.delete(oldTempIndex);
engAppApproverCategories.delete(oldTempIndex);
engAppRuleTypes.delete(oldTempIndex);
engAppRuleItemClassIds.delete(oldTempIndex);
engAppAppItemIds.delete(oldTempIndex);
ruleDeleted := true;
/* If relative-priority processing deleted one or more rules from the engApp lists, compact them. */
if(ruleDeleted) then
compactEngAppLists(compactPrioritiesIn => true,
compactActionTypeIdsIn => false,
compactParametersIn => false);
tempSurrogateApprover.api_insertion := ame_util.apiInsertion;
tempSurrogateApprover.source := ame_util.surrogateInsertion;
instead of in insertApprover. This will ensure that the surrogate has the same
order as the unresponsive approver. Also changed call to insertApprover so that
adjustMemberOrderNumbers is false */
tempSurrogateApprover.member_order_number := engStApprovers(tempIndex).member_order_number;
insertApprover(indexIn => tempIndex + 1,
approverIn => tempSurrogateApprover,
adjustMemberOrderNumbersIn => false);
fetchInsertionsIn in boolean,
fetchDeletionsIn in boolean,
fetchAttributeValuesIn in boolean,
fetchInactiveAttValuesIn in boolean,
processProductionActionsIn in boolean,
processProductionRulesIn in boolean,
updateCurrentApproverListIn in boolean,
updateOldApproverListIn in boolean,
processPrioritiesIn in boolean,
prepareItemDataIn in boolean,
prepareRuleIdsIn in boolean,
prepareRuleDescsIn in boolean,
prepareApproverTreeIn in boolean default false,
transactionIdIn in varchar2,
ameApplicationIdIn in integer default null,
fndApplicationIdIn in integer default null,
transactionTypeIdIn in varchar2 default null) as
badLocalTransException exception;
engLMParameterOnes.delete;
engLMParameterTwos.delete;
engGroupMemberGroupIds.delete;
engGroupMemberNames.delete;
engGroupMemberOrderNumbers.delete;
engGroupMemberDisplayNames.delete;
engGroupMemberOrigSystems.delete;
engGroupMemberOrigSystemIds.delete;
engGroupUseItemBind.delete;
engAppActionTypeIds.delete;
engAppApproverCategories.delete;
engAppItemClassIds.delete;
engAppItemIds.delete;
engAppLMSubItemClassIds.delete;
engAppLMSubItemIds.delete;
engAppParameters.delete;
engAppParameterTwos.delete;
engAppPriorities.delete;
engAppRuleIds.delete;
engRuleAppliedYN.delete;
engAppRuleTypes.delete;
engAppRuleItemClassIds.delete;
engAppAppItemIds.delete;
engAppPerAppProdFirstIndexes.delete;
engAppPerAppProdRuleIds.delete;
engAppPerAppProdVariableNames.delete;
engAppPerAppProdVariableValues.delete;
engStApprovers.delete;
engStApproversTree.delete;
engStItemClasses.delete;
engStItemIds.delete;
engStItemIndexes.delete;
engStItemSources.delete;
engStProductionIndexes.delete;
engStRepeatedIndexes.delete;
engStRepeatedAppIndexes.delete;
engStRuleDescriptions.delete;
engStRuleIds.delete;
engStRuleIndexes.delete;
engStSourceTypes.delete;
engStSuspendedItems.delete;
engStSuspendedItemClasses.delete;
engStProductionsTable.delete;
engStVariableNames.delete;
engStVariableValues.delete;
engStItemAppProcessCompleteYN.delete;
engConfigVarNames.delete;
engConfigVarValues.delete;
engForwardingBehaviors.delete;
engPriorityModes.delete;
engPriorityThresholds.delete;
engActionTypeChainOrderModes.delete;
engActionTypeOrderNumbers.delete;
engActionTypeVotingRegimes.delete;
engActionTypeNames.delete;
engActionTypeUsages.delete;
engActionTypePackageNames.delete;
engItemClassIds.delete;
engItemClassIndexes.delete;
engItemClassItemIdIndexes.delete;
engItemClassNames.delete;
engItemClassOrderNumbers.delete;
engItemClassParModes.delete;
engItemClassSublistModes.delete;
engItemCounts.delete;
engItemIds.delete;
engAttributeIsStatics.delete;
engAttributeItemClassIds.delete;
engAttributeNames.delete;
engAttributeTypes.delete;
engAttributeValueIndexes.delete;
engAttributeValues1.delete;
engAttributeValues2.delete;
engAttributeValues3.delete;
engAttributeQueries.delete;
engAttributeVariant.delete;
engHeaderConditionValues.delete;
engACUsageConditionIds.delete;
engACUsageFirstIndexes.delete;
engACUsageItemClassCounts.delete;
engACUsageRuleIds.delete;
engACUsageRulePriorities.delete;
engACUsageRuleTypes.delete;
engACUsageRuleApprCategories.delete;
engACAttributeIds.delete;
engACConditionTypes.delete;
engACIncludeLowerLimits.delete;
engACIncludeUpperLimits.delete;
engACParameterOnes.delete;
engACParameterThrees.delete;
engACParameterTwos.delete;
engACStringValueCounts.delete;
engACStringValueFirstIndexes.delete;
engACStringValues.delete;
engOldApproverList.delete;
engInsertedApproverList.delete;
engInsertionOrderTypeList.delete;
engInsertionParameterList.delete;
engInsertionIsSpecialForwardee.delete;
engDeletedApproverList.delete;
engStInsertionIndexes.delete;
/*delete deviation related info*/
engDeviationResultList.delete;
engInsertionReasonList.delete;
engInsertionDateList.delete;
engSuppressionDateList.delete;
engSupperssionReasonList.delete;
engInsApproverIndex.delete;
engUpdateCurrentApproverList := updateCurrentApproverListIn;
engUpdateOldApproverList := updateOldApproverListIn;
updateCurrentApproverListIn or
updateOldApproverListIn or
processPrioritiesIn or
prepareItemDataIn or
prepareRuleIdsIn or
prepareRuleDescsIn) then
/* Fetch the active condition usages. */
fetchActiveConditionUsages;
/* Optionally fetch the inserted-approvers list. */
if(fetchInsertionsIn) then
fetchInsertedApprovers;
fetchDeletedApprovers;
delete
from ame_temp_handler_states
where
handler_name = handlerName and
application_id = engAmeApplicationId and
((parameter is null and parameterIn is null) or
(parameter = parameterIn));
insert into ame_temp_handler_states(
handler_name,
row_timestamp,
application_id,
parameter,
state)
values(
handlerName,
sysdate, /* Don't use engEffectiveRuleDate here. */
engAmeApplicationId,
parameterIn,
stateIn);
procedure setInsertedApprovalStatus(currentApproverIndexIn in integer
,approvalStatusIn in varchar2) as
begin
for i in 1..engInsApproverIndex.count loop
if engInsApproverIndex(i) = currentApproverIndexIn then
engInsertedApproverList(i).approval_status := approvalStatusIn;
update ame_temp_insertions
set approval_status = approvalStatusIn
where application_id = engAmeApplicationId
and transaction_id = engTransactionId
and insertion_order = engInsertionOrderList(i)
and order_type = engInsertionOrderTypeList(i)
and parameter = engInsertionParameterList(i)
and api_insertion = engInsertedApproverList(i).api_insertion
and authority = engInsertedApproverList(i).authority;
routineNameIn => 'setInsertedApprovalStatus',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end setInsertedApprovalStatus;
/* Update the occurrence values of the same approver wherever the approver occurs later in the same chain. */
for i in approverIndexIn + 1 .. engStApprovers.count loop
if(currentGroupOrChainId <> engStApprovers(i).group_or_chain_id or
engStApprovers(approverIndexIn).action_type_id <> engStApprovers(i).action_type_id or
engStApprovers(approverIndexIn).item_id <> engStApprovers(i).item_id or
engStApprovers(approverIndexIn).item_class <> engStApprovers(i).item_class) then
exit;
delete the rest of the chain.
*/
truncateCount := lastIndexToTruncate - firstIndexToTruncate + 1;
/* Code to delete the approvers from the tree whenever there are approver suppressions */
if engPrepareApproverTree then
siblingTreeNodeIndex := ame_util.noSiblingIndex;
engStApproversTree.delete(loopIndex);
engStApproversTree.delete(loopIndex);
/* Delete the last truncateCount places. */
engStApprovers.delete(engStApproversCount - truncateCount + 1, engStApproversCount);
delete from ame_temp_trans_locks
where
fnd_application_id = fndApplicationIdIn and
((transaction_type_id is null and transactionTypeIdIn is null) or
transaction_type_id = transactionTypeIdIn) and
transaction_id = transactionIdIn;
procedure updateTransactionState(isTestTransactionIn in boolean,
isLocalTransactionIn in boolean,
fetchConfigVarsIn in boolean,
fetchOldApproversIn in boolean,
fetchInsertionsIn in boolean,
fetchDeletionsIn in boolean,
fetchAttributeValuesIn in boolean,
fetchInactiveAttValuesIn in boolean,
processProductionActionsIn in boolean,
processProductionRulesIn in boolean,
updateCurrentApproverListIn in boolean,
updateOldApproverListIn in boolean,
processPrioritiesIn in boolean,
prepareItemDataIn in boolean,
prepareRuleIdsIn in boolean,
prepareRuleDescsIn in boolean,
prepareApproverTreeIn in boolean default false,
transactionIdIn in varchar2,
ameApplicationIdIn in integer default null,
fndApplicationIdIn in integer default null,
transactionTypeIdIn in varchar2 default null) as
configVarException exception;
fetchInsertionsIn => fetchInsertionsIn,
fetchDeletionsIn => fetchDeletionsIn,
fetchAttributeValuesIn => fetchAttributeValuesIn,
fetchInactiveAttValuesIn => fetchInactiveAttValuesIn,
processProductionActionsIn => processProductionActionsIn,
processProductionRulesIn => processProductionRulesIn,
updateCurrentApproverListIn => updateCurrentApproverListIn,
updateOldApproverListIn => updateOldApproverListIn,
processPrioritiesIn => processPrioritiesIn,
prepareItemDataIn => prepareItemDataIn,
prepareRuleIdsIn => prepareRuleIdsIn,
prepareRuleDescsIn => prepareRuleDescsIn,
prepareApproverTreeIn => prepareApproverTreeIn,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => ameApplicationIdIn,
fndApplicationIdIn => fndApplicationIdIn,
transactionTypeIdIn => transactionTypeIdIn);
,'ame_engine.updateTransactionState'
,'AME Application Id ::: ' || engAmeApplicationId
);
,'ame_engine.updateTransactionState'
,'AME Transaction Id ::: ' || engTransactionId
);
if updateOldApproverListIn then
logTransaction;
updateCurrentApproverListIn or
updateOldApproverListIn or
processPrioritiesIn or
prepareItemDataIn or
prepareRuleIdsIn or
prepareRuleDescsIn) then
if(not fetchConfigVarsIn) then
raise configVarException;
if(updateCurrentApproverListIn or
updateOldApproverListIn) then
processRules(processOnlyProductionsIn => false);
,'ame_engine.updateTransactionState'
,'Approver count after processRules ::: ' || engStApprovers.count
);
if(updateCurrentApproverListIn or
updateOldApproverListIn) then
processAdHocInsertions;
,'ame_engine.updateTransactionState'
,'Approver count after processAdHocInsertions ::: ' || engStApprovers.count
);
,'ame_engine.updateTransactionState'
,'Approver count after processUnresponsiveApprovers ::: ' || engStApprovers.count
);
,'ame_engine.updateTransactionState'
,'Approver count after repeatSubstitutions ::: ' || engStApprovers.count
);
,'ame_engine.updateTransactionState'
,'Approver count after processSuppressions ::: ' || engStApprovers.count
);
,'ame_engine.updateTransactionState'
,'Approver count after processRepeatedApprovers ::: ' || engStApprovers.count
);
if updateOldApproverListIn then
ame_approver_deviation_pkg.updateDeviationState(
engAmeApplicationId
,engTransactionId
,engDeviationResultList
,engStApprovalProcessCompleteYN
,engStApprovers);
,'ame_engine.updateTransactionState'
,'**************** No Approvers ******************'
);
,'ame_engine.updateTransactionState'
,'Approver ::: ' || engStApprovers(i).name || ' Order Number ::: ' || engStApprovers(i).approver_order_number
);
if(updateOldApproverListIn) then
updateOldApproverList;
routineNameIn => 'updateTransactionState',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateTransactionState',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateTransactionState;
procedure updateOldApproverList as
tempActionTypeIds ame_util.idList;
delete from ame_temp_old_approver_lists
where
application_id = engAmeApplicationId and
transaction_id = engTransactionId;
Bulk insert the transaction's current state into ame_temp_old_approver_lists.
Take the state from engStApprovers, to account for all operations on the approver list.
*/
ame_util.convertApproversTable2ToValues(approversTableIn => engStApprovers,
namesOut => tempNames,
itemClassesOut => tempItemClasses,
itemIdsOut => tempItemIds,
apiInsertionsOut => tempApiValues,
authoritiesOut => tempAuthorityValues,
actionTypeIdsOut => tempActionTypeIds,
groupOrChainIdsOut => tempGroupOrChainIds,
occurrencesOut => tempOccurrences,
approverCategoriesOut => tempCategories,
statusesOut => tempStatuses);
setInsertedApprovalStatus(currentApproverIndexIn => i
,approvalStatusIn => engStApprovers(i).approval_status);
insert into ame_temp_old_approver_lists(
transaction_id,
application_id,
order_number,
name,
item_class,
item_id,
api_insertion,
authority,
action_type_id,
group_or_chain_id,
occurrence,
approver_category,
approval_status) values(
engTransactionId,
engAmeApplicationId,
tempOrderNumbers(i),
tempNames(i),
tempItemClasses(i),
tempItemIds(i),
tempApiValues(i),
tempAuthorityValues(i),
tempActionTypeIds(i),
tempGroupOrChainIds(i),
tempOccurrences(i),
tempCategories(i),
tempStatuses(i));
routineNameIn => 'updateOldApproverList',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateOldApproverList;
procedure updateApprovalStatus(applicationIdIn in number,
transactionTypeIn in varchar2,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord2,
notificationIn in ame_util2.notificationRecord
default ame_util2.emptyNotificationRecord,
forwardeeIn in ame_util.approverRecord2 default
ame_util.emptyApproverRecord2,
updateItemIn in boolean default false) as
ameApplicationId integer;
apiInsertionException exception;
insertedApprover ame_util.approverRecord2;
tempInsertionOrder integer;
tempParameter ame_temp_insertions.parameter%type;
delete from ame_exceptions_log
where
transaction_id = transactionIdIn and
application_id = ameApplicationId;
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => true,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn);
if(approver.source like (ame_util.specialForwardInsertion || '%')) then
approverInIsSpecialForwardee := true;
/* If approverInIsSpecialForwardee, silently convert api_insertion to
ame_util.apiInsertion for forwardee */
if approverInIsSpecialForwardee then
forwardee.api_insertion := ame_util.apiInsertion;
/* Make sure the forwardee has the correct api_insertion value for COA forwarders. */
if(/* Special forwardees can only do ad-hoc forwardings. */
(approverInIsSpecialForwardee and
forwardee.api_insertion <> ame_util.apiInsertion) or
/* Ad-hoc approvers can only do ad-hoc forwardings. */
((not approverInIsSpecialForwardee and
approver.authority <> ame_util.authorityApprover or
approver.api_insertion = ame_util.apiInsertion) and
forwardee.api_insertion <> ame_util.apiInsertion) or
/* COA approvers other than special forwardees can only do COA forwardings. */
(not approverInIsSpecialForwardee and
approver.authority = ame_util.authorityApprover and
approver.api_insertion <> ame_util.apiInsertion and
forwardee.api_insertion <> ame_util.apiAuthorityInsertion)) then
raise apiInsertionException;
make sure the forwardee is an ad-hoc insertion. Make this change silently,
so calling applications don't have to be originating-system aware.
*/
if(approver.orig_system <> forwardee.orig_system) then
forwardee.api_insertion := ame_util.apiInsertion;
/* Log the status update to the history table. */
if(approverInIndex is null) then
insert into ame_approvals_history(transaction_id,
application_id,
approval_status,
row_timestamp,
item_class,
item_id,
name,
approver_category,
action_type_id,
group_or_chain_id,
occurrence)
values(transactionIdIn,
ameApplicationId,
decode(approver.approval_status,
ame_util.nullStatus, ame_util.nullHistoryStatus,
null, ame_util.nullHistoryStatus,
approver.approval_status),
sysdate,
approver.item_class,
approver.item_id,
approver.name,
approver.approver_category,
ame_util.nullHistoryActionTypeId,
ame_util.nullHistoryGroupOrChainId,
ame_util.nullHistoryOccurrence);
update AME_TRANS_APPROVAL_HISTORY
set date_cleared = sysdate
where transaction_id = transactionIdIn
and application_id = ameApplicationId
and name = approver.name
and (approver.item_class is null or
item_class = approver.item_class)
and (approver.item_id is null or
item_id = approver.item_id)
and (approver.action_type_id is null or
action_type_id = approver.action_type_id)
and (approver.group_or_chain_id is null or
group_or_chain_id = approver.group_or_chain_id)
and (approver.occurrence is null or
occurrence = approver.occurrence)
and date_cleared is null;
insertIntoTransApprovalHistory
(transactionIdIn => transactionIdIn
,applicationIdIn => ameApplicationId
,orderNumberIn => approver.approver_order_number
,nameIn => approver.name
,appCategoryIn => approver.approver_category
,itemClassIn => approver.item_class
,itemIdIn => approver.item_id
,actionTypeIdIn => ame_util.nullHistoryActionTypeId
,authorityIn => approver.authority
,statusIn => approver.approval_status
,grpOrChainIdIn => ame_util.nullHistoryGroupOrChainId
,occurrenceIn => ame_util.nullHistoryOccurrence
,apiInsertionIn => approver.api_insertion
,memberOrderNumberIn => approver.member_order_number
,notificationIdIn => notificationIn.notification_id
,userCommentsIn => notificationIn.user_comments
,dateClearedIn => sysdate
,historyTypeIn => 'APPROVERNOTPRESENT');
/* Insert a warning into AME's exception log and return. */
errorCode := -20001;
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
insert into ame_approvals_history(transaction_id,
application_id,
approval_status,
row_timestamp,
item_class,
item_id,
name,
approver_category,
action_type_id,
group_or_chain_id,
occurrence)
values(transactionIdIn,
ameApplicationId,
decode(approver.approval_status,
ame_util.nullStatus, ame_util.nullHistoryStatus,
null, ame_util.nullHistoryStatus,
approver.approval_status),
sysdate,
approver.item_class,
approver.item_id,
approver.name,
approver.approver_category,
approver.action_type_id,
approver.group_or_chain_id,
approver.occurrence);
insertIntoTransApprovalHistory
(transactionIdIn => transactionIdIn
,applicationIdIn => ameApplicationId
,orderNumberIn => approver.approver_order_number
,nameIn => approver.name
,appCategoryIn => approver.approver_category
,itemClassIn => approver.item_class
,itemIdIn => approver.item_id
,actionTypeIdIn => approver.action_type_id
,authorityIn => approver.authority
,statusIn => approver.approval_status
,grpOrChainIdIn => approver.group_or_chain_id
,occurrenceIn => approver.occurrence
,apiInsertionIn => approver.api_insertion
,memberOrderNumberIn => approver.member_order_number
,notificationIdIn => notificationIn.notification_id
,userCommentsIn => notificationIn.user_comments
,dateClearedIn => sysdate
,historyTypeIn => 'APPROVERPRESENT');
insertIntoTransApprovalHistory
(transactionIdIn => transactionIdIn
,applicationIdIn => ameApplicationId
,orderNumberIn => approver.approver_order_number
,nameIn => approver.name
,appCategoryIn => approver.approver_category
,itemClassIn => approver.item_class
,itemIdIn => approver.item_id
,actionTypeIdIn => approver.action_type_id
,authorityIn => approver.authority
,statusIn => approver.approval_status
,grpOrChainIdIn => approver.group_or_chain_id
,occurrenceIn => approver.occurrence
,apiInsertionIn => approver.api_insertion
,memberOrderNumberIn => approver.member_order_number
,notificationIdIn => notificationIn.notification_id
,userCommentsIn => notificationIn.user_comments
,dateClearedIn => null
,historyTypeIn => 'APPROVERPRESENT');
Update the status of approverIn in ame_temp_old_approver_lists. If updateItemIn
is true, update also any other occurrences of the same approver for the same item
class and item ID.
*/
if(updateItemIn) then
if(approver.approval_status in (ame_util.forwardStatus, ame_util.approveAndForwardStatus)) then
/* Update the forwarder proper. */
update ame_temp_old_approver_lists
set approval_status = approver.approval_status
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
name = approver.name and
item_class = approver.item_class and
item_id = approver.item_id and
action_type_id = approver.action_type_id and
group_or_chain_id = approver.group_or_chain_id and
occurrence = approver.occurrence;
update ame_temp_old_approver_lists
set approval_status = ame_util.suppressedStatus
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
name = approver.name and
item_class = approver.item_class and
item_id = approver.item_id and
(action_type_id <> approver.action_type_id or
group_or_chain_id <> approver.group_or_chain_id or
occurrence <> approver.occurrence);
update ame_temp_old_approver_lists
set approval_status = approver.approval_status
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
name = approver.name and
item_class = approver.item_class and
item_id = approver.item_id;
update ame_temp_old_approver_lists
set approval_status = approver.approval_status
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
name = approver.name and
item_class = approver.item_class and
item_id = approver.item_id and
action_type_id = approver.action_type_id and
group_or_chain_id = approver.group_or_chain_id and
occurrence = approver.occurrence;
setInsertedApprovalStatus(currentApproverIndexIn => approverInIndex
,approvalStatusIn => approver.approval_status);
update ame_temp_old_approver_lists
set approval_status = decode(approver.approval_status
,ame_util.approvedStatus
,ame_util.approvedByRepeatedStatus
,ame_util.approveAndForwardStatus
,ame_util.approvedByRepeatedStatus
,ame_util.rejectStatus
,ame_util.rejectedByRepeatedStatus
,ame_util.forwardStatus
,ame_util2.forwardByRepeatedStatus
,ame_util2.reassignStatus
,ame_util.nullStatus
,ame_util.noResponseStatus
,ame_util2.noResponseByRepeatedStatus
,ame_util.repeatedStatus)
where application_id = ameApplicationId
and transaction_id = transactionIdIn
and name = approver.name
and item_class = currentApprovers(repeatedAppIndexes(z)).item_class
and item_id = currentApprovers(repeatedAppIndexes(z)).item_id
and action_type_id = currentApprovers(repeatedAppIndexes(z)).action_type_id
and group_or_chain_id = currentApprovers(repeatedAppIndexes(z)).group_or_chain_id
and occurrence = currentApprovers(repeatedAppIndexes(z)).occurrence
and approval_status in ( ame_util.notifiedByRepeatedStatus
,ame_util.repeatedStatus);
select decode(approver.approval_status
,ame_util.approvedStatus
,ame_util.approvedByRepeatedStatus
,ame_util.approveAndForwardStatus
,ame_util.approvedByRepeatedStatus
,ame_util.rejectStatus
,ame_util.rejectedByRepeatedStatus
,ame_util.forwardStatus
,ame_util2.forwardByRepeatedStatus
,ame_util2.reassignStatus
,ame_util.nullStatus
,ame_util.noResponseStatus
,ame_util2.noResponseByRepeatedStatus
,ame_util.repeatedStatus) into tempStatus from dual;
setInsertedApprovalStatus(currentApproverIndexIn => repeatedAppIndexes(z)
,approvalStatusIn => tempStatus);
update ame_temp_old_approver_lists
set approval_status = ame_util.beatByFirstResponderStatus
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
(name <> approver.name or
occurrence <> approver.occurrence) and
item_class = approver.item_class and
item_id = approver.item_id and
action_type_id = approver.action_type_id and
group_or_chain_id = approver.group_or_chain_id and
approver_category = ame_util.approvalApproverCategory and
approval_status = ame_util.notifiedStatus;
setInsertedApprovalStatus(currentApproverIndexIn => i
,approvalStatusIn => ame_util.beatByFirstResponderStatus);
/* Insert rows in the notification approval history region for approvers
with beat by first responder */
insertIntoTransApprovalHistory
(transactionIdIn => transactionIdIn
,applicationIdIn => ameApplicationId
,orderNumberIn => approver.approver_order_number
,nameIn => approver.name
,appCategoryIn => null
,itemClassIn => approver.item_class
,itemIdIn => approver.item_id
,actionTypeIdIn => approver.action_type_id
,authorityIn => null
,statusIn => null
,grpOrChainIdIn => approver.group_or_chain_id
,occurrenceIn => approver.occurrence
,apiInsertionIn => approver.api_insertion
,memberOrderNumberIn => approver.member_order_number
,notificationIdIn => notificationIn.notification_id
,userCommentsIn => null
,dateClearedIn => null
,historyTypeIn => 'BEATBYFIRSTRESPONDER');
update ame_temp_old_approver_lists
set approval_status = ame_util.nullStatus
where application_id = ameApplicationId
and transaction_id = transactionIdIn
and name = currentApprovers(x).name
and item_class = currentApprovers(repeatedAppIndexes(z)).item_class
and item_id = currentApprovers(repeatedAppIndexes(z)).item_id
and action_type_id = currentApprovers(repeatedAppIndexes(z)).action_type_id
and group_or_chain_id = currentApprovers(repeatedAppIndexes(z)).group_or_chain_id
and occurrence = currentApprovers(repeatedAppIndexes(z)).occurrence
and approval_status = ame_util.notifiedByRepeatedStatus;
setInsertedApprovalStatus(currentApproverIndexIn => repeatedAppIndexes(z)
,approvalStatusIn => ame_util.nullStatus);
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || approver.display_name,
forwardee.name,
forwardee.item_class,
forwardee.item_id,
forwardee.approver_category,
decode(forwarderType,
ame_util.chainOfAuthorityForwarder, ame_util.apiAuthorityInsertion,
ame_util.apiInsertion),
approver.authority,
sysdate,
ame_approver_deviation_pkg.reassignStatus );
approver.api_insertion = ame_util.apiInsertion) then
forwarderType := ame_util.adHocForwarder;
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || approver.display_name,
forwardee.name,
forwardee.item_class,
forwardee.item_id,
forwardee.approver_category,
decode(forwarderType,
ame_util.chainOfAuthorityForwarder, ame_util.apiAuthorityInsertion,
ame_util.apiInsertion),
approver.authority,
sysdate
,ame_approver_deviation_pkg.forwardReason );
All of the insertees generated within this if statement should have the same
action_type_id and group_or_chain_id as approver. The insertees' source
should always be ame_util.specialForwardInsertion. The insertions' order
types should always be ame_util.afterApprover.
*/
forwardingBehavior := ame_engine.getForwardingBehavior(forwarderTypeIn => forwarderType,
forwardeeTypeIn => forwardeeType,
approvalStatusIn => approver.approval_status);
Locate the start of the chain, so we can calculate each insertee's occurrence
value, for use in the following insertee's insertion parameter.
*/
for i in reverse 1 .. (approverInIndex - 1) loop
if(currentApprovers(i).group_or_chain_id <> approver.group_or_chain_id or
currentApprovers(i).action_type_id <> approver.action_type_id or
currentApprovers(i).item_id <> approver.item_id or
currentApprovers(i).item_class <> approver.item_class) then
firstIndexInChain := i + 1;
/* Insert forwardee as ad-hoc or COA, according to whether approverIn is ad-hoc or COA. */
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
special_forwardee,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || approver.display_name,
forwardee.name,
forwardee.item_class,
forwardee.item_id,
ame_util.approvalApproverCategory, /* Forwarding is not possible from an FYI approver. */
decode(forwarderType,
ame_util.chainOfAuthorityForwarder, ame_util.apiAuthorityInsertion,
ame_util.apiInsertion),
approver.authority,
ame_util.booleanTrue,
sysdate,
ame_approver_deviation_pkg.forwardReason);
if(forwardingBehavior = ame_util.forwarderAndForwardee) then /* Insert the forwarder. */
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
special_forwardee,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || forwardee.display_name,
approver.name,
approver.item_class,
approver.item_id,
ame_util.approvalApproverCategory, /* Forwarding is not possible from an FYI approver. */
decode(forwarderType,
ame_util.chainOfAuthorityForwarder, ame_util.apiAuthorityInsertion,
ame_util.apiInsertion),
approver.authority,
ame_util.booleanTrue,
sysdate,
ame_approver_deviation_pkg.forwardForwardeeReason);
case we insert starting at the insertee, and ascending the hierarchy up to but not
including approver. In the case of ame_util.repeatForwarder, we then add
approverIn and stop. In the case of ame_util.skipForwarder, we then add
approverIn's superior and stop. The insertees are all COA approvers.
*/
/* Insert the forwardee. */
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
special_forwardee,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || approver.display_name,
forwardee.name,
forwardee.item_class,
forwardee.item_id,
ame_util.approvalApproverCategory, /* Forwarding is not possible from an FYI approver. */
ame_util.apiAuthorityInsertion,
ame_util.authorityApprover,
ame_util.booleanTrue,
sysdate,
ame_approver_deviation_pkg.forwardReason);
insertedApprover.name := forwardee.name;
insertedApprover.orig_system := forwardee.orig_system;
insertedApprover.orig_system_id := forwardee.orig_system_id;
insertedApprover.item_class := forwardee.item_class;
insertedApprover.item_id := forwardee.item_id;
insertedApprover.action_type_id := forwardee.action_type_id;
insertedApprover.group_or_chain_id :=forwardee.group_or_chain_id;
insertedApprover.occurrence := forwardee.occurrence;
/* Get the next superior to insert. */
tempReason := ame_approver_deviation_pkg.forwardEngInsReason;
ame_approver_type_pkg.getSuperior(approverIn => insertedApprover,
superiorOut => superiorApprover);
/* Calculate insertedApprover.occurrence. */
insertedApprover.occurrence := 1;
if(currentApprovers(i).name = insertedApprover.name) then
insertedApprover.occurrence := currentApprovers(i).occurrence + 1;
/* Prepare and do the insertion. */
tempInsertionOrder := ame_engine.getNextInsertionOrder;
tempParameter := insertedApprover.name ||
ame_util.fieldDelimiter ||
insertedApprover.item_class ||
ame_util.fieldDelimiter ||
insertedApprover.item_id ||
ame_util.fieldDelimiter ||
insertedApprover.action_type_id ||
ame_util.fieldDelimiter ||
insertedApprover.group_or_chain_id ||
ame_util.fieldDelimiter ||
insertedApprover.occurrence;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
special_forwardee,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || insertedApprover.display_name,
superiorApprover.name,
approver.item_class,
approver.item_id,
ame_util.approvalApproverCategory, /* Forwarding is not possible from an FYI approver. */
ame_util.apiAuthorityInsertion,
ame_util.authorityApprover,
ame_util.booleanTrue,
sysdate,
tempReason);
insertedApprover.name := superiorApprover.name;
insertedApprover.orig_system_id := superiorApprover.orig_system_id;
such cases, we insert starting with forwardeeIn and continuing up to and including
approver. The insertees are always ad-hoc, and pertain to the same item, sublist,
action type, and group or chain as the forwarder.
*/
for i in forwardeeIndex .. approverInIndex loop
if(i = forwardeeIndex) then
prevApproverIndex := approverInIndex;
tempInsertionOrder := ame_engine.getNextInsertionOrder;
insert into ame_temp_insertions(
transaction_id,
application_id,
insertion_order,
order_type,
parameter,
description,
name,
item_class,
item_id,
approver_category,
api_insertion,
authority,
special_forwardee,
effective_date,
reason) values(
transactionIdIn,
ameApplicationId,
tempInsertionOrder,
ame_util.afterApprover,
tempParameter,
ame_util.afterApproverDescription || insertedApprover.display_name,
currentApprovers(i).name,
approver.item_class,
approver.item_id,
ame_util.approvalApproverCategory, /* Forwarding is not possible from an FYI approver. */
ame_util.apiInsertion,
approver.authority,
ame_util.booleanTrue,
sysdate,
ame_approver_deviation_pkg.forwardRemandReason);
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => true,
updateOldApproverListIn => true,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
when apiInsertionException then
ame_engine.unlockTransaction(fndApplicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
transactionTypeIdIn => transactionTypeIn);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus;
procedure updateInsertions(indexIn in integer) as
tempIndex integer;
approverInsertionOrder integer;
deletionInsertionOrder integer;
if engStInsertionIndexes.exists(indexIn) then
deletionInsertionOrder := engStInsertionIndexes(indexIn);
tempIndex := engStInsertionIndexes.next(indexIn);
while engStInsertionIndexes.exists(tempIndex) loop
approverInsertionOrder := engStInsertionIndexes(tempIndex);
if approverInsertionOrder > deletionInsertionOrder then
update ame_temp_insertions
set parameter = parameter - 1
where insertion_order = approverInsertionOrder
and application_id = engAmeApplicationId
and transaction_id = engTransactionId
and order_type = ame_util.absoluteOrder;
tempIndex := engStInsertionIndexes.next(tempIndex);
end updateInsertions;