[Home] [Help]
PACKAGE BODY: APPS.AME_RULE_USAGES_API
Source
1 PACKAGE BODY AME_RULE_USAGES_API AS
2 /* $Header: ameruapi.pkb 120.2 2005/10/14 04:13:49 ubhat noship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATED_BY out nocopy NUMBER,
6 X_LAST_UPDATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_LOGIN out nocopy NUMBER
8 ) is
9 begin
10 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
11 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATE_LOGIN := 0;
13 end OWNER_TO_WHO;
14
15 procedure KEY_TO_IDS (
16 X_RULE_ID in NUMBER,
17 X_APPLICATION_SHORT_NAME in VARCHAR2,
18 X_TRANSACTION_TYPE_ID in VARCHAR2,
19 X_ITEM_ID out nocopy NUMBER,
20 X_RULE_USAGE_COUNT out nocopy NUMBER
21 ) is
22 cursor CSR_GET_ITEM_ID
23 (
24 X_APPLICATION_SHORT_NAME in VARCHAR2,
25 X_TRANSACTION_TYPE_ID in VARCHAR2
26 ) is
27 select ACA.APPLICATION_ID
28 from AME_CALLING_APPS ACA,
29 FND_APPLICATION_VL FA
30 where FA.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
31 and nvl(ACA.TRANSACTION_TYPE_ID,'NULL') = nvl(X_TRANSACTION_TYPE_ID,'NULL')
32 and FA.APPLICATION_ID = ACA.FND_APPLICATION_ID
33 and ((ACA.START_DATE - (1/86400)) <= sysdate)
34 and (((ACA.END_DATE - (1/86400)) >= sysdate)
35 or (ACA.END_DATE is null));
36
37 cursor CSR_GET_RULE_USAGE_COUNT
38 (
39 X_ITEM_ID in NUMBER,
40 X_RULE_ID in NUMBER
41 ) is
42 select COUNT(*)
43 from AME_RULE_USAGES
44 where RULE_ID = X_RULE_ID
45 and ITEM_ID = X_ITEM_ID;
46
47 begin
48 open CSR_GET_ITEM_ID (
49 X_APPLICATION_SHORT_NAME,
50 X_TRANSACTION_TYPE_ID
51 );
52 fetch CSR_GET_ITEM_ID into X_ITEM_ID;
53 if (CSR_GET_ITEM_ID%notfound) then
54 X_ITEM_ID := null;
55 end if;
56 close CSR_GET_ITEM_ID;
57
58 if X_ITEM_ID is not null then
59 open CSR_GET_RULE_USAGE_COUNT (
60 X_ITEM_ID, X_RULE_ID
61 );
62 fetch CSR_GET_RULE_USAGE_COUNT into X_RULE_USAGE_COUNT;
63 close CSR_GET_RULE_USAGE_COUNT;
64 end if;
65
66 end KEY_TO_IDS;
67
68 function CALCULATE_USE_COUNT(X_ATTRIBUTE_ID ame_attribute_usages.attribute_id%type,
69 X_APPLICATION_ID ame_attribute_usages.application_id%type) return integer as
70 cursor RULE_CURSOR(X_APPLICATION_ID in integer) is
71 select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
72 from AME_RULES, AME_RULE_USAGES
73 where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
74 and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
75 and ((sysdate between AME_RULES.START_DATE
76 and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
77 or (sysdate < AME_RULES.START_DATE
78 and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
79 AME_RULES.START_DATE + (1/86400))))
80 and ((sysdate between AME_RULE_USAGES.START_DATE
81 and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
82 or (sysdate < AME_RULE_USAGES.START_DATE
83 and AME_RULE_USAGES.START_DATE < nvl(AME_RULE_USAGES.END_DATE,
84 AME_RULE_USAGES.START_DATE + (1/86400))));
85 RULE_COUNT integer;
86 TEMP_COUNT integer;
87 NEW_USE_COUNT integer;
88 begin
89 NEW_USE_COUNT := 0;
90 for TEMPRULE in RULE_CURSOR(X_APPLICATION_ID => X_APPLICATION_ID) loop
91 select count(*)
92 into TEMP_COUNT
93 from AME_CONDITIONS,
94 AME_CONDITION_USAGES
95 where
96 AME_CONDITIONS.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
97 AME_CONDITIONS.CONDITION_ID = AME_CONDITION_USAGES.CONDITION_ID and
98 AME_CONDITION_USAGES.RULE_ID = TEMPRULE.RULE_ID and
99 sysdate between AME_CONDITIONS.START_DATE and
100 nvl(AME_CONDITIONS.END_DATE - (1/86400), sysdate) and
101 ((sysdate between AME_CONDITION_USAGES.START_DATE and
102 nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate)) or
103 (sysdate < AME_CONDITION_USAGES.START_DATE and
104 AME_CONDITION_USAGES.START_DATE < nvl(AME_CONDITION_USAGES.END_DATE,
105 AME_CONDITION_USAGES.START_DATE + (1/86400))));
106 if(TEMP_COUNT > 0) then
107 NEW_USE_COUNT := NEW_USE_COUNT + 1;
108 else
109 if(TEMPRULE.ACTION_ID is null) then
110 -- action_id is already migrated from ame_rules to ame_action_usages
111 select count(*)
112 into TEMP_COUNT
113 from
114 AME_MANDATORY_ATTRIBUTES,
115 AME_ACTIONS,
116 AME_ACTION_USAGES
117 where
118 AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
119 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
120 AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID and
121 AME_ACTION_USAGES.RULE_ID = TEMPRULE.RULE_ID and
122 sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE and
123 nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate) and
124 sysdate between AME_ACTIONS.START_DATE and
125 nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate) and
126 ((sysdate between AME_ACTION_USAGES.START_DATE and
127 nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate)) or
128 (sysdate < AME_ACTION_USAGES.START_DATE and
129 AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,
130 AME_ACTION_USAGES.START_DATE + (1/86400))));
131 else
132 select count(*)
133 into TEMP_COUNT
134 from
135 AME_MANDATORY_ATTRIBUTES,
136 AME_ACTIONS,
137 AME_RULES
138 where
139 AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
140 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
141 AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID and
142 AME_RULES.RULE_ID = TEMPRULE.RULE_ID and
143 sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE and
144 nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate) and
145 sysdate between AME_ACTIONS.START_DATE and
146 nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate) and
147 ((sysdate between AME_RULES.START_DATE and
148 nvl(AME_RULES.END_DATE - (1/86400), sysdate)) or
149 (sysdate < AME_RULES.START_DATE and
150 AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
151 AME_RULES.START_DATE + (1/86400))));
152 end if;
153 if(TEMP_COUNT > 0) then
154 NEW_USE_COUNT := NEW_USE_COUNT + 1;
155 end if;
156 end if;
157 end loop;
158 return(NEW_USE_COUNT);
159 exception
160 when others then
161 ame_util.runtimeException('ame_attribute_usages_api',
162 'calculate_use_count',
163 sqlcode,
164 sqlerrm);
165 raise;
166 return(null);
167 end CALCULATE_USE_COUNT;
168
169 procedure CHANGE_ATTRIBUTE_USAGES_COUNT(X_RULE_ID ame_rule_usages.rule_id%type,
170 X_APPLICATION_ID ame_rule_usages.item_id%type) is
171 cursor GET_USED_ATTRIBUTES (X_RULE_ID ame_rule_usages.rule_id%type) is
172 select AME_CONDITIONS.ATTRIBUTE_ID
173 from AME_CONDITIONS,
174 AME_CONDITION_USAGES
175 where
176 AME_CONDITIONS.CONDITION_TYPE in (AME_UTIL.ORDINARYCONDITIONTYPE,
177 AME_UTIL.EXCEPTIONCONDITIONTYPE) and
178 AME_CONDITION_USAGES.RULE_ID = X_RULE_ID and
179 AME_CONDITION_USAGES.CONDITION_ID = AME_CONDITIONS.CONDITION_ID and
180 (AME_CONDITIONS.START_DATE <= sysdate and
181 (AME_CONDITIONS.END_DATE is null or sysdate < AME_CONDITIONS.END_DATE)) and
182 ((sysdate between AME_CONDITION_USAGES.START_DATE and
183 nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate)) or
184 (sysdate < AME_CONDITION_USAGES.START_DATE and
185 AME_CONDITION_USAGES.START_DATE < nvl(AME_CONDITION_USAGES.END_DATE,
186 AME_CONDITION_USAGES.START_DATE + (1/86400))))
187 union
188 select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
189 from AME_MANDATORY_ATTRIBUTES,
190 AME_ACTION_USAGES,
191 AME_ACTIONS
192 where
193 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
194 AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID and
195 AME_ACTION_USAGES.RULE_ID = X_RULE_ID and
196 (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
197 (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) and
198 ((sysdate between AME_ACTION_USAGES.START_DATE and
199 nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate)) or
200 (sysdate < AME_ACTION_USAGES.START_DATE and
201 AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,AME_ACTION_USAGES.START_DATE
202 + (1/86400)))) and
203 (AME_ACTIONS.START_DATE <= sysdate and
204 (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE))
205 union
206 select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
207 from AME_MANDATORY_ATTRIBUTES,
208 AME_RULES,
209 AME_ACTIONS
210 where
211 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
212 AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID and
213 AME_RULES.ACTION_ID is not null and
214 AME_RULES.RULE_ID = X_RULE_ID and
215 (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
216 (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) and
217 ((sysdate between AME_RULES.START_DATE and
218 nvl(AME_RULES.END_DATE - (1/86400), sysdate)) or
219 (sysdate < AME_RULES.START_DATE and
220 AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,AME_RULES.START_DATE
221 + (1/86400)))) and
222 (AME_ACTIONS.START_DATE <= sysdate and
223 (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE));
224 ATTRIBUTE_IDS_LIST ame_util.idList;
225 X_USE_COUNT ame_attribute_usages.use_count%type;
226 begin
227 for ATTRIBUTE_REC in GET_USED_ATTRIBUTES(X_RULE_ID => X_RULE_ID) loop
228 -- calculate use count
229 X_USE_COUNT := CALCULATE_USE_COUNT(ATTRIBUTE_REC.ATTRIBUTE_ID, X_APPLICATION_ID);
230 -- update ame_attribute_usages
231 update AME_ATTRIBUTE_USAGES
232 set USE_COUNT = X_USE_COUNT
233 where
234 ATTRIBUTE_ID = ATTRIBUTE_REC.ATTRIBUTE_ID and
235 APPLICATION_ID = X_APPLICATION_ID and
236 sysdate between START_DATE and
237 nvl(END_DATE - (1/86400), sysdate);
238 end loop;
239 end CHANGE_ATTRIBUTE_USAGES_COUNT;
240 procedure INSERT_ROW (
241 X_ITEM_ID in NUMBER,
242 X_RULE_ID in NUMBER,
243 X_APPROVER_CATEGORY in VARCHAR2,
244 X_CREATED_BY in NUMBER,
245 X_CREATION_DATE in DATE,
246 X_LAST_UPDATED_BY in NUMBER,
247 X_LAST_UPDATE_DATE in DATE,
248 X_LAST_UPDATE_LOGIN in NUMBER,
249 X_START_DATE in DATE,
250 X_OBJECT_VERSION_NUMBER in NUMBER)
251 is
252 begin
253
254 insert into AME_RULE_USAGES
255 (
256 ITEM_ID,
257 RULE_ID,
258 APPROVER_CATEGORY,
259 CREATED_BY,
260 CREATION_DATE,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_DATE,
263 LAST_UPDATE_LOGIN,
264 START_DATE,
265 END_DATE,
266 OBJECT_VERSION_NUMBER
267 ) values (
268 X_ITEM_ID,
269 X_RULE_ID,
270 X_APPROVER_CATEGORY,
271 X_CREATED_BY,
272 X_CREATION_DATE,
273 X_LAST_UPDATED_BY,
274 X_LAST_UPDATE_DATE,
275 X_LAST_UPDATE_LOGIN,
276 X_START_DATE,
277 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
278 X_OBJECT_VERSION_NUMBER);
279 CHANGE_ATTRIBUTE_USAGES_COUNT(X_RULE_ID => X_RULE_ID,
280 X_APPLICATION_ID => X_ITEM_ID);
281 end INSERT_ROW;
282
283 procedure DELETE_ROW (
284 X_ITEM_ID in NUMBER,
285 X_RULE_ID in NUMBER
286 ) is
287 begin
288 delete from AME_RULE_USAGES
289 where ITEM_ID = X_ITEM_ID
290 and RULE_ID = X_RULE_ID;
291
292 if (sql%notfound) then
293 raise no_data_found;
294 end if;
295 end DELETE_ROW;
296
297 procedure create_parallel_config
298 (x_rule_id in integer
299 ,x_application_id in integer
300 ) as
301 cursor rule_action_cursor is
302 select aa.action_id,
303 aa.action_type_id,
304 aat.name,
305 aa.parameter
306 from ame_rule_usages aru,
307 ame_action_usages aau,
308 ame_actions aa,
309 ame_action_types aat
310 where aru.rule_id = x_rule_id
311 and aru.item_id = x_application_id
312 and sysdate between aat.start_date and nvl(aat.end_date,sysdate)
313 and sysdate between aa.start_date and nvl(aa.end_date,sysdate)
314 and (sysdate between aru.start_date and nvl(aru.end_date,sysdate) or
318 and aru.rule_id = aau.rule_id
315 aru.start_date > sysdate and nvl(aru.end_date,aru.start_date + (1/86400)) < aru.start_date)
316 and (sysdate between aau.start_date and nvl(aau.end_date,sysdate) or
317 aau.start_date > sysdate and nvl(aau.end_date,aau.start_date + (1/86400)) < aau.start_date)
319 and aau.action_id = aa.action_id
320 and aa.action_type_id = aat.action_type_id;
321 cursor group_action_type_cursor(c_action_type_id integer) is
322 select null
323 from ame_action_types
324 where sysdate between start_date and nvl(end_date,sysdate)
325 and action_type_id = c_action_type_id
326 and name in ('pre-chain-of-authority approvals'
327 ,'post-chain-of-authority approvals'
328 ,'approval-group chain of authority');
329 x_action_type_id integer;
330 x_action_id integer;
331 x_action_type_name varchar2(100);
332 x_group_based_action varchar2 (10);
333 x_parameter varchar2(320);
334 x_approval_group_id integer;
335 x_dummy varchar2(10);
336 begin
337 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
341 open rule_action_cursor;
338 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null or to_number(AME_SEED_UTILITY.AME_INSTALLATION_LEVEL) < 2 then
339 return;
340 end if;
342 loop
343 fetch rule_action_cursor
344 into x_action_id,
345 x_action_type_id,
346 x_action_type_name,
347 x_parameter;
348 exit when rule_action_cursor%notfound;
349
350 open group_action_type_cursor(x_action_type_id);
351 fetch group_action_type_cursor into x_dummy;
352 if group_action_type_cursor%found then
353 x_group_based_action := 'Y';
354 x_approval_group_id := to_number(x_parameter);
355 else
356 x_group_based_action := 'N';
357 x_approval_group_id := null;
358 end if;
359 close group_action_type_cursor;
360
361 ame_seed_utility.create_parallel_config
362 (x_action_type_id
363 ,x_action_type_name
364 ,x_action_id
365 ,x_approval_group_id
366 );
367 end loop;
368 close rule_action_cursor;
369 end create_parallel_config;
370
371 procedure LOAD_ROW (
372 X_RULE_ID in VARCHAR2,
373 X_APPLICATION_SHORT_NAME in VARCHAR2,
374 X_TRANSACTION_TYPE_ID in VARCHAR2,
375 X_OWNER in VARCHAR2,
376 X_LAST_UPDATE_DATE in VARCHAR2
377 )
378 is
379 X_APPROVER_CATEGORY varchar2(1) := ame_util.approvalApproverCategory;
380 X_ITEM_ID NUMBER;
381 X_CREATED_BY NUMBER;
382 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
383 X_CURRENT_OWNER VARCHAR2(100);
384 X_LAST_UPDATED_BY NUMBER;
385 X_LAST_UPDATE_LOGIN NUMBER;
386 X_RULE_KEY VARCHAR2(100);
387 X_RULE_USAGE_COUNT NUMBER :=0;
388 begin
389 OWNER_TO_WHO (
390 X_OWNER,
391 X_CREATED_BY,
392 X_LAST_UPDATED_BY,
393 X_LAST_UPDATE_LOGIN
394 );
395 KEY_TO_IDS (
396 X_RULE_ID,
397 X_APPLICATION_SHORT_NAME,
398 X_TRANSACTION_TYPE_ID,
399 X_ITEM_ID,
400 X_RULE_USAGE_COUNT
401 );
402 -- the current row was not found insert a new row
403 if (X_RULE_USAGE_COUNT = 0)
404 and (X_ITEM_ID is not null) then
405 INSERT_ROW (
406 X_ITEM_ID,
407 X_RULE_ID,
408 X_APPROVER_CATEGORY,
409 X_CREATED_BY,
410 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
411 X_LAST_UPDATED_BY,
412 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
413 X_LAST_UPDATE_LOGIN,
414 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
415 1);
416 create_parallel_config
417 (x_rule_id => x_rule_id
418 ,x_application_id => x_item_id);
419 end if;
420 exception
421 when others then
422 ame_util.runtimeException('ame_rules_usages_api',
423 'load_row',
424 sqlcode,
425 sqlerrm);
426 raise;
427 end LOAD_ROW;
428 --
429 END AME_RULE_USAGES_API;