[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
( insertReason
,suppressReason
,forwardReason
,timeoutReason
,firstauthReason
,firstauthHandlerInsReason
,forwarHandlerAuthInsReason
,reassignStatus
,forwardForwardeeReason
,forwardEngInsReason
,forwardRemandReason) then
return true;
select trans_deviation_flag /*allowed values are Y and N*/
from ame_temp_transactions
where application_id = applId
and transaction_id = transIdIn;
update ame_temp_transactions
set trans_deviation_flag = 'Y'
,end_date = sysdate
,transaction_requestor = transactionRequesterIn
,transaction_description = transactionDescriptionIn
where application_id = applicationIdIn
and transaction_id = tranasactionId;
update ame_temp_transactions
set trans_deviation_flag = 'Y'
,end_date = sysdate
,transaction_requestor = transactionRequesterIn
,transaction_description = transactionDescriptionIn
where application_id = applicationIdIn
and transaction_id = tranasactionId;
select is_static
,query_string
from ame_attribute_usages atu
,ame_attributes attr
where attr.name = 'DESCRIPTION_OF_TRANSACTION'
and attr.attribute_id = atu.attribute_id
and application_id = applnIdIn
and sysdate between attr.start_date and nvl(attr.end_date,sysdate)
and sysdate between atu.start_date and nvl(atu.end_date,sysdate);
/*This method insert the approverlist into the table. This method will be called only
when approvalprocessis completed. This method does the following
1. mark the transaction as deviated if not already done and mark the transaction as
completed.
2. insert the record if the transaction is not already completed. This to prevent
the insertion of the same record in repeatdely. To implement this we need the following
check in other place
a) clear the record from detailed table and trans_deviation_flag when approver status is cleared
b) approver list changes even after the approval process is complete and now the transaction has
new set of approver. In this case AME engine is responsible to clear the record from detailed table
and the trans_deviation_flag from master table*/
procedure insertDeviations( applicationIdIn in number
,tranasactionIdIn in varchar2
,deviationListIn in deviationReasonList
,finalapproverListIn in ame_util.approversTable2) as
tempTransSequenceId integer;
select temp_transactions_id
from ame_temp_transactions
where application_id = applnIdIn
and transaction_id = txnIdIn
and end_date is not null;
select temp_transactions_id
from ame_temp_transactions
where application_id = applnIdIn
and transaction_id = txnIdIn;
update ame_temp_transactions
set end_date = sysdate
,trans_deviation_flag = 'N'
,transaction_requestor = tempDisplayName
,transaction_description = tempTransDescr
where application_id = applicationIdIn
and transaction_id = tranasactionIdIn;
select ame_txn_approvers_s.nextval
into approverDeviationId from dual;
insert into ame_txn_approvers
(
txn_approvers_id
,temp_transactions_id
,name
,orig_system
,orig_system_id
,display_name
,approver_category
,api_insertion
,authority
,approval_status
,action_type_id
,group_or_chain_id
,occurrence
,source
,item_class
,item_id
,item_class_order_number
,item_order_number
,sub_list_order_number
,action_type_order_number
,group_or_chain_order_number
,member_order_number
,approver_order_number
,effective_date
,reason
,txn_attribute_1
,txn_attribute_2
,txn_attribute_3
,txn_attribute_4
,txn_attribute_5
,txn_attribute_6
,txn_attribute_7
,txn_attribute_8
,txn_attribute_9
,txn_attribute_10
)values
(
approverDeviationId
,tempTransSequenceId
,finalapproverListIn(approverIndex).name
,finalapproverListIn(approverIndex).orig_system
,finalapproverListIn(approverIndex).orig_system_id
,finalapproverListIn(approverIndex).display_name
,finalapproverListIn(approverIndex).approver_category
,finalapproverListIn(approverIndex).api_insertion
,finalapproverListIn(approverIndex).authority
,finalapproverListIn(approverIndex).approval_status
,finalapproverListIn(approverIndex).action_type_id
,finalapproverListIn(approverIndex).group_or_chain_id
,finalapproverListIn(approverIndex).occurrence
,finalapproverListIn(approverIndex).source
,finalapproverListIn(approverIndex).item_class
,finalapproverListIn(approverIndex).item_id
,finalapproverListIn(approverIndex).item_class_order_number
,finalapproverListIn(approverIndex).item_order_number
,finalapproverListIn(approverIndex).sub_list_order_number
,finalapproverListIn(approverIndex).action_type_order_number
,finalapproverListIn(approverIndex).group_or_chain_order_number
,finalapproverListIn(approverIndex).member_order_number
,finalapproverListIn(approverIndex).approver_order_number
,tempDate
,tempReason
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
);
routineNameIn => 'insertDeviations',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end insertDeviations;
if reasonIn = insertReason then
templookupcode := 'INSERT';
select meaning
into descrOut
from fnd_lookups
where lookup_type = 'AME_DEVIATION_REASON'
and lookup_code = templookupcode;
procedure updateDeviationState( applicationIdIn in number
,tranasactionIdIn in varchar2
,deviationListIn in deviationReasonList
,approvalProcessCompleteYNIn in varchar2
,finalapproverListIn in ame_util.approversTable2) as
tempTransactionKey integer;
select temp_transactions_id
from ame_temp_transactions
where application_id = applnId
and transaction_id = transIdIn
and end_date is not null;
routineNameIn => 'updateDeviationState',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
delete from ame_txn_approvers
where temp_transactions_id = tempTransactionKey;
update ame_temp_transactions
set end_Date = null
,trans_deviation_flag = null
,transaction_requestor = null
,transaction_description = null
where temp_transactions_id = tempTransactionKey;
routineNameIn => 'updateDeviationState',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
insertDeviations( applicationIdIn => applicationIdIn
,tranasactionIdIn => tranasactionIdIn
,deviationListIn => deviationListIn
,finalapproverListIn => finalapproverListIn);
end updateDeviationState;
select temp_transactions_id
from ame_temp_transactions
where application_id = applnId
and transaction_id = transIdIn
and end_date is not null;
delete from ame_txn_approvers
where temp_transactions_id = tempTransSeq;
update ame_temp_transactions
set end_date = null
,transaction_requestor = null
,transaction_description = null
,trans_deviation_flag = null
where temp_transactions_id = tempTransSeq;
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,
prepareApproverTreeIn => true,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => false,
prepareRuleDescsIn => false,
transactionIdIn => transactionIdIn,
ameApplicationIdIn => applicationIdIn,
fndApplicationIdIn => null,
transactionTypeIdIn => null );
select lookup_code
into TEMP_REASON
from fnd_lookup_values
where lookup_type ='AME_DEVIATION_REASON'
and language = userenv('LANG')
and meaning = P_REASON;
select application_name
into tempName
from fnd_application_vl
where application_id = to_number(P_APPLICATION);
select application_name
into tempapplName
from ame_calling_apps_vl
where application_id = P_AMEAPPLID
and sysdate between start_date and nvl(end_date,sysdate);