[Home] [Help]
PACKAGE BODY: APPS.AME_ADMIN_PKG
Source
1 package body ame_admin_pkg as
2 /* $Header: ameoadmi.pkb 120.2 2006/12/26 13:28:43 avarri noship $ */
3 function arePrioritiesDisabled(applicationIdIn in integer) return boolean is
4 begin
5 if(ame_util.getConfigVar(variableNameIn => ame_util.rulePriorityModesConfigVar,
6 applicationIdIn => applicationIdIn) =
7 'disabled:disabled:disabled:disabled:disabled:disabled:disabled:disabled') then
8 return(true);
9 end if;
10 return(false);
11 exception
12 when others then
13 rollback;
14 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
15 routineNameIn => 'arePrioritiesDisabled',
16 exceptionNumberIn => sqlcode,
17 exceptionStringIn => sqlerrm);
18 raise;
19 return(true);
20 end arePrioritiesDisabled;
21 function canHaveItemAttributes(applicationIdIn in integer,
22 itemClassIdIn in integer) return boolean is
23 itemCount integer;
24 itemIdQuery ame_calling_apps.line_item_id_query%type;
25 begin
26 select item_id_query
27 into itemIdQuery
28 from
29 ame_item_class_usages
30 where
31 application_id = applicationIdIn and
32 item_class_id = itemClassIdIn and
33 sysdate between start_date and
34 nvl(end_date - ame_util.oneSecond, sysdate);
35 if itemIdQuery is null then
36 return(false);
37 end if;
38 return(true);
39 exception
40 when others then
41 rollback;
42 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
43 routineNameIn => 'canHaveItemAttributes',
44 exceptionNumberIn => sqlcode,
45 exceptionStringIn => sqlerrm);
46 raise;
47 return(false);
48 end canHaveItemAttributes;
49 function getChildVersionStartDate(itemClassIdIn in integer,
50 applicationIdIn in integer) return varchar2 as
51 startDate date;
52 stringStartDate varchar2(50);
53 begin
54 select start_date
55 into startDate
56 from ame_item_class_usages
57 where
58 item_class_id = itemClassIdIn and
59 application_id = applicationIdIn and
60 sysdate between start_date and
61 nvl(end_date - ame_util.oneSecond, sysdate) ;
62 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
63 return(stringStartDate);
64 exception
65 when others then
66 rollback;
67 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
68 routineNameIn => 'getChildVersionStartDate',
69 exceptionNumberIn => sqlcode,
70 exceptionStringIn => '(item class ID ' ||
71 itemClassIdIn||
72 ') ' ||
73 sqlerrm);
74 raise;
75 return(null);
76 end getChildVersionStartDate;
77 /*
78 AME_STRIPING
79 function doesStripeSetIdExist(stripeSetIdIn in integer) return boolean as
80 stripeSetCount integer;
81 begin
82 select count(*)
83 into stripeSetCount
84 from ame_stripe_sets
85 where
86 stripe_set_id = stripeSetIdIn and
87 (start_date <= sysdate and
88 (end_date is null or sysdate < end_date));
89 if(stripeSetCount > 0) then
90 return(true);
91 end if;
92 return(false);
93 exception
94 when others then
95 rollback;
96 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
97 routineNameIn => 'doesStripeSetIdExist',
98 exceptionNumberIn => sqlcode,
99 exceptionStringIn => sqlerrm);
100 raise;
101 return(false);
102 end doesStripeSetIdExist;
103 */
104 function getEqualityConditionId(attributeIdIn in integer,
105 stringValueIn in varchar2) return integer as
106 conditionId ame_conditions.condition_id%type;
107 begin
108 select ame_conditions.condition_id
109 into conditionId
110 from ame_conditions,
111 ame_string_values
112 where
113 ame_conditions.condition_id = ame_string_values.condition_id and
114 ame_conditions.condition_type = ame_util.ordinaryConditionType and
115 ame_conditions.attribute_id = attributeIdIn and
116 ame_string_values.string_value = stringValueIn and
117 sysdate between ame_conditions.start_date and
118 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
119 sysdate between ame_string_values.start_date and
120 nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate) ;
121 return(conditionId);
122 exception
123 when no_data_found then
124 return(null);
125 when others then
126 rollback;
127 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
128 routineNameIn => 'getEqualityConditionId',
129 exceptionNumberIn => sqlcode,
130 exceptionStringIn => sqlerrm);
131 raise;
132 return(null);
133 end getEqualityConditionId;
134 function getApplicationId(fndAppIdIn in integer,
135 transactionTypeIdIn in varchar2) return integer as
136 appId integer;
137 begin
138 select application_id
139 into appId
140 from ame_calling_apps
141 where
142 fnd_application_id = fndAppIdIn and
143 ((transaction_type_id is null and transactionTypeIdIn is null) or
144 transaction_type_id = transactionTypeIdIn) and
145 sysdate between start_date and
146 nvl(end_date - ame_util.oneSecond, sysdate) ;
147 return(appId);
148 exception
149 when others then
150 rollback;
151 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
152 routineNameIn => 'getApplicationId',
153 exceptionNumberIn => sqlcode,
154 exceptionStringIn => sqlerrm);
155 raise;
156 return(null);
157 end getApplicationId;
158 function getApplicationIdByName(nameIn in varchar2) return integer as
159 appId integer;
160 begin
161 select application_id
162 into appId
163 from ame_calling_apps
164 where
165 application_name = nameIn and
166 sysdate between start_date and
167 nvl(end_date - ame_util.oneSecond, sysdate);
168 return(appId);
169 exception
170 when others then
171 rollback;
172 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
173 routineNameIn => 'getApplicationIdByName',
174 exceptionNumberIn => sqlcode,
175 exceptionStringIn => sqlerrm);
176 raise;
177 return(null);
178 end getApplicationIdByName;
179 function getApplicationName(applicationIdIn in integer) return varchar2 as
180 applicationName ame_calling_apps.application_name%type;
181 begin
182 select application_name
183 into applicationName
184 from ame_calling_apps
185 where
186 application_id = applicationIdIn and
187 sysdate between start_date and
188 nvl(end_date - ame_util.oneSecond, sysdate) ;
189 return(applicationName);
190 exception
191 when others then
192 rollback;
193 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
194 routineNameIn => 'getApplicationName',
195 exceptionNumberIn => sqlcode,
196 exceptionStringIn => sqlerrm);
197 raise;
198 return(null);
199 end getApplicationName;
200 /*
201 AME_STRIPING
202 function getAttributeDisplayValue(attributeValueIn in varchar2) return varchar2 as
203 attributeValue ame_stripe_sets.value_1%type;
204 begin
205 if(substrb(attributeValueIn, 1,5) = ame_util.stripeWildcard) THEN
206 return('[any]');
207 end if;
208 return(attributeValueIn);
209 exception
210 when others then
211 rollback;
212 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
213 routineNameIn => 'getAttributeDisplayValue',
214 exceptionNumberIn => sqlcode,
215 exceptionStringIn => sqlerrm);
216 raise;
217 return(null);
218 end getAttributeDisplayValue;
219 */
220 function getAttributeQuery(selectClauseIn in varchar2) return ame_util.queryCursor as
221 queryCursor ame_util.queryCursor;
222 sqlStatement varchar2(4000);
223 begin
224 sqlStatement := selectClauseIn;
225 open queryCursor for sqlStatement;
226 return queryCursor;
227 exception
228 when others then
229 rollback;
230 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
231 routineNameIn => 'getAttributeQuery',
232 exceptionNumberIn => sqlcode,
233 exceptionStringIn => sqlerrm);
234 raise;
235 return(queryCursor);
236 end getAttributeQuery;
237 /*
238 AME_STRIPING
239 function getAttributeStripingUseCount(applicationIdIn in integer,
240 attributeIdIn in integer) return integer as
241 ruleCount integer;
242 begin
243 select count(*)
244 into ruleCount
245 from ame_rules,
246 ame_rule_usages,
247 ame_rule_stripe_sets
248 where
249 ame_rule_usages.item_id = applicationIdIn and
250 ame_rules.rule_id = ame_rule_stripe_sets.rule_id and
251 ame_rules.rule_id = ame_rule_usages.rule_id and
252 (ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date) and
253 (ame_rule_stripe_sets.end_date is null or sysdate < ame_rule_stripe_sets.end_date) and
254 (ame_rules.end_date is null or sysdate < ame_rules.end_date);
255 return(ruleCount);
256 exception
257 when others then
258 rollback;
259 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
260 routineNameIn => 'getAttributeStripingUseCount',
261 exceptionNumberIn => sqlcode,
262 exceptionStringIn => sqlerrm);
263 raise;
264 return(null);
265 end getAttributeStripingUseCount;
266 */
267 function getFndAppDescription(fndAppIdIn in integer) return varchar2 as
268 description fnd_application_vl.application_name%type;
269 begin
270 select ltrim(fnd_application_vl.application_name)
271 into description
272 from fnd_application_vl
273 where application_id = fndAppIdIn;
274 return(description);
275 exception
276 when others then
277 rollback;
278 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
279 routineNameIn => 'getFndAppDescription',
280 exceptionNumberIn => sqlcode,
281 exceptionStringIn => sqlerrm);
282 raise;
283 return(null);
284 end getFndAppDescription;
285 function getFndAppDescription1(applicationIdIn in integer) return varchar2 as
286 description fnd_application_vl.application_name%type;
287 begin
288 select ltrim(fnd_application_vl.application_name)
289 into description
290 from
291 fnd_application_vl,
292 ame_calling_apps
293 where
294 fnd_application_vl.application_id = ame_calling_apps.fnd_application_id and
295 ame_calling_apps.application_id = applicationIdIn and
296 sysdate between ame_calling_apps.start_date and
297 nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
298 order by fnd_application_vl.application_name;
299 return(description);
300 exception
301 when others then
302 rollback;
303 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
304 routineNameIn => 'getFndAppDescription1',
305 exceptionNumberIn => sqlcode,
306 exceptionStringIn => sqlerrm);
307 raise;
308 return(null);
309 end getFndAppDescription1;
310 function getFndApplicationId(applicationIdIn in integer) return integer as
311 fndApplicationId ame_calling_apps.fnd_application_id%type;
312 begin
313 select fnd_application_id
314 into fndApplicationId
315 from ame_calling_apps
316 where
317 application_id = applicationIdIn and
318 sysdate between start_date and
319 nvl(end_date - ame_util.oneSecond, sysdate) ;
320 return fndApplicationId;
321 exception
322 when others then
323 rollback;
324 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
325 routineNameIn => 'getFndApplicationId',
326 exceptionNumberIn => sqlcode,
327 exceptionStringIn => sqlerrm);
328 raise;
329 return(null);
330 end getFndApplicationId;
331 function getItemClassCount return integer as
332 itemClassCount integer;
333 begin
334 select count(*)
335 into itemClassCount
336 from ame_item_classes
337 where
338 sysdate between start_date and
339 nvl(end_date - ame_util.oneSecond, sysdate);
340 return(itemClassCount);
341 exception
342 when others then
343 rollback;
344 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
345 routineNameIn => 'getItemClassCount',
346 exceptionNumberIn => sqlcode,
347 exceptionStringIn => sqlerrm);
348 raise;
349 return(null);
350 end getItemClassCount;
351 function getItemClassIdByName(itemClassNameIn in varchar2) return integer as
352 itemId integer;
353 begin
354 select item_class_id
355 into itemId
356 from ame_item_classes
357 where
358 upper(name) = upper(itemClassNameIn) and
359 sysdate between start_date and
360 nvl(end_date - ame_util.oneSecond, sysdate);
361 return(itemId);
362 exception
363 when others then
364 rollback;
365 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
366 routineNameIn => 'getItemClassIdByName',
367 exceptionNumberIn => sqlcode,
368 exceptionStringIn => '(item name ' ||
369 itemClassNameIn||
370 ') ' ||
371 sqlerrm);
372 raise;
373 return(null);
374 end getItemClassIdByName;
375 function getItemClassIdQuery(itemClassIdIn in integer,
376 applicationIdIn in integer) return varchar2 as
377 itemIdQuery ame_util.longestStringType;
378 begin
379 select item_id_query
380 into itemIdQuery
381 from ame_item_class_usages
382 where
383 item_class_id = itemClassIdIn and
384 application_id = applicationIdIn and
385 sysdate between start_date and
386 nvl(end_date - ame_util.oneSecond, sysdate);
387 return(itemIdQuery);
388 exception
389 when others then
390 rollback;
391 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
392 routineNameIn => 'getItemClassIdQuery',
393 exceptionNumberIn => sqlcode,
394 exceptionStringIn => sqlerrm);
395 raise;
396 return(null);
397 end getItemClassIdQuery;
398 function getItemClassName(itemClassIdIn in integer) return varchar2 as
399 itemName ame_item_classes.name%type;
400 begin
401 select name
402 into itemName
403 from ame_item_classes
404 where
405 item_class_id = itemClassIdIn and
406 sysdate between start_date and
407 nvl(end_date - ame_util.oneSecond, sysdate) ;
408 return(itemName);
409 exception
410 when others then
411 rollback;
412 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
413 routineNameIn => 'getItemClassName',
414 exceptionNumberIn => sqlcode,
415 exceptionStringIn => '(item class ID ' ||
416 itemClassIdIn||
417 ') ' ||
418 sqlerrm);
419 raise;
420 return(null);
421 end getItemClassName;
422 function getLineItemQueryString(applicationIdIn in integer) return varchar2 as
423 lineItemQueryString ame_calling_apps.line_item_id_query%type;
424 begin
425 select line_item_id_query
426 into lineItemQueryString
427 from ame_calling_apps
428 where
429 application_id = applicationIdIn and
430 sysdate between start_date and
431 nvl(end_date - ame_util.oneSecond, sysdate) ;
432 return lineItemQueryString;
433 exception
434 when no_data_found then
435 return(null);
436 when others then
437 rollback;
438 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
439 routineNameIn => 'getLineItemQueryString',
440 exceptionNumberIn => sqlcode,
441 exceptionStringIn => sqlerrm);
442 raise;
443 return(null);
444 end getLineItemQueryString;
445 function getItemClassMaxOrderNumber(applicationIdIn in integer) return integer as
446 orderNumber integer;
447 begin
448 select nvl(max(item_class_order_number), 0)
449 into orderNumber
450 from ame_item_class_usages
451 where
452 application_id = applicationIdIn and
453 sysdate between start_date and
454 nvl(end_date - ame_util.oneSecond, sysdate) ;
455 return(orderNumber);
456 exception
457 when others then
458 rollback;
459 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
460 routineNameIn => 'getItemClassMaxOrderNumber',
461 exceptionNumberIn => sqlcode,
462 exceptionStringIn => sqlerrm);
463 raise;
464 return(null);
465 end getItemClassMaxOrderNumber;
466 function getItemClassOrderNumber(itemClassIdIn in integer,
467 applicationIdIn in integer) return integer as
468 itemClassOrderNumber integer;
469 begin
470 select item_class_order_number
471 into itemClassOrderNumber
472 from ame_item_class_usages
473 where
474 item_class_id = itemClassIdIn and
475 application_id = applicationIdIn and
476 sysdate between start_date and
477 nvl(end_date - ame_util.oneSecond, sysdate);
478 return(itemClassOrderNumber);
479 exception
480 when others then
481 rollback;
482 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
483 routineNameIn => 'getItemClassOrderNumber',
484 exceptionNumberIn => sqlcode,
485 exceptionStringIn => sqlerrm);
486 raise;
487 return(null);
488 end getItemClassOrderNumber;
489 function getItemClassTransTypeCount(applicationIdIn in integer) return integer as
490 tempCount integer;
491 begin
492 select count(*)
493 into tempCount
494 from ame_item_class_usages
495 where
496 application_id = applicationIdIn and
497 sysdate between start_date and
498 nvl(end_date - ame_util.oneSecond, sysdate);
499 return(tempCount);
500 exception
501 when others then
502 rollback;
503 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
504 routineNameIn => 'getItemClassTransTypeCount',
505 exceptionNumberIn => sqlcode,
506 exceptionStringIn => sqlerrm);
507 raise;
508 return(null);
509 end getItemClassTransTypeCount;
510 function getItemClassParMode(itemClassIdIn in integer,
511 applicationIdIn in integer) return varchar2 as
512 itemClassParMode ame_util.charType;
513 begin
514 select item_class_par_mode
515 into itemClassParMode
516 from ame_item_class_usages
517 where
518 item_class_id = itemClassIdIn and
519 application_id = applicationIdIn and
520 sysdate between start_date and
521 nvl(end_date - ame_util.oneSecond, sysdate);
522 return(itemClassParMode);
523 exception
524 when others then
525 rollback;
526 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
527 routineNameIn => 'getItemClassParMode',
528 exceptionNumberIn => sqlcode,
529 exceptionStringIn => sqlerrm);
530 raise;
531 return(null);
532 end getItemClassParMode;
533 function getParentVersionStartDate(itemClassIdIn in integer) return varchar2 as
534 startDate date;
535 stringStartDate varchar2(50);
536 begin
537 select start_date
538 into startDate
539 from ame_item_classes
540 where
541 item_class_id = itemClassIdIn and
542 sysdate between start_date and
543 nvl(end_date - ame_util.oneSecond, sysdate) ;
544 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
545 return(stringStartDate);
546 exception
547 when others then
548 rollback;
549 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
550 routineNameIn => 'getParentVersionStartDate',
551 exceptionNumberIn => sqlcode,
552 exceptionStringIn => '(item class ID ' ||
553 itemClassIdIn||
554 ') ' ||
555 sqlerrm);
556 raise;
557 return(null);
558 end getParentVersionStartDate;
559 function getItemClassSublistMode(itemClassIdIn in integer,
560 applicationIdIn in integer) return varchar2 as
561 itemClassSubListMode ame_util.charType;
562 begin
563 select item_class_sublist_mode
564 into itemClassSublistMode
565 from ame_item_class_usages
566 where
567 item_class_id = itemClassIdIn and
568 application_id = applicationIdIn and
569 sysdate between start_date and
570 nvl(end_date - ame_util.oneSecond, sysdate);
571 return(itemClassSublistMode);
572 exception
573 when others then
574 rollback;
575 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
576 routineNameIn => 'getItemClassSublistMode',
577 exceptionNumberIn => sqlcode,
578 exceptionStringIn => sqlerrm);
579 raise;
580 return(null);
581 end getItemClassSublistMode;
582 function getSubordinateItemClassId(applicationIdIn in integer) return integer as
583 itemId integer;
584 begin
585 select ame_item_classes.item_class_id
586 into itemId
587 from ame_item_classes,
588 ame_item_class_usages
589 where
590 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
591 ame_item_class_usages.application_id = applicationIdIn and
592 ame_item_classes.name <> ame_util.headerItemClassName and
593 sysdate between ame_item_classes.start_date and
594 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
595 sysdate between ame_item_class_usages.start_date and
596 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate);
597 return(itemId);
598 exception
599 when others then
600 rollback;
601 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
602 routineNameIn => 'getSubordinateItemClassId',
603 exceptionNumberIn => sqlcode,
604 exceptionStringIn => sqlerrm);
605 raise;
606 return(null);
607 end getSubordinateItemClassId;
608 function getTransactionTypeId(applicationIdIn in integer) return varchar2 as
609 tempTransactionTypeId ame_calling_apps.transaction_type_id%type;
610 begin
611 select transaction_type_id
612 into tempTransactionTypeId
613 from ame_calling_apps
614 where application_id = applicationIdIn and
615 sysdate between start_date and
616 nvl(end_date - ame_util.oneSecond, sysdate) ;
617 return tempTransactionTypeId;
618 exception
619 when no_data_found then
620 return(null);
621 when others then
622 rollback;
623 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
624 routineNameIn => 'getTransactionTypeId',
625 exceptionNumberIn => sqlcode,
626 exceptionStringIn => sqlerrm);
627 raise;
628 return(null);
629 end getTransactionTypeId;
630 function getVersionStartDate(applicationIdIn in integer) return varchar2 as
631 startDate date;
632 stringStartDate varchar2(50);
633 begin
634 select start_date
635 into startDate
636 from ame_calling_apps
637 where
638 application_id = applicationIdIn and
639 sysdate between start_date and
640 nvl(end_date - ame_util.oneSecond, sysdate) ;
641 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
642 return(stringStartDate);
643 exception
644 when others then
645 rollback;
646 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
647 routineNameIn => 'getVersionStartDate',
648 exceptionNumberIn => sqlcode,
649 exceptionStringIn => sqlerrm);
650 raise;
651 return(null);
652 end getVersionStartDate;
653 /*
654 AME_STRIPING
655 function getVersionStartDate2(applicationIdIn in integer,
656 stripeSetIdIn in integer) return varchar2 as
657 startDate date;
658 stringStartDate varchar2(50);
659 begin
660 select start_date
661 into startDate
662 from ame_stripe_sets
663 where
664 application_id = applicationIdIn and
665 stripe_set_id = stripeSetIdIn and
666 (start_date <= sysdate and
667 (end_date is null or sysdate < end_date));
668 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
669 return(stringStartDate);
670 exception
671 when others then
672 rollback;
673 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
674 routineNameIn => 'getVersionStartDate2',
675 exceptionNumberIn => sqlcode,
676 exceptionStringIn => sqlerrm);
677 raise;
678 return(null);
679 end getVersionStartDate2;
680 */
681 function hasLineItemAttributes(applicationIdIn in integer) return boolean is
682 lineItemCount integer;
683 begin
684 select count(*)
685 into lineItemCount
686 from
687 ame_attribute_usages,
688 ame_attributes
689 where
690 ame_attributes.line_item = ame_util.booleanTrue and
691 ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
692 ame_attribute_usages.application_id = applicationIdIn and
693 sysdate between ame_attribute_usages.start_date and
694 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
695 sysdate between ame_attributes.start_date and
696 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) ;
697 if(lineItemCount > 0) then
698 return(true);
699 end if;
700 return(false);
701 exception
702 when others then
703 rollback;
704 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
705 routineNameIn => 'hasLineItemAttributes',
706 exceptionNumberIn => sqlcode,
707 exceptionStringIn => sqlerrm);
708 raise;
709 return(false);
710 end hasLineItemAttributes;
711 /*
712 AME_STRIPING
713 function hasRuleStripes(applicationIdIn in integer) return boolean as
714 stripeCount integer;
715 begin
716 select count(*)
717 into stripeCount
718 from ame_stripe_sets
719 where
720 application_id = applicationIdIn and
721 (start_date <= sysdate and
722 (end_date is null or sysdate < end_date));
723 if(stripeCount > 0) then
724 return(true);
725 end if;
726 return(false);
727 exception
728 when others then
729 rollback;
730 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
731 routineNameIn => 'hasRuleStripes',
732 exceptionNumberIn => sqlcode,
733 exceptionStringIn => sqlerrm);
734 raise;
735 return(true);
736 end hasRuleStripes;
737 */
738 function icInUseByAttributeUsage(itemClassIdIn in integer,
739 applicationIdIn in integer) return boolean is
740 attributeUsageCount integer;
741 begin
742 select count(*)
743 into attributeUsageCount
744 from
745 ame_attribute_usages,
746 ame_attributes
747 where
748 ame_attribute_usages.attribute_id = ame_attributes.attribute_id and
749 ame_attribute_usages.application_id = applicationIdIn and
750 ame_attributes.item_class_id = itemClassIdIn and
751 sysdate between ame_attribute_usages.start_date and
752 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
753 sysdate between ame_attributes.start_date and
754 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
755 if(attributeUsageCount > 0) then
756 return(true);
757 end if;
758 return(false);
759 exception
760 when others then
761 rollback;
762 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
763 routineNameIn => 'icInUseByAttributeUsage',
764 exceptionNumberIn => sqlcode,
765 exceptionStringIn => sqlerrm);
766 raise;
767 return(false);
768 end icInUseByAttributeUsage;
769 function icInUseByRuleUsage(itemClassIdIn in integer,
770 applicationIdIn in integer) return boolean is
771 ruleUsageCount integer;
772 begin
773 select count(*)
774 into ruleUsageCount
775 from
776 ame_rule_usages,
777 ame_rules
778 where
779 ame_rule_usages.rule_id = ame_rules.rule_id and
780 ame_rule_usages.item_id = applicationIdIn and
781 ame_rules.item_class_id = itemClassIdIn and
782 sysdate between ame_rule_usages.start_date and
783 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate) and
784 sysdate between ame_rules.start_date and
785 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate);
786 if(ruleUsageCount > 0) then
787 return(true);
788 end if;
789 return(false);
790 exception
791 when others then
792 rollback;
793 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
794 routineNameIn => 'icInUseByRuleUsage',
795 exceptionNumberIn => sqlcode,
796 exceptionStringIn => sqlerrm);
797 raise;
798 return(false);
799 end icInUseByRuleUsage;
800 function inputToCanonStaticCurUsage(attributeIdIn in integer,
801 applicationIdIn in integer,
802 queryStringIn varchar2) return varchar2 as
803 amount ame_util.attributeValueType;
804 conversionType ame_util.attributeValueType;
805 convTypeException exception;
806 curCodeException exception;
807 currencyCode ame_util.attributeValueType;
808 errorCode integer;
809 errorMessage ame_util.longestStringType;
810 begin
811 /*
812 The ame_engine.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
813 but it doesn't validate the individual values, or convert the amount to canonical format.
814 */
815 ame_util.parseStaticCurAttValue(applicationIdIn => applicationIdIn,
816 attributeIdIn => attributeIdIn,
817 attributeValueIn => queryStringIn,
818 localErrorIn => false,
819 amountOut => amount,
820 currencyOut => currencyCode,
821 conversionTypeOut => conversionType);
822 /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
823 amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
824 currencyCodeIn => currencyCode);
825 if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
826 raise curCodeException;
827 end if;
828 if not ame_util.isConversionTypeValid(conversionTypeIn => conversionType) then
829 raise convTypeException;
830 end if;
831 return(amount || ',' || currencyCode || ',' || conversionType);
832 exception
833 when convTypeException then
834 rollback;
835 errorCode := -20001;
836 errorMessage :=
837 ame_util.getMessage(applicationShortNameIn => 'PER',
838 messageNameIn => 'AME_400150_ATT_STA_CONV_INV');
839 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
840 routineNameIn => 'inputToCanonStaticCurUsage',
841 exceptionNumberIn => errorCode,
842 exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
843 raise_application_error(errorCode,
844 errorMessage);
845 return(null);
846 when curCodeException then
847 rollback;
848 errorCode := -20001;
849 errorMessage :=
850 ame_util.getMessage(applicationShortNameIn => 'PER',
851 messageNameIn => 'AME_400151_ATT_STA_CURR_INV');
852 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
853 routineNameIn => 'inputToCanonStaticCurUsage',
854 exceptionNumberIn => errorCode,
855 exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
856 raise_application_error(errorCode,
857 errorMessage);
858 return(null);
859 when others then
860 rollback;
861 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
862 routineNameIn => 'inputToCanonStaticCurUsage',
863 exceptionNumberIn => sqlcode,
864 exceptionStringIn => sqlerrm); /* Runtime code doesn't validate input. */
865 raise;
866 return(null);
867 end inputToCanonStaticCurUsage;
868 function isApplicationActive(applicationIdIn in integer) return boolean as
869 appCount integer;
870 begin
871 select count(*)
872 into appCount
873 from ame_calling_apps
874 where
875 application_id = applicationIdIn and
876 sysdate between start_date and
877 nvl(end_date - ame_util.oneSecond, sysdate);
878 if(appCount > 0) then
879 return(true);
880 end if;
881 return(false);
882 exception
883 when others then
884 rollback;
885 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
886 routineNameIn => 'isApplicationActive',
887 exceptionNumberIn => sqlcode,
888 exceptionStringIn => sqlerrm);
889 raise;
890 return(null);
891 end isApplicationActive;
892 function isInUseByApplication(itemClassIdIn in integer,
893 applicationIdIn in integer) return boolean as
894 useCount integer;
895 begin
896 select count(*)
897 into useCount
898 from ame_item_class_usages
899 where
900 item_class_id = itemClassIdIn and
901 application_id = applicationIdIn and
902 sysdate between start_date and
903 nvl(end_date - ame_util.oneSecond, sysdate);
904 if(useCount > 0) then
905 return(true);
906 end if;
907 return(false);
908 exception
909 when no_data_found then
910 rollback;
911 return(false);
912 when others then
913 rollback;
914 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
915 routineNameIn => 'isInUseByApplication',
916 exceptionNumberIn => sqlcode,
917 exceptionStringIn => '(item class ID ' ||
918 itemClassIdIn||
919 ') ' ||
920 sqlerrm);
921 raise;
922 return(true); /* conservative: avoids allowing deletion if might still be in use */
923 end isInUseByApplication;
924 function isSeeded(applicationIdIn in integer) return boolean as
925 createdByValue integer;
926 begin
927 select created_by
928 into createdByValue
929 from ame_calling_apps
930 where application_id = applicationIdIn and
931 sysdate between start_date and
932 nvl(end_date - ame_util.oneSecond, sysdate) ;
933 if(createdByValue = 1) then
934 return(true);
935 end if;
936 return(false);
937 exception
938 when others then
939 rollback;
940 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
941 routineNameIn => 'isSeeded',
942 exceptionNumberIn => sqlcode,
943 exceptionStringIn => sqlerrm);
944 raise;
945 return(true);
946 end isSeeded;
947 /*
948 AME_STRIPING
949 function isStripingOn(applicationIdIn in integer) return boolean as
950 isStripingOn varchar2(20);
951 begin
952 select variable_value
953 into isStripingOn
954 from ame_config_vars
955 where application_id = applicationIdIn and
956 variable_name = ame_util.useRuleStripingConfigVar and
957 (start_date <= sysdate and
958 (end_date is null or sysdate < end_date));
959 if(isStripingOn = ame_util.yes) then
960 return(true);
961 end if;
962 return(false);
963 exception
964 when no_data_found then
965 return(false);
966 when others then
967 rollback;
968 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
969 routineNameIn => 'isStripingOn',
970 exceptionNumberIn => sqlcode,
971 exceptionStringIn => sqlerrm);
972 raise;
973 return(false);
974 end isStripingOn;
975 */
976 /*
977 AME_STRIPING
978 function newStripeSet(applicationIdIn in integer,
979 attributeValuesIn in ame_util.stringList,
980 commitIn in boolean default false) return integer as
981 attributeValues ame_util.stringList;
982 attributeValuesCount integer;
983 currentUserId integer;
984 stripeSetId ame_stripe_sets.stripe_set_id%type;
985 tempIndex integer;
986 begin
987 */
988 /* need to set the rest of the attributeValues to null so the call to
989 getStripeSetId below does not hit the no_data_found exception */
990 /*
991 attributeValuesCount := attributeValuesIn.count;
992 for i in 1..5 loop
993 attributeValues(i) := null;
994 end loop;
995 for i in 1..attributeValuesCount loop
996 attributeValues(i) := attributeValuesIn(i);
997 end loop;
998 stripeSetId := getStripeSetId(applicationIdIn => applicationIdIn,
999 attributeValuesIn => attributeValues);
1000 if(stripeSetId is null) then
1001 currentUserId := ame_util.getCurrentUserId;
1002 select max(stripe_set_id + 1) into stripeSetId from ame_stripe_sets;
1003 insert into ame_stripe_sets(application_id,
1004 stripe_set_id,
1005 value_1,
1006 value_2,
1007 value_3,
1008 value_4,
1009 value_5,
1010 created_by,
1011 creation_date,
1012 last_updated_by,
1013 last_update_date,
1014 last_update_login,
1015 start_date,
1016 end_date,
1017 security_group_id)
1018 values(applicationIdIn,
1019 stripeSetId,
1020 attributeValuesIn(1),
1021 attributeValuesIn(2),
1022 attributeValuesIn(3),
1023 attributeValuesIn(4),
1024 attributeValuesIn(5),
1025 currentUserId,
1026 sysdate,
1027 currentUserId,
1028 sysdate,
1029 currentUserId,
1030 sysdate,
1031 null,
1032 null);
1033 end if;
1034 if(commitIn) then
1035 commit;
1036 end if;
1037 return(stripeSetId);
1038 exception
1039 when others then
1040 rollback;
1041 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1042 routineNameIn => 'newStripeSet',
1043 exceptionNumberIn => sqlcode,
1044 exceptionStringIn => sqlerrm);
1045 raise;
1046 return(null);
1047 end newStripeSet;
1048 */
1049 /*
1050 AME_STRIPING
1051 function getStripeSetId(applicationIdIn in integer,
1052 attributeValuesIn in ame_util.stringList) return integer as
1053 stripeSetId ame_stripe_sets.stripe_set_id%type;
1054 begin
1055 select stripe_set_id
1056 into stripeSetId
1057 from ame_stripe_sets
1058 where
1059 application_id = applicationIdIn and
1060 stripe_set_id <> 0 and
1061 ((value_1 is null and attributeValuesIn(1) is null) or value_1 = attributeValuesIn(1)) and
1062 ((value_2 is null and attributeValuesIn(2) is null) or value_2 = attributeValuesIn(2)) and
1063 ((value_3 is null and attributeValuesIn(3) is null) or value_3 = attributeValuesIn(3)) and
1064 ((value_4 is null and attributeValuesIn(4) is null) or value_4 = attributeValuesIn(4)) and
1065 ((value_5 is null and attributeValuesIn(5) is null) or value_5 = attributeValuesIn(5)) and
1066 (start_date <= sysdate and
1067 (end_date is null or sysdate < end_date));
1068 return(stripeSetId);
1069 exception
1070 when no_data_found then
1071 return(null);
1072 when others then
1073 rollback;
1074 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1075 routineNameIn => 'getStripeSetId',
1076 exceptionNumberIn => sqlcode,
1077 exceptionStringIn => sqlerrm);
1078 raise;
1079 return(null);
1080 end getStripeSetId;
1081 */
1082 function itemClassNameExists(itemClassNameIn in varchar2) return boolean as
1083 tempCount integer;
1084 begin
1085 select count(*)
1086 into tempCount
1087 from ame_item_classes
1088 where
1089 upper(name) = upper(itemClassNameIn) and
1090 sysdate between start_date and
1091 nvl(end_date - ame_util.oneSecond, sysdate) ;
1092 if(tempCount > 0) then
1093 return(true);
1094 end if;
1095 return(false);
1096 exception
1097 when others then
1098 rollback;
1099 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1100 routineNameIn => 'itemClassNameExists',
1101 exceptionNumberIn => sqlcode,
1102 exceptionStringIn => sqlerrm);
1103 raise;
1104 return(true); /* conservative: avoids possibility of re-creation of existing name */
1105 end itemClassNameExists;
1106 function newItemClass(itemClassNameIn in varchar2,
1107 newStartDateIn in date,
1108 finalizeIn in boolean default false,
1109 itemClassIdIn in integer default null) return integer as
1110 createdBy integer;
1111 currentUserId integer;
1112 errorCode integer;
1113 errorMessage ame_util.longestStringType;
1114 itemClassId integer;
1115 itemClassCount integer;
1116 itemClassExistsException exception;
1117 itemClassName ame_item_classes.name%type;
1118 nameLengthException exception;
1119 processingDate date;
1120 tempCount integer;
1121 begin
1122 itemClassName := trim(trailing ' ' from itemClassNameIn);
1123 /*
1124 if(processingDateIn is null) then
1125 processingDate := sysdate;
1126 else
1127 processingDate := processingDateIn;
1128 end if;
1129 */
1130 begin
1131 select item_class_id
1132 into itemClassId
1133 from ame_item_classes
1134 where
1135 (itemClassIdIn is null or item_class_id <> itemClassIdIn) and
1136 name = itemClassName and
1137 sysdate between start_date and
1138 nvl(end_date - ame_util.oneSecond, sysdate);
1139 if itemClassId is not null then
1140 raise itemClassExistsException;
1141 end if;
1142 exception
1143 when no_data_found then null;
1144 end;
1145 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_item_classes',
1146 columnNameIn => 'name',
1147 argumentIn => itemClassName)) then
1148 raise nameLengthException;
1149 end if;
1150 /*
1151 If any version of the object has created_by = 1, all versions,
1152 including the new version, should. This is a failsafe way to check
1153 whether previous versions of an already end-dated object had
1154 created_by = 1.
1155 */
1156 currentUserId := ame_util.getCurrentUserId;
1157 if(itemClassIdIn is null) then
1158 createdBy := currentUserId;
1159 select count(*)
1160 into itemClassCount
1161 from ame_item_classes
1162 where
1163 sysdate between start_date and
1164 nvl(end_date - ame_util.oneSecond, sysdate);
1165 /* new id */
1166 itemClassId := (itemClassCount + 1);
1167 else
1168 itemClassId := itemClassIdIn;
1169 select count(*)
1170 into tempCount
1171 from ame_item_classes
1172 where
1173 item_class_id = itemClassId and
1174 created_by = ame_util.seededDataCreatedById;
1175 if(tempCount > 0) then
1176 createdBy := ame_util.seededDataCreatedById;
1177 else
1178 createdBy := currentUserId;
1179 end if;
1180 end if;
1181 insert into ame_item_classes(item_class_id,
1182 name,
1183 created_by,
1184 creation_date,
1185 last_updated_by,
1186 last_update_date,
1187 last_update_login,
1188 start_date,
1189 end_date)
1190 values(itemClassId,
1191 itemClassName,
1192 createdBy,
1193 newStartDateIn,
1194 currentUserId,
1195 newStartDateIn,
1196 currentUserId,
1197 newStartDateIn,
1198 null);
1199 if(finalizeIn) then
1200 commit;
1201 end if;
1202 return(itemClassId);
1203 exception
1204 when itemClassExistsException then
1205 rollback;
1206 errorCode := -20001;
1207 errorMessage :=
1208 ame_util.getMessage(applicationShortNameIn => 'PER',
1209 messageNameIn => 'AME_400374_ADM IC_NAME_EXISTS');
1210 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1211 routineNameIn => 'newItemClass',
1212 exceptionNumberIn => errorCode,
1213 exceptionStringIn => errorMessage);
1214 raise_application_error(errorCode,
1215 errorMessage);
1216 return(null);
1217 when nameLengthException then
1218 rollback;
1219 errorCode := -20001;
1220 errorMessage :=
1221 ame_util.getMessage(applicationShortNameIn => 'PER',
1222 messageNameIn => 'AME_400375_ADM IC_NAME_LONG',
1223 tokenNameOneIn => 'COLUMN_LENGTH',
1224 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_item_classes',
1225 columnNameIn => 'name'));
1226 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1227 routineNameIn => 'newItemClass',
1228 exceptionNumberIn => errorCode,
1229 exceptionStringIn => errorMessage);
1230 raise_application_error(errorCode,
1231 errorMessage);
1232 return(null);
1233 when others then
1234 rollback;
1235 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1236 routineNameIn => 'newItemClass',
1237 exceptionNumberIn => sqlcode,
1238 exceptionStringIn => '(item class ID ' ||
1239 itemClassIdIn||
1240 ') ' ||
1241 sqlerrm);
1242 raise;
1243 return(null);
1244 end newItemClass;
1245 function orderNumberUnique(applicationIdIn in integer,
1246 orderNumberIn in integer) return boolean as
1247 tempCount integer;
1248 begin
1249 select count(*)
1250 into tempCount
1251 from ame_item_class_usages
1252 where
1253 application_id = applicationIdIn and
1254 item_class_order_number = orderNumberIn and
1255 sysdate between start_date and
1256 nvl(end_date - ame_util.oneSecond, sysdate);
1257 if(tempCount > 1) then
1258 return(false);
1259 else
1260 return(true);
1261 end if;
1262 exception
1263 when others then
1264 rollback;
1265 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1266 routineNameIn => 'orderNumberUnique',
1267 exceptionNumberIn => sqlcode,
1268 exceptionStringIn => sqlerrm);
1269 raise;
1270 return(false);
1271 end orderNumberUnique;
1272 function subordinateItemClassCount(applicationIdIn in integer) return integer is
1273 itemClassCount integer;
1274 begin
1275 select count(*)
1276 into itemClassCount
1277 from ame_item_classes,
1278 ame_item_class_usages
1279 where
1280 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
1281 ame_item_class_usages.application_id = applicationIdIn and
1282 ame_item_classes.name <> ame_util.headerItemClassName and
1283 sysdate between ame_item_classes.start_date and
1284 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
1285 sysdate between ame_item_class_usages.start_date and
1286 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate);
1287 return(itemClassCount);
1288 exception
1289 when others then
1290 rollback;
1291 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1292 routineNameIn => 'subordinateItemClassCount',
1293 exceptionNumberIn => sqlcode,
1294 exceptionStringIn => sqlerrm);
1295 raise;
1296 return(null);
1297 end subordinateItemClassCount;
1298 function transTypeCVValueExists(applicationIdIn in integer,
1299 variableNameIn in varchar2) return boolean as
1300 tempCount integer;
1301 begin
1302 select count(*)
1303 into tempCount
1304 from ame_config_vars
1305 where
1306 application_id = applicationIdIn and
1307 variable_name = variableNameIn and
1308 sysdate between start_date and
1309 nvl(end_date - ame_util.oneSecond, sysdate) ;
1310 if(tempCount > 0) then
1311 return(true);
1312 end if;
1313 return(false);
1314 exception
1315 when others then
1316 rollback;
1317 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1318 routineNameIn => 'transTypeCVValueExists',
1319 exceptionNumberIn => sqlcode,
1320 exceptionStringIn => sqlerrm);
1321 raise;
1322 return(false);
1323 end transTypeCVValueExists;
1324 /*
1325 AME_STRIPING
1326 procedure addStripingAttribute(attributeIdIn in integer,
1327 applicationIdIn in integer) as
1328 cursor pairCursor(attributeIdIn in integer,
1329 applicationIdIn in integer) is
1330 select
1331 ame_condition_usages.rule_id,
1332 ame_string_values.string_value
1333 from
1334 ame_condition_usages,
1335 ame_rule_usages,
1336 ame_string_values
1337 where
1338 ame_rule_usages.item_id = applicationIdIn and
1339 ame_rule_usages.rule_id = ame_condition_usages.rule_id and
1340 ame_string_values.condition_id = ame_condition_usages.condition_id and
1341 ame_condition_usages.condition_id in
1342 (select condition_id
1343 from ame_conditions
1344 where
1345 attribute_id = attributeIdIn and
1346 */
1347 /* The condition only has one string value. */
1348 /*
1349 (select count(*) from ame_string_values
1350 where
1351 ame_string_values.condition_id = ame_conditions.condition_id and
1352 sysdate between ame_string_values.start_date and
1353 nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate)
1354 ) = 1 and
1355 sysdate between ame_conditions.start_date and
1356 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)) and
1357 ((sysdate between ame_rule_usages.start_date and
1358 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
1359 (sysdate < ame_rule_usages.start_date and
1360 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
1361 ame_rule_usages.start_date + ame_util.oneSecond))) and
1362 ((sysdate between ame_condition_usages.start_date and
1363 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
1364 (sysdate < ame_condition_usages.start_date and
1365 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
1366 ame_condition_usages.start_date + ame_util.oneSecond))) and
1367 sysdate between ame_string_values.start_date and
1368 nvl(ame_string_values.end_date - ame_util.oneSecond, sysdate)
1369 order by rule_id; */
1370 /* The order-by is crucial to 2(B) below. */
1371 /*
1372 cursor noConditionRuleCursor(attributeIdIn in integer,
1373 applicationIdIn in integer) is
1374 select rule_id
1375 from ame_rule_usages
1376 where
1377 item_id = applicationIdIn and
1378 rule_id not in
1379 (select ame_condition_usages.rule_id
1380 from
1381 ame_condition_usages,
1382 ame_rule_usages ru2
1383 where
1384 ru2.item_id = applicationIdIn and
1385 ru2.rule_id = ame_condition_usages.rule_id and
1386 ame_condition_usages.condition_id in
1387 (select condition_id
1388 from ame_conditions
1389 where
1390 attribute_id = attributeIdIn and
1391 */
1392 /* The condition only has one string value. */
1393 /*
1394 (select count(*)
1395 from ame_string_values
1396 where
1397 ame_string_values.condition_id = ame_conditions.condition_id and
1398 (ame_string_values.start_date <= sysdate and
1399 (ame_string_values.end_date is null or sysdate < ame_string_values.end_date))) = 1 and
1400 (ame_conditions.start_date <= sysdate and
1401 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))) and
1402 (ru2.start_date <= sysdate and
1403 (ru2.end_date is null or sysdate < ru2.end_date)) and
1404 (ame_condition_usages.start_date <= sysdate and
1405 (ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date))) and
1406 (ame_rule_usages.start_date <= sysdate and
1407 (ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date));
1408 cursor oldRuleStripeSetCursor(ruleIdIn in integer,
1409 applicationIdIn in integer,
1410 stripeSetDateIn in date) is
1411 select
1412 value_1,
1413 value_2,
1414 value_3,
1415 value_4,
1416 value_5
1417 from
1418 ame_stripe_sets,
1419 ame_rule_stripe_sets
1420 where
1421 ame_stripe_sets.stripe_set_id = ame_rule_stripe_sets.stripe_set_id and
1422 ame_stripe_sets.application_id = applicationIdIn and
1423 ame_rule_stripe_sets.rule_id = ruleIdIn and
1424 */
1425 /*
1426 Here we want a <= comparison for the end dates, because what we're querying for
1427 stripe sets that were end-dated at tempDate, which gets passed to this cursor as
1428 stripeSetDateIn.
1429 */
1430 /*
1431 (ame_stripe_sets.start_date <= stripeSetDateIn and stripeSetDateIn <= ame_stripe_sets.end_date) and
1432 (ame_rule_stripe_sets.start_date <= stripeSetDateIn and stripeSetDateIn <= ame_rule_stripe_sets.end_date);
1433 attributeCount integer;
1434 errorCode integer;
1435 errorMessage ame_util.longStringType;
1436 existingSAException exception;
1437 firstRuleLackingIndex integer;
1438 lastRuleHavingIndex integer;
1439 newAttributeCount integer;
1440 newStripingAttributeIds ame_util.stringList;
1441 newStripingAttributeIndex integer;
1442 noConditionRuleIds ame_util.idList;
1443 ruleIds ame_util.idList;
1444 rulesLackingEqualityConditions ame_util.idList;
1445 stringValues ame_util.stringList;
1446 stripingAttributeCount integer;
1447 stripingAttributeIdCount integer;
1448 stripingAttributeIds ame_util.idList;
1449 stripingAttNextValIndex integer;
1450 stripingAttributeValues ame_util.stringList;
1451 tempDate date;
1452 tempIndex integer;
1453 tempIndex1 integer;
1454 tempStripeSetId integer;
1455 begin
1456 */
1457 /* Initialize tempDate as early as possible, to avoid waiting later for sysdate to pass tempDate. */
1458 /*
1459 tempDate := sysdate;
1460 */
1461 /*
1462 1. Make sure the attribute with ID attributeIdIn is not already a striping
1463 attribute for the transaction type with ID applicationIdIn.
1464 */
1465 /*
1466 select count(*)
1467 into tempIndex
1468 from ame_attribute_usages
1469 where
1470 attribute_id = attributeIdIn and
1471 application_id = applicationIdIn and
1472 is_striping_attribute = ame_util.booleanTrue and
1473 (start_date <= sysdate and
1474 (end_date is null or sysdate < end_date));
1475 if(tempIndex > 0) then
1476 raise existingSAException;
1477 end if;
1478 */
1479 /*
1480 2. Fetch all (ruleId, stringValue) ordered pairs such that either (A)
1481 the rule with ID ruleId uses the equality condition with condition ID on the
1482 attribute with ID attributeIdIn, and the condition has the unique allowed
1483 value stringValue, or (B) the rule has no such equality condition, so that
1484 stringValue is 'AME_*' and there is no conditionId--all within the transaction
1485 type with ID applicationIdIn.
1486 */
1487 /*
1488 open pairCursor(attributeIdIn => attributeIdIn,
1489 applicationIdIn => applicationIdIn);
1490 fetch pairCursor bulk collect
1491 into
1492 ruleIds,
1493 stringValues;
1494 close pairCursor;
1495 open noConditionRuleCursor(attributeIdIn => attributeIdIn,
1496 applicationIdIn => applicationIdIn);
1497 fetch noConditionRuleCursor bulk collect into rulesLackingEqualityConditions;
1498 close noConditionRuleCursor;
1499 lastRuleHavingIndex := ruleIds.count;
1500 tempIndex := lastRuleHavingIndex; */
1501 /* pre-increment */
1502 /*
1503 firstRuleLackingIndex := 0;
1504 if(rulesLackingEqualityConditions.count > 0) then
1505 firstRuleLackingIndex := tempIndex + 1;
1506 for i in 1 .. rulesLackingEqualityConditions.count loop
1507 tempIndex := tempIndex + 1;
1508 ruleIds(tempIndex) := rulesLackingEqualityConditions(i);
1509 stringValues(tempIndex) := ame_util.stripeWildcard;
1510 end loop;
1511 else
1512 firstRuleLackingIndex := null;
1513 end if;
1514 */
1515 /*
1516 3. (A) If this is the first striping attribute (so that no stripe sets exist yet),
1517 loop through the ordered pairs from #1 above. For each ordered pair, if a
1518 stripe set has been created (by this loop) that uses stringValue, add ruleId
1519 to the stripe set; otherwise, create a new stripe set for stringValue, and
1520 add ruleId to it.
1521 (B) Otherwise:
1522 (i) Pause until sysdate > tempDate, so that queries using sysdate will not hit
1523 rows end-dated at tempDate.
1524 (ii) End date at tempDate all current stripe sets and rule-stripe-set assignments
1525 in the current transaction type.
1526 (iii) For each ordered pair in #1 above:
1527 (a) Fetch the striping-attribute values of all of the stripe sets
1528 that were current as of tempDate that ruleId satisfies, ignoring
1529 the new striping attribute.
1530 (b) For each stripe set in (a):
1531 (1) If no current stripe set exists with the same attribute values,
1532 including stringValue for the new striping attribute, create a new
1533 stripe set having those values.
1534 (2) Put ruleId in the stripe set you either found or created in (1).
1535 */
1536 /* Determine whether this is the first striping attribute. */
1537 /*
1538 select count(*)
1539 into stripingAttributeCount
1540 from ame_attribute_usages
1541 where
1542 application_id = applicationIdIn and
1543 is_striping_attribute = ame_util.booleanTrue and
1544 (start_date <= sysdate and
1545 (end_date is null or sysdate < end_date));
1546 newStripingAttributeIndex := stripingAttributeCount + 1;
1547 */
1548 /* Loop through the ordered pairs, processing them. */
1549 /* if(stripingAttributeCount = 0) then */
1550 /* This is the first striping attribute. */
1551 /* Initialize the unused slots in stripingAttributeValues to null. */
1552 /*
1553 for i in 1 .. 5 loop
1554 stripingAttributeValues(i) := null;
1555 stripingAttributeIds(i) := null;
1556 end loop;
1557 */
1558 /* Now create new stripe sets and rule-stripe-set assignments as needed. */
1559 /*
1560 for i in 1 .. lastRuleHavingIndex loop
1561 stripingAttributeValues(1) := stringValues(i);
1562 tempStripeSetId := ame_admin_pkg.getStripeSetId(applicationIdIn => applicationIdIn,
1563 attributeValuesIn => stripingAttributeValues);
1564 if(tempStripeSetId is null) then
1565 tempStripeSetId := ame_admin_pkg.newStripeSet(applicationIdIn => applicationIdIn,
1566 attributeValuesIn => stripingAttributeValues);
1567 end if;
1568 ame_rule_pkg.newRuleStripeSet(applicationIdIn => applicationIdIn,
1569 ruleIdIn => ruleIds(i),
1570 stripeSetIdIn => tempStripeSetId);
1571 end loop;
1572 if(firstRuleLackingIndex is not null) then
1573 stripingAttributeValues(1) := ame_util.stripeWildcard;
1574 tempStripeSetId := ame_admin_pkg.newStripeSet(applicationIdIn => applicationIdIn,
1575 attributeValuesIn => stripingAttributeValues);
1576 for i in firstRuleLackingIndex .. ruleIds.count loop
1577 ame_rule_pkg.newRuleStripeSet(applicationIdIn => applicationIdIn,
1578 ruleIdIn => ruleIds(i),
1579 stripeSetIdIn => tempStripeSetId);
1580 end loop;
1581 end if;
1582 else */
1583 /* At least one striping attribute already exists. */
1584 /*
1585 End date the current rule-stripe-set assignments, and then the current
1586 stripe sets, with end_date = tempDate. (The order of update statements matters!)
1587 */
1588 /*
1589 getStripingAttributeIds(applicationIdIn => applicationIdIn,
1590 stripingAttributeIdsOut => stripingAttributeIds);
1591 update ame_rule_stripe_sets
1592 set end_date = tempDate
1593 where
1594 stripe_set_id in
1595 (select stripe_set_id
1596 from ame_stripe_sets
1597 where
1598 application_id = applicationIdIn and
1599 (start_date <= tempDate and
1600 (end_date is null or tempDate < end_date))) and
1601 (start_date <= tempDate and
1602 (end_date is null or tempDate < end_date));
1603 update ame_stripe_sets
1604 set end_date = tempDate
1605 where
1606 application_id = applicationIdIn and
1607 (start_date <= tempDate and
1608 (end_date is null or tempDate < end_date));
1609 */
1610 /* Wait until sysdate is past tempDate. */
1611 /*
1612 while(sysdate - tempDate = 0) loop
1613 null;
1614 end loop;
1615 */
1616 /* Replace old stripe sets as needed, adding the rules to the new stripe sets in the process. */
1617 /*
1618 for i in 1 .. ruleIds.count loop
1619 for oldStripeSet in oldRuleStripeSetCursor(ruleIdIn => ruleIds(i),
1620 applicationIdIn => applicationIdIn,
1621 stripeSetDateIn => tempDate) loop
1622 stripingAttributeValues(1) := oldStripeSet.value_1;
1623 stripingAttributeValues(2) := oldStripeSet.value_2;
1624 stripingAttributeValues(3) := oldStripeSet.value_3;
1625 stripingAttributeValues(4) := oldStripeSet.value_4;
1626 stripingAttributeValues(5) := oldStripeSet.value_5;
1627 stripingAttributeValues(newStripingAttributeIndex) := stringValues(i);
1628 tempStripeSetId := ame_admin_pkg.getStripeSetId(applicationIdIn => applicationIdIn,
1629 attributeValuesIn => stripingAttributeValues);
1630 if(tempStripeSetId is null) then
1631 tempStripeSetId := ame_admin_pkg.newStripeSet(applicationIdIn => applicationIdIn,
1632 attributeValuesIn => stripingAttributeValues);
1633 end if;
1634 ame_rule_pkg.newRuleStripeSet(applicationIdIn => applicationIdIn,
1635 ruleIdIn => ruleIds(i),
1636 stripeSetIdIn => tempStripeSetId);
1637 end loop;
1638 end loop;
1639 end if;
1640 */
1641 /*
1642 4. Make the attribute with ID attributeIdIn a striping attribute for the current
1643 transaction type.
1644 */
1645 /*
1646 attributeCount := stripingAttributeIds.count;
1647 for i in 1..attributeCount loop
1648 newStripingAttributeIds(i) := to_char(stripingAttributeIds(i));
1649 end loop;
1650 newStripingAttributeIds(newStripingAttributeIndex) := to_char(attributeIdIn);
1651 tempIndex1 := 1;
1652 for i in 1..(5 - newStripingAttributeIndex) loop
1653 newStripingAttributeIds(newStripingAttributeIndex + tempIndex1) := null;
1654 tempIndex1 := tempIndex1 + 1;
1655 end loop;
1656 newStripeSet2(applicationIdIn => applicationIdIn,
1657 newStripedAttributesSetIn => newStripingAttributeIds);
1658 tempDate := sysdate;
1659 ame_attribute_pkg.changeUsage(attributeIdIn => attributeIdIn,
1660 applicationIdIn => applicationIdIn,
1661 staticUsageIn =>
1662 ame_attribute_pkg.getStaticUsage(attributeIdIn => attributeIdIn,
1663 applicationIdIn => applicationIdIn),
1664 queryStringIn =>
1665 ame_attribute_pkg.getQueryString(attributeIdIn => attributeIdIn,
1666 applicationIdIn => applicationIdIn),
1667 endDateIn => tempDate,
1668 newStartDateIn => tempDate,
1669 lineItemAttributeIn =>
1670 ame_attribute_pkg.getLineItem(attributeIdIn => attributeIdIn),
1671 isStripingAttributeIn => ame_util.booleanTrue);
1672 exception
1673 when existingSAException then
1674 rollback;
1675 errorCode := -20001;
1676 errorMessage :=
1677 'The attribute ' ||
1678 ame_attribute_pkg.getName(attributeIdIn => attributeIdIn) ||
1679 ' is already a striping attribute for this transaction type. ';
1680 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1681 routineNameIn => 'addStripingAttribute',
1682 exceptionNumberIn => errorCode,
1683 exceptionStringIn => errorMessage);
1684 raise_application_error(errorCode,
1685 errorMessage);
1686 when others then
1687 rollback;
1688 if(pairCursor%isopen) then
1689 close pairCursor;
1690 end if;
1691 if(noConditionRuleCursor%isopen) then
1692 close noConditionRuleCursor;
1693 end if;
1694 if(oldRuleStripeSetCursor%isopen) then
1695 close oldRuleStripeSetCursor;
1696 end if;
1697 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1698 routineNameIn => 'addStripingAttribute',
1699 exceptionNumberIn => sqlcode,
1700 exceptionStringIn => sqlerrm);
1701 raise;
1702 end addStripingAttribute;
1703 */
1704 procedure change(applicationIdIn in integer,
1705 transactionTypeIdIn in varchar2,
1706 transactionTypeDescriptionIn in varchar2,
1707 versionStartDateIn in date) as
1708 cursor startDateCursor is
1709 select start_date
1710 from ame_calling_apps
1711 where
1712 application_id = applicationIdIn and
1713 sysdate between start_date and
1714 nvl(end_date - ame_util.oneSecond, sysdate)
1715 for update;
1716 appCount integer;
1717 createdBy integer;
1718 currentUserId integer;
1719 errorCode integer;
1720 errorMessage ame_util.longestStringType;
1721 fndApplicationId ame_calling_apps.fnd_application_id%type;
1722 invalidOrderException exception;
1723 multipleTransTypesException exception;
1724 processingDate date;
1725 startDate date;
1726 tempCount integer;
1727 upperLimit integer;
1728 begin
1729 processingDate := sysdate;
1730 currentUserId := ame_util.getCurrentUserId;
1731 fndApplicationId := getFndApplicationId(applicationIdIn => applicationIdIn);
1732 /* Check version date. */
1733 open startDateCursor;
1734 fetch startDateCursor into startDate;
1735 if(versionStartDateIn <> startDate) then
1736 close startDateCursor;
1737 raise ame_util.objectVersionException;
1738 end if;
1739 close startDateCursor;
1740 update ame_calling_apps
1741 set
1742 last_updated_by = currentUserId,
1743 last_update_date = processingDate,
1744 last_update_login = currentUserId,
1745 end_date = processingDate
1746 where
1747 application_id = applicationIdIn and
1748 processingDate between start_date and
1749 nvl(end_date - ame_util.oneSecond, processingDate) ;
1750 /*
1751 Now that the current version has been end-dated, checkNewOrChangedTransType can
1752 do the same checks as for a new transaction type.
1753 */
1754 checkNewOrChangedTransType(fndAppIdIn => fndApplicationId,
1755 transTypeIdIn => transactionTypeIdIn,
1756 transTypeDescIn => transactionTypeDescriptionIn);
1757 select count(*)
1758 into tempCount
1759 from ame_calling_apps
1760 where
1761 application_id = applicationIdIn and
1762 created_by = ame_util.seededDataCreatedById;
1763 if(tempCount > 0) then
1764 createdBy := ame_util.seededDataCreatedById;
1765 else
1766 createdBy := currentUserId;
1767 end if;
1768 /* Perform update. */
1769 insert into ame_calling_apps(fnd_application_id,
1770 application_name,
1771 application_id,
1772 transaction_type_id,
1773 created_by,
1774 creation_date,
1775 last_updated_by,
1776 last_update_date,
1777 last_update_login,
1778 start_date,
1779 line_item_id_query)
1780 values(fndApplicationId,
1781 transactionTypeDescriptionIn,
1782 applicationIdIn,
1783 transactionTypeIdIn,
1784 createdBy,
1785 processingDate,
1786 currentUserId,
1787 processingDate,
1788 currentUserId,
1789 processingDate,
1790 null);
1791 commit;
1792 exception
1793 when multipleTransTypesException then
1794 rollback;
1795 if(startDateCursor%isOpen) then
1796 close startDateCursor;
1797 end if;
1798 errorCode := -20001;
1799 errorMessage :=
1800 ame_util.getMessage(applicationShortNameIn => 'PER',
1801 messageNameIn => 'AME_400173_ADM_TTY_NOT_NULL_ID');
1802 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1803 routineNameIn => 'change',
1804 exceptionNumberIn => errorCode,
1805 exceptionStringIn => errorMessage);
1806 raise_application_error(errorCode,
1807 errorMessage);
1808 when invalidOrderException then
1809 rollback;
1810 if(startDateCursor%isOpen) then
1811 close startDateCursor;
1812 end if;
1813 errorCode := -20001;
1814 errorMessage :=
1815 ame_util.getMessage(applicationShortNameIn => 'PER',
1816 messageNameIn => 'AME_400174_ADM_LIN_QRY_ORD_BY');
1817 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1818 routineNameIn => 'change',
1819 exceptionNumberIn => errorCode,
1820 exceptionStringIn => errorMessage);
1821 raise_application_error(errorCode,
1822 errorMessage);
1823 when ame_util.objectVersionException then
1824 rollback;
1825 if(startDateCursor%isOpen) then
1826 close startDateCursor;
1827 end if;
1828 errorCode := -20001;
1829 errorMessage :=
1830 ame_util.getMessage(applicationShortNameIn => 'PER',
1831 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1832 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1833 routineNameIn => 'change',
1834 exceptionNumberIn => errorCode,
1835 exceptionStringIn => errorMessage);
1836 raise_application_error(errorCode,
1837 errorMessage);
1838 when no_data_found then
1839 rollback;
1840 if(startDateCursor%isOpen) then
1841 close startDateCursor;
1842 end if;
1843 errorCode := -20001;
1844 errorMessage :=
1845 ame_util.getMessage(applicationShortNameIn => 'PER',
1846 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1847 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1848 routineNameIn => 'change',
1849 exceptionNumberIn => errorCode,
1850 exceptionStringIn => errorMessage);
1851 raise_application_error(errorCode,
1852 errorMessage);
1853 when others then
1854 rollback;
1855 if(startDateCursor%isOpen) then
1856 close startDateCursor;
1857 end if;
1858 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1859 routineNameIn => 'change',
1860 exceptionNumberIn => sqlcode,
1861 exceptionStringIn => sqlerrm);
1862 raise;
1863 end change;
1864 procedure changeItemClass(itemClassIdIn in integer,
1865 itemClassNameIn in varchar2,
1866 startDateIn in date,
1867 endDateIn in date,
1868 finalizeIn in boolean default false) as
1869 itemClassId integer;
1870 currentUserId integer;
1871 begin
1872 currentUserId := ame_util.getCurrentUserId;
1873 update ame_item_classes
1874 set
1875 last_updated_by = currentUserId,
1876 last_update_date = endDateIn,
1877 last_update_login = currentUserId,
1878 end_date = endDateIn
1879 where
1880 item_class_id = itemClassIdIn and
1881 sysdate between start_date and
1882 nvl(end_date - ame_util.oneSecond, sysdate);
1883 itemClassId := newItemClass(itemClassIdIn => itemClassIdIn,
1884 itemClassNameIn => itemClassNameIn,
1885 newStartDateIn => startDateIn,
1886 finalizeIn => false);
1887 if(finalizeIn) then
1888 commit;
1889 end if;
1890 exception
1891 when others then
1892 rollback;
1893 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
1894 routineNameIn => 'changeItemClass',
1895 exceptionNumberIn => sqlcode,
1896 exceptionStringIn => '(item class ID ' ||
1897 itemClassIdIn||
1898 ') ' ||
1899 sqlerrm);
1900 raise;
1901 end changeItemClass;
1902 procedure changeItemClassAndUsage(applicationIdIn in integer,
1903 itemClassIdIn in integer,
1904 itemClassNameIn in varchar2,
1905 itemClassParModeIn in varchar2,
1906 itemClassSublistModeIn in varchar2,
1907 itemClassIdQueryIn in varchar2,
1908 orderNumberIn in integer,
1909 orderNumberUniqueIn in varchar2,
1910 parentVersionStartDateIn in date,
1911 childVersionStartDateIn in date,
1912 finalizeIn in boolean default false) as
1913 cursor startDateCursor is
1914 select start_date
1915 from ame_item_classes
1916 where
1917 item_class_id = itemClassIdIn and
1918 sysdate between start_date and
1919 nvl(end_date - ame_util.oneSecond, sysdate)
1920 for update;
1921 cursor startDateCursor2 is
1922 select start_date
1923 from ame_item_class_usages
1924 where
1925 item_class_id = itemClassIdIn and
1926 application_id = applicationIdIn and
1927 sysdate between start_date and
1928 nvl(end_date - ame_util.oneSecond, sysdate)
1929 for update;
1930 itemClassId integer;
1931 currentUserId integer;
1932 errorCode integer;
1933 errorMessage ame_util.longestStringType;
1934 newStartAndEndDate date;
1935 objectVersionNoDataException exception;
1936 startDate date;
1937 startDate2 date;
1938 tempCount integer;
1939 begin
1940 /* Try to get a lock on the record. */
1941 open startDateCursor;
1942 fetch startDateCursor into startDate;
1943 if startDateCursor%notfound then
1944 raise objectVersionNoDataException;
1945 end if;
1946 if(parentVersionStartDateIn <> startDate) then
1947 close startDateCursor;
1948 raise ame_util.objectVersionException;
1949 end if;
1950 open startDateCursor2;
1951 fetch startDateCursor2 into startDate2;
1952 if startDateCursor2%notfound then
1953 raise objectVersionNoDataException;
1954 end if;
1955 if(childVersionStartDateIn <> startDate2) then
1956 close startDateCursor2;
1957 raise ame_util.objectVersionException;
1958 end if;
1959 /* Check whether the input values match the existing values; if so, just return. */
1960 select count(*)
1961 into tempCount
1962 from
1963 ame_item_classes,
1964 ame_item_class_usages
1965 where
1966 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
1967 ame_item_classes.item_class_id = itemClassIdIn and
1968 ame_item_class_usages.application_id = applicationIdIn and
1969 ame_item_class_usages.item_id_query = itemClassIdQueryIn and
1970 ame_item_class_usages.item_class_par_mode = itemClassParModeIn and
1971 ame_item_class_usages.item_class_sublist_mode = itemClassSublistModeIn and
1972 ame_item_class_usages.item_class_order_number = orderNumberIn and
1973 sysdate between ame_item_classes.start_date and
1974 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
1975 sysdate between ame_item_class_usages.start_date and
1976 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate) ;
1977 if(tempCount > 0) then
1978 return;
1979 end if;
1980 /* Get current values as necessary for update. */
1981 newStartAndEndDate := sysdate;
1982 ame_admin_pkg.changeItemClass(itemClassIdIn => itemClassIdIn,
1983 itemClassNameIn => itemClassNameIn,
1984 endDateIn => newStartAndEndDate,
1985 startDateIn => newStartAndEndDate,
1986 finalizeIn => false);
1987 ame_admin_pkg.changeUsage(itemClassIdIn => itemClassIdIn,
1988 applicationIdIn => applicationIdIn,
1989 itemClassParModeIn => itemClassParModeIn,
1990 itemClassSublistModeIn => itemClassSublistModeIn,
1991 itemClassIdQueryIn => itemClassIdQueryIn,
1992 orderNumberIn => orderNumberIn,
1993 orderNumberUniqueIn => orderNumberUniqueIn,
1994 endDateIn => newStartAndEndDate,
1995 newStartDateIn => newStartAndEndDate,
1996 finalizeIn => false);
1997 close startDateCursor2;
1998 close startDateCursor;
1999 if(finalizeIn) then
2000 commit;
2001 end if;
2002 exception
2003 when ame_util.objectVersionException then
2004 rollback;
2005 if(startDateCursor%isOpen) then
2006 close startDateCursor;
2007 end if;
2008 if(startDateCursor2%isOpen) then
2009 close startDateCursor2;
2010 end if;
2011 errorCode := -20001;
2012 errorMessage :=
2013 ame_util.getMessage(applicationShortNameIn => 'PER',
2014 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
2015 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2016 routineNameIn => 'changeItemClassAndUsage',
2017 exceptionNumberIn => errorCode,
2018 exceptionStringIn => errorMessage);
2019 raise_application_error(errorCode,
2020 errorMessage);
2021 when objectVersionNoDataException then
2022 rollback;
2023 if(startDateCursor%isOpen) then
2024 close startDateCursor;
2025 end if;
2026 if(startDateCursor2%isOpen) then
2027 close startDateCursor2;
2028 end if;
2029 errorCode := -20001;
2030 errorMessage :=
2031 ame_util.getMessage(applicationShortNameIn => 'PER',
2032 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
2033 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2034 routineNameIn => 'changeItemClassAndUsage',
2035 exceptionNumberIn => errorCode,
2036 exceptionStringIn => errorMessage);
2037 raise_application_error(errorCode,
2038 errorMessage);
2039 when others then
2040 rollback;
2041 if(startDateCursor%isOpen) then
2042 close startDateCursor;
2043 end if;
2044 if(startDateCursor2%isOpen) then
2045 close startDateCursor2;
2046 end if;
2047 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2048 routineNameIn => 'changeItemClassAndUsage',
2049 exceptionNumberIn => sqlcode,
2050 exceptionStringIn => '(item class ID ' ||
2051 itemClassIdIn||
2052 ') ' ||
2053 sqlerrm);
2054 raise;
2055 end changeItemClassAndUsage;
2056 procedure changeUsage(applicationIdIn in integer,
2057 itemClassIdIn in integer,
2058 itemClassParModeIn in varchar2,
2059 itemClassSublistModeIn in varchar2,
2060 itemClassIdQueryIn in varchar2,
2061 orderNumberIn in integer,
2062 orderNumberUniqueIn in varchar2,
2063 endDateIn in date,
2064 newStartDateIn in date,
2065 finalizeIn in boolean default false) as
2066 currentUserId integer;
2067 endDate date;
2068 errorCode integer;
2069 errorMessage ame_util.longestStringType;
2070 newOrderNumber integer;
2071 newStartDate date;
2072 oldOrderNumber integer;
2073 oldOrderNumberUnique ame_util.stringType;
2074 orderNumberException exception;
2075 updateOnlyICModified boolean;
2076 begin
2077 oldOrderNumber := getItemClassOrderNumber(applicationIdIn => applicationIdIn,
2078 itemClassIdIn => itemClassIdIn);
2079 if(ame_admin_pkg.orderNumberUnique(applicationIdIn => applicationIdIn,
2080 orderNumberIn => oldOrderNumber)) then
2081 oldOrderNumberUnique := ame_util.yes;
2082 else
2083 oldOrderNumberUnique := ame_util.no;
2084 end if;
2085 currentUserId := ame_util.getCurrentUserId;
2086 endDate := endDateIn;
2087 newStartDate := newStartDateIn;
2088 updateOnlyICModified := false;
2089 /* Check if order number was modified */
2090 if(oldOrderNumber = orderNumberIn) then
2091 if(orderNumberUniqueIn = oldOrderNumberUnique) then
2092 updateOnlyICModified := true;
2093 elsif(orderNumberUniqueIn = ame_util.yes) then
2094 /* Need to adjust the order numbers to keep them in sequence. */
2095 incrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
2096 itemClassIdIn => itemClassIdIn,
2097 orderNumberIn => orderNumberIn);
2098
2099 else /* The order number is not unique. */
2100 raise orderNumberException;
2101 end if;
2102 else
2103 update ame_item_class_usages
2104 set
2105 last_updated_by = currentUserId,
2106 last_update_date = endDate,
2107 last_update_login = currentUserId,
2108 end_date = endDate
2109 where
2110 application_id = applicationIdIn and
2111 item_class_id = itemClassIdIn and
2112 sysdate between start_date and
2113 nvl(end_date - ame_util.oneSecond, sysdate);
2114 if(oldOrderNumberUnique = ame_util.yes) then
2115 decrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
2116 orderNumberIn => oldOrderNumber);
2117 if(orderNumberIn > oldOrderNumber)then
2118 newOrderNumber := (orderNumberIn - 1);
2119 else
2120 newOrderNumber := orderNumberIn;
2121 end if;
2122 else
2123 newOrderNumber := orderNumberIn;
2124 end if;
2125 if(orderNumberUniqueIn = ame_util.yes) then
2126 incrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
2127 itemClassIdIn => itemClassIdIn,
2128 orderNumberIn => newOrderNumber);
2129 end if;
2130 insert into ame_item_class_usages(application_id,
2131 item_class_id,
2132 item_id_query,
2133 item_class_order_number,
2134 item_class_par_mode,
2135 item_class_sublist_mode,
2136 created_by,
2137 creation_date,
2138 last_updated_by,
2139 last_update_date,
2140 last_update_login,
2141 start_date,
2142 end_date)
2143 values(applicationIdIn,
2144 itemClassIdIn,
2145 itemClassIdQueryIn,
2146 newOrderNumber,
2147 itemClassParModeIn,
2148 itemClassSublistModeIn,
2149 currentUserId,
2150 newStartDate,
2151 currentUserId,
2152 newStartDate,
2153 currentUserId,
2154 newStartDate,
2155 null);
2156 end if;
2157 if(updateOnlyICModified) then
2158 update ame_item_class_usages
2159 set
2160 last_updated_by = currentUserId,
2161 last_update_date = endDate,
2162 last_update_login = currentUserId,
2163 end_date = endDate
2164 where
2165 application_id = applicationIdIn and
2166 item_class_id = itemClassIdIn and
2167 sysdate between start_date and
2168 nvl(end_date - ame_util.oneSecond, sysdate);
2169 insert into ame_item_class_usages(application_id,
2170 item_class_id,
2171 item_id_query,
2172 item_class_order_number,
2173 item_class_par_mode,
2174 item_class_sublist_mode,
2175 created_by,
2176 creation_date,
2177 last_updated_by,
2178 last_update_date,
2179 last_update_login,
2180 start_date,
2181 end_date)
2182 values(applicationIdIn,
2183 itemClassIdIn,
2184 itemClassIdQueryIn,
2185 orderNumberIn,
2186 itemClassParModeIn,
2187 itemClassSublistModeIn,
2188 currentUserId,
2189 newStartDate,
2190 currentUserId,
2191 newStartDate,
2192 currentUserId,
2193 newStartDate,
2194 null);
2195 end if;
2196 if(finalizeIn) then
2197 commit;
2198 end if;
2199 exception
2200 when others then
2201 rollback;
2202 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2203 routineNameIn => 'changeUsage',
2204 exceptionNumberIn => sqlcode,
2205 exceptionStringIn => '(item class ID ' ||
2206 itemClassIdIn||
2207 ') ' ||
2208 sqlerrm);
2209 raise;
2210 end changeUsage;
2211 procedure checkNewOrChangedTransType(fndAppIdIn in integer,
2212 transTypeIdIn in varchar2,
2213 transTypeDescIn in varchar2) as
2214 badDescException exception;
2215 badTransTypeIdException exception;
2216 errorCode integer;
2217 errorMessage ame_util.longestStringType;
2218 existingNullTTException exception;
2219 existingTTException exception;
2220 tempCount integer;
2221 begin
2222 /* You can't add a trans type if a null trans type ID already exists for the same app. */
2223 select count(*)
2224 into tempCount
2225 from ame_calling_apps
2226 where
2227 fnd_application_id = fndAppIdIn and
2228 transaction_type_id is null and
2229 sysdate between start_date and
2230 nvl(end_date - ame_util.oneSecond, sysdate) ;
2231 if(tempCount > 0) then
2232 raise existingNullTTException;
2233 end if;
2234 /* You can't add a null trans type ID if a trans type already exists for same app. */
2235 if(transTypeIdIn is null) then
2236 select count(*)
2237 into tempCount
2238 from ame_calling_apps
2239 where
2240 fnd_application_id = fndAppIdIn and
2241 sysdate between start_date and
2242 nvl(end_date - ame_util.oneSecond, sysdate) ;
2243 if(tempCount > 0) then
2244 raise existingTTException;
2245 end if;
2246 end if;
2247 /* You can't add the same trans type as one that already exists. */
2248 select count(*)
2249 into tempCount
2250 from ame_calling_apps
2251 where
2252 fnd_application_id = fndAppIdIn and
2253 transaction_type_id = transTypeIdIn and
2254 sysdate between start_date and
2255 nvl(end_date - ame_util.oneSecond, sysdate) ;
2256 if(tempCount > 0) then
2257 raise badTransTypeIdException;
2258 end if;
2259 /* You can't add the same desc as one that already exists. */
2260 select count(*)
2261 into tempCount
2262 from ame_calling_apps
2263 where
2264 upper(application_name) = upper(transTypeDescIn) and
2265 sysdate between start_date and
2266 nvl(end_date - ame_util.oneSecond, sysdate) ;
2267 if(tempCount > 0) then
2268 raise badDescException;
2269 end if;
2270 exception
2271 when badTransTypeIdException then
2272 rollback;
2273 errorCode := -20001;
2274 errorMessage :=
2275 ame_util.getMessage(applicationShortNameIn => 'PER',
2276 messageNameIn => 'AME_400175_ADM_TTY_ID_ALREADY');
2277 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2278 routineNameIn => 'checkNewOrChangedTransType',
2279 exceptionNumberIn => errorCode,
2280 exceptionStringIn => errorMessage);
2281 raise_application_error(errorCode,
2282 errorMessage);
2283 when badDescException then
2284 rollback;
2285 errorCode := -20001;
2286 errorMessage :=
2287 ame_util.getMessage(applicationShortNameIn => 'PER',
2288 messageNameIn => 'AME_400176_ADM_TTY_HAS_DESC');
2289 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2290 routineNameIn => 'checkNewOrChangedTransType',
2291 exceptionNumberIn => errorCode,
2292 exceptionStringIn => errorMessage);
2293 raise_application_error(errorCode,
2294 errorMessage);
2295 when existingNullTTException then
2296 errorCode := -20001;
2297 errorMessage :=
2298 ame_util.getMessage(applicationShortNameIn => 'PER',
2299 messageNameIn => 'AME_400177_ADM_TTY_EXIST_NULL');
2300 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2301 routineNameIn => 'checkNewOrChangedTransType',
2302 exceptionNumberIn => errorCode,
2303 exceptionStringIn => errorMessage);
2304 raise_application_error(errorCode,
2305 errorMessage);
2306 when existingTTException then
2307 rollback;
2308 errorCode := -20001;
2309 errorMessage :=
2310 ame_util.getMessage(applicationShortNameIn => 'PER',
2311 messageNameIn => 'AME_400178_ADM_TTY_EXIST_NONUL');
2312 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2313 routineNameIn => 'checkNewOrChangedTransType',
2314 exceptionNumberIn => errorCode,
2315 exceptionStringIn => errorMessage);
2316 raise_application_error(errorCode,
2317 errorMessage);
2318 when others then
2319 rollback;
2320 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2321 routineNameIn => 'checkNewOrChangedTransType',
2322 exceptionNumberIn => sqlcode,
2323 exceptionStringIn => sqlerrm);
2324 raise;
2325 end checkNewOrChangedTransType;
2326 /*
2327 AME_STRIPING
2328 procedure checkStripeSetUsage(stripeSetIdIn in integer,
2329 commitIn in boolean default false) as
2330 useCount integer;
2331 begin
2332 select count(*)
2333 into useCount
2334 from ame_rule_stripe_sets
2335 where
2336 stripe_set_id = stripeSetIdIn and
2337 (start_date <= sysdate and
2338 (end_date is null or sysdate < end_date));
2339 if(useCount = 0) then
2340 update ame_stripe_sets
2341 set end_date = sysdate
2342 where
2343 stripe_set_id = stripeSetIdIn and
2344 (start_date <= sysdate and
2345 (end_date is null or sysdate < end_date));
2346 if(commitIn) then
2347 commit;
2348 end if;
2349 end if;
2350 exception
2351 when others then
2352 rollback;
2353 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2354 routineNameIn => 'checkStripeSetUsage',
2355 exceptionNumberIn => sqlcode,
2356 exceptionStringIn => sqlerrm);
2357 raise;
2358 end checkStripeSetUsage;
2359 */
2360 procedure clearTransException(applicationIdIn in integer,
2361 transactionIdIn in varchar2) as
2362 begin
2363 delete from ame_exceptions_log
2364 where
2365 application_id = applicationIdIn and
2366 transaction_id = transactionIdIn;
2367 commit;
2368 exception
2369 when others then
2370 rollback;
2371 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2372 routineNameIn => 'clearTransException',
2373 exceptionNumberIn => sqlcode,
2374 exceptionStringIn => sqlerrm);
2375 raise;
2376 end clearTransException;
2377 procedure clearTransTypeConfigVarValue(applicationIdIn in integer,
2378 variableNameIn in varchar2) as
2379 begin
2380 update ame_config_vars
2381 set end_date = sysdate
2382 where
2383 application_id = applicationIdIn and
2384 variable_name = variableNameIn and
2385 sysdate between start_date and
2386 nvl(end_date - ame_util.oneSecond, sysdate) ;
2387 commit;
2388 exception
2389 when others then
2390 rollback;
2391 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2392 routineNameIn => 'clearTransTypeConfigVarValue',
2393 exceptionNumberIn => sqlcode,
2394 exceptionStringIn => sqlerrm);
2395 raise;
2396 end clearTransTypeConfigVarValue;
2397 procedure clearTransTypeExceptions(applicationIdIn in integer) as
2398 begin
2399 delete from ame_exceptions_log
2400 where application_id = applicationIdIn;
2401 commit;
2402 exception
2403 when others then
2404 rollback;
2405 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2406 routineNameIn => 'clearTransTypeExceptions',
2407 exceptionNumberIn => sqlcode,
2408 exceptionStringIn => sqlerrm);
2409 raise;
2410 end clearTransTypeExceptions;
2411 procedure clearWebExceptions as
2412 begin
2413 delete from ame_exceptions_log
2414 where
2415 application_id is null and
2416 transaction_id is null;
2417 commit;
2418 exception
2419 when others then
2420 rollback;
2421 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2422 routineNameIn => 'clearWebExceptions',
2423 exceptionNumberIn => sqlcode,
2424 exceptionStringIn => sqlerrm);
2425 raise;
2426 end clearWebExceptions;
2427 procedure decrementItemClassOrderNumbers(applicationIdIn in integer,
2428 orderNumberIn in integer,
2429 finalizeIn in boolean default false) as
2430 cursor orderNumberCursor is
2431 select item_class_id, item_class_order_number
2432 from ame_item_class_usages
2433 where
2434 application_id = applicationIdIn and
2435 item_class_order_number > orderNumberIn and
2436 sysdate between start_date and
2437 nvl(end_date - ame_util.oneSecond, sysdate)
2438 order by item_class_order_number;
2439 currentUserId integer;
2440 itemClassIds ame_util.idList;
2441 itemClassIdQuery ame_item_class_usages.item_id_query%type;
2442 itemClassParMode ame_item_class_usages.item_class_par_mode%type;
2443 itemClassSublistMode ame_item_class_usages.item_class_sublist_mode%type;
2444 orderNumbers ame_util.idList;
2445 processingDate date;
2446 votingRegime ame_util.charType;
2447 begin
2448 currentUserId := ame_util.getCurrentUserId;
2449 processingDate := sysdate;
2450 open orderNumberCursor;
2451 fetch orderNumberCursor bulk collect
2452 into itemClassIds, orderNumbers;
2453 close orderNumberCursor;
2454 for i in 1 .. itemClassIds.count loop
2455 itemClassIdQuery := getItemClassIdQuery(itemClassIdIn => itemClassIds(i),
2456 applicationIdIn => applicationIdIn);
2457 itemClassParMode := getItemClassParMode(itemClassIdIn => itemClassIds(i),
2458 applicationIdIn => applicationIdIn);
2459 itemClassSublistMode := getItemClassSublistMode(itemClassIdIn => itemClassIds(i),
2460 applicationIdIn => applicationIdIn);
2461 update ame_item_class_usages
2462 set
2463 last_updated_by = currentUserId,
2464 last_update_date = processingDate,
2465 last_update_login = currentUserId,
2466 end_date = processingDate
2467 where
2468 application_id = applicationIdIn and
2469 item_class_id = itemClassIds(i) and
2470 sysdate between start_date and
2471 nvl(end_date - ame_util.oneSecond, sysdate);
2472 insert into ame_item_class_usages(application_id,
2473 item_class_id,
2474 item_id_query,
2475 item_class_order_number,
2476 item_class_par_mode,
2477 item_class_sublist_mode,
2478 created_by,
2479 creation_date,
2480 last_updated_by,
2481 last_update_date,
2482 last_update_login,
2483 start_date,
2484 end_date)
2485 values(applicationIdIn,
2486 itemClassIds(i),
2487 itemClassIdQuery,
2488 (orderNumbers(i) - 1),
2489 itemClassParMode,
2490 itemClassSublistMode,
2491 currentUserId,
2492 processingDate,
2493 currentUserId,
2494 processingDate,
2495 currentUserId,
2496 processingDate,
2497 null);
2498 end loop;
2499 if(finalizeIn) then
2500 commit;
2501 end if;
2502 exception
2503 when others then
2504 rollback;
2505 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2506 routineNameIn => 'decrementItemClassOrderNumbers',
2507 exceptionNumberIn => sqlcode,
2508 exceptionStringIn => sqlerrm);
2509 raise;
2510 end decrementItemClassOrderNumbers;
2511 /*
2512 procedure enableRuleStriping(applicationIdIn in integer,
2513 commitIn in boolean default false) as
2514 */
2515 /*
2516 This procedure creates all necessary ame_rule_stripe_sets rows for the input
2517 transaction type, when a user enables rule striping. At least one striping
2518 attribute must be selected for the input transaction type, before this
2519 procedure is called.
2520 */
2521 /*
2522 cursor ruleCursor(applicationIdIn in integer) is
2523 */
2524 /* This cursor fetches the IDs of all rules used by the input transaction type. */
2525 /*
2526 select rule_id
2527 from ame_rule_usages
2528 where
2529 item_id = applicationIdIn and
2530 (start_date <= sysdate and (end_date is null or sysdate < end_date));
2531 */
2532 /*
2533 This cursor fetches the attribute IDs and condition IDs of all striping conditions
2534 used by the input rule.
2535 */
2536 /*
2537 cursor ruleStripingCondCursor(ruleIdIn in integer,
2538 stripingAttribute1IdIn in integer,
2539 stripingAttribute2IdIn in integer,
2540 stripingAttribute3IdIn in integer,
2541 stripingAttribute4IdIn in integer,
2542 stripingAttribute5IdIn in integer) is
2543 select
2544 ame_conditions.attribute_id,
2545 ame_conditions.condition_id
2546 from
2547 ame_conditions,
2548 ame_condition_usages
2549 where
2550 ame_condition_usages.rule_id = ruleIdIn and
2551 ame_condition_usages.condition_id = ame_conditions.condition_id and
2552 ame_conditions.condition_type = ame_util.ordinaryConditionType and
2553 (ame_conditions.attribute_id = stripingAttribute1IdIn or
2554 ame_conditions.attribute_id = stripingAttribute2IdIn or
2555 ame_conditions.attribute_id = stripingAttribute3IdIn or
2556 ame_conditions.attribute_id = stripingAttribute4IdIn or
2557 ame_conditions.attribute_id = stripingAttribute5IdIn) and
2558 (select count(*)
2559 from ame_string_values sv2
2560 where sv2.condition_id = ame_conditions.condition_id and
2561 (sv2.start_date <= sysdate and (sv2.end_date is null or sysdate < sv2.end_date))) = 1 and
2562 (ame_conditions.start_date <= sysdate and
2563 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
2564 (ame_condition_usages.start_date <= sysdate and
2565 (ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date));
2566 cursor stripingConditionCursor(applicationIdIn in integer,
2567 stripingAttributeIdIn in integer) is
2568 */
2569 /*
2570 This cursor fetches all active striping conditions for the input transaction type. A
2571 condition qualifies as such if it is (1) ordinary, (2) defined on the input attribute ID,
2572 (3) has exactly one allowed (string) value, and (4) is used by at least one rule that the
2573 input transaction type uses. The query's 'distinct' qualifier is necessary because
2574 several rule usages can reference the same condition ID.
2575 */
2576 /*
2577 select distinct
2578 ame_conditions.condition_id,
2579 ame_string_values.string_value
2580 from
2581 ame_conditions,
2582 ame_condition_usages,
2583 ame_rule_usages,
2584 ame_string_values
2585 where
2586 ame_conditions.attribute_id = stripingAttributeIdIn and
2587 ame_conditions.condition_type = ame_util.ordinaryConditionType and
2588 ame_string_values.condition_id = ame_conditions.condition_id and
2589 ame_condition_usages.condition_id = ame_conditions.condition_id and
2590 ame_condition_usages.rule_id = ame_rule_usages.rule_id and
2591 ame_rule_usages.item_id = applicationIdIn and
2592 (select count(*)
2593 from ame_string_values sv2
2594 where sv2.condition_id = ame_conditions.condition_id and
2595 (sv2.start_date <= sysdate and (sv2.end_date is null or sysdate < sv2.end_date))) = 1 and
2596 (ame_conditions.start_date <= sysdate and
2597 (ame_conditions.end_date is null or sysdate < ame_conditions.end_date)) and
2598 (ame_condition_usages.start_date <= sysdate and
2599 (ame_condition_usages.end_date is null or sysdate < ame_condition_usages.end_date)) and
2600 (ame_rule_usages.start_date <= sysdate and
2601 (ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date)) and
2602 (ame_string_values.start_date <= sysdate and
2603 (ame_string_values.end_date is null or sysdate < ame_string_values.end_date));
2604 errorCode integer;
2605 errorMessage ame_util.longStringType;
2606 noStripingAttsException exception;
2607 ruleIds ame_util.idList;
2608 stripingAttributeCount integer;
2609 stripingAttributeIds ame_util.idList;
2610 stripingConditionValues ame_util.stringList; */
2611 /* indexed by condition ID */
2612 /* tempAttributeIds ame_util.idList; */
2613 /* indexed consecutively, reused */
2614 /* tempConditionIds ame_util.idList; */
2615 /* indexed consecutively, reused */
2616 /* tempConditionValues ame_util.stringList; */
2617 /* indexed consecutively, reused */
2618 /*
2619 tempStripeSetId integer;
2620 begin
2621 ame_admin_pkg.getStripingAttributeIds(applicationIdIn => applicationIdIn,
2622 stripingAttributeIdsOut => stripingAttributeIds);
2623 stripingAttributeCount := stripingAttributeIds.count;
2624 if(stripingAttributeCount = 0) then
2625 raise noStripingAttsException;
2626 end if;
2627 for i in stripingAttributeCount + 1 .. 5 loop
2628 stripingAttributeIds(i) := null;
2629 end loop;
2630 */
2631 /*
2632 Fetch the condition IDs and string values for each ordinary equality condition
2633 on each striping attribute, where at least one rule in the current transaction
2634 type uses the condition. Copy the string values into a list indexed by
2635 condition ID, so that code below this loop doesn't have to fetch striping
2636 conditions' allowed values one condition at a time, or fetch the same condition's
2637 allowed value several times.
2638 */
2639 /*
2640 for i in 1 .. stripingAttributeCount loop
2641 open stripingConditionCursor(applicationIdIn => applicationIdIn,
2642 stripingAttributeIdIn => stripingAttributeIds(i));
2643 fetch stripingConditionCursor bulk collect
2644 into
2645 tempConditionIds,
2646 tempConditionValues;
2647 close stripingConditionCursor;
2648 */
2649 /* Copy the temp values into a list that is indexed by condition ID. */
2650 /*
2651 for j in 1 .. tempConditionIds.count loop
2652 stripingConditionValues(tempConditionIds(j)) := tempConditionValues(j);
2653 end loop;
2654 tempConditionIds.delete;
2655 tempConditionValues.delete;
2656 end loop;
2657 */
2658 /* Fetch the rules used by this transaction type. */
2659 /*
2660 open ruleCursor(applicationIdIn => applicationIdIn);
2661 fetch ruleCursor bulk collect into ruleIds;
2662 close ruleCursor;
2663 for i in 1 .. ruleIds.count loop
2664 tempAttributeIds.delete;
2665 tempConditionIds.delete;
2666 open ruleStripingCondCursor(ruleIdIn => ruleIds(i),
2667 stripingAttribute1IdIn => stripingAttributeIds(1),
2668 stripingAttribute2IdIn => stripingAttributeIds(2),
2669 stripingAttribute3IdIn => stripingAttributeIds(3),
2670 stripingAttribute4IdIn => stripingAttributeIds(4),
2671 stripingAttribute5IdIn => stripingAttributeIds(5));
2672 fetch ruleStripingCondCursor bulk collect
2673 into
2674 tempAttributeIds,
2675 tempConditionIds;
2676 close ruleStripingCondCursor;
2677 for j in 1 .. stripingAttributeCount loop
2678 tempConditionValues(j) := ame_util.stripeWildcard;
2679 end loop;
2680 for j in stripingAttributeCount + 1 .. 5 loop
2681 tempConditionValues(j) := null;
2682 end loop;
2683 for j in 1 .. tempAttributeIds.count loop
2684 for k in 1 .. stripingAttributeCount loop
2685 if(tempAttributeIds(j) = stripingAttributeIds(k)) then
2686 tempConditionValues(k) := stripingConditionValues(tempConditionIds(j));
2687 end if;
2688 end loop;
2689 end loop;
2690 tempStripeSetId := ame_admin_pkg.getStripeSetId(applicationIdIn => applicationIdIn,
2691 attributeValuesIn => tempConditionValues);
2692 if(tempStripeSetId is null) then
2693 tempStripeSetId := ame_admin_pkg.newStripeSet(applicationIdIn => applicationIdIn,
2694 attributeValuesIn => tempConditionValues);
2695 end if;
2696 ame_rule_pkg.newRuleStripeSet(applicationIdIn => applicationIdIn,
2697 ruleIdIn => ruleIds(i),
2698 stripeSetIdIn => tempStripeSetId);
2699 end loop;
2700 if(commitIn) then
2701 commit;
2702 end if;
2703 exception
2704 when noStripingAttsException then
2705 rollback;
2706 errorCode := -20001;
2707 errorMessage := 'At least one striping attribute must be selected to enable rule striping. ';
2708 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2709 routineNameIn => 'enableRuleStriping',
2710 exceptionNumberIn => errorCode,
2711 exceptionStringIn => errorMessage);
2712 raise_application_error(errorCode,
2713 errorMessage);
2714 when others then
2715 rollback;
2716 if(stripingConditionCursor%isopen) then
2717 close stripingConditionCursor;
2718 end if;
2719 if(ruleCursor%isopen) then
2720 close ruleCursor;
2721 end if;
2722 if(ruleStripingCondCursor%isopen) then
2723 close ruleStripingCondCursor;
2724 end if;
2725 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2726 routineNameIn => 'enableRuleStriping',
2727 exceptionNumberIn => sqlcode,
2728 exceptionStringIn => sqlerrm);
2729 raise;
2730 end enableRuleStriping;
2731 */
2732 /*
2733 AME_STRIPING
2734 procedure getAttributeStripeSetNames(applicationIdIn in integer,
2735 stripingAttributeIdsOut out nocopy ame_util.idList,
2736 stripingAttributeNamesOut out nocopy ame_util.stringList) as
2737 attributeCount integer;
2738 attributeId1 integer;
2739 attributeId2 integer;
2740 attributeId3 integer;
2741 attributeId4 integer;
2742 attributeId5 integer;
2743 begin
2744 begin
2745 select to_number(value_1),
2746 to_number(value_2),
2747 to_number(value_3),
2748 to_number(value_4),
2749 to_number(value_5)
2750 into
2751 attributeId1,
2752 attributeId2,
2753 attributeId3,
2754 attributeId4,
2755 attributeId5
2756 from ame_stripe_sets
2757 where
2758 application_id = applicationIdIn and
2759 stripe_set_id = 0 and
2760 (start_date <= sysdate and
2761 (end_date is null or sysdate < end_date));
2762 exception
2763 when no_data_found then
2764 stripingAttributeIdsOut := ame_util.emptyIdList;
2765 stripingAttributeNamesOut := ame_util.emptyStringList;
2766 return;
2767 end;
2768 if(attributeId1 is not null) then
2769 stripingAttributeIdsOut(1) := attributeId1;
2770 stripingAttributeNamesOut(1) := ame_attribute_pkg.getName(attributeIdIn => attributeId1);
2771 end if;
2772 if(attributeId2 is not null) then
2773 stripingAttributeIdsOut(2) := attributeId2;
2774 stripingAttributeNamesOut(2) := ame_attribute_pkg.getName(attributeIdIn => attributeId2);
2775 end if;
2776 if (attributeId3 is not null) then
2777 stripingAttributeIdsOut(3) := attributeId3;
2778 stripingAttributeNamesOut(3) := ame_attribute_pkg.getName(attributeIdIn => attributeId3);
2779 end if;
2780 if (attributeId4 is not null) then
2781 stripingAttributeIdsOut(4) := attributeId4;
2782 stripingAttributeNamesOut(4) := ame_attribute_pkg.getName(attributeIdIn => attributeId4);
2783 end if;
2784 if(attributeId5 is not null) then
2785 stripingAttributeIdsOut(5) := attributeId5;
2786 stripingAttributeNamesOut(5) := ame_attribute_pkg.getName(attributeIdIn => attributeId5);
2787 end if;
2788 exception
2789 when others then
2790 rollback;
2791 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2792 routineNameIn => 'getAttributeStripeSetNames',
2793 exceptionNumberIn => sqlcode,
2794 exceptionStringIn => sqlerrm);
2795 stripingAttributeIdsOut := ame_util.emptyIdList;
2796 stripingAttributeNamesOut := ame_util.emptyStringList;
2797 raise;
2798 end getAttributeStripeSetNames;
2799 */
2800 procedure getConfigVariables(applicationIdIn in integer default null,
2801 variableNamesOut out nocopy ame_util.stringList,
2802 descriptionsOut out nocopy ame_util.stringList) as
2803 cursor configVariablesCursor is
2804 select
2805 variable_name,
2806 description
2807 from ame_config_vars
2808 where
2809 application_id is null and
2810 sysdate between start_date and
2811 nvl(end_date - ame_util.oneSecond, sysdate)
2812 order by variable_name;
2813 tempIndex integer;
2814 begin
2815 tempIndex := 1;
2816 /* First handle the default-values case. */
2817 if(applicationIdIn is null) then
2818 for tempRows in configVariablesCursor loop
2819 variableNamesOut(tempIndex) := tempRows.variable_name;
2820 descriptionsOut(tempIndex) := tempRows.description;
2821 tempIndex := tempIndex + 1;
2822 end loop;
2823 return;
2824 end if;
2825 /* Now handle the transaction-type case. */
2826 variableNamesOut(1) := ame_util.adminApproverConfigVar;
2827 descriptionsOut(1) := ame_util.getConfigDesc(variableNameIn => ame_util.adminApproverConfigVar);
2828 variableNamesOut(2) := ame_util.allowAllApproverTypesConfigVar;
2829 descriptionsOut(2) := ame_util.getConfigDesc(variableNameIn => ame_util.allowAllApproverTypesConfigVar);
2830 variableNamesOut(3) := ame_util.allowAllICRulesConfigVar;
2831 descriptionsOut(3) := ame_util.getConfigDesc(variableNameIn => ame_util.allowAllICRulesConfigVar);
2832 variableNamesOut(4) := ame_util.allowFyiNotificationsConfigVar;
2833 descriptionsOut(4) := ame_util.getConfigDesc(variableNameIn => ame_util.allowFyiNotificationsConfigVar);
2834 variableNamesOut(5) := ame_util.curConvWindowConfigVar;
2835 descriptionsOut(5) := ame_util.getConfigDesc(variableNameIn => ame_util.curConvWindowConfigVar);
2836 variableNamesOut(6) := ame_util.forwardingConfigVar;
2837 descriptionsOut(6) := ame_util.getConfigDesc(variableNameIn => ame_util.forwardingConfigVar);
2838 variableNamesOut(7) := ame_util.productionConfigVar;
2839 descriptionsOut(7) := ame_util.getConfigDesc(variableNameIn => ame_util.productionConfigVar);
2840 variableNamesOut(8) := ame_util.purgeFrequencyConfigVar;
2841 descriptionsOut(8) := ame_util.getConfigDesc(variableNameIn => ame_util.purgeFrequencyConfigVar);
2842 variableNamesOut(9) := ame_util.repeatedApproverConfigVar;
2843 descriptionsOut(9) := ame_util.getConfigDesc(variableNameIn => ame_util.repeatedApproverConfigVar);
2844 variableNamesOut(10) := ame_util.rulePriorityModesConfigVar;
2845 descriptionsOut(10) := ame_util.getConfigDesc(variableNameIn => ame_util.rulePriorityModesConfigVar);
2846
2847 /*
2848 AME_STRIPING
2849 variableNamesOut(8) := ame_util.useRuleStripingConfigVar;
2850 descriptionsOut(8) := ame_util.getConfigDesc(variableNameIn => ame_util.useRuleStripingConfigVar);
2851 */
2852 exception
2853 when others then
2854 rollback;
2855 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2856 routineNameIn => 'getConfigVariables',
2857 exceptionNumberIn => sqlcode,
2858 exceptionStringIn => sqlerrm);
2859 variableNamesOut := ame_util.emptyStringList;
2860 descriptionsOut := ame_util.emptyStringList;
2861 raise;
2862 end getConfigVariables;
2863 procedure getExistingShareableIClasses(applicationIdIn in integer,
2864 itemClassIdsOut out nocopy ame_util.stringList,
2865 itemClassNamesOut out nocopy ame_util.stringList) as
2866 cursor unusedItemClassCursor(applicationIdIn in integer) is
2867 select
2868 item_class_id,
2869 name
2870 from
2871 ame_item_classes
2872 where
2873 (start_date <= sysdate and
2874 (end_date is null or sysdate < end_date))
2875 minus
2876 select
2877 ame_item_classes.item_class_id,
2878 name
2879 from
2880 ame_item_classes,
2881 ame_item_class_usages
2882 where
2883 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
2884 ame_item_class_usages.application_id = applicationIdIn and
2885 sysdate between ame_item_classes.start_date and
2886 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2887 sysdate between ame_item_class_usages.start_date and
2888 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
2889 order by name;
2890 tempIndex integer;
2891 begin
2892 tempIndex := 1;
2893 for tempItemClass in unusedItemClassCursor(applicationIdIn => applicationIdIn) loop
2894 /* The explicit conversion below lets nocopy work. */
2895 itemClassIdsOut(tempIndex) := to_char(tempItemClass.item_class_id);
2896 itemClassNamesOut(tempIndex) := tempItemClass.name;
2897 tempIndex := tempIndex + 1;
2898 end loop;
2899 exception
2900 when others then
2901 rollback;
2902 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2903 routineNameIn => 'getExistingShareableIClasses',
2904 exceptionNumberIn => sqlcode,
2905 exceptionStringIn => '(application ID ' ||
2906 applicationIdIn||
2907 ') ' ||
2908 sqlerrm);
2909 itemClassIdsOut := ame_util.emptyStringList;
2910 itemClassNamesOut := ame_util.emptyStringList;
2911 raise;
2912 end getExistingShareableIClasses;
2913 procedure getFndApplications(fndAppIdsOut out nocopy ame_util.stringList,
2914 fndAppNamesOut out nocopy ame_util.stringList) as
2915 cursor fndAppNamesCursor is
2916 select
2917 application_id,
2918 substrb(ltrim(application_name),1,99) application_name
2919 from fnd_application_vl
2920 order by application_name;
2921 tempIndex integer;
2922 begin
2923 tempIndex := 0;
2924 for tempRows in fndAppNamesCursor loop
2925 tempIndex := tempIndex + 1;
2926 /* The explicit conversion below lets nocopy work. */
2927 fndAppIdsOut(tempIndex) := to_char(tempRows.application_id);
2928 fndAppNamesOut(tempIndex) := tempRows.application_name;
2929 end loop;
2930 exception
2931 when others then
2932 rollback;
2933 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2934 routineNameIn => 'getFndApplications',
2935 exceptionNumberIn => sqlcode,
2936 exceptionStringIn => sqlerrm);
2937 fndAppIdsOut := ame_util.emptyStringList;
2938 fndAppNamesOut := ame_util.emptyStringList;
2939 raise;
2940 end getFndApplications;
2941 procedure getForwardingBehaviorList(forwardingBehaviorIn in integer,
2942 forwardingBehaviorValuesOut out nocopy ame_util.stringList,
2943 forwardingBehaviorLabelsOut out nocopy ame_util.stringList) as
2944 valueIndex integer;
2945 begin
2946 valueIndex := 1;
2947 if(forwardingBehaviorIn in (1,2,5,6)) then
2948 forwardingBehaviorValuesOut(valueIndex) := ame_util.remand;
2949 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_REMAND');
2950 valueIndex := valueIndex + 1;
2951 end if;
2952 forwardingBehaviorValuesOut(valueIndex) := ame_util.forwarderAndForwardee;
2953 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_FOR_TO_FORWARDER_FORWARDEE');
2954 valueIndex := valueIndex +1;
2955 forwardingBehaviorValuesOut(valueIndex) := ame_util.forwardeeOnly;
2956 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_FOR_TO_FORWARDEE_ONLY');
2957 valueIndex := valueIndex +1;
2958 if(forwardingBehaviorIn in (3,4)) then
2959 forwardingBehaviorValuesOut(valueIndex) := ame_util.repeatForwarder;
2960 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_REPEAT_FORWARDER');
2961 valueIndex := valueIndex + 1;
2962 forwardingBehaviorValuesOut(valueIndex) := ame_util.skipForwarder;
2963 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_SKIP_FORWARDER');
2964 valueIndex := valueIndex + 1;
2965 end if;
2966 forwardingBehaviorValuesOut(valueIndex) := ame_util.ignoreForwarding;
2967 forwardingBehaviorLabelsOut(valueIndex) := ame_util.getLabel(ame_util.perFndAppId,'AME_IGNORE_FORWARDING');
2968 exception
2969 when others then
2970 rollback;
2971 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
2972 routineNameIn => 'getForwardingBehaviorList',
2973 exceptionNumberIn => sqlcode,
2974 exceptionStringIn => sqlerrm);
2975 forwardingBehaviorValuesOut := ame_util.emptyStringList;
2976 forwardingBehaviorLabelsOut := ame_util.emptyStringList;
2977 raise;
2978 end getForwardingBehaviorList;
2979 procedure getItemClassList(applicationIdIn in integer,
2980 itemClassIdListOut out nocopy ame_util.idList,
2981 itemClassNameListOut out nocopy ame_util.stringList,
2982 itemClassOrderNumbersOut out nocopy ame_util.idList) as
2983 cursor itemClassCursor(applicationIdIn in integer) is
2984 select ame_item_classes.item_class_id,
2985 ame_item_classes.name,
2986 ame_item_class_usages.item_class_order_number
2987 from ame_item_classes,
2988 ame_item_class_usages
2989 where
2990 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
2991 ame_item_class_usages.application_id = applicationIdIn and
2992 sysdate between ame_item_classes.start_date and
2993 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
2994 sysdate between ame_item_class_usages.start_date and
2995 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
2996 order by ame_item_class_usages.item_class_order_number;
2997 tempIndex integer;
2998 begin
2999 tempIndex := 1;
3000 for tempItemClass in itemClassCursor(applicationIdIn => applicationIdIn) loop
3001 itemClassIdListOut(tempIndex) := tempItemClass.item_class_id;
3002 itemClassNameListOut(tempIndex) := tempItemClass.name;
3003 itemClassOrderNumbersOut(tempIndex) := tempItemClass.item_class_order_number;
3004 tempIndex := tempIndex + 1;
3005 end loop;
3006 exception
3007 when others then
3008 rollback;
3009 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3010 routineNameIn => 'getItemClassList',
3011 exceptionNumberIn => sqlcode,
3012 exceptionStringIn => sqlerrm);
3013 itemClassIdListOut := ame_util.emptyIdList;
3014 itemClassNameListOut := ame_util.emptyStringList;
3015 raise;
3016 end getItemClassList;
3017 /*
3018 AME_STRIPING
3019 procedure getStripeSetIds(applicationIdIn in integer,
3020 stripeSetIdsOut out nocopy ame_util.idList) as
3021 cursor getStripeSetIdsCursor(applicationIdIn in integer) is
3022 select stripe_set_id
3023 from ame_stripe_sets
3024 where
3025 application_id = applicationIdIn and
3026 (start_date <= sysdate and
3027 (end_date is null or sysdate < end_date))
3028 order by stripe_set_id;
3029 tempIndex integer;
3030 begin
3031 tempIndex := 1;
3032 for getStripeSetIdsRec in getStripeSetIdsCursor(applicationIdIn => applicationIdIn) loop
3033 stripeSetIdsOut(tempIndex) := getStripeSetIdsRec.stripe_set_id;
3034 tempIndex := tempIndex + 1;
3035 end loop;
3036 exception
3037 when others then
3038 rollback;
3039 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3040 routineNameIn => 'getStripeSetIds',
3041 exceptionNumberIn => sqlcode,
3042 exceptionStringIn => sqlerrm);
3043 stripeSetIdsOut := ame_util.emptyIdList;
3044 raise;
3045 end getStripeSetIds;
3046 */
3047 /*
3048 AME_STRIPING
3049 procedure getStripeSetList(applicationIdIn in integer,
3050 attributeValues1In in ame_util.stringList default ame_util.emptyStringList,
3051 attributeValues2In in ame_util.stringList default ame_util.emptyStringList,
3052 attributeValues3In in ame_util.stringList default ame_util.emptyStringList,
3053 attributeValues4In in ame_util.stringList default ame_util.emptyStringList,
3054 attributeValues5In in ame_util.stringList default ame_util.emptyStringList,
3055 attributeValues1Out out nocopy ame_util.stringList,
3056 attributeValues2Out out nocopy ame_util.stringList,
3057 attributeValues3Out out nocopy ame_util.stringList,
3058 attributeValues4Out out nocopy ame_util.stringList,
3059 attributeValues5Out out nocopy ame_util.stringList,
3060 stripeSetIdListOut out nocopy ame_util.idList) as
3061 attributeCur ame_util.queryCursor;
3062 attributeValues1Count integer;
3063 attributeValues2Count integer;
3064 attributeValues3Count integer;
3065 attributeValues4Count integer;
3066 attributeValues5Count integer;
3067 constraintValues ame_util.longestStringType;
3068 dynamicQuery ame_util.longestStringType;
3069 stripeSetId integer;
3070 value1 ame_stripe_sets.value_1%type;
3071 value2 ame_stripe_sets.value_2%type;
3072 value3 ame_stripe_sets.value_3%type;
3073 value4 ame_stripe_sets.value_4%type;
3074 value5 ame_stripe_sets.value_5%type;
3075 tempCount integer;
3076 tempIndex integer;
3077 begin
3078 attributeValues1Count := attributeValues1In.count;
3079 if(attributeValues1Count > 0) then
3080 constraintValues := ' and value_1 in (';
3081 for i in 1..attributeValues1Count loop
3082 if i = attributeValues1Count then
3083 constraintValues := constraintValues ||''''|| attributeValues1In(i) || ''')';
3084 else
3085 constraintValues := constraintValues ||''''|| attributeValues1In(i) ||''',';
3086 end if;
3087 end loop;
3088 end if;
3089 attributeValues2Count := attributeValues2In.count;
3090 if(attributeValues2Count > 0) then
3091 constraintValues := constraintValues ||' and value_2 in (';
3092 for i in 1..attributeValues2Count loop
3093 if i = attributeValues2Count then
3094 constraintValues := constraintValues ||''''|| attributeValues2In(i) ||''')';
3095 else
3096 constraintValues := constraintValues ||''''|| attributeValues2In(i) ||''',';
3097 end if;
3098 end loop;
3099 end if;
3100 attributeValues3Count := attributeValues3In.count;
3101 if(attributeValues3Count > 0) then
3102 constraintValues := constraintValues ||' and value_3 in (';
3103 for i in 1..attributeValues3Count loop
3104 if i = attributeValues3Count then
3105 constraintValues := constraintValues ||''''|| attributeValues3In(i) ||''')';
3106 else
3107 constraintValues := constraintValues ||''''|| attributeValues3In(i) ||''',';
3108 end if;
3109 end loop;
3110 end if;
3111 attributeValues4Count := attributeValues4In.count;
3112 if(attributeValues4Count > 0) then
3113 constraintValues := constraintValues ||' and value_4 in (';
3114 for i in 1..attributeValues4Count loop
3115 if i = attributeValues4Count then
3116 constraintValues := constraintValues ||''''|| attributeValues4In(i) ||''')';
3117 else
3118 constraintValues := constraintValues ||''''|| attributeValues4In(i) ||''',';
3119 end if;
3120 end loop;
3121 end if;
3122 attributeValues5Count := attributeValues5In.count;
3123 if(attributeValues5Count > 0) then
3124 constraintValues := constraintValues ||' and value_5 in (';
3125 for i in 1..attributeValues5Count loop
3126 if i = attributeValues5Count then
3127 constraintValues := constraintValues ||''''|| attributeValues5In(i) ||''')';
3128 else
3129 constraintValues := constraintValues ||''''|| attributeValues5In(i) ||''',';
3130 end if;
3131 end loop;
3132 end if;
3133 dynamicQuery :=
3134 'select stripe_set_id,
3135 value_1,
3136 value_2,
3137 value_3,
3138 value_4,
3139 value_5' ||
3140 ' from ame_stripe_sets where application_id = ' ||
3141 applicationIdIn ||
3142 constraintValues ||
3143 ' and stripe_set_id <> 0 and start_date <= sysdate and (end_date is null or sysdate < end_date)';
3144 attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
3145 tempIndex := 1;
3146 loop
3147 fetch attributeCur
3148 into stripeSetId,
3149 value1,
3150 value2,
3151 value3,
3152 value4,
3153 value5;
3154 exit when attributeCur%notfound;
3155 stripeSetIdListOut(tempIndex) := stripeSetId;
3156 attributeValues1Out(tempIndex) := value1;
3157 attributeValues2Out(tempIndex) := value2;
3158 attributeValues3Out(tempIndex) := value3;
3159 attributeValues4Out(tempIndex) := value4;
3160 attributeValues5Out(tempIndex) := value5;
3161 tempIndex := tempIndex + 1;
3162 end loop;
3163 close attributeCur;
3164 exception
3165 when others then
3166 rollback;
3167 ame_util.runtimeException(packageNamein => 'ame_admin_pkg',
3168 routineNamein => 'getStripeSetList',
3169 exceptionNumberIn => sqlcode,
3170 exceptionStringIn => sqlerrm);
3171 attributeValues1Out := ame_util.emptyStringList;
3172 attributeValues2Out := ame_util.emptyStringList;
3173 attributeValues3Out := ame_util.emptyStringList;
3174 attributeValues4Out := ame_util.emptyStringList;
3175 attributeValues5Out := ame_util.emptyStringList;
3176 stripeSetIdListOut := ame_util.emptyIdList;
3177 end getStripeSetList;
3178 */
3179 /*
3180 AME_STRIPING
3181 procedure getStripingAttributeIds(applicationIdIn in integer,
3182 stripingAttributeIdsOut out nocopy ame_util.idList) as
3183 valueColumns ame_util.idList;
3184 begin
3185 select
3186 to_number(value_1),
3187 to_number(value_2),
3188 to_number(value_3),
3189 to_number(value_4),
3190 to_number(value_5)
3191 into
3192 valueColumns(1),
3193 valueColumns(2),
3194 valueColumns(3),
3195 valueColumns(4),
3196 valueColumns(5)
3197 from ame_stripe_sets
3198 where
3199 application_id = applicationIdIn and
3200 stripe_set_id = 0 and
3201 (start_date <= sysdate and
3202 (end_date is null or sysdate < end_date));
3203 for i in 1 .. 5 loop
3204 if(valueColumns(i) is null) then
3205 exit;
3206 end if;
3207 stripingAttributeIdsOut(i) := valueColumns(i);
3208 end loop;
3209 exception
3210 when others then
3211 rollback;
3212 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3213 routineNameIn => 'getStripingAttributeIds',
3214 exceptionNumberIn => sqlcode,
3215 exceptionStringIn => sqlerrm);
3216
3217 stripingAttributeIdsOut := ame_util.emptyIdList;
3218 raise;
3219 end getStripingAttributeIds;
3220 */
3221 /*
3222 AME_STRIPING
3223 procedure getStripingAttributeNames(applicationIdIn in integer,
3224 stripingAttributeNamesOut out nocopy ame_util.stringList) as
3225 valueColumns ame_util.idList;
3226 begin
3227 select
3228 to_number(value_1),
3229 to_number(value_2),
3230 to_number(value_3),
3231 to_number(value_4),
3232 to_number(value_5)
3233 into
3234 valueColumns(1),
3235 valueColumns(2),
3236 valueColumns(3),
3237 valueColumns(4),
3238 valueColumns(5)
3239 from ame_stripe_sets
3240 where
3241 application_id = applicationIdIn and
3242 stripe_set_id = 0 and
3243 (start_date <= sysdate and
3244 (end_date is null or sysdate < end_date));
3245 for i in 1 .. 5 loop
3246 if(valueColumns(i) is null) then
3247 exit;
3248 end if;
3249 stripingAttributeNamesOut(i) := ame_attribute_pkg.getName(attributeIdIn => valueColumns(i));
3250 end loop;
3251 exception
3252 when others then
3253 rollback;
3254 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3255 routineNameIn => 'getStripingAttributeNames',
3256 exceptionNumberIn => sqlcode,
3257 exceptionStringIn => sqlerrm);
3258 stripingAttributeNamesOut := ame_util.emptyStringList;
3259 raise;
3260 end getStripingAttributeNames;
3261 */
3262 /*
3263 AME_STRIPING
3264 procedure getStripingAttributeValues(applicationIdIn in integer,
3265 stripingAttributeIdsOut out nocopy ame_util.stringList,
3266 stripingAttributeNamesOut out nocopy ame_util.stringList,
3267 allowedStripeValues1Out out nocopy ame_util.stringList,
3268 allowedStripeValues2Out out nocopy ame_util.stringList,
3269 allowedStripeValues3Out out nocopy ame_util.stringList,
3270 allowedStripeValues4Out out nocopy ame_util.stringList,
3271 allowedStripeValues5Out out nocopy ame_util.stringList) as
3272 attributeCur ame_util.queryCursor;
3273 attributeIds ame_util.idList;
3274 dynamicCursor integer;
3275 dynamicQuery ame_util.longestStringType;
3276 tempIndex integer;
3277 tempValue1 ame_stripe_sets.value_1%type;
3278 tempValueList ame_util.stringList;
3279 upperLimit integer;
3280 upperLimit2 integer;
3281 begin
3282 select value_1,
3283 value_2,
3284 value_3,
3285 value_4,
3286 value_5
3287 into
3288 attributeIds(1),
3289 attributeIds(2),
3290 attributeIds(3),
3291 attributeIds(4),
3292 attributeIds(5)
3293 from ame_stripe_sets
3294 where
3295 application_id = applicationIdIn and
3296 stripe_set_id = 0 and
3297 (start_date <= sysdate and
3298 (end_date is null or sysdate < end_date));
3299 for i in 1 .. 5 loop
3300 if(attributeIds(i) is null) then
3301 exit;
3302 end if;
3303 stripingAttributeIdsOut(i) := attributeIds(i);
3304 stripingAttributeNamesOut(i) := ame_attribute_pkg.getName(attributeIdIn => attributeIds(i));
3305 end loop;
3306 upperLimit := stripingAttributeIdsOut.count;
3307 for i in 1 .. upperLimit loop
3308 tempValueList.delete;
3309 dynamicQuery :=
3310 'select distinct(nvl(value_' ||
3311 i || ', ''NULL''))' ||
3312 ' from ame_stripe_sets where application_id = ' ||
3313 applicationIdIn ||
3314 ' and stripe_set_id <> 0 and start_date <= sysdate and (end_date is null or sysdate < end_date)';
3315 attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
3316 tempIndex := 1;
3317 loop
3318 fetch attributeCur into tempValue1;
3319 exit when attributeCur%notfound;
3320 tempValueList(tempIndex) := tempValue1;
3321 tempIndex := tempIndex + 1;
3322 end loop;
3323 close attributeCur;
3324 upperLimit2 := tempValueList.count;
3325 if(i = 1) then
3326 for j in 1 .. upperLimit2 loop
3327 allowedStripeValues1Out(j) := tempValueList(j);
3328 end loop;
3329 elsif(i = 2) then
3330 for j in 1 .. upperLimit2 loop
3331 allowedStripeValues2Out(j) := tempValueList(j);
3332 end loop;
3333 elsif(i = 3) then
3334 for j in 1 .. upperLimit2 loop
3335 allowedStripeValues3Out(j) := tempValueList(j);
3336 end loop;
3337 elsif(i = 4) then
3338 for j in 1 .. upperLimit2 loop
3339 allowedStripeValues4Out(j) := tempValueList(j);
3340 end loop;
3341 elsif(i = 5) then
3342 for j in 1 .. upperLimit2 loop
3343 allowedStripeValues5Out(j) := tempValueList(j);
3344 end loop;
3345 end if;
3346 end loop;
3347 exception
3348 when others then
3349 rollback;
3350 if(dbms_sql.is_open(dynamicCursor)) then
3351 dbms_sql.close_cursor(dynamicCursor);
3352 end if;
3353 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3354 routineNameIn => 'getStripingAttributeValues',
3355 exceptionNumberIn => sqlcode,
3356 exceptionStringIn => sqlerrm);
3357 stripingAttributeIdsOut := ame_util.emptyStringList;
3358 stripingAttributeNamesOut := ame_util.emptyStringList;
3359 allowedStripeValues1Out := ame_util.emptyStringList;
3360 allowedStripeValues2Out := ame_util.emptyStringList;
3361 allowedStripeValues3Out := ame_util.emptyStringList;
3362 allowedStripeValues4Out := ame_util.emptyStringList;
3363 allowedStripeValues5Out := ame_util.emptyStringList;
3364 raise;
3365 end getStripingAttributeValues;
3366 */
3367 /*
3368 procedure getStripingAttributeValues2(applicationIdIn in integer,
3369 stripeSetIdIn in integer,
3370 stripingAttributeIdsOut out nocopy ame_util.stringList,
3371 stripingAttributeNamesOut out nocopy ame_util.stringList,
3372 stripeValue1Out out nocopy varchar2,
3373 stripeValue2Out out nocopy varchar2,
3374 stripeValue3Out out nocopy varchar2,
3375 stripeValue4Out out nocopy varchar2,
3376 stripeValue5Out out nocopy varchar2) as
3377 attributeIds ame_util.idList;
3378 attributeCur ame_util.queryCursor;
3379 dynamicCursor integer;
3380 dynamicQuery ame_util.longestStringType;
3381 errorCode integer;
3382 errorMessage varchar2(200);
3383 tempIndex integer;
3384 tempValue1 ame_stripe_sets.value_1%type;
3385 tempValueList ame_util.stringList;
3386 upperLimit integer;
3387 begin
3388 select value_1,
3389 value_2,
3390 value_3,
3391 value_4,
3392 value_5
3393 into
3394 attributeIds(1),
3395 attributeIds(2),
3396 attributeIds(3),
3397 attributeIds(4),
3398 attributeIds(5)
3399 from ame_stripe_sets
3400 where
3401 application_id = applicationIdIn and
3402 stripe_set_id = 0 and
3403 (start_date <= sysdate and
3404 (end_date is null or sysdate < end_date));
3405 for i in 1 .. 5 loop
3406 if(attributeIds(i) is null) then
3407 exit;
3408 end if;
3409 stripingAttributeIdsOut(i) := attributeIds(i);
3410 stripingAttributeNamesOut(i) := ame_attribute_pkg.getName(attributeIdIn => attributeIds(i));
3411 end loop;
3412 upperLimit := stripingAttributeIdsOut.count;
3413 for i in 1 .. upperLimit loop
3414 tempValueList.delete;
3415 dynamicQuery :=
3416 'select value_' ||
3417 i ||
3418 ' from ame_stripe_sets where application_id = ' ||
3419 applicationIdIn ||
3420 ' and stripe_set_id = ' || stripeSetIdIn || ' and start_date <= sysdate and (end_date is null or sysdate < end_date)';
3421 attributeCur := getAttributeQuery(selectClauseIn => dynamicQuery);
3422 tempIndex := 1;
3423 loop
3424 fetch attributeCur into tempValue1;
3425 exit when attributeCur%notfound;
3426 tempValueList(tempIndex) := tempValue1;
3427 tempIndex := tempIndex + 1;
3428 end loop;
3429 close attributeCur;
3430 if(i = 1) then
3431 stripeValue1Out := tempValueList(1);
3432 elsif(i = 2) then
3433 stripeValue2Out := tempValueList(1);
3434 elsif(i = 3) then
3435 stripeValue3Out := tempValueList(1);
3436 elsif(i = 4) then
3437 stripeValue4Out := tempValueList(1);
3438 elsif(i = 5) then
3439 stripeValue5Out := tempValueList(1);
3440 end if;
3441 end loop;
3442 exception
3443 when others then
3444 rollback;
3445 if(dbms_sql.is_open(dynamicCursor)) then
3446 dbms_sql.close_cursor(dynamicCursor);
3447 end if;
3448 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3449 routineNameIn => 'getStripingAttributeValues2',
3450 exceptionNumberIn => sqlcode,
3451 exceptionStringIn => sqlerrm);
3452 stripingAttributeIdsOut := ame_util.emptyStringList;
3453 stripingAttributeNamesOut := ame_util.emptyStringList;
3454 stripeValue1Out := null;
3455 stripeValue2Out := null;
3456 stripeValue3Out := null;
3457 stripeValue4Out := null;
3458 stripeValue5Out := null;
3459 raise;
3460 end getStripingAttributeValues2;
3461 */
3462 /*
3463 AME_STRIPING
3464 procedure getStripingAttributeValues3(applicationIdIn in integer,
3465 stripeSetIdIn in integer,
3466 stripeValue1Out out nocopy varchar2,
3467 stripeValue2Out out nocopy varchar2,
3468 stripeValue3Out out nocopy varchar2,
3469 stripeValue4Out out nocopy varchar2,
3470 stripeValue5Out out nocopy varchar2) as
3471 begin
3472 select
3473 value_1,
3474 value_2,
3475 value_3,
3476 value_4,
3477 value_5
3478 into
3479 stripeValue1Out,
3480 stripeValue2Out,
3481 stripeValue3Out,
3482 stripeValue4Out,
3483 stripeValue5Out
3484 from ame_stripe_sets
3485 where
3486 application_id = applicationIdIn and
3487 stripe_set_id = stripeSetIdIn and
3488 (start_date <= sysdate and (end_date is null or sysdate < end_date));
3489 exception
3490 when others then
3491 rollback;
3492 stripeValue1Out := null;
3493 stripeValue2Out := null;
3494 stripeValue3Out := null;
3495 stripeValue4Out := null;
3496 stripeValue5Out := null;
3497 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3498 routineNameIn => 'getStripingAttributeValues3',
3499 exceptionNumberIn => sqlcode,
3500 exceptionStringIn => sqlerrm);
3501 raise;
3502 end getStripingAttributeValues3;
3503 */
3504 procedure getTransExceptions(applicationIdIn in integer,
3505 transactionIdIn in varchar2,
3506 exceptionLogOut out nocopy ame_util.exceptionLogTable) as
3507 cursor exceptionLogCursor is
3508 select *
3509 from ame_exceptions_log
3510 where
3511 application_id = applicationIdIn and
3512 transaction_id = transactionIdIn
3513 order by log_id desc;
3514 logLength integer;
3515 tempIndex integer;
3516 workflowLog ame_util.workflowLogTable;
3517 begin
3518 tempIndex := 1;
3519 /* Fetch local log. */
3520 for tempLog in exceptionLogCursor loop
3521 exceptionLogOut(tempIndex).log_id := tempLog.log_id;
3522 exceptionLogOut(tempIndex).package_name := tempLog.package_name;
3523 exceptionLogOut(tempIndex).routine_name := tempLog.routine_name;
3524 exceptionLogOut(tempIndex).transaction_id := tempLog.transaction_id;
3525 exceptionLogOut(tempIndex).application_id := tempLog.application_id;
3526 exceptionLogOut(tempIndex).exception_number := tempLog.exception_number;
3527 exceptionLogOut(tempIndex).exception_string := tempLog.exception_string;
3528 tempIndex := tempIndex + 1;
3529 end loop;
3530 /*
3531 If the log is in the Workflow table and it's not in the AME table,
3532 then fetch the log from Workflow.
3533 */
3534 if(ame_util.useWorkflow(transactionIdIn => transactionIdIn,
3535 applicationIdIn => applicationIdIn) and
3536 ame_util.getConfigVar(variableNameIn => ame_util.distEnvConfigVar) = ame_util.yes) then
3537 getWorkflowLog(applicationIdIn => applicationIdIn,
3538 transactionIdIn => transactionIdIn,
3539 logOut => workflowLog);
3540 logLength := workflowLog.count;
3541 for i in 1 .. logLength loop
3542 exceptionLogOut(tempIndex).log_id := workflowLog(i).log_id;
3543 exceptionLogOut(tempIndex).package_name := workflowLog(i).package_name;
3544 exceptionLogOut(tempIndex).routine_name := workflowLog(i).routine_name;
3545 exceptionLogOut(tempIndex).transaction_id := workflowLog(i).transaction_id;
3546 exceptionLogOut(tempIndex).application_id := applicationIdIn;
3547 exceptionLogOut(tempIndex).exception_number := workflowLog(i).exception_number;
3548 exceptionLogOut(tempIndex).exception_string := workflowLog(i).exception_string;
3549 tempIndex := tempIndex + 1;
3550 end loop;
3551 end if;
3552 exception
3553 when others then
3554 rollback;
3555 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3556 routineNameIn => 'getTransExceptions',
3557 exceptionNumberIn => sqlcode,
3558 exceptionStringIn => sqlerrm);
3559 exceptionLogOut := ame_util.emptyExceptionLogTable;
3560 raise;
3561 end getTransExceptions;
3562 procedure getTransTypeExceptions1(applicationIdIn in integer,
3563 exceptionLogOut out nocopy ame_util.exceptionLogTable) as
3564 cursor exceptionLogCursor(applicationIdIn in integer) is
3565 select *
3566 from ame_exceptions_log
3567 where application_id = applicationIdIn
3568 order by log_id desc;
3569 logLength integer;
3570 tempIndex integer;
3571 workflowLog ame_util.workflowLogTable;
3572 begin
3573 tempIndex := 1;
3574 /* Always fetch the log in the AME table. */
3575 for tempLog in exceptionLogCursor(applicationIdIn => applicationIdIn) loop
3576 exceptionLogOut(tempIndex).log_id := tempLog.log_id;
3577 exceptionLogOut(tempIndex).package_name := tempLog.package_name;
3578 exceptionLogOut(tempIndex).routine_name := tempLog.routine_name;
3579 exceptionLogOut(tempIndex).transaction_id := tempLog.transaction_id;
3580 exceptionLogOut(tempIndex).application_id := tempLog.application_id;
3581 exceptionLogOut(tempIndex).exception_number := tempLog.exception_number;
3582 exceptionLogOut(tempIndex).exception_string := tempLog.exception_string;
3583 tempIndex := tempIndex + 1;
3584 end loop;
3585 /*
3586 If the log is in the Workflow table and it's not in the AME table,
3587 then fetch the log from Workflow.
3588 */
3589 if(ame_util.useWorkflow(applicationIdIn => applicationIdIn) and
3590 ame_util.getConfigVar(variableNameIn => ame_util.distEnvConfigVar) = ame_util.yes) then
3591 getWorkflowLog(applicationIdIn => applicationIdIn,
3592 transactionIdIn => null,
3593 logOut => workflowLog);
3594 logLength := workflowLog.count;
3595 for i in 1 .. logLength loop
3596 exceptionLogOut(tempIndex).log_id := workflowLog(i).log_id;
3597 exceptionLogOut(tempIndex).package_name := workflowLog(i).package_name;
3598 exceptionLogOut(tempIndex).routine_name := workflowLog(i).routine_name;
3599 exceptionLogOut(tempIndex).transaction_id := workflowLog(i).transaction_id;
3600 exceptionLogOut(tempIndex).application_id := applicationIdIn;
3601 exceptionLogOut(tempIndex).exception_number := workflowLog(i).exception_number;
3602 exceptionLogOut(tempIndex).exception_string := workflowLog(i).exception_string;
3603 tempIndex := tempIndex + 1;
3604 end loop;
3605 end if;
3606 exception
3607 when others then
3608 rollback;
3609 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3610 routineNameIn => 'getTransTypeExceptions1',
3611 exceptionNumberIn => sqlcode,
3612 exceptionStringIn => sqlerrm);
3613 exceptionLogOut := ame_util.emptyExceptionLogTable;
3614 raise;
3615 end getTransTypeExceptions1;
3616 procedure getTransTypeExceptions2(applicationIdIn in integer,
3617 exceptionLogOut out nocopy ame_util.exceptionLogTable) as
3618 cursor exceptionLogCursor is
3619 select *
3620 from ame_exceptions_log
3621 where application_id = applicationIdIn
3622 order by
3623 package_name,
3624 routine_name;
3625 tempIndex integer;
3626 logLength integer;
3627 workflowLog ame_util.workflowLogTable;
3628 begin
3629 tempIndex := 1;
3630 /* Fetch local log. */
3631 for tempLog in exceptionLogCursor loop
3632 exceptionLogOut(tempIndex).log_id := tempLog.log_id;
3633 exceptionLogOut(tempIndex).package_name := tempLog.package_name;
3634 exceptionLogOut(tempIndex).routine_name := tempLog.routine_name;
3635 exceptionLogOut(tempIndex).transaction_id := tempLog.transaction_id;
3636 exceptionLogOut(tempIndex).application_id := tempLog.application_id;
3637 exceptionLogOut(tempIndex).exception_number := tempLog.exception_number;
3638 exceptionLogOut(tempIndex).exception_string := tempLog.exception_string;
3639 tempIndex := tempIndex + 1;
3640 end loop;
3641 /*
3642 If the log is in the Workflow table and it's not in the AME table,
3643 then fetch the log from Workflow.
3644 */
3645 if(ame_util.useWorkflow(applicationIdIn => applicationIdIn) and
3646 ame_util.getConfigVar(variableNameIn => ame_util.distEnvConfigVar) = ame_util.yes) then
3647 getWorkflowLog(applicationIdIn => applicationIdIn,
3648 transactionIdIn => null,
3649 logOut => workflowLog);
3650 logLength := workflowLog.count;
3651 for i in 1 .. logLength loop
3652 exceptionLogOut(tempIndex).log_id := workflowLog(i).log_id;
3653 exceptionLogOut(tempIndex).package_name := workflowLog(i).package_name;
3654 exceptionLogOut(tempIndex).routine_name := workflowLog(i).routine_name;
3655 exceptionLogOut(tempIndex).transaction_id := workflowLog(i).transaction_id;
3656 exceptionLogOut(tempIndex).application_id := applicationIdIn;
3657 exceptionLogOut(tempIndex).exception_number := workflowLog(i).exception_number;
3658 exceptionLogOut(tempIndex).exception_string := workflowLog(i).exception_string;
3659 tempIndex := tempIndex + 1;
3660 end loop;
3661 end if;
3662 exception
3663 when others then
3664 rollback;
3665 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3666 routineNameIn => 'getTransTypeExceptions2',
3667 exceptionNumberIn => sqlcode,
3668 exceptionStringIn => sqlerrm);
3669 exceptionLogOut := ame_util.emptyExceptionLogTable;
3670 raise;
3671 end getTransTypeExceptions2;
3672 procedure getTransactionTypes(applicationIdsOut out nocopy ame_util.idList,
3673 applicationNamesOut out nocopy ame_util.stringList,
3674 transactionTypesOut out nocopy ame_util.stringList,
3675 createdByOut out nocopy ame_util.idList) as
3676 cursor applicationsCursor is
3677 select
3678 fnd_application_id,
3679 application_id,
3680 created_by,
3681 application_name,
3682 transaction_type_id
3683 from
3684 ame_calling_apps
3685 where
3686 sysdate between start_date and
3687 nvl(end_date - ame_util.oneSecond, sysdate)
3688 order by application_name;
3689 tempIndex integer;
3690 begin
3691 tempIndex := 0;
3692 for tempTransType in applicationsCursor loop
3693 tempIndex := tempIndex + 1;
3694 applicationIdsOut(tempIndex) := tempTransType.application_id;
3695 applicationNamesOut(tempIndex) := tempTransType.application_name;
3696 transactionTypesOut(tempIndex) := tempTransType.transaction_type_id;
3697 createdByOut(tempIndex) := tempTransType.created_by;
3698 end loop;
3699 exception
3700 when others then
3701 rollback;
3702 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3703 routineNameIn => 'getTransactionTypes',
3704 exceptionNumberIn => sqlcode,
3705 exceptionStringIn => sqlerrm);
3706 applicationIdsOut := ame_util.emptyIdList;
3707 applicationNamesOut := ame_util.emptyStringList;
3708 transactionTypesOut := ame_util.emptyStringList;
3709 createdByOut := ame_util.emptyIdList;
3710 raise;
3711 end getTransactionTypes;
3712 procedure getTransTypeItemClasses(applicationIdIn in integer,
3713 itemClassIdsOut out nocopy ame_util.stringList,
3714 itemClassNamesOut out nocopy ame_util.stringList) as
3715 cursor getItemClassesCursor(applicationIdIn in integer) is
3716 select ame_item_classes.item_class_id,
3717 ame_item_classes.name
3718 from ame_item_classes,
3719 ame_item_class_usages
3720 where
3721 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
3722 ame_item_class_usages.application_id = applicationIdIn and
3723 sysdate between ame_item_classes.start_date and
3724 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
3725 sysdate between ame_item_class_usages.start_date and
3726 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
3727 order by ame_item_class_usages.item_class_order_number;
3728 begin
3729 open getItemClassesCursor(applicationIdIn => applicationIdIn);
3730 fetch getItemClassesCursor bulk collect
3731 into itemClassIdsOut,
3732 itemClassNamesOut;
3733 close getItemClassesCursor;
3734 exception
3735 when others then
3736 rollback;
3737 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3738 routineNameIn => 'getTransTypeItemClasses',
3739 exceptionNumberIn => sqlcode,
3740 exceptionStringIn => sqlerrm);
3741 itemClassIdsOut := ame_util.emptyStringList;
3742 itemClassNamesOut := ame_util.emptyStringList;
3743 raise;
3744 end getTransTypeItemClasses;
3745 procedure getTransTypeItemClasses2(applicationIdIn in integer,
3746 itemClassIdsOut out nocopy ame_util.idList,
3747 itemClassNamesOut out nocopy ame_util.stringList) as
3748 cursor getItemClassesCursor(applicationIdIn in integer) is
3749 select ame_item_classes.item_class_id,
3750 ame_item_classes.name
3751 from ame_item_classes,
3752 ame_item_class_usages
3753 where
3754 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
3755 ame_item_class_usages.application_id = applicationIdIn and
3756 ame_item_classes.name <> ame_util.headerItemClassName and
3757 sysdate between ame_item_classes.start_date and
3758 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
3759 sysdate between ame_item_class_usages.start_date and
3760 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
3761 order by ame_item_class_usages.item_class_order_number;
3762 begin
3763 open getItemClassesCursor(applicationIdIn => applicationIdIn);
3764 fetch getItemClassesCursor bulk collect
3765 into itemClassIdsOut,
3766 itemClassNamesOut;
3767 close getItemClassesCursor;
3768 exception
3769 when others then
3770 rollback;
3771 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3772 routineNameIn => 'getTransTypeItemClasses2',
3773 exceptionNumberIn => sqlcode,
3774 exceptionStringIn => sqlerrm);
3775 itemClassIdsOut := ame_util.emptyIdList;
3776 itemClassNamesOut := ame_util.emptyStringList;
3777 raise;
3778 end getTransTypeItemClasses2;
3779 procedure getTransTypeItemClasses3(applicationIdIn in integer,
3780 itemClassIdsOut out nocopy ame_util.idList,
3781 itemClassNamesOut out nocopy ame_util.stringList) as
3782 cursor getItemClassesCursor(applicationIdIn in integer) is
3783 select ame_item_classes.item_class_id,
3784 ame_item_classes.name
3785 from ame_item_classes,
3786 ame_item_class_usages
3787 where
3788 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
3789 ame_item_class_usages.application_id = applicationIdIn and
3790 sysdate between ame_item_classes.start_date and
3791 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
3792 sysdate between ame_item_class_usages.start_date and
3793 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
3794 order by ame_item_class_usages.item_class_order_number,
3795 ame_item_classes.name;
3796 begin
3797 open getItemClassesCursor(applicationIdIn => applicationIdIn);
3798 fetch getItemClassesCursor bulk collect
3799 into itemClassIdsOut,
3800 itemClassNamesOut;
3801 close getItemClassesCursor;
3802 exception
3803 when others then
3804 rollback;
3805 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3806 routineNameIn => 'getTransTypeItemClasses3',
3807 exceptionNumberIn => sqlcode,
3808 exceptionStringIn => sqlerrm);
3809 itemClassIdsOut := ame_util.emptyIdList;
3810 itemClassNamesOut := ame_util.emptyStringList;
3811 raise;
3812 end getTransTypeItemClasses3;
3813 procedure getTransTypeItemClasses4(applicationIdIn in integer,
3814 itemClassIdsOut out nocopy ame_util.stringList,
3815 itemClassNamesOut out nocopy ame_util.stringList) as
3816 cursor getItemClassesCursor(applicationIdIn in integer) is
3817 select ame_item_classes.item_class_id,
3818 ame_item_classes.name
3819 from ame_item_classes,
3820 ame_item_class_usages
3821 where
3822 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
3823 ame_item_class_usages.application_id = applicationIdIn and
3824 ame_item_classes.name <> ame_util.headerItemClassName and
3825 sysdate between ame_item_classes.start_date and
3826 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
3827 sysdate between ame_item_class_usages.start_date and
3828 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
3829 order by ame_item_class_usages.item_class_order_number;
3830 begin
3831 open getItemClassesCursor(applicationIdIn => applicationIdIn);
3832 fetch getItemClassesCursor bulk collect
3833 into itemClassIdsOut,
3834 itemClassNamesOut;
3835 close getItemClassesCursor;
3836 exception
3837 when others then
3838 rollback;
3839 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3840 routineNameIn => 'getTransTypeItemClasses4',
3841 exceptionNumberIn => sqlcode,
3842 exceptionStringIn => sqlerrm);
3843 itemClassIdsOut := ame_util.emptyStringList;
3844 itemClassNamesOut := ame_util.emptyStringList;
3845 raise;
3846 end getTransTypeItemClasses4;
3847 procedure getTransTypeItemClassIds(applicationIdIn in integer,
3848 itemClassIdsOut out nocopy ame_util.idList) as
3849 cursor getItemClassesCursor(applicationIdIn in integer) is
3850 select ame_item_classes.item_class_id
3851 from ame_item_classes,
3852 ame_item_class_usages
3853 where
3854 ame_item_classes.item_class_id = ame_item_class_usages.item_class_id and
3855 ame_item_class_usages.application_id = applicationIdIn and
3856 sysdate between ame_item_classes.start_date and
3857 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate) and
3858 sysdate between ame_item_class_usages.start_date and
3859 nvl(ame_item_class_usages.end_date - ame_util.oneSecond, sysdate)
3860 order by ame_item_class_usages.item_class_order_number;
3861 begin
3862 open getItemClassesCursor(applicationIdIn => applicationIdIn);
3863 fetch getItemClassesCursor bulk collect
3864 into itemClassIdsOut;
3865 close getItemClassesCursor;
3866 exception
3867 when others then
3868 rollback;
3869 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3870 routineNameIn => 'getTransTypeItemClassIds',
3871 exceptionNumberIn => sqlcode,
3872 exceptionStringIn => sqlerrm);
3873 itemClassIdsOut := ame_util.emptyIdList;
3874 raise;
3875 end getTransTypeItemClassIds;
3876 procedure getWebExceptions(exceptionLogOut out nocopy ame_util.exceptionLogTable) as
3877 cursor exceptionLogCursor is
3878 select
3879 log_id,
3880 package_name,
3881 routine_name,
3882 exception_number,
3883 exception_string
3884 from ame_exceptions_log
3885 where
3886 transaction_id is null and
3887 application_id is null
3888 order by log_id desc;
3889 tempIndex integer;
3890 begin
3891 tempIndex := 1;
3892 for tempLog in exceptionLogCursor loop
3893 exceptionLogOut(tempIndex).log_id := tempLog.log_id;
3894 exceptionLogOut(tempIndex).package_name := tempLog.package_name;
3895 exceptionLogOut(tempIndex).routine_name := tempLog.routine_name;
3896 exceptionLogOut(tempIndex).exception_number := tempLog.exception_number;
3897 exceptionLogOut(tempIndex).exception_string := tempLog.exception_string;
3898 tempIndex := tempIndex + 1;
3899 end loop;
3900 exception
3901 when others then
3902 rollback;
3903 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
3904 routineNameIn => 'getWebExceptions',
3905 exceptionNumberIn => sqlcode,
3906 exceptionStringIn => sqlerrm);
3907 exceptionLogOut := ame_util.emptyExceptionLogTable;
3908 raise;
3909 end getWebExceptions;
3910 procedure getWorkflowLog(applicationIdIn in integer,
3911 transactionIdIn in varchar2 default null,
3912 logOut out nocopy ame_util.workflowLogTable) as
3913 type logCursorReturnType is record(
3914 item_key wf_item_activity_statuses.item_key%type,
3915 error_name wf_item_activity_statuses.error_name%type,
3916 error_message wf_item_activity_statuses.error_message%type,
3917 error_stack wf_item_activity_statuses.error_stack%type);
3918 type logCursorType is ref cursor return logCursorReturnType;
3919 badCallException exception;
3920 currentCallStart integer;
3921 currentCallDot integer;
3922 currentCallLeftParen integer;
3923 currentCallRightParen integer;
3924 errorCode integer;
3925 errorMessage ame_util.longestStringType;
3926 errorStackLength integer;
3927 fndAppId integer;
3928 logCursor logCursorType;
3929 logCursorValues logCursorReturnType;
3930 tempIndex integer;
3931 transactionTypeId ame_calling_apps.transaction_type_id%type;
3932 workflowItemKey wf_item_activity_statuses.item_key%type;
3933 workflowItemType wf_item_activity_statuses.item_type%type;
3934 begin
3935 ame_util.getFndApplicationId(applicationIdIn => applicationIdIn,
3936 fndApplicationIdOut => fndAppId,
3937 transactionTypeIdOut => transactionTypeId);
3938 ame_util.getWorkflowAttributeValues(applicationIdIn => applicationIdIn,
3939 transactionIdIn => transactionIdIn,
3940 workflowItemKeyOut => workflowItemKey,
3941 workflowItemTypeOut => workflowItemType);
3942 if(transactionIdIn is null) then
3943 open logCursor for
3944 select
3945 wf_item_activity_statuses.item_key,
3946 wf_item_activity_statuses.error_name,
3947 wf_item_activity_statuses.error_message,
3948 wf_item_activity_statuses.error_stack
3949 from
3950 wf_item_activity_statuses,
3951 wf_items
3952 where
3953 wf_item_activity_statuses.item_type = workflowItemType and
3954 wf_item_activity_statuses.activity_status = 'ERROR' and
3955 wf_item_activity_statuses.error_stack like (wf_core.newline || 'AME_%') and
3956 wf_item_activity_statuses.item_type = wf_items.item_type and
3957 wf_item_activity_statuses.item_key = wf_items.item_key and
3958 wf_items.end_date is null;
3959 else
3960 open logCursor for
3961 select
3962 wf_item_activity_statuses.item_key, /* We need to select this column to use the same cursor. */
3963 wf_item_activity_statuses.error_name,
3964 wf_item_activity_statuses.error_message,
3965 wf_item_activity_statuses.error_stack
3966 from
3967 wf_item_activity_statuses,
3968 wf_items
3969 where
3970 wf_item_activity_statuses.item_type = workflowItemType and
3971 wf_item_activity_statuses.item_key = workflowItemKey and
3972 wf_item_activity_statuses.activity_status = 'ERROR' and
3973 wf_item_activity_statuses.error_stack like (wf_core.newline || 'AME_%') and
3974 wf_item_activity_statuses.item_type = wf_items.item_type and
3975 wf_item_activity_statuses.item_key = wf_items.item_key and
3976 wf_items.end_date is null;
3977 end if;
3978 tempIndex := 1;
3979 loop
3980 fetch logCursor into logCursorValues;
3981 exit when logCursor%notfound;
3982 /*
3983 This code assumes that the error_stack is a sequence of entries of the form
3984 wf_core.newline || [package].[routine]([logId])
3985 which in aggregate look like a call stack (but we don't include a real
3986 argument list between the parentheses, just our log ID). See ame_util.runtimeException
3987 for the code that generates these entries.
3988 */
3989 errorStackLength := lengthb(logCursorValues.error_stack);
3990 currentCallRightParen := 1;
3991 loop
3992 currentCallStart := instrb(logCursorValues.error_stack,
3993 wf_core.newline,
3994 currentCallRightParen,
3995 1) + 1;
3996 if(currentCallStart = 0) then
3997 exit;
3998 end if;
3999 currentCallDot := instrb(logCursorValues.error_stack,
4000 '.',
4001 currentCallStart,
4002 1);
4003 if(currentCallDot = 0) then
4004 raise badCallException;
4005 end if;
4006 currentCallLeftParen := instrb(logCursorValues.error_stack,
4007 '(',
4008 currentCallDot,
4009 1);
4010 if(currentCallLeftParen = 0) then
4011 raise badCallException;
4012 end if;
4013 currentCallRightParen := instrb(logCursorValues.error_stack,
4014 ')',
4015 currentCallLeftParen,
4016 1);
4017 if(currentCallRightParen = 0) then
4018 raise badCallException;
4019 end if;
4020 logOut(tempIndex).package_name := substrb(logCursorValues.error_stack,
4021 currentCallStart,
4022 currentCallDot - currentCallStart);
4023 logOut(tempIndex).routine_name := substrb(logCursorValues.error_stack,
4024 currentCallDot + 1,
4025 currentCallLeftParen - currentCallDot - 1);
4026 logOut(tempIndex).log_id := to_number(substrb(logCursorValues.error_stack,
4027 currentCallLeftParen + 1,
4028 currentCallRightParen - currentCallLeftParen - 1));
4029 /*
4030 For exceptions outside of the Workflow engine, Workflow puts sqlcode in error_name
4031 and sqlerrm in error_message. See the source code for wf_item_activity_status.Set_Error.
4032 */
4033 logOut(tempIndex).transaction_id := substrb(logCursorValues.item_key, 1, 50);
4034 logOut(tempIndex).exception_number := to_number(logCursorValues.error_name);
4035 logOut(tempIndex).exception_string := substrb(logCursorValues.error_message, 1, 4000);
4036 tempIndex := tempIndex + 1;
4037 end loop;
4038 end loop;
4039 close logCursor;
4040 exception
4041 when badCallException then
4042 rollback;
4043 errorCode := -20001;
4044 errorMessage :=
4045 ame_util.getMessage(applicationShortNameIn => 'PER',
4046 messageNameIn => 'AME_400181_ADM_WKFLW_NOT_PRS');
4047 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4048 routineNameIn => 'getWorkflowLog',
4049 exceptionNumberIn => errorCode,
4050 exceptionStringIn => errorMessage);
4051 logOut := ame_util.emptyWorkflowLogTable;
4052 raise_application_error(errorCode,
4053 errorMessage);
4054 when others then
4055 rollback;
4056 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4057 routineNameIn => 'getWorkflowLog',
4058 exceptionNumberIn => sqlcode,
4059 exceptionStringIn => sqlerrm);
4060 logOut := ame_util.emptyWorkflowLogTable;
4061 raise;
4062 end getWorkflowLog;
4063 procedure incrementItemClassOrderNumbers(applicationIdIn in integer,
4064 itemClassIdIn in integer,
4065 orderNumberIn in integer,
4066 finalizeIn in boolean default false) as
4067 cursor orderNumberCursor(applicationIdIn in integer,
4068 itemClassIdIn in integer,
4069 orderNumberIn in integer) is
4070 select item_class_id, item_class_order_number
4071 from ame_item_class_usages
4072 where
4073 application_id = applicationIdIn and
4074 item_class_id <> itemClassIdIn and
4075 item_class_order_number >= orderNumberIn and
4076 sysdate between start_date and
4077 nvl(end_date - ame_util.oneSecond, sysdate)
4078 order by item_class_order_number;
4079 currentUserId integer;
4080 itemClassIds ame_util.idList;
4081 itemClassIdQuery ame_item_class_usages.item_id_query%type;
4082 itemClassParMode ame_item_class_usages.item_class_par_mode%type;
4083 itemClassSublistMode ame_item_class_usages.item_class_sublist_mode%type;
4084 orderNumbers ame_util.idList;
4085 processingDate date;
4086 begin
4087 currentUserId := ame_util.getCurrentUserId;
4088 processingDate := sysdate;
4089 open orderNumberCursor(applicationIdIn => applicationIdIn,
4090 itemClassIdIn => itemClassIdIn,
4091 orderNumberIn => orderNumberIn);
4092 fetch orderNumberCursor bulk collect
4093 into itemClassIds, orderNumbers;
4094 close orderNumberCursor;
4095 for i in 1 .. itemClassIds.count loop
4096 itemClassIdQuery := getItemClassIdQuery(itemClassIdIn => itemClassIds(i),
4097 applicationIdIn => applicationIdIn);
4098 itemClassParMode := getItemClassParMode(itemClassIdIn => itemClassIds(i),
4099 applicationIdIn => applicationIdIn);
4100 itemClassSublistMode := getItemClassSublistMode(itemClassIdIn => itemClassIds(i),
4101 applicationIdIn => applicationIdIn);
4102 update ame_item_class_usages
4103 set
4104 last_updated_by = currentUserId,
4105 last_update_date = processingDate,
4106 last_update_login = currentUserId,
4107 end_date = processingDate
4108 where
4109 application_id = applicationIdIn and
4110 item_class_id = itemClassIds(i) and
4111 sysdate between start_date and
4112 nvl(end_date - ame_util.oneSecond, sysdate);
4113 insert into ame_item_class_usages(application_id,
4114 item_class_id,
4115 item_id_query,
4116 item_class_order_number,
4117 item_class_par_mode,
4118 item_class_sublist_mode,
4119 created_by,
4120 creation_date,
4121 last_updated_by,
4122 last_update_date,
4123 last_update_login,
4124 start_date,
4125 end_date)
4126 values(applicationIdIn,
4127 itemClassIds(i),
4128 itemClassIdQuery,
4129 (orderNumbers(i) + 1),
4130 itemClassParMode,
4131 itemClassSublistMode,
4132 currentUserId,
4133 processingDate,
4134 currentUserId,
4135 processingDate,
4136 currentUserId,
4137 processingDate,
4138 null);
4139 end loop;
4140 if(finalizeIn) then
4141 commit;
4142 end if;
4143 exception
4144 when others then
4145 rollback;
4146 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4147 routineNameIn => 'incrementItemClassOrderNumbers',
4148 exceptionNumberIn => sqlcode,
4149 exceptionStringIn => sqlerrm);
4150 raise;
4151 end incrementItemClassOrderNumbers;
4152 procedure newItemClassUsage(applicationIdIn in integer,
4153 itemClassIdIn in integer,
4154 itemClassParModeIn in varchar2,
4155 itemClassSublistModeIn in varchar2,
4156 itemClassIdQueryIn in varchar2,
4157 orderNumberIn in integer default null,
4158 orderNumberUniqueIn in varchar2 default ame_util.yes,
4159 updateParentObjectIn in boolean,
4160 newStartDateIn in date,
4161 finalizeIn in boolean default false,
4162 parentVersionStartDateIn in date default null) as
4163 cursor startDateCursor is
4164 select start_date
4165 from ame_item_classes
4166 where
4167 item_class_id = itemClassIdIn and
4168 (start_date <= sysdate and
4169 (end_date is null or sysdate < end_date))
4170 for update;
4171 createdBy integer;
4172 currentUserId integer;
4173 dynamicUsageException exception;
4174 endDate date;
4175 errorCode integer;
4176 errorMessage ame_util.longestStringType;
4177 itemId integer;
4178 itemIdQuery ame_item_class_usages.item_id_query%type;
4179 lastUpdatedBy integer;
4180 maxOrderNumber integer;
4181 name ame_item_classes.name%type;
4182 nullQueryStringException exception;
4183 objectVersionNoDataException exception;
4184 orderNumber integer;
4185 startDate date;
4186 stringDynamicException exception;
4187 tempCount integer;
4188 tempCount2 integer;
4189 tempInt integer;
4190 transIdPlaceholderPosition integer;
4191 transIdPlaceholderPosition2 integer;
4192 upperItemIdQuery ame_item_class_usages.item_id_query%type;
4193 upperTransIdPlaceholder ame_util.stringType;
4194 usageExistsException exception;
4195 begin
4196 if(finalizeIn) then
4197 open startDateCursor;
4198 fetch startDateCursor into startDate;
4199 if startDateCursor%notfound then
4200 raise objectVersionNoDataException;
4201 end if;
4202 if(parentVersionStartDateIn <> startDate) then
4203 close startDateCursor;
4204 raise ame_util.objectVersionException;
4205 end if;
4206 end if;
4207 if(orderNumberIn is null) then
4208 orderNumber := 1;
4209 else
4210 orderNumber := orderNumberIn;
4211 end if;
4212 maxOrderNumber :=
4213 ame_admin_pkg.getItemClassMaxOrderNumber(applicationIdIn => applicationIdIn);
4214 itemIdQuery := itemClassIdQueryIn;
4215 if(itemIdQuery is null) then
4216 raise nullQueryStringException;
4217 end if;
4218 if(instrb(itemIdQuery, ';', 1, 1) > 0) or
4219 (instrb(itemIdQuery, '--', 1, 1) > 0) or
4220 (instrb(itemIdQuery, '/*', 1, 1) > 0) or
4221 (instrb(itemIdQuery, '*/', 1, 1) > 0) then
4222 raise stringDynamicException;
4223 end if;
4224 tempInt := 1;
4225 upperItemIdQuery := upper(itemClassIdQueryIn);
4226 upperTransIdPlaceholder := upper(ame_util.transactionIdPlaceholder);
4227 loop
4228 transIdPlaceholderPosition :=
4229 instrb(upperItemIdQuery, upperTransIdPlaceholder, 1, tempInt);
4230 if(transIdPlaceholderPosition = 0) then
4231 exit;
4232 end if;
4233 transIdPlaceholderPosition2 :=
4234 instrb(itemClassIdQueryIn, ame_util.transactionIdPlaceholder, 1, tempInt);
4235 if(transIdPlaceholderPosition <> transIdPlaceholderPosition2) then
4236 raise dynamicUsageException;
4237 end if;
4238 tempInt := tempInt + 1;
4239 end loop;
4240 select count(*)
4241 into tempCount
4242 from ame_item_class_usages
4243 where
4244 item_class_id = itemClassIdIn and
4245 application_id = applicationIdIn and
4246 (start_date <= sysdate and
4247 (end_date is null or sysdate < end_date));
4248 if(tempCount > 0) then
4249 raise usageExistsException;
4250 end if;
4251 currentUserId := ame_util.getCurrentUserId;
4252 select count(*)
4253 into tempCount2
4254 from ame_item_class_usages
4255 where
4256 item_class_id = itemClassIdIn and
4257 application_id = applicationIdIn and
4258 created_by = ame_util.seededDataCreatedById;
4259 if(tempCount2 > 0) then
4260 createdBy := ame_util.seededDataCreatedById;
4261 else
4262 createdBy := currentUserId;
4263 end if;
4264 lastUpdatedBy := currentUserId;
4265 --+ following code has beed added for the bug 5623266
4266 if(createdBy = -1 )then
4267 lastUpdatedBy := ame_util.seededDataCreatedById;
4268 createdBy := ame_util.seededDataCreatedById;
4269 end if;
4270 insert into ame_item_class_usages(application_id,
4271 item_class_id,
4272 item_id_query,
4273 item_class_order_number,
4274 item_class_par_mode,
4275 item_class_sublist_mode,
4276 created_by,
4277 creation_date,
4278 last_updated_by,
4279 last_update_date,
4280 last_update_login,
4281 start_date,
4282 end_date)
4283 values(applicationIdIn,
4284 itemClassIdIn,
4285 itemClassIdQueryIn,
4286 orderNumber,
4287 itemClassParModeIn,
4288 itemClassSublistModeIn,
4289 createdBy,
4290 newStartDateIn,
4291 lastUpdatedBy,
4292 newStartDateIn,
4293 currentUserId,
4294 newStartDateIn,
4295 null);
4296 if(orderNumberUniqueIn = ame_util.yes) then
4297 if(orderNumber <> (maxOrderNumber + 1)) then
4298 incrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
4299 itemClassIdIn => itemClassIdIn,
4300 orderNumberIn => orderNumber);
4301 end if;
4302 end if;
4303 if(finalizeIn) then
4304 if(updateParentObjectIn) then
4305 name := getItemClassName(itemClassIdIn => itemClassIdIn);
4306 endDate := newStartDateIn;
4307 update ame_item_classes
4308 set
4309 last_updated_by = currentUserId,
4310 last_update_date = endDate,
4311 last_update_login = currentUserId,
4312 end_date = endDate
4313 where
4314 item_class_id = itemClassIdIn and
4315 sysdate between start_date and
4316 nvl(end_date - ame_util.oneSecond, sysdate);
4317 itemId := newItemClass(itemClassNameIn => name,
4318 itemClassIdIn => itemClassIdIn,
4319 newStartDateIn => newStartDateIn,
4320 finalizeIn => false);
4321 close startDateCursor;
4322 end if;
4323 commit;
4324 end if;
4325 exception
4326 when ame_util.objectVersionException then
4327 rollback;
4328 if(startDateCursor%isOpen) then
4329 close startDateCursor;
4330 end if;
4331 errorCode := -20001;
4332 errorMessage :=
4333 ame_util.getMessage(applicationShortNameIn => 'PER',
4334 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4335 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4336 routineNameIn => 'newItemClassUsage',
4337 exceptionNumberIn => errorCode,
4338 exceptionStringIn => errorMessage);
4339 raise_application_error(errorCode,
4340 errorMessage);
4341 when objectVersionNoDataException then
4342 rollback;
4343 if(startDateCursor%isOpen) then
4344 close startDateCursor;
4345 end if;
4346 errorCode := -20001;
4347 errorMessage :=
4348 ame_util.getMessage(applicationShortNameIn => 'PER',
4349 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
4350 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4351 routineNameIn => 'newItemClassUsage',
4352 exceptionNumberIn => errorCode,
4353 exceptionStringIn => errorMessage);
4354 raise_application_error(errorCode,
4355 errorMessage);
4356 when stringDynamicException then
4357 rollback;
4358 errorCode := -20001;
4359 errorMessage :=
4360 ame_util.getMessage(applicationShortNameIn => 'PER',
4361 messageNameIn => 'AME_400376_ADM IC_QUERY');
4362 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4363 routineNameIn => 'newItemClassUsage',
4364 exceptionNumberIn => errorCode,
4365 exceptionStringIn => errorMessage);
4366 raise_application_error(errorCode,
4367 errorMessage);
4368 when dynamicUsageException then
4369 rollback;
4370 errorCode := -20001;
4371 errorMessage :=
4372 ame_util.getMessage(applicationShortNameIn => 'PER',
4373 messageNameIn => 'AME_400377_ADM IC_QUERY2');
4374 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4375 routineNameIn => 'newItemClassUsage',
4376 exceptionNumberIn => errorCode,
4377 exceptionStringIn => errorMessage);
4378 raise_application_error(errorCode,
4379 errorMessage);
4380 when nullQueryStringException then
4381 rollback;
4382 errorCode := -20001;
4383 errorMessage :=
4384 ame_util.getMessage(applicationShortNameIn => 'PER',
4385 messageNameIn => 'AME_400410_ADM_NO_EMPTY_USAGE');
4386 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4387 routineNameIn => 'newItemClassUsage',
4388 exceptionNumberIn => errorCode,
4389 exceptionStringIn => errorMessage);
4390 raise_application_error(errorCode,
4391 errorMessage);
4392 when usageExistsException then
4393 rollback;
4394 errorCode := -20001;
4395 errorMessage :=
4396 ame_util.getMessage(applicationShortNameIn => 'PER',
4397 messageNameIn => 'AME_400379_ADM IC_USAGE_EXISTS');
4398 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4399 routineNameIn => 'newItemClassUsage',
4400 exceptionNumberIn => errorCode,
4401 exceptionStringIn => errorMessage);
4402 raise_application_error(errorCode,
4403 errorMessage);
4404 when others then
4405 rollback;
4406 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4407 routineNameIn => 'newItemClassUsage',
4408 exceptionNumberIn => sqlcode,
4409 exceptionStringIn => '(item class ID ' ||
4410 itemClassIdIn||
4411 ') ' ||
4412 sqlerrm);
4413 raise;
4414 end newItemClassUsage;
4415 /*
4416 AME_STRIPING
4417 procedure newStripeSet2(applicationIdIn in integer,
4418 newStripedAttributesSetIn in ame_util.stringList,
4419 commitIn in boolean default false) as
4420 attributeCount integer;
4421 currentUserId integer;
4422 errorCode varchar2(10);
4423 errorMessage varchar2(5000);
4424 existingSSException exception;
4425 begin
4426 currentUserId := ame_util.getCurrentUserId;
4427 select count(*)
4428 into attributeCount
4429 from ame_stripe_sets
4430 where
4431 application_id = applicationIdIn and
4432 ((value_1 is null and newStripedAttributesSetIn(1) is null) or value_1 = newStripedAttributesSetIn(1)) and
4433 ((value_2 is null and newStripedAttributesSetIn(2) is null) or value_2 = newStripedAttributesSetIn(2)) and
4434 ((value_3 is null and newStripedAttributesSetIn(3) is null) or value_3 = newStripedAttributesSetIn(3)) and
4435 ((value_4 is null and newStripedAttributesSetIn(4) is null) or value_4 = newStripedAttributesSetIn(4)) and
4436 ((value_5 is null and newStripedAttributesSetIn(5) is null) or value_5 = newStripedAttributesSetIn(5)) and
4437 (start_date <= sysdate and
4438 (end_date is null or sysdate < end_date));
4439 if(attributeCount > 0) then
4440 raise existingSSException;
4441 end if;
4442 insert into ame_stripe_sets(application_id,
4443 stripe_set_id,
4444 value_1,
4445 value_2,
4446 value_3,
4447 value_4,
4448 value_5,
4449 created_by,
4450 creation_date,
4451 last_updated_by,
4452 last_update_date,
4453 last_update_login,
4454 start_date,
4455 end_date,
4456 security_group_id)
4457 values(applicationIdIn,
4458 0,
4459 newStripedAttributesSetIn(1),
4460 newStripedAttributesSetIn(2),
4461 newStripedAttributesSetIn(3),
4462 newStripedAttributesSetIn(4),
4463 newStripedAttributesSetIn(5),
4464 currentUserId,
4465 sysdate,
4466 currentUserId,
4467 sysdate,
4468 currentUserId,
4469 sysdate,
4470 null,
4471 null);
4472 if(commitIn) then
4473 commit;
4474 end if;
4475 exception
4476 when existingSSException then
4477 rollback;
4478 errorCode := -20001;
4479 errorMessage := 'This stripe set already exists for the ' ||
4480 'transaction type. ';
4481 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4482 routineNameIn => 'newStripeSet2',
4483 exceptionNumberIn => errorCode,
4484 exceptionStringIn => errorMessage);
4485 raise_application_error(errorCode,
4486 errorMessage);
4487 when others then
4488 rollback;
4489 rollback;
4490 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4491 routineNameIn => 'newStripeSet2',
4492 exceptionNumberIn => sqlcode,
4493 exceptionStringIn => sqlerrm);
4494 raise;
4495 end newStripeSet2;
4496 */
4497 procedure registerTransactionType(fndApplicationIdIn in integer,
4498 transTypeDescIn in varchar2,
4499 transactionTypeIdIn in varchar2 default null,
4500 attributeIdsIn in ame_util.stringList,
4501 queryStringsIn in ame_util.longestStringList,
4502 staticUsagesIn in ame_util.stringList,
4503 versionStartDatesIn in ame_util.stringList) as
4504 cursor attributeCursor(attributeIdIn in integer) is
4505 select
4506 to_char(ame_attributes.start_date) start_date
4507 from
4508 ame_attributes,
4509 ame_mandatory_attributes
4510 where
4511 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
4512 ame_attributes.attribute_id = attributeIdIn and
4513 ame_mandatory_attributes.action_type_id = -1 and
4514 sysdate between ame_attributes.start_date and
4515 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
4516 sysdate between ame_mandatory_attributes.start_date and
4517 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
4518 for update;
4519 actionTypeIds ame_util.idList;
4520 applicationId integer;
4521 attributeCount integer;
4522 attributeIds ame_util.stringList;
4523 attributeName ame_attributes.name%type;
4524 currentUserId integer;
4525 errorCode varchar2(10);
4526 errorMessage ame_util.longestStringType;
4527 evalAttributeId ame_attributes.attribute_id%type;
4528 evalPrioritiesPerItemAttId integer;
4529 evalPriorityStartDate ame_util.stringType;
4530 evalQueryString ame_attribute_usages.query_string%type;
4531 evalStaticUsage ame_attribute_usages.is_static%type;
4532 finalize boolean;
4533 found number;
4534 groupList ame_util.idList;
4535 orderNumber integer;
4536 queryStrings ame_util.longestStringList;
4537 ruleType integer;
4538 ruleTypes ame_util.idList;
4539 startDate ame_util.stringType;
4540 staticUsage ame_attribute_usages.is_static%type;
4541 staticUsages ame_util.stringList;
4542 tempIndex integer;
4543 tempIndex2 integer;
4544 useRestrItemEvalAttId integer;
4545 useRestrStartDate ame_util.stringType;
4546 versionStartDates ame_util.stringList;
4547 processingDate date;
4548 begin
4549 processingDate := sysdate;
4550 currentUserId := ame_util.getCurrentUserId;
4551 select ame_applications_s.nextval
4552 into applicationId
4553 from dual;
4554 insert into ame_calling_apps(fnd_application_id,
4555 application_name,
4556 application_id,
4557 transaction_type_id,
4558 created_by,
4559 creation_date,
4560 last_updated_by,
4561 last_update_date,
4562 last_update_login,
4563 start_date,
4564 line_item_id_query)
4565 values(fndApplicationIdIn,
4566 transTypeDescIn,
4567 applicationId,
4568 transactionTypeIdIn,
4569 currentUserId,
4570 processingDate,
4571 currentUserId,
4572 processingDate,
4573 currentUserId,
4574 processingDate,
4575 null);
4576 tempIndex := 1;
4577 queryStrings := queryStringsIn;
4578 staticUsages := staticUsagesIn;
4579 attributeIds := attributeIdsIn;
4580 versionStartDates := versionStartDatesIn;
4581 tempIndex2 := (attributeIds.count + 1);
4582 evalPrioritiesPerItemAttId :=
4583 ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.evalPrioritiesPerItemAttribute);
4584 evalPriorityStartDate :=
4585 ame_attribute_pkg.getStartDate(attributeIdIn => evalPrioritiesPerItemAttId);
4586 attributeIds(tempIndex2) := evalPrioritiesPerItemAttId;
4587 queryStrings(tempIndex2) := ame_util.booleanAttributeFalse;
4588 staticUsages(tempIndex2) := ame_util.booleanTrue;
4589 versionStartDates(tempIndex2) := evalPriorityStartDate;
4590 useRestrItemEvalAttId :=
4591 ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.restrictiveItemEvalAttribute);
4592 useRestrStartDate :=
4593 ame_attribute_pkg.getStartDate(attributeIdIn => useRestrItemEvalAttId);
4594 tempIndex2 := (tempIndex2 + 1);
4595 attributeIds(tempIndex2) := useRestrItemEvalAttId;
4596 queryStrings(tempIndex2) := ame_util.booleanAttributeFalse;
4597 staticUsages(tempIndex2) := ame_util.booleanTrue;
4598 versionStartDates(tempIndex2) := useRestrStartDate;
4599 /* attempt to get a lock on the mandatory attributes */
4600 for i in 1..attributeIds.count loop
4601 open attributeCursor(attributeIdIn => attributeIds(i));
4602 loop
4603 fetch attributeCursor into startDate;
4604 exit when attributeCursor%notfound;
4605 /* verifies that the mandatory attribute has not been updated */
4606 /* the versionStartDate is in sync with the start date due to how the data
4607 was retrieved for each */
4608 if(versionStartDates(tempIndex) = startDate) then
4609 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIds(i));
4610 if(attributeName = ame_util.evalPrioritiesPerItemAttribute) then
4611 evalAttributeId := attributeIds(i);
4612 evalQueryString := queryStrings(tempIndex);
4613 evalStaticUsage := staticUsages(tempIndex);
4614 else
4615 ame_attribute_pkg.newAttributeUsage(attributeIdIn => attributeIds(i),
4616 applicationIdIn => applicationId,
4617 queryStringIn => queryStrings(tempIndex),
4618 staticUsageIn => staticUsages(tempIndex),
4619 updateParentObjectIn => true,
4620 finalizeIn => false);
4621 if(attributeName = ame_util.restrictiveItemEvalAttribute) then
4622 ame_attribute_pkg.newAttributeUsage(attributeIdIn => evalAttributeId,
4623 applicationIdIn => applicationId,
4624 queryStringIn => evalQueryString,
4625 staticUsageIn => evalStaticUsage,
4626 updateParentObjectIn => true,
4627 finalizeIn => false);
4628 end if;
4629 end if;
4630 else
4631 close attributeCursor;
4632 raise ame_util.objectVersionException;
4633 end if;
4634 tempIndex := tempIndex + 1;
4635 end loop;
4636 close attributeCursor;
4637 end loop;
4638 ame_action_pkg.getActionTypeUsages2(actionTypeIdsOut => actionTypeIds,
4639 ruleTypesOut => ruleTypes);
4640 for i in 1..actionTypeIds.count loop
4641 if(i = 1) then
4642 ruleType := ruleTypes(i);
4643 orderNumber := 1;
4644 else
4645 if(ruleType = ruleTypes(i)) then
4646 orderNumber := orderNumber + 1;
4647 else
4648 ruleType := ruleTypes(i);
4649 orderNumber := 1;
4650 end if;
4651 end if;
4652 insert into ame_action_type_config(application_id,
4653 action_type_id,
4654 voting_regime,
4655 order_number,
4656 chain_ordering_mode,
4657 created_by,
4658 creation_date,
4659 last_updated_by,
4660 last_update_date,
4661 last_update_login,
4662 start_date,
4663 end_date)
4664 values(applicationId,
4665 actionTypeIds(i),
4666 ame_util.serializedVoting,
4667 orderNumber,
4668 ame_util.sequentialChainsMode,
4669 currentUserId,
4670 processingDate,
4671 currentUserId,
4672 processingDate,
4673 currentUserId,
4674 processingDate,
4675 null);
4676 end loop;
4677 ame_approval_group_pkg.getApprovalGroupList(groupListOut => groupList);
4678 for i in 1..groupList.count loop
4679 insert into ame_approval_group_config(application_id,
4680 approval_group_id,
4681 voting_regime,
4682 order_number,
4683 created_by,
4684 creation_date,
4685 last_updated_by,
4686 last_update_date,
4687 last_update_login,
4688 start_date,
4689 end_date)
4690 values(applicationId,
4691 groupList(i),
4692 ame_util.serializedVoting,
4693 i,
4694 currentUserId,
4695 processingDate,
4696 currentUserId,
4697 processingDate,
4698 currentUserId,
4699 processingDate,
4700 null);
4701 end loop;
4702 commit;
4703 exception
4704 when ame_util.objectVersionException then
4705 rollback;
4706 if(attributeCursor%isOpen) then
4707 close attributeCursor;
4708 end if;
4709 errorCode := -20001;
4710 errorMessage :=
4711 ame_util.getMessage(applicationShortNameIn => 'PER',
4712 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4713 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4714 routineNameIn => 'registerTransactionType',
4715 exceptionNumberIn => errorCode,
4716 exceptionStringIn => errorMessage);
4717 raise_application_error(errorCode,
4718 errorMessage);
4719 when others then
4720 rollback;
4721 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4722 routineNameIn => 'registerTransactionType',
4723 exceptionNumberIn => sqlcode,
4724 exceptionStringIn => sqlerrm);
4725 raise;
4726 end registerTransactionType;
4727 /*
4728 AME_STRIPING
4729 procedure removeAllStripeSets(applicationIdIn in integer,
4730 deleteStripeSetIdZeroIn in boolean,
4731 commitIn in boolean default false) as
4732 cursor getAllStripeSetsCursor(applicationIdIn in integer) is
4733 select stripe_set_id,
4734 value_1,
4735 value_2,
4736 value_3,
4737 value_4,
4738 value_5
4739 from ame_stripe_sets
4740 where
4741 application_id = applicationIdIn and
4742 stripe_set_id <> 0 and
4743 (start_date <= sysdate and
4744 (end_date is null or sysdate < end_date))
4745 order by stripe_set_id;
4746 currentUserId integer;
4747 startDate date;
4748 begin
4749 currentUserId := ame_util.getCurrentUserId;
4750 if(deleteStripeSetIdZeroIn) then
4751 update ame_stripe_sets
4752 set
4753 last_updated_by = currentUserId,
4754 last_update_date = sysdate,
4755 last_update_login = currentUserId,
4756 end_date = sysdate
4757 where
4758 application_id = applicationIdIn and
4759 (start_date <= sysdate and
4760 (end_date is null or sysdate < end_date));
4761 else
4762 for getAllStripeSetsRec in getAllStripeSetsCursor(applicationIdIn => applicationIdIn) loop
4763 update ame_stripe_sets
4764 set
4765 last_updated_by = currentUserId,
4766 last_update_date = sysdate,
4767 last_update_login = currentUserId,
4768 end_date = sysdate
4769 where
4770 application_id = applicationIdIn and
4771 stripe_set_id = getAllStripeSetsRec.stripe_set_id and
4772 (start_date <= sysdate and
4773 (end_date is null or sysdate < end_date));
4774 end loop;
4775 end if;
4776 if(commitIn) then
4777 commit;
4778 end if;
4779 exception
4780 when others then
4781 rollback;
4782 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
4783 routineNameIn => 'removeAllStripeSets',
4784 exceptionNumberIn => sqlcode,
4785 exceptionStringIn => sqlerrm);
4786 raise;
4787 end removeAllStripeSets;
4788 */
4789 /*
4790 AME_STRIPING
4791 procedure removeStripeSetAttributes(applicationIdIn in integer,
4792 attributeIdIn in integer) as
4793 cursor stripeSetCursor(applicationIdIn in integer) is
4794 select
4795 stripe_set_id,
4796 value_1,
4797 value_2,
4798 value_3,
4799 value_4,
4800 value_5
4801 from ame_stripe_sets
4802 where
4803 stripe_set_id <> 0 and
4804 application_id = applicationIdIn and
4805 (start_date <= sysdate and
4806 (end_date is null or sysdate < end_date))
4807 order by
4808 value_1,
4809 value_2,
4810 value_3,
4811 value_4,
4812 value_5; */
4813 /* This lexicographic ordering of the value_i columns is critical. */
4814 /*
4815 cursor stripeSetRuleCursor(stripeSetIdIn in integer) is
4816 select rule_id
4817 from ame_rule_stripe_sets
4818 where
4819 ame_rule_stripe_sets.stripe_set_id = stripeSetIdIn and
4820 (start_date <= sysdate and
4821 (end_date is null or sysdate < end_date));
4822 columnIndex integer;
4823 conversionStripeSetIds ame_util.idList;
4824 conversionStripingAttValues ame_util.stringList;
4825 conversionConditionIds ame_util.idList;
4826 currentUserId integer;
4827 dynamicQuery ame_util.longestStringType;
4828 errorCode integer;
4829 errorMessage ame_util.longStringType;
4830 indexToWrite integer;
4831 insertRow boolean;
4832 lastStripeSetId integer;
4833 lastStripingAttValues ame_util.stringList;
4834 newConditionId ame_conditions.condition_id%type;
4835 noAttributeMatchException exception;
4836 ruleIds ame_util.idList;
4837 stringValueList ame_util.longestStringList;
4838 stripeSetIds ame_util.idList;
4839 stripingAttributeValues1 ame_util.stringList;
4840 stripingAttributeValues2 ame_util.stringList;
4841 stripingAttributeValues3 ame_util.stringList;
4842 stripingAttributeValues4 ame_util.stringList;
4843 stripingAttributeValues5 ame_util.stringList;
4844 stripingAttributeIds ame_util.idList;
4845 upperLimit integer;
4846 begin
4847 currentUserId := ame_util.getCurrentUserId;
4848 */
4849 /* Fetch the current striping attribute IDs. */
4850 /*
4851 select
4852 to_number(value_1),
4853 to_number(value_2),
4854 to_number(value_3),
4855 to_number(value_4),
4856 to_number(value_5)
4857 into
4858 stripingAttributeIds(1),
4859 stripingAttributeIds(2),
4860 stripingAttributeIds(3),
4861 stripingAttributeIds(4),
4862 stripingAttributeIds(5)
4863 from ame_stripe_sets
4864 where
4865 application_id = applicationIdIn and
4866 stripe_set_id = 0 and
4867 (start_date <= sysdate and
4868 (end_date is null or sysdate < end_date));
4869 */
4870 /* Set columnIndex to the index of the column containing the striping attribute to be dropped. */
4871 /*
4872 columnIndex := null;
4873 for i in 1 .. 5 loop
4874 if(stripingAttributeIds(i) = attributeIdIn) then
4875 columnIndex := i;
4876 exit;
4877 end if;
4878 end loop;
4879 if(columnIndex is null) then
4880 raise noAttributeMatchException;
4881 end if;
4882 */
4883 /*
4884 Fetch all current stripe sets in the current transaction type, in
4885 lexicographic order of striping-attribute value.
4886 */
4887 /*
4888 open stripeSetCursor(applicationIdIn => applicationIdIn);
4889 fetch stripeSetCursor bulk collect
4890 into
4891 stripeSetIds,
4892 stripingAttributeValues1,
4893 stripingAttributeValues2,
4894 stripingAttributeValues3,
4895 stripingAttributeValues4,
4896 stripingAttributeValues5;
4897 close stripeSetCursor;
4898 upperLimit := stripeSetIds.count; */
4899 /* Don't update upperLimit below. */
4900 /* End date all current stripe sets, and the zero-ID row containing the striping-attribute IDs. */
4901 /*
4902 update ame_stripe_sets
4903 set end_date = sysdate
4904 where
4905 application_id = applicationIdIn and
4906 (start_date <= sysdate and
4907 (end_date is null or sysdate < end_date));
4908 */
4909 /* Insert a new zero-ID row, compacted. */
4910 /*
4911 if(columnIndex < 5) then
4912 for i in columnIndex .. 4 loop
4913 stripingAttributeIds(i) := stripingAttributeIds(i + 1);
4914 end loop;
4915 end if;
4916 stripingAttributeIds(5) := null;
4917 insert into ame_stripe_sets(
4918 application_id,
4919 stripe_set_id,
4920 value_1,
4921 value_2,
4922 value_3,
4923 value_4,
4924 value_5,
4925 created_by,
4926 creation_date,
4927 last_updated_by,
4928 last_update_date,
4929 last_update_login,
4930 start_date,
4931 end_date) values(
4932 applicationIdIn,
4933 0,
4934 to_number(stripingAttributeIds(1)),
4935 to_number(stripingAttributeIds(2)),
4936 to_number(stripingAttributeIds(3)),
4937 to_number(stripingAttributeIds(4)),
4938 to_number(stripingAttributeIds(5)),
4939 currentUserId,
4940 sysdate,
4941 currentUserId,
4942 sysdate,
4943 currentUserId,
4944 sysdate,
4945 null);
4946 */
4947 /* Initialize lastStripingAttValues so the first stripe set will always get inserted. */
4948 /* lastStripeSetId := null; */
4949 /* Just to be safe. */
4950 /*
4951 for i in 1 .. 5 loop
4952 lastStripingAttValues(i) := null;
4953 end loop;
4954 */
4955 /* Eliminate duplicate stripe sets. */
4956 /* for i in 1 .. upperLimit loop */
4957 /*
4958 First check whether the ith stripe set in the stripingAttributeValues[i] tables is new.
4959 Because of the lexicographic ordering, if the ith row does not match the previous row
4960 inserted, the ith row must be inserted.
4961 */
4962 /*
4963 insertRow := false;
4964 if(columnIndex <> 1 and
4965 ((stripingAttributeValues1(i) is null and lastStripingAttValues(1) is not null) or
4966 ((stripingAttributeValues1(i) is not null and lastStripingAttValues(1) is null)) or
4967 (stripingAttributeValues1(i) <> lastStripingAttValues(1)))) then
4968 insertRow := true;
4969 end if;
4970 if((not insertRow) and
4971 columnIndex <> 2 and
4972 ((stripingAttributeValues2(i) is null and lastStripingAttValues(2) is not null) or
4973 ((stripingAttributeValues2(i) is not null and lastStripingAttValues(2) is null)) or
4974 (stripingAttributeValues2(i) <> lastStripingAttValues(2)))) then
4975 insertRow := true;
4976 end if;
4977 if((not insertRow) and
4978 columnIndex <> 3 and
4979 ((stripingAttributeValues3(i) is null and lastStripingAttValues(3) is not null) or
4980 ((stripingAttributeValues3(i) is not null and lastStripingAttValues(3) is null)) or
4981 (stripingAttributeValues3(i) <> lastStripingAttValues(3)))) then
4982 insertRow := true;
4983 end if;
4984 if((not insertRow) and
4985 columnIndex <> 4 and
4986 ((stripingAttributeValues4(i) is null and lastStripingAttValues(4) is not null) or
4987 ((stripingAttributeValues4(i) is not null and lastStripingAttValues(4) is null)) or
4988 (stripingAttributeValues4(i) <> lastStripingAttValues(4)))) then
4989 insertRow := true;
4990 end if;
4991 if((not insertRow) and
4992 columnIndex <> 5 and
4993 ((stripingAttributeValues5(i) is null and lastStripingAttValues(5) is not null) or
4994 ((stripingAttributeValues5(i) is not null and lastStripingAttValues(5) is null)) or
4995 (stripingAttributeValues5(i) <> lastStripingAttValues(5)))) then
4996 insertRow := true;
4997 end if;
4998 */
4999 /* Now insert the ith row if it's new. Remember to update the last-row-inserted data. */
5000 /* if(insertRow) then */
5001 /* Update lastStripeSetId and lastStripingAttValues. */
5002 /*
5003 lastStripeSetId := stripeSetIds(i);
5004 lastStripingAttValues(1) := stripingAttributeValues1(i);
5005 lastStripingAttValues(2) := stripingAttributeValues2(i);
5006 lastStripingAttValues(3) := stripingAttributeValues3(i);
5007 lastStripingAttValues(4) := stripingAttributeValues4(i);
5008 lastStripingAttValues(5) := stripingAttributeValues5(i);
5009 */
5010 /* Compact the row. */
5011 /*
5012 if(columnIndex < 2) then
5013 stripingAttributeValues1(i) := stripingAttributeValues2(i);
5014 end if;
5015 if(columnIndex < 3) then
5016 stripingAttributeValues2(i) := stripingAttributeValues3(i);
5017 end if;
5018 if(columnIndex < 4) then
5019 stripingAttributeValues3(i) := stripingAttributeValues4(i);
5020 end if;
5021 stripingAttributeValues5(i) := null;
5022 else
5023 */
5024 /* Move the rules in this stripe set into the most recently inserted stripe set. */
5025 /*
5026 open stripeSetRuleCursor(stripeSetIdIn => stripeSetIds(i));
5027 fetch stripeSetRuleCursor bulk collect into ruleIds;
5028 close stripeSetRuleCursor;
5029 update ame_rule_stripe_sets
5030 set end_date = sysdate
5031 where
5032 stripe_set_id = stripeSetIds(i) and
5033 (start_date <= sysdate and
5034 (end_date is null or sysdate < end_date));
5035 forall i in 1 .. ruleIds.count
5036 insert into ame_rule_stripe_sets(
5037 rule_id,
5038 stripe_set_id,
5039 created_by,
5040 creation_date,
5041 last_updated_by,
5042 last_update_date,
5043 last_update_login,
5044 start_date,
5045 end_date) values(
5046 ruleIds(i),
5047 lastStripeSetId,
5048 currentUserId,
5049 sysdate,
5050 currentUserId,
5051 sysdate,
5052 currentUserId,
5053 sysdate,
5054 null);
5055 */
5056 /* Delete this stripe set. */
5057 /*
5058 stripeSetIds.delete(i);
5059 end if;
5060 end loop;
5061 */
5062 /* Compact the stripe-set lists. */
5063 /* indexToWrite := stripeSetIds.first; */
5064 /* post-increment */
5065 /*
5066 for i in 1 .. upperLimit loop
5067 if(indexToWrite is null) then
5068 stripeSetIds.delete(i, upperLimit);
5069 stripingAttributeValues1.delete(i, upperLimit);
5070 stripingAttributeValues2.delete(i, upperLimit);
5071 stripingAttributeValues3.delete(i, upperLimit);
5072 stripingAttributeValues4.delete(i, upperLimit);
5073 stripingAttributeValues5.delete(i, upperLimit);
5074 exit;
5075 else
5076 stripeSetIds(i) := stripeSetIds(indexToWrite);
5077 stripingAttributeValues1(i) := stripingAttributeValues1(indexToWrite);
5078 stripingAttributeValues2(i) := stripingAttributeValues2(indexToWrite);
5079 stripingAttributeValues3(i) := stripingAttributeValues3(indexToWrite);
5080 stripingAttributeValues4(i) := stripingAttributeValues4(indexToWrite);
5081 stripingAttributeValues5(i) := stripingAttributeValues5(indexToWrite);
5082 end if;
5083 indexToWrite := stripeSetIds.next(indexToWrite);
5084 end loop;
5085 */
5086 /* Bulk insert the stripe sets. */
5087 /*
5088 forall i in 1 .. stripeSetIds.count
5089 insert into ame_stripe_sets(
5090 application_id,
5091 stripe_set_id,
5092 value_1,
5093 value_2,
5094 value_3,
5095 value_4,
5096 value_5,
5097 created_by,
5098 creation_date,
5099 last_updated_by,
5100 last_update_date,
5101 last_update_login,
5102 start_date,
5103 end_date) values(
5104 applicationIdIn,
5105 stripeSetIds(i),
5106 stripingAttributeValues1(i),
5107 stripingAttributeValues2(i),
5108 stripingAttributeValues3(i),
5109 stripingAttributeValues4(i),
5110 stripingAttributeValues5(i),
5111 currentUserId,
5112 sysdate,
5113 currentUserId,
5114 sysdate,
5115 currentUserId,
5116 sysdate,
5117 null);
5118 commit;
5119 exception
5120 when noAttributeMatchException then
5121 rollback;
5122 errorCode := -20001;
5123 errorMessage := 'The attribute you selected to remove was not ' ||
5124 'found within the current stripe set. Please ' ||
5125 'contact your systems administrator.';
5126 ame_util.runtimeException(packageNamein => 'ame_admin_pkg',
5127 routineNamein => 'removeStripeSetAttributes',
5128 exceptionNumberIn => errorCode,
5129 exceptionStringIn => errorMessage);
5130 raise_application_error(errorCode,
5131 errorMessage);
5132 when others then
5133 rollback;
5134 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5135 routineNameIn => 'removeStripeSetAttributes',
5136 exceptionNumberIn => sqlcode,
5137 exceptionStringIn => sqlerrm);
5138 raise;
5139 end removeStripeSetAttributes;
5140 */
5141 procedure removeUsage(itemClassIdIn in integer,
5142 parentVersionStartDateIn in date,
5143 childVersionStartDateIn in date,
5144 applicationIdIn in integer,
5145 finalizeIn in boolean default false) as
5146 cursor startDateCursor is
5147 select start_date
5148 from ame_item_classes
5149 where
5150 item_class_id = itemClassIdIn and
5151 sysdate between start_date and
5152 nvl(end_date - ame_util.oneSecond, sysdate)
5153 for update;
5154 cursor startDateCursor2 is
5155 select start_date
5156 from ame_item_class_usages
5157 where
5158 item_class_id = itemClassIdIn and
5159 application_id = applicationIdIn and
5160 sysdate between start_date and
5161 nvl(end_date - ame_util.oneSecond, sysdate)
5162 for update;
5163 currentUserId integer;
5164 errorCode integer;
5165 errorMessage ame_util.longestStringType;
5166 icUsageDeletionException exception;
5167 inUseException exception;
5168 itemClassId integer;
5169 itemClassName ame_item_classes.name%type;
5170 objectVersionNoDataException exception;
5171 orderNumber integer;
5172 startDate date;
5173 startDate2 date;
5174 processingDate date;
5175 begin
5176 processingDate := sysdate;
5177 /* Try to get a lock on the record. */
5178 open startDateCursor;
5179 fetch startDateCursor into startDate;
5180 if startDateCursor%notfound then
5181 raise objectVersionNoDataException;
5182 end if;
5183 if(parentVersionStartDateIn <> startDate) then
5184 close startDateCursor;
5185 raise ame_util.objectVersionException;
5186 end if;
5187 open startDateCursor2;
5188 fetch startDateCursor2 into startDate2;
5189 if startDateCursor2%notfound then
5190 raise objectVersionNoDataException;
5191 end if;
5192 if(childVersionStartDateIn <> startDate2) then
5193 close startDateCursor2;
5194 raise ame_util.objectVersionException;
5195 end if;
5196 currentUserId := ame_util.getCurrentUserId;
5197 if((ame_admin_pkg.icInUseByAttributeUsage(itemClassIdIn => itemClassIdIn,
5198 applicationIdIn => applicationIdIn)) or
5199 (ame_admin_pkg.icInUseByRuleUsage(itemClassIdIn => itemClassIdIn,
5200 applicationIdIn => applicationIdIn))) then
5201 raise icUsageDeletionException;
5202 end if;
5203 select item_class_order_number
5204 into orderNumber
5205 from ame_item_class_usages
5206 where
5207 application_id = applicationIdIn and
5208 item_class_id = itemClassIdIn and
5209 sysdate between start_date and
5210 nvl(end_date - ame_util.oneSecond, sysdate);
5211 if(orderNumberUnique(applicationIdIn => applicationIdIn,
5212 orderNumberIn => orderNumber)) then
5213 /* subtract 1 from the order number for those above the one being deleted */
5214 decrementItemClassOrderNumbers(applicationIdIn => applicationIdIn,
5215 orderNumberIn => orderNumber,
5216 finalizeIn => false);
5217 end if;
5218 update ame_item_class_usages
5219 set
5220 last_updated_by = currentUserId,
5221 last_update_date = processingDate,
5222 last_update_login = currentUserId,
5223 end_date = processingDate
5224 where
5225 item_class_id = itemClassIdIn and
5226 application_id = applicationIdIn and
5227 processingDate between start_date and
5228 nvl(end_date - ame_util.oneSecond, processingDate);
5229 itemClassName := ame_admin_pkg.getItemClassName(itemClassIdIn => itemClassIdIn);
5230 update ame_item_classes
5231 set
5232 last_updated_by = currentUserId,
5233 last_update_date = processingDate,
5234 last_update_login = currentUserId,
5235 end_date = processingDate
5236 where
5237 item_class_id = itemClassIdIn and
5238 processingDate between start_date and
5239 nvl(end_date - ame_util.oneSecond, processingDate);
5240 itemClassId := newItemClass(itemClassIdIn => itemClassIdIn,
5241 itemClassNameIn => itemClassName,
5242 newStartDateIn => processingDate,
5243 finalizeIn => false);
5244 close startDateCursor;
5245 close startDateCursor2;
5246 if(finalizeIn) then
5247 commit;
5248 end if;
5249 exception
5250 when ame_util.objectVersionException then
5251 rollback;
5252 if(startDateCursor%isOpen) then
5253 close startDateCursor;
5254 end if;
5255 if(startDateCursor2%isOpen) then
5256 close startDateCursor2;
5257 end if;
5258 errorCode := -20001;
5259 errorMessage :=
5260 ame_util.getMessage(applicationShortNameIn => 'PER',
5261 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
5262 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5263 routineNameIn => 'removeUsage',
5264 exceptionNumberIn => errorCode,
5265 exceptionStringIn => errorMessage);
5266 raise_application_error(errorCode,
5267 errorMessage);
5268 when objectVersionNoDataException then
5269 rollback;
5270 if(startDateCursor%isOpen) then
5271 close startDateCursor;
5272 end if;
5273 if(startDateCursor2%isOpen) then
5274 close startDateCursor2;
5275 end if;
5276 errorCode := -20001;
5277 errorMessage :=
5278 ame_util.getMessage(applicationShortNameIn => 'PER',
5279 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
5280 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5281 routineNameIn => 'removeUsage',
5282 exceptionNumberIn => errorCode,
5283 exceptionStringIn => errorMessage);
5284 raise_application_error(errorCode,
5285 errorMessage);
5286 when icUsageDeletionException then
5287 rollback;
5288 if(startDateCursor%isOpen) then
5289 close startDateCursor;
5290 end if;
5291 if(startDateCursor2%isOpen) then
5292 close startDateCursor2;
5293 end if;
5294 errorCode := -20001;
5295 errorMessage :=
5296 ame_util.getMessage(applicationShortNameIn => 'PER',
5297 messageNameIn => 'AME_400381_ADM IC_USAGE_DEL');
5298 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5299 routineNameIn => 'removeUsage',
5300 exceptionNumberIn => errorCode,
5301 exceptionStringIn => errorMessage);
5302 raise_application_error(errorCode,
5303 errorMessage);
5304 when others then
5305 rollback;
5306 if(startDateCursor%isOpen) then
5307 close startDateCursor;
5308 end if;
5309 if(startDateCursor2%isOpen) then
5310 close startDateCursor2;
5311 end if;
5312 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5313 routineNameIn => 'removeUsage',
5314 exceptionNumberIn => sqlcode,
5315 exceptionStringIn => '(item class ID ' ||
5316 itemClassIdIn||
5317 ') ' ||
5318 sqlerrm);
5319 raise;
5320 end removeUsage;
5321 procedure removeTransactionType(applicationIdIn in integer,
5322 versionStartDateIn in date) as
5323 cursor startDateCursor is
5324 select start_date
5325 from ame_calling_apps
5326 where
5327 application_id = applicationIdIn and
5328 sysdate between start_date and
5329 nvl(end_date - ame_util.oneSecond, sysdate)
5330 for update;
5331 cursor ruleUsageCursor(applicationIdIn in integer) is
5332 select rule.rule_id rule_id,
5333 start_date
5334 from ame_rule_usages rule
5335 where
5336 item_id = applicationIdIn and
5337 sysdate between start_date and
5338 nvl(end_date - ame_util.oneSecond, sysdate) ;
5339 attributeIdsList ame_util.idList;
5340 currentUserId integer;
5341 errorCode integer;
5342 errorMessage ame_util.longestStringType;
5343 itemClassId ame_attributes.item_class_id%type;
5344 startDate date;
5345 tempIndex integer;
5346 versionStartDate date;
5347 childVersionStartDate date;
5348 parentVersionStartDate date;
5349 processingDate date;
5350 begin
5351 processingDate := sysdate;
5352 currentUserId := ame_util.getCurrentUserId;
5353 open startDateCursor;
5354 fetch startDateCursor into startDate;
5355 if(versionStartDateIn = startDate) then
5356 for tempRows in ruleUsageCursor(applicationIdIn => applicationIdIn) loop
5357 versionStartDate := ame_rule_pkg.getStartDate(ruleIdIn => tempRows.rule_id);
5358 ame_rule_pkg.removeUsage(ruleIdIn => tempRows.rule_id,
5359 itemIdIn => applicationIdIn,
5360 usageStartDateIn => tempRows.start_date,
5361 parentVersionStartDateIn => versionStartDate,
5362 finalizeIn => false);
5363 end loop;
5364 ame_attribute_pkg.getApplicationAttributes(applicationIdIn => applicationIdIn,
5365 attributeIdOut => attributeIdsList);
5366 tempIndex := attributeIdsList.count;
5367 for i in 1 .. tempIndex loop
5368 parentVersionStartDate := ame_util.versionStringToDate(stringDateIn =>
5369 ame_attribute_pkg.getParentVersionStartDate(attributeIdIn => attributeIdsList(i)));
5370 childVersionStartDate := ame_util.versionStringToDate(stringDateIn =>
5371 ame_attribute_pkg.getChildVersionStartDate(attributeIdIn => attributeIdsList(i),
5372 applicationIdIn => applicationIdIn));
5373 itemClassId := ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdsList(i));
5374 ame_attribute_pkg.removeUsage(attributeIdIn => attributeIdsList(i),
5375 applicationIdIn => applicationIdIn,
5376 parentVersionStartDateIn => parentVersionStartDate,
5377 childVersionStartDateIn => childVersionStartDate,
5378 allowAttributeUsageDeleteIn => true,
5379 finalizeIn => false,
5380 itemClassIdIn => itemClassId);
5381 end loop;
5382 update ame_calling_apps
5383 set
5384 last_updated_by = currentUserId,
5385 last_update_date = processingDate,
5386 last_update_login = currentUserId,
5387 end_date = processingDate
5388 where
5389 application_id = applicationIdIn and
5390 processingDate between start_date and
5391 nvl(end_date - ame_util.oneSecond, processingDate);
5392 update ame_config_vars
5393 set
5394 last_updated_by = currentUserId,
5395 last_update_date = processingDate,
5396 last_update_login = currentUserId,
5397 end_date = processingDate
5398 where
5399 application_id = applicationIdIn and
5400 processingDate between start_date and
5401 nvl(end_date - ame_util.oneSecond, processingDate);
5402 update ame_action_type_config
5403 set
5404 last_updated_by = currentUserId,
5405 last_update_date = processingDate,
5406 last_update_login = currentUserId,
5407 end_date = processingDate
5408 where
5409 application_id = applicationIdIn and
5410 processingDate between start_date and
5411 nvl(end_date - ame_util.oneSecond, processingDate);
5412 update ame_approval_group_config
5413 set
5414 last_updated_by = currentUserId,
5415 last_update_date = processingDate,
5416 last_update_login = currentUserId,
5417 end_date = processingDate
5418 where
5419 application_id = applicationIdIn and
5420 processingDate between start_date and
5421 nvl(end_date - ame_util.oneSecond, processingDate);
5422 update ame_item_class_usages
5423 set
5424 last_updated_by = currentUserId,
5425 last_update_date = processingDate,
5426 last_update_login = currentUserId,
5427 end_date = processingDate
5428 where
5429 application_id = applicationIdIn and
5430 processingDate between start_date and
5431 nvl(end_date - ame_util.oneSecond, processingDate);
5432 commit;
5433 else
5434 close startDateCursor;
5435 raise ame_util.objectVersionException;
5436 end if;
5437 close startDateCursor;
5438 exception
5439 when ame_util.objectVersionException then
5440 rollback;
5441 if(startDateCursor%isOpen) then
5442 close startDateCursor;
5443 end if;
5444 errorCode := -20001;
5445 errorMessage :=
5446 ame_util.getMessage(applicationShortNameIn => 'PER',
5447 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
5448 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5449 routineNameIn => 'removeTransactionType',
5450 exceptionNumberIn => errorCode,
5451 exceptionStringIn => errorMessage);
5452 raise_application_error(errorCode,
5453 errorMessage);
5454 when no_data_found then
5455 rollback;
5456 if(startDateCursor%isOpen) then
5457 close startDateCursor;
5458 end if;
5459 errorCode := -20001;
5460 errorMessage :=
5461 ame_util.getMessage(applicationShortNameIn => 'PER',
5462 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
5463 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5464 routineNameIn => 'removeTransactionType',
5465 exceptionNumberIn => errorCode,
5466 exceptionStringIn => errorMessage);
5467 raise_application_error(errorCode,
5468 errorMessage);
5469 when others then
5470 rollback;
5471 if(startDateCursor%isOpen) then
5472 close startDateCursor;
5473 end if;
5474 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5475 routineNameIn => 'removeTransactionType',
5476 exceptionNumberIn => sqlcode,
5477 exceptionStringIn => sqlerrm);
5478 raise;
5479 end removeTransactionType;
5480 /*
5481 AME_STRIPING
5482 procedure updateStripingAttIds(applicationIdIn in integer,
5483 stripedAttributesIn in ame_util.stringList) as
5484 currentUserId integer;
5485 existingStripingAttCount integer;
5486 newStripingAttributeIds ame_util.idList;
5487 stripeCount integer;
5488 stripedAttributeCount integer;
5489 stripingAttributeIds ame_util.idList;
5490 stripeSetId integer;
5491 begin
5492 existingStripingAttCount := 0;
5493 select count(*)
5494 into stripeCount
5495 from ame_stripe_sets
5496 where
5497 application_id = applicationIdIn and
5498 (start_date <= sysdate and
5499 (end_date is null or sysdate < end_date));
5500 if(stripeCount > 0) then
5501 ame_admin_pkg.getStripingAttributeIds(applicationIdIn => applicationIdIn,
5502 stripingAttributeIdsOut => stripingAttributeIds);
5503 update ame_stripe_sets
5504 set end_date = sysdate
5505 where
5506 application_id = applicationIdIn and
5507 (start_date <= sysdate and
5508 (end_date is null or sysdate < end_date));
5509 existingStripingAttCount := stripingAttributeIds.count;
5510 end if;
5511 for i in 1..5 loop
5512 newStripingAttributeIds(i) := null;
5513 end loop;
5514 for i in 1..existingStripingAttCount loop
5515 newStripingAttributeIds(i) := stripingAttributeIds(i);
5516 end loop;
5517 stripedAttributeCount := stripedAttributesIn.count;
5518 for i in 1..stripedAttributeCount loop
5519 newStripingAttributeIds(existingStripingAttCount + i) := to_number(stripedAttributesIn(i));
5520 end loop;
5521 currentUserId := ame_util.getCurrentUserId;
5522 select max(stripe_set_id + 1) into stripeSetId from ame_stripe_sets;
5523 insert into ame_stripe_sets(application_id,
5524 stripe_set_id,
5525 value_1,
5526 value_2,
5527 value_3,
5528 value_4,
5529 value_5,
5530 created_by,
5531 creation_date,
5532 last_updated_by,
5533 last_update_date,
5534 last_update_login,
5535 start_date,
5536 end_date,
5537 security_group_id)
5538 values(applicationIdIn,
5539 0,
5540 to_char(newStripingAttributeIds(1)),
5541 to_char(newStripingAttributeIds(2)),
5542 to_char(newStripingAttributeIds(3)),
5543 to_char(newStripingAttributeIds(4)),
5544 to_char(newStripingAttributeIds(5)),
5545 currentUserId,
5546 sysdate,
5547 currentUserId,
5548 sysdate,
5549 currentUserId,
5550 sysdate,
5551 null,
5552 null);
5553 for i in 1..newStripingAttributeIds.count loop
5554 if(newStripingAttributeIds(i) is not null) then
5555 ame_attribute_pkg.changeUsage(attributeIdIn => newStripingAttributeIds(i),
5556 applicationIdIn => applicationIdIn,
5557 staticUsageIn =>
5558 ame_attribute_pkg.getStaticUsage(attributeIdIn => newStripingAttributeIds(i),
5559 applicationIdIn => applicationIdIn),
5560 queryStringIn =>
5561 ame_attribute_pkg.getQueryString(attributeIdIn => newStripingAttributeIds(i),
5562 applicationIdIn => applicationIdIn),
5563 endDateIn => sysdate - ame_util.oneSecond,
5564 newStartDateIn => sysdate,
5565 lineItemAttributeIn =>
5566 ame_attribute_pkg.getLineItem(attributeIdIn => newStripingAttributeIds(i)),
5567 isStripingAttributeIn => ame_util.booleanTrue);
5568 end if;
5569 end loop;
5570 commit;
5571 exception
5572 when others then
5573 rollback;
5574 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5575 routineNameIn => 'updateStripingAttIds',
5576 exceptionNumberIn => sqlcode,
5577 exceptionStringIn => sqlerrm);
5578 raise;
5579 end updateStripingAttIds;
5580 */
5581 /*
5582 AME_STRIPING
5583 procedure updateStripingAttUseCount(applicationIdIn in integer) as
5584 attributeCount integer;
5585 endDate date;
5586 lineItemAttribute ame_attributes.line_item%type;
5587 newStartDate date;
5588 queryString ame_attribute_usages.query_string%type;
5589 staticUsage ame_attribute_usages.is_static%type;
5590 stripingAttributeIds ame_util.idList;
5591 stripingAttributeNames ame_util.stringList;
5592 begin
5593 if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn)) then
5594 ame_admin_pkg.getAttributeStripeSetNames(applicationIdIn => applicationIdIn,
5595 stripingAttributeIdsOut => stripingAttributeIds,
5596 stripingAttributeNamesOut => stripingAttributeNames);
5597 attributeCount := stripingAttributeIds.count;
5598 for i in 1..attributeCount loop
5599 endDate := sysdate - ame_util.oneSecond;
5600 newStartDate := sysdate;
5601 queryString := ame_attribute_pkg.getQueryString(applicationIdIn => applicationIdIn,
5602 attributeIdIn => stripingAttributeIds(i));
5603 staticUsage := ame_attribute_pkg.getStaticUsage(applicationIdIn => applicationIdIn,
5604 attributeIdIn => stripingAttributeIds(i));
5605 lineItemAttribute := ame_attribute_pkg.getLineItem(attributeIdIn => stripingAttributeIds(i));
5606 ame_attribute_pkg.changeUsage(attributeIdIn => stripingAttributeIds(i),
5607 applicationIdIn => applicationIdIn,
5608 staticUsageIn => staticUsage,
5609 queryStringIn => queryString,
5610 endDateIn => endDate,
5611 newStartDateIn => newStartDate,
5612 lineItemAttributeIn => lineItemAttribute,
5613 isStripingAttributeIn => ame_util.booleanTrue,
5614 commitIn => false);
5615 end loop;
5616 end if;
5617 exception
5618 when others then
5619 rollback;
5620 ame_util.runtimeException(packageNameIn => 'ame_admin_pkg',
5621 routineNameIn => 'updateStripingAttUseCount',
5622 exceptionNumberIn => sqlcode,
5623 exceptionStringIn => sqlerrm);
5624 raise;
5625 end updateStripingAttUseCount;
5626 */
5627 end ame_admin_pkg;