[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select variable_value
into adminName
from ame_config_vars
where variable_name = ame_util.adminApproverConfigVar and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into adminName
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) ;
select width
into columnLength
from fnd_columns
where
table_id =
(select table_id
from fnd_tables
where
table_name = upper(tableNameIn) and
application_id = fndApplicationIdIn) and
application_id = fndApplicationIdIn and
column_name = upper(columnNameIn);
select description
into description
from ame_config_vars
where
variable_name = variableNameIn and
(application_id is null or application_id = 0) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into variableValue
from ame_config_vars
where
variable_name = variableNameIn and
(application_id is null or application_id = 0) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into variableValue
from ame_config_vars
where
variable_name = variableNameIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select variable_value
into variableValue
from ame_config_vars
where
variable_name = variableNameIn and
(application_id is null or application_id = 0) and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select name
into returnValue
from fnd_currencies_active_v
where currency_code = currencyCodeIn;
select name
into tempName
from per_business_groups
where
business_group_id = busGroupIdIn and
sysdate >= date_from and
(date_to is null or sysdate < date_to);
select a.responsibility_key
from FND_SECURITY_GROUPS_VL fsg,
fnd_responsibility_vl a,
FND_USER_RESP_GROUPS b
where b.user_id = userId and
b.start_date <= sysdate and
(b.end_date is null or b.end_date > sysdate) and
b.RESPONSIBILITY_id = a.responsibility_id and
b.RESPONSIBILITY_application_id = a.application_id and
a.version in ('W','4') and
a.start_date <= sysdate and
(a.end_date is null or a.end_date > sysdate) and
b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID
order by a.responsibility_key;
select attribute_label_long
into attributeLabelOut
from ak_attributes_vl
where attribute_code = attributeCodeIn
and attribute_application_id = attributeApplicationIdIn;
select name
into tempName
from hr_organization_units
where
organization_id = orgIdIn and
trunc(sysdate) >= date_from and
(date_to is null or trunc(sysdate) < date_to);
function getQuery(selectClauseIn in varchar2) return ame_util.queryCursor as
queryCursor ame_util.queryCursor;
sqlStatement := selectClauseIn;
select name
into tempName
from gl_sets_of_books
where set_of_books_id = setOfBooksIdIn;
select
host_name,
instance_name
into
hostName,
instanceName
from v$instance;
select count(*)
into tempCount
from gl_daily_conversion_types
where conversion_type = conversionTypeIn;
select count(*)
into tempCount
from fnd_currencies_active_v
where currency_code = currencyCodeIn;
select user_id
into tempUserId
from fnd_user
where employee_id = personIdIn;
select employee_id
into tempPersonId
from fnd_user
where user_id = userIdIn;
select attribute_id
into tempAttributeId
from ame_attributes
where name = upper(attributeNameIn)
and sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_exceptions_log_s.nextval into tempLogId from dual;
select query_string
into temQueryString
from ame_attribute_usages
where
attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_exceptions_log_s.nextval into tempLogId from dual;
select is_static
into tempIsSatic
from ame_attribute_usages
where attribute_id = attributeIdIn and
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) ;
select ame_exceptions_log_s.nextval into tempLogId from dual;
select name, display_name
into wfName, wfDisplayName
from wf_roles wf
where orig_system = tempOrigSystem
and orig_system_id = tempOrigSystemId
and status = 'ACTIVE'
and (expiration_date is null or sysdate < expiration_date)
and exists (select null
from fnd_user u
where u.user_name = wf.name
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate)))
and not exists (
select null from wf_roles wf2
where wf.orig_system = wf2.orig_system
and wf.orig_system_id = wf2.orig_system_id
and wf.start_date > wf2.start_date
)
and rownum < 2;
select name
,display_name
,orig_system
,orig_system_id
into wfName
,wfDisplayName
,tempOrigSystem
,tempOrigSystemId
from wf_roles wf
where wf.orig_system in('FND_USR','PER')
and wf.name in (select u.user_name
from fnd_user u
where u.user_id = tempOrigSystemId
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate)))
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or sysdate < wf.expiration_date)
-- need not check for proxy user in this case
and rownum < 2;
approverRecord2Out.api_insertion := approverRecordIn.api_insertion;
select pap.first_name
,pap.last_name
into firstName
,lastName
from per_all_people_f pap
,per_all_assignments_f pas
where pap.person_id = approverRecordOut.person_id
and pap.person_id = pas.person_id
and pas.primary_flag = 'Y'
and pas.assignment_type in ('E','C')
and pas.assignment_status_type_id not in
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'TERM_ASSIGN')
and trunc(sysdate) between pas.effective_start_date and pas.effective_end_date
and (
trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
or (pap.effective_start_date <= trunc(sysdate) and pap.effective_end_date is null)
);
select user_name
into userName
from fnd_user
where user_id = approverRecordOut.user_id and
(sysdate between start_date and end_date or
(start_date <= sysdate and end_date is null));
approverRecordOut.api_insertion := approverRecord2In.api_insertion;
approversTable2Out(ct).api_insertion := approversTableIn(ct).api_insertion;
select name, display_name
into wfName, wfDisplayName
from wf_roles wf
where orig_system = tempOrigSystem
and orig_system_id = tempOrigSystemId
and status = 'ACTIVE'
and (expiration_date is null or sysdate < expiration_date)
and exists (select null
from fnd_user u
where u.user_name = wf.name
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate)))
and not exists (
select null from wf_roles wf2
where wf.orig_system = wf2.orig_system
and wf.orig_system_id = wf2.orig_system_id
and wf.start_date > wf2.start_date
)
and rownum < 2;
select name
,display_name
,orig_system
,orig_system_id
into wfName
,wfDisplayName
,tempOrigSystem
,tempOrigSystemId
from wf_roles wf
where wf.orig_system in('FND_USR','PER')
and wf.name in (select u.user_name
from fnd_user u
where u.user_id = tempOrigSystemId
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate)))
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or sysdate < wf.expiration_date)
-- need not check for proxy user in this case
and rownum < 2;
approversTable2Out.delete;
approversTable2Out.delete;
select pap.first_name
,pap.last_name
into firstName
,lastName
from per_all_people_f pap
,per_all_assignments_f pas
where pap.person_id = approversTableOut(ct).person_id
and pap.person_id = pas.person_id
and pas.primary_flag = 'Y'
and pas.assignment_type in ('E','C')
and pas.assignment_status_type_id not in
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'TERM_ASSIGN')
and trunc(sysdate) between pas.effective_start_date and pas.effective_end_date
and (
trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
or (pap.effective_start_date <= trunc(sysdate) and pap.effective_end_date is null)
);
select user_name
into userName
from fnd_user
where user_id = approversTableOut(ct).user_id and
(sysdate between start_date and end_date or
(start_date <= sysdate and end_date is null));
approversTableOut(ct).api_insertion := approversTable2In(ct).api_insertion;
approversTableOut.delete;
approversTableOut.delete;
approversTableOut.delete;
approversTableOut.delete;
insert into ame_exceptions_log(
log_id,
package_name,
routine_name,
transaction_id,
application_id,
exception_number,
exception_string) values(
logIdIn,
substrb(packageNameIn, 1, 50),
substrb(routineNameIn, 1, 50),
transactionIdIn,
applicationIdIn,
exceptionNumberIn,
substrb(to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')||exceptionStringIn, 1, 4000));
keywords(1) := 'delete';
keywords(2) := 'insert';
keywords(3) := 'update';
/* Second, delete all slots beyond the nth slot. */
loop
tempNextIndex := idListInOut.next(upperLimit);
idListInOut.delete(tempNextIndex);
/* Second, delete all slots beyond the nth slot. */
loop
tempNextIndex := longestStringListInOut.next(upperLimit);
longestStringListInOut.delete(tempNextIndex);
/* Second, delete all slots beyond the nth slot. */
loop
tempNextIndex := longStringListInOut.next(upperLimit);
longStringListInOut.delete(tempNextIndex);
/* Second, delete all slots beyond the nth slot. */
loop
tempNextIndex := stringListInOut.next(upperLimit);
stringListInOut.delete(tempNextIndex);
apiInsertionValuesOut out nocopy ame_util.charList,
authorityValuesOut out nocopy ame_util.charList,
approvalTypeIdValuesOut out nocopy ame_util.idList,
groupOrChainIdValuesOut out nocopy ame_util.idList,
occurrenceValuesOut out nocopy ame_util.idList,
sourceValuesOut out nocopy ame_util.longStringList,
statusValuesOut out nocopy ame_util.stringList) as
upperLimit integer;
apiInsertionValuesOut(i) := approversTableIn(i).api_insertion;
apiInsertionValuesOut.delete;
authorityValuesOut.delete;
personIdValuesOut.delete;
statusValuesOut.delete;
userIdValuesOut.delete;
approvalTypeIdValuesOut.delete;
groupOrChainIdValuesOut.delete;
occurrenceValuesOut.delete;
sourceValuesOut.delete;
apiInsertionsOut out nocopy ame_util.charList,
authoritiesOut out nocopy ame_util.charList,
actionTypeIdsOut out nocopy ame_util.idList,
groupOrChainIdsOut out nocopy ame_util.idList,
occurrencesOut out nocopy ame_util.idList,
approverCategoriesOut out nocopy ame_util.charList,
statusesOut out nocopy ame_util.stringList) as
begin
for i in 1 .. approversTableIn.count loop
namesOut(i) := approversTableIn(i).name;
apiInsertionsOut(i) := approversTableIn(i).api_insertion;
apiInsertionValuesIn in ame_util.charList,
authorityValuesIn in ame_util.charList,
approvalTypeIdValuesIn in ame_util.idList,
groupOrChainIdValuesIn in ame_util.idList,
occurrenceValuesIn in ame_util.idList,
sourceValuesIn in ame_util.longStringList,
statusValuesIn in ame_util.stringList,
approversTableOut out nocopy ame_util.approversTable) as
badCountException exception;
upperLimit <> apiInsertionValuesIn.count or
upperLimit <> authorityValuesIn.count or
upperLimit <> approvalTypeIdValuesIn.count or
upperLimit <> groupOrChainIdValuesIn.count or
upperLimit <> occurrenceValuesIn.count or
upperLimit <> sourceValuesIn.count or
upperLimit <> statusValuesIn.count) then
raise badCountException;
approversTableOut(i).api_insertion := apiInsertionValuesIn(i);
approversTableOut.delete;
apiInsertionValuesIn in ame_util.charList,
authorityValuesIn in ame_util.charList,
approvalTypeIdValuesIn in ame_util.idList,
groupOrChainIdValuesIn in ame_util.idList,
occurrenceValuesIn in ame_util.idList,
sourceValuesIn in ame_util.longStringList,
statusValuesIn in ame_util.stringList,
approversTableOut out nocopy ame_util.approversTable2) as
badCountException exception;
upperLimit <> apiInsertionValuesIn.count or
upperLimit <> authorityValuesIn.count or
upperLimit <> approvalTypeIdValuesIn.count or
upperLimit <> groupOrChainIdValuesIn.count or
upperLimit <> occurrenceValuesIn.count or
upperLimit <> sourceValuesIn.count or
upperLimit <> statusValuesIn.count) then
raise badCountException;
approversTableOut(i).api_insertion := apiInsertionValuesIn(i);
approversTableOut.delete;
approverRecord2Out.api_insertion := approverRecord2In.api_insertion;
approversTable2Out(tempIndex).api_insertion := approversTable2In(tempIndex).api_insertion;
approversTable2Out.delete;
charListOut.delete;
idListOut.delete;
longStringListOut.delete;
stringListOut.delete;
select
application_id,
application_name
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_name;
select
ak_web_user_sec_attr_values.number_value,
ame_calling_apps.application_name
from ak_web_user_sec_attr_values,
ame_calling_apps
where ak_web_user_sec_attr_values.number_value = ame_calling_apps.application_id and
web_user_id = userId and
sysdate between ame_calling_apps.start_date and
nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
order by application_name;
select
application_id,
application_name
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_name;
select
application_id,
application_name
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_name;
select
application_id,
application_name
from ame_calling_apps
where
application_id <> applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
order by application_name;
select distinct conversion_type
from gl_daily_conversion_types;
select currency_code
from fnd_currencies_active_v;
select
name,
currency_code
from
fnd_currencies_active_v
order by currency_code;
select
fnd_application_id,
transaction_type_id
into
fndApplicationIdOut,
transactionTypeIdOut
from ame_calling_apps
where
application_id = applicationIdIn and
/* Don't use tempEffectiveRuleDate here. */
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
procedure insTable2ToInsTable(insertionsTable2In in ame_util.insertionsTable2,
insertionsTableOut out nocopy ame_util.insertionsTable) as
errorCode integer;
insertionTargetActionId ame_util.StringType;
insertionTargetApprover ame_util.longStringType;
insertionTargetGrpChainId ame_util.longStringType;
insertionTargetItemClass ame_util.StringType;
insertionTargetItemId ame_util.StringType;
insertionTargetOccurrence ame_util.StringType;
for i in 1..insertionsTable2In.count loop
if insertionsTable2In(i).item_class <> ame_util.headerItemClassName then
raise wrongItemClass;
insertionsTableOut(i).order_type := insertionsTable2In(i).order_type ;
insertionsTableOut(i).api_insertion := insertionsTable2In(i).api_insertion ;
insertionsTableOut(i).authority := insertionsTable2In(i).authority ;
insertionsTableOut(i).description := insertionsTable2In(i).description ;
if (insertionsTableOut(i).order_type = ame_util.absoluteOrder) then
/* The parameter is unchanged */
insertionsTableOut(i).parameter := insertionsTable2In(i).parameter ;
elsif (insertionsTableOut(i).order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
/* The in parameter has the format :
approvers(positionIn - 1).name || ame_util.fieldDelimiter ||
approvers(positionIn - 1).item_class || ame_util.fieldDelimiter ||
approvers(positionIn - 1).item_id || ame_util.fieldDelimiter ||
approvers(positionIn - 1).action_type_id || ame_util.fieldDelimiter ||
approvers(positionIn - 1).group_or_chain_id || ame_util.fieldDelimiter ||
approvers(positionIn - 1).occurrence;
tempInteger1 := instrb(insertionsTable2In(i).parameter,ame_util.fieldDelimiter, 1);
insertionTargetApprover := substrb(insertionsTable2In(i).parameter, 1, tempInteger1 - 1);
tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
insertionTargetItemClass := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
insertionTargetItemId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
insertionTargetActionId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
insertionTargetGrpChainId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
insertionTargetOccurrence := substrb(insertionsTable2In(i).parameter, tempInteger1);
ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => insertionTargetApprover,
origSystemOut => tempOrigSystem,
origSystemIdOut => tempIndex);
insertionsTableOut(i).parameter := tempOrigSystem || ':' || tempIndex || ':' ||
insertionTargetActionId || ':' || insertionTargetGrpChainId || ':' ||
insertionTargetOccurrence ;
elsif (insertionsTableOut(i).order_type = ame_util.firstPreApprover) then
/* the out parameter has the format :
ame_util.firstPreApprover
*/
insertionsTableOut(i).parameter := ame_util.firstPreApprover;
elsif (insertionsTableOut(i).order_type = ame_util.lastPreApprover) then
/* the out parameter has the format :
ame_util.lastPreApprover
*/
insertionsTableOut(i).parameter := ame_util.lastPreApprover;
elsif (insertionsTableOut(i).order_type = ame_util.firstPostApprover) then
/* the out parameter has the format :
ame_util.firstPostApprover
*/
insertionsTableOut(i).parameter := ame_util.firstPostApprover;
elsif (insertionsTableOut(i).order_type = ame_util.lastPostApprover) then
/* the out parameter has the format :
ame_util.lastPostApprover
*/
insertionsTableOut(i).parameter := ame_util.lastPostApprover;
elsif (insertionsTableOut(i).order_type = ame_util.firstAuthority) then
/* the out parameter has the format :
ame_util.firstAuthority
*/
insertionsTableOut(i).parameter := ame_util.firstAuthority;
procedure insTableToInsTable2(insertionsTableIn in ame_util.insertionsTable,
transactionIdIn in varchar2,
insertionsTable2Out out nocopy ame_util.insertionsTable2) as
errorCode integer;
insertionTargetActionId ame_util.StringType;
insertionTargetApprover ame_util.longStringType;
insertionTargetGrpChainId ame_util.longStringType;
insertionTargetItemClass ame_util.StringType;
insertionTargetItemId ame_util.StringType;
insertionTargetOccurrence ame_util.StringType;
for i in 1..insertionsTableIn.count loop
insertionsTable2Out(i).item_class := ame_util.headerItemClassName ;
insertionsTable2Out(i).item_id := transactionIdIn ;
insertionsTable2Out(i).order_type := insertionsTableIn(i).order_type ;
insertionsTable2Out(i).api_insertion := insertionsTableIn(i).api_insertion ;
insertionsTable2Out(i).authority := insertionsTableIn(i).authority ;
insertionsTable2Out(i).description := insertionsTableIn(i).description ;
insertionsTable2Out(i).action_type_id := ame_util.nullInsertionActionTypeId ;
insertionsTable2Out(i).group_or_chain_id := ame_util.nullInsertionGroupOrChainId ;
if (insertionsTable2Out(i).order_type = ame_util.absoluteOrder) then
/* The parameter is unchanged */
insertionsTable2Out(i).parameter := insertionsTableIn(i).parameter ;
elsif (insertionsTable2Out(i).order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
/* The in parameter has the format :
{ame_util.approverUserId,ame_util.approverPersonId} || ':' ||
{approverList(positionIn - 1).user_id, approverList(positionIn - 1).person_id}
':' || approverList(positionIn - 1).approval_type_id || ':' ||
approverList(positionIn - 1).group_or_chain_id || ':' ||
approverList(positionIn - 1).occurrence;
tempInteger1 := instrb(insertionsTableIn(i).parameter, ':', 1, 1);
insertionTargetApprover := substrb(insertionsTableIn(i).parameter, 1, tempInteger1 - 1);
tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
tempIndex := to_number(substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1));
if insertionTargetApprover = ame_util.approverPersonId then
tempOrigSystem := ame_util.perOrigSystem;
elsif insertionTargetApprover = ame_util.approverUserId then
tempOrigSystem := ame_util.fndUserOrigSystem;
insertionTargetItemClass := ame_util.headerItemClassName;
insertionTargetItemId := transactionIdIn;
tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
insertionTargetActionId := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
insertionTargetGrpChainId := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
insertionTargetOccurrence := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
insertionsTable2Out(i).parameter := apprName || ame_util.fieldDelimiter || insertionTargetItemClass||
ame_util.fieldDelimiter ||insertionTargetItemId||
ame_util.fieldDelimiter ||insertionTargetActionId ||
ame_util.fieldDelimiter || insertionTargetGrpChainId ||
ame_util.fieldDelimiter||insertionTargetOccurrence;
insertionsTable2Out(i).action_type_id := insertionTargetActionId;
insertionsTable2Out(i).group_or_chain_id := insertionTargetGrpChainId;
elsif (insertionsTable2Out(i).order_type = ame_util.firstPreApprover) then
/* the in parameter has the format :
ame_util.firstPreApprover
the out format is:
ame_util.firstPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionsTable2Out(i).parameter := ame_util.firstPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionsTable2Out(i).order_type = ame_util.lastPreApprover) then
/* the in parameter has the format :
ame_util.lastPreApprover
the out format is:
ame_util.lastPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionsTable2Out(i).parameter := ame_util.lastPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionsTable2Out(i).order_type = ame_util.firstPostApprover) then
/* the in parameter has the format :
ame_util.firstPostApprover
the out format is:
ame_util.firstPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionsTable2Out(i).parameter := ame_util.firstPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionsTable2Out(i).order_type = ame_util.lastPostApprover) then
/* the in parameter has the format :
ame_util.lastPostApprover
the out format is:
ame_util.lastPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionsTable2Out(i).parameter := ame_util.lastPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionsTable2Out(i).order_type = ame_util.firstAuthority) then
/* the in parameter has the format :
ame_util.firstAuthority
the out format is:
ame_util.firstAuthority ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionsTable2Out(i).parameter := ame_util.firstAuthority ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insert into ame_exceptions_log(
log_id,
package_name,
routine_name,
transaction_id,
application_id,
exception_number,
exception_string) values(
logIdIn,
substrb(packageNameIn, 1, 50),
substrb(routineNameIn, 1, 50),
transactionIdIn,
applicationIdIn,
exceptionNumberIn,
substrb(to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')||exceptionStringIn, 1, 4000));
insertionRecord2Out out nocopy ame_util.insertionRecord2) as
errorCode integer;
insertionTargetActionId ame_util.StringType;
insertionTargetApprover ame_util.longStringType;
insertionTargetGrpChainId ame_util.longStringType;
insertionTargetItemClass ame_util.StringType;
insertionTargetItemId ame_util.StringType;
insertionTargetOccurrence ame_util.StringType;
insertionRecord2Out.item_class := ame_util.headerItemClassName ;
insertionRecord2Out.item_id := transactionIdIn ;
insertionRecord2Out.order_type := orderRecordIn.order_type ;
insertionRecord2Out.api_insertion := approverIn.api_insertion ;
insertionRecord2Out.authority := approverIn.authority ;
insertionRecord2Out.description := orderRecordIn.description ;
insertionRecord2Out.action_type_id := ame_util.nullInsertionActionTypeId ;
insertionRecord2Out.group_or_chain_id := ame_util.nullInsertionGroupOrChainId ;
if (insertionRecord2Out.order_type = ame_util.absoluteOrder) then
/* The parameter is unchanged */
insertionRecord2Out.parameter := orderRecordIn.parameter ;
elsif (insertionRecord2Out.order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
/* The in parameter has the format :
{ame_util.approverUserId,ame_util.approverPersonId} || ':' ||
{approverList(positionIn - 1).user_id, approverList(positionIn - 1).person_id}
':' || approverList(positionIn - 1).approval_type_id || ':' ||
approverList(positionIn - 1).group_or_chain_id || ':' ||
approverList(positionIn - 1).occurrence;
insertionTargetApprover := substrb(orderRecordIn.parameter, 1, tempInteger1 - 1);
if insertionTargetApprover = ame_util.approverPersonId then
tempOrigSystem := ame_util.perOrigSystem;
elsif insertionTargetApprover = ame_util.approverUserId then
tempOrigSystem := ame_util.fndUserOrigSystem;
insertionTargetItemClass := ame_util.headerItemClassName;
insertionTargetItemId := transactionIdIn;
insertionTargetActionId := substrb(orderRecordIn.parameter, tempInteger1, tempInteger2 - tempInteger1);
insertionTargetGrpChainId := substrb(orderRecordIn.parameter, tempInteger1, tempInteger2 - tempInteger1);
insertionTargetOccurrence := substrb(orderRecordIn.parameter, tempInteger1);
insertionRecord2Out.parameter := apprName || ame_util.fieldDelimiter || insertionTargetItemClass||
ame_util.fieldDelimiter ||insertionTargetItemId||
ame_util.fieldDelimiter ||insertionTargetActionId ||
ame_util.fieldDelimiter || insertionTargetGrpChainId ||
ame_util.fieldDelimiter||insertionTargetOccurrence;
insertionRecord2Out.action_type_id := insertionTargetActionId;
insertionRecord2Out.group_or_chain_id := insertionTargetGrpChainId;
elsif (insertionRecord2Out.order_type = ame_util.firstPreApprover) then
/* the in parameter has the format :
ame_util.firstPreApprover
the out format is:
ame_util.firstPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionRecord2Out.parameter := ame_util.firstPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionRecord2Out.order_type = ame_util.lastPreApprover) then
/* the in parameter has the format :
ame_util.lastPreApprover
the out format is:
ame_util.lastPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionRecord2Out.parameter := ame_util.lastPreApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionRecord2Out.order_type = ame_util.firstPostApprover) then
/* the in parameter has the format :
ame_util.firstPostApprover
the out format is:
ame_util.firstPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionRecord2Out.parameter := ame_util.firstPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionRecord2Out.order_type = ame_util.lastPostApprover) then
/* the in parameter has the format :
ame_util.lastPostApprover
the out format is:
ame_util.lastPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionRecord2Out.parameter := ame_util.lastPostApprover ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
elsif (insertionRecord2Out.order_type = ame_util.firstAuthority) then
/* the in parameter has the format :
ame_util.firstAuthority
the out format is:
ame_util.firstAuthority ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
insertionRecord2Out.parameter := ame_util.firstAuthority ||
ame_util.fieldDelimiter ||
ame_util.headerItemClassName ||
ame_util.fieldDelimiter ||
transactionIdIn;
if(sourceSubstring in (approveAndForwardInsertion,
forwardInsertion,
specialForwardInsertion)) then
sourceDescriptionOut := forwardeeSource;
elsif(sourceSubstring = surrogateInsertion) then
sourceDescriptionOut := surrogateSource;
elsif(sourceSubstring = otherInsertion) then
sourceDescriptionOut := inserteeSource;
select application_id
from ame_calling_apps
where
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select attribute_id
from ame_attribute_usages
where
application_id = applicationIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
delete from ame_temp_trans_locks
where row_timestamp < sysdate - 1/24;
select ame_exceptions_log_s.nextval into logId from dual;
select description
into description
from ame_config_vars
where variable_name = variableNameIn and
(application_id is null or application_id = 0) and
end_date is null;
update ame_config_vars
set
last_updated_by = currentUserId,
last_update_date = sysdate,
last_update_login = currentUserId,
end_date = sysdate
where variable_name = variableNameIn and
((applicationIdIn is null and (application_id is null or application_id = 0)) or
application_id = applicationIdIn) and
sysdate between start_date and
nvl(end_date - (ame_util.oneSecond), sysdate);
insert into ame_config_vars(variable_name,
variable_value,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
application_id)
values(variableNameIn,
variableValueIn,
description,
currentUserId,
sysdate,
currentUserId,
sysdate,
currentUserId,
sysdate,
applicationIdIn);