[Home] [Help]
PACKAGE BODY: APPS.AME_ATTRIBUTE_PKG
Source
1 package body ame_attribute_pkg as
2 /* $Header: ameoattr.pkb 120.1 2006/12/26 13:14:27 avarri noship $ */
3 function attributeExists(attributeIdIn in integer) return boolean as
4 attributeCount integer;
5 begin
6 select count(*)
7 into attributeCount
8 from ame_attributes
9 where
10 attribute_id = attributeIdIn and
11 sysdate between start_date and
12 nvl(end_date - ame_util.oneSecond, sysdate) ;
13 if attributeCount > 0 then
14 return(true);
15 end if;
16 return(false);
17 exception
18 when others then
19 rollback;
20 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
21 routineNameIn => 'attributeExists',
22 exceptionNumberIn => sqlcode,
23 exceptionStringIn => '(attribute ID ' ||
24 attributeIdIn||
25 ') ' ||
26 sqlerrm);
27 raise;
28 return(false);
29 end attributeExists;
30 function attributeExistsForDiffIC(attributeNameIn in varchar2,
31 itemClassIdIn in integer) return boolean as
32 itemClassId integer;
33 begin
34 select item_class_id
35 into itemClassId
36 from ame_attributes
37 where
38 name = attributeNameIn and
39 sysdate between start_date and
40 nvl(end_date - ame_util.oneSecond, sysdate);
41 if(itemClassId <> itemClassIdIn) then
42 return(true);
43 end if;
44 return(false);
45 exception
46 when others then
47 rollback;
48 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
49 routineNameIn => 'attributeExistsForDiffIC',
50 exceptionNumberIn => sqlcode,
51 exceptionStringIn => sqlerrm);
52 raise;
53 return(true);
54 end attributeExistsForDiffIC;
55 /*
56 AME_STRIPING
57 function calculateUseCount(attributeIdIn in integer,
58 applicationIdIn in integer,
59 isStripingAttributeChangeIn in varchar2 default ame_util.booleanFalse,
60 isBecomingStripingAttributeIn in varchar2 default ame_util.booleanFalse) return integer as
61 */
62 function calculateUseCount(attributeIdIn in integer,
63 applicationIdIn in integer) return integer as
64 cursor ruleCursor(applicationIdIn in integer) is
65 select rule_id
66 from ame_rule_usages
67 where
68 ame_rule_usages.item_id = applicationIdIn and
69 ((sysdate between ame_rule_usages.start_date and
70 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
71 (sysdate < ame_rule_usages.start_date and
72 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
73 ame_rule_usages.start_date + ame_util.oneSecond)));
74 ruleCount integer;
75 tempCount integer;
76 useCount integer;
77 begin
78 /* Due to the placement of this call within newAttributeUsage, the
79 ame_attribute_usages table is not yet updated so we need
80 to verify if striping is on and if so, check whether:
81 1. It's a striping attribute that has been set to null or
82 (isStripingAttributeChangeIn)
83 2. It's an attribute that is becoming a striping attribute.
84 (isBecomingStripingAttributeIn) */
85 /*
86 if(ame_admin_pkg.isStripingOn(applicationIdIn => applicationIdIn) and
87 (isStripingAttribute(applicationIdIn => applicationIdIn,
88 attributeIdIn => attributeIdIn) or
89 isBecomingStripingAttributeIn = ame_util.booleanTrue) and
90 isStripingAttributeChangeIn = ame_util.booleanFalse) then
91 select count(*)
92 into ruleCount
93 from ame_rule_usages
94 where
95 ame_rule_usages.item_id = applicationIdIn and
96 (ame_rule_usages.start_date <= sysdate and
97 (ame_rule_usages.end_date is null or sysdate < ame_rule_usages.end_date));
98 return(ruleCount);
99 end if;
100 */
101 useCount := 0;
102 for tempRule in ruleCursor(applicationIdIn => applicationIdIn) loop
103 select count(*)
104 into tempCount
105 from
106 ame_conditions,
107 ame_condition_usages
108 where
109 ame_conditions.attribute_id = attributeIdIn and
110 ame_conditions.condition_id = ame_condition_usages.condition_id and
111 ame_condition_usages.rule_id = tempRule.rule_id and
112 sysdate between ame_conditions.start_date and
113 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
114 ((sysdate between ame_condition_usages.start_date and
115 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
116 (sysdate < ame_condition_usages.start_date and
117 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
118 ame_condition_usages.start_date + ame_util.oneSecond)));
119 if(tempCount > 0) then
120 useCount := useCount + 1;
121 else
122 select count(*)
123 into tempCount
124 from
125 ame_mandatory_attributes,
126 ame_actions,
127 ame_action_usages
128 where
129 ame_mandatory_attributes.attribute_id = attributeIdIn and
130 ame_mandatory_attributes.action_type_id = ame_actions.action_type_id and
131 ame_actions.action_id = ame_action_usages.action_id and
132 ame_action_usages.rule_id = tempRule.rule_id and
133 sysdate between ame_mandatory_attributes.start_date and
134 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
135 sysdate between ame_actions.start_date and
136 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
137 ((sysdate between ame_action_usages.start_date and
138 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
139 (sysdate < ame_action_usages.start_date and
140 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
141 ame_action_usages.start_date + ame_util.oneSecond)));
142 if(tempCount > 0) then
143 useCount := useCount + 1;
144 end if;
145 end if;
146 end loop;
147 return(useCount);
148 exception
149 when others then
150 rollback;
151 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
152 routineNameIn => 'calculateUseCount',
153 exceptionNumberIn => sqlcode,
154 exceptionStringIn => '(Attribute ID ' ||
155 attributeIdIn||
156 ') ' ||
157 sqlerrm);
158 raise;
159 return(null);
160 end calculateUseCount;
161 function getApprovalTypeNames(attributeIdIn in integer) return varchar2 as
162 cursor getApprovalTypeNames(attributeIdIn in integer) is
163 select
164 ame_action_types.name
165 from
166 ame_action_types,
167 ame_mandatory_attributes
168 where
169 ame_action_types.action_type_id = ame_mandatory_attributes.action_type_id and
170 ame_mandatory_attributes.attribute_id = attributeIdIn and
171 sysdate between ame_action_types.start_date and
172 nvl(ame_action_types.end_date - ame_util.oneSecond, sysdate) and
173 sysdate between ame_mandatory_attributes.start_date and
174 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
175 order by name;
176 tempCount integer;
177 approvalTypeNames varchar2(500);
178 begin
179 tempCount := 1;
180 for getApprovalTypeNamesRec in getApprovalTypeNames(attributeIdIn => attributeIdIn) loop
181 if tempCount = 1 then
182 approvalTypeNames := getApprovalTypeNamesRec.name;
183 tempCount := tempCount + 1;
184 else
185 approvalTypeNames := approvalTypeNames ||', '|| getApprovalTypeNamesRec.name;
186 tempCount := tempCount + 1;
187 end if;
188 end loop;
189 return(approvalTypeNames);
190 exception
191 when others then
192 rollback;
193 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
194 routineNameIn => 'getApprovalTypeNames',
195 exceptionNumberIn => sqlcode,
196 exceptionStringIn => '(attribute ID ' ||
197 attributeIdIn ||
198 ') ' ||
199 sqlerrm);
200 raise;
201 return(null);
202 end getApprovalTypeNames;
203 function getApproverTypeId(attributeIdIn in integer) return integer as
204 approverTypeId integer;
205 begin
206 select approver_type_id
207 into approverTypeId
208 from ame_attributes
209 where
210 attribute_id = attributeIdIn and
211 sysdate between ame_attributes.start_date and
212 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate);
213 return(approverTypeId);
214 exception
215 when others then
216 rollback;
217 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
218 routineNameIn => 'getApproverTypeId',
219 exceptionNumberIn => sqlcode,
220 exceptionStringIn => '(attribute ID ' ||
221 attributeIdIn ||
222 ') ' ||
223 sqlerrm);
224 raise;
225 return(null);
226 end getApproverTypeId;
227 function getAttributeConditionCnt(attributeIdIn in integer,
228 conditionTypeIn in varchar2) return integer as
229 attributeConditionCnt integer;
230 begin
231 select count(*)
232 into attributeConditionCnt
233 from ame_attributes,
234 ame_conditions
235 where
236 ame_attributes.attribute_id = ame_conditions.attribute_id and
237 ame_attributes.attribute_id = attributeIdIn and
238 ame_conditions.condition_type = conditionTypeIn and
239 sysdate between ame_attributes.start_date and
240 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
241 sysdate between ame_conditions.start_date and
242 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate);
243 return(attributeConditionCnt);
244 exception
245 when others then
246 rollback;
247 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
248 routineNameIn => 'getAttributeConditionCnt',
249 exceptionNumberIn => sqlcode,
250 exceptionStringIn => '(attribute ID ' ||
251 attributeIdIn ||
252 ') ' ||
253 sqlerrm);
254 raise;
255 return(null);
256 end getAttributeConditionCnt;
257 function getAttributeConditionInUseCnt(attributeIdIn in integer,
258 conditionTypeIn in varchar2,
259 ruleIdIn in integer) return integer as
260 attributeConditionInUseCnt integer;
261 begin
262 select count(*)
263 into attributeConditionInUseCnt
264 from ame_attributes,
265 ame_conditions,
266 ame_rules,
267 ame_condition_usages
268 where
269 ame_attributes.attribute_id = ame_conditions.attribute_id and
270 ame_conditions.condition_id = ame_condition_usages.condition_id and
271 ame_rules.rule_id = ame_condition_usages.rule_id and
272 ame_rules.rule_id = ruleIdIn and
273 ame_attributes.attribute_id = attributeIdIn and
274 ame_conditions.condition_type = conditionTypeIn and
275 sysdate between ame_attributes.start_date and
276 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
277 sysdate between ame_conditions.start_date and
278 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
279 ((sysdate between ame_condition_usages.start_date and
280 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
281 (sysdate < ame_condition_usages.start_date and
282 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
283 ame_condition_usages.start_date + ame_util.oneSecond))) and
284 ((sysdate between ame_rules.start_date and
285 nvl(ame_rules.end_date - ame_util.oneSecond, sysdate)) or
286 (sysdate < ame_rules.start_date and
287 ame_rules.start_date < nvl(ame_rules.end_date,
288 ame_rules.start_date + ame_util.oneSecond)));
289 return(attributeConditionInUseCnt);
290 exception
291 when others then
292 rollback;
293 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
294 routineNameIn => 'getAttributeConditionInUseCnt',
295 exceptionNumberIn => sqlcode,
296 exceptionStringIn => '(attribute ID ' ||
297 attributeIdIn ||
298 ') ' ||
299 sqlerrm);
300 raise;
301 return(null);
302 end getAttributeConditionInUseCnt;
303 function getAttributeNames(actionTypeIdIn in integer) return varchar2 as
304 cursor getAttributeNames(actionTypeIdIn in integer) is
305 select ame_attributes.name
306 from ame_attributes,
307 ame_mandatory_attributes
308 where
309 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
310 ame_mandatory_attributes.action_type_id = actionTypeIdIn and
311 sysdate between ame_attributes.start_date and
312 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
313 sysdate between ame_mandatory_attributes.start_date and
314 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
315 order by ame_attributes.name;
316 tempCount integer;
317 attributeNames varchar2(500);
318 begin
319 tempCount := 1;
320 for getAttributeNamesRec in getAttributeNames(actionTypeIdIn => actionTypeIdIn) loop
321 if tempCount = 1 then
322 attributeNames := getAttributeNamesRec.name;
323 tempCount := tempCount + 1;
324 else
325 attributeNames := attributeNames ||', '|| getAttributeNamesRec.name;
326 tempCount := tempCount + 1;
327 end if;
328 end loop;
329 return(attributeNames);
330 exception
331 when others then
332 rollback;
333 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
334 routineNameIn => 'getAttributeNames',
335 exceptionNumberIn => sqlcode,
336 exceptionStringIn => '(action type ID ' ||
337 actionTypeIdIn ||
338 ') ' ||
339 sqlerrm);
340 raise;
341 return(null);
342 end getAttributeNames;
343 function getDescription(attributeIdIn in integer) return varchar2 as
344 description ame_attributes.description%type;
345 begin
346 select description
347 into description
348 from ame_attributes
349 where
350 attribute_id = attributeIdIn and
351 sysdate between start_date and
352 nvl(end_date - ame_util.oneSecond, sysdate) ;
353 return(description);
354 exception
355 when others then
356 rollback;
357 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
358 routineNameIn => 'getDescription',
359 exceptionNumberIn => sqlcode,
360 exceptionStringIn => '(attribute ID ' ||
361 attributeIdIn||
362 ') ' ||
363 sqlerrm);
364 raise;
365 return(null);
366 end getDescription;
367 function getIdByName(attributeNameIn in varchar2) return integer as
368 attributeId integer;
369 begin
370 select attribute_id
371 into attributeId
372 from ame_attributes
373 where
374 name = upper(attributeNameIn) and
375 sysdate between start_date and
376 nvl(end_date - ame_util.oneSecond, sysdate) ;
377 return(attributeId);
378 exception
379 when others then
380 rollback;
381 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
382 routineNameIn => 'getIdByName',
383 exceptionNumberIn => sqlcode,
384 exceptionStringIn => '(attribute name ' ||
385 attributeNameIn||
386 ') ' ||
387 sqlerrm);
388 raise;
389 return(null);
390 end getIdByName;
391 function getItemClassId(attributeIdIn in integer) return integer as
392 itemClassId ame_attributes.item_class_id%type;
393 begin
394 select item_class_id
395 into itemClassId
396 from ame_attributes
397 where
398 attribute_id = attributeIdIn and
399 sysdate between start_date and
400 nvl(end_date - ame_util.oneSecond, sysdate) ;
401 return(itemClassId);
402 exception
403 when others then
404 rollback;
405 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
406 routineNameIn => 'getItemClassId',
407 exceptionNumberIn => sqlcode,
408 exceptionStringIn => '(attribute ID ' ||
409 attributeIdIn||
410 ') ' ||
411 sqlerrm);
412 raise;
413 return(null);
414 end getItemClassId;
415 function getLineItem(attributeIdIn in integer) return varchar2 as
416 lineItem ame_attributes.line_item%type;
417 begin
418 select line_item
419 into lineItem
420 from ame_attributes
421 where
422 attribute_id = attributeIdIn and
423 sysdate between start_date and
424 nvl(end_date - ame_util.oneSecond, sysdate) ;
425 return(lineItem);
426 exception
427 when others then
428 rollback;
429 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
430 routineNameIn => 'getLineItem',
431 exceptionNumberIn => sqlcode,
432 exceptionStringIn => '(attribute ID ' ||
433 attributeIdIn||
434 ') ' ||
435 sqlerrm);
436 raise;
437 return(ame_util.booleanFalse);
438 end getLineItem;
439 function getName(attributeIdIn in integer) return varchar2 as
440 name ame_attributes.name%type;
441 begin
442 select name
443 into name
444 from ame_attributes
445 where
446 attribute_id = attributeIdIn and
447 sysdate between start_date and
448 nvl(end_date - ame_util.oneSecond, sysdate) ;
449 return(name);
450 exception
451 when others then
452 rollback;
453 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
454 routineNameIn => 'getName',
455 exceptionNumberIn => sqlcode,
456 exceptionStringIn => '(attribute ID ' ||
457 attributeIdIn||
458 ') ' ||
459 sqlerrm);
460 raise;
461 return(null);
462 end getName;
463 function getQueryString(attributeIdIn in integer,
464 applicationIdIn in integer) return varchar2 as
465 errorCode integer;
466 errorMessage ame_util.longestStringType;
467 queryString ame_attribute_usages.query_string%type;
468 begin
469 select query_string
470 into queryString
471 from ame_attribute_usages
472 where
473 attribute_id = attributeIdIn and
474 application_id = applicationIdIn and
475 sysdate between start_date and
476 nvl(end_date - ame_util.oneSecond, sysdate) ;
477 return(queryString);
478 exception
479 when no_data_found then
480 rollback;
481 errorCode := -20001;
482 errorMessage :=
483 ame_util.getMessage(applicationShortNameIn => 'PER',
484 messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
485 tokenNameOneIn => 'ATTRIBUTE',
486 tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
487 tokenNameTwoIn => 'APPLICATION',
488 tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
489 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
490 routineNameIn => 'getQueryString',
491 exceptionNumberIn => errorCode,
492 exceptionStringIn => errorMessage);
493 raise_application_error(errorCode,
494 errorMessage);
495 return(null);
496 when others then
497 rollback;
498 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
499 routineNameIn => 'getQueryString',
500 exceptionNumberIn => sqlcode,
501 exceptionStringIn => '(attribute ID ' ||
502 attributeIdIn||
503 ') ' ||
504 sqlerrm);
505 raise;
506 return(null);
507 end getQueryString;
508 function getStartDate(attributeIdIn in integer) return date as
509 startDate date;
510 begin
511 select start_date
512 into startDate
513 from ame_attributes
514 where
515 attribute_id = attributeIdIn and
516 sysdate between start_date and
517 nvl(end_date - ame_util.oneSecond, sysdate) ;
518 return(startDate);
519 exception
520 when others then
521 rollback;
522 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
523 routineNameIn => 'getStartDate',
524 exceptionNumberIn => sqlcode,
525 exceptionStringIn => '(attribute ID ' ||
526 attributeIdIn||
527 ') ' ||
528 sqlerrm);
529 raise;
530 return(null);
531 end getStartDate;
532 function getStaticUsage(attributeIdIn in integer,
533 applicationIdIn in integer) return varchar2 as
534 staticUsage ame_attribute_usages.is_static%type;
535 begin
536 select is_static
537 into staticUsage
538 from ame_attribute_usages
539 where attribute_id = attributeIdIn and
540 application_id = applicationIdIn and
541 sysdate between start_date and
542 nvl(end_date - ame_util.oneSecond, sysdate) ;
543 return(staticUsage);
544 exception
545 when others then
546 rollback;
547 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
548 routineNameIn => 'getStaticUsage',
549 exceptionNumberIn => sqlcode,
550 exceptionStringIn => '(attribute ID ' ||
551 attributeIdIn||
552 ') ' ||
553 sqlerrm);
554 raise;
555 return(null);
556 end getStaticUsage;
557 function getUseCount(attributeIdIn in integer,
558 applicationIdIn in integer) return varchar2 as
559 errorCode integer;
560 errorMessage ame_util.longestStringType;
561 useCount ame_attribute_usages.use_count%type;
562 begin
563 select use_count
564 into useCount
565 from ame_attribute_usages
566 where
567 attribute_id = attributeIdIn and
568 application_id = applicationIdIn and
569 sysdate between start_date and
570 nvl(end_date - ame_util.oneSecond, sysdate) ;
571 return(useCount);
572 exception
573 when no_data_found then
574 rollback;
575 errorCode := -20001;
576 errorMessage :=
577 ame_util.getMessage(applicationShortNameIn => 'PER',
578 messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
579 tokenNameOneIn => 'ATTRIBUTE',
580 tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
581 tokenNameTwoIn => 'APPLICATION',
582 tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
583 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
584 routineNameIn => 'getUseCount',
585 exceptionNumberIn => errorCode,
586 exceptionStringIn => errorMessage);
587 raise_application_error(errorCode,
588 errorMessage);
589 return(null);
590 when others then
591 rollback;
592 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
593 routineNameIn => 'getUseCount',
594 exceptionNumberIn => sqlcode,
595 exceptionStringIn => '(attribute ID ' ||
596 attributeIdIn||
597 ') ' ||
598 sqlerrm);
599 raise;
600 return(null);
601 end getUseCount;
602 function getUserEditable(attributeIdIn in integer,
603 applicationIdIn in integer) return varchar2 as
604 errorCode integer;
605 errorMessage ame_util.longestStringType;
606 userEditable ame_attribute_usages.user_editable%type;
607 begin
608 select user_editable
609 into userEditable
610 from ame_attribute_usages
611 where
612 attribute_id = attributeIdIn and
613 application_id = applicationIdIn and
614 sysdate between start_date and
615 nvl(end_date - ame_util.oneSecond, sysdate) ;
616 return(userEditable);
617 exception
618 when no_data_found then
619 rollback;
620 errorCode := -20001;
621 errorMessage :=
622 ame_util.getMessage(applicationShortNameIn => 'PER',
623 messageNameIn => 'AME_400149_ATT_TTY_NO_USAGE',
624 tokenNameOneIn => 'ATTRIBUTE',
625 tokenValueOneIn => getName(attributeIdIn => attributeIdIn),
626 tokenNameTwoIn => 'APPLICATION',
627 tokenValueTwoIn => ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn));
628 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
629 routineNameIn => 'getUserEditable',
630 exceptionNumberIn => errorCode,
631 exceptionStringIn => errorMessage);
632 raise_application_error(errorCode,
633 errorMessage);
634 return(null);
635 when others then
636 rollback;
637 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
638 routineNameIn => 'getUserEditable',
639 exceptionNumberIn => sqlcode,
640 exceptionStringIn => '(attribute ID ' ||
641 attributeIdIn||
642 ') ' ||
643 sqlerrm);
644 raise;
645 return(null);
646 end getUserEditable;
647 function getChildVersionStartDate(attributeIdIn in integer,
648 applicationIdIn in integer) return varchar2 as
649 startDate date;
650 stringStartDate varchar2(50);
651 begin
652 select start_date
653 into startDate
654 from ame_attribute_usages
655 where
656 attribute_id = attributeIdIn and
657 application_id = applicationIdIn and
658 sysdate between start_date and
659 nvl(end_date - ame_util.oneSecond, sysdate) ;
660 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
661 return(stringStartDate);
662 exception
663 when others then
664 rollback;
665 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
666 routineNameIn => 'getChildVersionStartDate',
667 exceptionNumberIn => sqlcode,
668 exceptionStringIn => '(attribute ID ' ||
669 attributeIdIn||
670 ') ' ||
671 sqlerrm);
672 raise;
673 return(null);
674 end getChildVersionStartDate;
675 function getParentVersionStartDate(attributeIdIn in integer) return varchar2 as
676 startDate date;
677 stringStartDate varchar2(50);
678 begin
679 select start_date
680 into startDate
681 from ame_attributes
682 where
683 attribute_id = attributeIdIn and
684 sysdate between start_date and
685 nvl(end_date - ame_util.oneSecond, sysdate) ;
686 stringStartDate := ame_util.versionDateToString(dateIn => startDate);
687 return(stringStartDate);
688 exception
689 when others then
690 rollback;
691 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
692 routineNameIn => 'getParentVersionStartDate',
693 exceptionNumberIn => sqlcode,
694 exceptionStringIn => '(attribute ID ' ||
695 attributeIdIn||
696 ') ' ||
697 sqlerrm);
698 raise;
699 return(null);
700 end getParentVersionStartDate;
701 function getType(attributeIdIn in integer) return varchar2 as
702 attributeType ame_attributes.attribute_type%type;
703 begin
704 select attribute_type
705 into attributeType
706 from ame_attributes
707 where
708 attribute_id = attributeIdIn and
709 sysdate between start_date and
710 nvl(end_date - ame_util.oneSecond, sysdate) ;
711 return(attributeType);
712 exception
713 when others then
714 rollback;
715 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
716 routineNameIn => 'getType',
717 exceptionNumberIn => sqlcode,
718 exceptionStringIn => '(attribute ID ' ||
719 attributeIdIn||
720 ') ' ||
721 sqlerrm);
722 raise;
723 return(null);
724 end getType;
725 function inputToCanonStaticCurUsage(attributeIdIn in integer,
726 applicationIdIn in integer,
727 queryStringIn varchar2) return varchar2 as
728 amount ame_util.attributeValueType;
729 conversionType ame_util.attributeValueType;
730 convTypeException exception;
731 curCodeException exception;
732 currencyCode ame_util.attributeValueType;
733 errorCode integer;
734 errorMessage ame_util.longestStringType;
735 begin
736 /*
737 The ame_util.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
738 but it doesn't validate the individual values, or convert the amount to canonical format.
739 */
740 ame_util.parseStaticCurAttValue(applicationIdIn => applicationIdIn,
741 attributeIdIn => attributeIdIn,
742 attributeValueIn => queryStringIn,
743 amountOut => amount,
744 localErrorIn => true,
745 currencyOut => currencyCode,
746 conversionTypeOut => conversionType);
747 /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
748 amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
749 currencyCodeIn => currencyCode);
750 if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
751 raise curCodeException;
752 end if;
753 if not ame_util.isConversionTypeValid(conversionTypeIn => conversionType) then
754 raise convTypeException;
755 end if;
756 return(amount || ',' || currencyCode || ',' || conversionType);
757 exception
758 when convTypeException then
759 rollback;
760 errorCode := -20001;
761 errorMessage :=
762 ame_util.getMessage(applicationShortNameIn => 'PER',
763 messageNameIn => 'AME_400150_ATT_STA_CONV_INV');
764 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
765 routineNameIn => 'inputToCanonStaticCurUsage',
766 exceptionNumberIn => errorCode,
767 exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
768 raise_application_error(errorCode,
769 errorMessage);
770 return(null);
771 when curCodeException then
772 rollback;
773 errorCode := -20001;
774 errorMessage :=
775 ame_util.getMessage(applicationShortNameIn => 'PER',
776 messageNameIn => 'AME_400151_ATT_STA_CURR_INV');
777 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
778 routineNameIn => 'inputToCanonStaticCurUsage',
779 exceptionNumberIn => errorCode,
780 exceptionStringIn => errorMessage); /* Runtime code doesn't validate input. */
781 raise_application_error(errorCode,
782 errorMessage);
783 return(null);
784 when others then
785 rollback;
786 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
787 routineNameIn => 'inputToCanonStaticCurUsage',
788 exceptionNumberIn => sqlcode,
789 exceptionStringIn => '(attribute ID ' ||
790 attributeIdIn||
791 ') ' ||
792 sqlerrm); /* Runtime code doesn't validate input. */
793 raise;
794 return(null);
795 end inputToCanonStaticCurUsage;
796 function hasUsage(attributeIdIn in integer,
797 applicationIdIn in integer) return boolean as
798 attributeCount integer;
799 begin
800 select count(*)
801 into attributeCount
802 from ame_attribute_usages
803 where
804 attribute_id = attributeIdIn and
805 application_id <> applicationIdIn and
806 sysdate between start_date and
807 nvl(end_date - ame_util.oneSecond, sysdate) ;
808 if(attributeCount > 0) then
809 return(true);
810 end if;
811 return(false);
812 exception
813 when others then
814 rollback;
815 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
816 routineNameIn => 'hasUsage',
817 exceptionNumberIn => sqlcode,
818 exceptionStringIn => '(attribute ID ' ||
819 attributeIdIn||
820 ') ' ||
821 sqlerrm);
822 raise;
823 return(true); /* conservative: avoids allowing deletion if might still be in use */
824 end hasUsage;
825 /*
826 AME_STRIPING
827 function isAStripingAttribute(applicationIdIn in integer,
828 attributeIdIn in integer) return boolean as
829 stripingAttributeIds ame_util.idList;
830 useCount integer;
831 begin
832 select
833 to_number(value_1),
834 to_number(value_2),
835 to_number(value_3),
836 to_number(value_4),
837 to_number(value_5)
838 into
839 stripingAttributeIds(1),
840 stripingAttributeIds(2),
841 stripingAttributeIds(3),
842 stripingAttributeIds(4),
843 stripingAttributeIds(5)
844 from ame_stripe_sets
845 where
846 application_id = applicationIdIn and
847 stripe_set_id = 0 and
848 sysdate between start_date and
849 nvl(end_date - ame_util.oneSecond, sysdate) ;
850 for i in 1..5 loop
851 if(stripingAttributeIds(i) = attributeIdIn) then
852 return(true);
853 end if;
854 end loop;
855 return(false);
856 exception
857 when others then
858 rollback;
859 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
860 routineNameIn => 'isAStripingAttribute',
861 exceptionNumberIn => sqlcode,
862 exceptionStringIn => '(attribute ID ' ||
863 attributeIdIn||
864 ') ' ||
865 sqlerrm);
866 raise;
867 return(true);
868 end isAStripingAttribute;
869 */
870 function isInUse(attributeIdIn in integer) return boolean as
871 useCount integer;
872 begin
873 select count(*)
874 into useCount
875 from
876 ame_conditions
877 where
878 attribute_id = attributeIdIn and
879 sysdate between start_date and
880 nvl(end_date - ame_util.oneSecond, sysdate) ;
881 if(useCount > 0) then
882 return(true);
883 end if;
884 return(false);
885 exception
886 when others then
887 rollback;
888 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
889 routineNameIn => 'isInUse',
890 exceptionNumberIn => sqlcode,
891 exceptionStringIn => '(attribute ID ' ||
892 attributeIdIn||
893 ') ' ||
894 sqlerrm);
895 raise;
896 return(true); /* conservative: avoids allowing deletion if might still be in use */
897 end isInUse;
898 function isInUseByApplication(attributeIdIn in integer,
899 applicationIdIn in integer) return boolean as
900 useCount integer;
901 begin
902 select use_count
903 into useCount
904 from ame_attribute_usages
905 where
906 attribute_id = attributeIdIn and
907 application_id = applicationIdIn and
908 sysdate between start_date and
909 nvl(end_date - ame_util.oneSecond, sysdate) ;
910 if(useCount > 0) then
911 return(true);
912 end if;
913 return(false);
914 exception
915 when no_data_found then
916 rollback;
917 return(false);
918 when others then
919 rollback;
920 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
921 routineNameIn => 'isInUseByApplication',
922 exceptionNumberIn => sqlcode,
923 exceptionStringIn => '(attribute ID ' ||
924 attributeIdIn||
925 ') ' ||
926 sqlerrm);
927 raise;
928 return(true); /* conservative: avoids allowing deletion if might still be in use */
929 end isInUseByApplication;
930 function isLineItem(attributeIdIn in integer) return boolean as
931 lineItemCount integer;
932 begin
933 select count(*)
934 into lineItemCount
935 from ame_attributes
936 where
937 attribute_id = attributeIdIn and
938 line_item = ame_util.booleanTrue and
939 sysdate between start_date and
940 nvl(end_date - ame_util.oneSecond, sysdate) ;
941 if lineItemCount > 0 then
942 return(true);
943 end if;
944 return(false);
945 exception
946 when others then
947 rollback;
948 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
949 routineNameIn => 'isLineItem',
950 exceptionNumberIn => sqlcode,
951 exceptionStringIn => '(attribute ID ' ||
952 attributeIdIn||
953 ') ' ||
954 sqlerrm);
955 raise;
956 return(false);
957 end isLineItem;
958 function isMandatory(attributeIdIn in integer) return boolean is
959 mandatoryCount integer;
960 begin
961 select count(*)
962 into mandatoryCount
963 from ame_mandatory_attributes
964 where action_type_id = -1 and
965 attribute_id = attributeIdIn and
966 sysdate between start_date and
967 nvl(end_date - ame_util.oneSecond, sysdate) ;
968 if mandatoryCount > 0 then
969 return(true);
970 end if;
971 return(false);
972 exception
973 when others then
974 rollback;
975 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
976 routineNameIn => 'isMandatory',
977 exceptionNumberIn => sqlcode,
978 exceptionStringIn => '(attribute ID ' ||
979 attributeIdIn||
980 ') ' ||
981 sqlerrm);
982 raise;
983 return(true);
984 end isMandatory;
985 function isNonHeaderAttributeItem(attributeIdIn in integer) return boolean as
986 itemClassId integer;
987 begin
988 select item_class_id
989 into itemClassId
990 from ame_attributes
991 where
992 attribute_id = attributeIdIn and
993 sysdate between start_date and
994 nvl(end_date - ame_util.oneSecond, sysdate);
995 if(itemClassId <> ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
996 ame_util.headerItemClassName)) then
997 return(true);
998 end if;
999 return(false);
1000 exception
1001 when others then
1002 rollback;
1003 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1004 routineNameIn => 'isNonHeaderAttributeItem',
1005 exceptionNumberIn => sqlcode,
1006 exceptionStringIn => '(attribute ID ' ||
1007 attributeIdIn||
1008 ') ' ||
1009 sqlerrm);
1010 raise;
1011 return(false);
1012 end isNonHeaderAttributeItem;
1013 function isRequired(attributeIdIn in integer) return boolean is
1014 requiredCount integer;
1015 begin
1016 select count(*)
1017 into requiredCount
1018 from ame_mandatory_attributes
1019 where
1020 action_type_id <> -1 and
1021 attribute_id = attributeIdIn and
1022 sysdate between start_date and
1023 nvl(end_date - ame_util.oneSecond, sysdate) ;
1024 if requiredCount > 0 then
1025 return(true);
1026 end if;
1027 return(false);
1028 exception
1029 when others then
1030 rollback;
1031 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1032 routineNameIn => 'isRequired',
1033 exceptionNumberIn => sqlcode,
1034 exceptionStringIn => '(attribute ID ' ||
1035 attributeIdIn||
1036 ') ' ||
1037 sqlerrm);
1038 raise;
1039 return(true);
1040 end isRequired;
1041 function isSeeded(attributeIdIn in integer) return boolean as
1042 createdByValue integer;
1043 begin
1044 select created_by
1045 into createdByValue
1046 from ame_attributes
1047 where
1048 attribute_id = attributeIdIn and
1049 sysdate between start_date and
1050 nvl(end_date - ame_util.oneSecond, sysdate) ;
1051 if(createdByValue = 1) then
1052 return(true);
1053 end if;
1054 return(false);
1055 exception
1056 when others then
1057 rollback;
1058 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1059 routineNameIn => 'isSeeded',
1060 exceptionNumberIn => sqlcode,
1061 exceptionStringIn => '(attribute ID ' ||
1062 attributeIdIn||
1063 ') ' ||
1064 sqlerrm);
1065 raise;
1066 return(true); /* conservative: avoids allowing deletion if might still be in use */
1067 end isSeeded;
1068 /*
1069 AME_STRIPING
1070 function isStripingAttribute(applicationIdIn in integer,
1071 attributeIdIn in integer) return boolean as
1072 isStripingAttribute ame_attribute_usages.is_striping_attribute%type;
1073 begin
1074 select is_striping_attribute
1075 into isStripingAttribute
1076 from ame_attribute_usages
1077 where
1078 attribute_id = attributeIdIn and
1079 application_id = applicationIdIn and
1080 (start_date <= sysdate and
1081 (end_date is null or sysdate < end_date));
1082 if(isStripingAttribute = ame_util.booleanTrue) then
1083 return(true);
1084 end if;
1085 return(false);
1086 exception
1087 when no_data_found then
1088 rollback;
1089 return(false);
1090 when others then
1091 rollback;
1092 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1093 routineNameIn => 'isStripingAttribute',
1094 exceptionNumberIn => sqlcode,
1095 exceptionStringIn => '(attribute ID ' ||
1096 attributeIdIn||
1097 ') ' ||
1098 sqlerrm);
1099 raise;
1100 return(true);
1101 end isStripingAttribute;
1102 */
1103 function nameExists(nameIn in varchar2) return boolean as
1104 tempCount integer;
1105 begin
1106 select count(*)
1107 into tempCount
1108 from ame_attributes
1109 where
1110 name = upper(nameIn) and
1111 sysdate between start_date and
1112 nvl(end_date - ame_util.oneSecond, sysdate) ;
1113 if(tempCount > 0) then
1114 return(true);
1115 end if;
1116 return(false);
1117 exception
1118 when others then
1119 rollback;
1120 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1121 routineNameIn => 'nameExists',
1122 exceptionNumberIn => sqlcode,
1123 exceptionStringIn => sqlerrm);
1124 raise;
1125 return(true); /* conservative: avoids possibility of re-creation of existing name */
1126 end nameExists;
1127 function new(nameIn in varchar2,
1128 typeIn in varchar2,
1129 descriptionIn in varchar2,
1130 itemClassIdIn in integer,
1131 approverTypeIdIn in integer default null,
1132 finalizeIn in boolean default false,
1133 newStartDateIn in date default null,
1134 attributeIdIn in integer default null,
1135 createdByIn in integer default null) return integer as
1136 attributeExistsException exception;
1137 attributeId integer;
1138 attributeName ame_attributes.name%type;
1139 createdBy integer;
1140 currentUserId integer;
1141 descriptionLengthException exception;
1142 errorCode integer;
1143 errorMessage ame_util.longestStringType;
1144 lineItem varchar2(1);
1145 nameLengthException exception;
1146 typeLengthException exception;
1147 processingDate date;
1148 tempCount integer;
1149 begin
1150 attributeName := upper(trim(trailing ' ' from nameIn));
1151 processingDate := sysdate;
1152 begin
1153 select attribute_id
1154 into attributeId
1155 from ame_attributes
1156 where
1157 (attributeIdIn is null or attribute_id <> attributeIdIn) and
1158 name = attributeName and
1159 sysdate between start_date and
1160 nvl(end_date - ame_util.oneSecond, sysdate) ;
1161 if attributeId is not null then
1162 raise attributeExistsException;
1163 end if;
1164 exception
1165 when no_data_found then null;
1166 end;
1167 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1168 columnNameIn => 'name',
1169 argumentIn => attributeName)) then
1170 raise nameLengthException;
1171 end if;
1172 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1173 columnNameIn => 'attribute_type',
1174 argumentIn => typeIn)) then
1175 raise typeLengthException;
1176 end if;
1177 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attributes',
1178 columnNameIn => 'description',
1179 argumentIn => descriptionIn)) then
1180 raise descriptionLengthException;
1181 end if;
1182 /*
1183 If any version of the object has created_by = 1, all versions,
1184 including the new version, should. This is a failsafe way to check
1185 whether previous versions of an already end-dated object had
1186 created_by = 1.
1187 */
1188 currentUserId := ame_util.getCurrentUserId;
1189 if(attributeIdIn is null) then
1190 if(createdByIn is null) then
1191 createdBy := currentUserId;
1192 else
1193 createdBy := createdByIn;
1194 end if;
1195 select ame_attributes_s.nextval into attributeId from dual;
1196 else
1197 attributeId := attributeIdIn;
1198 select count(*)
1199 into tempCount
1200 from ame_attributes
1201 where
1202 attribute_id = attributeId and
1203 created_by = ame_util.seededDataCreatedById;
1204 if(tempCount > 0) then
1205 createdBy := ame_util.seededDataCreatedById;
1206 elsif(createdByIn is null) then
1207 createdBy := currentUserId;
1208 else
1209 createdBy := createdByIn;
1210 end if;
1211 end if;
1212 insert into ame_attributes(attribute_id,
1213 name,
1214 attribute_type,
1215 created_by,
1216 creation_date,
1217 last_updated_by,
1218 last_update_date,
1219 last_update_login,
1220 start_date,
1221 end_date,
1222 description,
1223 line_item,
1224 approver_type_id,
1225 item_class_id)
1226 values(attributeId,
1227 attributeName,
1228 typeIn,
1229 createdBy,
1230 processingDate,
1231 currentUserId,
1232 processingDate,
1233 currentUserId,
1234 nvl(newStartDateIn, processingDate),
1235 null,
1236 descriptionIn,
1237 null,
1238 approverTypeIdIn,
1239 itemClassIdIn);
1240 if(finalizeIn) then
1241 commit;
1242 end if;
1243 return(attributeId);
1244 exception
1245 when attributeExistsException then
1246 rollback;
1247 errorCode := -20001;
1248 errorMessage :=
1249 ame_util.getMessage(applicationShortNameIn => 'PER',
1250 messageNameIn => 'AME_400152_ATT_NAME_EXISTS');
1251 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1252 routineNameIn => 'new',
1253 exceptionNumberIn => errorCode,
1254 exceptionStringIn => errorMessage);
1255 raise_application_error(errorCode,
1256 errorMessage);
1257 return(null);
1258 when nameLengthException then
1259 rollback;
1260 errorCode := -20001;
1261 errorMessage :=
1262 ame_util.getMessage(applicationShortNameIn => 'PER',
1263 messageNameIn => 'AME_400153_ATT_NAME_LONG',
1264 tokenNameOneIn => 'COLUMN_LENGTH',
1265 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1266 columnNameIn => 'name'));
1267 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1268 routineNameIn => 'new',
1269 exceptionNumberIn => errorCode,
1270 exceptionStringIn => errorMessage);
1271 raise_application_error(errorCode,
1272 errorMessage);
1273 return(null);
1274 when typeLengthException then
1275 rollback;
1276 errorCode := -20001;
1277 errorMessage :=
1278 ame_util.getMessage(applicationShortNameIn => 'PER',
1279 messageNameIn => 'AME_400154_ATT_TYPE_LONG',
1280 tokenNameOneIn => 'COLUMN_LENGTH',
1281 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1282 columnNameIn => 'attribute_type'));
1283 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1284 routineNameIn => 'new',
1285 exceptionNumberIn => errorCode,
1286 exceptionStringIn => errorMessage);
1287 raise_application_error(errorCode,
1288 errorMessage);
1289 return(null);
1290 when descriptionLengthException then
1291 rollback;
1292 errorCode := -20001;
1293 errorMessage :=
1294 ame_util.getMessage(applicationShortNameIn => 'PER',
1295 messageNameIn => 'AME_400155_ATT_DESC_LONG',
1296 tokenNameOneIn => 'COLUMN_LENGTH',
1297 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attributes',
1298 columnNameIn => 'description'));
1299 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1300 routineNameIn => 'new',
1301 exceptionNumberIn => errorCode,
1302 exceptionStringIn => errorMessage);
1303 raise_application_error(errorCode,
1304 errorMessage);
1305 return(null);
1306 when others then
1307 rollback;
1308 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1309 routineNameIn => 'new',
1310 exceptionNumberIn => sqlcode,
1311 exceptionStringIn => '(attribute ID ' ||
1312 attributeIdIn||
1313 ') ' ||
1314 sqlerrm);
1315 raise;
1316 return(null);
1317 end new;
1318 function usageIsUserEditable(attributeIdIn in integer,
1319 applicationIdIn in integer) return boolean as
1320 isEditable varchar2(1);
1321 begin
1322 if not isSeeded(attributeIdIn => attributeIdIn) then
1323 return(true);
1324 end if;
1325 select user_editable
1326 into isEditable
1327 from ame_attribute_usages
1328 where attribute_id = attributeIdIn and
1329 application_id = applicationIdIn and
1330 sysdate between start_date and
1331 nvl(end_date - ame_util.oneSecond, sysdate) ;
1332 if isEditable = ame_util.booleanTrue then
1333 return(true);
1334 end if;
1335 return(false);
1336 exception
1337 when others then
1338 rollback;
1339 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1340 routineNameIn => 'usageIsUserEditable',
1341 exceptionNumberIn => sqlcode,
1342 exceptionStringIn => '(attribute ID ' ||
1343 attributeIdIn||
1344 ') ' ||
1345 sqlerrm);
1346 raise;
1347 return(false);
1348 end usageIsUserEditable;
1349 procedure change(attributeIdIn in integer,
1350 applicationIdIn in integer default null,
1351 nameIn in varchar2,
1352 typeIn in varchar2,
1353 startDateIn in date,
1354 endDateIn in date,
1355 descriptionIn in varchar2 default null,
1356 itemClassIdIn in integer,
1357 finalizeIn in boolean default false) as
1358 approverTypeId integer;
1359 attributeId integer;
1360 currentUserId integer;
1361 begin
1362 currentUserId := ame_util.getCurrentUserId;
1363 approverTypeId := getApproverTypeId(attributeIdIn => attributeIdIn);
1364 update ame_attributes
1365 set
1366 last_updated_by = currentUserId,
1367 last_update_date = endDateIn,
1368 last_update_login = currentUserId,
1369 end_date = endDateIn
1370 where
1371 attribute_id = attributeIdIn and
1372 sysdate between start_date and
1373 nvl(end_date - ame_util.oneSecond, sysdate);
1374 attributeId := new(nameIn => nameIn,
1375 typeIn => typeIn,
1376 descriptionIn => descriptionIn,
1377 attributeIdIn => attributeIdIn,
1378 itemClassIdIn => itemClassIdIn,
1379 newStartDateIn => startDateIn,
1380 approverTypeIdIn => approverTypeId,
1381 finalizeIn => false);
1382 if(finalizeIn) then
1383 commit;
1384 end if;
1385 exception
1386 when others then
1387 rollback;
1388 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1389 routineNameIn => 'change',
1390 exceptionNumberIn => sqlcode,
1391 exceptionStringIn => '(attribute ID ' ||
1392 attributeIdIn||
1393 ') ' ||
1394 sqlerrm);
1395 raise;
1396 end change;
1397 procedure changeAttributeAndUsage(attributeIdIn in integer,
1398 applicationIdIn in integer default null,
1399 staticUsageIn in varchar2,
1400 queryStringIn in varchar2 default null,
1401 nameIn in varchar2 default null,
1402 descriptionIn in varchar2 default null,
1403 parentVersionStartDateIn in date,
1404 childVersionStartDateIn in date,
1405 itemClassIdIn in integer,
1406 finalizeIn in boolean default false) as
1407 cursor startDateCursor is
1408 select start_date
1409 from ame_attributes
1410 where
1411 attribute_id = attributeIdIn and
1412 sysdate between start_date and
1413 nvl(end_date - ame_util.oneSecond, sysdate)
1414 for update;
1415 cursor startDateCursor2 is
1416 select start_date
1417 from ame_attribute_usages
1418 where
1419 attribute_id = attributeIdIn and
1420 application_id = applicationIdIn and
1421 sysdate between start_date and
1422 nvl(end_date - ame_util.oneSecond, sysdate)
1423 for update;
1424 attributeId integer;
1425 currentUserId integer;
1426 errorCode integer;
1427 errorMessage ame_util.longestStringType;
1428 name ame_attributes.name%type;
1429 attributeType ame_attributes.attribute_type%type;
1430 description ame_attributes.description%type;
1431 invalidReferenceException exception;
1432 newStartAndEndDate date;
1433 objectVersionNoDataException exception;
1434 queryString ame_attribute_usages.query_string%type;
1435 startDate date;
1436 startDate2 date;
1437 tempCount integer;
1438 begin
1439 /* Try to get a lock on the record. */
1440 open startDateCursor;
1441 fetch startDateCursor into startDate;
1442 if startDateCursor%notfound then
1443 raise objectVersionNoDataException;
1444 end if;
1445 if(parentVersionStartDateIn <> startDate) then
1446 close startDateCursor;
1447 raise ame_util.objectVersionException;
1448 end if;
1449 open startDateCursor2;
1450 fetch startDateCursor2 into startDate2;
1451 if startDateCursor2%notfound then
1452 raise objectVersionNoDataException;
1453 end if;
1454 if(childVersionStartDateIn <> startDate2) then
1455 close startDateCursor2;
1456 raise ame_util.objectVersionException;
1457 end if;
1458 attributeType := getType(attributeIdIn => attributeIdIn);
1459 if(staticUsageIn = ame_util.booleanTrue) then
1460 queryString := ame_util.removeReturns(stringIn => queryStringIn,
1461 replaceWithSpaces => false);
1462 else
1463 queryString := queryStringIn;
1464 end if;
1465 /* Check whether the input values match the existing values; if so, just return. */
1466 select count(*)
1467 into tempCount
1468 from
1469 ame_attributes,
1470 ame_attribute_usages
1471 where
1472 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1473 ame_attributes.attribute_id = attributeIdIn and
1474 ame_attribute_usages.application_id = applicationIdIn and
1475 ame_attribute_usages.is_static = staticUsageIn and
1476 ame_attribute_usages.query_string = queryString and
1477 (nameIn is null or name = upper(nameIn)) and
1478 (attributeType is null or upper(attribute_type) = upper(attributeType)) and
1479 (descriptionIn is null or description = descriptionIn) and
1480 sysdate between ame_attributes.start_date and
1481 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1482 sysdate between ame_attribute_usages.start_date and
1483 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) ;
1484 if(tempCount > 0) then
1485 return;
1486 end if;
1487 /* Get current values as necessary for update. */
1488 if(nameIn is null) then
1489 name := getName(attributeIdIn => attributeIdIn);
1490 else
1491 name := nameIn;
1492 end if;
1493 if(descriptionIn is null) then
1494 description := getDescription(attributeIdIn => attributeIdIn);
1495 else
1496 description := descriptionIn;
1497 end if;
1498 newStartAndEndDate := sysdate;
1499 ame_attribute_pkg.change(attributeIdIn => attributeIdIn,
1500 applicationIdIn => applicationIdIn,
1501 nameIn => name,
1502 typeIn => attributeType,
1503 endDateIn => newStartAndEndDate,
1504 startDateIn => newStartAndEndDate,
1505 descriptionIn => description,
1506 itemClassIdIn => itemClassIdIn,
1507 finalizeIn => false);
1508 ame_attribute_pkg.changeUsage(attributeIdIn => attributeIdIn,
1509 applicationIdIn => applicationIdIn,
1510 staticUsageIn => staticUsageIn,
1511 queryStringIn => queryString,
1512 endDateIn => newStartAndEndDate,
1513 newStartDateIn => newStartAndEndDate,
1514 finalizeIn => false);
1515 close startDateCursor2;
1516 close startDateCursor;
1517 if(finalizeIn) then
1518 commit;
1519 end if;
1520 exception
1521 when ame_util.objectVersionException then
1522 rollback;
1523 if(startDateCursor%isOpen) then
1524 close startDateCursor;
1525 end if;
1526 if(startDateCursor2%isOpen) then
1527 close startDateCursor2;
1528 end if;
1529 errorCode := -20001;
1530 errorMessage :=
1531 ame_util.getMessage(applicationShortNameIn => 'PER',
1532 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
1533 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1534 routineNameIn => 'changeAttributeAndUsage',
1535 exceptionNumberIn => errorCode,
1536 exceptionStringIn => errorMessage);
1537 raise_application_error(errorCode,
1538 errorMessage);
1539 when objectVersionNoDataException then
1540 rollback;
1541 if(startDateCursor%isOpen) then
1542 close startDateCursor;
1543 end if;
1544 if(startDateCursor2%isOpen) then
1545 close startDateCursor2;
1546 end if;
1547 errorCode := -20001;
1548 errorMessage :=
1549 ame_util.getMessage(applicationShortNameIn => 'PER',
1550 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
1551 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1552 routineNameIn => 'changeAttributeAndUsage',
1553 exceptionNumberIn => errorCode,
1554 exceptionStringIn => errorMessage);
1555 raise_application_error(errorCode,
1556 errorMessage);
1557 when invalidReferenceException then
1558 rollback;
1559 if(startDateCursor%isOpen) then
1560 close startDateCursor;
1561 end if;
1562 if(startDateCursor2%isOpen) then
1563 close startDateCursor2;
1564 end if;
1565 errorCode := -20001;
1566 errorMessage :=
1567 ame_util.getMessage(applicationShortNameIn => 'PER',
1568 messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
1569 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1570 routineNameIn => 'changeAttributeAndUsage',
1571 exceptionNumberIn => errorCode,
1572 exceptionStringIn => errorMessage);
1573 raise_application_error(errorCode,
1574 errorMessage);
1575 when others then
1576 rollback;
1577 if(startDateCursor%isOpen) then
1578 close startDateCursor;
1579 end if;
1580 if(startDateCursor2%isOpen) then
1581 close startDateCursor2;
1582 end if;
1583 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1584 routineNameIn => 'changeAttributeAndUsage',
1585 exceptionNumberIn => sqlcode,
1586 exceptionStringIn => '(attribute ID ' ||
1587 attributeIdIn||
1588 ') ' ||
1589 sqlerrm);
1590 raise;
1591 end changeAttributeAndUsage;
1592 /*
1593 AME_STRIPING
1594 procedure changeUsage(attributeIdIn in integer,
1595 applicationIdIn in integer,
1596 staticUsageIn in varchar2,
1597 queryStringIn in varchar2 default null,
1598 endDateIn in date,
1599 newStartDateIn in date,
1600 lineItemAttributeIn in varchar2,
1601 isStripingAttributeIn in varchar2 default null,
1602 finalizeIn in boolean default true) as
1603 */
1604 procedure changeUsage(attributeIdIn in integer,
1605 applicationIdIn in integer,
1606 staticUsageIn in varchar2,
1607 queryStringIn in varchar2 default null,
1608 endDateIn in date,
1609 newStartDateIn in date,
1610 finalizeIn in boolean default false) as
1611 attributeName ame_attributes.name%type;
1612 attributeType ame_attributes.attribute_type%type;
1613 comma1Location integer;
1614 comma2Location integer;
1615 currentUserId integer;
1616 errorCode integer;
1617 errorMessage ame_util.longestStringType;
1618 firstReturnLocation integer;
1619 loweredQueryString varchar2(4000);
1620 queryString ame_attribute_usages.query_string%type;
1621 queryStringColumnException exception;
1622 tempCount integer;
1623 transactionType ame_calling_apps.application_name%type;
1624 begin
1625 attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
1626 if(staticUsageIn = ame_util.booleanTrue) then
1627 queryString := ame_util.removeReturns(stringIn => queryStringIn,
1628 replaceWithSpaces => false);
1629 if(attributeType = ame_util.numberAttributeType) then
1630 queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
1631 end if;
1632 else
1633 queryString := queryStringIn;
1634 if(attributeType = ame_util.currencyAttributeType) then
1635 loweredQueryString := lower(queryString);
1636 if(instrb(loweredQueryString, ',', 1, 2) = 0 or
1637 instrb(loweredQueryString, ',', 1, 2) > instrb(loweredQueryString, 'from', 1, 1)) then
1638 raise queryStringColumnException;
1639 end if;
1640 end if;
1641 end if;
1642 currentUserId := ame_util.getCurrentUserId;
1643 update ame_attribute_usages
1644 set
1645 last_updated_by = currentUserId,
1646 last_update_date = endDateIn,
1647 last_update_login = currentUserId,
1648 end_date = endDateIn
1649 where
1650 attribute_id = attributeIdIn and
1651 application_id = applicationIdIn and
1652 sysdate between start_date and
1653 nvl(end_date - ame_util.oneSecond, sysdate) ;
1654 /*
1655 AME_STRIPING
1656 newAttributeUsage(attributeIdIn => attributeIdIn,
1657 applicationIdIn => applicationIdIn,
1658 staticUsageIn => staticUsageIn,
1659 queryStringIn => queryString,
1660 newStartDateIn => newStartDateIn,
1661 lineItemAttributeIn => lineItemAttributeIn,
1662 isStripingAttributeIn => isStripingAttributeIn,
1663 finalizeIn => finalizeIn);
1664 */
1665 newAttributeUsage(attributeIdIn => attributeIdIn,
1666 applicationIdIn => applicationIdIn,
1667 staticUsageIn => staticUsageIn,
1668 updateParentObjectIn => true,
1669 queryStringIn => queryString,
1670 newStartDateIn => newStartDateIn,
1671 finalizeIn => finalizeIn);
1672 exception
1673 when queryStringColumnException then
1674 rollback;
1675 errorCode := -20001;
1676 errorMessage := 'The select clause of a currency attribute''s ' ||
1677 'usage must select three values: ' ||
1678 'amount, currency code, and conversion-type code ';
1679 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1680 routineNameIn => 'changeUsage',
1681 exceptionNumberIn => errorCode,
1682 exceptionStringIn => errorMessage);
1683 raise_application_error(errorCode,
1684 errorMessage);
1685 when others then
1686 rollback;
1687 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1688 routineNameIn => 'changeUsage',
1689 exceptionNumberIn => sqlcode,
1690 exceptionStringIn => '(attribute ID ' ||
1691 attributeIdIn||
1692 ') ' ||
1693 sqlerrm);
1694 raise;
1695 end changeUsage;
1696 procedure getActiveAttributes(applicationIdIn in integer,
1697 attributeIdsOut out nocopy ame_util.idList,
1698 attributeNamesOut out nocopy ame_util.stringList) as
1699 cursor activeAttributeCursor(applicationIdIn in integer) is
1700 select
1701 ame_attributes.attribute_id attribute_id,
1702 ame_attributes.name name
1703 from
1704 ame_attributes,
1705 ame_attribute_usages
1706 where
1707 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1708 ame_attribute_usages.application_id = applicationIdIn and
1709 use_count > 0 and
1710 sysdate between ame_attributes.start_date and
1711 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1712 sysdate between ame_attribute_usages.start_date and
1713 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1714 order by name;
1715 cursor mandatoryAttributeCursor(applicationIdIn in integer) is
1716 select
1717 ame_attributes.attribute_id attribute_id,
1718 ame_attributes.name name
1719 from
1720 ame_attributes,
1721 ame_mandatory_attributes
1722 where
1723 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
1724 ame_mandatory_attributes.action_type_id = -1 and
1725 sysdate between ame_attributes.start_date and
1726 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1727 sysdate between ame_mandatory_attributes.start_date and
1728 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1729 order by name;
1730 tempIndex integer;
1731 begin
1732 tempIndex := 1;
1733 for tempAttribute in mandatoryAttributeCursor(applicationIdIn => applicationIdIn) loop
1734 attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
1735 attributeNamesOut(tempIndex) := tempAttribute.name;
1736 tempIndex := tempIndex + 1;
1737 end loop;
1738 for tempAttribute in activeAttributeCursor(applicationIdIn => applicationIdIn) loop
1739 attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
1740 attributeNamesOut(tempIndex) := tempAttribute.name;
1741 tempIndex := tempIndex + 1;
1742 end loop;
1743 exception
1744 when others then
1745 rollback;
1746 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1747 routineNameIn => 'getActiveAttributes',
1748 exceptionNumberIn => sqlcode,
1749 exceptionStringIn => '(application ID ' ||
1750 applicationIdIn||
1751 ') ' ||
1752 sqlerrm);
1753 raise;
1754 end getActiveAttributes;
1755 procedure getActiveHeaderAttributes(applicationIdIn in integer,
1756 attributeIdsOut out nocopy ame_util.idList,
1757 attributeNamesOut out nocopy ame_util.stringList) as
1758 cursor activeAttributeCursor(applicationIdIn in integer,
1759 itemClassIdIn in integer) is
1760 select
1761 ame_attributes.attribute_id attribute_id,
1762 ame_attributes.name name
1763 from
1764 ame_attributes,
1765 ame_attribute_usages
1766 where
1767 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1768 ame_attribute_usages.application_id = applicationIdIn and
1769 ame_attributes.item_class_id = itemClassIdIn and
1770 use_count > 0 and
1771 sysdate between ame_attributes.start_date and
1772 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1773 sysdate between ame_attribute_usages.start_date and
1774 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1775 order by name;
1776 cursor mandatoryAttributeCursor(applicationIdIn in integer,
1777 itemClassIdIn in integer) is
1778 select
1779 ame_attributes.attribute_id attribute_id,
1780 ame_attributes.name name
1781 from
1782 ame_attributes,
1783 ame_mandatory_attributes
1784 where
1785 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
1786 ame_attributes.item_class_id = itemClassIdIn and
1787 ame_mandatory_attributes.action_type_id = -1 and
1788 sysdate between ame_attributes.start_date and
1789 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1790 sysdate between ame_mandatory_attributes.start_date and
1791 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
1792 order by name;
1793 itemClassId integer;
1794 tempIndex integer;
1795 begin
1796 tempIndex := 1;
1797 itemClassId :=
1798 ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerITemClassName);
1799 for tempAttribute in mandatoryAttributeCursor(applicationIdIn => applicationIdIn,
1800 itemClassIdIn => itemClassId) loop
1801 attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
1802 attributeNamesOut(tempIndex) := tempAttribute.name;
1803 tempIndex := tempIndex + 1;
1804 end loop;
1805 for tempAttribute in activeAttributeCursor(applicationIdIn => applicationIdIn,
1806 itemClassIdIn => itemClassId) loop
1807 attributeIdsOut(tempIndex) := tempAttribute.attribute_id;
1808 attributeNamesOut(tempIndex) := tempAttribute.name;
1809 tempIndex := tempIndex + 1;
1810 end loop;
1811 exception
1812 when others then
1813 rollback;
1814 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1815 routineNameIn => 'getActiveHeaderAttributes',
1816 exceptionNumberIn => sqlcode,
1817 exceptionStringIn => '(application ID ' ||
1818 applicationIdIn||
1819 ') ' ||
1820 sqlerrm);
1821 raise;
1822 end getActiveHeaderAttributes;
1823 procedure getAllAttributes(attributeIdsOut out nocopy ame_util.stringList,
1824 attributeNamesOut out nocopy ame_util.stringList) as
1825 cursor attributeCursor is
1826 select attribute_id, name
1827 from ame_attributes
1828 where
1829 sysdate between start_date and
1830 nvl(end_date - ame_util.oneSecond, sysdate)
1831 order by name;
1832 tempIndex integer;
1833 begin
1834 tempIndex := 1;
1835 for tempAttributeRec in attributeCursor loop
1836 /* The explicit conversion below lets nocopy work. */
1837 attributeIdsOut(tempIndex) := to_char(tempAttributeRec.attribute_id);
1838 attributeNamesOut(tempIndex) := tempAttributeRec.name;
1839 tempIndex := tempIndex + 1;
1840 end loop;
1841 exception
1842 when others then
1843 rollback;
1844 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1845 routineNameIn => 'getAllAttributes',
1846 exceptionNumberIn => sqlcode,
1847 exceptionStringIn => sqlerrm);
1848 raise;
1849 end getAllAttributes;
1850 procedure getApplicationAttributes(applicationIdIn in integer,
1851 attributeIdOut out nocopy ame_util.idList) as
1852 cursor attributeCursor(applicationIdIn in integer) is
1853 select attribute_id
1854 from ame_attribute_usages
1855 where
1856 application_id = applicationIdIn and
1857 sysdate between start_date and
1858 nvl(end_date - ame_util.oneSecond, sysdate)
1859 order by attribute_id;
1860 tempIndex integer;
1861 begin
1862 tempIndex := 1;
1863 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn) loop
1864 attributeIdOut(tempIndex) := tempAttributeUsage.attribute_id;
1865 tempIndex := tempIndex + 1;
1866 end loop;
1867 exception
1868 when others then
1869 rollback;
1870 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1871 routineNameIn => 'getApplicationAttributes',
1872 exceptionNumberIn => sqlcode,
1873 exceptionStringIn => '(applicationID ' ||
1874 applicationIdIn||
1875 ') ' ||
1876 sqlerrm);
1877 raise;
1878 end getApplicationAttributes;
1879 procedure getApplicationAttributes2(applicationIdIn in integer,
1880 itemClassIdIn in integer,
1881 attributeIdOut out nocopy ame_util.stringList,
1882 attributeNameOut out nocopy ame_util.stringList) as
1883 cursor attributeCursor(applicationIdIn in integer) is
1884 select
1885 ame_attributes.attribute_id id,
1886 ame_attributes.name name
1887 from
1888 ame_attributes,
1889 ame_attribute_usages
1890 where
1891 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1892 ame_attributes.item_class_id = itemClassIdIn and
1893 ame_attribute_usages.application_id = applicationIdIn and
1894 ame_attributes.attribute_id not in (select attribute_id
1895 from ame_mandatory_attributes
1896 where action_type_id = ame_util.mandAttActionTypeId and
1897 sysdate between start_date and
1898 nvl(end_date - ame_util.oneSecond, sysdate)) and
1899 sysdate between ame_attributes.start_date and
1900 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1901 sysdate between ame_attribute_usages.start_date and
1902 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1903 order by name;
1904 tempIndex integer;
1905 begin
1906 tempIndex := 1;
1907 for tempAttribute in attributeCursor(applicationIdIn => applicationIdIn) loop
1908 /* The explicit conversion below lets nocopy work. */
1909 attributeIdOut(tempIndex) := to_char(tempAttribute.id);
1910 attributeNameOut(tempIndex) := tempAttribute.name;
1911 tempIndex := tempIndex + 1;
1912 end loop;
1913 exception
1914 when others then
1915 rollback;
1916 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
1917 routineNameIn => 'getApplicationAttributes2',
1918 exceptionNumberIn => sqlcode,
1919 exceptionStringIn => '(application ID ' ||
1920 applicationIdIn||
1921 ') ' ||
1922 sqlerrm);
1923 raise;
1924 end getApplicationAttributes2;
1925 /*
1926 AME_STRIPING
1927 procedure getApplicationAttributes3(applicationIdIn in integer,
1928 attributeIdsOut out nocopy ame_util.stringList,
1929 attributeNamesOut out nocopy ame_util.stringList) as
1930 cursor applicationAttributeCursor(applicationIdIn in integer) is
1931 select
1932 ame_attributes.attribute_id attribute_id,
1933 ame_attributes.name name
1934 from
1935 ame_attributes,
1936 ame_attribute_usages
1937 where
1938 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
1939 ame_attribute_usages.application_id = applicationIdIn and
1940 ame_attributes.attribute_type = ame_util.stringAttributeType and
1941 sysdate between ame_attributes.start_date and
1942 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
1943 sysdate between ame_attribute_usages.start_date and
1944 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
1945 order by name;
1946 attributeIdList ame_util.idList;
1947 tempCount integer;
1948 tempIndex integer;
1949 begin
1950 begin
1951 select to_number(value_1),
1952 to_number(value_2),
1953 to_number(value_3),
1954 to_number(value_4),
1955 to_number(value_5)
1956 into
1957 attributeIdList(1),
1958 attributeIdList(2),
1959 attributeIdList(3),
1960 attributeIdList(4),
1961 attributeIdList(5)
1962 from ame_stripe_sets
1963 where
1964 application_id = applicationIdIn and
1965 stripe_set_id = 0 and
1966 sysdate between start_date and
1967 nvl(end_date - ame_util.oneSecond, sysdate) ;
1968 exception
1969 when no_data_found then */ /* striping is not on, user needs to select
1970 from entire string attribute list */
1971 /*
1972 tempIndex := 1;
1973 for applicationAttributeRec in applicationAttributeCursor(applicationIdIn => applicationIdIn) loop
1974 attributeIdsOut(tempIndex) := to_char(applicationAttributeRec.attribute_id);
1975 attributeNamesOut(tempIndex) := applicationAttributeRec.name;
1976 tempIndex := tempIndex + 1;
1977 end loop;
1978 return;
1979 end;
1980 if(attributeIdList(5) is not null)then
1981 tempCount := 5;
1982 elsif(attributeIdList(4) is not null)then
1983 tempCount := 4;
1984 elsif(attributeIdList(3) is not null)then
1985 tempCount := 3;
1986 elsif(attributeIdList(2) is not null)then
1987 tempCount := 2;
1988 else
1989 tempCount := 1;
1990 end if;
1991 tempIndex := 1;
1992 for applicationAttributeRec in applicationAttributeCursor(applicationIdIn => applicationIdIn) loop
1993 for i in 1..tempCount loop
1994 if(applicationAttributeRec.attribute_id = attributeIdList(i)) then
1995 exit;
1996 elsif(applicationAttributeRec.attribute_id <> attributeIdList(i) and
1997 i = tempCount) then
1998 attributeIdsOut(tempIndex) := to_char(applicationAttributeRec.attribute_id);
1999 attributeNamesOut(tempIndex) := applicationAttributeRec.name;
2000 tempIndex := tempIndex + 1;
2001 end if;
2002 end loop;
2003 end loop;
2004 exception
2005 when others then
2006 rollback;
2007 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2008 routineNameIn => 'getApplicationAttributes3',
2009 exceptionNumberIn => sqlcode,
2010 exceptionStringIn => '(application ID ' ||
2011 applicationIdIn||
2012 ') ' ||
2013 sqlerrm);
2014 attributeIdsOut := ame_util.emptyStringList;
2015 attributeNamesOut := ame_util.emptyStringList;
2016 raise;
2017 end getApplicationAttributes3;
2018 */
2019 procedure getAttributes(applicationIdIn in integer,
2020 ruleTypeIn in integer,
2021 lineItemIn in varchar2 default ame_util.booleanFalse,
2022 attributeIdOut out nocopy ame_util.stringList,
2023 attributeNameOut out nocopy ame_util.stringList) as
2024 cursor attributeCursor(applicationIdIn in integer,
2025 ruleTypeIn in integer,
2026 lineItemIn in varchar2) is
2027 /* the distinct below is necessary to select a distinct list of attribute
2028 attribute names that are used within a condition */
2029 select distinct
2030 ame_attributes.attribute_id id,
2031 ame_attributes.name name
2032 from
2033 ame_attributes,
2034 ame_attribute_usages,
2035 ame_conditions
2036 where
2037 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2038 ame_conditions.attribute_id = ame_attributes.attribute_id and
2039 ame_attribute_usages.application_id = applicationIdIn and
2040 nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2041 ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2042 sysdate between ame_attributes.start_date and
2043 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2044 sysdate between ame_attribute_usages.start_date and
2045 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2046 sysdate between ame_conditions.start_date and
2047 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2048 order by name;
2049 tempIndex integer;
2050 begin
2051 tempIndex := 1;
2052 for tempAttribute in attributeCursor(applicationIdIn => applicationIdIn,
2053 ruleTypeIn => ruleTypeIn,
2054 lineItemIn => lineItemIn) loop
2055 /* The explicit conversion below lets nocopy work. */
2056 attributeIdOut(tempIndex) := to_char(tempAttribute.id);
2057 attributeNameOut(tempIndex) := tempAttribute.name;
2058 tempIndex := tempIndex + 1;
2059 end loop;
2060 exception
2061 when others then
2062 rollback;
2063 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2064 routineNameIn => 'getAttributes',
2065 exceptionNumberIn => sqlcode,
2066 exceptionStringIn => '(application ID ' ||
2067 applicationIdIn||
2068 ') ' ||
2069 sqlerrm);
2070 raise;
2071 end getAttributes;
2072 procedure getAttributes2(applicationIdIn in integer,
2073 itemClassIdIn in integer,
2074 ruleTypeIn in integer,
2075 lineItemIn in varchar2 default ame_util.booleanFalse,
2076 attributeIdOut out nocopy ame_util.stringList,
2077 attributeNameOut out nocopy ame_util.stringList) as
2078 cursor attributeCursor(applicationIdIn in integer,
2079 itemClassIdIn in integer,
2080 ruleTypeIn in integer,
2081 lineItemIn in varchar2) is
2082 /* the distinct below is necessary to select a distinct list of attribute
2083 attribute names that are used within a condition */
2084 select distinct
2085 ame_attributes.attribute_id id,
2086 ame_attributes.name name
2087 from
2088 ame_attributes,
2089 ame_attribute_usages,
2090 ame_conditions
2091 where
2092 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2093 ame_conditions.attribute_id = ame_attributes.attribute_id and
2094 ame_attribute_usages.application_id = applicationIdIn and
2095 ame_attributes.item_class_id = itemClassIdIn and
2096 nvl(ame_attributes.line_item, ame_util.booleanFalse) = lineItemIn and
2097 ame_conditions.condition_type = decode(ruleTypeIn, 1, ame_util.ordinaryConditionType, 2, ame_util.exceptionConditionType) and
2098 sysdate between ame_attributes.start_date and
2099 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2100 sysdate between ame_attribute_usages.start_date and
2101 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2102 sysdate between ame_conditions.start_date and
2103 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2104 order by name;
2105 tempIndex integer;
2106 begin
2107 tempIndex := 1;
2108 for tempAttribute in attributeCursor(applicationIdIn => applicationIdIn,
2109 itemClassIdIn => itemClassIdIn,
2110 ruleTypeIn => ruleTypeIn,
2111 lineItemIn => lineItemIn) loop
2112 /* The explicit conversion below lets nocopy work. */
2113 attributeIdOut(tempIndex) := to_char(tempAttribute.id);
2114 attributeNameOut(tempIndex) := tempAttribute.name;
2115 tempIndex := tempIndex + 1;
2116 end loop;
2117 exception
2118 when others then
2119 rollback;
2120 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2121 routineNameIn => 'getAttributes2',
2122 exceptionNumberIn => sqlcode,
2123 exceptionStringIn => '(application ID ' ||
2124 applicationIdIn||
2125 ') ' ||
2126 sqlerrm);
2127 raise;
2128 end getAttributes2;
2129 procedure getAttributes3(applicationIdIn in integer,
2130 ruleIdIn in integer,
2131 itemClassIdIn in integer,
2132 conditionTypeIn in varchar2,
2133 ruleTypeIn in integer,
2134 attributeIdOut out nocopy ame_util.stringList,
2135 attributeNameOut out nocopy ame_util.stringList) as
2136 cursor attributeCursor(applicationIdIn in integer,
2137 itemClassIdIn in integer,
2138 conditionTypeIn in varchar2,
2139 ruleTypeIn in integer) is
2140 /* the distinct below is necessary to select a distinct list of attribute
2141 attribute names that are used within a condition */
2142 select distinct
2143 ame_attributes.attribute_id id,
2144 ame_attributes.name name
2145 from
2146 ame_attributes,
2147 ame_attribute_usages,
2148 ame_conditions
2149 where
2150 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2151 ame_conditions.attribute_id = ame_attributes.attribute_id and
2152 ame_attribute_usages.application_id = applicationIdIn and
2153 ame_attributes.item_class_id = itemClassIdIn and
2154 ame_conditions.condition_type = conditionTypeIn and
2155 sysdate between ame_attributes.start_date and
2156 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2157 sysdate between ame_attribute_usages.start_date and
2158 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2159 sysdate between ame_conditions.start_date and
2160 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate)
2161 order by name;
2162 attributeConditionCount integer;
2163 attributeConditionsInUseCount integer;
2164 tempIndex integer;
2165 begin
2166 tempIndex := 1;
2167 for tempAttribute in attributeCursor(applicationIdIn => applicationIdIn,
2168 itemClassIdIn => itemClassIdIn,
2169 conditionTypeIn => conditionTypeIn,
2170 ruleTypeIn => ruleTypeIn) loop
2171 /* Verify that at least one condition is not in use for the rule. */
2172 attributeConditionCount :=
2173 getAttributeConditionCnt(attributeIdIn => tempAttribute.id,
2174 conditionTypeIn => conditionTypeIn);
2175 attributeConditionsInUseCount :=
2176 getAttributeConditionInUseCnt(ruleIdIn => ruleIdIn,
2177 conditionTypeIn => conditionTypeIn,
2178 attributeIdIn => tempAttribute.id);
2179 if(attributeConditionCount > attributeConditionsInUseCount) then
2180 /* The explicit conversion below lets nocopy work. */
2181 attributeIdOut(tempIndex) := to_char(tempAttribute.id);
2182 attributeNameOut(tempIndex) := tempAttribute.name;
2183 tempIndex := tempIndex + 1;
2184 end if;
2185 end loop;
2186 exception
2187 when others then
2188 rollback;
2189 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2190 routineNameIn => 'getAttributes3',
2191 exceptionNumberIn => sqlcode,
2192 exceptionStringIn => '(application ID ' ||
2193 applicationIdIn||
2194 ') ' ||
2195 sqlerrm);
2196 raise;
2197 end getAttributes3;
2198 procedure getAttributeConditions(attributeIdIn in integer,
2199 conditionIdListOut out nocopy ame_util.idList) as
2200 cursor getConditionsCursor(attributeIdIn in integer) is
2201 select condition_id
2202 from ame_conditions
2203 where
2204 attribute_id = attributeIdIn and
2205 sysdate between start_date and
2206 nvl(end_date - ame_util.oneSecond, sysdate);
2207 tempIndex integer;
2208 begin
2209 tempIndex := 1;
2210 for getConditionsRec in getConditionsCursor(attributeIdIn => attributeIdIn) loop
2211 /* The explicit conversion below lets nocopy work. */
2212 conditionIdListOut(tempIndex) := getConditionsRec.condition_id;
2213 tempIndex := tempIndex + 1;
2214 end loop;
2215 exception
2216 when others then
2217 rollback;
2218 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2219 routineNameIn => 'getAttributeConditions',
2220 exceptionNumberIn => sqlcode,
2221 exceptionStringIn => sqlerrm);
2222 raise;
2223 end getAttributeConditions;
2224 procedure getAvailReqAttributes(actionTypeIdIn in integer,
2225 attributeIdOut out nocopy ame_util.stringList,
2226 attributeNameOut out nocopy ame_util.stringList) as
2227 cursor attributeCursor(actionTypeIdIn in integer) is
2228 select
2229 ame_attributes.attribute_id,
2230 ame_attributes.name
2231 from
2232 ame_attributes
2233 where
2234 ame_attributes.attribute_id not in
2235 (select attribute_id from ame_mandatory_attributes
2236 where
2237 (action_type_id = actionTypeIdIn or
2238 action_type_id = ame_util.mandAttActionTypeId) and
2239 sysdate between ame_mandatory_attributes.start_date and
2240 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2241 sysdate between ame_attributes.start_date and
2242 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
2243 order by name;
2244 tempIndex integer;
2245 begin
2246 tempIndex := 1;
2247 for tempAttribute in attributeCursor(actionTypeIdIn => actionTypeIdIn) loop
2248 /* The explicit conversion below lets nocopy work. */
2249 attributeIdOut(tempIndex) := to_char(tempAttribute.attribute_id);
2250 attributeNameOut(tempIndex) := tempAttribute.name;
2251 tempIndex := tempIndex + 1;
2252 end loop;
2253 exception
2254 when others then
2255 rollback;
2256 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2257 routineNameIn => 'getAvailReqAttributes',
2258 exceptionNumberIn => sqlcode,
2259 exceptionStringIn => '(action type ID ' ||
2260 actionTypeIdIn||
2261 ') ' ||
2262 sqlerrm);
2263 raise;
2264 end getAvailReqAttributes;
2265 procedure getHeaderICAttributes(applicationIdIn in integer,
2266 attributeIdsOut out nocopy ame_util.stringList,
2267 attributeNamesOut out nocopy ame_util.stringList) as
2268 cursor getHeaderICAttributesCursor(applicationIdIn in integer,
2269 headerItemClassIdIn in integer) is
2270 select distinct(ame_attributes.attribute_id),
2271 ame_attributes.name
2272 from
2273 ame_attributes,
2274 ame_attribute_usages
2275 where
2276 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2277 ame_attributes.item_class_id = headerItemClassIdIn and
2278 ame_attribute_usages.application_id = applicationIdIn and
2279 ame_attributes.attribute_id not in
2280 (select attribute_id from ame_mandatory_attributes
2281 where
2282 action_type_id = -1 and
2283 sysdate between ame_mandatory_attributes.start_date and
2284 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)) and
2285 sysdate between ame_attributes.start_date and
2286 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2287 sysdate between ame_attribute_usages.start_date and
2288 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2289 order by name;
2290 headerItemClassId integer;
2291 tempIndex integer;
2292 begin
2293 headerItemClassId :=
2294 ame_admin_pkg.getItemClassIdByName(itemClassNameIn =>
2295 ame_util.headerItemClassName);
2296 open getHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2297 headerItemClassIdIn => headerItemClassId);
2298 fetch getHeaderICAttributesCursor bulk collect
2299 into attributeIdsOut,
2300 attributeNamesOut;
2301 close getHeaderICAttributesCursor;
2302 exception
2303 when others then
2304 rollback;
2305 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2306 routineNameIn => 'getHeaderICAttributes',
2307 exceptionNumberIn => sqlcode,
2308 exceptionStringIn => '(application ID ' ||
2309 applicationIdIn||
2310 ') ' ||
2311 sqlerrm);
2312 raise;
2313 end getHeaderICAttributes;
2314 procedure getMandatoryAttributes(attributeIdOut out nocopy ame_util.stringList,
2315 attributeNameOut out nocopy ame_util.stringList,
2316 attributeTypeOut out nocopy ame_util.stringList,
2317 attributeStartDateOut out nocopy ame_util.stringList) as
2318 cursor attributeCursor is
2319 select
2320 ame_attributes.attribute_id,
2321 ame_attributes.attribute_type,
2322 ame_attributes.name,
2323 ame_attributes.start_date
2324 from
2325 ame_attributes,
2326 ame_mandatory_attributes
2327 where
2328 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2329 ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2330 sysdate between ame_attributes.start_date and
2331 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2332 sysdate between ame_mandatory_attributes.start_date and
2333 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2334 order by name;
2335 tempIndex integer;
2336 begin
2337 tempIndex := 1;
2338 for tempAttributeUsage in attributeCursor loop
2339 /* The explicit conversion below lets nocopy work. */
2340 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2341 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2342 attributeTypeOut(tempIndex) := tempAttributeUsage.attribute_type;
2343 attributeStartDateOut(tempIndex) := tempAttributeUsage.start_date;
2344 tempIndex := tempIndex + 1;
2345 end loop;
2346 exception
2347 when others then
2348 rollback;
2349 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2350 routineNameIn => 'getMandatoryAttributes',
2351 exceptionNumberIn => sqlcode,
2352 exceptionStringIn => sqlerrm);
2353 raise;
2354 end getMandatoryAttributes;
2355 procedure getMandatoryAttributes2(applicationIdIn in integer,
2356 attributeIdOut out nocopy ame_util.stringList,
2357 attributeNameOut out nocopy ame_util.stringList,
2358 attributeTypeOut out nocopy ame_util.stringList) as
2359 cursor attributeCursor(applicationIdIn in integer) is
2360 select
2361 ame_attributes.attribute_id,
2362 ame_attributes.attribute_type,
2363 ame_attributes.name
2364 from
2365 ame_attributes,
2366 ame_attribute_usages,
2367 ame_mandatory_attributes
2368 where
2369 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2370 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2371 ame_attribute_usages.application_id = applicationIdIn and
2372 ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2373 sysdate between ame_attributes.start_date and
2374 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2375 sysdate between ame_attribute_usages.start_date and
2376 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2377 sysdate between ame_mandatory_attributes.start_date and
2378 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2379 order by name;
2380 tempIndex integer;
2381 begin
2382 tempIndex := 1;
2383 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn) loop
2384 /* The explicit conversion below lets nocopy work. */
2385 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2386 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2387 attributeTypeOut(tempIndex) := tempAttributeUsage.attribute_type;
2388 tempIndex := tempIndex + 1;
2389 end loop;
2390 exception
2391 when others then
2392 rollback;
2393 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2394 routineNameIn => 'getMandatoryAttributes2',
2395 exceptionNumberIn => sqlcode,
2396 exceptionStringIn => sqlerrm);
2397 raise;
2398 end getMandatoryAttributes2;
2399 procedure getMandatoryAttributes3(attributeIdOut out nocopy ame_util.stringList,
2400 attributeNameOut out nocopy ame_util.stringList,
2401 attributeTypeOut out nocopy ame_util.stringList,
2402 attributeStartDateOut out nocopy ame_util.stringList) as
2403 cursor attributeCursor is
2404 select
2405 ame_attributes.attribute_id,
2406 ame_attributes.attribute_type,
2407 ame_attributes.name,
2408 ame_attributes.start_date
2409 from
2410 ame_attributes,
2411 ame_mandatory_attributes
2412 where
2413 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2414 ame_mandatory_attributes.action_type_id = ame_util.mandAttActionTypeId and
2415 ame_attributes.name not in (ame_util.evalPrioritiesPerItemAttribute,
2416 ame_util.restrictiveItemEvalAttribute) and
2417 sysdate between ame_attributes.start_date and
2418 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2419 sysdate between ame_mandatory_attributes.start_date and
2420 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2421 order by name;
2422 tempIndex integer;
2423 begin
2424 tempIndex := 1;
2425 for tempAttributeUsage in attributeCursor loop
2426 /* The explicit conversion below lets nocopy work. */
2427 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2428 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2429 attributeTypeOut(tempIndex) := tempAttributeUsage.attribute_type;
2430 attributeStartDateOut(tempIndex) := tempAttributeUsage.start_date;
2431 tempIndex := tempIndex + 1;
2432 end loop;
2433 exception
2434 when others then
2435 rollback;
2436 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2437 routineNameIn => 'getMandatoryAttributes3',
2438 exceptionNumberIn => sqlcode,
2439 exceptionStringIn => sqlerrm);
2440 raise;
2441 end getMandatoryAttributes3;
2442 procedure getNonHeaderICAttributes(applicationIdIn in integer,
2443 itemClassIdIn in integer,
2444 attributeIdsOut out nocopy ame_util.stringList,
2445 attributeNamesOut out nocopy ame_util.stringList) as
2446 cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2447 itemClassIdIn in integer) is
2448 select
2449 ame_attributes.attribute_id,
2450 ame_attributes.name
2451 from
2452 ame_attributes,
2453 ame_attribute_usages
2454 where
2455 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2456 ame_attributes.item_class_id = itemClassIdIn and
2457 ame_attribute_usages.application_id = applicationIdIn and
2458 sysdate between ame_attributes.start_date and
2459 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2460 sysdate between ame_attribute_usages.start_date and
2461 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2462 order by name;
2463 tempIndex integer;
2464 begin
2465 open getNonHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2466 itemClassIdIn => itemClassIdIn);
2467 fetch getNonHeaderICAttributesCursor bulk collect
2468 into attributeIdsOut,
2469 attributeNamesOut;
2470 close getNonHeaderICAttributesCursor;
2471 exception
2472 when others then
2473 rollback;
2474 attributeIdsOut := ame_util.emptyStringList;
2475 attributeNamesOut := ame_util.emptyStringList;
2476 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2477 routineNameIn => 'getNonHeaderICAttributes',
2478 exceptionNumberIn => sqlcode,
2479 exceptionStringIn => '(application ID ' ||
2480 applicationIdIn||
2481 ') ' ||
2482 sqlerrm);
2483 raise;
2484 end getNonHeaderICAttributes;
2485 procedure getNonHeaderICAttributes2(applicationIdIn in integer,
2486 itemClassIdIn in integer,
2487 attributeIdsOut out nocopy ame_util.idList,
2488 attributeNamesOut out nocopy ame_util.stringList) as
2489 cursor getNonHeaderICAttributesCursor(applicationIdIn in integer,
2490 itemClassIdIn in integer) is
2491 select
2492 ame_attributes.attribute_id,
2493 ame_attributes.name
2494 from
2495 ame_attributes,
2496 ame_attribute_usages
2497 where
2498 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2499 ame_attributes.item_class_id = itemClassIdIn and
2500 ame_attribute_usages.application_id = applicationIdIn and
2501 sysdate between ame_attributes.start_date and
2502 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2503 sysdate between ame_attribute_usages.start_date and
2504 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2505 order by name;
2506 tempIndex integer;
2507 begin
2508 open getNonHeaderICAttributesCursor(applicationIdIn => applicationIdIn,
2509 itemClassIdIn => itemClassIdIn);
2510 fetch getNonHeaderICAttributesCursor bulk collect
2511 into attributeIdsOut,
2512 attributeNamesOut;
2513 close getNonHeaderICAttributesCursor;
2514 exception
2515 when others then
2516 rollback;
2517 attributeIdsOut := ame_util.emptyIdList;
2518 attributeNamesOut := ame_util.emptyStringList;
2519 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2520 routineNameIn => 'getNonHeaderICAttributes2',
2521 exceptionNumberIn => sqlcode,
2522 exceptionStringIn => '(application ID ' ||
2523 applicationIdIn||
2524 ') ' ||
2525 sqlerrm);
2526 raise;
2527 end getNonHeaderICAttributes2;
2528 procedure getRequiredAttributes(actionTypeIdIn in integer,
2529 attributeIdOut out nocopy ame_util.stringList,
2530 attributeNameOut out nocopy ame_util.stringList) as
2531 cursor attributeCursor(actionTypeIdIn in integer) is
2532 select ame_attributes.attribute_id,
2533 ame_attributes.name
2534 from ame_attributes,
2535 ame_mandatory_attributes
2536 where
2537 ame_attributes.attribute_id = ame_mandatory_attributes.attribute_id and
2538 ame_mandatory_attributes.action_type_id = actionTypeIdIn and
2539 sysdate between ame_attributes.start_date and
2540 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2541 sysdate between ame_mandatory_attributes.start_date and
2542 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate)
2543 order by name;
2544 tempIndex integer;
2545 begin
2546 tempIndex := 1;
2547 for tempAttribute in attributeCursor(actionTypeIdIn => actionTypeIdIn) loop
2548 /* The explicit conversion below lets nocopy work. */
2549 attributeIdOut(tempIndex) := to_char(tempAttribute.attribute_id);
2550 attributeNameOut(tempIndex) := tempAttribute.name;
2551 tempIndex := tempIndex + 1;
2552 end loop;
2553 exception
2554 when others then
2555 rollback;
2556 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2557 routineNameIn => 'getRequiredAttributes',
2558 exceptionNumberIn => sqlcode,
2559 exceptionStringIn => '(action type ID ' ||
2560 actionTypeIdIn||
2561 ') ' ||
2562 sqlerrm);
2563 raise;
2564 end getRequiredAttributes;
2565 /*
2566 AME_STRIPING
2567 procedure getLineItemAttributes(applicationIdIn in integer,
2568 isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2569 attributeIdOut out nocopy ame_util.stringList,
2570 attributeNameOut out nocopy ame_util.stringList) as
2571 cursor attributeCursor(applicationIdIn in integer,
2572 isStripingAttributeIn in varchar2) is
2573 select
2574 ame_attributes.attribute_id,
2575 ame_attributes.name
2576 from
2577 ame_attributes,
2578 ame_attribute_usages
2579 where
2580 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2581 ame_attribute_usages.application_id = applicationIdIn and
2582 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2583 nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2584 (ame_attributes.start_date <= sysdate and
2585 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
2586 (ame_attribute_usages.start_date <= sysdate and
2587 (ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date))
2588 order by name;
2589 tempIndex integer;
2590 begin
2591 tempIndex := 1;
2592 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
2593 isStripingAttributeIn => isStripingAttributeIn) loop
2594 */
2595 /* The explicit conversion below lets nocopy work. */
2596 /*
2597 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2598 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2599 tempIndex := tempIndex + 1;
2600 end loop;
2601 exception
2602 when others then
2603 rollback;
2604 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2605 routineNameIn => 'getLineItemAttributes',
2606 exceptionNumberIn => sqlcode,
2607 exceptionStringIn => '(application ID ' ||
2608 applicationIdIn||
2609 ') ' ||
2610 sqlerrm);
2611 raise;
2612 end getLineItemAttributes;
2613 */
2614 procedure getLineItemAttributes(applicationIdIn in integer,
2615 attributeIdOut out nocopy ame_util.stringList,
2616 attributeNameOut out nocopy ame_util.stringList) as
2617 cursor attributeCursor(applicationIdIn in integer) is
2618 select
2619 ame_attributes.attribute_id,
2620 ame_attributes.name
2621 from
2622 ame_attributes,
2623 ame_attribute_usages
2624 where
2625 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2626 ame_attribute_usages.application_id = applicationIdIn and
2627 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2628 sysdate between ame_attributes.start_date and
2629 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2630 sysdate between ame_attribute_usages.start_date and
2631 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2632 order by name;
2633 tempIndex integer;
2634 begin
2635 tempIndex := 1;
2636 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn) loop
2637 /* The explicit conversion below lets nocopy work. */
2638 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2639 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2640 tempIndex := tempIndex + 1;
2641 end loop;
2642 exception
2643 when others then
2644 rollback;
2645 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2646 routineNameIn => 'getLineItemAttributes',
2647 exceptionNumberIn => sqlcode,
2648 exceptionStringIn => '(application ID ' ||
2649 applicationIdIn||
2650 ') ' ||
2651 sqlerrm);
2652 raise;
2653 end getLineItemAttributes;
2654 procedure getLineItemAttributes2(applicationIdIn in integer,
2655 attributeIdOut out nocopy ame_util.stringList,
2656 attributeNameOut out nocopy ame_util.stringList) as
2657 cursor attributeCursor(applicationIdIn in integer) is
2658 select
2659 ame_attributes.attribute_id,
2660 ame_attributes.name
2661 from
2662 ame_attributes,
2663 ame_attribute_usages
2664 where
2665 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2666 ame_attribute_usages.application_id = applicationIdIn and
2667 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanTrue and
2668 sysdate between ame_attributes.start_date and
2669 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2670 sysdate between ame_attribute_usages.start_date and
2671 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2672 order by name;
2673 tempIndex integer;
2674 begin
2675 tempIndex := 1;
2676 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn) loop
2677 /* The explicit conversion below lets nocopy work. */
2678 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2679 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2680 tempIndex := tempIndex + 1;
2681 end loop;
2682 exception
2683 when others then
2684 rollback;
2685 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2686 routineNameIn => 'getLineItemAttributes2',
2687 exceptionNumberIn => sqlcode,
2688 exceptionStringIn => '(application ID ' ||
2689 applicationIdIn||
2690 ') ' ||
2691 sqlerrm);
2692 raise;
2693 end getLineItemAttributes2;
2694 /*
2695 AME_STRIPING
2696 procedure getNonMandatoryAttributes(applicationIdIn in integer,
2697 isStripingAttributeIn in varchar2 default ame_util.booleanFalse,
2698 attributeIdOut out nocopy ame_util.stringList,
2699 attributeNameOut out nocopy ame_util.stringList) as
2700 cursor attributeCursor(applicationIdIn in integer,
2701 isStripingAttributeIn in varchar2) is
2702 select
2703 ame_attributes.attribute_id,
2704 ame_attributes.name
2705 from
2706 ame_attributes,
2707 ame_attribute_usages
2708 where
2709 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2710 ame_attribute_usages.application_id = applicationIdIn and
2711 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2712 nvl(ame_attribute_usages.is_striping_attribute, ame_util.booleanFalse) = isStripingAttributeIn and
2713 ame_attributes.attribute_id not in
2714 (select attribute_id from ame_mandatory_attributes
2715 where action_type_id = -1 and
2716 (ame_mandatory_attributes.start_date <= sysdate and
2717 (ame_mandatory_attributes.end_date is null or sysdate < ame_mandatory_attributes.end_date))) and
2718 (ame_attributes.start_date <= sysdate and
2719 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date)) and
2720 (ame_attribute_usages.start_date <= sysdate and
2721 (ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date))
2722 order by name;
2723 tempIndex integer;
2724 begin
2725 tempIndex := 1;
2726 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
2727 isStripingAttributeIn => isStripingAttributeIn) loop
2728 */
2729 /* The explicit conversion below lets nocopy work. */
2730 /*
2731 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2732 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2733 tempIndex := tempIndex + 1;
2734 end loop;
2735 exception
2736 when others then
2737 rollback;
2738 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2739 routineNameIn => 'getNonMandatoryAttributes',
2740 exceptionNumberIn => sqlcode,
2741 exceptionStringIn => '(application ID ' ||
2742 applicationIdIn||
2743 ') ' ||
2744 sqlerrm);
2745 raise;
2746 end getNonMandatoryAttributes;
2747 */
2748 procedure getNonMandatoryAttributes(applicationIdIn in integer,
2749 attributeIdOut out nocopy ame_util.stringList,
2750 attributeNameOut out nocopy ame_util.stringList) as
2751 cursor attributeCursor(applicationIdIn in integer) is
2752 select
2753 ame_attributes.attribute_id,
2754 ame_attributes.name
2755 from
2756 ame_attributes,
2757 ame_attribute_usages
2758 where
2759 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2760 ame_attribute_usages.application_id = applicationIdIn and
2761 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2762 ame_attributes.attribute_id not in
2763 (select attribute_id from ame_mandatory_attributes
2764 where action_type_id = -1 and
2765 sysdate between ame_mandatory_attributes.start_date and
2766 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2767 sysdate between ame_attributes.start_date and
2768 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2769 sysdate between ame_attribute_usages.start_date and
2770 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2771 order by name;
2772 tempIndex integer;
2773 begin
2774 tempIndex := 1;
2775 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn) loop
2776 /* The explicit conversion below lets nocopy work. */
2777 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2778 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2779 tempIndex := tempIndex + 1;
2780 end loop;
2781 exception
2782 when others then
2783 rollback;
2784 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2785 routineNameIn => 'getNonMandatoryAttributes',
2786 exceptionNumberIn => sqlcode,
2787 exceptionStringIn => '(application ID ' ||
2788 applicationIdIn||
2789 ') ' ||
2790 sqlerrm);
2791 raise;
2792 end getNonMandatoryAttributes;
2793 procedure getNonMandHeaderAttributes(applicationIdIn in integer,
2794 attributeIdOut out nocopy ame_util.stringList,
2795 attributeNameOut out nocopy ame_util.stringList) as
2796 cursor attributeCursor(applicationIdIn in integer,
2797 headerItemClassIdIn in integer) is
2798 select
2799 ame_attributes.attribute_id,
2800 ame_attributes.name
2801 from
2802 ame_attributes,
2803 ame_attribute_usages,
2804 ame_item_classes
2805 where
2806 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2807 ame_attributes.item_class_id = ame_item_classes.item_class_id and
2808 ame_item_classes.item_class_id = headerItemClassIdIn and
2809 ame_attribute_usages.application_id = applicationIdIn and
2810 nvl(ame_attributes.line_item, ame_util.booleanFalse) = ame_util.booleanFalse and
2811 ame_attributes.attribute_id not in
2812 (select attribute_id from ame_mandatory_attributes
2813 where action_type_id = -1 and
2814 sysdate between ame_mandatory_attributes.start_date and
2815 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) ) and
2816 sysdate between ame_attributes.start_date and
2817 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2818 sysdate between ame_attribute_usages.start_date and
2819 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate) and
2820 sysdate between ame_item_classes.start_date and
2821 nvl(ame_item_classes.end_date - ame_util.oneSecond, sysdate)
2822 order by ame_attributes.name;
2823 headerItemClassId integer;
2824 tempIndex integer;
2825 begin
2826 tempIndex := 1;
2827 headerItemClassId :=
2828 ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerItemClassName);
2829 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
2830 headerItemClassIdIn => headerItemClassId) loop
2831 /* The explicit conversion below lets nocopy work. */
2832 attributeIdOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
2833 attributeNameOut(tempIndex) := tempAttributeUsage.name;
2834 tempIndex := tempIndex + 1;
2835 end loop;
2836 exception
2837 when others then
2838 rollback;
2839 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2840 routineNameIn => 'getNonMandHeaderAttributes',
2841 exceptionNumberIn => sqlcode,
2842 exceptionStringIn => '(application ID ' ||
2843 applicationIdIn||
2844 ') ' ||
2845 sqlerrm);
2846 raise;
2847 end getNonMandHeaderAttributes;
2848 /*
2849 AME_STRIPING
2850 procedure getRuleStripingAttributes(applicationIdIn in integer,
2851 attributeIdsOut out nocopy ame_util.stringList) as
2852 cursor ruleStripingAttributeCursor(applicationIdIn in integer) is
2853 select
2854 ame_attributes.attribute_id attribute_id
2855 from
2856 ame_attributes,
2857 ame_attribute_usages
2858 where
2859 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2860 ame_attribute_usages.application_id = applicationIdIn and
2861 ame_attributes.attribute_type = ame_util.stringAttributeType and
2862 ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
2863 sysdate between ame_attributes.start_date and
2864 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2865 sysdate between ame_attribute_usages.start_date and
2866 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2867 order by ame_attributes.attribute_id;
2868 tempIndex integer;
2869 begin
2870 tempIndex := 1;
2871 for ruleStripingAttributeRec in ruleStripingAttributeCursor(applicationIdIn => applicationIdIn) loop
2872 attributeIdsOut(tempIndex) := to_char(ruleStripingAttributeRec.attribute_id);
2873 tempIndex := tempIndex + 1;
2874 end loop;
2875 exception
2876 when others then
2877 rollback;
2878 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2879 routineNameIn => 'getRuleStripingAttributes',
2880 exceptionNumberIn => sqlcode,
2881 exceptionStringIn => '(application ID ' ||
2882 applicationIdIn||
2883 ') ' ||
2884 sqlerrm);
2885 raise;
2886 end getRuleStripingAttributes;
2887 */
2888 procedure getExistingShareableAttNames(applicationIdIn in integer,
2889 itemClassIdIn in integer,
2890 attributeIdsOut out nocopy ame_util.stringList,
2891 attributeNamesOut out nocopy ame_util.stringList) as
2892 cursor unusedAttributeCursor(applicationIdIn in integer,
2893 itemClassIdIn in integer) is
2894 select
2895 ame_attributes.attribute_id attribute_id,
2896 ame_attributes.name name
2897 from
2898 ame_attributes
2899 where
2900 item_class_id = itemClassIdIn and
2901 (ame_attributes.start_date <= sysdate and
2902 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
2903 minus
2904 select
2905 ame_attributes.attribute_id attribute_id,
2906 ame_attributes.name name
2907 from
2908 ame_attributes,
2909 ame_attribute_usages
2910 where
2911 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2912 ame_attribute_usages.application_id = applicationIdIn and
2913 sysdate between ame_attributes.start_date and
2914 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2915 sysdate between ame_attribute_usages.start_date and
2916 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2917 order by name;
2918 cursor unusedAttributeCursor2(applicationIdIn in integer,
2919 itemClassIdIn in integer,
2920 perApproverTypeIdIn in integer,
2921 fndUserApproverTypeIdIn in integer) is
2922 select
2923 ame_attributes.attribute_id attribute_id,
2924 ame_attributes.name name
2925 from
2926 ame_attributes
2927 where
2928 item_class_id = itemClassIdIn and
2929 (ame_attributes.start_date <= sysdate and
2930 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
2931 minus
2932 select
2933 ame_attributes.attribute_id attribute_id,
2934 ame_attributes.name name
2935 from
2936 ame_attributes,
2937 ame_attribute_usages
2938 where
2939 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
2940 ame_attribute_usages.application_id = applicationIdIn and
2941 sysdate between ame_attributes.start_date and
2942 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
2943 sysdate between ame_attribute_usages.start_date and
2944 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
2945 minus
2946 select
2947 ame_attributes.attribute_id attribute_id,
2948 ame_attributes.name name
2949 from
2950 ame_attributes
2951 where
2952 approver_type_id not in (perApproverTypeIdIn, fndUserApproverTypeIdIn) and
2953 (ame_attributes.start_date <= sysdate and
2954 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date))
2955 order by name;
2956 allowAllApproverTypes ame_util.stringType;
2957 fndUserApproverTypeId integer;
2958 perApproverTypeId integer;
2959 tempIndex integer;
2960 begin
2961 perApproverTypeId :=
2962 ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.perOrigSystem);
2963 fndUserApproverTypeId :=
2964 ame_approver_type_pkg.getApproverTypeId(origSystemIn => ame_util.fndUserOrigSystem);
2965 allowAllApproverTypes :=
2966 ame_util.getConfigVar(variableNameIn => ame_util.allowAllApproverTypesConfigVar,
2967 applicationIdIn => applicationIdIn);
2968 tempIndex := 1;
2969 if(allowAllApproverTypes = ame_util.yes) then
2970 for tempAttribute in unusedAttributeCursor(applicationIdIn => applicationIdIn,
2971 itemClassIdIn => itemClassIdIn) loop
2972 /* The explicit conversion below lets nocopy work. */
2973 attributeIdsOut(tempIndex) := to_char(tempAttribute.attribute_id);
2974 attributeNamesOut(tempIndex) := tempAttribute.name;
2975 tempIndex := tempIndex + 1;
2976 end loop;
2977 else
2978 for tempAttribute in unusedAttributeCursor2(applicationIdIn => applicationIdIn,
2979 itemClassIdIn => itemClassIdIn,
2980 perApproverTypeIdIn => perApproverTypeId,
2981 fndUserApproverTypeIdIn => fndUserApproverTypeId) loop
2982 /* The explicit conversion below lets nocopy work. */
2983 attributeIdsOut(tempIndex) := to_char(tempAttribute.attribute_id);
2984 attributeNamesOut(tempIndex) := tempAttribute.name;
2985 tempIndex := tempIndex + 1;
2986 end loop;
2987 end if;
2988 exception
2989 when others then
2990 rollback;
2991 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
2992 routineNameIn => 'getExistingShareableAttNames',
2993 exceptionNumberIn => sqlcode,
2994 exceptionStringIn => '(application ID ' ||
2995 applicationIdIn||
2996 ') ' ||
2997 sqlerrm);
2998 raise;
2999 end getExistingShareableAttNames;
3000 procedure getSubordinateICAttributes(applicationIdIn in integer,
3001 itemClassIdIn in integer,
3002 attributeIdsOut out nocopy ame_util.stringList,
3003 attributeNamesOut out nocopy ame_util.stringList) as
3004 cursor attributeCursor(applicationIdIn in integer,
3005 itemClassIdIn in integer) is
3006 select
3007 ame_attributes.attribute_id,
3008 ame_attributes.name
3009 from
3010 ame_attributes,
3011 ame_attribute_usages
3012 where
3013 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
3014 ame_attribute_usages.application_id = applicationIdIn and
3015 ame_attributes.item_class_id = itemClassIdIn and
3016 sysdate between ame_attributes.start_date and
3017 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3018 sysdate between ame_attribute_usages.start_date and
3019 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3020 order by name;
3021 tempIndex integer;
3022 begin
3023 tempIndex := 1;
3024 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
3025 itemClassIdIn => itemClassIdIn) loop
3026 /* The explicit conversion below lets nocopy work. */
3027 attributeIdsOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
3028 attributeNamesOut(tempIndex) := tempAttributeUsage.name;
3029 tempIndex := tempIndex + 1;
3030 end loop;
3031 exception
3032 when others then
3033 rollback;
3034 attributeIdsOut := ame_util.emptyStringList;
3035 attributeNamesOut := ame_util.emptyStringList;
3036 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3037 routineNameIn => 'getSubordinateICAttributes',
3038 exceptionNumberIn => sqlcode,
3039 exceptionStringIn => '(application ID ' ||
3040 applicationIdIn||
3041 ') ' ||
3042 sqlerrm);
3043 raise;
3044 end getSubordinateICAttributes;
3045 procedure getSubordinateICAttributes2(applicationIdIn in integer,
3046 itemClassIdIn in integer,
3047 attributeIdsOut out nocopy ame_util.idList,
3048 attributeNamesOut out nocopy ame_util.stringList,
3049 attributeTypesOut out nocopy ame_util.stringList) as
3050 cursor attributeCursor(applicationIdIn in integer,
3051 itemClassIdIn in integer) is
3052 select
3053 ame_attributes.attribute_id,
3054 ame_attributes.name,
3055 ame_attributes.attribute_type
3056 from
3057 ame_attributes,
3058 ame_attribute_usages
3059 where
3060 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
3061 ame_attribute_usages.application_id = applicationIdIn and
3062 ame_attributes.item_class_id = itemClassIdIn and
3063 sysdate between ame_attributes.start_date and
3064 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate) and
3065 sysdate between ame_attribute_usages.start_date and
3066 nvl(ame_attribute_usages.end_date - ame_util.oneSecond, sysdate)
3067 order by name;
3068 tempIndex integer;
3069 begin
3070 tempIndex := 1;
3071 for tempAttributeUsage in attributeCursor(applicationIdIn => applicationIdIn,
3072 itemClassIdIn => itemClassIdIn) loop
3073 /* The explicit conversion below lets nocopy work. */
3074 attributeIdsOut(tempIndex) := to_char(tempAttributeUsage.attribute_id);
3075 attributeNamesOut(tempIndex) := tempAttributeUsage.name;
3076 attributeTypesOut (tempIndex) := tempAttributeUsage.attribute_type;
3077 tempIndex := tempIndex + 1;
3078 end loop;
3079 exception
3080 when others then
3081 rollback;
3082 attributeIdsOut := ame_util.emptyIdList;
3083 attributeNamesOut := ame_util.emptyStringList;
3084 attributeTypesOut := ame_util.emptyStringList;
3085 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3086 routineNameIn => 'getSubordinateICAttributes2',
3087 exceptionNumberIn => sqlcode,
3088 exceptionStringIn => '(application ID ' ||
3089 applicationIdIn||
3090 ') ' ||
3091 sqlerrm);
3092 raise;
3093 end getSubordinateICAttributes2;
3094 /*
3095 AME_STRIPING
3096 procedure newAttributeUsage(attributeIdIn in integer,
3097 applicationIdIn in integer,
3098 staticUsageIn in varchar2,
3099 queryStringIn in varchar2 default null,
3100 newStartDateIn in date default null,
3101 lineItemAttributeIn in varchar2,
3102 isStripingAttributeIn in varchar2 default null,
3103 finalizeIn in boolean default true) as
3104 */
3105 procedure newAttributeUsage(attributeIdIn in integer,
3106 applicationIdIn in integer,
3107 staticUsageIn in varchar2,
3108 updateParentObjectIn in boolean,
3109 queryStringIn in varchar2 default null,
3110 newStartDateIn in date default null,
3111 finalizeIn in boolean default false,
3112 parentVersionStartDateIn in date default null,
3113 createdByIn in integer default null) as
3114 cursor startDateCursor is
3115 select start_date
3116 from ame_attributes
3117 where
3118 attribute_id = attributeIdIn and
3119 (start_date <= sysdate and
3120 (end_date is null or sysdate < end_date))
3121 for update;
3122 approverTypeId integer;
3123 attributeDescription ame_attributes.description%type;
3124 lineItemAttribute varchar2(1);
3125 attributeId ame_attributes.attribute_id%type;
3126 attributeName ame_attributes.name%type;
3127 attributeType ame_attributes.attribute_type%type;
3128 badCurUsageException exception;
3129 badStaticDateUsageException exception;
3130 booleanException exception;
3131 charMonths ame_util.stringList;
3132 comma1Location integer;
3133 comma2Location integer;
3134 createdBy integer;
3135 currentUserId integer;
3136 dateAttribute date;
3137 dynamicUsageException exception;
3138 errorCode integer;
3139 errorMessage ame_util.longestStringType;
3140 firstReturnLocation integer;
3141 invalidBooleanValueException exception;
3142 invalidReferenceException exception;
3143 itemClassId integer;
3144 /*
3145 AME_STRIPING
3146 isBecomingStripingAttribute varchar2(1);
3147 isStripingAttributeChange varchar2(1);
3148 */
3149 lineItemIdPlaceholderPosition integer;
3150 lineItemIdPlaceholderPosition2 integer;
3151 loweredQueryString varchar2(4000);
3152 numMonths ame_util.stringList;
3153 nullQueryStringException exception;
3154 objectVersionNoDataException exception;
3155 placeholderException exception;
3156 queryString ame_attribute_usages.query_string%type;
3157 queryString1 ame_attribute_usages.query_string%type;
3158 queryStringLengthException exception;
3159 queryStringColumnException exception;
3160 startDate date;
3161 startDate2 date;
3162 stringDynamicException exception;
3163 stringStaticUsageException exception;
3164 substitutionString ame_util.stringType;
3165 tempCount integer;
3166 tempCount2 integer;
3167 tempInt integer;
3168 transactionType ame_calling_apps.application_name%type;
3169 transIdPlaceholderPosition integer;
3170 transIdPlaceholderPosition2 integer;
3171 upperLineItemIdPlaceholder varchar2(100);
3172 upperQueryString ame_attribute_usages.query_string%type;
3173 upperTransIdPlaceholder varchar2(100);
3174 usageExistsException exception;
3175 useCount integer;
3176 processingDate date;
3177 begin
3178 processingDate := sysdate;
3179 if(finalizeIn) then
3180 open startDateCursor;
3181 fetch startDateCursor into startDate;
3182 if startDateCursor%notfound then
3183 raise objectVersionNoDataException;
3184 end if;
3185 if(parentVersionStartDateIn <> startDate) then
3186 close startDateCursor;
3187 raise ame_util.objectVersionException;
3188 end if;
3189 end if;
3190 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3191 attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
3192 itemClassId := ame_attribute_pkg.getItemClassId(attributeIdIn => attributeIdIn);
3193 if(staticUsageIn = ame_util.booleanTrue) then /* static usage */
3194 queryString := ame_util.removeReturns(stringIn => queryStringIn,
3195 replaceWithSpaces => false);
3196 if(instrb(upper(queryString), upper(ame_util.transactionIdPlaceholder))) > 0 then
3197 raise placeholderException;
3198 end if;
3199 /* Format the static usage correctly. */
3200 if(attributeType = ame_util.currencyAttributeType) then
3201 queryString := inputToCanonStaticCurUsage(attributeIdIn => attributeIdIn,
3202 applicationIdIn => applicationIdIn,
3203 queryStringIn => queryString);
3204 elsif(attributeType = ame_util.numberAttributeType) then
3205 queryString := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => queryString);
3206 elsif(attributeType = ame_util.stringAttributeType) then
3207 if(instrb(queryString, '''') > 0) or length(queryString) > ame_util.stringTypeLength then
3208 raise stringStaticUsageException;
3209 end if;
3210 elsif(attributeType = ame_util.booleanAttributeType) then
3211 if(instrb(upper(queryStringIn),'TRUE') > 0) then
3212 queryString := 'true';
3213 elsif(instrb(upper(queryStringIn), 'FALSE') > 0) then
3214 queryString := 'false';
3215 else
3216 raise booleanException;
3217 end if;
3218 if(attributeName = ame_util.evalPrioritiesPerItemAttribute) then
3219 if(queryString = 'true') then
3220 attributeId := ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.restrictiveItemEvalAttribute);
3221 queryString1 := ame_attribute_pkg.getQueryString(attributeIdIn => attributeId,
3222 applicationIdIn => applicationIdIn);
3223 if(queryString1 is null or queryString1 = 'false') then
3224 raise invalidBooleanValueException;
3225 end if;
3226 end if;
3227 end if;
3228 elsif(attributeType = ame_util.dateAttributeType) then
3229 /* check to make sure the user entered the date in the correct format */
3230 begin
3231 if(queryString is not null) then
3232 numMonths(1) := '01';
3233 numMonths(2) := '02';
3234 numMonths(3) := '03';
3235 numMonths(4) := '04';
3236 numMonths(5) := '05';
3237 numMonths(6) := '06';
3238 numMonths(7) := '07';
3239 numMonths(8) := '08';
3240 numMonths(9) := '09';
3241 numMonths(10) := '10';
3242 numMonths(11) := '11';
3243 numMonths(12) := '12';
3244 charMonths(1) := 'JAN';
3245 charMonths(2) := 'FEB';
3246 charMonths(3) := 'MAR';
3247 charMonths(4) := 'APR';
3248 charMonths(5) := 'MAY';
3249 charMonths(6) := 'JUN';
3250 charMonths(7) := 'JUL';
3251 charMonths(8) := 'AUG';
3252 charMonths(9) := 'SEP';
3253 charMonths(10) := 'OCT';
3254 charMonths(11) := 'NOV';
3255 charMonths(12) := 'DEC';
3256 ame_util.substituteStrings(stringIn => queryString,
3257 targetStringsIn => charMonths,
3258 substitutionStringsIn => numMonths,
3259 stringOut => substitutionString);
3260 queryString := substitutionString;
3261 if(instrb(queryString, ':', 1, 5)) = 0 then
3262 raise badStaticDateUsageException;
3263 end if;
3264 end if;
3265 exception
3266 when others then
3267 raise badStaticDateUsageException;
3268 end;
3269 end if;
3270 else /* dynamic usage (actual query string) */
3271 queryString := queryStringIn;
3272 if(queryString is null) then
3273 raise nullQueryStringException;
3274 end if;
3275 if(instrb(queryString, ';', 1, 1) > 0) or
3276 (instrb(queryString, '--', 1, 1) > 0) or
3277 (instrb(queryString, '/*', 1, 1) > 0) or
3278 (instrb(queryString, '*/', 1, 1) > 0) then
3279 raise stringDynamicException;
3280 end if;
3281 tempInt := 1;
3282 upperQueryString := upper(queryStringIn);
3283 upperTransIdPlaceholder := upper(ame_util.transactionIdPlaceholder);
3284 loop
3285 transIdPlaceholderPosition :=
3286 instrb(upperQueryString, upperTransIdPlaceholder, 1, tempInt);
3287 if(transIdPlaceholderPosition = 0) then
3288 exit;
3289 end if;
3290 transIdPlaceholderPosition2 :=
3291 instrb(queryStringIn, ame_util.transactionIdPlaceholder, 1, tempInt);
3292 if(transIdPlaceholderPosition <> transIdPlaceholderPosition2) then
3293 raise dynamicUsageException;
3294 end if;
3295 tempInt := tempInt + 1;
3296 end loop;
3297 if(attributeType = ame_util.currencyAttributeType) then
3298 comma1Location := instrb(queryString, ',', -1, 2);
3299 comma2Location := instrb(queryString, ',', -1, 1);
3300 if(comma1Location = 0 or
3301 comma2Location = 0 or
3302 comma1Location < 2 or
3303 comma2Location < 4) then
3304 /*
3305 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3306 */
3307 raise badCurUsageException;
3308 end if;
3309 end if;
3310 end if;
3311 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attribute_usages',
3312 columnNameIn => 'query_string',
3313 argumentIn => queryString)) then
3314 raise queryStringLengthException;
3315 end if;
3316 select count(*)
3317 into tempCount
3318 from ame_attribute_usages
3319 where
3320 attribute_id = attributeIdIn and
3321 application_id = applicationIdIn and
3322 (start_date <= sysdate and
3323 (end_date is null or sysdate < end_date));
3324 if(tempCount > 0) then
3325 raise usageExistsException;
3326 end if;
3327 /* Need to check if the striping attribute has been set to null.
3328 If so, need to let calculateUseCount know because it checks
3329 to see if it is a striping attribute before the ame_attribute_usages
3330 gets updated (which is after the call to calculateUseCount below).
3331 calculateUse check to see if striping is on. */
3332 /*
3333 AME_STRIPING
3334 if(isStripingAttribute(applicationIdIn => applicationIdIn,
3335 attributeIdIn => attributeIdIn) and
3336 isStripingAttributeIn is null) then */ /* existing striping attribute
3337 has been set to null */
3338 /*
3339 isStripingAttributeChange := ame_util.booleanTrue;
3340 isBecomingStripingAttribute := ame_util.booleanFalse;
3341 elsif(not isStripingAttribute(applicationIdIn => applicationIdIn,
3342 attributeIdIn => attributeIdIn) and
3343 isStripingAttributeIn is not null) then */ /* attribute is becoming a
3344 striping attribute */
3345 /*
3346 isStripingAttributeChange := ame_util.booleanFalse;
3347 isBecomingStripingAttribute := ame_util.booleanTrue;
3348 else
3349 isStripingAttributeChange := ame_util.booleanFalse;
3350 isBecomingStripingAttribute := ame_util.booleanFalse;
3351 end if;
3352 useCount := calculateUseCount(attributeIdIn => attributeIdIn,
3353 applicationIdIn => applicationIdIn,
3354 isStripingAttributeChangeIn => isStripingAttributeChange,
3355 isBecomingStripingAttributeIn => isBecomingStripingAttribute);
3356 select count(*)
3357 into tempCount2
3358 from ame_attribute_usages
3359 where
3360 attribute_id = attributeIdIn and
3361 applicationI_id = applicationIdIn and
3362 created_by = ame_util.seededDataCreatedById;
3363 if(tempCount2 > 0) then
3364 createdBy := ame_util.seededDataCreatedById;
3365 else
3366 createdBy := currentUserId;
3367 end if;
3368 insert into ame_attribute_usages(attribute_id,
3369 application_id,
3370 query_string,
3371 use_count,
3372 is_static,
3373 created_by,
3374 creation_date,
3375 last_updated_by,
3376 last_update_date,
3377 last_update_login,
3378 start_date,
3379 end_date,
3380 user_editable,
3381 is_striping_attribute)
3382 values(attributeIdIn,
3383 applicationIdIn,
3384 queryString,
3385 useCount,
3386 staticUsageIn,
3387 createdBy,
3388 sysdate,
3389 currentUserId,
3390 sysdate,
3391 currentUserId,
3392 nvl(newStartDateIn, sysdate),
3393 null,
3394 ame_util.booleanTrue,
3395 isStripingAttributeIn);
3396 */
3397 useCount := calculateUseCount(attributeIdIn => attributeIdIn,
3398 applicationIdIn => applicationIdIn);
3399 startDate2 := nvl(newStartDateIn, sysdate);
3400 /* parent record was locked above so see if parent has been modified; if so
3401 raise an error, if not then insert
3402 */
3403 /*
3404 If any version of the object has created_by = 1, all versions,
3405 including the new version, should. This is a failsafe way to check
3406 whether previous versions of an already end-dated object had
3407 created_by = 1.
3408 */
3409 currentUserId := ame_util.getCurrentUserId;
3410 select count(*)
3411 into tempCount2
3412 from ame_attribute_usages
3413 where
3414 attribute_id = attributeIdIn and
3415 application_id = applicationIdIn and
3416 created_by = ame_util.seededDataCreatedById;
3417 if(tempCount2 > 0) then
3418 createdBy := ame_util.seededDataCreatedById;
3419 elsif(createdByIn is null) then
3420 createdBy := currentUserId;
3421 else
3422 createdBy := createdByIn;
3423 end if;
3424 insert into ame_attribute_usages(attribute_id,
3425 application_id,
3426 query_string,
3427 use_count,
3428 is_static,
3429 created_by,
3430 creation_date,
3431 last_updated_by,
3432 last_update_date,
3433 last_update_login,
3434 start_date,
3435 end_date,
3436 user_editable)
3437 values(attributeIdIn,
3438 applicationIdIn,
3439 queryString,
3440 useCount,
3441 staticUsageIn,
3442 createdBy,
3443 processingDate,
3444 currentUserId,
3445 processingDate,
3446 currentUserId,
3447 startDate2,
3448 null,
3449 ame_util.booleanTrue);
3450 if(finalizeIn) then
3451 if(updateParentObjectIn) then
3452 attributeDescription := getDescription(attributeIdIn => attributeIdIn);
3453 approverTypeId := getApproverTypeId(attributeIdIn => attributeIdIn);
3454 update ame_attributes
3455 set
3456 last_updated_by = currentUserId,
3457 last_update_date = startDate2,
3458 last_update_login = currentUserId,
3459 end_date = startDate2
3460 where
3461 attribute_id = attributeIdIn and
3462 sysdate between start_date and
3463 nvl(end_date - ame_util.oneSecond, sysdate);
3464 attributeId := new(nameIn => attributeName,
3465 typeIn => attributeType,
3466 descriptionIn => attributeDescription,
3467 attributeIdIn => attributeIdIn,
3468 itemClassIdIn => itemClassId,
3469 newStartDateIn => startDate2,
3470 approverTypeIdIn => approverTypeId,
3471 finalizeIn => false,
3472 createdByIn => createdByIn);
3473 close startDateCursor;
3474 end if;
3475 commit;
3476 end if;
3477 exception
3478 when ame_util.objectVersionException then
3479 rollback;
3480 if(startDateCursor%isOpen) then
3481 close startDateCursor;
3482 end if;
3483 errorCode := -20001;
3484 errorMessage :=
3485 ame_util.getMessage(applicationShortNameIn => 'PER',
3486 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
3487 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3488 routineNameIn => 'newAttributeUsage',
3489 exceptionNumberIn => errorCode,
3490 exceptionStringIn => errorMessage);
3491 raise_application_error(errorCode,
3492 errorMessage);
3493 when objectVersionNoDataException then
3494 rollback;
3495 if(startDateCursor%isOpen) then
3496 close startDateCursor;
3497 end if;
3498 errorCode := -20001;
3499 errorMessage :=
3500 ame_util.getMessage(applicationShortNameIn => 'PER',
3501 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
3502 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3503 routineNameIn => 'newAttributeUsage',
3504 exceptionNumberIn => errorCode,
3505 exceptionStringIn => errorMessage);
3506 raise_application_error(errorCode,
3507 errorMessage);
3508 when placeholderException then
3509 rollback;
3510 errorCode := -20001;
3511 errorMessage :=
3512 ame_util.getMessage(applicationShortNameIn => 'PER',
3513 messageNameIn => 'AME_400159_ATT_STAT_NOT_PLC');
3514 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3515 routineNameIn => 'newAttributeUsage',
3516 exceptionNumberIn => errorCode,
3517 exceptionStringIn => errorMessage);
3518 raise_application_error(errorCode,
3519 errorMessage);
3520 when dynamicUsageException then
3521 rollback;
3522 errorCode := -20001;
3523 errorMessage :=
3524 ame_util.getMessage(applicationShortNameIn => 'PER',
3525 messageNameIn => 'AME_400414_DYNAMIC_ATTR_USAGES');
3526 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3527 routineNameIn => 'newAttributeUsage',
3528 exceptionNumberIn => errorCode,
3529 exceptionStringIn => errorMessage);
3530 raise_application_error(errorCode,
3531 errorMessage);
3532 when nullQueryStringException then
3533 rollback;
3534 errorCode := -20001;
3535 errorMessage :=
3536 ame_util.getMessage(applicationShortNameIn => 'PER',
3537 messageNameIn => 'AME_400161_ATT_EMPTY_USAGE');
3538 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3539 routineNameIn => 'newAttributeUsage',
3540 exceptionNumberIn => errorCode,
3541 exceptionStringIn => errorMessage);
3542 raise_application_error(errorCode,
3543 errorMessage);
3544 when usageExistsException then
3545 rollback;
3546 errorCode := -20001;
3547 errorMessage :=
3548 ame_util.getMessage(applicationShortNameIn => 'PER',
3549 messageNameIn => 'AME_400162_ATT_USAGE_EXISTS');
3550 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3551 routineNameIn => 'newAttributeUsage',
3552 exceptionNumberIn => errorCode,
3553 exceptionStringIn => errorMessage);
3554 raise_application_error(errorCode,
3555 errorMessage);
3556 when queryStringColumnException then
3557 rollback;
3558 errorCode := -20001;
3559 errorMessage :=
3560 ame_util.getMessage(applicationShortNameIn => 'PER',
3561 messageNameIn => 'AME_400158_ATT_THREE_VALUES');
3562 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3563 routineNameIn => 'newAttributeUsage',
3564 exceptionNumberIn => errorCode,
3565 exceptionStringIn => errorMessage);
3566 raise_application_error(errorCode,
3567 errorMessage);
3568 when queryStringLengthException then
3569 rollback;
3570 errorCode := -20001;
3571 errorMessage :=
3572 ame_util.getMessage(applicationShortNameIn => 'PER',
3573 messageNameIn => 'AME_400163_ATT_USAGE_LONG',
3574 tokenNameOneIn => 'COLUMN_LENGTH',
3575 tokenValueOneIn => ame_util.getColumnLength(tableNameIn => 'ame_attribute_usages',
3576 columnNameIn => 'query_string'));
3577 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3578 routineNameIn => 'newAttributeUsage',
3579 exceptionNumberIn => errorCode,
3580 exceptionStringIn => errorMessage);
3581 raise_application_error(errorCode,
3582 errorMessage);
3583 when invalidBooleanValueException then
3584 rollback;
3585 errorCode := -20001;
3586 errorMessage :=
3587 ame_util.getMessage(applicationShortNameIn => 'PER',
3588 messageNameIn => 'AME_400322_LIN_ITEM_TRUE_SET');
3589 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3590 routineNameIn => 'newAttributeUsage',
3591 exceptionNumberIn => errorCode,
3592 exceptionStringIn => errorMessage);
3593 raise_application_error(errorCode,
3594 errorMessage);
3595 when invalidReferenceException then
3596 rollback;
3597 errorCode := -20001;
3598 errorMessage :=
3599 ame_util.getMessage(applicationShortNameIn => 'PER',
3600 messageNameIn => 'AME_400157_ATT_REF_LINE_ITEM');
3601 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3602 routineNameIn => 'newAttributeUsage',
3603 exceptionNumberIn => errorCode,
3604 exceptionStringIn => errorMessage);
3605 raise_application_error(errorCode,
3606 errorMessage);
3607 when badCurUsageException then
3608 rollback;
3609 transactionType := ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn);
3610 errorCode := -20001;
3611 errorMessage :=
3612 ame_util.getMessage(applicationShortNameIn => 'PER',
3613 messageNameIn => 'AME_400164_ATT_BAD_STAT_USG',
3614 tokenNameOneIn => 'TRANSACTION_TYPE',
3615 tokenValueOneIn => transactionType,
3616 tokenNameTwoIn => 'ATTRIBUTE',
3617 tokenValueTwoIn => attributeName);
3618 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3619 routineNameIn => 'newAttributeUsage',
3620 exceptionNumberIn => errorCode,
3621 exceptionStringIn => errorMessage);
3622 raise_application_error(errorCode,
3623 errorMessage);
3624 when stringDynamicException then
3625 rollback;
3626 errorCode := -20001;
3627 errorMessage :=
3628 ame_util.getMessage(applicationShortNameIn => 'PER',
3629 messageNameIn => 'AME_400165_ATT_DYN_USG_COMM');
3630 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3631 routineNameIn => 'newAttributeUsage',
3632 exceptionNumberIn => errorCode,
3633 exceptionStringIn => errorMessage);
3634 raise_application_error(errorCode,
3635 errorMessage);
3636 when stringStaticUsageException then
3637 rollback;
3638 errorCode := -20001;
3639 errorMessage :=
3640 ame_util.getMessage(applicationShortNameIn => 'PER',
3641 messageNameIn => 'AME_400166_ATT_STAT_USG_STRING');
3642 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3643 routineNameIn => 'newAttributeUsage',
3644 exceptionNumberIn => errorCode,
3645 exceptionStringIn => errorMessage);
3646 raise_application_error(errorCode,
3647 errorMessage);
3648 when booleanException then
3649 rollback;
3650 errorCode := -20001;
3651 errorMessage :=
3652 ame_util.getMessage(applicationShortNameIn => 'PER',
3653 messageNameIn => 'AME_400167_ATT_STAT_USG_BOOL');
3654 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3655 routineNameIn => 'newAttributeUsage',
3656 exceptionNumberIn => errorCode,
3657 exceptionStringIn => errorMessage);
3658 raise_application_error(errorCode,
3659 errorMessage);
3660 when badStaticDateUsageException then
3661 rollback;
3662 errorCode := -20001;
3663 errorMessage :=
3664 ame_util.getMessage(applicationShortNameIn => 'PER',
3665 messageNameIn => 'AME_400168_ATT_STAT_USG_DATE');
3666 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3667 routineNameIn => 'newAttributeUsage',
3668 exceptionNumberIn => errorCode,
3669 exceptionStringIn => errorMessage);
3670 raise_application_error(errorCode,
3671 errorMessage);
3672 when others then
3673 rollback;
3674 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3675 routineNameIn => 'newAttributeUsage',
3676 exceptionNumberIn => sqlcode,
3677 exceptionStringIn => '(attribute ID ' ||
3678 attributeIdIn||
3679 ') ' ||
3680 sqlerrm);
3681 raise;
3682 end newAttributeUsage;
3683 procedure newMandatoryAttributes(attributeIdIn in integer,
3684 actionTypeIdIn in integer,
3685 createdByIn in integer default null) as
3686 /* select every application having a rule that uses the approval type */
3687 cursor getApplicationId (actionTypeIdIn in integer) is
3688 /* the distinct is necessary because of the possibility that multiple rules
3689 within an application will use the specified approval type */
3690 select distinct ame_rule_usages.item_id
3691 from ame_action_usages,
3692 ame_actions,
3693 ame_rule_usages
3694 where
3695 ame_action_usages.action_id = ame_actions.action_id and
3696 ame_action_usages.rule_id = ame_rule_usages.rule_id and
3697 ame_actions.action_type_id = actionTypeIdIn and
3698 ((sysdate between ame_action_usages.start_date and
3699 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
3700 (sysdate < ame_action_usages.start_date and
3701 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
3702 ame_action_usages.start_date + ame_util.oneSecond))) and
3703 ( sysdate between ame_actions.start_date and
3704 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) ) and
3705 ((sysdate between ame_rule_usages.start_date and
3706 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
3707 (sysdate < ame_rule_usages.start_date and
3708 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
3709 ame_rule_usages.start_date + ame_util.oneSecond)));
3710 cursor applicationCursor(attributeIdIn in integer) is
3711 select application_id
3712 from ame_attribute_usages
3713 where
3714 attribute_id = attributeIdIn and
3715 sysdate between start_date and
3716 nvl(end_date - ame_util.oneSecond, sysdate) ;
3717 applicationCount integer;
3718 commitData boolean;
3719 currentUserId integer;
3720 createdBy integer;
3721 tempCount integer;
3722 tempCount1 integer;
3723 tempIndex integer;
3724 begin
3725 select count(*)
3726 into applicationCount
3727 from ame_attribute_usages
3728 where attribute_id = attributeIdIn and
3729 sysdate between start_date and
3730 nvl(end_date - ame_util.oneSecond, sysdate);
3731 for getAppRec in getApplicationId(actionTypeIdIn => actionTypeIdIn) loop
3732 /* for every application that uses the approval type, make sure an
3733 attribute usage exists for the required attribute */
3734 select count(*)
3735 into tempCount1
3736 from ame_attribute_usages
3737 where
3738 attribute_id = attributeIdIn and
3739 application_id = getAppRec.item_id and
3740 sysdate between start_date and
3741 nvl(end_date - ame_util.oneSecond, sysdate) ;
3742 if tempCount1 = 0 then
3743 raise_application_error(-20001,
3744 ame_util.getMessage(applicationShortNameIn => 'PER',
3745 messageNameIn => 'AME_400169_ATT_LACK_QRY_STRING'));
3746 end if;
3747 end loop;
3748 select count(*)
3749 into tempCount
3750 from ame_mandatory_attributes
3751 where
3752 attribute_id = attributeIdIn and
3753 action_type_id = actionTypeIdIn and
3754 sysdate between start_date and
3755 nvl(end_date - ame_util.oneSecond, sysdate) ;
3756 if tempCount > 0 then
3757 return;
3758 end if;
3759 currentUserId := ame_util.getCurrentUserId;
3760 if(createdByIn is null) then
3761 createdBy := currentUserId;
3762 else
3763 createdBy := createdByIn;
3764 end if;
3765 insert into ame_mandatory_attributes
3766 (attribute_id,
3767 action_type_id,
3768 created_by,
3769 creation_date,
3770 last_updated_by,
3771 last_update_date,
3772 last_update_login,
3773 start_date,
3774 end_date)
3775 values
3776 (attributeIdIn,
3777 actionTypeIdIn,
3778 createdBy,
3779 sysdate,
3780 currentUserId,
3781 sysdate,
3782 currentUserId,
3783 sysdate,
3784 null);
3785 /* Call updateUseCount after creating the new attribute requirements. */
3786 tempIndex := 1;
3787 for tempApplication in applicationCursor(attributeIdIn => attributeIdIn) loop
3788 if(tempIndex = applicationCount)then
3789 commitData := true;
3790 else
3791 commitData := false;
3792 end if;
3793 updateUseCount(attributeIdIn => attributeIdIn,
3794 applicationIdIn => tempApplication.application_id,
3795 finalizeIn => commitData);
3796 tempIndex := tempIndex + 1;
3797 end loop;
3798 exception
3799 when others then
3800 rollback;
3801 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3802 routineNameIn => 'newMandatoryAttributes',
3803 exceptionNumberIn => sqlcode,
3804 exceptionStringIn => '(attribute ID ' ||
3805 attributeIdIn||
3806 ') ' ||
3807 sqlerrm);
3808 raise;
3809 end newMandatoryAttributes;
3810 procedure remove(attributeIdIn in integer,
3811 finalizeIn in boolean default false) as
3812 currentUserId integer;
3813 errorCode integer;
3814 errorMessage ame_util.longestStringType;
3815 inUseException exception;
3816 processingDate date;
3817 attributeType ame_attributes.attribute_type%type;
3818 begin
3819 processingDate := sysdate;
3820 currentUserId := ame_util.getCurrentUserId;
3821 attributeType := getType(attributeIdIn => attributeIdIn);
3822 if attributeType = ame_util.stringAttributeType then
3823 update ame_string_values
3824 set
3825 last_updated_by = currentUserId,
3826 last_update_date = processingDate,
3827 last_update_login = currentUserId,
3828 end_date = processingDate
3829 where
3830 condition_id in (select condition_id
3831 from ame_conditions
3832 where processingDate between start_date and
3833 nvl(end_date - ame_util.oneSecond, processingDate) and
3834 attribute_id = attributeIdIn) and
3835 processingDate between start_date and
3836 nvl(end_date - ame_util.oneSecond, processingDate) ;
3837 end if;
3838 update ame_conditions
3839 set
3840 last_updated_by = currentUserId,
3841 last_update_date = processingDate,
3842 last_update_login = currentUserId,
3843 end_date = processingDate
3844 where
3845 attribute_id = attributeIdIn and
3846 processingDate between start_date and
3847 nvl(end_date - ame_util.oneSecond, processingDate) ;
3848 update ame_attributes
3849 set
3850 last_updated_by = currentUserId,
3851 last_update_date = processingDate,
3852 last_update_login = currentUserId,
3853 end_date = processingDate
3854 where
3855 attribute_id = attributeIdIn and
3856 processingDate between start_date and
3857 nvl(end_date - ame_util.oneSecond, processingDate) ;
3858 if(finalizeIn) then
3859 commit;
3860 end if;
3861 exception
3862 when others then
3863 rollback;
3864 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3865 routineNameIn => 'remove',
3866 exceptionNumberIn => sqlcode,
3867 exceptionStringIn => '(attribute ID ' ||
3868 attributeIdIn||
3869 ') ' ||
3870 sqlerrm);
3871 raise;
3872 end remove;
3873 procedure removeMandatoryAttributes(attributeIdIn in integer,
3874 actionTypeIdIn in integer,
3875 finalizeIn in boolean default true) as
3876 cursor applicationCursor(attributeIdIn in integer) is
3877 select application_id
3878 from ame_attribute_usages
3879 where
3880 attribute_id = attributeIdIn and
3881 sysdate between start_date and
3882 nvl(end_date - ame_util.oneSecond, sysdate) ;
3883 currentUserId integer;
3884 processingDate date;
3885 begin
3886 processingDate := sysdate;
3887 currentUserId := ame_util.getCurrentUserId;
3888 update ame_mandatory_attributes
3889 set
3890 last_updated_by = currentUserId,
3891 last_update_date = processingDate,
3892 last_update_login = currentUserId,
3893 end_date = processingDate
3894 where
3895 attribute_id = attributeIdIn and
3896 action_type_id = actionTypeIdIn and
3897 processingDate between start_date and
3898 nvl(end_date - ame_util.oneSecond, processingDate) ;
3899 /* Call updateUseCount after removing the attribute requirements. */
3900 for tempApplication in applicationCursor(attributeIdIn => attributeIdIn) loop
3901 updateUseCount(attributeIdIn => attributeIdIn,
3902 applicationIdIn => tempApplication.application_id,
3903 finalizeIn => finalizeIn);
3904 end loop;
3905 exception
3906 when others then
3907 rollback;
3908 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
3909 routineNameIn => 'removeMandatoryAttributes',
3910 exceptionNumberIn => sqlcode,
3911 exceptionStringIn => '(attribute ID ' ||
3912 attributeIdIn||
3913 ') ' ||
3914 sqlerrm);
3915 raise;
3916 end removeMandatoryAttributes;
3917 procedure removeUsage(attributeIdIn in integer,
3918 parentVersionStartDateIn in date,
3919 childVersionStartDateIn in date,
3920 applicationIdIn in integer,
3921 allowAttributeUsageDeleteIn in boolean default false,
3922 finalizeIn in boolean default false,
3923 deleteConditionsIn in boolean default false,
3924 itemClassIdIn in integer) as
3925 cursor startDateCursor is
3926 select start_date
3927 from ame_attributes
3928 where
3929 attribute_id = attributeIdIn and
3930 sysdate between start_date and
3931 nvl(end_date - ame_util.oneSecond, sysdate)
3932 for update;
3933 cursor startDateCursor2 is
3934 select start_date
3935 from ame_attribute_usages
3936 where
3937 attribute_id = attributeIdIn and
3938 application_id = applicationIdIn and
3939 sysdate between start_date and
3940 nvl(end_date - ame_util.oneSecond, sysdate)
3941 for update;
3942 approverTypeId integer;
3943 attributeId ame_attributes.attribute_id%type;
3944 attributeDescription ame_attributes.description%type;
3945 attributeName ame_attributes.name%type;
3946 attributeType ame_attributes.attribute_type%type;
3947 conditionIdList ame_util.idList;
3948 conditionVersionStartDate date;
3949 currentUserId integer;
3950 errorCode integer;
3951 errorMessage ame_util.longestStringType;
3952 inUseException exception;
3953 isMandatoryException exception;
3954 lineItem ame_attributes.line_item%type;
3955 objectVersionNoDataException exception;
3956 startDate date;
3957 startDate2 date;
3958 processingDate date;
3959 /*
3960 AME_STRIPING
3961 stripingAttributeException exception;
3962 */
3963 begin
3964 processingDate := sysdate;
3965 /* Try to get a lock on the record. */
3966 open startDateCursor;
3967 fetch startDateCursor into startDate;
3968 if startDateCursor%notfound then
3969 raise objectVersionNoDataException;
3970 end if;
3971 if(parentVersionStartDateIn <> startDate) then
3972 close startDateCursor;
3973 raise ame_util.objectVersionException;
3974 end if;
3975 open startDateCursor2;
3976 fetch startDateCursor2 into startDate2;
3977 if startDateCursor2%notfound then
3978 raise objectVersionNoDataException;
3979 end if;
3980 if(childVersionStartDateIn <> startDate2) then
3981 close startDateCursor2;
3982 raise ame_util.objectVersionException;
3983 end if;
3984 /* Don't allow deleting usages for mandatory attributes. */
3985 if not allowAttributeUsageDeleteIn then
3986 if(isMandatory(attributeIdIn => attributeIdIn)) then
3987 raise isMandatoryException;
3988 end if;
3989 end if;
3990 /* Don't allow deleting usages for active attributes. */
3991 if(isInUseByApplication(attributeIdIn => attributeIdIn,
3992 applicationIdIn => applicationIdIn)) then
3993 raise inUseException;
3994 end if;
3995 /*
3996 AME_STRIPING
3997 if(isAStripingAttribute(applicationIdIn => applicationIdIn,
3998 attributeIdIn => attributeIdIn)) then
3999 raise stripingAttributeException;
4000 end if;
4001 */
4002 currentUserId := ame_util.getCurrentUserId;
4003 /* Not active, either not mandatory or allowed to delete, so delete the usage. */
4004 update ame_attribute_usages
4005 set
4006 last_updated_by = currentUserId,
4007 last_update_date = processingDate,
4008 last_update_login = currentUserId,
4009 end_date = processingDate
4010 where
4011 attribute_id = attributeIdIn and
4012 application_id = applicationIdIn and
4013 processingDate between start_date and
4014 nvl(end_date - ame_util.oneSecond, processingDate);
4015 /*
4016 If the attribute name is
4017 (1) not used by any conditions in any rules used by the transaction type
4018 (2) is not required by any approval type (whether or not a rule uses the approval type, at present)
4019 (3) is not mandatory delete the attribute (not just the usage).
4020 */
4021
4022 if(hasUsage(attributeIdIn => attributeIdIn,
4023 applicationIdIn => applicationIdIn) or
4024 isRequired(attributeIdIn => attributeIdIn) or
4025 isMandatory(attributeIdIn => attributeIdIn)) then
4026 attributeName := getName(attributeIdIn => attributeIdIn);
4027 attributeDescription := getDescription(attributeIdIn => attributeIdIn);
4028 attributeType := getType(attributeIdIn => attributeIdIn);
4029 approverTypeId := getApproverTypeId(attributeIdIn => attributeIdIn);
4030 update ame_attributes
4031 set
4032 last_updated_by = currentUserId,
4033 last_update_date = processingDate,
4034 last_update_login = currentUserId,
4035 end_date = processingDate
4036 where
4037 attribute_id = attributeIdIn and
4038 processingDate between start_date and
4039 nvl(end_date - ame_util.oneSecond, processingDate) ;
4040 attributeId := new(nameIn => attributeName,
4041 typeIn => attributeType,
4042 descriptionIn => attributeDescription,
4043 attributeIdIn => attributeIdIn,
4044 approverTypeIdIn => approverTypeId,
4045 finalizeIn => false,
4046 itemClassIdIn => itemClassIdIn);
4047 else
4048 ame_attribute_pkg.remove(attributeIdIn => attributeIdIn,
4049 finalizeIn => false);
4050 end if;
4051 close startDateCursor;
4052 close startDateCursor2;
4053 if(finalizeIn) then
4054 commit;
4055 end if;
4056 exception
4057 /*
4058 AME_STRIPING
4059 when stripingAttributeException then
4060 if(startDateCursor%isOpen) then
4061 close startDateCursor;
4062 end if;
4063 attributeName := getName(attributeIdIn => attributeIdIn);
4064 errorCode := -20001;
4065 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4066 messageNameIn => 'AME_400309_ATT_NAME_STRP_ATTR',
4067 tokenNameOneIn => 'ATTRIBUTE_NAME',
4068 tokenValueOneIn => attributeName);
4069 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4070 routineNameIn => 'removeUsage',
4071 exceptionNumberIn => errorCode,
4072 exceptionStringIn => errorMessage);
4073 raise_application_error(errorCode,
4074 errorMessage);
4075 */
4076 when isMandatoryException then
4077 rollback;
4078 if(startDateCursor%isOpen) then
4079 close startDateCursor;
4080 end if;
4081 if(startDateCursor2%isOpen) then
4082 close startDateCursor2;
4083 end if;
4084 errorCode := -20001;
4085 errorMessage :=
4086 ame_util.getMessage(applicationShortNameIn => 'PER',
4087 messageNameIn => 'AME_400170_ATT_MAND_CANT_DEL');
4088 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4089 routineNameIn => 'removeUsage',
4090 exceptionNumberIn => errorCode,
4091 exceptionStringIn => errorMessage);
4092 raise_application_error(errorCode,
4093 errorMessage);
4094 when inUseException then
4095 rollback;
4096 if(startDateCursor%isOpen) then
4097 close startDateCursor;
4098 end if;
4099 if(startDateCursor2%isOpen) then
4100 close startDateCursor2;
4101 end if;
4102 errorCode := -20001;
4103 errorMessage :=
4104 ame_util.getMessage(applicationShortNameIn => 'PER',
4105 messageNameIn => 'AME_400171_ATT_IS_IN_USE');
4106 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4107 routineNameIn => 'removeUsage',
4108 exceptionNumberIn => errorCode,
4109 exceptionStringIn => errorMessage);
4110 raise_application_error(errorCode,
4111 errorMessage);
4112 when ame_util.objectVersionException then
4113 rollback;
4114 if(startDateCursor%isOpen) then
4115 close startDateCursor;
4116 end if;
4117 if(startDateCursor2%isOpen) then
4118 close startDateCursor2;
4119 end if;
4120 errorCode := -20001;
4121 errorMessage :=
4122 ame_util.getMessage(applicationShortNameIn => 'PER',
4123 messageNameIn => 'AME_400143_ACT_OBJECT_CHNGED');
4124 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4125 routineNameIn => 'removeUsage',
4126 exceptionNumberIn => errorCode,
4127 exceptionStringIn => errorMessage);
4128 raise_application_error(errorCode,
4129 errorMessage);
4130 when objectVersionNoDataException then
4131 rollback;
4132 if(startDateCursor%isOpen) then
4133 close startDateCursor;
4134 end if;
4135 if(startDateCursor2%isOpen) then
4136 close startDateCursor2;
4137 end if;
4138 errorCode := -20001;
4139 errorMessage :=
4140 ame_util.getMessage(applicationShortNameIn => 'PER',
4141 messageNameIn => 'AME_400145_ACT_OBJECT_DELETED');
4142 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4143 routineNameIn => 'removeUsage',
4144 exceptionNumberIn => errorCode,
4145 exceptionStringIn => errorMessage);
4146 raise_application_error(errorCode,
4147 errorMessage);
4148 when others then
4149 rollback;
4150 if(startDateCursor%isOpen) then
4151 close startDateCursor;
4152 end if;
4153 if(startDateCursor2%isOpen) then
4154 close startDateCursor2;
4155 end if;
4156 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4157 routineNameIn => 'removeUsage',
4158 exceptionNumberIn => sqlcode,
4159 exceptionStringIn => '(attribute ID ' ||
4160 attributeIdIn||
4161 ') ' ||
4162 sqlerrm);
4163 raise;
4164 end removeUsage;
4165 /*
4166 AME_STRIPING
4167 procedure setStripingAttributesToNull(applicationIdIn in integer,
4168 oldStripedAttributesIn in ame_util.idList default ame_util.emptyIdList,
4169 lastStripingAttributeIn in boolean default false) as
4170 cursor getStripingAttributesCursor(applicationIdIn in integer) is
4171 select ame_attribute_usages.attribute_id,
4172 ame_attribute_usages.is_static,
4173 ame_attribute_usages.query_string,
4174 ame_attribute_usages.end_date,
4175 ame_attributes.line_item
4176 from ame_attributes,
4177 ame_attribute_usages
4178 where
4179 ame_attributes.attribute_id = ame_attribute_usages.attribute_id and
4180 ame_attribute_usages.application_id = applicationIdIn and
4181 ame_attribute_usages.is_striping_attribute = ame_util.booleanTrue and
4182 (ame_attribute_usages.start_date <= sysdate and
4183 (ame_attribute_usages.end_date is null or sysdate < ame_attribute_usages.end_date)) and
4184 (ame_attributes.start_date <= sysdate and
4185 (ame_attributes.end_date is null or sysdate < ame_attributes.end_date));
4186 endDate date;
4187 oldAttributeCount integer;
4188 startDate varchar2(50);
4189 stripeSetCount integer;
4190 stripeSetId ame_stripe_sets.stripe_set_id%type;
4191 stripeSetIds ame_util.idList;
4192 versionStartDate date;
4193 begin
4194 oldAttributeCount := oldStripedAttributesIn.count;
4195 for getStripingAttributesRec in
4196 getStripingAttributesCursor(applicationIdIn => applicationIdIn) loop
4197 if oldAttributeCount > 0 then
4198 /*
4199 /* check to see if the attribute is no longer in the new set */
4200 /*
4201 for i in 1..oldAttributeCount loop
4202 if(getStripingAttributesRec.attribute_id = oldStripedAttributesIn(i)) then
4203 endDate := sysdate - ame_util.oneSecond;
4204 changeUsage(attributeIdIn => getStripingAttributesRec.attribute_id,
4205 applicationIdIn => applicationIdIn,
4206 staticUsageIn => getStripingAttributesRec.is_static,
4207 queryStringIn => getStripingAttributesRec.query_string,
4208 endDateIn => endDate,
4209 newStartDateIn => sysdate,
4210 lineItemAttributeIn => getStripingAttributesRec.line_item,
4211 isStripingAttributeIn => ame_util.booleanFalse);
4212 ame_admin_pkg.removeStripeSetAttributes(applicationIdIn => applicationIdIn,
4213 attributeIdIn => getStripingAttributesRec.attribute_id);
4214 end if;
4215 end loop;
4216 end if;
4217 end loop;
4218 if(oldAttributeCount = 0 or lastStripingAttributeIn) then
4219 ame_admin_pkg.getStripeSetIds(applicationIdIn => applicationIdIn,
4220 stripeSetIdsOut => stripeSetIds);
4221 ame_admin_pkg.removeAllStripeSets(applicationIdIn => applicationIdIn,
4222 deleteStripeSetIdZeroIn => lastStripingAttributeIn);
4223 ame_rule_pkg.removeRuleStripeSet(stripeSetIdsIn => stripeSetIds);
4224 stripeSetId := ame_util.getCurrentStripeSetId(applicationIdIn => applicationIdIn);
4225 owa_util.mime_header('text/html', FALSE);
4226 owa_cookie.remove(name => ame_util.getStripeSetCookieName(applicationIdIn => applicationIdIn),
4227 val => to_char(stripeSetId));
4228 owa_util.http_header_close;
4229 end if;
4230 commit;
4231 exception
4232 when others then
4233 rollback;
4234 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4235 routineNameIn => 'setStripingAttributesToNull',
4236 exceptionNumberIn => sqlcode,
4237 exceptionStringIn => sqlerrm);
4238 raise;
4239 end setStripingAttributesToNull;
4240 */
4241 procedure updateUseCount(attributeIdIn in integer,
4242 applicationIdIn in integer,
4243 finalizeIn in boolean default true) as
4244 currentUserId integer;
4245 errorCode integer;
4246 errorMessage ame_util.longestStringType;
4247 /*
4248 AME_STRIPING
4249 stripingAttribute ame_attribute_usages.is_striping_attribute%type;
4250 */
4251 lineItem ame_attributes.line_item%type;
4252 nullQueryException exception;
4253 queryString ame_attribute_usages.query_string%type;
4254 result boolean;
4255 staticUsage ame_attribute_usages.is_static%type;
4256 useCount integer;
4257 processingDate date;
4258 begin
4259 processingDate := sysdate;
4260 queryString := getQueryString(attributeIdIn => attributeIdIn,
4261 applicationIdIn => applicationIdIn);
4262 staticUsage := ame_attribute_pkg.getStaticUsage(attributeIdIn => attributeIdIn,
4263 applicationIdIn => applicationIdIn);
4264 /*
4265 AME_STRIPING
4266 if(isStripingAttribute(attributeIdIn => attributeIdIn,
4267 applicationIdIn => applicationIdIn)) then
4268 stripingAttribute := ame_util.booleanTrue;
4269 else
4270 stripingAttribute := ame_util.booleanFalse;
4271 end if;
4272 */
4273 if(queryString is null and
4274 staticUsage = ame_util.booleanFalse) then
4275 raise nullQueryException;
4276 end if;
4277 currentUserId := ame_util.getCurrentUserId;
4278 useCount := calculateUseCount(attributeIdIn => attributeIdIn,
4279 applicationIdIn => applicationIdIn);
4280 update ame_attribute_usages
4281 set use_count = useCount
4282 where
4283 application_id = applicationIdIn and
4284 attribute_id = attributeIdIn and
4285 processingDate between start_date and
4286 nvl(end_date - ame_util.oneSecond, processingDate);
4287 /* newAttributeUsage calls calculateUseCount to get the new use_count value. */
4288 /*
4289 AME_STRIPING
4290 newAttributeUsage(attributeIdIn => attributeIdIn,
4291 applicationIdIn => applicationIdIn,
4292 staticUsageIn => staticUsage,
4293 queryStringIn => queryString,
4294 lineItemAttributeIn => lineItem,
4295 isStripingAttributeIn => stripingAttribute,
4296 finalizeIn => finalizeIn);
4297 */
4298 exception
4299 when nullQueryException then
4300 rollback;
4301 errorCode := -20001;
4302 errorMessage :=
4303 ame_util.getMessage(applicationShortNameIn => 'PER',
4304 messageNameIn => 'AME_400172_ATT_NULL_ATT_USAGE');
4305 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4306 routineNameIn => 'updateUseCount',
4307 exceptionNumberIn => errorCode,
4308 exceptionStringIn => errorMessage);
4309 raise_application_error(errorCode,
4310 errorMessage);
4311 when others then
4312 rollback;
4313 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4314 routineNameIn => 'updateUseCount',
4315 exceptionNumberIn => sqlcode,
4316 exceptionStringIn => '(attribute ID ' ||
4317 attributeIdIn||
4318 ') ' ||
4319 sqlerrm);
4320 raise;
4321 end updateUseCount;
4322 procedure updateUseCount2(ruleIdIn in integer,
4323 applicationIdIn in integer) as
4324 attributeIds ame_util.idList;
4325 upperLimit integer;
4326 useCount integer;
4327 processingDate date;
4328 begin
4329 processingDate := sysdate;
4330 ame_rule_pkg.getRequiredAttributes(ruleIdIn => ruleIdIn,
4331 attributeIdsOut => attributeIds);
4332 upperLimit := attributeIds.count;
4333 for i in 1 .. upperLimit loop
4334 useCount := to_number(getUseCount(attributeIdIn => attributeIds(i),
4335 applicationIdIn => applicationIdIn));
4336 update ame_attribute_usages
4337 set use_count = useCount - 1
4338 where
4339 application_id = applicationIdIn and
4340 attribute_id = attributeIds(i) and
4341 processingDate between start_date and
4342 nvl(end_date - ame_util.oneSecond, processingDate) ;
4343 end loop;
4344 commit;
4345 exception
4346 when others then
4347 rollback;
4348 ame_util.runtimeException(packageNameIn => 'ame_attribute_pkg',
4349 routineNameIn => 'updateUseCount2',
4350 exceptionNumberIn => sqlcode,
4351 exceptionStringIn => '(rule ID ' ||
4352 ruleIdIn||
4353 ') ' ||
4354 sqlerrm);
4355 raise;
4356 end updateUseCount2;
4357 end ame_attribute_pkg;