[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* insert,update,delete,commit,rollback */
/* truncate,drop,grant,execute,locl,alter */
if instr(lower(temp_query_string),' insert ',1,1) > 0 then
fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
fnd_message.set_token('KEYWORD','INSERT');
if instr(lower(temp_query_string),' update ',1,1) > 0 then
fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
fnd_message.set_token('KEYWORD','UPDATE');
if instr(lower(temp_query_string),' delete ',1,1) > 0 then
fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
fnd_message.set_token('KEYWORD','DELETE');
select dynamic_description
from ame_action_types aty,
ame_actions act
where act.action_id = actionIdIn
and act.action_type_id = aty.action_type_id
and p_effective_date between act.start_date and nvl(act.end_date - (1/86400), p_effective_date)
and p_effective_date between aty.start_date and nvl(aty.end_date - (1/86400), p_effective_date);
select description
from ame_actions_vl
where action_id = actionIdIn
and p_effective_date between start_date and nvl(end_date - (1/86400), p_effective_date);
select description_query,
parameter,
parameter_two
from ame_action_types aty,
ame_actions act
where act.action_id = actionIdIn
and act.action_type_id = aty.action_type_id
and p_effective_date between act.start_date and nvl(act.end_date - (1/86400), p_effective_date)
and p_effective_date between aty.start_date and nvl(aty.end_date - (1/86400), p_effective_date);
select name into l_name
from ame_action_types
where action_type_id = p_action_type_id
and sysdate between start_date and nvl(end_date-(1/86400),sysdate)
and rownum < 2;
select 'Y' into l_return_value
from wf_roles wfroles
,ame_actions act
where wfroles.name = act.parameter
and wfroles.status = 'ACTIVE'
and (wfroles.expiration_date is null or
sysdate < wfroles.expiration_date)
and act.action_type_id = p_action_type_id
and act.action_id = p_action_id
and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate)
and rownum < 2;
select application_id into l_application_id
from ame_calling_apps_vl
where upper(trim(application_name)) = upper(trim(p_transaction_type))
and sysdate between start_date and nvl(end_date-(1/86400),sysdate);
select count(*)
into l_count
from ame_exceptions_log
where application_id = l_application_id ;
delete from ame_exceptions_log
where application_id = l_application_id ;
select count(*)
into l_count
from ame_exceptions_log
where transaction_id like (p_transaction_id || '%');
delete from ame_exceptions_log
where transaction_id like (p_transaction_id || '%');
select count(*)
into l_count
from ame_exceptions_log
where transaction_id like (p_transaction_id || '%')
and application_id = l_application_id ;
delete from ame_exceptions_log
where application_id = l_application_id
and transaction_id like (p_transaction_id || '%');
select attr.name
,attr.attribute_type
,attr.approver_type_id
,cond.parameter_one
,cond.parameter_two
,cond.parameter_three
,cond.include_lower_limit
,cond.include_upper_limit
,cond.condition_type
from ame_conditions cond
,(select name
,attribute_id
,attribute_type
,approver_type_id
from ame_attributes
where p_effective_date between start_date
and nvl(end_date,p_effective_date)
) attr
where cond.condition_id = p_condition_id
and cond.attribute_id = attr.attribute_id (+)
and p_effective_date between cond.start_date
and nvl(cond.end_date-(1/86400),p_effective_date);
select strval.string_value
from ame_string_values strval
where strval.condition_id = p_condition_id
and p_effective_date between strval.start_date
and nvl(strval.end_date-(1/86400),p_effective_date)
order by strval.string_value;
select lookup.meaning ||': '||wfroles.display_name
into l_string_value
from ame_approver_types appr
,fnd_lookups lookup
,wf_roles wfroles
where appr.approver_type_id = l_approver_type_id
and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
and lookup.lookup_code = appr.orig_system
and p_effective_date between appr.start_date
and nvl(appr.end_date-(1/86400),p_effective_date)
and wfroles.orig_system = appr.orig_system
and to_char(wfroles.orig_system_id) = l_parameter_one
and wfroles.status = 'ACTIVE'
and (wfroles.expiration_date is null or
p_effective_date < wfroles.expiration_date);
select lookup.meaning ||': '||wfroles.display_name
into l_string_value
from ame_approver_types appr
,fnd_lookups lookup
,wf_local_roles wfroles
where appr.approver_type_id = l_approver_type_id
and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
and lookup.lookup_code = appr.orig_system
and p_effective_date between appr.start_date
and nvl(appr.end_date-(1/86400),p_effective_date)
and wfroles.orig_system = appr.orig_system
and to_char(wfroles.orig_system_id) = l_parameter_one
and rownum < 2;
select lookup.meaning ||': '|| l_parameter_one
into l_string_value
from ame_approver_types appr
,fnd_lookups lookup
where appr.approver_type_id = l_approver_type_id
and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
and lookup.lookup_code = appr.orig_system
and p_effective_date between appr.start_date
and nvl(appr.end_date-(1/86400),p_effective_date)
and rownum < 2;
select act.name
from ame_action_types act,
ame_mandatory_attributes man
where Man.action_type_id = Act.action_type_id
and sysdate between act.start_date and nvl(act.end_date,sysdate)
and sysdate between man.start_date and nvl(man.end_date,sysdate)
and man.attribute_id = l_attribute_id
order by act.name;
select man.action_type_id
from ame_attributes atr,
ame_mandatory_attributes man
where atr.attribute_id = man.attribute_id
and atr.attribute_id = l_attribute_id
and sysdate between atr.start_date and nvl(atr.end_date-(1/84600),sysdate)
and sysdate between man.start_date and nvl(man.end_date-(1/84600),sysdate);
,p_select out nocopy varchar2) is
l_select varchar2(4000);
select column_type
from fnd_columns fcol
,fnd_tables ftab
where ftab.table_name = upper(TabNameIn)
and ftab.table_id = fcol.table_id
and fcol.column_name =upper(ColumnNameIn);
select validation_type,format_type
from fnd_flex_value_sets
where flex_value_set_id = p_valuesetIdIn;
p_select := 'AME_400818_INV_VALIDATION_TYP';
fnd_flex_val_api.get_independent_vset_select
(p_value_set_id => p_value_set_id
,x_select => l_select
,x_mapping_code => l_mapping_code
,x_success => l_success
);
l_before_from := substrb(l_select,1,instrb(lower(l_select),'from') - 1);
l_after_from := substrb(l_select,instrb(lower(l_select),'from')+4);
instrb(lower(l_before_from),'select')+6
)
),
fnd_global.local_chr(10),
'');
p_select := 'select '||l_column1||' VALUE, '||l_column2||' MEANING from'||l_after_from;
p_select := rtrim('select ' ||
l_v_r.table_info.value_column_name ||
' Value, ' ||
l_column2 ||
' Meaning from ' ||
l_v_r.table_info.table_name ||
' ' ||
l_whr
);
if(validate_query(p_select) <> 'Y' ) then
p_select := 'AME_400779_INV_VALUE_SET';
if(p_select <> 'AME_400779_INV_VALUE_SET' and l_format_type = 'N' and l_validation_type = 'F') then
open fnd_attr_data_type( TabNameIn =>l_v_r.table_info.table_name
,ColumnNameIn =>l_v_r.table_info.value_column_name);
p_select := 'AME_400819_INV_VAL_COL_TYPE';
p_select := 'AME_400779_INV_VALUE_SET';
function get_rule_last_update_date
(p_rule_id integer
,p_application_id integer
,p_usage_start_date date
) return date is
cursor c_last_update_date (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
select ar.last_update_date RULE_LUD,
ar.last_updated_by RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_rules ar
where ar.rule_id = c_rule_id
and ar.last_update_date in (select max(last_update_date)
from ame_rules art
where art.rule_id = c_rule_id)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
aru.last_update_date RULE_USAGE_LUD,
aru.last_updated_by RULE_USAGE_LUB,
aru.end_date RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_rule_usages aru
where aru.rule_id = c_rule_id
and aru.item_id = c_application_id
and aru.start_date = c_rule_usage_start_date
and aru.start_date < aru.end_date
and aru.last_update_date in (select max(last_update_date)
from ame_rule_usages arut
where arut.rule_id = c_rule_id
and arut.item_id = c_application_id
and arut.start_date = c_rule_usage_start_date
and arut.start_date < arut.end_date)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
acu.last_update_date CONDITION_USAGE_LUD,
acu.last_updated_by CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_condition_usages acu
where acu.rule_id = c_rule_id
and acu.last_update_date in (select max(last_update_date)
from ame_condition_usages acut
where acut.rule_id = c_rule_id)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
aau.last_update_date ACTION_USAGE_LUD,
aau.last_updated_by ACTION_USAGE_LUB
from ame_action_usages aau
where aau.rule_id = c_rule_id
and aau.last_update_date in (select max(last_update_date)
from ame_action_usages aaut
where aaut.rule_id = c_rule_id)
and rownum < 2;
latest_update_date date;
latest_update_by integer;
open c_last_update_date(p_rule_id,p_application_id,p_usage_start_date);
fetch c_last_update_date into rl_lud,
rl_lub,
ru_lud,
ru_lub,
ru_ed,
cu_lud,
cu_lub,
au_lud,
au_lub;
exit when c_last_update_date%notfound;
close c_last_update_date;
latest_update_date := rule_lud;
latest_update_by := rule_lub;
if condition_usage_lud > latest_update_date then
latest_update_date := condition_usage_lud;
latest_update_by := condition_usage_lub;
if action_usage_lud > latest_update_date then
latest_update_date := action_usage_lud;
latest_update_by := action_usage_lub;
if rule_usage_lud > latest_update_date then
latest_update_date := rule_usage_lud;
latest_update_by := rule_usage_lub;
if rule_usage_ed < latest_update_date then
latest_update_date := rule_usage_ed;
latest_update_by := rule_usage_lub;
return latest_update_date;
end get_rule_last_update_date;
function get_rule_last_updated_by
(p_rule_id integer
,p_application_id integer
,p_usage_start_date date
) return integer is
cursor c_last_update_date (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
select ar.last_update_date RULE_LUD,
ar.last_updated_by RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_rules ar
where ar.rule_id = c_rule_id
and ar.last_update_date in (select max(last_update_date)
from ame_rules art
where art.rule_id = c_rule_id)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
aru.last_update_date RULE_USAGE_LUD,
aru.last_updated_by RULE_USAGE_LUB,
aru.end_date RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_rule_usages aru
where aru.rule_id = c_rule_id
and aru.item_id = c_application_id
and aru.start_date = c_rule_usage_start_date
and aru.start_date < aru.end_date
and aru.last_update_date in (select max(last_update_date)
from ame_rule_usages arut
where arut.rule_id = c_rule_id
and arut.item_id = c_application_id
and arut.start_date = c_rule_usage_start_date
and arut.start_date < arut.end_date)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
acu.last_update_date CONDITION_USAGE_LUD,
acu.last_updated_by CONDITION_USAGE_LUB,
null ACTION_USAGE_LUD,
null ACTION_USAGE_LUB
from ame_condition_usages acu
where acu.rule_id = c_rule_id
and acu.last_update_date in (select max(last_update_date)
from ame_condition_usages acut
where acut.rule_id = c_rule_id)
and rownum < 2
union
select null RULE_LUD,
null RULE_LUB,
null RULE_USAGE_LUD,
null RULE_USAGE_LUB,
null RULE_USAGE_ED,
null CONDITION_USAGE_LUD,
null CONDITION_USAGE_LUB,
aau.last_update_date ACTION_USAGE_LUD,
aau.last_updated_by ACTION_USAGE_LUB
from ame_action_usages aau
where aau.rule_id = c_rule_id
and aau.last_update_date in (select max(last_update_date)
from ame_action_usages aaut
where aaut.rule_id = c_rule_id)
and rownum < 2;
latest_update_date date;
latest_update_by integer;
open c_last_update_date(p_rule_id,p_application_id,p_usage_start_date);
fetch c_last_update_date into rl_lud,
rl_lub,
ru_lud,
ru_lub,
ru_ed,
cu_lud,
cu_lub,
au_lud,
au_lub;
exit when c_last_update_date%notfound;
close c_last_update_date;
latest_update_date := rule_lud;
latest_update_by := rule_lub;
if condition_usage_lud > latest_update_date then
latest_update_date := condition_usage_lud;
latest_update_by := condition_usage_lub;
if action_usage_lud > latest_update_date then
latest_update_date := action_usage_lud;
latest_update_by := action_usage_lub;
if rule_usage_lud > latest_update_date then
latest_update_date := rule_usage_lud;
latest_update_by := rule_usage_lub;
if rule_usage_ed < latest_update_date then
latest_update_date := rule_usage_ed;
latest_update_by := rule_usage_lub;
return latest_update_by;
end get_rule_last_updated_by;
select rule_id
from ame_rules
where rule_id = ruleId
and (sysdate between start_date and nvl(end_date - (1/86400),sysdate) or
(start_date > sysdate and start_date < nvl(end_date,start_date + (1/86400)))
);
select count(rule_id)
from ame_rule_usages
where rule_id = ruleId
and item_id = applicationId
and start_date = usageStartDate
and start_date < end_date
and (sysdate between start_date and nvl(end_date - (1/86400),sysdate) or
(start_date > sysdate and start_date < nvl(end_date,start_date + (1/86400)))
);
return 'UpdateDisabled';
return 'UpdateDisabled';
return 'UpdateEnabled';
function get_rule_last_update_action
(p_rule_id integer
,p_application_id integer
,p_usage_start_date date
,p_usage_end_date date
) return varchar2 is
cursor c_row_count (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
select count(ar.rule_id) RULE_COUNT,
null CONDITION_USAGE_COUNT,
null ACTION_USAGE_COUNT
from ame_rules ar
where ar.rule_id = c_rule_id
and ar.last_update_date > (c_rule_usage_start_date + (1/86400))
union
select null RULE_COUNT,
count(acu.rule_id) CONDITION_USAGE_COUNT,
null ACTION_USAGE_COUNT
from ame_condition_usages acu
where acu.rule_id = c_rule_id
and acu.last_update_date > (c_rule_usage_start_date + (1/86400))
union
select null RULE_COUNT,
null CONDITION_USAGE_COUNT,
count(aau.rule_id) ACTION_USAGE_COUNT
from ame_action_usages aau
where aau.rule_id = c_rule_id
and aau.last_update_date > (c_rule_usage_start_date + (1/86400));
latest_action := 'DELETED';
latest_action := 'UPDATED';
end get_rule_last_update_action;
select count(*)
from ame_attribute_usages
where attribute_id = attributeIdIn
and application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate);
select count(*)
into temp_count
from ame_item_class_usages itu,
ame_attributes atr
where itu.item_class_id = atr.item_class_id
and itu.application_id = l_application_id
and atr.attribute_id = l_attribute_id
and sysdate between itu.start_date
and nvl(itu.end_date - (1/86400), sysdate)
and sysdate between atr.start_date
and nvl(atr.end_date - (1/86400), sysdate);
select atr.approver_type_id
into temp_count
from ame_attributes atr
where atr.attribute_id = l_attribute_id
and sysdate between atr.start_date
and nvl(atr.end_date - (1/86400), sysdate);
select count(*) into temp_count
from ame_attributes atr,
ame_approver_types apt
where atr.approver_type_id = apt.approver_type_id
and atr.attribute_id = l_attribute_id
and sysdate between atr.start_date
and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between apt.start_date
and nvl(apt.end_date - (1/86400), sysdate)
and apt.orig_system in ('FND_USR','PER');
select max(ar.end_date)
into l_rule_end_date
from ame_rules ar
where rule_id = p_rule_id;
select min(rule_id) - 1
into l_rule_id
from ame_rules;
select ame_rules_s.nextval
into l_rule_id
from sys.dual;
select min(condition_id) - 1
into l_condition_id
from ame_conditions;
select ame_conditions_s.nextval
into l_condition_id
from sys.dual;
select max(item_class_id) + 1
into l_item_class_id
from ame_item_classes;
select ame_approver_types_s.nextval into nextSequence from dual;
select count(*)
into countOfIds
from ame_approver_types
where approver_type_id = nextSequence;