[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
display_name,
orig_system,
orig_system_id
into
displayNameOut,
origSystemOut,
origSystemIdOut
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select
display_name,
orig_system,
orig_system_id
into
displayNameOut,
origSystemOut,
origSystemIdOut
from wf_local_roles
where
name = nameIn and
rownum < 2;
select atah.row_timestamp row_timestamp
,atah.item_class item_class
,atah.item_id item_id
,atah.name name
,atah.order_number order_number
,atah.approver_category category
,atah.user_comments user_comment
,atah.status status
,atah.authority
,atah.occurrence
,atah.action_type_id
,atah.group_or_chain_id
,atah.api_insertion
,atah.member_order_number
from ame_trans_approval_history atah
where atah.date_cleared is null
and atah.transaction_id = transactionIdIn
and atah.application_id = applicationIdIn
and atah.row_timestamp =
(
select max(b.row_timestamp)
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);
approversOut(tempIndex).api_insertion := approver.api_insertion;
approversOut.delete;
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
errorCode integer;
ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
transactionTypeIn => transactionTypeIn,
transactionIdIn => transactionIdIn,
approverIn => approverIn,
notificationIn => notificationIn,
forwardeeIn => forwardeeIn,
updateItemIn => updateItemIn);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateApprovalStatus',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus;
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,
notificationIn in ame_util2.notificationRecord
default ame_util2.emptyNotificationRecord,
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;
tokenValueOneIn => 'ame_api6.updateApprovalStatus2',
tokenNameTwoIn => 'NAME',
tokenValueTwoIn => approver.name);
ame_engine.updateApprovalStatus(applicationIdIn => applicationIdIn,
transactionIdIn => transactionIdIn,
approverIn => approver,
transactionTypeIn => transactionTypeIn,
notificationIn => notificationIn,
forwardeeIn => forwardeeIn,
updateItemIn => updateItemIn);
routineNameIn => 'updateApprovalStatus2',
exceptionNumberIn => errorCode,
exceptionStringIn => errorMessage);
routineNameIn => 'updateApprovalStatus2',
exceptionNumberIn => sqlcode,
exceptionStringIn => sqlerrm);
end updateApprovalStatus2;
SELECT ol.name
,ame_approver_type_pkg.getapproverdisplayname4 (ol.name) display_name
,ol.approval_status
,ol.approver_category
,ol.authority
,ol.action_type_id
,ol.group_or_chain_id
,ol.item_class
,ol.item_id
FROM ame_temp_old_approver_lists ol
,ame_trans_approval_history h
,fnd_lookups lookup
,fnd_lookups lookup2
,fnd_lookups lookup3
,ame_approval_groups_vl apg
WHERE ol.transaction_id = h.transaction_id
AND ol.application_id = h.application_id
AND ol.name = h.name
AND ol.item_class = h.item_class
AND ol.item_id = h.item_id
AND ol.api_insertion = h.api_insertion
AND ol.action_type_id = h.action_type_id
AND ol.authority = h.authority
AND ol.occurrence = h.occurrence
AND lookup.lookup_type (+) = 'AME_APPROVAL_STATUS'
AND lookup.lookup_code (+) = h.status
AND apg.approval_group_id (+) = ol.group_or_chain_id
AND (
h.status IS NULL
OR h.status = 'APPROVE AND FORWARD'
OR h.status = 'APPROVE'
OR h.status = 'BEAT BY FIRST RESPONDER'
OR h.status = 'FORWARD'
OR h.status = 'NO RESPONSE'
OR h.status = 'NOTIFIED'
OR h.status = 'REJECT'
)
AND (
ol.approval_status IS NULL
OR ol.approval_status = 'APPROVE AND FORWARD'
OR ol.approval_status = 'APPROVE'
OR ol.approval_status = 'BEAT BY FIRST RESPONDER'
OR ol.approval_status = 'FORWARD'
OR ol.approval_status = 'NO RESPONSE'
OR ol.approval_status = 'NOTIFIED'
OR ol.approval_status = 'REJECT'
)
AND h.date_cleared IS NULL
AND lookup2.lookup_type = 'AME_APPROVER_CATEGORY'
AND ol.approver_category = lookup2.lookup_code
AND h.transaction_id = transactionIdIn
AND h.application_id =
(
SELECT application_id
FROM ame_calling_apps
WHERE SYSDATE BETWEEN start_date
AND nvl (end_date
,SYSDATE)
AND fnd_application_id = applicationIdIn
AND transaction_type_id = transactionTypeIn
)
AND lookup3.lookup_type (+) = 'FND_WF_ORIG_SYSTEMS'
AND lookup3.lookup_code (+) = ame_approver_type_pkg.getapproverorigsystem3 (ol.name)
AND h.row_timestamp =
(
SELECT max (b.row_timestamp)
FROM ame_trans_approval_history b
WHERE h.transaction_id = b.transaction_id
AND h.application_id = b.application_id
AND h.name = b.name
AND h.approver_category = b.approver_category
AND h.item_class = b.item_class
AND h.item_id = b.item_id
AND h.action_type_id = b.action_type_id
AND h.authority = b.authority
AND h.group_or_chain_id = b.group_or_chain_id
AND h.occurrence = b.occurrence
AND b.date_cleared IS NULL
)
UNION
SELECT ol.name
,ame_approver_type_pkg.getapproverdisplayname4 (ol.name) display_name
,ol.approval_status
,ol.approver_category
,ol.authority
,ol.action_type_id
,ol.group_or_chain_id
,ol.item_class
,ol.item_id
FROM ame_temp_old_approver_lists ol
,fnd_lookups lookup
,fnd_lookups lookup2
,fnd_lookups lookup3
,ame_approval_groups_vl apg
WHERE NOT EXISTS
(
SELECT x.transaction_id
,x.application_id
FROM ame_trans_approval_history x
WHERE ol.transaction_id = x.transaction_id
AND ol.name = x.name
AND ol.application_id = x.application_id
AND ol.item_class = x.item_class
AND ol.item_id = x.item_id
AND ol.api_insertion = x.api_insertion
AND ol.action_type_id = x.action_type_id
AND ol.authority = x.authority
AND ol.occurrence = x.occurrence
)
AND ol.transaction_id = transactionIdIn
AND ol.application_id =
(
SELECT application_id
FROM ame_calling_apps
WHERE SYSDATE BETWEEN start_date
AND nvl (end_date
,SYSDATE)
AND fnd_application_id = applicationIdIn
AND transaction_type_id = transactionTypeIn
)
AND lookup.lookup_type (+) = 'AME_APPROVAL_STATUS'
AND lookup.lookup_code (+) = ol.approval_status
AND lookup2.lookup_type = 'AME_APPROVER_CATEGORY'
AND ol.approver_category = lookup2.lookup_code
AND apg.approval_group_id (+) = ol.group_or_chain_id
AND lookup3.lookup_type (+) = 'FND_WF_ORIG_SYSTEMS'
AND lookup3.lookup_code (+) = ame_approver_type_pkg.getapproverorigsystem3 (ol.name)
AND (
ol.approval_status IS NULL
OR ol.approval_status = 'APPROVE AND FORWARD'
OR ol.approval_status = 'APPROVE'
OR ol.approval_status = 'BEAT BY FIRST RESPONDER'
OR ol.approval_status = 'FORWARD'
OR ol.approval_status = 'NO RESPONSE'
OR ol.approval_status = 'NOTIFIED'
OR ol.approval_status = 'REJECT'
);
approversOut.delete;