7: X_APPLICATION_ID ame_attribute_usages.application_id%type)
8: return integer as
9: cursor RULE_CURSOR
10: (X_APPLICATION_ID ame_attribute_usages.application_id%type) is
11: select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
12: from AME_RULES, AME_RULE_USAGES
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
8: return integer as
9: cursor RULE_CURSOR
10: (X_APPLICATION_ID ame_attribute_usages.application_id%type) is
11: select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
12: from AME_RULES, AME_RULE_USAGES
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
9: cursor RULE_CURSOR
10: (X_APPLICATION_ID ame_attribute_usages.application_id%type) is
11: select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
12: from AME_RULES, AME_RULE_USAGES
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
11: select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
12: from AME_RULES, AME_RULE_USAGES
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
18: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19: AME_RULES.START_DATE + (1/86400))))
12: from AME_RULES, AME_RULE_USAGES
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
18: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19: AME_RULES.START_DATE + (1/86400))))
20: and ((sysdate between AME_RULE_USAGES.START_DATE
13: where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
18: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19: AME_RULES.START_DATE + (1/86400))))
20: and ((sysdate between AME_RULE_USAGES.START_DATE
21: and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
14: and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
18: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19: AME_RULES.START_DATE + (1/86400))))
20: and ((sysdate between AME_RULE_USAGES.START_DATE
21: and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
22: or (sysdate < AME_RULE_USAGES.START_DATE
15: and ((sysdate between AME_RULES.START_DATE
16: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17: or (sysdate < AME_RULES.START_DATE
18: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19: AME_RULES.START_DATE + (1/86400))))
20: and ((sysdate between AME_RULE_USAGES.START_DATE
21: and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
22: or (sysdate < AME_RULE_USAGES.START_DATE
23: and AME_RULE_USAGES.START_DATE < nvl(AME_RULE_USAGES.END_DATE,
57: if(TEMP_COUNT > 0) then
58: NEW_USE_COUNT := NEW_USE_COUNT + 1;
59: else
60: if TEMPRULE.ACTION_ID is null then
61: -- action_id is already migrated from ame_rules to ame_action_usages
62: select count(*)
63: into TEMP_COUNT
64: from AME_MANDATORY_ATTRIBUTES,
65: AME_ACTIONS,
78: or (sysdate < AME_ACTION_USAGES.START_DATE
79: and AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,
80: AME_ACTION_USAGES.START_DATE + (1/86400))));
81: else
82: -- action_id is yet to be migrated from ame_rules to ame_action_usages
83: select count(*)
84: into TEMP_COUNT
85: from AME_MANDATORY_ATTRIBUTES,
86: AME_ACTIONS,
83: select count(*)
84: into TEMP_COUNT
85: from AME_MANDATORY_ATTRIBUTES,
86: AME_ACTIONS,
87: AME_RULES
88: where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
89: and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
90: AME_ACTIONS.ACTION_TYPE_ID
91: and AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID
87: AME_RULES
88: where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
89: and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
90: AME_ACTIONS.ACTION_TYPE_ID
91: and AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID
92: and AME_RULES.RULE_ID = TEMPRULE.RULE_ID
93: and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
94: and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
95: and sysdate between AME_ACTIONS.START_DATE
88: where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
89: and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
90: AME_ACTIONS.ACTION_TYPE_ID
91: and AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID
92: and AME_RULES.RULE_ID = TEMPRULE.RULE_ID
93: and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
94: and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
95: and sysdate between AME_ACTIONS.START_DATE
96: and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
93: and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
94: and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
95: and sysdate between AME_ACTIONS.START_DATE
96: and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
97: and ((sysdate between AME_RULES.START_DATE
98: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99: or (sysdate < AME_RULES.START_DATE
100: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101: AME_RULES.START_DATE + (1/86400))));
94: and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
95: and sysdate between AME_ACTIONS.START_DATE
96: and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
97: and ((sysdate between AME_RULES.START_DATE
98: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99: or (sysdate < AME_RULES.START_DATE
100: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101: AME_RULES.START_DATE + (1/86400))));
102: end if;
95: and sysdate between AME_ACTIONS.START_DATE
96: and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
97: and ((sysdate between AME_RULES.START_DATE
98: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99: or (sysdate < AME_RULES.START_DATE
100: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101: AME_RULES.START_DATE + (1/86400))));
102: end if;
103: if(TEMP_COUNT > 0) then
96: and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
97: and ((sysdate between AME_RULES.START_DATE
98: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99: or (sysdate < AME_RULES.START_DATE
100: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101: AME_RULES.START_DATE + (1/86400))));
102: end if;
103: if(TEMP_COUNT > 0) then
104: NEW_USE_COUNT := NEW_USE_COUNT + 1;
97: and ((sysdate between AME_RULES.START_DATE
98: and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99: or (sysdate < AME_RULES.START_DATE
100: and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101: AME_RULES.START_DATE + (1/86400))));
102: end if;
103: if(TEMP_COUNT > 0) then
104: NEW_USE_COUNT := NEW_USE_COUNT + 1;
105: end if;