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