[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rule1.description into dummy
from ame_rules rule1
,ame_rules rule2
where rule1.description = rule2.description
and rule1.end_date = endDateIn
and ((sysdate between rule2.start_date and nvl(rule2.end_date - (1/86400),sysdate))
or
(sysdate < rule2.start_date and rule2.start_date < nvl(rule2.end_date, rule2.start_date + (1/86400)))
);
select axu.rule_type into dummy
from ame_actions act
,ame_action_type_usages axu
where act.action_id = actionIdIn
and act.action_type_id = axu.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate);
select rule_type into dummy from ame_rules
where rule_id = ruleIdIn
and rownum < 2;
select 'Y'
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
,ame_action_type_usages axu
where rul.rule_id = ruleIdIn
and rul.rule_id = acu.rule_id
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and aty.action_type_id = axu.action_type_id
and axu.rule_type = 7
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);
select 'Y'
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
,ame_action_type_usages axu
where rul.rule_id = ruleIdIn
and rul.rule_id = acu.rule_id
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and aty.action_type_id = axu.action_type_id
and axu.rule_type = 7
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate)
and rul.end_date = endDateIn
and acu.end_date = rul.end_date;
select distinct atr.attribute_id, atr.name
from ame_attributes atr
,ame_conditions cond
,ame_condition_usages condu
,ame_rules rules
where cond.attribute_id = atr.attribute_id
and condu.condition_id = cond.condition_id
and condu.rule_id = rules.rule_id
and rules.rule_id = ruleIdIn
and cond.condition_type <> ame_util.listModConditionType
and atr.attribute_id not in (select attribute_id
from ame_attribute_usages
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate))
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between cond.start_date and nvl(cond.end_date - (1/86400), sysdate)
and rules.end_date = endDateIn
and condu.end_date = rules.end_date
union
select distinct atr.attribute_id, atr.name
from ame_attributes atr
,ame_action_usages acu
,ame_actions act
,ame_mandatory_attributes ama
,ame_rules rules
where ama.attribute_id = atr.attribute_id
and act.action_id = acu.action_id
and act.action_type_id = ama.action_type_id
and acu.rule_id = rules.rule_id
and rules.rule_id = ruleIdIn
and ama.attribute_id not in (select attribute_id
from ame_attribute_usages
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate))
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between ama.start_date and nvl(ama.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and rules.end_date = endDateIn
and acu.end_date = rules.end_date;
select name
from ame_item_classes itc
,ame_rules rul
where rul.item_class_id = itc.item_class_id
and rul.rule_id = ruleIdIn
and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
and rul.end_date = endDateIn
and itc.item_class_id not in (select item_class_id
from ame_item_class_usages itu
where itu.application_id = applicationIdIn
and sysdate between itu.start_date
and nvl(itu.end_date - (1/86400), sysdate));
select application_name
from ame_calling_apps
where application_id = applicationIdIn
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
select 'Y'
from ame_rules rul
,ame_item_classes itc
where rul.rule_id = ruleIdIn
and rul.item_class_id = itc.item_class_id
and itc.name <> ame_util.headerItemClassName
and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
and rul.end_date = endDateIn;
select distinct aty.action_type_id, aty.name
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.rule_id = acu.rule_id
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and aty.action_type_id not in (select atf.action_type_id
from ame_action_type_config atf
where atf.application_id = applicationIdIn
and sysdate between atf.start_date
and nvl(atf.end_date - (1/86400), sysdate))
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and rul.end_date = endDateIn
and acu.end_date = rul.end_date;
select apg.approval_group_id
,apg.name
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
,ame_approval_groups apg
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and rul.start_date < endDateIn
and rul.rule_id = acu.rule_id
and acu.end_date = rul.end_date
and acu.start_date >= rul.start_date
and acu.action_id = act.action_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and act.action_type_id = aty.action_type_id
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and to_char(apg.approval_group_id) = act.parameter
and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate)
and not exists (select null
from ame_approval_group_config agf
where agf.application_id = applicationIdIn
and agf.approval_group_id = apg.approval_group_id
and sysdate between agf.start_date and nvl(agf.end_date - (1/86400), sysdate))
and aty.name in ('pre-chain-of-authority approvals'
,'post-chain-of-authority approvals'
,'approval-group chain of authority');
select 'Y'
from ame_rules rul
,ame_conditions con
,ame_condition_usages cnu
,wf_roles wf
where rul.rule_id = ruleIdIn
and con.condition_id = cnu.condition_id
and con.condition_type = ame_util.listModConditionType
and cnu.rule_id = rul.rule_id
and con.parameter_two = wf.name
and wf.orig_system = 'POS'
and wf.status = 'ACTIVE'
and rul.end_date = endDateIn
and cnu.end_date = rul.end_date;
actionTypeIdList.delete;
select 'Y'
from ame_rule_usages
where rule_id = ruleIdIn
and item_id = applicationIdIn
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select distinct atr.attribute_id, atr.name
from ame_attributes atr
,ame_conditions cond
,ame_condition_usages condu
,ame_rules rules
where cond.attribute_id = atr.attribute_id
and condu.condition_id = cond.condition_id
and condu.rule_id = rules.rule_id
and rules.rule_id = ruleIdIn
and cond.condition_type <> ame_util.listModConditionType
and atr.attribute_id not in (select attribute_id
from ame_attribute_usages
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate))
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between cond.start_date and nvl(cond.end_date - (1/86400), sysdate)
and ((sysdate between rules.start_date and nvl(rules.end_date - (1/86400),sysdate))
or
(sysdate < rules.start_date and rules.start_date < nvl(rules.end_date, rules.start_date + (1/86400)))
)
and ((sysdate between condu.start_date and nvl(condu.end_date - (1/86400),sysdate))
or
(sysdate < condu.start_date and condu.start_date < nvl(condu.end_date, condu.start_date + (1/86400)))
)
union
select distinct atr.attribute_id, atr.name
from ame_attributes atr
,ame_action_usages acu
,ame_actions act
,ame_mandatory_attributes ama
,ame_rules rules
where ama.attribute_id = atr.attribute_id
and act.action_id = acu.action_id
and act.action_type_id = ama.action_type_id
and acu.rule_id = rules.rule_id
and rules.rule_id = ruleIdIn
and ama.attribute_id not in (select attribute_id
from ame_attribute_usages
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate))
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between ama.start_date and nvl(ama.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and ((sysdate between rules.start_date and nvl(rules.end_date - (1/86400),sysdate))
or
(sysdate < rules.start_date and rules.start_date < nvl(rules.end_date, rules.start_date + (1/86400)))
)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);
select name
from ame_item_classes itc
,ame_rules rul
where rul.item_class_id = itc.item_class_id
and rul.rule_id = ruleIdIn
and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and itc.item_class_id not in (select item_class_id
from ame_item_class_usages itu
where itu.application_id = applicationIdIn
and sysdate between itu.start_date
and nvl(itu.end_date - (1/86400), sysdate));
select application_name
from ame_calling_apps
where application_id = applicationIdIn
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
select 'Y'
from ame_rules rul
,ame_item_classes itc
where rul.rule_id = ruleIdIn
and rul.item_class_id = itc.item_class_id
and itc.name <> ame_util.headerItemClassName
and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
);
select distinct aty.action_type_id, aty.name
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.rule_id = acu.rule_id
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and aty.action_type_id not in (select atf.action_type_id
from ame_action_type_config atf
where atf.application_id = applicationIdIn
and sysdate between atf.start_date
and nvl(atf.end_date - (1/86400), sysdate))
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);
select apg.approval_group_id, apg.name
from ame_action_types aty
,ame_actions act
,ame_action_usages acu
,ame_rules rul
,ame_approval_groups apg
where rul.rule_id = ruleIdIn
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and rul.rule_id = acu.rule_id
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
)
and acu.action_id = act.action_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and act.action_type_id = aty.action_type_id
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and to_char(apg.approval_group_id) = act.parameter
and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate)
and not exists (select null
from ame_approval_group_config agf
where agf.application_id = applicationIdIn
and agf.approval_group_id = apg.approval_group_id
and sysdate between agf.start_date and nvl(agf.end_date - (1/86400), sysdate))
and aty.name in ('pre-chain-of-authority approvals'
,'post-chain-of-authority approvals'
,'approval-group chain of authority');
select 'Y'
from ame_rules rul
,ame_conditions con
,ame_condition_usages cnu
,wf_roles wf
where rul.rule_id = ruleIdIn
and con.condition_id = cnu.condition_id
and cnu.rule_id = rul.rule_id
and con.parameter_two = wf.name
and wf.orig_system = 'POS'
and wf.status = 'ACTIVE'
and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
);
actionTypeIdList.delete;
select count(*)
from ame_conditions con
,ame_condition_usages cnu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and cnu.rule_id = rul.rule_id
and cnu.end_date = rul.end_date
and con.condition_id = cnu.condition_id
and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate);
select count(*)
from ame_action_usages acu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and acu.rule_id = rul.rule_id
and acu.end_date = rul.end_date;
select count(*)
from ame_actions act
,ame_action_usages acu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and acu.rule_id = rul.rule_id
and acu.end_date = rul.end_date
and act.action_id = acu.action_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
select 'Y'
from ame_actions act
,ame_action_usages acu
,ame_rules rul
,ame_action_types aty
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and acu.rule_id = rul.rule_id
and acu.end_date = rul.end_date
and act.action_id = acu.action_id
and act.action_type_id = aty.action_type_id
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate);
select count(*)
from ame_condition_usages cnu
,ame_rules rul
where rul.rule_id = ruleIdIn
and rul.end_date = endDateIn
and cnu.rule_id = rul.rule_id
and cnu.end_date = rul.end_date;
select condition_id,created_by,creation_date
from ame_condition_usages
where rule_id = ruleIdIn
and end_date = endDateIn;
select action_id,created_by,creation_date
from ame_action_usages
where rule_id = ruleIdIn
and end_date = endDateIn;
insert into ame_rules
(rule_id
,rule_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,start_date
,end_date
,description
,rule_key
,item_class_id
,object_version_number)
select rule_id
,rule_type
,created_by
,creation_date
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,startDate
,endDate
,description
,rule_key
,item_class_id
,object_version_number+1 from ame_rules
where rule_id = ruleIdIn
and end_date = ruleEndDateIn;
insert into ame_condition_usages
(rule_id
,condition_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,start_date
,end_date
,object_version_number)
values(ruleIdIn
,condRec.condition_id
,condRec.created_by
,condRec.creation_date
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,startDate
,endDate
,1);
insert into ame_action_usages
(rule_id
,action_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,start_date
,end_date
,object_version_number)
values(ruleIdIn
,actionRec.action_id
,actionRec.created_by
,actionRec.creation_date
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,startDate
,endDate
,1);
select aca.application_id, aca.application_name
from ame_rule_usages rlu
,ame_calling_apps aca
where rlu.rule_id = ruleIdIn
and aca.application_id = rlu.item_id
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
and (sysdate between rlu.start_date
and nvl(rlu.end_date - ame_util.oneSecond, sysdate) or
(sysdate < rlu.start_date
and rlu.start_date < nvl(rlu.end_date, rlu.start_date + ame_util.oneSecond)));
select atr.attribute_id, atr.name
from ame_attributes atr
,ame_conditions con
where atr.attribute_id = con.attribute_id
and con.condition_id = conditionIdIn
and con.condition_type <> ame_util.listModConditionType
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate)
and not exists (select attribute_id
from ame_attribute_usages atu
where atu.application_id = applicationIdIn
and atu.attribute_id = atr.attribute_id
and sysdate between atu.start_date
and nvl(atu.end_date - (1/86400), sysdate));
select 'Y'
from ame_conditions con
,wf_roles wf
where con.condition_id = conditionIdIn
and con.condition_type = ame_util.listModConditionType
and con.parameter_two = wf.name
and wf.orig_system = 'POS'
and wf.status = 'ACTIVE'
and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate);
attributeIdList.delete;
attributeNameList.delete;
select aca.application_id, aca.application_name
from ame_rule_usages rlu
,ame_calling_apps aca
where rlu.rule_id = ruleIdIn
and aca.application_id = rlu.item_id
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
and (sysdate between rlu.start_date
and nvl(rlu.end_date - ame_util.oneSecond, sysdate) or
(sysdate < rlu.start_date
and rlu.start_date < nvl(rlu.end_date, rlu.start_date + ame_util.oneSecond)));
select aty.action_type_id, aty.name
from ame_action_types aty
,ame_actions act
where act.action_id = actionIdIn
and act.action_type_id = aty.action_type_id
and aty.action_type_id not in (select atf.action_type_id
from ame_action_type_config atf
where atf.application_id = applicationIdIn
and sysdate between atf.start_date
and nvl(atf.end_date - (1/86400), sysdate))
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
select distinct atr.attribute_id, atr.name
from ame_attributes atr
,ame_mandatory_attributes man
,ame_actions act
where act.action_id = actionIdIn
and act.action_type_id = man.action_type_id
and atr.attribute_id = man.attribute_id
and atr.attribute_id not in (select attribute_id
from ame_attribute_usages atu
where atu.application_id = applicationIdIn
and atu.attribute_id = atr.attribute_id
and sysdate between atu.start_date
and nvl(atu.end_date - (1/86400), sysdate))
and sysdate between man.start_date and nvl(man.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
select apg.approval_group_id, apg.name
from ame_action_types aty
,ame_actions act
,ame_approval_groups apg
where act.action_id = actionIdIn
and act.action_type_id = aty.action_type_id
and act.parameter = to_char(apg.approval_group_id)
and apg.approval_group_id not in (select agf.approval_group_id
from ame_approval_group_config agf
where agf.application_id = applicationIdIn
and sysdate between agf.start_date
and nvl(agf.end_date - (1/86400), sysdate))
and aty.name in (ame_util.preApprovalTypeName
,ame_util.postApprovalTypeName
,ame_util.groupChainApprovalTypeName)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate);
actionTypeIdList.delete;
actionTypeNameList.delete;
attributeIdList.delete;
attributeNameList.delete;
actionTypeIdList.delete;
actionTypeNameList.delete;
select min(start_date)
into p_rul_start_date
from ame_rule_usages
where rule_id = p_rule_id
and (sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate )
or
(sysdate < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select max(end_date)
into p_rul_end_date
from ame_rule_usages
where rule_id = p_rule_id
and (sysdate between start_date
and nvl(end_date - ame_util.oneSecond, sysdate )
or
(sysdate < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
l_update_rule boolean;
select action_id
,start_date
,end_date
,object_version_number
from ame_action_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select condition_id
,start_date
,end_date
,object_version_number
from ame_condition_usages
where rule_id = p_rule_id
and ((l_effective_date between start_date
and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date
and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
select start_date
,end_date
,object_version_number
from ame_rules
where rule_id = p_rule_id
and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
or
(l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
l_update_rule := false;
l_update_rule := true;
l_update_rule := true;
l_update_rule := true;
if l_update_rule then
--+
for tempActions in getActions(l_effective_date) loop
l_acu_object_version_number := tempActions.object_version_number;
,p_datetrack_mode => hr_api.g_update
,p_rule_id => p_rule_id
,p_action_id => tempActions.action_id
,p_object_version_number => l_acu_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_rule_id => p_rule_id
,p_condition_id => tempConditions.condition_id
,p_object_version_number => l_cnu_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
,p_datetrack_mode => hr_api.g_update
,p_rule_id => p_rule_id
,p_object_version_number => l_rul_object_version_number
,p_start_date => l_rul_start_date
,p_end_date => l_rul_end_date
);
select name
from ame_attributes
where attribute_id = p_attribute_id
and sysdate between start_date and nvl(end_date-(1/84600),sysdate);
select count(*)
from ame_rules
where rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select count(*)
from ame_actions
where action_id = p_action_id
and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
select count(*)
from ame_conditions
where condition_id = p_condition_id
and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
select count(*)
from ame_calling_apps
where application_id = p_application_id
and sysdate between start_date and nvl(end_date-(1/84600),sysdate);
select count(*)
from ame_rules rul
,ame_condition_usages cnu
where rul.rule_id = p_rule_id
and cnu.rule_id = rul.rule_id
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate);
select count(act.action_id)
from ame_actions act
,ame_action_type_config atf
,ame_action_types aty
where act.action_id = p_action_id
and atf.application_id = p_application_id
and act.action_type_id = atf.action_type_id
and act.action_type_id = aty.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and aty.name not in ('approval-group chain of authority'
,'pre-chain-of-authority approvals'
,'post-chain-of-authority approvals');
select count(*)
from ame_actions act
,ame_action_type_config atf
,ame_action_types aty
where act.action_id = p_action_id
and atf.application_id = p_application_id
and act.action_type_id = atf.action_type_id
and act.action_type_id = aty.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and aty.name in ('hr position'
,'hr position level');
select act.parameter
from ame_actions act
,ame_action_type_config atf
,ame_action_types aty
where act.action_id = p_action_id
and atf.application_id = p_application_id
and act.action_type_id = atf.action_type_id
and aty.action_type_id = act.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and aty.name in ('approval-group chain of authority'
,'pre-chain-of-authority approvals'
,'post-chain-of-authority approvals');
select acu.action_id
from ame_action_usages acu
,ame_rules rul
where rul.rule_id = acu.rule_id
and rul.rule_id = p_rule_id
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);
select cnu.condition_id
from ame_condition_usages cnu
,ame_rules rul
where rul.rule_id = cnu.rule_id
and rul.rule_id = p_rule_id
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
);
select count(*)
from ame_rules rul
,ame_condition_usages cnu
,ame_conditions cnd
where rul.rule_id = p_rule_id
and cnu.rule_id = rul.rule_id
and cnd.condition_id = cnu.condition_id
and cnd.condition_type = ame_util.listModConditionType
and rul.rule_type = 0
and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
);
select count(*)
from ame_conditions cnd
,ame_attribute_usages atu
where cnd.condition_id = p_condition_id
and cnd.condition_type <> ame_util.listModConditionType
and atu.application_id = p_application_id
and cnd.attribute_id = atu.attribute_id
and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate);
select count(*)
into lm_count
from ame_conditions
where condition_type = ame_util.listModConditionType
and condition_id = p_condition_id
and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
select parameter_two
into lm_param2
from ame_conditions
where condition_id = p_condition_id
and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
select count(*)
from ame_rules rul
,ame_action_usages acu
,ame_action_type_usages atyu
,ame_actions act
where act.action_id <> p_action_id
and rul.rule_id = p_rule_id
and rul.rule_id = acu.rule_id
and acu.action_id = act.action_id
and act.action_type_id = atyu.action_type_id
and (atyu.rule_type <> ame_util.productionRuleType
or rul.rule_type <> ame_util.productionRuleType)
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
)
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between atyu.start_date and nvl(atyu.end_date - (1/86400),sysdate);
select parameter_one
,parameter_two
from ame_conditions
where condition_id = p_condition_id
and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
select aty.name
from ame_actions act
,ame_action_types aty
where act.action_id = p_action_id
and aty.action_type_id = act.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate);
select count(*)
from ame_approval_group_config gpc
where gpc.approval_group_id = p_approval_group_id
and gpc.application_id = p_application_id
and sysdate between gpc.start_date and nvl(gpc.end_date - (1/86400),sysdate);
select count(*)
from wf_roles
where status = 'ACTIVE'
and nvl(expiration_date,sysdate) >= sysdate
and orig_system = 'PER'
and name = p_name;
select count(*)
from wf_roles
where status = 'ACTIVE'
and nvl(expiration_date,sysdate) >= sysdate
and orig_system = 'POS'
and name = p_name;
select distinct atr.item_class_id
from ame_rules rul
,ame_condition_usages cnu
,ame_attributes atr
,ame_conditions con
,ame_item_classes itc
where rul.rule_id = p_rule_id
and rul.rule_id = cnu.rule_id
and cnu.condition_id = con.condition_id
and con.attribute_id = atr.attribute_id
and atr.item_class_id = itc.item_class_id
and itc.name <> ame_util.headerItemClassName
and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
or
(sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
)
and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
or
(sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
)
and sysdate between con.start_date and nvl(con.end_date - (1/86400),sysdate)
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and sysdate between itc.start_date and nvl(itc.end_date - (1/86400),sysdate);
select condition_type
into l_con_type
from ame_conditions
where condition_id = p_condition_id
and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
select item_class_id
into l_header_item_class_id
from ame_item_classes
where name = ame_util.headerItemClassName
and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
select atr.item_class_id
into l_con_item_class_id
from ame_conditions con
,ame_attributes atr
where con.attribute_id = atr.attribute_id
and con.condition_id = p_condition_id
and sysdate between con.start_date and nvl(con.end_date - (1/86400),sysdate)
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate);
select nvl(item_class_id,l_header_item_class_id)
into l_item_class_id
from ame_rules
where rule_id = p_rule_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select count(*)
into l_count
from ame_condition_usages
where rule_id = p_rule_id
and condition_id = p_condition_id
and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
or
(sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
);
select aty.name
into l_aty_name
from ame_actions act
,ame_action_types aty
where act.action_id = p_action_id
and act.action_type_id = aty.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate);
select count(*)
into l_count
from ame_action_usages acu
,ame_action_types aty
,ame_actions act
where acu.rule_id = p_rule_id
and aty.name = 'nonfinal authority'
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);
select count(*)
into l_count
from ame_action_usages acu
,ame_action_types aty
,ame_actions act
where acu.rule_id = p_rule_id
and aty.name = 'final authority'
and acu.action_id = act.action_id
and act.action_type_id = aty.action_type_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
or
(sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
);