The following lines contain the word 'select', 'insert', 'update' or 'delete':
select person_id, full_name
from per_all_people_f
where person_id = p_person_id
and sysdate between effective_start_date and effective_end_date;
select acty.name,
acty.procedure_name
from ame_action_types acty
where acty.created_by <> 1 and
not exists (select null
from ame_action_types
where procedure_name = acty.procedure_name and
created_by = 1 and
sysdate between start_date and
nvl(end_date - (ame_util.oneSecond), sysdate)) and
sysdate between acty.start_date and
nvl(acty.end_date - (ame_util.oneSecond), sysdate);
select acty.name action_type
,act.description action
,act.parameter source_attribute
from ame_actions act,
ame_action_types acty
where act.action_type_id = acty.action_type_id and
acty.name in (dynamicPreApprover, dynamicPostApprover) and
sysdate between act.start_date and nvl(act.end_date - (ame_util.oneSecond), sysdate) and
sysdate between acty.start_date and nvl(acty.end_date - (ame_util.oneSecond), sysdate);
select 'Y'
from all_tables
where table_name = 'AME_ACTION_USAGES'
and owner = table_owner;
select distinct oldappr.person_id person_id
from ame_temp_old_approver_lists oldappr
where oldappr.person_id is not null
and not exists (select null
from wf_roles wf
where wf.orig_system_id = oldappr.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct del.person_id person_id
from ame_temp_deletions del
where del.person_id is not null
and not exists (select null from wf_roles wf
where wf.orig_system_id = del.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct ins.person_id person_id
from ame_temp_insertions ins
where ins.person_id is not null
and not exists (select null from wf_roles wf
where wf.orig_system_id = ins.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct cond.condition_id
,cond.parameter_one
,cond.parameter_two person_id
from ame_conditions cond
where cond.parameter_one in ('any_approver_person_id','final_approver_person_id')
and not exists (select null from wf_roles wf
where wf.orig_system_id = cond.parameter_two
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct grpitems.approval_group_id
,parameter person_id
,grp.name
from ame_approval_group_items grpitems
,ame_approval_groups grp
where grpitems.parameter_name = ame_util.approverPersonId
and grp.approval_group_id = grpitems.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id)
and not exists (select null from wf_roles wf
where wf.orig_system_id = grpitems.parameter
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct grpmem.approval_group_id
,grpmem.parameter person_id
,grp.name
from ame_approval_group_members grpmem
,ame_approval_groups grp
where grpmem.parameter_name = ame_util.approverPersonId
and grp.approval_group_id = grpmem.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id)
and not exists (select null from wf_roles wf
where wf.orig_system_id = grpmem.parameter
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select substrb(config1.variable_value, 11, instrb(config1.variable_value,',')-11) person_id
from ame_config_vars config1
where config1.variable_name = 'adminApprover'
and config1.variable_value like 'person_id%'
and not exists (select null from ame_config_vars config2
where config2.rowid = config1.rowid
--and config2.variable_value like 'person_id:,user_id%'
and substrb(config2.variable_value,1,12) in ('person_id:,u','person_id:0,'))
and not exists (select null from wf_roles wf
where wf.orig_system_id = substrb(config1.variable_value, 11, instrb(config1.variable_value,',')-11)
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct action_id, substrb(parameter, instrb(parameter,':')+1) person_id, description
from ame_actions
where parameter like 'person_id:%'
and exists (select null
from ame_action_types
where name = ame_util.substitutionTypeName
and action_type_id = ame_actions.action_type_id
and rownum < 2)
and not exists (select null
from wf_roles wf
where wf.orig_system_id = substrb(ame_actions.parameter, instrb(ame_actions.parameter,':')+1)
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct oldappr.person_id person_id
from ame_temp_old_approver_lists oldappr
where oldappr.person_id is not null
and exists (select null
from wf_roles wf
where wf.orig_system_id = oldappr.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and name like 'AME_MIGRATION%'
and rownum < 2);
select distinct del.person_id person_id
from ame_temp_deletions del
where del.person_id is not null
and exists (select null
from wf_roles wf
where wf.orig_system_id = del.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and name like 'AME_MIGRATION%'
and rownum < 2);
select distinct ins.person_id person_id
from ame_temp_insertions ins
where ins.person_id is not null
and exists (select null
from wf_roles wf
where wf.orig_system_id = ins.person_id
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and name like 'AME_MIGRATION%'
and rownum < 2);
select distinct cond.condition_id
,cond.parameter_one
,substrb(cond.parameter_two, instrb(cond.parameter_two,'AME_MIGRATION_')+14) person_id
,cond.parameter_two
from ame_conditions cond
where cond.parameter_one in ('any_approver','final_approver')
and cond.parameter_two like 'AME_MIGRATION%'
and exists (select null
from wf_roles wf
where wf.name = cond.parameter_two
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct grpitems.approval_group_id
,substrb(parameter, instrb(parameter,'AME_MIGRATION_')+14) person_id
,parameter
,grp.name
from ame_approval_group_items grpitems
,ame_approval_groups grp
where grpitems.parameter_name = 'wf_roles_name'
and grp.approval_group_id = grpitems.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id)
and parameter like 'AME_MIGRATION%'
and exists (select null from wf_roles wf
where wf.name = grpitems.parameter
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct grpmem.approval_group_id
,substrb(grpmem.parameter,instrb(grpmem.parameter,'AME_MIGRATION_')+14) person_id
,grpmem.parameter
,grp.name
from ame_approval_group_members grpmem
,ame_approval_groups grp
where grpmem.parameter_name = 'wf_roles_name'
and grp.approval_group_id = grpmem.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id)
and grpmem.parameter like 'AME_MIGRATION%'
and exists (select null from wf_roles wf
where wf.name = grpmem.parameter
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select substrb(config1.variable_value, instrb(config1.variable_value,'AME_MIGRATION_')+14) person_id
from ame_config_vars config1
where config1.variable_name = 'adminApprover'
and config1.variable_value like 'AME_MIGRATION%'
and exists (select null from wf_roles wf
where name = config1.variable_value
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct action_id, substrb(parameter, instrb(parameter,'AME_MIGRATION_')+14) person_id, description
from ame_actions
where parameter like 'AME_MIGRATION%'
and exists (select null
from ame_action_types
where name = ame_util.substitutionTypeName
and action_type_id = ame_actions.action_type_id
and rownum < 2)
and exists (select null
from wf_roles wf
where wf.name = ame_actions.parameter
and wf.orig_system = 'PER'
and wf.status = 'ACTIVE'
and (wf.expiration_date is null or
sysdate < wf.expiration_date)
and rownum < 2);
select distinct oldappr.user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_temp_old_approver_lists oldappr
,fnd_user fnd
where oldappr.user_id is not null
and oldappr.person_id is null
and fnd.employee_id is not null
and oldappr.user_id = fnd.user_id;
select distinct del.user_id user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_temp_deletions del
,fnd_user fnd
where del.user_id is not null
and del.person_id is null
and fnd.employee_id is not null
and fnd.user_id = del.user_id;
select distinct ins.user_id user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_temp_insertions ins
,fnd_user fnd
where ins.user_id is not null
and ins.person_id is null
and fnd.employee_id is not null
and fnd.user_id = ins.user_id;
select distinct condition_id
,cond.parameter_two user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_conditions cond
,fnd_user fnd
where cond.parameter_one in ('any_approver_user_id','final_approver_user_id')
and fnd.employee_id is not null
and cond.parameter_two = to_char(fnd.user_id);
select distinct grp.name
,grpitems.parameter user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_approval_group_items grpitems
,ame_approval_groups grp
,fnd_user fnd
where grpitems.parameter_name = 'user_id'
and fnd.employee_id is not null
and grpitems.parameter = to_char(fnd.user_id)
and grp.approval_group_id = grpitems.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id);
select distinct grp.name
,grpmems.parameter user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_approval_group_members grpmems
,ame_approval_groups grp
,fnd_user fnd
where grpmems.parameter_name = 'user_id'
and fnd.employee_id is not null
and grpmems.parameter = to_char(fnd.user_id)
and grp.approval_group_id = grpmems.approval_group_id
and grp.start_date = (select max(start_date)
from ame_approval_groups
where approval_group_id = grp.approval_group_id);
select distinct substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 ) user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_config_vars config1
,fnd_user fnd
where config1.variable_name = 'adminApprover'
and config1.variable_value like 'person_id:%'
and lengthb(substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 )) > 0
and fnd.employee_id is not null
and substrb(config1.variable_value, instrb(config1.variable_value,':',-1)+1 ) = to_char(fnd.user_id);
select distinct action_id
,substrb(parameter, instrb(parameter,':')+1) user_id
,fnd.employee_id person_id
,fnd.user_name
from ame_actions
,fnd_user fnd
where parameter like 'user_id:%'
and fnd.employee_id is not null
and substrb(parameter, instrb(parameter,':')+1) = to_char(fnd.user_id)
and exists (select null
from ame_action_types
where name = ame_util.substitutionTypeName
and action_type_id = ame_actions.action_type_id
and rownum < 2);
,source => 'ame_temp_insertions'
,source_object => 'n/a'
,description => 'n/a'
,approvers => approversToBeMigrated);
,source => 'ame_temp_insertions'
,source_object => 'n/a'
,description => 'n/a'
,approvers => migratedApprovers);
,source_object => 'ame_temp_insertions'
,description => null
,approvers => invalidUsers);