57: from ame_rules
58: where upper(description) = upper(p_description)
59: and (p_rule_id is null or rule_id <> p_rule_id) /* allows for future start date */
60: and ((p_effective_date between start_date
61: and nvl(end_date - ame_util.oneSecond, p_effective_date))
62: or
63: (p_effective_date < start_date
64: and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
65: --+
60: and ((p_effective_date between start_date
61: and nvl(end_date - ame_util.oneSecond, p_effective_date))
62: or
63: (p_effective_date < start_date
64: and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
65: --+
66: if l_descriptionCount > 0 then
67: fnd_message.set_name('PER','AME_400206_RUL_DESC_IN_USE');
68: fnd_message.raise_error;
67: fnd_message.set_name('PER','AME_400206_RUL_DESC_IN_USE');
68: fnd_message.raise_error;
69: end if;
70: -- check length
71: if(ame_util.isArgumentTooLong(tableNameIn => 'ame_rules'
72: ,columnNameIn => 'description'
73: ,argumentIn => p_description)) then
74: fnd_message.set_name('PER','AME_400207_RUL_DESC_LONG');
75: fnd_message.raise_error;
130: from ame_item_classes
131: where
132: item_class_id = p_item_class_id and
133: p_effective_date between start_date and
134: nvl(end_date - ame_util.oneSecond, p_effective_date) ;
135: l_exists varchar2(1);
136: begin
137: if p_item_class_id is not null or p_rule_type <> ame_util.combinationRuleType then
138: open c_sel1;
133: p_effective_date between start_date and
134: nvl(end_date - ame_util.oneSecond, p_effective_date) ;
135: l_exists varchar2(1);
136: begin
137: if p_item_class_id is not null or p_rule_type <> ame_util.combinationRuleType then
138: open c_sel1;
139: fetch c_sel1 into l_exists;
140: if c_sel1%notfound then
141: close c_sel1;
203: -- {Start Of Comments}
204: --
205: -- Description:
206: -- Validates that the rule type is one of the following:
207: -- ame_util.combinationRuleType
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
204: --
205: -- Description:
206: -- Validates that the rule type is one of the following:
207: -- ame_util.combinationRuleType
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
205: -- Description:
206: -- Validates that the rule type is one of the following:
207: -- ame_util.combinationRuleType
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
206: -- Validates that the rule type is one of the following:
207: -- ame_util.combinationRuleType
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
214: -- ame_util.productionRuleType
207: -- ame_util.combinationRuleType
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
214: -- ame_util.productionRuleType
215: --
208: -- ame_util.authorityRuleType
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
214: -- ame_util.productionRuleType
215: --
216: -- Prerequisites:
209: -- ame_util.exceptionRuleType
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
214: -- ame_util.productionRuleType
215: --
216: -- Prerequisites:
217: -- None.
210: -- ame_util.listModRuleType
211: -- ame_util.substitutionRuleType
212: -- ame_util.preListGroupRuleType
213: -- ame_util.postListGroupRuleType
214: -- ame_util.productionRuleType
215: --
216: -- Prerequisites:
217: -- None.
218: --
240: p_object_version_number in number,
241: p_rule_type in ame_rules.rule_type%type) is
242: l_proc varchar2(72) := g_package||'chk_rule_type';
243: begin
244: if not (p_rule_type = ame_util.combinationRuleType or
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
241: p_rule_type in ame_rules.rule_type%type) is
242: l_proc varchar2(72) := g_package||'chk_rule_type';
243: begin
244: if not (p_rule_type = ame_util.combinationRuleType or
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
242: l_proc varchar2(72) := g_package||'chk_rule_type';
243: begin
244: if not (p_rule_type = ame_util.combinationRuleType or
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
243: begin
244: if not (p_rule_type = ame_util.combinationRuleType or
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
251: p_rule_type = ame_util.productionRuleType) then
244: if not (p_rule_type = ame_util.combinationRuleType or
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
251: p_rule_type = ame_util.productionRuleType) then
252: fnd_message.set_name('PER','AME_400468_RULE_TYPE_INVALID');
245: p_rule_type = ame_util.authorityRuleType or
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
251: p_rule_type = ame_util.productionRuleType) then
252: fnd_message.set_name('PER','AME_400468_RULE_TYPE_INVALID');
253: fnd_message.raise_error;
246: p_rule_type = ame_util.exceptionRuleType or
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
251: p_rule_type = ame_util.productionRuleType) then
252: fnd_message.set_name('PER','AME_400468_RULE_TYPE_INVALID');
253: fnd_message.raise_error;
254: end if;
247: p_rule_type = ame_util.listModRuleType or
248: p_rule_type = ame_util.substitutionRuleType or
249: p_rule_type = ame_util.preListGroupRuleType or
250: p_rule_type = ame_util.postListGroupRuleType or
251: p_rule_type = ame_util.productionRuleType) then
252: fnd_message.set_name('PER','AME_400468_RULE_TYPE_INVALID');
253: fnd_message.raise_error;
254: end if;
255: exception
305: l_item_class_name ame_item_classes.name%type;
306:
307: begin
308:
309: if (p_rule_type = ame_util.listModRuleType or
310: p_rule_type = ame_util.substitutionRuleType) then
311: if p_item_class_id is not null then
312: fnd_message.set_name('PER','AME_400722_INV_IC_LM_SUB_RULE');
313: fnd_message.raise_error;
306:
307: begin
308:
309: if (p_rule_type = ame_util.listModRuleType or
310: p_rule_type = ame_util.substitutionRuleType) then
311: if p_item_class_id is not null then
312: fnd_message.set_name('PER','AME_400722_INV_IC_LM_SUB_RULE');
313: fnd_message.raise_error;
314: end if;
311: if p_item_class_id is not null then
312: fnd_message.set_name('PER','AME_400722_INV_IC_LM_SUB_RULE');
313: fnd_message.raise_error;
314: end if;
315: elsif p_rule_type = ame_util.combinationRuleType then
316: null;
317: elsif p_item_class_id is null then
318: fnd_message.set_name('PER','AME_400472_INV_ITEM_CLASS');
319: fnd_message.raise_error;
429: from ame_rule_usages
430: where
431: rule_id = p_rule_id and
432: p_effective_date between start_date and
433: nvl(end_date - ame_util.oneSecond, p_effective_date) ;
434: cursor c_sel2 is
435: select null
436: from ame_condition_usages
437: where
436: from ame_condition_usages
437: where
438: rule_id = p_rule_id and
439: p_effective_date between start_date and
440: nvl(end_date - ame_util.oneSecond, p_effective_date) ;
441: cursor c_sel3 is
442: select null
443: from ame_action_usages
444: where
443: from ame_action_usages
444: where
445: rule_id = p_rule_id and
446: p_effective_date between start_date and
447: nvl(end_date - ame_util.oneSecond, p_effective_date) ;
448: l_exists varchar2(1);
449: begin
450: -- ame_rule_usages
451: open c_sel1;