[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name
from ame_item_classes
where item_class_id = itemClassIdIn
and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
update ame_temp_old_approver_lists
set approval_status = null
where
application_id = ameAppId and
transaction_id = transactionIdIn and
item_class = item_class_name and
(itemIdIn is null or
item_id = itemIdIn);
/* 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
item_class = item_class_name and
(itemIdIn is null or
item_id = itemIdIn) and
date_cleared is null;
routineNameIn => 'getAvailableInsertions',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
select item_class_id
from ame_item_classes
where name = itemClassNameIn
and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
update ame_temp_old_approver_lists
set approval_status = null
where
application_id = ameAppId and
transaction_id = transactionIdIn and
item_class = itemClassNameIn and
(itemIdIn is null or
item_id = itemIdIn);
/* 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
item_class = itemClassNameIn and
(itemIdIn is null or
item_id = itemIdIn) and
date_cleared is null;
routineNameIn => 'getAvailableInsertions',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
select name
into groupNameOut
from ame_approval_groups
where approval_group_id = groupIdIn
and sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate);
procedure getAllApproversAndInsertions
(applicationIdIn in number
,transactionTypeIn in varchar2
,transactionIdIn in varchar2
,activeApproversYNIn in varchar2 default ame_util.booleanFalse
,coaInsertionsYNIn in varchar2 default ame_util.booleanFalse
,approvalProcessCompleteYNOut out nocopy varchar2
,approversOut out nocopy ame_util.approversTable2
,availableInsertionsOut out nocopy ame_util2.insertionsTable3
) as
allApprovers ame_util.approversTable2;
positionInsertions ame_util.insertionsTable2;
nextAvailableInsertionIndex integer;
(insertionRec2In in ame_util.insertionRecord2
,insertionRec3Out out nocopy ame_util2.insertionRecord3
) as
begin
insertionRec3Out.item_class := insertionRec2In.item_class;
insertionRec3Out.item_id := insertionRec2In.item_id;
insertionRec3Out.action_type_id := insertionRec2In.action_type_id;
insertionRec3Out.group_or_chain_id := insertionRec2In.group_or_chain_id;
insertionRec3Out.order_type := insertionRec2In.order_type;
insertionRec3Out.parameter := insertionRec2In.parameter;
insertionRec3Out.api_insertion := insertionRec2In.api_insertion;
insertionRec3Out.authority := insertionRec2In.authority;
insertionRec3Out.description := insertionRec2In.description;
select count(*)
into tempCount
from ame_calling_apps
where sysdate between start_date and nvl(end_date - (1/86400),sysdate)
and fnd_application_id = applicationIdIn
and transaction_type_id = 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 => false
,prepareRuleIdsIn => false
,prepareRuleDescsIn => false
,prepareApproverTreeIn => true
,transactionIdIn => transactionIdIn
,ameApplicationIdIn => null
,fndApplicationIdIn => applicationIdIn
,transactionTypeIdIn => transactionTypeIn
);
nextAvailableInsertionIndex := 0;
positionInsertions.delete;
/* Get available insertions at position i from the engine */
ame_engine.getInsertions
(positionIn => i
,orderTypeIn => null
,coaInsertionsYNIn => coaInsertionsYNIn
,availableInsertionsOut => positionInsertions
);
/* Copy insertion records only if insertions are available at this position */
if positionInsertions.count > 0 then
for j in 1 .. positionInsertions.count loop
nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
(insertionRec2In => positionInsertions(j)
,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
);
availableInsertionsOut(nextAvailableInsertionIndex).position := i;
positionInsertions.delete;
/* Get available insertions at position i from the engine */
ame_engine.getInsertions
(positionIn => i
,orderTypeIn => null
,coaInsertionsYNIn => coaInsertionsYNIn
,availableInsertionsOut => positionInsertions
);
/* Copy insertion records only if insertions are available at this position */
if positionInsertions.count > 0 then
for j in 1 .. positionInsertions.count loop
nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
(insertionRec2In => positionInsertions(j)
,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
);
availableInsertionsOut(nextAvailableInsertionIndex).position := i;
positionInsertions.delete;
/* Get available insertions at position i from the engine */
ame_engine.getInsertions
(positionIn => allApprovers.count + 1
,orderTypeIn => null
,coaInsertionsYNIn => coaInsertionsYNIn
,availableInsertionsOut => positionInsertions
);
/* Copy insertion records only if insertions are available at this position */
if positionInsertions.count > 0 then
for j in 1 .. positionInsertions.count loop
nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
(insertionRec2In => positionInsertions(j)
,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
);
availableInsertionsOut(nextAvailableInsertionIndex).position := allApprovers.count + 1;
positionInsertions.delete;
/* Get available insertions at position lastActiveApproverIndex + 1 from the engine */
ame_engine.getInsertions
(positionIn => lastActiveApproverIndex + 1
,orderTypeIn => null
,coaInsertionsYNIn => coaInsertionsYNIn
,availableInsertionsOut => positionInsertions
);
/* Copy insertion records only if insertions are available at this position */
if positionInsertions.count > 0 then
for j in 1 .. positionInsertions.count loop
if positionInsertions(j).order_type <> ame_util.beforeApprover then
nextAvailableInsertionIndex := nextAvailableInsertionIndex + 1;
(insertionRec2In => positionInsertions(j)
,insertionRec3Out => availableInsertionsOut(nextAvailableInsertionIndex)
);
availableInsertionsOut(nextAvailableInsertionIndex).position := lastActiveApproverIndex + 1;
,routineNameIn => 'getAllApproversAndInsertions'
,exceptionNumberIn => sqlcode
,exceptionStringIn => sqlerrm
);
approversOut.delete;
availableInsertionsOut.delete;
end getAllApproversAndInsertions;