APPS.AME_RULE_API dependencies on AME_CONDITION_USAGES

Line 306: ,ame_condition_usages

302: cursor conditionCursor(ruleIdIn in integer) is
303: select ame_conditions.condition_id condition_id
304: ,ame_conditions.condition_type condition_type
305: from ame_conditions
306: ,ame_condition_usages
307: where ame_conditions.condition_id = ame_condition_usages.condition_id
308: and ame_condition_usages.rule_id = ruleIdIn
309: and (ame_conditions.start_date <= sysdate
310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))

Line 307: where ame_conditions.condition_id = ame_condition_usages.condition_id

303: select ame_conditions.condition_id condition_id
304: ,ame_conditions.condition_type condition_type
305: from ame_conditions
306: ,ame_condition_usages
307: where ame_conditions.condition_id = ame_condition_usages.condition_id
308: and ame_condition_usages.rule_id = ruleIdIn
309: and (ame_conditions.start_date <= sysdate
310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311: and ((sysdate between ame_condition_usages.start_date

Line 308: and ame_condition_usages.rule_id = ruleIdIn

304: ,ame_conditions.condition_type condition_type
305: from ame_conditions
306: ,ame_condition_usages
307: where ame_conditions.condition_id = ame_condition_usages.condition_id
308: and ame_condition_usages.rule_id = ruleIdIn
309: and (ame_conditions.start_date <= sysdate
310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311: and ((sysdate between ame_condition_usages.start_date
312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))

Line 311: and ((sysdate between ame_condition_usages.start_date

307: where ame_conditions.condition_id = ame_condition_usages.condition_id
308: and ame_condition_usages.rule_id = ruleIdIn
309: and (ame_conditions.start_date <= sysdate
310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311: and ((sysdate between ame_condition_usages.start_date
312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <

Line 312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))

308: and ame_condition_usages.rule_id = ruleIdIn
309: and (ame_conditions.start_date <= sysdate
310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311: and ((sysdate between ame_condition_usages.start_date
312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <
316: nvl(ame_condition_usages.end_date,

Line 314: (sysdate < ame_condition_usages.start_date

310: and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311: and ((sysdate between ame_condition_usages.start_date
312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <
316: nvl(ame_condition_usages.end_date,
317: ame_condition_usages.start_date + ame_util.oneSecond)))
318: order by condition_type;

Line 315: and ame_condition_usages.start_date <

311: and ((sysdate between ame_condition_usages.start_date
312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <
316: nvl(ame_condition_usages.end_date,
317: ame_condition_usages.start_date + ame_util.oneSecond)))
318: order by condition_type;
319: tempIndex integer;

Line 316: nvl(ame_condition_usages.end_date,

312: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <
316: nvl(ame_condition_usages.end_date,
317: ame_condition_usages.start_date + ame_util.oneSecond)))
318: order by condition_type;
319: tempIndex integer;
320: begin

Line 317: ame_condition_usages.start_date + ame_util.oneSecond)))

313: or
314: (sysdate < ame_condition_usages.start_date
315: and ame_condition_usages.start_date <
316: nvl(ame_condition_usages.end_date,
317: ame_condition_usages.start_date + ame_util.oneSecond)))
318: order by condition_type;
319: tempIndex integer;
320: begin
321: /*

Line 908: ,ame_condition_usages

904: from ame_attribute_usages
905: where attribute_id in (
906: select ame_conditions.attribute_id
907: from ame_conditions
908: ,ame_condition_usages
909: where ame_condition_usages.rule_id = p_rule_id
910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or

Line 909: where ame_condition_usages.rule_id = p_rule_id

905: where attribute_id in (
906: select ame_conditions.attribute_id
907: from ame_conditions
908: ,ame_condition_usages
909: where ame_condition_usages.rule_id = p_rule_id
910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date

Line 910: and (l_effective_date between ame_condition_usages.start_date

906: select ame_conditions.attribute_id
907: from ame_conditions
908: ,ame_condition_usages
909: where ame_condition_usages.rule_id = p_rule_id
910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,

Line 911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )

907: from ame_conditions
908: ,ame_condition_usages
909: where ame_condition_usages.rule_id = p_rule_id
910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915: ame_condition_usages.start_date + ame_util.oneSecond)))

Line 913: (l_effective_date < ame_condition_usages.start_date

909: where ame_condition_usages.rule_id = p_rule_id
910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915: ame_condition_usages.start_date + ame_util.oneSecond)))
916: and ame_condition_usages.condition_id = ame_conditions.condition_id
917: and l_effective_date between ame_conditions.start_date

Line 914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,

910: and (l_effective_date between ame_condition_usages.start_date
911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915: ame_condition_usages.start_date + ame_util.oneSecond)))
916: and ame_condition_usages.condition_id = ame_conditions.condition_id
917: and l_effective_date between ame_conditions.start_date
918: and nvl(ame_conditions.end_date - ame_util.oneSecond, l_effective_date)

Line 915: ame_condition_usages.start_date + ame_util.oneSecond)))

911: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915: ame_condition_usages.start_date + ame_util.oneSecond)))
916: and ame_condition_usages.condition_id = ame_conditions.condition_id
917: and l_effective_date between ame_conditions.start_date
918: and nvl(ame_conditions.end_date - ame_util.oneSecond, l_effective_date)
919: )

Line 916: and ame_condition_usages.condition_id = ame_conditions.condition_id

912: or
913: (l_effective_date < ame_condition_usages.start_date
914: and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915: ame_condition_usages.start_date + ame_util.oneSecond)))
916: and ame_condition_usages.condition_id = ame_conditions.condition_id
917: and l_effective_date between ame_conditions.start_date
918: and nvl(ame_conditions.end_date - ame_util.oneSecond, l_effective_date)
919: )
920: and application_id = p_application_id

Line 964: from ame_condition_usages

960: select condition_id
961: ,start_date
962: ,end_date
963: ,object_version_number
964: from ame_condition_usages
965: where rule_id = p_rule_id
966: and (l_effective_date between start_date
967: and nvl(end_date - ame_util.oneSecond, l_effective_date)
968: or

Line 1629: ,ame_condition_usages cnu

1625: --+
1626: select count(*)
1627: into lm_count
1628: from ame_rules rul
1629: ,ame_condition_usages cnu
1630: ,ame_conditions cnd
1631: where rul.rule_id = p_rule_id
1632: and cnu.rule_id = rul.rule_id
1633: and cnd.condition_id = cnu.condition_id

Line 1699: -- insert the row in ame_condition_usages

1695: l_cnu_start_date := l_rul_start_date;
1696: end if;
1697: l_cnu_end_date := l_rul_end_date;
1698: --
1699: -- insert the row in ame_condition_usages
1700: --
1701: ame_cnu_ins.ins(p_rule_id => p_rule_id
1702: ,p_condition_id => p_condition_id
1703: ,p_effective_date => l_effective_date

Line 2437: from ame_condition_usages

2433: select condition_id
2434: ,start_date
2435: ,end_date
2436: ,object_version_number
2437: from ame_condition_usages
2438: where rule_id = p_rule_id
2439: and ((l_effective_date between start_date
2440: and nvl(end_date - ame_util.oneSecond, l_effective_date))
2441: or

Line 2743: from ame_condition_usages

2739: select condition_id
2740: ,start_date
2741: ,end_date
2742: ,object_version_number
2743: from ame_condition_usages
2744: where rule_id = p_rule_id
2745: and ((l_effective_date between start_date
2746: and nvl(end_date - ame_util.oneSecond,l_effective_date))
2747: or

Line 2754: ,ame_condition_usages

2750: --+
2751: cursor getRuleConditions is
2752: select ame_conditions.attribute_id attribute_id
2753: from ame_conditions
2754: ,ame_condition_usages
2755: where ame_condition_usages.rule_id = p_rule_id
2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or

Line 2755: where ame_condition_usages.rule_id = p_rule_id

2751: cursor getRuleConditions is
2752: select ame_conditions.attribute_id attribute_id
2753: from ame_conditions
2754: ,ame_condition_usages
2755: where ame_condition_usages.rule_id = p_rule_id
2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date

Line 2756: and (l_effective_date between ame_condition_usages.start_date

2752: select ame_conditions.attribute_id attribute_id
2753: from ame_conditions
2754: ,ame_condition_usages
2755: where ame_condition_usages.rule_id = p_rule_id
2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <

Line 2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)

2753: from ame_conditions
2754: ,ame_condition_usages
2755: where ame_condition_usages.rule_id = p_rule_id
2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <
2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))

Line 2759: (l_effective_date < ame_condition_usages.start_date

2755: where ame_condition_usages.rule_id = p_rule_id
2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <
2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))
2762: and ame_condition_usages.condition_id = ame_conditions.condition_id
2763: and ame_conditions.condition_type <> ame_util.listModConditionType

Line 2760: and ame_condition_usages.start_date <

2756: and (l_effective_date between ame_condition_usages.start_date
2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <
2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))
2762: and ame_condition_usages.condition_id = ame_conditions.condition_id
2763: and ame_conditions.condition_type <> ame_util.listModConditionType
2764: and l_effective_date between ame_conditions.start_date

Line 2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))

2757: and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <
2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))
2762: and ame_condition_usages.condition_id = ame_conditions.condition_id
2763: and ame_conditions.condition_type <> ame_util.listModConditionType
2764: and l_effective_date between ame_conditions.start_date
2765: and nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date);

Line 2762: and ame_condition_usages.condition_id = ame_conditions.condition_id

2758: or
2759: (l_effective_date < ame_condition_usages.start_date
2760: and ame_condition_usages.start_date <
2761: nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))
2762: and ame_condition_usages.condition_id = ame_conditions.condition_id
2763: and ame_conditions.condition_type <> ame_util.listModConditionType
2764: and l_effective_date between ame_conditions.start_date
2765: and nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date);
2766: --+

Line 3019: from ame_conditions, ame_condition_usages

3015: --+
3016: cursor getConditionCount(p_condition_type in varchar2
3017: ,p_condition_id in integer) is
3018: select count(*)
3019: from ame_conditions, ame_condition_usages
3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or

Line 3020: where ame_condition_usages.condition_id <> p_condition_id

3016: cursor getConditionCount(p_condition_type in varchar2
3017: ,p_condition_id in integer) is
3018: select count(*)
3019: from ame_conditions, ame_condition_usages
3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and

Line 3021: and ame_condition_usages.rule_id = p_rule_id

3017: ,p_condition_id in integer) is
3018: select count(*)
3019: from ame_conditions, ame_condition_usages
3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <

Line 3022: and ( l_effective_date between ame_condition_usages.start_date and

3018: select count(*)
3019: from ame_conditions, ame_condition_usages
3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))

Line 3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or

3019: from ame_conditions, ame_condition_usages
3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027: and ame_condition_usages.condition_id = ame_conditions.condition_id

Line 3024: (l_effective_date < ame_condition_usages.start_date and

3020: where ame_condition_usages.condition_id <> p_condition_id
3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027: and ame_condition_usages.condition_id = ame_conditions.condition_id
3028: and ame_conditions.condition_type = p_condition_type

Line 3025: ame_condition_usages.start_date <

3021: and ame_condition_usages.rule_id = p_rule_id
3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027: and ame_condition_usages.condition_id = ame_conditions.condition_id
3028: and ame_conditions.condition_type = p_condition_type
3029: and l_effective_date between ame_conditions.start_date and

Line 3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))

3022: and ( l_effective_date between ame_condition_usages.start_date and
3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027: and ame_condition_usages.condition_id = ame_conditions.condition_id
3028: and ame_conditions.condition_type = p_condition_type
3029: and l_effective_date between ame_conditions.start_date and
3030: nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date );

Line 3027: and ame_condition_usages.condition_id = ame_conditions.condition_id

3023: nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024: (l_effective_date < ame_condition_usages.start_date and
3025: ame_condition_usages.start_date <
3026: nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027: and ame_condition_usages.condition_id = ame_conditions.condition_id
3028: and ame_conditions.condition_type = p_condition_type
3029: and l_effective_date between ame_conditions.start_date and
3030: nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date );
3031: --+

Line 3237: -- delete the row in ame_condition_usages

3233: open getConditionType(conditionIdIn => p_condition_id);
3234: fetch getConditionType into l_condition_type;
3235: close getConditionType;
3236: --
3237: -- delete the row in ame_condition_usages
3238: --
3239: ame_cnu_del.del(p_effective_date => l_effective_date
3240: ,p_datetrack_mode => hr_api.g_delete
3241: ,p_rule_id => p_rule_id

Line 3763: from ame_condition_usages cnu

3759: --+ Get the Old Condition ID
3760: --+
3761: select cnu.condition_id
3762: into l_old_condition_id
3763: from ame_condition_usages cnu
3764: ,ame_conditions cnd
3765: where cnu.rule_id = p_rule_id
3766: and cnu.condition_id = cnd.condition_id
3767: and cnd.condition_type = ame_util.listModConditionType

Line 3812: from ame_condition_usages

3808: ,end_date
3809: into l_cnu_object_version_number
3810: ,l_cnu_start_date
3811: ,l_cnu_end_date
3812: from ame_condition_usages
3813: where condition_id = l_old_condition_id
3814: and rule_id = p_rule_id
3815: and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
3816: or