[Home] [Help]
PACKAGE BODY: APPS.AME_TEST_PKG
Source
1 package body ame_test_pkg as
2 /* $Header: ameotest.pkb 120.0 2005/07/26 06:05:34 mbocutt noship $ */
3 function getTestTransactionId return varchar2 is
4 tempId integer;
5 begin
6 select ame_test_trans_s.nextval into tempId from dual;
7 return(to_char(tempId));
8 exception
9 when others then
10 rollback;
11 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
12 routineNameIn => 'getTestTransactionId',
13 exceptionNumberIn => sqlcode,
14 exceptionStringIn => sqlerrm);
15 raise;
16 return(null);
17 end getTestTransactionId;
18 function isTestItemIdDuplicate(applicationIdIn in integer,
19 transactionIdIn in varchar2,
20 itemClassIdIN in integer,
21 itemIdIn in varchar2) return boolean as
22 cursor itemIdCursor(applicationIdIn in integer,
23 transactionIdIn in varchar2,
24 itemClassIdIn in integer) is
25 select item_id
26 from ame_test_trans_att_values
27 where
28 application_id = applicationIdIn and
29 transaction_id = transactionIdIn and
30 item_class_id = itemClassIdIn and
31 item_id is not null
32 order by item_id;
33 begin
34 for itemIdRec in itemIdCursor(applicationIdIn => applicationIdIn,
35 transactionIdIn => transactionIdIn,
36 itemClassIdIn => itemClassIdIn) loop
37 if(itemIdRec.item_id = itemIdIn) then
38 return true;
39 end if;
40 end loop;
41 return(false);
42 exception
43 when others then
44 rollback;
45 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
46 routineNameIn => 'isTestItemIdDuplicate',
47 exceptionNumberIn => sqlcode,
48 exceptionStringIn => sqlerrm);
49 raise;
50 return(true);
51 end isTestItemIdDuplicate;
52 procedure deleteTestItems(applicationIdIn in integer,
53 transactionIdIn in varchar2,
54 itemClassIdIn in integer,
55 deleteIn in ame_util.stringList) as
56 upperLimit integer;
57 begin
58 upperLimit := deleteIn.count;
59 forall i in 1 .. upperLimit
60 delete from ame_test_trans_att_values
61 where
62 application_id = applicationIdIn and
63 transaction_id = transactionIdIn and
64 itemClassIdIn = itemClassIdIn and
65 item_id = deleteIn(i);
66 commit;
67 exception
68 when others then
69 rollback;
70 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
71 routineNameIn => 'deleteTestItems',
72 exceptionNumberIn => sqlcode,
73 exceptionStringIn => sqlerrm);
74 raise;
75 end deleteTestItems;
76 procedure getAllAttributeValues(applicationIdIn in integer,
77 transactionIdIn in varchar2,
78 itemIdIn in varchar2 default null,
79 attributeIdsOut out nocopy ame_util.idList,
80 attributeNamesOut out nocopy ame_util.stringList,
81 attributeTypesOut out nocopy ame_util.stringList,
82 isMandatoryOut out nocopy ame_util.stringList,
83 attributeValues1Out out nocopy ame_util.attributeValueList,
84 attributeValues2Out out nocopy ame_util.attributeValueList,
85 attributeValues3Out out nocopy ame_util.attributeValueList) as
86 cursor attValueCursor(applicationIdIn in integer,
87 transactionIdIn in varchar2,
88 itemIdIn in varchar2) is
89 select
90 attribute_id,
91 attribute_name,
92 attribute_type,
93 is_mandatory,
94 attribute_value_1,
95 attribute_value_2,
96 attribute_value_3
97 from ame_test_trans_att_values
98 where
99 application_id = applicationIdIn and
100 transaction_id = transactionIdIn and
101 ((itemIdIn is null and item_id is null) or
102 itemIdIn = item_id)
103 order by attribute_name;
104 outputIndex integer;
105 begin
106 outputIndex := 0; /* pre-increment */
107 for tempAttValue in attValueCursor(applicationIdIn => applicationIdIn,
108 transactionIdIn => transactionIdIn,
109 itemIdIn => itemIdIn) loop
110 outputIndex := outputIndex + 1;
111 attributeIdsOut(outputIndex) := tempAttValue.attribute_id;
112 attributeNamesOut(outputIndex) := tempAttValue.attribute_name;
113 attributeTypesOut(outputIndex) := tempAttValue.attribute_type;
114 isMandatoryOut(outputIndex) := tempAttValue.is_mandatory;
115 attributeValues1Out(outputIndex) := tempAttValue.attribute_value_1;
116 attributeValues2Out(outputIndex) := tempAttValue.attribute_value_2;
117 attributeValues3Out(outputIndex) := tempAttValue.attribute_value_3;
118 end loop;
119 if(outputIndex = 0) then
120 attributeIdsOut := ame_util.emptyIdList;
121 attributeNamesOut := ame_util.emptyStringList;
122 attributeTypesOut := ame_util.emptyStringList;
123 isMandatoryOut := ame_util.emptyStringList;
124 attributeValues1Out := ame_util.emptyAttributeValueList;
125 attributeValues2Out := ame_util.emptyAttributeValueList;
126 attributeValues3Out := ame_util.emptyAttributeValueList;
127 end if;
128 exception
129 when others then
130 rollback;
131 attributeIdsOut := ame_util.emptyIdList;
132 attributeNamesOut := ame_util.emptyStringList;
133 attributeTypesOut := ame_util.emptyStringList;
134 isMandatoryOut := ame_util.emptyStringList;
135 attributeValues1Out := ame_util.emptyAttributeValueList;
136 attributeValues2Out := ame_util.emptyAttributeValueList;
137 attributeValues3Out := ame_util.emptyAttributeValueList;
138 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
139 routineNameIn => 'getAllAttributeValues',
140 exceptionNumberIn => sqlcode,
141 exceptionStringIn => sqlerrm);
142 raise;
143 end getAllAttributeValues;
144 procedure getAllAttributeValues2(applicationIdIn in integer,
145 transactionIdIn in varchar2,
146 itemClassIdIn in integer,
147 itemIdIn in varchar2 default null,
148 attributeIdsOut out nocopy ame_util.idList,
149 attributeNamesOut out nocopy ame_util.stringList,
150 attributeTypesOut out nocopy ame_util.stringList,
151 isMandatoryOut out nocopy ame_util.stringList,
152 attributeValues1Out out nocopy ame_util.attributeValueList,
153 attributeValues2Out out nocopy ame_util.attributeValueList,
154 attributeValues3Out out nocopy ame_util.attributeValueList) as
155 cursor attValueCursor(applicationIdIn in integer,
156 transactionIdIn in varchar2,
157 itemClassIdIn in integer,
158 itemIdIn in varchar2) is
159 select
160 attribute_id,
161 attribute_name,
162 attribute_type,
163 is_mandatory,
164 attribute_value_1,
165 attribute_value_2,
166 attribute_value_3
167 from ame_test_trans_att_values
168 where
169 application_id = applicationIdIn and
170 transaction_id = transactionIdIn and
171 item_class_id = itemClassIdIn and
172 ((itemIdIn is null and item_id is null) or
173 itemIdIn = item_id)
174 order by attribute_name;
175 outputIndex integer;
176 begin
177 outputIndex := 0; /* pre-increment */
178 for tempAttValue in attValueCursor(applicationIdIn => applicationIdIn,
179 transactionIdIn => transactionIdIn,
180 itemClassIdIn => itemClassIdIn,
181 itemIdIn => itemIdIn) loop
182 outputIndex := outputIndex + 1;
183 attributeIdsOut(outputIndex) := tempAttValue.attribute_id;
184 attributeNamesOut(outputIndex) := tempAttValue.attribute_name;
185 attributeTypesOut(outputIndex) := tempAttValue.attribute_type;
186 isMandatoryOut(outputIndex) := tempAttValue.is_mandatory;
187 attributeValues1Out(outputIndex) := tempAttValue.attribute_value_1;
188 attributeValues2Out(outputIndex) := tempAttValue.attribute_value_2;
189 attributeValues3Out(outputIndex) := tempAttValue.attribute_value_3;
190 end loop;
191 if(outputIndex = 0) then
192 attributeIdsOut := ame_util.emptyIdList;
193 attributeNamesOut := ame_util.emptyStringList;
194 attributeTypesOut := ame_util.emptyStringList;
195 isMandatoryOut := ame_util.emptyStringList;
196 attributeValues1Out := ame_util.emptyAttributeValueList;
197 attributeValues2Out := ame_util.emptyAttributeValueList;
198 attributeValues3Out := ame_util.emptyAttributeValueList;
199 end if;
200 exception
201 when others then
202 rollback;
203 attributeIdsOut := ame_util.emptyIdList;
204 attributeNamesOut := ame_util.emptyStringList;
205 attributeTypesOut := ame_util.emptyStringList;
206 isMandatoryOut := ame_util.emptyStringList;
207 attributeValues1Out := ame_util.emptyAttributeValueList;
208 attributeValues2Out := ame_util.emptyAttributeValueList;
209 attributeValues3Out := ame_util.emptyAttributeValueList;
210 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
211 routineNameIn => 'getAllAttributeValues2',
212 exceptionNumberIn => sqlcode,
213 exceptionStringIn => sqlerrm);
214 raise;
215 end getAllAttributeValues2;
216 procedure getApplicableRules(applicationIdIn in integer,
217 transactionIdIn in varchar2,
218 ruleListVersionIn in integer,
219 testOrRealTransTypeIn in varchar2,
220 ruleItemClassIdsOut out nocopy ame_util.idList,
221 itemClassIdsOut out nocopy ame_util.idList,
222 itemIdsOut out nocopy ame_util.stringList,
223 ruleTypesOut out nocopy ame_util.idList,
224 ruleDescriptionsOut out nocopy ame_util.stringList,
225 ruleIdsOut out nocopy ame_util.idList) as
226 isTestTransaction boolean;
227 processPriorities boolean;
228 processProductionActions boolean;
229 processProductionRules boolean;
230 tempConfigVarValue ame_util.stringType;
231 begin
232 if(ruleListVersionIn = 1) then
233 /* The returned rule list does not account for priorities, exceptions,
234 or multiple actions. */
235 processPriorities := false;
236 elsif(ruleListVersionIn = 2) then
237 /* The returned rule list accounts for priorities but not exceptions
238 or multiple actions. */
239 processPriorities := true;
240 else
241 /* The returned rule list accounts for priorities, exceptions, and
242 multiple actions. */
243 processPriorities := true;
244 end if;
245 if(testOrRealTransTypeIn = ame_util.testTrans) then
246 isTestTransaction := true;
247 else
248 isTestTransaction := false;
249 end if;
250 ame_engine.updateTransactionState(isTestTransactionIn => isTestTransaction,
251 isLocalTransactionIn => true,
252 fetchConfigVarsIn => true,
253 fetchOldApproversIn => false,
254 fetchInsertionsIn => false,
255 fetchDeletionsIn => false,
256 fetchAttributeValuesIn => true,
257 fetchInactiveAttValuesIn => false,
258 processProductionActionsIn => false,
259 processProductionRulesIn => true,
260 updateCurrentApproverListIn => false,
261 updateOldApproverListIn => false,
262 processPrioritiesIn => processPriorities,
263 prepareItemDataIn => false,
264 prepareRuleIdsIn => false,
265 prepareRuleDescsIn => false,
266 transactionIdIn => transactionIdIn,
267 ameApplicationIdIn => applicationIdIn,
268 fndApplicationIdIn => null,
269 transactionTypeIdIn => null);
270 ame_engine.getTestTransApplicableRules(ruleItemClassIdsOut => ruleItemClassIdsOut,
271 itemClassIdsOut => itemClassIdsOut,
272 itemIdsOut => itemIdsOut,
273 ruleIdsOut => ruleIdsOut,
274 ruleTypesOut => ruleTypesOut,
275 ruleDescriptionsOut => ruleDescriptionsOut);
276 exception
277 when others then
278 rollback;
279 ruleItemClassIdsOut := ame_util.emptyIdList;
280 itemClassIdsOut := ame_util.emptyIdList;
281 itemIdsOut := ame_util.emptyStringList;
282 ruleTypesOut := ame_util.emptyIdList;
283 ruleIdsOut := ame_util.emptyIdList;
284 ruleDescriptionsOut := ame_util.emptyStringList;
285 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
286 routineNameIn => 'getApplicableRules',
287 exceptionNumberIn => sqlcode,
288 exceptionStringIn => sqlerrm);
289 raise;
290 end getApplicableRules;
291 procedure getApproverAttributes(applicationIdIn in integer,
292 transactionIdIn in varchar2,
293 itemClassIdIn in integer,
294 itemIdIn in varchar2,
295 attributeIdsOut out nocopy ame_util.IdList,
296 attributeNamesOut out nocopy ame_util.stringList,
297 approverTypeIdsOut out nocopy ame_util.idList) as
298 cursor attributeCursor(applicationIdIn in integer,
299 transactionIdIn in varchar2,
300 itemClassIdIn in integer,
301 itemIdIn in varchar2) is
302 select
303 ame_attributes.attribute_id,
304 ame_attributes.name,
305 ame_attributes.approver_type_id
306 from ame_test_trans_att_values,
307 ame_attributes
308 where
309 ame_attributes.attribute_id = ame_test_trans_att_values.attribute_id and
310 ame_attributes.item_class_id = itemClassIdIn and
311 ame_test_trans_att_values.item_id = itemIdIn and
312 ame_test_trans_att_values.application_id = applicationIdIn and
313 ame_test_trans_att_values.transaction_id = transactionIdIn and
314 ame_attributes.approver_type_id is not null and
315 sysdate between ame_attributes.start_date and
316 nvl(ame_attributes.end_date - ame_util.oneSecond, sysdate)
317 order by ame_attributes.name;
318 begin
319 open attributeCursor(applicationIdIn => applicationIdIn,
320 transactionIdIn => transactionIdIn,
321 itemClassIdIn => itemClassIdIn,
322 itemIdIn => itemIdIn);
323 fetch attributeCursor bulk collect
324 into
325 attributeIdsOut,
326 attributeNamesOut,
327 approverTypeIdsOut;
328 close attributeCursor;
329 exception
330 when others then
331 attributeIdsOut := ame_util.emptyIdList;
332 attributeNamesOut := ame_util.emptyStringList;
333 approverTypeIdsOut := ame_util.emptyIdList;
334 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
335 routineNameIn => 'getApproverAttributes',
336 exceptionNumberIn => sqlcode,
337 exceptionStringIn => sqlerrm);
338 raise;
339 end getApproverAttributes;
340 procedure getApproverList(applicationIdIn in integer,
341 transactionIdIn in varchar2,
342 testOrRealTransTypeIn in varchar2,
343 approverListStageIn in integer,
344 approverListOut out nocopy ame_util.approversTable2,
345 productionIndexesOut out nocopy ame_util.idList,
346 variableNamesOut out nocopy ame_util.stringList,
347 variableValuesOut out nocopy ame_util.stringList,
348 doRepeatSubstitutionsOut out nocopy varchar2) as
349 errorCode integer;
350 errorMessage ame_util.longestStringType;
351 isTestTransaction boolean;
352 processProductionActions boolean;
353 processProductionRules boolean;
354 stageException exception;
355 tempConfigVarValue ame_util.stringType;
356 begin
357 if(approverListStageIn < 1 or approverListStageIn > 6) then
358 raise stageException;
359 end if;
360 ame_engine.getTestTransApprovers(isTestTransactionIn => testOrRealTransTypeIn = ame_util.testTrans,
361 transactionIdIn => transactionIdIn,
362 ameApplicationIdIn => applicationIdIn,
363 approverListStageIn => approverListStageIn,
364 approversOut => approverListOut,
365 productionIndexesOut => productionIndexesOut,
366 variableNamesOut => variableNamesOut,
367 variableValuesOut => variableValuesOut);
368 doRepeatSubstitutionsOut := ame_engine.getHeaderAttValue2(attributeNameIn=> ame_util.repeatSubstitutionsAttribute);
369 exception
370 when stageException then
371 errorCode := -20001;
372 errorMessage :=
373 ame_util.getMessage(applicationShortNameIn => 'PER',
374 messageNameIn =>'AME_400452_APPR_STAGE_INT');
375 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
376 routineNameIn => 'getApproverList',
377 exceptionNumberIn => errorCode,
378 exceptionStringIn => errorMessage);
379 raise_application_error(errorCode,
380 errorMessage);
381 when others then
382 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
383 routineNameIn => 'getApproverList',
384 exceptionNumberIn => sqlcode,
385 exceptionStringIn => sqlerrm);
386 approverListOut := ame_util.emptyApproversTable2;
387 raise;
388 end getApproverList;
389 procedure getItemAttributeValues(applicationIdIn in integer,
390 transactionIdIn in varchar2,
391 itemClassIdIn in integer,
392 itemIdIn in varchar2,
393 testOrRealTransTypeIn in varchar2,
394 attributeNamesOut out nocopy ame_util.stringList,
395 attributeTypesOut out nocopy ame_util.stringList,
396 attributeValuesOut1 out nocopy ame_util.attributeValueList,
397 attributeValuesOut2 out nocopy ame_util.attributeValueList,
398 attributeValuesOut3 out nocopy ame_util.attributeValueList) as
399 attributeIds ame_util.idList;
400 attributeNames ame_util.stringList;
401 attributeTypes ame_util.stringList;
402 isTestTransaction boolean;
403 outputIndex integer;
404 begin
405 ame_attribute_pkg.getSubordinateICAttributes2(applicationIdIn => applicationIdIn,
406 itemClassIdIn => itemClassIdIn,
407 attributeIdsOut => attributeIds,
408 attributeNamesOut => attributeNames,
409 attributeTypesOut => attributeTypes);
410 outputIndex := 0; /* pre-increment */
411 if(testOrRealTransTypeIn = ame_util.testTrans) then
412 isTestTransaction := true;
413 else
414 isTestTransaction := false;
415 end if;
416 ame_engine.updateTransactionState(isTestTransactionIn => isTestTransaction,
417 isLocalTransactionIn => true,
418 fetchConfigVarsIn => true,
419 fetchOldApproversIn => false,
420 fetchInsertionsIn => false,
421 fetchDeletionsIn => false,
422 fetchAttributeValuesIn => true,
423 fetchInactiveAttValuesIn => true,
424 processProductionActionsIn => false,
425 processProductionRulesIn => false,
426 updateCurrentApproverListIn => false,
427 updateOldApproverListIn => false,
428 processPrioritiesIn => false,
429 prepareItemDataIn => false,
430 prepareRuleIdsIn => false,
431 prepareRuleDescsIn => false,
432 transactionIdIn => transactionIdIn,
433 ameApplicationIdIn => applicationIdIn,
434 fndApplicationIdIn => null,
435 transactionTypeIdIn => null);
436 for i in 1 .. attributeIds.count loop
437 outputIndex := outputIndex + 1;
438 attributeNamesOut(outputIndex) := attributeNames(i);
439 attributeTypesOut(outputIndex) := attributeTypes(i);
440 ame_engine.getItemAttValues1(attributeIdIn => attributeIds(i),
441 itemIdIn => itemIdIn,
442 attributeValue1Out => attributeValuesOut1(outputIndex),
443 attributeValue2Out => attributeValuesOut2(outputIndex),
444 attributeValue3Out => attributeValuesOut3(outputIndex));
445 end loop;
446 exception
447 when others then
448 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
449 routineNameIn => 'getItemAttributeValues',
450 exceptionNumberIn => sqlcode,
451 exceptionStringIn => sqlerrm);
452 attributeNamesOut := ame_util.emptyStringList;
453 attributeValuesOut1 := ame_util.emptyAttributeValueList;
454 attributeValuesOut2 := ame_util.emptyAttributeValueList;
455 attributeValuesOut3 := ame_util.emptyAttributeValueList;
456 raise;
457 end getItemAttributeValues;
458 procedure getItemIds(applicationIdIn in integer,
459 transactionIdIn in varchar2,
460 itemClassIdIn in integer,
461 itemIdsOut out nocopy ame_util.stringList) as
462 cursor itemIdCursor(applicationIdIn in integer,
463 transactionIdIn in varchar2,
464 itemClassIdIn in integer) is
465 select distinct item_id
466 from ame_test_trans_att_values
467 where
468 application_id = applicationIdIn and
469 transaction_id = transactionIdIn and
470 item_class_id = itemClassIdIn and
471 item_id is not null
472 order by item_id;
473 begin
474 open itemIdCursor(applicationIdIn => applicationIdIn,
475 transactionIdIn => transactionIdIn,
476 itemClassIdIn => itemClassIdIn);
477 fetch itemIdCursor bulk collect
478 into itemIdsOut;
479 close itemIdCursor;
480 exception
481 when others then
482 rollback;
483 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
484 routineNameIn => 'getitemIds',
485 exceptionNumberIn => sqlcode,
486 exceptionStringIn => sqlerrm);
487 itemIdsOut := ame_util.emptyStringList;
488 raise;
489 end getitemIds;
490 procedure getTransactionProductions(applicationIdIn in integer,
491 transactionIdIn in varchar2,
492 testOrRealTransTypeIn in varchar2,
493 variableNamesOut out nocopy ame_util.stringList,
494 variableValuesOut out nocopy ame_util.stringList) as
495 isTestTransaction boolean;
496 begin
497 if(testOrRealTransTypeIn = ame_util.testTrans) then
498 isTestTransaction := true;
499 else
500 isTestTransaction := false;
501 end if;
502 ame_engine.updateTransactionState(isTestTransactionIn => isTestTransaction,
503 isLocalTransactionIn => true,
504 fetchConfigVarsIn => true,
505 fetchOldApproversIn => false,
506 fetchInsertionsIn => false,
507 fetchDeletionsIn => false,
508 fetchAttributeValuesIn => true,
509 fetchInactiveAttValuesIn => false,
510 processProductionActionsIn => false,
511 processProductionRulesIn => true,
512 updateCurrentApproverListIn => false,
513 updateOldApproverListIn => false,
514 processPrioritiesIn => false,
515 prepareItemDataIn => false,
516 prepareRuleIdsIn => false,
517 prepareRuleDescsIn => false,
518 transactionIdIn => transactionIdIn,
519 ameApplicationIdIn => applicationIdIn,
520 fndApplicationIdIn => null,
521 transactionTypeIdIn => null);
522 ame_engine.getTransVariableNames(transVariableNamesOut => variableNamesOut);
523 ame_engine.getTransVariableValues(transVariableValuesOut => variableValuesOut);
524 exception
525 when others then
526 rollback;
527 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
528 routineNameIn => 'getTransactionProductions',
529 exceptionNumberIn => sqlcode,
530 exceptionStringIn => sqlerrm);
531 variableNamesOut := ame_util.emptyStringList;
532 variableValuesOut := ame_util.emptyStringList;
533 raise;
534 end getTransactionProductions;
535 procedure initializeTestTrans(applicationIdIn in integer,
536 transactionIdIn in varchar2,
537 itemClassIdIn in integer default null,
538 isHeaderItemClassIn in boolean default true,
539 itemIdIn in varchar2 default null) as
540 attributeIds ame_util.idList;
541 attributeNames ame_util.stringList;
542 attributeTypes ame_util.stringList;
543 errorCode integer;
544 errorMessage ame_util.longestStringType;
545 isLineItem boolean;
546 isMandatory ame_util.charType;
547 isNonHeaderAttributeItem boolean;
548 itemClassId integer;
549 noAttributesException exception;
550 upperLimit integer;
551 begin
552 if(isHeaderItemClassIn) then
553 ame_attribute_pkg.getActiveHeaderAttributes(applicationIdIn => applicationIdIn,
554 attributeIdsOut => attributeIds,
555 attributeNamesOut => attributeNames);
556 itemClassId :=
557 ame_admin_pkg.getItemClassIdByName(itemClassNameIn => ame_util.headerItemClassName);
558 else
559 ame_attribute_pkg.getNonHeaderICAttributes2(applicationIdIn => applicationIdIn,
560 itemClassIdIn => itemClassIdIn,
561 attributeIdsOut => attributeIds,
562 attributeNamesOut => attributeNames);
563 itemClassId := itemClassIdIn;
564 end if;
565 if(attributeIds.count = 0) then
566 raise noAttributesException;
567 end if;
568 upperLimit := attributeIds.count;
569 for i in 1 .. upperLimit loop
570 if(attributeNames(i) <> ame_util.workflowItemKeyAttribute and
571 attributeNames(i) <> ame_util.workflowItemTypeAttribute) then
572 attributeTypes(i) := ame_attribute_pkg.getType(attributeIdIn => attributeIds(i));
573 if(ame_attribute_pkg.isMandatory(attributeIdIn => attributeIds(i))) then
574 isMandatory := ame_util.booleanTrue;
575 else
576 isMandatory := ame_util.booleanFalse;
577 end if;
578 insert into ame_test_trans_att_values(
579 application_id,
580 transaction_id,
581 row_timestamp,
582 attribute_id,
583 attribute_name,
584 attribute_type,
585 is_mandatory,
586 line_item_id,
587 item_id,
588 item_class_id,
589 attribute_value_1,
590 attribute_value_2,
591 attribute_value_3) values(
592 applicationIdIn,
593 transactionIdIn,
594 sysdate,
595 attributeIds(i),
596 attributeNames(i),
597 attributeTypes(i),
598 isMandatory,
599 null,
600 nvl(itemIdIn, transactionIdIn),
601 itemClassId,
602 null,
603 null,
604 null);
605 commit;
606 end if;
607 end loop;
608 exception
609 when noAttributesException then
610 errorCode := -20001;
611 errorMessage :=
612 ame_util.getMessage(applicationShortNameIn => 'PER',
613 messageNameIn =>'AME_400443_TEST_NO_AU_EXST');
614 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
615 routineNameIn => 'initializeTestTrans',
616 exceptionNumberIn => errorCode,
617 exceptionStringIn => errorMessage);
618 raise_application_error(errorCode,
619 errorMessage);
620 when others then
621 rollback;
622 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
623 routineNameIn => 'initializeTestTrans',
624 exceptionNumberIn => sqlcode,
625 exceptionStringIn => sqlerrm);
626 raise;
627 end initializeTestTrans;
628 procedure setAllAttributeValues(applicationIdIn in integer,
629 transactionIdIn in varchar2,
630 itemClassIdIn in integer,
631 attributeIdsIn in ame_util.idList,
632 itemIdIn in varchar2 default null,
633 attributeValues1In in ame_util.attributeValueList,
634 attributeValues2In in ame_util.attributeValueList,
635 attributeValues3In in ame_util.attributeValueList) as
636 attributeNames ame_util.stringList;
637 attributeTypes ame_util.stringList;
638 isMandatory ame_util.stringList;
639 upperLimit integer;
640 begin
641 delete from ame_test_trans_att_values
642 where
643 application_id = applicationIdIn and
644 transaction_id = transactionIdIn and
645 item_class_id = itemClassIdIn and
646 ((itemIdIn is null and item_id is null) or
647 itemIdIn = item_id);
648 commit;
649 upperLimit := attributeIdsIn.count;
650 for i in 1 .. upperLimit loop
651 attributeNames(i) := ame_attribute_pkg.getName(attributeIdIn => attributeIdsIn(i));
652 attributeTypes(i) := ame_attribute_pkg.getType(attributeIdIn => attributeIdsIn(i));
653 if(ame_attribute_pkg.isMandatory(attributeIdIn => attributeIdsIn(i))) then
654 isMandatory(i) := ame_util.booleanTrue;
655 else
656 isMandatory(i) := ame_util.booleanFalse;
657 end if;
658 end loop;
659 forall i in 1 .. upperLimit
660 insert into ame_test_trans_att_values(
661 application_id,
662 transaction_id,
663 row_timestamp,
664 attribute_id,
665 attribute_name,
666 attribute_type,
667 is_mandatory,
668 line_item_id,
669 attribute_value_1,
670 attribute_value_2,
671 attribute_value_3,
672 item_class_id,
673 item_id)
674 values(
675 applicationIdIn,
676 transactionIdIn,
677 sysdate,
678 attributeIdsIn(i),
679 attributeNames(i),
680 attributeTypes(i),
681 isMandatory(i),
682 null,
683 attributeValues1In(i),
684 attributeValues2In(i),
685 attributeValues3In(i),
686 itemClassIdIn,
687 itemIdIn);
688 commit;
689 exception
690 when others then
691 rollback;
692 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
693 routineNameIn => 'setAllAttributeValues',
694 exceptionNumberIn => sqlcode,
695 exceptionStringIn => sqlerrm);
696 raise;
697 end setAllAttributeValues;
698 procedure setAttributeValues(applicationIdIn in integer,
699 transactionIdIn in varchar2,
700 itemClassIdIn in integer,
701 itemIdIn in varchar2,
702 attributeIdIn in integer,
703 attributeValue1In in varchar2,
704 attributeValue2In in varchar2 default null,
705 attributeValue3In in varchar2 default null) as
706 attributeName ame_attributes.name%type;
707 attributeType ame_attributes.attribute_Type%type;
708 isMandatory ame_test_trans_att_values.is_mandatory%type;
709 begin
710 delete from ame_test_trans_att_values
711 where
712 application_id = applicationIdIn and
713 transaction_id = transactionIdIn and
714 attribute_id = attributeIdIn and
715 item_class_id = itemClassIdIn and
716 itemIdIn = item_id;
717 commit;
718 if(ame_attribute_pkg.isMandatory(attributeIdIn => attributeIdIn)) then
719 isMandatory := ame_util.booleanTrue;
720 else
721 isMandatory := ame_util.booleanFalse;
722 end if;
723 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
724 attributeType := ame_attribute_pkg.getType(attributeIdIn => attributeIdIn);
725 insert into ame_test_trans_att_values(
726 application_id,
727 transaction_id,
728 row_timestamp,
729 attribute_id,
730 attribute_name,
731 attribute_type,
732 is_mandatory,
733 line_item_id,
734 item_id,
735 item_class_id,
736 attribute_value_1,
737 attribute_value_2,
738 attribute_value_3)
739 values(
740 applicationIdIn,
741 transactionIdIn,
742 sysdate,
743 attributeIdIn,
744 attributeName,
745 attributeType,
746 isMandatory,
747 null,
748 itemIdIn,
749 itemClassIdIn,
750 attributeValue1In,
751 attributeValue2In,
752 attributeValue3In);
753 commit;
754 exception
755 when others then
756 rollback;
757 ame_util.runtimeException(packageNameIn => 'ame_test_pkg',
758 routineNameIn => 'setAttributeValues',
759 exceptionNumberIn => sqlcode,
760 exceptionStringIn => sqlerrm);
761 raise;
762 end setAttributeValues;
763 end ame_test_pkg;