[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into tempCount
from wf_roles
where name = approverIn.name and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date ) ; /* Don't use tempEffectiveRuleDate here. */
delete from ame_temp_old_approver_lists
where
application_id = ameAppId and
transaction_id = transactionIdIn;
delete from ame_temp_insertions
where
application_id = ameAppId and
transaction_id = transactionIdIn;
delete from ame_temp_deletions
where
application_id = ameAppId and
transaction_id = transactionIdIn;
/* update all existing history rows from the Approval Notification History table
to indicate the rows were cleared */
update AME_TRANS_APPROVAL_HISTORY set
date_cleared = sysdate
where
application_id = ameAppId and
transaction_id = transactionIdIn
and date_cleared is null;
select application_id
into tempAmeApplicationId
from ame_calling_apps
where fnd_application_id = applicationIdIn and
transaction_type_id = transactionTypeIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select count(*)
into l_count
from ame_config_vars
where variable_name = ame_util.adminApproverConfigVar and
application_id = tempAmeApplicationId and
variable_value = l_admin_apr and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into l_admin_apr
from ame_config_vars
where variable_name = ame_util.adminApproverConfigVar and
(application_id is null or application_id = 0) and
sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate) ;
adminApproverOut.api_insertion := null;
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 => true
,prepareRuleIdsIn => false
,prepareRuleDescsIn => false
,prepareApproverTreeIn => true
,transactionIdIn => transactionIdIn
,ameApplicationIdIn => null
,fndApplicationIdIn => applicationIdIn
,transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => true,
processProductionRulesIn => false,
updateCurrentApproverListIn => true,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => true,
processProductionRulesIn => true,
updateCurrentApproverListIn => true,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => true,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => true,
prepareRuleDescsIn => true,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => true,
prepareRuleDescsIn => true,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 );
approversOut.delete;
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
prepareApproverTreeIn => true,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
approversOut.delete;
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
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 => true,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
itemClassNamesOut.delete;
itemIdsOut.delete;
approvalProcessesCompleteYNOut.delete;
nextApproversOut.delete;
itemIndexesOut.delete;
itemClassesOut.delete;
itemIdsOut.delete;
itemSourcesOut.delete;
nextApproversOut.delete;
itemIndexesOut.delete;
itemClassesOut.delete;
itemIdsOut.delete;
itemSourcesOut.delete;
productionIndexesOut.delete;
variableNamesOut.delete;
variableValuesOut.delete;
nextApproversOut.delete;
itemIndexesOut.delete;
itemClassesOut.delete;
itemIdsOut.delete;
itemSourcesOut.delete;
productionIndexesOut.delete;
variableNamesOut.delete;
variableValuesOut.delete;
transVariableNamesOut.delete;
transVariableValuesOut.delete;
nextApproversOut.delete;
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 );
approversOut.delete;
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => true,
updateCurrentApproverListIn => false,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
variableNamesOut.delete;
variableValuesOut.delete;
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => true,
fetchInsertionsIn => true,
fetchDeletionsIn => true,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => recordApproverListIn,
updateOldApproverListIn => recordApproverListIn,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => null,
fndApplicationIdIn => applicationIdIn,
transactionTypeIdIn => transactionTypeIn );
tempParameter ame_temp_insertions.parameter%type;
select apr.orig_system
from ame_approver_type_usages apu
,ame_approver_types apr
where apu.action_type_id=actionTypeIdIn
and apr.approver_type_id = apu.approver_type_id
and sysdate between apu.start_date
and nvl(apu.end_date - ame_util.oneSecond,sysdate)
and sysdate between apr.start_date
and nvl(apr.end_date - ame_util.oneSecond,sysdate);
select distinct count(*) into actionTypeCount
from ame_action_type_usages
where action_type_id = approverIn.action_type_id
and rule_type = ame_util.authorityRuleType
and sysdate between start_date
and nvl(end_date - ame_util.oneSecond,sysdate);
select user_action_type_name
from ame_action_types_vl
where action_type_id = actionTypeIdIn
and sysdate between start_date
and nvl(end_date - ame_util.oneSecond,sysdate);
approverIn.api_insertion <> ame_util.apiAuthorityInsertion or
approverIn.authority <> ame_util.authorityApprover or
approverIn.approval_status is not null) then
raise invalidApproverException;
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 );
/* Insertions are possible for a transaction with an empty approver list and also
an approver list which has only suppressed approvers
*/
ame_engine.getApprovers(approversOut => approvers);
select count(*)
into tempCount
from ame_temp_old_approver_lists
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
item_class = approverIn.item_class and
item_id = approverIn.item_id and
action_type_id = approverIn.action_type_id and
group_or_chain_id = approverIn.group_or_chain_id and
authority = ame_util.authorityApprover and
api_insertion in (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) and
approval_status not in (ame_util.nullStatus,ame_util.notifiedStatus);
update ame_temp_old_approver_lists set
approval_status = ame_util.nullStatus
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
item_class = approverIn.item_class and
item_id = approverIn.item_id and
action_type_id = approverIn.action_type_id and
group_or_chain_id = approverIn.group_or_chain_id and
authority = ame_util.authorityApprover and
api_insertion in (ame_util.oamGenerated, ame_util.apiAuthorityInsertion) and
approval_status not in (ame_util.nullStatus,ame_util.notifiedStatus);
/* update all existing history rows from the Approval Notification History table
to indicate the rows were cleared */
update AME_TRANS_APPROVAL_HISTORY set
date_cleared = sysdate
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
item_class = approverIn.item_class and
item_id = approverIn.item_id and
action_type_id = approverIn.action_type_id and
group_or_chain_id = approverIn.group_or_chain_id and
authority = ame_util.authorityApprover and
status not in (ame_util.nullStatus,ame_util.notifiedStatus) and
date_cleared is null;
/* If there is already a firstAuthority in the insertions table for the transaction, item class, item_id ,
delete it. */
if(ame_engine.insertionExists(orderTypeIn => ame_util.firstAuthority,
parameterIn => tempParameter)) then
delete from ame_temp_insertions
where
application_id = ameApplicationId and
transaction_id = transactionIdIn and
item_class = approverIn.item_class and
item_id = approverIn.item_id and
order_type = ame_util.firstAuthority;
tempCount := 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,
tempCount,
ame_util.firstAuthority,
tempParameter,
ame_util.firstAuthorityDescription,
approverIn.name,
approverIn.item_class,
approverIn.item_id,
approverIn.approver_category,
ame_util.apiAuthorityInsertion,
ame_util.authorityApprover,
sysdate,
ame_approver_deviation_pkg.firstauthReason
);
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 );
procedure updateApprovalStatus(applicationIdIn in number,
transactionTypeIn in varchar2,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord2,
forwardeeIn in ame_util.approverRecord2 default
ame_util.emptyApproverRecord2,
updateItemIn in boolean default false) as
errorCode integer;
ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn,
transactionIdIn => transactionIdIn,
approverIn => approverIn,
forwardeeIn => forwardeeIn,
updateItemIn => updateItemIn);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus;
procedure updateApprovalStatuses(applicationIdIn in number,
transactionTypeIn in varchar2,
transactionIdIn in varchar2,
approverIn in ame_util.approverRecord2,
approvalStatusesIn in ame_util.stringList default ame_util.emptyStringList,
itemClassesIn in ame_util.stringList default ame_util.emptyStringList,
itemIdsIn in ame_util.stringList default ame_util.emptyStringList,
forwardeesIn in ame_util.approversTable2 default ame_util.emptyApproversTable2) as
begin
null;
end updateApprovalStatuses;
procedure updateApprovalStatus2(applicationIdIn in number,
transactionTypeIn in varchar2,
transactionIdIn in varchar2,
approvalStatusIn in varchar2,
approverNameIn in varchar2,
itemClassIn in varchar2 default null,
itemIdIn in varchar2 default null,
actionTypeIdIn in number default null,
groupOrChainIdIn in number default null,
occurrenceIn in number default null,
forwardeeIn in ame_util.approverRecord2 default ame_util.emptyApproverRecord2,
updateItemIn in boolean default false) as
approver ame_util.approverRecord2;
/* No locking needed here as it is done in updateApprovalStatus */
if approverNameIn is not null then
approver.name := approverNameIn;
ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
approverIn => approver,
transactionTypeIn => transactionTypeIn,
forwardeeIn => forwardeeIn,
updateItemIn => updateItemIn);
routineNameIn => 'updateApprovalStatus2',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
tokenValueOneIn => 'updateApprovalStatus2',
tokenNameTwoIn => 'NAME',
tokenValueTwoIn => approver.name);
routineNameIn => 'updateApprovalStatus2',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus2;
procedure updateApprovalStatuses2(applicationIdIn in number,
transactionTypeIn in varchar2,
transactionIdIn in varchar2,
approvalStatusIn in varchar2,
approverNameIn in varchar2,
itemClassIn in varchar2 default null,
itemIdIn in varchar2 default null,
actionTypeIdIn in number default null,
groupOrChainIdIn in number default null,
occurrenceIn in number default null,
approvalStatusesIn in ame_util.stringList default ame_util.emptyStringList,
itemClassesIn in ame_util.stringList default ame_util.emptyStringList,
itemIdsIn in ame_util.stringList default ame_util.emptyStringList,
forwardeesIn in ame_util.approversTable2 default ame_util.emptyApproversTable2) is
begin
null;
end updateApprovalStatuses2;