[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select display_name
into name
from wf_local_roles
where ((orig_system = origSystemIn and
orig_system_id = origSystemIdIn) or
(origSystemIn = ame_util.fndUserOrigSystem and
orig_system = ame_util.perOrigSystem and
orig_system_id = (select employee_id
from fnd_user
where user_id = origSystemIdIn)))
and status = 'ACTIVE'
and (orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
or exists
(select null
from fnd_user u
where u.user_name = wf_local_roles.name))
and not exists (
select null from wf_local_roles wf2
where wf_local_roles.orig_system = wf2.orig_system
and wf_local_roles.orig_system_id = wf2.orig_system_id
and wf_local_roles.start_date > wf2.start_date)
and rownum < 2;
select display_name
into displayName
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select display_name
into displayName
from wf_roles
where
((orig_system = origSystemIn and
orig_system_id = origSystemIdIn) or
(origSystemIn = ame_util.fndUserOrigSystem and
orig_system = ame_util.perOrigSystem and
orig_system_id = (select employee_id
from fnd_user
where
user_id = origSystemIdIn and
sysdate between
start_date and
nvl(end_date, sysdate)))) and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
(orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
or exists (select null
from fnd_user u
where u.user_name = wf_roles.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_roles.orig_system = wf2.orig_system
and wf_roles.orig_system_id = wf2.orig_system_id
and wf_roles.start_date > wf2.start_date
) and
rownum < 2;
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 orig_system
into origSystem
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select orig_system
into origSystem
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select
orig_system
into
origSystem
from wf_local_roles
where
name = nameIn and
rownum < 2;
select orig_system_id
into origSystemId
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select approver_type_id
into approverTypeId
from ame_approver_types
where
orig_system = origSystemIn and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2;
select orig_system
into origSystem
from ame_approver_types
where
approver_type_id = approverTypeIdIn and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2;
select orig_system
into origSystem
from ame_approver_types
where
approver_type_id = approverTypeIdIn and
sysdate between start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2;
select meaning
into origDisplayName
from fnd_lookups
where
lookup_type = ame_util.origSystemLookupType and
lookup_code = origSystemIn and
sysdate between
start_date_active and
nvl(end_date_active, sysdate) and
rownum < 2;
select query_procedure
into queryProcedure
from ame_approver_types
where
approver_type_id = approverTypeIdIn and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate) and
rownum < 2;
select name
into name
from wf_roles
where
((orig_system = origSystemIn and
orig_system_id = origSystemIdIn) or
(origSystemIn = ame_util.fndUserOrigSystem and
orig_system = ame_util.perOrigSystem and
orig_system_id = (select employee_id
from fnd_user
where
user_id = origSystemIdIn and
sysdate between
start_date and
nvl(end_date, sysdate)))) and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
(orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
or exists (select null
from fnd_user u
where u.user_name = wf_roles.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_roles.orig_system = wf2.orig_system
and wf_roles.orig_system_id = wf2.orig_system_id
and wf_roles.start_date > wf2.start_date
) and
rownum < 2;
select count(*)
into rowCount
from ame_approver_type_usages
where
action_type_id = actionTypeIdIn and
approver_type_id = ame_util.anyApproverType and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select supervisor_id
into superiorId
from per_all_assignments_f
where
per_all_assignments_f.person_id = origSystemId and
per_all_assignments_f.primary_flag = 'Y' and
per_all_assignments_f.assignment_type in ('E','C') and
per_all_assignments_f.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
per_all_assignments_f.effective_start_date and
per_all_assignments_f.effective_end_date;
select str.parent_position_id
into superiorId
from per_pos_structure_elements str,
per_pos_structure_versions psv
where
str.subordinate_position_id = origSystemId and
str.pos_structure_version_id = psv.pos_structure_version_id and
trunc(sysdate) between psv.date_from and nvl( psv.date_to , sysdate) and
psv.position_structure_id =
(select position_structure_id
from per_position_structures
where
((positionStructureId is not null and
position_structure_id = positionStructureId) or
(positionStructureId is null and
business_group_id = str.business_group_id and
primary_position_flag = 'Y')));
select count(*)
into rowCount
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date);
select
/* The compiler forces passing arguments by position in the following function calls. */
getWfRolesName(ame_util.fndUserOrigSystem, user_id) approver_name,
getApproverDescription(getWfRolesName(ame_util.fndUserOrigSystem, user_id)) approver_description
from
fnd_user,
wf_roles
where
wf_roles.orig_system_id = fnd_user.user_id and
wf_roles.orig_system = ame_util.fndUserOrigSystem and
wf_roles.status = 'ACTIVE' and
wf_roles.name = fnd_user.user_name and
(userNameIn is null or
upper(fnd_user.user_name) like upper(replace(userNameIn, '''', '''''')) || '%') and
(emailAddressIn is null or
upper(fnd_user.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%') and
truncatedSysdateIn between
fnd_user.start_date and
nvl(fnd_user.end_date, truncatedSysdateIn) and
rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
order by fnd_user.user_name;
select
getWfRolesName(ame_util.fndRespOrigSystem||resp.application_id, resp.responsibility_id) approver_name,
getApproverDescription(getWfRolesName(ame_util.fndRespOrigSystem || resp.application_id,
resp.responsibility_id)) approver_description
from
fnd_application_vl apps,
fnd_responsibility_vl resp
where
(applicationNameIn is null or
upper(apps.application_name) like upper(replace(applicationNameIn, '''', '''''')) || '%') and
(responsibilityNameIn is null or
upper(resp.responsibility_name) like upper(replace(responsibilityNameIn, '''', '''''')) || '%') and
resp.application_id = apps.application_id and
truncatedSysdateIn between
resp.start_date and
nvl(resp.end_date,truncatedSysdateIn) and
rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
order by apps.application_name;
select
display_name,
orig_system
into
descriptionOut,
origSystem
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
into
descriptionOut,
origSystem
from wf_local_roles
where
name = nameIn and
rownum < 2;
select
orig_system,
orig_system_id
into
origSystemOut,
origSystemIdOut
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select name
from
ame_action_types,
ame_approver_type_usages
where
ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
approver_type_id = approverTypeIdIn and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_approver_type_usages.start_date and
nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
select name
from
ame_action_types,
ame_approver_type_usages
where
ame_action_types.action_type_id = ame_approver_type_usages.action_type_id and
approver_type_id = ame_util.anyApproverType and
sysdate between ame_action_types.start_date and
nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between ame_approver_type_usages.start_date and
nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate);
/* select queryVariableLabels and variableLovQueries into plsql tables */
select
query_variable_1_label,
query_variable_2_label,
query_variable_3_label,
query_variable_4_label,
query_variable_5_label,
variable_1_lov_query,
variable_2_lov_query,
variable_3_lov_query,
variable_4_lov_query,
variable_5_lov_query
into
queryVariableLabels(1),
queryVariableLabels(2),
queryVariableLabels(3),
queryVariableLabels(4),
queryVariableLabels(5),
variableLovQueries(1),
variableLovQueries(2),
variableLovQueries(3),
variableLovQueries(4),
variableLovQueries(5)
from ame_approver_types
where
approver_type_id = approverTypeIdIn and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select
approver_type_id,
ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
from ame_approver_types
where sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select
approver_type_id,
ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
from ame_approver_types
where
orig_system in (ame_util.perOrigSystem, ame_util.fndUserOrigSystem) and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select
approver_type_id,
ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
from ame_approver_types
where sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate)
minus
select
ame_approver_types.approver_type_id,
ame_approver_type_pkg.getOrigSystemDisplayName(orig_system) approver_type_name
from ame_approver_types,
ame_approver_type_usages
where
ame_approver_types.approver_type_id = ame_approver_type_usages.approver_type_id and
ame_approver_type_usages.action_type_id = actionTypeIdIn and
sysdate between
ame_approver_types.start_date and
nvl(ame_approver_types.end_date - ame_util.oneSecond, sysdate) and
sysdate between
ame_approver_type_usages.start_date and
nvl(ame_approver_type_usages.end_date - ame_util.oneSecond, sysdate)
order by approver_type_name;
select approver_type_id
from ame_approver_type_usages
where
action_type_id = actionTypeIdIn and
approver_type_id <> ame_util.anyApproverType and
sysdate between
start_date and
nvl(end_date - ame_util.oneSecond, sysdate);
select
orig_system,
orig_system_id,
display_name
into
origSystemOut,
origSystemIdOut,
displayNameOut
from wf_roles
where
name = nameIn and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
rownum < 2;
select
per_all_assignments_f.supervisor_id,
wf_roles.name,
wf_roles.display_name
into
superiorOut.orig_system_id,
superiorOut.name,
superiorOut.display_name
from
wf_roles,
per_all_assignments_f
where
wf_roles.orig_system = ame_util.perOrigSystem and
wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
exists (select null
from fnd_user u
where u.user_name = wf_roles.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_roles.orig_system = wf2.orig_system
and wf_roles.orig_system_id = wf2.orig_system_id
and wf_roles.start_date > wf2.start_date
) and
per_all_assignments_f.person_id =approverIn.orig_system_id and
per_all_assignments_f.primary_flag = 'Y' and
per_all_assignments_f.assignment_type in ('E','C') and
per_all_assignments_f.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
per_all_assignments_f.effective_start_date and
per_all_assignments_f.effective_end_date and
rownum < 2;
select
per_all_assignments_f.supervisor_id,
wf_roles.name,
wf_roles.display_name
into
superiorOut.orig_system_id,
superiorOut.name,
superiorOut.display_name
from
wf_roles,
per_all_assignments_f
where
wf_roles.orig_system = ame_util.perOrigSystem and
wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
exists (select null
from fnd_user u
where u.user_name = wf_roles.name
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate))) and
per_all_assignments_f.person_id =
(select employee_id
from fnd_user
where
user_id = approverIn.orig_system_id and
rownum < 2) and
per_all_assignments_f.primary_flag = 'Y' and
per_all_assignments_f.assignment_type in ('E','C') and
per_all_assignments_f.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
per_all_assignments_f.effective_start_date and
per_all_assignments_f.effective_end_date and
rownum < 2
order by wf_roles.name; /* Select the first matching wf_roles entry. */
select
str.parent_position_id,
wf_roles.name,
wf_roles.display_name
into
superiorOut.orig_system_id,
superiorOut.name,
superiorOut.display_name
from
per_pos_structure_elements str,
per_pos_structure_versions psv,
per_position_structures pst,
wf_roles
where
str.subordinate_position_id = approverIn.orig_system_id and
str.business_group_id =
nvl(hr_general.get_business_group_id,str.business_group_id) and
str.pos_structure_version_id = psv.pos_structure_version_id and
pst.position_structure_id = psv.position_structure_id and
pst.primary_position_flag = 'Y' and
wf_roles.orig_system = ame_util.posOrigSystem and
wf_roles.orig_system_id = str.parent_position_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
trunc(sysdate) between
psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
rownum < 2
order by wf_roles.name;
select
str.parent_position_id,
wf_roles.name,
wf_roles.display_name
into
superiorOut.orig_system_id,
superiorOut.name,
superiorOut.display_name
from
per_pos_structure_elements str,
per_pos_structure_versions psv,
per_position_structures pst,
wf_roles
where
str.subordinate_position_id = approverIn.orig_system_id and
str.pos_structure_version_id = psv.pos_structure_version_id and
pst.position_structure_id = positionStructureId and
pst.position_structure_id = psv.position_structure_id and
wf_roles.orig_system = ame_util.posOrigSystem and
wf_roles.orig_system_id = str.parent_position_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
trunc(sysdate) between
psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
rownum < 2
order by wf_roles.name;
select
per_all_assignments_f.supervisor_id,
wf_roles.name,
wf_roles.display_name
into
origSystemIdOut,
wfRolesNameOut,
displayNameOut
from
wf_roles,
per_all_assignments_f
where
wf_roles.orig_system = ame_util.perOrigSystem and
wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
exists (select null
from fnd_user u
where u.user_name = wf_roles.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_roles.orig_system = wf2.orig_system
and wf_roles.orig_system_id = wf2.orig_system_id
and wf_roles.start_date > wf2.start_date
) and
per_all_assignments_f.person_id = origSystemIdIn and
per_all_assignments_f.primary_flag = 'Y' and
per_all_assignments_f.assignment_type in ('E','C') and
per_all_assignments_f.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
per_all_assignments_f.effective_start_date and
per_all_assignments_f.effective_end_date and
rownum < 2;
select
per_all_assignments_f.supervisor_id,
wf_roles.name,
wf_roles.display_name
into
origSystemIdOut,
wfRolesNameOut,
displayNameOut
from
wf_roles,
per_all_assignments_f
where
wf_roles.orig_system = ame_util.perOrigSystem and
wf_roles.orig_system_id = per_all_assignments_f.supervisor_id and
wf_roles.status = 'ACTIVE' and
exists (select null
from fnd_user u
where u.user_name = wf_roles.name
and trunc(sysdate) between u.start_date
and nvl(u.end_date,trunc(sysdate))) and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
per_all_assignments_f.person_id =
(select employee_id
from fnd_user
where
user_id = origSystemIdIn and
rownum < 2) and
per_all_assignments_f.primary_flag = 'Y' and
per_all_assignments_f.assignment_type in ('E','C') and
per_all_assignments_f.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
per_all_assignments_f.effective_start_date and
per_all_assignments_f.effective_end_date and
rownum < 2
order by wf_roles.name; /* Select the first matching wf_roles entry. */
select
str.parent_position_id,
wf_roles.name,
wf_roles.display_name
into
origSystemIdOut,
wfRolesNameOut,
displayNameOut
from
per_pos_structure_elements str,
per_pos_structure_versions psv,
per_position_structures pst,
wf_roles
where
str.subordinate_position_id = origSystemIdIn and
str.business_group_id =
nvl(hr_general.get_business_group_id,str.business_group_id) and
str.pos_structure_version_id = psv.pos_structure_version_id and
pst.position_structure_id = psv.position_structure_id and
pst.primary_position_flag = 'Y' and
wf_roles.orig_system = ame_util.posOrigSystem and
wf_roles.orig_system_id = str.parent_position_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
trunc(sysdate) between
psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
rownum < 2
order by wf_roles.name;
select
str.parent_position_id,
wf_roles.name,
wf_roles.display_name
into
origSystemIdOut,
wfRolesNameOut,
displayNameOut
from
per_pos_structure_elements str,
per_pos_structure_versions psv,
per_position_structures pst,
wf_roles
where
str.subordinate_position_id = origSystemIdIn and
str.pos_structure_version_id = psv.pos_structure_version_id and
pst.position_structure_id = positionStructureId and
pst.position_structure_id = psv.position_structure_id and
wf_roles.orig_system = ame_util.posOrigSystem and
wf_roles.orig_system_id = str.parent_position_id and
wf_roles.status = 'ACTIVE' and
(wf_roles.expiration_date is null or sysdate < wf_roles.expiration_date) and
trunc(sysdate) between
psv.date_from and nvl( psv.date_to , trunc(sysdate)) and
rownum < 2
order by wf_roles.name;
This procedure should select the input approver's wf_roles.display_name, NOT the
display name of the input approver's orig_system.
*/
/*
If an fnd_user entry has a non-null employee_id (person ID) value, it gets
converted to the PER originating system in wf_roles; otherwise, it gets
select
name,
display_name
into
nameOut,
displayNameOut
from wf_roles
where
((orig_system = origSystemIn and
orig_system_id = origSystemIdIn) or
(origSystemIn = ame_util.fndUserOrigSystem and
orig_system = ame_util.perOrigSystem and
orig_system_id = (select employee_id
from fnd_user
where
user_id = origSystemIdIn and
sysdate between
start_date and
nvl(end_date, sysdate)))) and
status = 'ACTIVE' and
(expiration_date is null or
sysdate < expiration_date) and
(orig_system not in (ame_util.fndUserOrigSystem,ame_util.perOrigSystem)
or exists (select null
from fnd_user u
where u.user_name = wf_roles.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_roles.orig_system = wf2.orig_system
and wf_roles.orig_system_id = wf2.orig_system_id
and wf_roles.start_date > wf2.start_date
) and
rownum < 2;
insert into ame_approver_type_usages(approver_type_id,
action_type_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
start_date,
end_date)
values(approverTypeIdIn,
actionTypeIdIn,
currentUserId,
processingDateIn,
currentUserId,
processingDateIn,
currentUserId,
processingDateIn,
null);
select
/* The compiler forces passing arguments by position in the following function calls. */
getWfRolesName(ame_util.perOrigSystem, pap.person_id) approver_name
,getApproverDescription(getWfRolesName(ame_util.perOrigSystem, pap.person_id)) approver_description
from
per_all_people_f pap
,hr_all_organization_units haou
,wf_roles wfr
,per_all_assignments_f pas
where 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 wfr.orig_system_id = pap.person_id
and wfr.orig_system = ame_util.perOrigSystem
and wfr.status = 'ACTIVE'
and exists (select null
from fnd_user u
where u.user_name = wfr.name
and truncatedSysdateIn between u.start_date
and nvl(u.end_date,truncatedSysdateIn))
and (firstNameIn is null or upper(pap.first_name) like upper(replace(firstNameIn, '''', '''''')) || '%')
and (lastNameIn is null or upper(pap.last_name) like upper(replace(lastNameIn, '''', '''''')) || '%')
and (emailAddressIn is null or upper(pap.email_address) like upper(replace(emailAddressIn, '''', '''''')) || '%')
and pap.business_group_id = haou.organization_id
and truncatedSysdateIn between pap.effective_start_date and nvl(pap.effective_end_date, truncatedSysdateIn)
and truncatedSysdateIn between haou.date_from and nvl(haou.date_to, truncatedSysdateIn)
and truncatedSysdateIn between pas.effective_start_date and pas.effective_end_date
and rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
order by last_name;
select
getWfRolesName(ame_util.posOrigSystem, per_positions.position_id) approver_name,
getApproverDescription(getWfRolesName(ame_util.posOrigSystem, orig_system_id)) approver_description
from
per_positions,
hr_organization_units,
wf_roles
where
wf_roles.orig_system_id = per_positions.position_id and
wf_roles.orig_system = ame_util.posOrigSystem and
wf_roles.status = 'ACTIVE' and
(positionNameIn is null or upper(per_positions.name) like upper(replace(positionNameIn, '''', '''''')) || '%') and
(businessGroupNameIn is null or upper(hr_organization_units.name) like upper(replace(businessGroupNameIn, '''', '''''')) || '%') and
per_positions.business_group_id = hr_organization_units.organization_id and
truncatedSysdateIn between
per_positions.date_effective and
nvl(per_positions.date_end, truncatedSysdateIn) and
truncatedSysdateIn between
hr_organization_units.date_from and
nvl(hr_organization_units.date_to, truncatedSysdateIn) and
rownum < 52 + rowsToExcludeIn /* This prevents oversized fetches. */
order by per_positions.name;
procedure processApproverQuery(selectClauseIn in varchar2,
approverNamesOut out nocopy ame_util.longStringList,
approverDisplayNamesOut out nocopy ame_util.longStringList) as
tempApproverDisplayName ame_util.longStringType;
variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn);
procedure processApproverQuery2(selectClauseIn in varchar2,
approverNamesOut out nocopy ame_util.longStringList) as
tempApproverDisplayName ame_util.longStringType;
variableCur := ame_util.getQuery(selectClauseIn => selectClauseIn); -- may not be right, need to check on this);
update ame_approver_type_usages
set
last_updated_by = currentUserId,
last_update_date = processingDateIn,
last_update_login = currentUserId,
end_date = processingDateIn
where
action_type_id = actionTypeIdIn and
approver_type_id = approverTypeIdIn and
processingDateIn between start_date and
nvl(end_date - ame_util.oneSecond, processingDateIn) ;
/* loop through approverTypeIdsIn and update/end date
ame_approver_type_usages */
for i in 1..approverTypeIdsIn.count loop
removeApproverTypeUsage(actionTypeIdIn => actionTypeIdIn,
approverTypeIdIn => approverTypeIdsIn(i),
processingDateIn => processingDate);