[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from ame_item_class_usages
where application_id = applicationIdIn
and item_class_id = itemClassIdIn
and item_class_sublist_mode <> ame_util.serialSublists
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select 'Y'
from (select (count(item_class_order_number) - count(distinct item_class_order_number)) uniq
from ame_item_class_usages
where application_id = applicationIdIn
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
)
where uniq <> 0;
select 'Y'
from ame_action_type_config
where application_id = applicationIdIn
and (chain_ordering_mode <> ame_util.serialChainsMode
or ( voting_regime is not null and
voting_regime <> ame_util.serializedVoting
)
)
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select 'Y'
from (select distinct (count(order_number) - count(distinct order_number)) uniq
from ame_action_type_config acf,
ame_action_type_usages axu
where acf.application_id = applicationIdIn
and acf.action_type_id = axu.action_type_id
and sysdate between acf.start_date and
nvl(acf.end_Date - ame_util.oneSecond, sysdate)
and sysdate between axu.start_date and
nvl(axu.end_Date - ame_util.oneSecond, sysdate)
group by rule_type
)
where uniq <> 0;
select 'Y'
from ame_approval_group_config
where application_id = applicationIdIn
and (voting_regime = ame_util.consensusVoting
or voting_regime = ame_util.firstApproverVoting)
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select 'Y'
from (select (count(order_number) - count(distinct order_number)) uniq
from ame_approval_group_config
where application_id = applicationIdIn
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
)
where uniq <> 0;
select gpi.approval_group_id group_id
from ame_approval_groups apg,
ame_approval_group_config gcf,
ame_approval_group_items gpi
where apg.is_static = ame_util.booleanTrue
and gcf.application_id = applicationIdIn
and gcf.approval_group_id = apg.approval_group_id
and gcf.voting_regime = ame_util.orderNumberVoting
and gpi.approval_group_id = apg.approval_group_id
and sysdate between apg.start_date and
nvl(apg.end_date - ame_util.oneSecond, sysdate)
and sysdate between gcf.start_date and
nvl(gcf.end_date - ame_util.oneSecond, sysdate)
and sysdate between gpi.start_date and
nvl(gpi.end_date - ame_util.oneSecond, sysdate)
group by(gpi.approval_group_id)
having (count(gpi.order_number) - count(distinct gpi.order_number)) <> 0;
select 'Y'
from ame_actions act,
ame_action_usages acu,
ame_rule_usages aru
where acu.rule_id = aru.rule_id
and act.action_id = acu.action_id
and act.parameter = groupIdIn
and aru.item_id = ameApplicationIdIn
and sysdate between act.start_date and nvl(act.end_Date - (1/86400), sysdate)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400))))
and ((sysdate between aru.start_date and nvl(aru.end_date - (1/86400),sysdate))
or
(sysdate < aru.start_date and aru.start_date < nvl(aru.end_date, aru.start_date + (1/86400))));
procedure clearInsertion(approverIn in ame_util.approverRecord,
applicationIdIn in integer,
transactionIdIn in varchar2,
transactionTypeIn in varchar2 default null) as
approver ame_util.approverRecord2;
ame_api3.clearInsertion(approverIn => approver,
applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
transactionTypeIn => transactionTypeIn);
routineNameIn => 'clearInsertion',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end clearInsertion;
procedure clearInsertions(applicationIdIn in integer,
transactionIdIn in varchar2,
transactionTypeIn in varchar2 default null) as
begin
check11510(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn);
ame_api3.clearInsertions( applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
transactionTypeIn => transactionTypeIn);
routineNameIn => 'clearInsertions',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end clearInsertions;
procedure deleteApprover(applicationIdIn in integer,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord,
transactionTypeIn in varchar2 default null) as
approver ame_util.approverRecord2;
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,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
routineNameIn => 'deleteApprover',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'deleteApprover',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end deleteApprover;
procedure deleteApprovers(applicationIdIn in integer,
transactionIdIn in varchar2,
approversIn in ame_util.approversTable,
transactionTypeIn in varchar2 default null) as
currentApproverInIndex integer;
ame_api.deleteApprover(applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
approverIn => approversIn(currentApproverInIndex),
transactionTypeIn => transactionTypeIn);
routineNameIn => 'deleteApprovers',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end deleteApprovers;
approversOut.delete;
approversOut.delete;
approversOut.delete;
ruleIdsOut.delete;
approversOut.delete;
ruleDescriptionsOut.delete;
approversOut.delete;
ruleIdsOut.delete;
ruleDescriptionsOut.delete;
procedure getAvailableInsertions(applicationIdIn in integer,
transactionIdIn in varchar2,
positionIn in integer,
transactionTypeIn in varchar2 default null,
availableInsertionsOut out nocopy ame_util.insertionsTable) as
i integer;
availableInsertions ame_util.insertionsTable2;
ame_api3.getAvailableInsertions(applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
positionIn => positionIn,
transactionTypeIn => transactionTypeIn,
availableInsertionsOut => availableInsertions);
ame_util.insTable2ToInsTable(insertionsTable2In => availableInsertions,
insertionsTableOut => availableInsertionsOut);
routineNameIn => 'getAvailableInsertions',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end getAvailableInsertions;
tempInsertionDoesNotExist boolean;
tempParameter ame_temp_insertions.parameter%type;
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,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
if(not ame_engine.insertionExists(orderTypeIn => ame_util.absoluteOrder,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
tempInsertionDoesNotExist := not ame_engine.insertionExists(orderTypeIn => ame_util.afterApprover,
parameterIn => tempParameter);
if(tempInsertionDoesNotExist) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.beforeApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
approvers(positionIn).api_insertion <> ame_util.apiInsertion ) then
tempBoolean := true; /* tempBoolean remains true if no previous authority is found. */
approvers(i).api_insertion <> ame_util.apiInsertion) then
tempBoolean := false;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstAuthority,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
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 approvers 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 approvers' 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
*/
tempParameter := ame_util.firstPostParameter ;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
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 < approversCount + 1) then
if(the approvers 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
*/
tempParameter := ame_util.firstPreApprover ;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.firstPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
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 = approversCount + 1) then
allow last-post-approver insertion for the last approver's item
elsif(positionIn > 1) then
if(the approvers 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
*/
tempParameter := ame_util.lastPostApprover ;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPostApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
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 <= approversCount) then
if(the approvers 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 approvers' 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
*/
tempParameter := ame_util.lastPreApprover ;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
if(not ame_engine.insertionExists(orderTypeIn => ame_util.lastPreApprover,
parameterIn => tempParameter)) then
availableOrdersIndex := availableOrdersIndex + 1;
messageNameIn => 'AME_400418_INVALID_INSERTION');
update ame_temp_old_approver_lists
set approval_status = ame_util.notifiedStatus
where item_class = nextApprovers(i).item_class and
item_id = nextApprovers(i).item_id and
name = nextApprovers(i).name and
action_type_id = nextApprovers(i).action_type_id and
group_or_chain_id = nextApprovers(i).group_or_chain_id and
occurrence = nextApprovers(i).occurrence and
transaction_id = transactionIdIn and
application_id = ameApplicationId;
/* delete after talking to TM and SS - nsoni
ame_api2.getNextApprovers4(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn,
transactionIdIn => transactionIdIn,
flagApproversAsNotifiedIn => ame_util.booleanFalse,
approvalProcessCompleteYNOut => approvalProcessCompleteYN,
nextApproversOut => nextApprovers);
oldApproversOut.delete;
conditionIdsOut.delete;
conditionDescriptionsOut.delete;
conditionIdsOut.delete;
conditionDescriptionsOut.delete;
procedure insertApprover(applicationIdIn in integer,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord,
positionIn in integer,
orderIn in ame_util.orderRecord,
transactionTypeIn in varchar2 default null) as
approver ame_util.approverRecord2;
insertion ame_util.insertionRecord2;
insertionRecord2Out => insertion );
approver.action_type_id := insertion.action_type_id;
approver.group_or_chain_id := insertion.group_or_chain_id ;
ame_api3.insertApprover(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn,
transactionIdIn => transactionIdIn,
approverIn => approver,
positionIn => positionIn,
insertionIn =>insertion );
routineNameIn => 'insertApprover',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertApprover;
chainToBeInserted boolean;
/* call ame_engine.UpdateTransactionState */
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,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
chainToBeInserted := false;
select count(*)
into tempCount
from ame_approver_type_usages
where action_type_id = currentActionTypeId
and approver_type_id not in (select approver_type_id
from ame_approver_types
where orig_system = ame_util.perOrigSystem) ;
chainToBeInserted := true;
chainToBeInserted := false;
chainToBeInserted := true;
chainToBeInserted := false;
if chainToBeInserted then
/* convert approverIn to ame_util.approverRecord2, set action_type_id
and group_or_chain_id. Call ame_api2.setFirstAuthorityApprover*/
ame_util.apprRecordToApprRecord2(approverRecordIn => approverIn,
itemIdIn => transactionIdIn,
approverRecord2Out =>approver);
procedure updateApprovalStatus(applicationIdIn in integer,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord,
transactionTypeIn in varchar2 default null,
forwardeeIn in ame_util.approverRecord default ame_util.emptyApproverRecord) as
approver ame_util.approverRecord2;
ame_api2.updateApprovalStatus(applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
approverIn => approver,
transactionTypeIn => transactionTypeIn,
forwardeeIn => forwardee);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus;
procedure updateApprovalStatus2(applicationIdIn in integer,
transactionIdIn in varchar2,
approvalStatusIn in varchar2,
approverPersonIdIn in integer default null,
approverUserIdIn in integer default null,
transactionTypeIn in varchar2 default null,
forwardeeIn in ame_util.approverRecord default ame_util.emptyApproverRecord,
approvalTypeIdIn in integer default null,
groupOrChainIdIn in integer default null,
occurrenceIn in integer default null) as
forwardee ame_util.approverRecord2;
ame_api2.updateApprovalStatus2(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn,
transactionIdIn => transactionIdIn,
approvalStatusIn => approvalStatusIn,
approverNameIn=> approverName,
itemClassIn => ame_util.headerItemClassName,
itemIdIn => transactionIdIn,
actionTypeIdIn => approvalTypeIdIn,
groupOrChainIdIn => groupOrChainIdIn,
occurrenceIn => occurrenceIn,
forwardeeIn => forwardee);
routineNameIn => 'updateApprovalStatus2',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus2;