[Home] [Help]
PACKAGE BODY: APPS.AME_UTIL
Source
1 package body ame_util as
2 /* $Header: ameoutil.pkb 120.4.12010000.3 2008/08/05 05:15:31 ubhat ship $ */
3 /* forward declarations */
4 procedure autonomousLog(logIdIn number,
5 packageNameIn in varchar2,
6 routineNameIn in varchar2,
7 exceptionNumberIn in integer,
8 exceptionStringIn in varchar2,
9 transactionIdIn in varchar2 default null,
10 applicationIdIn in integer default null);
11 procedure nonautonomousLog(logIdIn number,
12 packageNameIn in varchar2,
13 routineNameIn in varchar2,
14 exceptionNumberIn in integer,
15 exceptionStringIn in varchar2,
16 transactionIdIn in varchar2 default null,
17 applicationIdIn in integer default null);
18 /* routine definitions */
19 function canonNumStringToDisplayString(canonicalNumberStringIn in varchar2,
20 currencyCodeIn in varchar2 default null) return varchar2 as
21 begin
22 if(currencyCodeIn is null) then
23 /* It would be nice to be able to format this string with the right decimal character. */
24 return(canonicalNumberStringIn);
25 else
26 return(hr_chkfmt.changeformat(input => canonicalNumberStringIn,
27 format => 'M',
28 curcode => currencyCodeIn));
29 end if;
30 exception
31 when others then
32 runtimeException(packageNameIn => 'ame_util',
33 routineNameIn => 'canonNumStringToDisplayString',
34 exceptionNumberIn => sqlcode,
35 exceptionStringIn => sqlerrm);
36 raise;
37 return(null);
38 end canonNumStringToDisplayString;
39 function convertCurrency(fromCurrencyCodeIn in varchar2,
40 toCurrencyCodeIn in varchar2,
41 conversionTypeIn in varchar2,
42 amountIn in number,
43 dateIn in date default sysdate,
44 applicationIdIn in integer default null) return number as
45 amount number;
46 denominator number;
47 errorCode integer;
48 errorMessage ame_util.longestStringType;
49 numerator number;
50 rate number;
51 begin
52 amount := amountIn;
53 denominator := null;
54 numerator := null;
55 rate := null;
56 gl_currency_api.convert_closest_amount(x_from_currency => fromCurrencyCodeIn,
57 x_to_currency => toCurrencyCodeIn,
58 x_conversion_date => trunc(dateIn),
59 x_conversion_type => conversionTypeIn,
60 x_user_rate => null,
61 x_amount => amountIn,
62 x_max_roll_days => getConfigVar(variableNameIn => curConvWindowConfigVar,
63 applicationIdIn => applicationIdIn),
64 x_converted_amount => amount,
65 x_denominator => denominator,
66 x_numerator => numerator,
67 x_rate => rate);
68 return(amount);
69 exception
70 when gl_currency_api.INVALID_CURRENCY then
71 errorCode := -20001;
72 errorMessage :=
73 ame_util.getMessage(applicationShortNameIn => 'PER',
74 messageNameIn => 'AME_400217_UTL_CURR_INVALID',
75 tokenNameOneIn => 'FROM_CURRENCY_CODE',
76 tokenValueOneIn => fromCurrencyCodeIn,
77 tokenNameTwoIn => 'TO_CURRENCY_CODE',
78 tokenValueTwoIn => toCurrencyCodeIn,
79 tokenNameThreeIn => 'CONVERSION_TYPE',
80 tokenValueThreeIn => conversionTypeIn,
81 tokenNameFourIn => 'DATE_IN',
82 tokenValueFourIn => dateIn);
83 runtimeException(packageNameIn => 'ame_util',
84 routineNameIn => 'convertCurrency',
85 exceptionNumberIn => errorCode,
86 exceptionStringIn => errorMessage);
87 raise_application_error(errorCode,
88 errorMessage);
89 return(null);
90 when gl_currency_api.NO_RATE then
91 errorCode := -20001;
92 errorMessage :=
93 ame_util.getMessage(applicationShortNameIn => 'PER',
94 messageNameIn => 'AME_400294_UTL_NO_RAT_EXISTS',
95 tokenNameOneIn => 'FROM_CURRENCY_CODE',
96 tokenValueOneIn => fromCurrencyCodeIn,
97 tokenNameTwoIn => 'TO_CURRENCY_CODE',
98 tokenValueTwoIn => toCurrencyCodeIn,
99 tokenNameThreeIn => 'CONVERSION_TYPE',
100 tokenValueThreeIn => conversionTypeIn,
101 tokenNameFourIn => 'DATE_IN',
102 tokenValueFourIn => dateIn);
103 raise_application_error(errorCode,
104 errorMessage);
105 return(null);
106 when gl_currency_api.NO_DERIVE_TYPE then
107 errorCode := -20001;
108 errorMessage :=
109 ame_util.getMessage(applicationShortNameIn => 'PER',
110 messageNameIn => 'AME_400295_UTL_NO_DERIVE_TYPE',
111 tokenNameOneIn => 'FROM_CURRENCY_CODE',
112 tokenValueOneIn => fromCurrencyCodeIn,
113 tokenNameTwoIn => 'TO_CURRENCY_CODE',
114 tokenValueTwoIn => toCurrencyCodeIn,
115 tokenNameThreeIn => 'CONVERSION_TYPE',
116 tokenValueThreeIn => conversionTypeIn,
117 tokenNameFourIn => 'DATE_IN',
118 tokenValueFourIn => dateIn);
119 raise_application_error(errorCode,
120 errorMessage);
121 return(null);
122 when others then
123 errorCode := -20001;
124 errorMessage :=
125 ame_util.getMessage(applicationShortNameIn => 'PER',
126 messageNameIn => 'AME_400218_UTL_NOT_CONV_CURR',
127 tokenNameOneIn => 'FROM_CURRENCY_CODE',
128 tokenValueOneIn => fromCurrencyCodeIn,
129 tokenNameTwoIn => 'TO_CURRENCY_CODE',
130 tokenValueTwoIn => toCurrencyCodeIn,
131 tokenNameThreeIn => 'CONVERSION_TYPE',
132 tokenValueThreeIn => conversionTypeIn,
133 tokenNameFourIn => 'DATE_IN',
134 tokenValueFourIn => dateIn,
135 tokenNameFiveIn => 'SQLERRM',
136 tokenValueFiveIn => sqlerrm);
137 runtimeException(packageNameIn => 'ame_util',
138 routineNameIn => 'convertCurrency',
139 exceptionNumberIn => errorCode,
140 exceptionStringIn => errorMessage);
141 raise_application_error(errorCode,
142 errorMessage);
143 return(null);
144 end convertCurrency;
145 function dateStringsToString(yearIn in varchar2,
146 monthIn in varchar2,
147 dayIn in varchar2) return varchar2 as
148 begin
149 if(yearIn is null or
150 monthIn is null or
151 dayIn is null) then
152 return(null);
153 end if;
154 return(versionDateToString(dateIn => to_date(yearIn || ':' || monthIn || ':' || dayIn, 'YYYY:MM:DD')));
155 exception
156 when others then
157 runtimeException(packageNameIn => 'ame_util',
158 routineNameIn => 'dateStringsToString',
159 exceptionNumberIn => sqlcode,
160 exceptionStringIn => sqlerrm);
161 raise;
162 return(null);
163 end dateStringsToString;
164 function fieldDelimiter return varchar2 as
165 begin
166 /* 11 is a vertical tab in the ASCII character set. */
167 -- This function will replace getAdminPersonIdIn and getAdminUserIdIn
168 --
169 return(fnd_global.local_chr(ascii_chr => 11));
170 end fieldDelimiter;
171 function filterHtmlUponInput(stringIn in varchar2) return varchar2 as
172 errorCode integer;
173 errorMessage ame_util.longestStringType;
174 invalidStringException exception;
175 string ame_util.longStringType;
176 begin
177 if(upper(stringIn) like '%<SCRIPT>%') then
178 raise invalidStringException;
179 end if;
180 string := wf_notification.substituteSpecialChars(stringIn);
181 return(string);
182 exception
183 when invalidStringException then
184 errorCode := -20001;
185 errorMessage :=
186 ame_util.getMessage(applicationShortNameIn => 'PER',
187 messageNameIn => 'AME_400456_NO_SCRIPT_TAG');
188 runtimeException(packageNameIn => 'ame_util',
189 routineNameIn => 'filterHtmlUponInput',
190 exceptionNumberIn => errorCode,
191 exceptionStringIn => errorMessage);
192 raise_application_error(errorCode,
193 errorMessage);
194 return(null);
195 when others then
196 runtimeException(packageNameIn => 'ame_util',
197 routineNameIn => 'filterHtmlUponInput',
198 exceptionNumberIn => sqlcode,
199 exceptionStringIn => sqlerrm);
200 raise;
201 return(null);
202 end filterHtmlUponInput;
203 function filterHtmlUponRendering(stringIn in varchar2) return varchar2 as
204 errorCode integer;
205 errorMessage ame_util.longestStringType;
206 invalidStringException exception;
207 string ame_util.longStringType;
208 begin
209 if(upper(string) like '%<SCRIPT>%') then
210 raise invalidStringException;
211 end if;
212 return(stringIn);
213 exception
214 when invalidStringException then
215 errorCode := -20001;
216 errorMessage :=
217 ame_util.getMessage(applicationShortNameIn => 'PER',
218 messageNameIn => 'AME_400456_NO_SCRIPT_TAG');
219 runtimeException(packageNameIn => 'ame_util',
220 routineNameIn => 'filterHtmlUponRendering',
221 exceptionNumberIn => errorCode,
222 exceptionStringIn => errorMessage);
223 raise_application_error(errorCode,
224 errorMessage);
225 return(null);
226 when others then
227 runtimeException(packageNameIn => 'ame_util',
228 routineNameIn => 'filterHtmlUponRendering',
229 exceptionNumberIn => sqlcode,
230 exceptionStringIn => sqlerrm);
231 raise;
232 return(null);
233 end filterHtmlUponRendering;
234 function escapeSpaceChars(stringIn in varchar2) return varchar2 as
235 begin
236 return(replace(stringIn, ' ', ' '));
237 exception
238 when others then
239 runtimeException(packageNameIn => 'ame_util',
240 routineNameIn => 'escapeSpaceChars',
241 exceptionNumberIn => sqlcode,
242 exceptionStringIn => sqlerrm);
243 raise;
244 return(null);
245 end escapeSpaceChars;
246 function getAdminName(applicationIdIn in integer default null) return varchar2 is
247 badAdminApproverException exception;
248 commaLocation integer;
249 adminName ame_config_vars.variable_value%type;
250 configVarLength integer;
251 errorCode integer;
252 errorMessage ame_util.longestStringType;
253 begin
254 select variable_value
255 into adminName
256 from ame_config_vars
257 where variable_name = ame_util.adminApproverConfigVar and
258 application_id = applicationIdIn and
259 sysdate between start_date and
260 nvl(end_date - ame_util.oneSecond, sysdate) ;
261 -- If no transaction-type-specific config var exists, revert to the
262 -- application-wide value.
263 return(adminName);
264 exception
265 when no_data_found then
266 select variable_value
267 into adminName
268 from ame_config_vars
269 where variable_name = ame_util.adminApproverConfigVar and
270 (application_id is null or application_id = 0) and
271 sysdate between start_date and
272 nvl(end_date - ame_util.oneSecond, sysdate) ;
273 return(adminName);
274 when others then
275 runtimeException(packageNameIn => 'ame_util',
276 routineNameIn => 'getAdminName',
277 exceptionNumberIn => sqlcode,
278 exceptionStringIn => sqlerrm);
279 raise;
280 return(null);
281 end getAdminName;
282 function getCarriageReturn return varchar2 as
283 begin
284 return(fnd_global.local_chr(13)); /* ASCII character 13 is a carriage return. */
285 exception
286 when others then
287 runtimeException(packageNameIn => 'ame_util',
288 routineNameIn => 'getCarriageReturn',
289 exceptionNumberIn => sqlcode,
290 exceptionStringIn => sqlerrm);
291 raise;
292 return(null);
293 end getCarriageReturn;
294 function getContactAdminString(applicationIdIn in integer default null) return varchar2 as
295 adminString varchar2(4000);
296 adminName varchar2(4000);
297 badContactApproverException exception;
298 errorCode integer;
299 errorMessage ame_util.longestStringType;
300 begin
301 adminName := getAdminName(applicationIdIn => applicationIdIn);
302 if(adminName is null) then
303 raise badContactApproverException;
304 end if; -- pa message
305 adminString := ame_approver_type_pkg.getApproverDescription(nameIn => adminName);
306 return('If the problem persists, please contact ' || adminString || '. ');
307 exception
308 when badContactApproverException then
309 errorCode := -20001;
310 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
311 messageNameIn => 'AME_400220_UTL_NO_ADMIN_APR');
312 runtimeException(packageNameIn => 'ame_util',
313 routineNameIn => 'getContactAdminString',
314 exceptionNumberIn => errorCode,
315 exceptionStringIn => errorMessage);
316 raise_application_error(errorCode,
317 errorMessage);
318 return(null);
319 when others then
320 runtimeException(packageNameIn => 'ame_util',
321 routineNameIn => 'getContactAdminString',
322 exceptionNumberIn => sqlcode,
323 exceptionStringIn => sqlerrm);
324 raise;
325 return(null);
326 end getContactAdminString;
327 /*
328 AME_STRIPING
329 function getCurrentStripeSetId(applicationIdIn in integer) return integer as
330 stripeSetCookie owa_cookie.cookie;
331 begin
332 stripeSetCookie := owa_cookie.get(getStripeSetCookieName(applicationIdIn => applicationIdIn));
333 return(stripeSetCookie.vals(1));
334 exception
335 when no_data_found then
336 return(null);
337 when others then
338 runtimeException(packageNameIn => 'ame_util',
339 routineNameIn => 'getCurrentStripeSetId',
340 exceptionNumberIn => sqlcode,
341 exceptionStringIn => sqlerrm);
342 raise;
343 return(null);
344 end getCurrentStripeSetId;
345 */
346 function getCurrentUserId return integer is
347 userId fnd_user.user_id%type;
348 begin
349 userId := fnd_global.user_id;
350 return(userId);
351 exception
352 when others then
353 runtimeException(packageNameIn => 'ame_util',
354 routineNameIn => 'getCurrentUserId',
355 exceptionNumberIn => sqlcode,
356 exceptionStringIn => sqlerrm);
357 raise;
358 return(null);
359 end getCurrentUserId;
360 function getColumnLength(tableNameIn in varchar2,
361 columnNameIn in varchar2,
362 fndApplicationIdIn in integer default 800) return integer as
363 columnLength integer;
364 begin
365 select width
366 into columnLength
367 from fnd_columns
368 where
369 table_id =
370 (select table_id
371 from fnd_tables
372 where
373 table_name = upper(tableNameIn) and
374 application_id = fndApplicationIdIn) and
375 application_id = fndApplicationIdIn and
376 column_name = upper(columnNameIn);
377 return(columnLength);
378 exception
379 when others then
380 runtimeException(packageNameIn => 'ame_util',
381 routineNameIn => 'getColumnLength',
382 exceptionNumberIn => sqlcode,
383 exceptionStringIn => sqlerrm);
384 raise;
385 return(null);
386 end getColumnLength;
387 function getConfigDesc(variableNameIn in varchar2) return varchar2 as
388 description ame_config_vars.description%type;
389 begin
390 select description
391 into description
392 from ame_config_vars
393 where
394 variable_name = variableNameIn and
395 (application_id is null or application_id = 0) and
396 sysdate between start_date and
397 nvl(end_date - ame_util.oneSecond, sysdate) ;
398 return(description);
399 exception
400 when others then
401 runtimeException(packageNameIn => 'ame_util',
402 routineNameIn => 'getConfigDesc',
403 exceptionNumberIn => sqlcode,
404 exceptionStringIn => sqlerrm);
405 raise;
406 return(null);
407 end getConfigDesc;
408 function getConfigVar(variableNameIn in varchar2,
409 applicationIdIn in integer default null) return varchar2 as
410 variableValue ame_config_vars.variable_value%type;
411 begin
412 if(applicationIdIn is null) then
413 select variable_value
414 into variableValue
415 from ame_config_vars
416 where
417 variable_name = variableNameIn and
418 (application_id is null or application_id = 0) and
419 sysdate between start_date and
420 nvl(end_date - ame_util.oneSecond, sysdate) ;
421 else
422 begin
423 select variable_value
424 into variableValue
425 from ame_config_vars
426 where
427 variable_name = variableNameIn and
428 application_id = applicationIdIn and
429 sysdate between start_date and
430 nvl(end_date - ame_util.oneSecond, sysdate) ;
431 exception
432 /* If no transaction-type-specific config var exists, revert to the application-wide value. */
433 when no_data_found then
434 select variable_value
435 into variableValue
436 from ame_config_vars
437 where
438 variable_name = variableNameIn and
439 (application_id is null or application_id = 0) and
440 sysdate between start_date and
441 nvl(end_date - ame_util.oneSecond, sysdate) ;
442 end;
443 end if;
444 return(variableValue);
445 exception
446 when others then
447 /*
448 Bug 2219719: Do not call runtimeException here; it could result in infinite looping,
449 because runtimeException now calls getConfig var.
450 */
451 raise;
452 return(null);
453 end getConfigVar;
454 function getCurrencyName(currencyCodeIn in varchar2) return varchar2 as
455 returnValue fnd_currencies_active_v.name%type;
456 begin
457 select name
458 into returnValue
459 from fnd_currencies_active_v
460 where currency_code = currencyCodeIn;
461 return(currencyCodeIn || ' (' || returnValue || ')');
462 exception
463 when others then
464 runtimeException(packageNameIn => 'ame_util',
465 routineNameIn => 'getCurrencyName',
466 exceptionNumberIn => sqlcode,
467 exceptionStringIn => sqlerrm);
468 raise;
469 return(currencyCodeIn);
470 end getCurrencyName;
471 function getBusGroupName(busGroupIdIn in integer) return varchar2 as
472 tempName per_business_groups.name%type;
473 begin
474 if(busGroupIdIn is null) then
475 return(null);
476 end if;
477 select name
478 into tempName
479 from per_business_groups
480 where
481 business_group_id = busGroupIdIn and
482 sysdate >= date_from and
483 (date_to is null or sysdate < date_to);
484 return(tempName);
485 exception
486 when others then
487 runtimeException(packageNameIn => 'ame_util',
488 routineNameIn => 'getBusGroupName',
489 exceptionNumberIn => sqlcode,
490 exceptionStringIn => sqlerrm);
491 raise;
492 return(null);
493 end getBusGroupName;
494 function getDayString(dateIn in date) return varchar2 as
495 begin
496 return(to_char(dateIn, 'DD'));
497 exception
498 when others then
499 runtimeException(packageNameIn => 'ame_util',
500 routineNameIn => 'getDayString',
501 exceptionNumberIn => sqlcode,
502 exceptionStringIn => sqlerrm);
503 raise;
504 return(null);
505 end getDayString;
506 function getHighestResponsibility return integer as
507 cursor getResponsibilityCur(userId in varchar2) is
508 select a.responsibility_key
509 from FND_SECURITY_GROUPS_VL fsg,
510 fnd_responsibility_vl a,
511 FND_USER_RESP_GROUPS b
512 where b.user_id = userId and
513 b.start_date <= sysdate and
514 (b.end_date is null or b.end_date > sysdate) and
515 b.RESPONSIBILITY_id = a.responsibility_id and
516 b.RESPONSIBILITY_application_id = a.application_id and
517 a.version in ('W','4') and
518 a.start_date <= sysdate and
519 (a.end_date is null or a.end_date > sysdate) and
520 b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID
521 order by a.responsibility_key;
522 highestResponsibility integer;
523 userId fnd_user.user_id%type;
524 begin
525 userId := fnd_global.user_id;
526 highestResponsibility := ame_util.noResponsibility;
527 for getResponsibilityRec in getResponsibilityCur(userId => userId) loop
528 if(getResponsibilityRec.responsibility_key = ame_util.devRespKey) then
529 return(ame_util.developerResponsibility);
530 elsif(getResponsibilityRec.responsibility_key = ame_util.appAdminRespKey and
531 highestResponsibility < ame_util.appAdminResponsibility) then
532 highestResponsibility := ame_util.appAdminResponsibility;
533 elsif(getResponsibilityRec.responsibility_key = ame_util.genBusUserRespKey and
534 highestResponsibility < ame_util.genBusResponsibility) then
535 highestResponsibility := ame_util.genBusResponsibility;
536 elsif(getResponsibilityRec.responsibility_key = ame_util.limBusUserRespKey and
537 highestResponsibility < ame_util.limBusResponsibility) then
538 highestResponsibility := ame_util.limBusResponsibility;
539 elsif(getResponsibilityRec.responsibility_key = ame_util.readOnlyUserRespKey and
540 highestResponsibility < ame_util.readOnlyResponsibility) then
541 highestResponsibility := ame_util.readOnlyResponsibility;
542 end if;
543 end loop;
544 return(highestResponsibility);
545 exception
546 when others then
547 runtimeException(packageNameIn => 'ame_util',
548 routineNameIn => 'getHighestResponsibility',
549 exceptionNumberIn => sqlcode,
550 exceptionStringIn => sqlerrm);
551 raise;
552 return(ame_util.noResponsibility);
553 end getHighestResponsibility;
554 function getLabel(attributeApplicationIdIn in number,
555 attributeCodeIn in varchar2,
556 returnColonAndSpaces in boolean default false) return varchar2 as
557 attributeLabelOut varchar2(80);
558 begin
559 select attribute_label_long
560 into attributeLabelOut
561 from ak_attributes_vl
562 where attribute_code = attributeCodeIn
563 and attribute_application_id = attributeApplicationIdIn;
564 if(returnColonAndSpaces) then
565 return(attributeLabelOut || ': ');
566 else
567 return(attributeLabelOut);
568 end if;
569 exception
570 when others then
571 runtimeException(packageNameIn => 'ame_util',
572 routineNameIn => 'getLabel',
573 exceptionNumberIn => sqlcode,
574 exceptionStringIn => sqlerrm);
575 raise;
576 return(null);
577 end getLabel;
578 function getLineFeed return varchar2 as
579 begin
580 return(fnd_global.local_chr(10)); /* ASCII character 10 is a line feed. */
581 exception
582 when others then
583 runtimeException(packageNameIn => 'ame_util',
584 routineNameIn => 'getLineFeed',
585 exceptionNumberIn => sqlcode,
586 exceptionStringIn => sqlerrm);
587 raise;
588 return(null);
589 end getLineFeed;
590 function getLongBoilerplate(applicationShortNameIn in varchar2,
591 messageNameIn in varchar2,
592 tokenNameOneIn in varchar2 default null,
593 tokenValueOneIn in varchar2 default null,
594 tokenNameTwoIn in varchar2 default null,
595 tokenValueTwoIn in varchar2 default null,
596 tokenNameThreeIn in varchar2 default null,
597 tokenValueThreeIn in varchar2 default null,
598 tokenNameFourIn in varchar2 default null,
599 tokenValueFourIn in varchar2 default null,
600 tokenNameFiveIn in varchar2 default null,
601 tokenValueFiveIn in varchar2 default null,
602 tokenNameSixIn in varchar2 default null,
603 tokenValueSixIn in varchar2 default null,
604 tokenNameSevenIn in varchar2 default null,
605 tokenValueSevenIn in varchar2 default null,
606 tokenNameEightIn in varchar2 default null,
607 tokenValueEightIn in varchar2 default null,
608 tokenNameNineIn in varchar2 default null,
609 tokenValueNineIn in varchar2 default null,
610 tokenNameTenIn in varchar2 default null,
611 tokenValueTenIn in varchar2 default null) return varchar2 as
612 boilerplateLabel ame_util.longBoilerplateType;
613 begin
614 /* The ame_util.longBoilerplateType is defined as a varchar2(300). */
615 boilerplateLabel :=
616 substrb(ame_util.getMessage(applicationShortNameIn => applicationShortNameIn,
617 messageNameIn => messageNameIn,
618 tokenNameOneIn => tokenNameOneIn,
619 tokenValueOneIn => tokenValueOneIn,
620 tokenNameTwoIn => tokenNameTwoIn,
621 tokenValueTwoIn => tokenValueTwoIn,
622 tokenNameThreeIn => tokenNameThreeIn,
623 tokenValueThreeIn => tokenValueThreeIn,
624 tokenNameFourIn => tokenNameFourIn,
625 tokenValueFourIn => tokenValueFourIn,
626 tokenNameFiveIn => tokenNameFiveIn,
627 tokenValueFiveIn => tokenValueFiveIn,
628 tokenNameSixIn => tokenNameSixIn,
629 tokenValueSixIn => tokenValueSixIn,
630 tokenNameSevenIn => tokenNameSevenIn,
631 tokenValueSevenIn => tokenValueSevenIn,
632 tokenNameEightIn => tokenNameEightIn,
633 tokenValueEightIn => tokenValueEightIn,
634 tokenNameNineIn => tokenNameNineIn,
635 tokenValueNineIn => tokenValueNineIn,
636 tokenNameTenIn => tokenNameTenIn,
637 tokenValueTenIn => tokenValueTenIn), 1, 300);
638 return(boilerplateLabel);
639 exception
640 when others then
641 runtimeException(packageNameIn => 'ame_util',
642 routineNameIn => 'getLongBoilerplate',
643 exceptionNumberIn => sqlcode,
644 exceptionStringIn => sqlerrm);
645 raise;
646 return(null);
647 end getLongBoilerplate;
648 function getMessage(applicationShortNameIn in varchar2,
649 messageNameIn in varchar2,
650 tokenNameOneIn in varchar2 default null,
651 tokenValueOneIn in varchar2 default null,
652 tokenNameTwoIn in varchar2 default null,
653 tokenValueTwoIn in varchar2 default null,
654 tokenNameThreeIn in varchar2 default null,
655 tokenValueThreeIn in varchar2 default null,
656 tokenNameFourIn in varchar2 default null,
657 tokenValueFourIn in varchar2 default null,
658 tokenNameFiveIn in varchar2 default null,
659 tokenValueFiveIn in varchar2 default null,
660 tokenNameSixIn in varchar2 default null,
661 tokenValueSixIn in varchar2 default null,
662 tokenNameSevenIn in varchar2 default null,
663 tokenValueSevenIn in varchar2 default null,
664 tokenNameEightIn in varchar2 default null,
665 tokenValueEightIn in varchar2 default null,
666 tokenNameNineIn in varchar2 default null,
667 tokenValueNineIn in varchar2 default null,
668 tokenNameTenIn in varchar2 default null,
669 tokenValueTenIn in varchar2 default null) return varchar2 as
670 begin
671 fnd_message.set_name(applicationShortNameIn, messageNameIn);
672 if (tokenNameOneIn is not null) then
673 fnd_message.set_token(tokenNameOneIn, tokenValueOneIn);
674 end if;
675 if (tokenNameTwoIn is not null) then
676 fnd_message.set_token(tokenNameTwoIn, tokenValueTwoIn);
677 end if;
678 if (tokenNameThreeIn is not null) then
679 fnd_message.set_token(tokenNameThreeIn, tokenValueThreeIn);
680 end if;
681 if (tokenNameFourIn is not null) then
682 fnd_message.set_token(tokenNameFourIn, tokenValueFourIn);
683 end if;
684 if (tokenNameFiveIn is not null) then
685 fnd_message.set_token(tokenNameFiveIn, tokenValueFiveIn);
686 end if;
687 if (tokenNameSixIn is not null) then
688 fnd_message.set_token(tokenNameSixIn, tokenValueSixIn);
689 end if;
690 if (tokenNameSevenIn is not null) then
691 fnd_message.set_token(tokenNameSevenIn, tokenValueSevenIn);
692 end if;
693 if (tokenNameEightIn is not null) then
694 fnd_message.set_token(tokenNameEightIn, tokenValueEightIn);
695 end if;
696 if (tokenNameNineIn is not null) then
697 fnd_message.set_token(tokenNameNineIn, tokenValueNineIn);
698 end if;
699 if (tokenNameTenIn is not null) then
700 fnd_message.set_token(tokenNameTenIn, tokenValueTenIn);
701 end if;
702 return(fnd_message.get);
703 exception
704 when others then
705 runtimeException(packageNameIn => 'ame_util',
706 routineNameIn => 'getMessage',
707 exceptionNumberIn => sqlcode,
708 exceptionStringIn => sqlerrm);
709 raise;
710 return(null);
711 end getMessage;
712 function getMonthString(dateIn in date) return varchar2 as
713 begin
714 return(to_char(dateIn, 'MM'));
715 exception
716 when others then
717 runtimeException(packageNameIn => 'ame_util',
718 routineNameIn => 'getMonthString',
719 exceptionNumberIn => sqlcode,
720 exceptionStringIn => sqlerrm);
721 raise;
722 return(null);
723 end getMonthString;
724 function getOrgName(orgIdIn in integer) return varchar2 as
725 tempName hr_organization_units.name%type;
726 begin
727 if(orgIdIn is null) then
728 return(null);
729 end if;
730 select name
731 into tempName
732 from hr_organization_units
733 where
734 organization_id = orgIdIn and
735 trunc(sysdate) >= date_from and
736 (date_to is null or trunc(sysdate) < date_to);
737 return(tempName);
738 exception
739 when others then
740 runtimeException(packageNameIn => 'ame_util',
741 routineNameIn => 'getOrgName',
742 exceptionNumberIn => sqlcode,
743 exceptionStringIn => sqlerrm);
744 raise;
745 return(null);
746 end getOrgName;
747 function getPlsqlDadPath return varchar2 as
748 begin
749 return(owa_util.get_owa_service_path);
750 exception
751 when others then
752 runtimeException(packageNameIn => 'ame_util',
753 routineNameIn => 'getPlsqlDadPath',
754 exceptionNumberIn => sqlcode,
755 exceptionStringIn => sqlerrm);
756 raise;
757 return(null);
758 end getPlsqlDadPath;
759 function getQuery(selectClauseIn in varchar2) return ame_util.queryCursor as
760 queryCursor ame_util.queryCursor;
761 sqlStatement varchar2(4000);
762 begin
763 sqlStatement := selectClauseIn;
764 open queryCursor for sqlStatement;
765 return queryCursor;
766 exception
767 when others then
768 rollback;
769 runtimeException(packageNameIn => 'ame_util',
770 routineNameIn => 'getQuery',
771 exceptionNumberIn => sqlcode,
772 exceptionStringIn => sqlerrm);
773 raise;
774 return(queryCursor);
775 end getQuery;
776 function getServerName return varchar2 as
777 begin
778 return('http://' || owa_util.get_cgi_env(param_name => 'SERVER_NAME'));
779 exception
780 when others then
781 runtimeException(packageNameIn => 'ame_util',
782 routineNameIn => 'getServerName',
783 exceptionNumberIn => sqlcode,
784 exceptionStringIn => sqlerrm);
785 raise;
786 return(null);
787 end getServerName;
788 function getSetOfBooksName(setOfBooksIdIn in integer) return varchar2 as
789 tempName gl_sets_of_books.name%type;
790 begin
791 if(setOfBooksIdIn is null) then
792 return(null);
793 end if;
794 select name
795 into tempName
796 from gl_sets_of_books
797 where set_of_books_id = setOfBooksIdIn;
798 return(tempName);
799 exception
800 when others then
801 runtimeException(packageNameIn => 'ame_util',
802 routineNameIn => 'getSetOfBooksName',
803 exceptionNumberIn => sqlcode,
804 exceptionStringIn => sqlerrm);
805 raise;
806 return(null);
807 end getSetOfBooksName;
808 /*
809 AME_STRIPING
810 hostName v$instance.host_name%type;
811 instanceName v$instance.instance_name%type;
812 begin
813 select
814 host_name,
815 instance_name
816 into
817 hostName,
818 instanceName
819 from v$instance;
820 exception
821 when others then
822 runtimeException(packageNameIn => 'ame_util',
823 routineNameIn => 'getStripeSetCookieName',
824 exceptionNumberIn => sqlcode,
825 exceptionStringIn => sqlerrm);
826 raise;
827 return(null);
828 end getStripeSetCookieName;
829 */
830 function getTransTypeCookie return integer as
831 applicationId integer;
832 highestResponsibility integer;
833 returnStatus ame_util.longestStringType;
834 securedAttributeList icx_sec.g_num_tbl_type;
835 transTypeCookie owa_cookie.cookie;
836 userId integer;
837 begin
838 userId := ame_util.getCurrentUserId;
839 transTypeCookie := owa_cookie.get(ame_util.transactionTypeCookie || ':' || userId);
840 if(transTypeCookie.vals.count > 0) then
841 highestResponsibility := getHighestResponsibility;
842 applicationId := to_number(transTypeCookie.vals(1));
843 if(highestResponsibility = ame_util.limBusResponsibility) then
844 icx_sec.getsecureattributevalues(p_attri_code => ame_util.attributeCode,
845 p_return_status => returnStatus,
846 p_num_tbl => securedAttributeList);
847 for i in 1..securedAttributeList.count loop
848 if(securedAttributeList(i) = applicationId) then
849 return(transTypeCookie.vals(1));
850 end if;
851 end loop;
852 else
853 return(to_number(transTypeCookie.vals(1)));
854 end if;
855 end if;
856 return(null);
857 exception
858 when no_data_found then
859 return(null);
860 when others then
861 runtimeException(packageNameIn => 'ame_util',
862 routineNameIn => 'getTransTypeCookie',
863 exceptionNumberIn => sqlcode,
864 exceptionStringIn => sqlerrm);
865 raise;
866 return(null);
867 end getTransTypeCookie;
868 function getYearString(dateIn in date) return varchar2 as
869 begin
870 return(to_char(dateIn, 'YYYY'));
871 exception
872 when others then
873 runtimeException(packageNameIn => 'ame_util',
874 routineNameIn => 'getYearString',
875 exceptionNumberIn => sqlcode,
876 exceptionStringIn => sqlerrm);
877 raise;
878 return(null);
879 end getYearString;
880 function getServerPort return varchar2 as
881 begin
882 return(':' || owa_util.get_cgi_env(param_name => 'SERVER_PORT'));
883 exception
884 when others then
885 runtimeException(packageNameIn => 'ame_util',
886 routineNameIn => 'getServerPort',
887 exceptionNumberIn => sqlcode,
888 exceptionStringIn => sqlerrm);
889 raise;
890 return(null);
891 end getServerPort;
892 function hasOrderClause(queryStringIn in varchar2) return boolean as
893 begin
894 if instrb(upper(queryStringIn), 'ORDER') = 0 then
895 return(false);
896 end if;
897 return(true);
898 exception
899 when others then
900 runtimeException(packageNameIn => 'ame_util',
901 routineNameIn => 'hasOrderClause',
902 exceptionNumberIn => sqlcode,
903 exceptionStringIn => sqlerrm);
904 raise;
905 return(true);
906 end hasOrderClause;
907 function idListsMatch(idList1InOut in out nocopy idList,
908 idList2InOut in out nocopy idList,
909 sortList1In in boolean default false,
910 sortList2In in boolean default true) return boolean as
911 listLength1 integer;
912 listLength2 integer;
913 tempIndex integer;
914 begin
915 listLength1 := idList1InOut.count;
916 listLength2 := idList2InOut.count;
917 if(listLength1 <> listLength2) then
918 return(false);
919 end if;
920 if(sortList1In) then
921 sortIdListInPlace(idListInOut => idList1InOut);
922 end if;
923 if(sortList2In) then
924 sortIdListInPlace(idListInOut => idList2InOut);
925 end if;
926 for tempIndex in 1 .. listLength1 loop
927 if(idList1InOut(tempIndex) <> idList2InOut(tempIndex)) then
928 return(false);
929 end if;
930 end loop;
931 return(true);
932 exception
933 when others then
934 runtimeException(packageNameIn => 'ame_util',
935 routineNameIn => 'idListsMatch',
936 exceptionNumberIn => sqlcode,
937 exceptionStringIn => sqlerrm);
938 raise;
939 return(null);
940 end idListsMatch;
941 function inputNumStringToCanonNumString(inputNumberStringIn in varchar2,
942 currencyCodeIn in varchar2 default null) return varchar2 as
943 format varchar2(1);
944 inputString varchar2(50);
945 returnValue varchar2(240); /* this length specified by HR documentation */
946 rgeflg varchar2(1);
947 begin
948 if(currencyCodeIn is null) then
949 format := 'N';
950 else
951 format := 'M';
952 end if;
953 inputString := replace(inputNumberStringIn,
954 ',',
955 '.');
956 /*
957 In hr_chkfmt.checkformat, <<value>> is an in/out argument that on output is end-user friendly,
958 and <<output>> is a number-string in canonical format (I checked the source code). <<nullok>>
959 must be either 'Y' or 'N' (the package does not offer constants for these values).
960 */
961 hr_chkfmt.checkformat(value => inputString,
962 format => format,
963 output => returnValue,
964 minimum => null,
965 maximum => null,
966 nullok => 'Y',
967 rgeflg => rgeflg,
968 curcode => currencyCodeIn);
969 return(returnValue);
970 exception
971 when others then
972 runtimeException(packageNameIn => 'ame_util',
973 routineNameIn => 'inputNumStringToCanonNumString',
974 exceptionNumberIn => sqlcode,
975 exceptionStringIn => sqlerrm);
976 raise;
977 return(null);
978 end inputNumStringToCanonNumString;
979 function isAnEvenNumber(numberIn in integer) return boolean as
980 errorCode integer;
981 errorMessage ame_util.longestStringType;
982 notAnIntException exception;
983 begin
984 if(not isAnInteger(stringIn => to_char(numberIn))) then
985 raise notAnIntException;
986 end if;
987 return(mod(numberIn, 2) = 0);
988 exception
989 when notAnIntException then
990 errorCode := -20001;
991 errorMessage :=
992 ame_util.getMessage(applicationShortNameIn => 'PER',
993 messageNameIn => 'AME_400457_INPUT_NOT_INTEGER');
994 runtimeException(packageNameIn => 'ame_util',
995 routineNameIn => 'isAnEvenNumber',
996 exceptionNumberIn => errorCode,
997 exceptionStringIn => errorMessage);
998 raise_application_error(errorCode,
999 errorMessage);
1000 return(false);
1001 when others then
1002 runtimeException(packageNameIn => 'ame_util',
1003 routineNameIn => 'isAnEvenNumber',
1004 exceptionNumberIn => sqlcode,
1005 exceptionStringIn => sqlerrm);
1006 raise;
1007 return(false);
1008 end isAnEvenNumber;
1009 function isAnInteger(stringIn in varchar2) return boolean as
1010 begin
1011 return(isANumber(stringIn => stringIn,
1012 allowDecimalsIn => false));
1013 exception
1014 when others then
1015 runtimeException(packageNameIn => 'ame_util',
1016 routineNameIn => 'isAnInteger',
1017 exceptionNumberIn => sqlcode,
1018 exceptionStringIn => sqlerrm);
1019 raise;
1020 return(false);
1021 end isAnInteger;
1022 function isANegativeInteger(stringIn in varchar2) return boolean as
1023 begin
1024 if(isANumber(stringIn => stringIn,
1025 allowDecimalsIn => false,
1026 allowNegativesIn => true) and
1027 to_number(stringIn) < 0) then
1028 return(true);
1029 end if;
1030 return(false);
1031 exception
1032 when others then
1033 runtimeException(packageNameIn => 'ame_util',
1034 routineNameIn => 'isANegativeInteger',
1035 exceptionNumberIn => sqlcode,
1036 exceptionStringIn => sqlerrm);
1037 raise;
1038 return(false);
1039 end isANegativeInteger;
1040 function isANonNegativeInteger(stringIn in varchar2) return boolean as
1041 begin
1042 return(isANumber(stringIn => stringIn,
1043 allowDecimalsIn => false,
1044 allowNegativesIn => false));
1045 exception
1046 when others then
1047 runtimeException(packageNameIn => 'ame_util',
1048 routineNameIn => 'isANonNegativeInteger',
1049 exceptionNumberIn => sqlcode,
1050 exceptionStringIn => sqlerrm);
1051 raise;
1052 return(false);
1053 end isANonNegativeInteger;
1054 function isANumber(stringIn in varchar2,
1055 allowDecimalsIn in boolean default true,
1056 allowNegativesIn in boolean default true) return boolean as
1057 firstOccurrence1 integer;
1058 firstOccurrence2 integer;
1059 nonNumberChars ame_util.stringType;
1060 begin
1061 nonNumberChars := stringIn;
1062 if(allowDecimalsIn) then
1063 firstOccurrence1 := instrb(nonNumberChars, '.', 1, 1);
1064 if(firstOccurrence1 > 0 and
1065 firstOccurrence1 <> instrb(nonNumberChars, '.', -1, 1)) then
1066 /* There are at least two periods in the string; return false. */
1067 return(false);
1068 end if;
1069 firstOccurrence2 := instrb(nonNumberChars, ',', 1, 1);
1070 if(firstOccurrence2 > 0 and
1071 firstOccurrence2 <> instrb(nonNumberChars, ',', -1, 1)) then
1072 /* There are at least two commas in the string; return false. */
1073 return(false);
1074 end if;
1075 if(firstOccurrence1 > 0 and
1076 firstOccurrence2 > 0) then
1077 /* Both a period and a comma appear in the string; return false. */
1078 return(false);
1079 end if;
1080 /*
1081 Now we're sure at most one period or at most comma appears in the string.
1082 Get rid of it, if it's there.
1083 */
1084 nonNumberChars := replace(replace(nonNumberChars, ',', ''), '.', '');
1085 end if;
1086 if(allowNegativesIn) then
1087 firstOccurrence1 := instrb(nonNumberChars, '-', 1, 1);
1088 if(firstOccurrence1 > 1) then
1089 /* There is a hyphen after the initial position; return false. */
1090 return(false);
1091 end if;
1092 /* Now either the first hyphen is in the first position, or there is no hyphen. */
1093 if(firstOccurrence1 = 1) then
1094 if(instrb(nonNumberChars, '-', -1, 1) <> 1) then
1095 /* There is a second hyphen in the string; return false. */
1096 return(false);
1097 else
1098 /*
1099 Now we're sure there is at most one hyphen in the string, and that
1100 it's in position 1. Get rid of it.
1101 */
1102 nonNumberChars := substrb(nonNumberChars, 2);
1103 end if;
1104 end if;
1105 end if;
1106 /* Now get rid of the digits. */
1107 for i in 0 .. 9 loop
1108 nonNumberChars := replace(nonNumberChars, i, '');
1109 end loop;
1110 if(nonNumberChars is null) then
1111 return(true);
1112 end if;
1113 return(false);
1114 exception
1115 when others then
1116 runtimeException(packageNameIn => 'ame_util',
1117 routineNameIn => 'isANumber',
1118 exceptionNumberIn => sqlcode,
1119 exceptionStringIn => sqlerrm);
1120 raise;
1121 return(false);
1122 end isANumber;
1123 function isArgumentTooLong(tableNameIn in varchar2,
1124 columnNameIn in varchar2,
1125 argumentIn in varchar2) return boolean as
1126 argumentLength integer;
1127 begin
1128 argumentLength := lengthb(argumentIn);
1129 if(ame_util.getColumnLength(tableNameIn, columnNameIn) < argumentLength) then
1130 return(true);
1131 end if;
1132 return(false);
1133 exception
1134 when others then
1135 runtimeException(packageNameIn => 'ame_util',
1136 routineNameIn => 'isArgumentTooLong',
1137 exceptionNumberIn => sqlcode,
1138 exceptionStringIn => sqlerrm);
1139 raise;
1140 return(true);
1141 end isArgumentTooLong;
1142 function isConversionTypeValid(conversionTypeIn in varchar2) return boolean as
1143 tempCount integer;
1144 begin
1145 select count(*)
1146 into tempCount
1147 from gl_daily_conversion_types
1148 where conversion_type = conversionTypeIn;
1149 if(tempCount > 0) then
1150 return(true);
1151 end if;
1152 return(false);
1153 exception
1154 when others then
1155 runtimeException(packageNameIn => 'ame_util',
1156 routineNameIn => 'isConversionTypeValid',
1157 exceptionNumberIn => sqlcode,
1158 exceptionStringIn => sqlerrm);
1159 raise;
1160 return(false);
1161 end isConversionTypeValid;
1162 function isCurrencyCodeValid(currencyCodeIn in varchar2) return boolean as
1163 tempCount integer;
1164 begin
1165 select count(*)
1166 into tempCount
1167 from fnd_currencies_active_v
1168 where currency_code = currencyCodeIn;
1169 if(tempCount > 0) then
1170 return(true);
1171 end if;
1172 return(false);
1173 exception
1174 when others then
1175 runtimeException(packageNameIn => 'ame_util',
1176 routineNameIn => 'isCurrencyCodeValid',
1177 exceptionNumberIn => sqlcode,
1178 exceptionStringIn => sqlerrm);
1179 raise;
1180 return(false);
1181 end isCurrencyCodeValid;
1182 function isDateInRange(currentDateIn in date default sysdate,
1183 startDateIn in date,
1184 endDateIn in date) return boolean is
1185 begin
1186 if((startDateIn is null and endDateIn is null) or
1187 (startDateIn is null and sysdate <= endDateIn) or
1188 (startDateIn <= sysdate and endDateIn is null) or
1189 (sysdate between startDateIn and endDateIn)) then
1190 return(true);
1191 end if;
1192 return(false);
1193 exception
1194 when others then
1195 runtimeException(packageNameIn => 'ame_util',
1196 routineNameIn => 'isDateInRange',
1197 exceptionNumberIn => sqlcode,
1198 exceptionStringIn => sqlerrm);
1199 raise;
1200 return(null);
1201 end isDateInRange;
1202 function longStringListsMatch(longStringList1InOut in out nocopy longStringList,
1203 longStringList2InOut in out nocopy longStringList,
1204 sortList1In in boolean default false,
1205 sortList2In in boolean default true) return boolean as
1206 listLength1 integer;
1207 listLength2 integer;
1208 tempIndex integer;
1209 begin
1210 listLength1 := longStringList1InOut.count;
1211 listLength2 := longStringList2InOut.count;
1212 if(listLength1 <> listLength2) then
1213 return(false);
1214 end if;
1215 if(sortList1In) then
1216 sortLongStringListInPlace(longStringListInOut => longStringList1InOut);
1217 end if;
1218 if(sortList2In) then
1219 sortLongStringListInPlace(longStringListInOut => longStringList2InOut);
1220 end if;
1221 for tempIndex in 1 .. listLength1 loop
1222 if(longStringList1InOut(tempIndex) <> longStringList2InOut(tempIndex)) then
1223 return(false);
1224 end if;
1225 end loop;
1226 return(true);
1227 exception
1228 when others then
1229 runtimeException(packageNameIn => 'ame_util',
1230 routineNameIn => 'longStringListsMatch',
1231 exceptionNumberIn => sqlcode,
1232 exceptionStringIn => sqlerrm);
1233 raise;
1234 return(null);
1235 end longStringListsMatch;
1236 function longestStringListsMatch(longestStringList1InOut in out nocopy longestStringList,
1237 longestStringList2InOut in out nocopy longestStringList,
1238 sortList1In in boolean default false,
1239 sortList2In in boolean default true) return boolean as
1240 listLength1 integer;
1241 listLength2 integer;
1242 tempIndex integer;
1243 begin
1244 listLength1 := longestStringList1InOut.count;
1245 listLength2 := longestStringList2InOut.count;
1246 if(listLength1 <> listLength2) then
1247 return(false);
1248 end if;
1249 if(sortList1In) then
1250 sortLongestStringListInPlace(longestStringListInOut => longestStringList1InOut);
1251 end if;
1252 if(sortList2In) then
1253 sortLongestStringListInPlace(longestStringListInOut => longestStringList2InOut);
1254 end if;
1255 for tempIndex in 1 .. listLength1 loop
1256 if(longestStringList1InOut(tempIndex) <> longestStringList2InOut(tempIndex)) then
1257 return(false);
1258 end if;
1259 end loop;
1260 return(true);
1261 exception
1262 when others then
1263 runtimeException(packageNameIn => 'ame_util',
1264 routineNameIn => 'longestStringListsMatch',
1265 exceptionNumberIn => sqlcode,
1266 exceptionStringIn => sqlerrm);
1267 raise;
1268 return(null);
1269 end longestStringListsMatch;
1270 function matchCharacter(stringIn in varchar2,
1271 locationIn in integer,
1272 characterIn in varchar2) return boolean as
1273 errorCode integer;
1274 errorMessage ame_util.longestStringType;
1275 locationException exception;
1276 charLengthException exception;
1277 begin
1278 if(locationIn < 1 or
1279 locationIn > lengthb(stringIn)) then
1280 raise locationException;
1281 end if;
1282 if(lengthb(characterIn) <> 1) then
1283 raise charLengthException;
1284 end if;
1285 if(substrb(stringIn, locationIn, 1) = characterIn) then
1286 return(true);
1287 end if;
1288 return(false);
1289 exception
1290 when locationException then
1291 errorCode := -20001; -- pa message
1292 errorMessage :=
1293 ame_util.getMessage(applicationShortNameIn => 'PER',
1294 messageNameIn => 'AME_400458_MUST_BE_INTEGER',
1295 tokenNameOneIn => 'LOCATION',
1296 tokenValueOneIn => locationIn,
1297 tokenNameTwoIn => 'STRING',
1298 tokenValueTwoIn => stringIn);
1299 ame_util.runtimeException(packageNameIn => 'ame_util',
1300 routineNameIn => 'matchCharacter',
1301 exceptionNumberIn => errorCode,
1302 exceptionStringIn => errorMessage);
1303 raise_application_error(errorCode,
1304 errorMessage);
1305 return(null);
1306 when charLengthException then
1307 errorCode := -20001;
1308 errorMessage := 'characterIn must be a single character. ';
1309 ame_util.runtimeException(packageNameIn => 'ame_util',
1310 routineNameIn => 'matchCharacter',
1311 exceptionNumberIn => errorCode,
1312 exceptionStringIn => errorMessage);
1313 raise_application_error(errorCode,
1314 errorMessage);
1315 return(null);
1316 when others then
1317 ame_util.runtimeException(packageNameIn => 'ame_util',
1318 routineNameIn => 'matchCharacter',
1319 exceptionNumberIn => sqlcode,
1320 exceptionStringIn => sqlerrm);
1321 raise;
1322 return(null);
1323 end matchCharacter;
1324 function personIdToUserId(personIdIn in integer) return integer as
1325 tempUserId fnd_user.user_id%type;
1326 begin
1327 select user_id
1328 into tempUserId
1329 from fnd_user
1330 where employee_id = personIdIn;
1331 return tempUserId;
1332 exception
1333 when others then
1334 runtimeException(packageNameIn => 'ame_util',
1335 routineNameIn => 'personIdToUserId',
1336 exceptionNumberIn => sqlcode,
1337 exceptionStringIn => sqlerrm);
1338 raise;
1339 return(null);
1340 end personIdToUserId;
1341 function recordDelimiter return varchar2 as
1342 begin
1343 /* 12 is a form feed in the ASCII character set. */
1344 return(fnd_global.local_chr(ascii_chr => 12));
1345 end recordDelimiter;
1346 function removeReturns(stringIn in varchar2,
1347 replaceWithSpaces in boolean default false) return varchar2 as
1348 replacementCharacter varchar2(1);
1349 begin
1350 if(replaceWithSpaces) then
1351 replacementCharacter := ' ';
1352 else
1353 replacementCharacter := null;
1354 end if;
1355 return(replace(replace(stringIn,
1356 getLineFeed,
1357 replacementCharacter),
1358 getCarriageReturn,
1359 replacementCharacter));
1360 exception
1361 when others then
1362 runtimeException(packageNameIn => 'ame_util',
1363 routineNameIn => 'removeReturns',
1364 exceptionNumberIn => sqlcode,
1365 exceptionStringIn => sqlerrm);
1366 raise;
1367 return(null);
1368 end removeReturns;
1369 function removeScriptTags(stringIn in varchar2 default null) return varchar2 as
1370 errorCode integer;
1371 errorMessage ame_util.longestStringType;
1372 inputString ame_util.longestStringType;
1373 inputStringLength integer;
1374 loopCounter integer;
1375 loopException exception;
1376 tagStartLocation integer;
1377 tagEndLocation integer;
1378 upperInputString ame_util.longestStringType;
1379 begin
1380 if(stringIn is null) then
1381 return(null);
1382 end if;
1383 inputString := stringIn;
1384 loopCounter := 0; /* pre-increment */
1385 /* Find and remove all occurrences of HTML script tagging. */
1386 loop
1387 loopCounter := loopCounter + 1;
1388 if(loopCounter > 100) then
1389 raise loopException;
1390 end if;
1391 /* Look for the script keyword. */
1392 upperInputString := upper(inputString);
1393 tagStartLocation := instrb(upperInputString, 'SCRIPT', 1, 1);
1394 if(tagStartLocation = 0) then
1395 exit;
1396 end if;
1397 /* The script keyword was found. Look for slashes and tag brackets. */
1398 inputStringLength := lengthb(inputString);
1399 tagEndLocation := tagStartLocation + 6;
1400 /* Look for a slash. */
1401 if(tagStartLocation > 1 and
1402 matchCharacter(stringIn => inputString,
1403 locationIn => tagStartLocation - 1,
1404 characterIn => '/')) then
1405 tagStartLocation := tagStartLocation - 1;
1406 end if;
1407 /* Look for an open bracket. */
1408 if(tagStartLocation > 1 and
1409 matchCharacter(stringIn => inputString,
1410 locationIn => tagStartLocation - 1,
1411 characterIn => '<')) then
1412 tagStartLocation := tagStartLocation - 1;
1413 end if;
1414 /* Look for a close bracket. */
1415 if(tagEndLocation <= inputStringLength and
1416 matchCharacter(stringIn => inputString,
1417 locationIn => tagEndLocation,
1418 characterIn => '>')) then
1419 tagEndLocation := tagEndLocation + 1;
1420 end if;
1421 inputString := substrb(inputString, 1, tagStartLocation - 1) || substrb(inputString, tagEndLocation);
1422 end loop;
1423 return(inputString);
1424 exception
1425 when loopException then
1426 errorCode := -20001;
1427 errorMessage := -- pa message
1428 'This function''s main loop iterated 100 times, which indicates an internal error. ' ||
1429 'Please contact Oracle technical support. ';
1430 ame_util.runtimeException(packageNameIn => 'ame_util',
1431 routineNameIn => 'removeScriptTags',
1432 exceptionNumberIn => errorCode,
1433 exceptionStringIn => errorMessage);
1434 raise_application_error(errorCode,
1435 errorMessage);
1436 return(null);
1437 when others then
1438 ame_util.runtimeException(packageNameIn => 'ame_util',
1439 routineNameIn => 'removeScriptTags',
1440 exceptionNumberIn => sqlcode,
1441 exceptionStringIn => sqlerrm);
1442 raise;
1443 return(null);
1444 end removeScriptTags;
1445 /*
1446 All of the serialization functions need to be very efficient. If you modify this
1447 code, please don't "roll up" the loops by having them call subroutines that are
1448 not absolutely necessary, and whose only purposes would be elegance and readability.
1449 */
1450 function stringListsMatch(stringList1InOut in out nocopy stringList,
1451 stringList2InOut in out nocopy stringList,
1452 sortList1In in boolean default false,
1453 sortList2In in boolean default true) return boolean as
1454 listLength1 integer;
1455 listLength2 integer;
1456 tempIndex integer;
1457 begin
1458 listLength1 := stringList1InOut.count;
1459 listLength2 := stringList2InOut.count;
1460 if(listLength1 <> listLength2) then
1461 return(false);
1462 end if;
1463 if(sortList1In) then
1464 sortStringListInPlace(stringListInOut => stringList1InOut);
1465 end if;
1466 if(sortList2In) then
1467 sortStringListInPlace(stringListInOut => stringList2InOut);
1468 end if;
1469 for tempIndex in 1 .. listLength1 loop
1470 if(stringList1InOut(tempIndex) <> stringList2InOut(tempIndex)) then
1471 return(false);
1472 end if;
1473 end loop;
1474 return(true);
1475 exception
1476 when others then
1477 runtimeException(packageNameIn => 'ame_util',
1478 routineNameIn => 'stringListsMatch',
1479 exceptionNumberIn => sqlcode,
1480 exceptionStringIn => sqlerrm);
1481 raise;
1482 return(null);
1483 end stringListsMatch;
1484 function userIdToPersonId(userIdIn in integer) return integer as
1485 tempPersonId fnd_user.employee_id%type;
1486 begin
1487 select employee_id
1488 into tempPersonId
1489 from fnd_user
1490 where user_id = userIdIn;
1491 return tempPersonId;
1492 exception
1493 when others then
1494 runtimeException(packageNameIn => 'ame_util',
1495 routineNameIn => 'userIdToPersonId',
1496 exceptionNumberIn => sqlcode,
1497 exceptionStringIn => sqlerrm);
1498 raise;
1499 return(null);
1500 end userIdToPersonId;
1501
1502 procedure getAttributeId(attributeNameIn in varchar2
1503 ,attributeIdOut out nocopy number
1504 ,transactionIdIn in varchar2
1505 ,applicationIdIn in number) as
1506 tempAttributeId number;
1507 tempLogId number;
1508 begin
1509 select attribute_id
1510 into tempAttributeId
1511 from ame_attributes
1512 where name = upper(attributeNameIn)
1513 and sysdate between start_date and
1514 nvl(end_date - ame_util.oneSecond, sysdate) ;
1515 attributeIdOut := tempAttributeId;
1516 exception
1517 when others then
1518 attributeIdOut := null;
1519 select ame_exceptions_log_s.nextval into tempLogId from dual;
1520 autonomousLog(logIdIn => tempLogId,
1521 packageNameIn => 'ame_util',
1522 routineNameIn => 'getAttributeId',
1523 exceptionNumberIn => sqlcode,
1524 exceptionStringIn => sqlerrm,
1525 transactionIdIn => transactionIdIn,
1526 applicationIdIn => applicationIdIn );
1527 end getAttributeId;
1528
1529 procedure getQueryString(attributeIdIn in varchar2
1530 ,queryStringOut out nocopy ame_attribute_usages.query_string%type
1531 ,transactionIdIn in varchar2
1532 ,applicationIdIn in number) as
1533 temQueryString ame_attribute_usages.query_string%type;
1534 tempLogId number;
1535 begin
1536 select query_string
1537 into temQueryString
1538 from ame_attribute_usages
1539 where
1540 attribute_id = attributeIdIn and
1541 application_id = applicationIdIn and
1542 sysdate between start_date and
1543 nvl(end_date - ame_util.oneSecond, sysdate) ;
1544 queryStringOut := temQueryString;
1545 exception
1546 when others then
1547 queryStringOut := null;
1548 select ame_exceptions_log_s.nextval into tempLogId from dual;
1549 autonomousLog(logIdIn => tempLogId,
1550 packageNameIn => 'ame_util',
1551 routineNameIn => 'getQueryString',
1552 exceptionNumberIn => sqlcode,
1553 exceptionStringIn => sqlerrm,
1554 transactionIdIn => transactionIdIn,
1555 applicationIdIn => applicationIdIn );
1556 end getQueryString;
1557
1558 procedure checkSaticUsage(attributeIdIn in varchar2
1559 ,isSaticUsage out nocopy varchar2
1560 ,transactionIdIn in varchar2
1561 ,applicationIdIn in number) as
1562 tempIsSatic varchar2(2);
1563 tempLogId number;
1564 begin
1565 select is_static
1566 into tempIsSatic
1567 from ame_attribute_usages
1568 where attribute_id = attributeIdIn and
1569 application_id = applicationIdIn and
1570 sysdate between start_date and
1571 nvl(end_date - ame_util.oneSecond, sysdate) ;
1572 isSaticUsage := tempIsSatic;
1573 exception
1574 when others then
1575 isSaticUsage := null;
1576 select ame_exceptions_log_s.nextval into tempLogId from dual;
1577 autonomousLog(logIdIn => tempLogId,
1578 packageNameIn => 'ame_util',
1579 routineNameIn => 'checkSaticUsage',
1580 exceptionNumberIn => sqlcode,
1581 exceptionStringIn => sqlerrm,
1582 transactionIdIn => transactionIdIn,
1583 applicationIdIn => applicationIdIn );
1584 end checkSaticUsage;
1585 function useWorkflow(transactionIdIn in varchar2 default null,
1586 applicationIdIn in integer) return boolean as
1587 attributeId integer;
1588 attributeUsage ame_attribute_usages.query_string%type;
1589 attributeValue ame_util.attributeValueType;
1590 badUsageException exception;
1591 dynamicCursor integer;
1592 errorCode integer;
1593 errorMessage ame_util.longestStringType;
1594 ignoreMe integer;
1595 tempIsstaticUsage varchar2(2);
1596 tempLogId number;
1597 begin
1598 /*
1599 If we don't know which transaction type we're dealing with, we can't interact
1600 with its Workflow exception stack.
1601 */
1602 if(applicationIdIn is null) then
1603 return false;
1604 end if;
1605 /*
1606 In the absence of a positive attribute value to the contrary, assume conservatively
1607 that we should not interact with the transaction type's Workflow exception stack.
1608 */
1609 attributeValue := ame_util.booleanAttributeFalse;
1610 /* Get the usage, either to read it if it's static or to execute it if it's dynamic. */
1611 /*methods getattributId, getqueryRting and checkSaticUsage are local method used to avoid
1612 infinite loop due to exception(bug 6837659). When exception occur in this methods they log the
1613 details but do not raise the error. This message will appear along with the original exception passed
1614 into run time exception*/
1615 getAttributeId(attributeNameIn => ame_util.useWorkflowAttribute
1616 ,attributeIdOut => attributeId
1617 ,transactionIdIn => transactionIdIn
1618 ,applicationIdIn => applicationIdIn );
1619 if attributeId is null then
1620 return false;
1621 end if;
1622 getQueryString(attributeIdIn => attributeId
1623 ,queryStringOut => attributeUsage
1624 ,transactionIdIn => transactionIdIn
1625 ,applicationIdIn => applicationIdIn );
1626 if attributeUsage is null then
1627 return false;
1628 end if;
1629 checkSaticUsage(attributeIdIn => attributeId
1630 ,isSaticUsage => tempIsstaticUsage
1631 ,transactionIdIn => transactionIdIn
1632 ,applicationIdIn => applicationIdIn );
1633 if tempIsstaticUsage is null then
1634 return false;
1635 end if;
1636 /* Check/execute the usage. */
1637 if( tempIsstaticUsage = ame_util.booleanTrue) then
1638 attributeValue := attributeUsage;
1639 elsif(transactionIdIn is not null) then
1640 dynamicCursor := dbms_sql.open_cursor;
1641 dbms_sql.parse(dynamicCursor,
1642 ame_util.removeReturns(stringIn => attributeUsage,
1643 replaceWithSpaces => true),
1644 dbms_sql.native);
1645 dbms_sql.define_column(dynamicCursor,
1646 1,
1647 attributeValue,
1648 ame_util.attributeValueTypeLength);
1649 if(instrb(attributeUsage, ame_util.transactionIdPlaceholder) > 0) then
1650 dbms_sql.bind_variable(dynamicCursor,
1651 ame_util.transactionIdPlaceholder,
1652 transactionIdIn);
1653 end if;
1654 ignoreMe := dbms_sql.execute(dynamicCursor);
1655 if(dbms_sql.fetch_rows(dynamicCursor) > 0) then
1656 /*
1657 Don't raise an exception if the fetch returns no rows, because we can't log the
1658 exception (see below); just silently use the default attributeValue value.
1659 */
1660 dbms_sql.column_value(dynamicCursor,
1661 1,
1662 attributeValue);
1663 end if;
1664 dbms_sql.close_cursor(dynamicCursor);
1665 end if;
1666 if(attributeValue = ame_util.booleanAttributeTrue) then
1667 return(true);
1668 end if;
1669 return(false);
1670 exception
1671 when others then
1672 /*
1673 Don't call runtimeException here; it could result in infinite looping,
1674 because runtimeException calls useWorkflow. (See bug 2219719.)
1675 */
1676 raise;
1677 return(false);
1678 end useWorkflow;
1679 function validateUser(responsibilityIn in integer,
1680 applicationIdIn in integer default null) return integer as
1681 securedAttributeList icx_sec.g_num_tbl_type;
1682 highestResponsibility integer;
1683 responsibilityCount integer;
1684 responsibilityList icx_sec.g_responsibility_list;
1685 returnStatus varchar2(4000);
1686 securedAttributeCount integer;
1687 tempIndex integer;
1688 tempSecuredAttribute ak_web_user_sec_attr_values.number_value%type;
1689 userId fnd_user.user_id%type;
1690 begin
1691 if not icx_sec.validatesession then
1692 return(ame_util.noResponsibility);
1693 end if;
1694 highestResponsibility := getHighestResponsibility;
1695 /* If applicationIdIn is not null, and the user is a limited business user, check secure attribute. */
1696 if(applicationIdIn is not null and
1697 highestResponsibility = ame_util.limBusResponsibility) then
1698 tempSecuredAttribute := applicationIdIn;
1699 icx_sec.getsecureattributevalues(p_attri_code => ame_util.attributeCode,
1700 p_return_status => returnStatus,
1701 p_num_tbl => securedAttributeList);
1702 securedAttributeCount := securedAttributeList.count;
1703 for i in 1..securedAttributeCount loop
1704 if i = 1 then
1705 tempIndex := securedAttributeList.first;
1706 if(securedAttributeList(tempIndex) = tempSecuredAttribute) then
1707 return(highestResponsibility);
1708 end if;
1709 else
1710 tempIndex := securedAttributeList.next(tempIndex);
1711 if(securedAttributeList(tempIndex) = tempSecuredAttribute) then
1712 return(highestResponsibility);
1713 end if;
1714 end if;
1715 end loop;
1716 highestResponsibility := ame_util.noResponsibility;
1717 end if;
1718 if highestResponsibility >= responsibilityIn then
1719 return(highestResponsibility);
1720 end if;
1721 return(ame_util.noResponsibility);
1722 exception
1723 when others then
1724 runtimeException(packageNameIn => 'ame_util',
1725 routineNameIn => 'validateUser',
1726 exceptionNumberIn => sqlcode,
1727 exceptionStringIn => sqlerrm);
1728 raise;
1729 return(ame_util.noResponsibility);
1730 end validateUser;
1731 function versionDateToDisplayDate(stringDateIn in varchar2) return varchar2 as
1732 begin
1733 return(fnd_date.date_to_displayDate(dateVal => versionStringToDate(stringDateIn => stringDateIn)));
1734 exception
1735 when no_data_found then
1736 return(null);
1737 when others then
1738 runtimeException(packageNameIn => 'ame_util',
1739 routineNameIn => 'versionDateToDisplayDate',
1740 exceptionNumberIn => sqlcode,
1741 exceptionStringIn => sqlerrm);
1742 raise;
1743 return(null);
1744 end versionDateToDisplayDate;
1745 function versionDateToString(dateIn in date) return varchar2 as
1746 begin
1747 return(to_char(dateIn, ame_util.versionDateFormatModel));
1748 exception
1749 when no_data_found then
1750 return(null);
1751 when others then
1752 runtimeException(packageNameIn => 'ame_util',
1753 routineNameIn => 'versionDateToString',
1754 exceptionNumberIn => sqlcode,
1755 exceptionStringIn => sqlerrm);
1756 raise;
1757 return(null);
1758 end versionDateToString;
1759 function versionStringToDate(stringDateIn in varchar2) return date as
1760 begin
1761 return(to_date(stringDateIn, ame_util.versionDateFormatModel));
1762 exception
1763 when no_data_found then
1764 return(null);
1765 when others then
1766 runtimeException(packageNameIn => 'ame_util',
1767 routineNameIn => 'versionStringToDate',
1768 exceptionNumberIn => sqlcode,
1769 exceptionStringIn => sqlerrm);
1770 raise;
1771 return(null);
1772 end versionStringToDate;
1773 procedure appendRuleIdToSource(ruleIdIn in integer,
1774 sourceInOut in out nocopy varchar2) as
1775 ruleId varchar2(50);
1776 ruleIds ame_util.idList;
1777 sourceDescription ame_util.longStringType;
1778 sourceLength integer;
1779 begin
1780 if(sourceInOut is null) then
1781 sourceInOut := to_char(ruleIdIn);
1782 return;
1783 end if;
1784 /* Now we can assume sourceInOut starts out nonempty. */
1785 sourceLength := lengthb(sourceInOut);
1786 ruleId := to_char(ruleIdIn);
1787 if(sourceLength + 1 + lengthb(ruleId) < 500) then
1788 parseSourceValue(sourceValueIn => sourceInOut,
1789 sourceDescriptionOut => sourceDescription,
1790 ruleIdListOut => ruleIds);
1791 for i in 1 .. ruleIds.count loop
1792 if(ruleIdIn = ruleIds(i)) then /* Don't duplicate rule IDs in a source field. */
1793 return;
1794 end if;
1795 end loop;
1796 sourceInOut := sourceInOut || fieldDelimiter || ruleId;
1797 end if;
1798 exception
1799 when others then
1800 ame_util.runtimeException(packageNameIn => 'ame_util',
1801 routineNameIn => 'appendRuleIdToSource',
1802 exceptionNumberIn => sqlcode,
1803 exceptionStringIn => sqlerrm);
1804 raise;
1805 end appendRuleIdToSource;
1806 --
1807 -- This procedure will translate the approver record from type
1808 -- ame_util.approverRecord to ame_util.approverRecord2
1809 --
1810 procedure apprRecordToApprRecord2(approverRecordIn in ame_util.approverRecord,
1811 itemIdIn in varchar2 default null,
1812 approverRecord2Out out nocopy ame_util.approverRecord2) as
1813 errorCode integer;
1814 errorMessage ame_util.longestStringType;
1815 tempOrigSystem wf_roles.orig_system%type;
1816 tempOrigSystemId wf_roles.orig_system_id%type;
1817 wfName wf_roles.name%type;
1818 wfDisplayName wf_roles.display_name%type;
1819 begin
1820 if approverRecordIn.person_id is null then
1821 tempOrigSystem := ame_util.fndUserOrigSystem;
1822 tempOrigSystemId := approverRecordIn.user_id;
1823 else
1824 tempOrigSystem := ame_util.perOrigSystem;
1825 tempOrigSystemId := approverRecordIn.person_id;
1826 end if;
1827 if tempOrigSystem = ame_util.perOrigSystem then
1828 select name, display_name
1829 into wfName, wfDisplayName
1830 from wf_roles wf
1831 where orig_system = tempOrigSystem
1832 and orig_system_id = tempOrigSystemId
1833 and status = 'ACTIVE'
1834 and (expiration_date is null or sysdate < expiration_date)
1835 and exists (select null
1836 from fnd_user u
1837 where u.user_name = wf.name
1838 and trunc(sysdate) between u.start_date
1839 and nvl(u.end_date,trunc(sysdate)))
1840 and not exists (
1841 select null from wf_roles wf2
1842 where wf.orig_system = wf2.orig_system
1843 and wf.orig_system_id = wf2.orig_system_id
1844 and wf.start_date > wf2.start_date
1845 )
1846 and rownum < 2;
1847 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
1848 select name
1849 ,display_name
1850 ,orig_system
1851 ,orig_system_id
1852 into wfName
1853 ,wfDisplayName
1854 ,tempOrigSystem
1855 ,tempOrigSystemId
1856 from wf_roles wf
1857 where wf.orig_system in('FND_USR','PER')
1858 and wf.name in (select u.user_name
1859 from fnd_user u
1860 where u.user_id = tempOrigSystemId
1861 and trunc(sysdate) between u.start_date
1862 and nvl(u.end_date,trunc(sysdate)))
1863 and wf.status = 'ACTIVE'
1864 and (wf.expiration_date is null or sysdate < wf.expiration_date)
1865 -- need not check for proxy user in this case
1866 and rownum < 2;
1867 end if;
1868 approverRecord2Out.orig_system := tempOrigSystem;
1869 approverRecord2Out.orig_system_id := tempOrigSystemId;
1870 approverRecord2Out.name := wfName;
1871 approverRecord2Out.display_name := wfDisplayName;
1872 approverRecord2Out.item_class := ame_util.headerItemClassName;
1873 approverRecord2Out.item_id := itemIdIn;
1874 approverRecord2Out.approver_category := ame_util.approvalApproverCategory ;
1875 approverRecord2Out.api_insertion := approverRecordIn.api_insertion;
1876 approverRecord2Out.authority := approverRecordIn.authority;
1877 approverRecord2Out.approval_status := approverRecordIn.approval_status;
1878 approverRecord2Out.action_type_id := approverRecordIn.approval_type_id;
1879 approverRecord2Out.group_or_chain_id := approverRecordIn.group_or_chain_id;
1880 approverRecord2Out.occurrence := approverRecordIn.occurrence;
1881 approverRecord2Out.source := approverRecordIn.source;
1882 /* initialize all order numbers to 1 */
1883 approverRecord2Out.item_class_order_number := 1;
1884 approverRecord2Out.item_order_number := 1;
1885 approverRecord2Out.sub_list_order_number := 1;
1886 approverRecord2Out.action_type_order_number := 1;
1887 approverRecord2Out.group_or_chain_order_number := 1;
1888 approverRecord2Out.member_order_number := 1;
1889 approverRecord2Out.approver_order_number := 1;
1890 exception
1891 when no_data_found then
1892 errorCode := -20001;
1893 errorMessage :=
1894 ame_util.getMessage(applicationShortNameIn =>'PER',
1895 messageNameIn => 'AME_400249_API_APR_REC_NULL');
1896 ame_util.runtimeException(packageNameIn => 'ame_util',
1897 routineNameIn => 'apprRecordToApprRecord2',
1898 exceptionNumberIn => errorCode,
1899 exceptionStringIn => errorMessage);
1900 approverRecord2Out.name := null ;
1901 raise_application_error(errorCode,
1902 errorMessage);
1903 when others then
1904 ame_util.runtimeException(packageNameIn => 'ame_util',
1905 routineNameIn => 'apprRecordToApprRecord2',
1906 exceptionNumberIn => sqlcode,
1907 exceptionStringIn => sqlerrm);
1908 approverRecord2Out := ame_util.emptyApproverRecord2;
1909 raise;
1910 end apprRecordToApprRecord2;
1911 --
1912 -- This procedure will translate the approver record from the new Type i.e.
1913 -- ame_util.approverRecord2 to ame_util.approverRecord
1914 --
1915 procedure apprRecord2ToApprRecord(approverRecord2In in ame_util.approverRecord2,
1916 approverRecordOut out nocopy ame_util.approverRecord) is
1917 errorCode integer;
1918 errorMessage ame_util.longestStringType;
1919 firstName per_all_people_f.first_name%type;
1920 lastName per_all_people_f.last_name%type;
1921 tempOrigSystem ame_util.stringType;
1922 tempOrigSystemId integer;
1923 userName fnd_user.user_name%type;
1924 wrongCategory exception;
1925 wrongItemClass exception;
1926 wrongOrigSystem exception;
1927 begin
1928 if approverRecord2In.item_class <> ame_util.headerItemClassName then
1929 raise wrongItemClass;
1930 end if;
1931 if approverRecord2In.approver_category <> ame_util.approvalApproverCategory then
1932 raise wrongCategory;
1933 end if;
1934 if approverRecord2In.orig_system is null or
1935 approverRecord2In.orig_system_id is null then
1936 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => approverRecord2In.name,
1937 origSystemOut => tempOrigSystem,
1938 origSystemIdOut => tempOrigSystemId);
1939 else
1940 tempOrigSystem := approverRecord2In.orig_system;
1941 tempOrigSystemId := approverRecord2In.orig_system_id;
1942 end if;
1943 if tempOrigSystem = ame_util.perOrigSystem then
1944 approverRecordOut.user_id := null;
1945 approverRecordOut.person_id := tempOrigSystemId;
1946 select pap.first_name
1947 ,pap.last_name
1948 into firstName
1949 ,lastName
1950 from per_all_people_f pap
1951 ,per_all_assignments_f pas
1952 where pap.person_id = approverRecordOut.person_id
1953 and pap.person_id = pas.person_id
1954 and pas.primary_flag = 'Y'
1955 and pas.assignment_type in ('E','C')
1956 and pas.assignment_status_type_id not in
1957 (select assignment_status_type_id
1958 from per_assignment_status_types
1959 where per_system_status = 'TERM_ASSIGN')
1960 and trunc(sysdate) between pas.effective_start_date and pas.effective_end_date
1961 and (
1962 trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
1963 or (pap.effective_start_date <= trunc(sysdate) and pap.effective_end_date is null)
1964 );
1965 approverRecordOut.first_name := firstName;
1966 approverRecordOut.last_name := lastName;
1967 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
1968 approverRecordOut.user_id := tempOrigSystemId;
1969 approverRecordOut.person_id := null;
1970 select user_name
1971 into userName
1972 from fnd_user
1973 where user_id = approverRecordOut.user_id and
1974 (sysdate between start_date and end_date or
1975 (start_date <= sysdate and end_date is null));
1976 approverRecordOut.first_name := userName;
1977 approverRecordOut.last_name := null;
1978 else
1979 raise wrongOrigSystem;
1980 end if;
1981 approverRecordOut.api_insertion := approverRecord2In.api_insertion;
1982 approverRecordOut.authority := approverRecord2In.authority;
1983 approverRecordOut.approval_status := approverRecord2In.approval_status;
1984 approverRecordOut.approval_type_id := approverRecord2In.action_type_id;
1985 approverRecordOut.group_or_chain_id := approverRecord2In.group_or_chain_id;
1986 approverRecordOut.occurrence := approverRecord2In.occurrence;
1987 approverRecordOut.source := approverRecord2In.source;
1988 exception
1989 when wrongOrigSystem then
1990 errorCode := -20001;
1991 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
1992 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
1993 tokenNameOneIn => 'ORIG_SYSTEM_ID',
1994 tokenValueOneIn => tempOrigSystem);
1995 ame_util.runtimeException(packageNameIn => 'ame_util',
1996 routineNameIn => 'apprRecord2ToApprRecord',
1997 exceptionNumberIn => errorCode,
1998 exceptionStringIn => errorMessage);
1999 raise_application_error(errorCode,
2000 errorMessage);
2001 when wrongCategory then
2002 errorCode := -20001;
2003 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2004 messageNameIn => 'AME_400416_APPR_FYI',
2005 tokenNameOneIn => 'NAME',
2006 tokenValueOneIn =>approverRecord2In.name );
2007 ame_util.runtimeException(packageNameIn => 'ame_util',
2008 routineNameIn => 'apprRecord2ToApprRecord',
2009 exceptionNumberIn => errorCode,
2010 exceptionStringIn => errorMessage);
2011 raise_application_error(errorCode,
2012 errorMessage);
2013 when wrongItemClass then
2014 errorCode := -20001;
2015 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2016 messageNameIn => 'AME_400417_APPR_NOT_HEADER_IC');
2017 ame_util.runtimeException(packageNameIn => 'ame_util',
2018 routineNameIn => 'apprRecord2ToApprRecord',
2019 exceptionNumberIn => errorCode,
2020 exceptionStringIn => errorMessage);
2021 raise_application_error(errorCode,
2022 errorMessage);
2023 when others then
2024 ame_util.runtimeException(packageNameIn => 'ame_util',
2025 routineNameIn => 'apprRecord2ToApprRecord',
2026 exceptionNumberIn => sqlcode,
2027 exceptionStringIn => sqlerrm);
2028 raise;
2029 end apprRecord2ToApprRecord;
2030 --
2031 -- This procedure will translate the approver table from type
2032 -- ame_util.approverTable to ame_util.approverTable2
2033 --
2034 procedure apprTableToApprTable2(approversTableIn in ame_util.approversTable,
2035 itemIdIn in varchar2 default null,
2036 approversTable2Out out nocopy ame_util.approversTable2) is
2037 ct integer;
2038 errorCode integer;
2039 errorMessage ame_util.longestStringType;
2040 wfName wf_roles.name%type;
2041 wfDisplayName wf_roles.display_name%type;
2042 tempOrigSystem wf_roles.orig_system%type;
2043 tempOrigSystemId wf_roles.orig_system_id%type;
2044 begin
2045 for ct in 1..approversTableIn.count loop
2046 if approversTableIn(ct).person_id is null then
2047 tempOrigSystem := ame_util.fndUserOrigSystem;
2048 tempOrigSystemId := approversTableIn(ct).user_id;
2049 else
2050 tempOrigSystem := ame_util.perOrigSystem;
2051 tempOrigSystemId := approversTableIn(ct).person_id;
2052 end if;
2053 approversTable2Out(ct).approver_category := ame_util.approvalApproverCategory ;
2054 approversTable2Out(ct).item_class := ame_util.headerItemClassName ;
2055 approversTable2Out(ct).item_id := itemIdIn ;
2056 approversTable2Out(ct).api_insertion := approversTableIn(ct).api_insertion;
2057 approversTable2Out(ct).authority := approversTableIn(ct).authority;
2058 approversTable2Out(ct).approval_status := approversTableIn(ct).approval_status;
2059 approversTable2Out(ct).action_type_id := approversTableIn(ct).approval_type_id;
2060 approversTable2Out(ct).group_or_chain_id := approversTableIn(ct).group_or_chain_id;
2061 approversTable2Out(ct).occurrence := approversTableIn(ct).occurrence;
2062 approversTable2Out(ct).source := approversTableIn(ct).source;
2063 if tempOrigSystem = ame_util.perOrigSystem then
2064 select name, display_name
2065 into wfName, wfDisplayName
2066 from wf_roles wf
2067 where orig_system = tempOrigSystem
2068 and orig_system_id = tempOrigSystemId
2069 and status = 'ACTIVE'
2070 and (expiration_date is null or sysdate < expiration_date)
2071 and exists (select null
2072 from fnd_user u
2073 where u.user_name = wf.name
2074 and trunc(sysdate) between u.start_date
2075 and nvl(u.end_date,trunc(sysdate)))
2076 and not exists (
2077 select null from wf_roles wf2
2078 where wf.orig_system = wf2.orig_system
2079 and wf.orig_system_id = wf2.orig_system_id
2080 and wf.start_date > wf2.start_date
2081 )
2082 and rownum < 2;
2083 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
2084 select name
2085 ,display_name
2086 ,orig_system
2087 ,orig_system_id
2088 into wfName
2089 ,wfDisplayName
2090 ,tempOrigSystem
2091 ,tempOrigSystemId
2092 from wf_roles wf
2093 where wf.orig_system in('FND_USR','PER')
2094 and wf.name in (select u.user_name
2095 from fnd_user u
2096 where u.user_id = tempOrigSystemId
2097 and trunc(sysdate) between u.start_date
2098 and nvl(u.end_date,trunc(sysdate)))
2099 and wf.status = 'ACTIVE'
2100 and (wf.expiration_date is null or sysdate < wf.expiration_date)
2101 -- need not check for proxy user in this case
2102 and rownum < 2;
2103 end if;
2104 approversTable2Out(ct).orig_system := tempOrigSystem;
2105 approversTable2Out(ct).orig_system_id := tempOrigSystemId;
2106 approversTable2Out(ct).name := wfName;
2107 approversTable2Out(ct).display_name := wfDisplayName;
2108 /* initialize all order numbers to 1 and approver_order_number to serial number */
2109 approversTable2Out(ct).item_class_order_number := 1;
2110 approversTable2Out(ct).item_order_number := 1;
2111 approversTable2Out(ct).sub_list_order_number := 1;
2112 approversTable2Out(ct).action_type_order_number := 1;
2113 approversTable2Out(ct).group_or_chain_order_number := 1;
2114 approversTable2Out(ct).member_order_number := 1;
2115 approversTable2Out(ct).approver_order_number := ct;
2116 end loop;
2117 exception
2118 when no_data_found then
2119 errorCode := -20001;
2120 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2121 messageNameIn => 'AME_400249_API_APR_REC_NULL');
2122 ame_util.runtimeException(packageNameIn => 'ame_util',
2123 routineNameIn => 'apprTableToApprTable2',
2124 exceptionNumberIn => errorCode,
2125 exceptionStringIn => errorMessage);
2126 approversTable2Out.delete;
2127 raise_application_error(errorCode,
2128 errorMessage);
2129 when others then
2130 ame_util.runtimeException(packageNameIn => 'ame_util',
2131 routineNameIn => 'apprTableToApprTable2',
2132 exceptionNumberIn => sqlcode,
2133 exceptionStringIn => sqlerrm);
2134 approversTable2Out.delete;
2135 raise;
2136 end apprTableToApprTable2;
2137 --
2138 -- This procedure will translate the approver table from the new Type i.e.
2139 -- ame_util.approverTable2 to ame_util.approverTable
2140 --
2141 procedure apprTable2ToApprTable(approversTable2In in ame_util.approversTable2,
2142 approversTableOut out nocopy ame_util.approversTable) is
2143 ct integer;
2144 errorCode integer;
2145 errorMessage ame_util.longestStringType;
2146 errorWfName ame_util.stringType;
2147 firstName per_all_people_f.first_name%type;
2148 lastName per_all_people_f.last_name%type;
2149 tempOrigSystem ame_util.stringType;
2150 tempOrigSystemId integer;
2151 userName fnd_user.user_name%type;
2152 wrongCategory exception;
2153 wrongItemClass exception;
2154 wrongOrigSystem exception;
2155 begin
2156 for ct in 1..approversTable2In.count loop
2157 if approversTable2In(ct).approver_category<>ame_util.approvalApproverCategory then
2158 errorWfName := approversTable2In(ct).name;
2159 raise wrongCategory;
2160 end if;
2161 if approversTable2In(ct).item_class <> ame_util.headerItemClassName then
2162 raise wrongItemClass;
2163 end if;
2164 if approversTable2In(ct).orig_system is null or
2165 approversTable2In(ct).orig_system_id is null then
2166 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => approversTable2In(ct).name,
2167 origSystemOut => tempOrigSystem,
2168 origSystemIdOut => tempOrigSystemId);
2169 else
2170 tempOrigSystem := approversTable2In(ct).orig_system;
2171 tempOrigSystemId := approversTable2In(ct).orig_system_id;
2172 end if;
2173 if tempOrigSystem = ame_util.perOrigSystem then
2174 approversTableOut(ct).user_id := null;
2175 approversTableOut(ct).person_id := tempOrigSystemId;
2176 select pap.first_name
2177 ,pap.last_name
2178 into firstName
2179 ,lastName
2180 from per_all_people_f pap
2181 ,per_all_assignments_f pas
2182 where pap.person_id = approversTableOut(ct).person_id
2183 and pap.person_id = pas.person_id
2184 and pas.primary_flag = 'Y'
2185 and pas.assignment_type in ('E','C')
2186 and pas.assignment_status_type_id not in
2187 (select assignment_status_type_id
2188 from per_assignment_status_types
2189 where per_system_status = 'TERM_ASSIGN')
2190 and trunc(sysdate) between pas.effective_start_date and pas.effective_end_date
2191 and (
2192 trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
2193 or (pap.effective_start_date <= trunc(sysdate) and pap.effective_end_date is null)
2194 );
2195 approversTableOut(ct).first_name := firstName;
2196 approversTableOut(ct).last_name := lastName;
2197 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
2198 approversTableOut(ct).user_id := tempOrigSystemId;
2199 approversTableOut(ct).person_id := null;
2200 select user_name
2201 into userName
2202 from fnd_user
2203 where user_id = approversTableOut(ct).user_id and
2204 (sysdate between start_date and end_date or
2205 (start_date <= sysdate and end_date is null));
2206 approversTableOut(ct).first_name := userName;
2207 approversTableOut(ct).last_name := null;
2208 else
2209 raise wrongOrigSystem;
2210 end if;
2211 approversTableOut(ct).api_insertion := approversTable2In(ct).api_insertion;
2212 approversTableOut(ct).authority := approversTable2In(ct).authority;
2213 approversTableOut(ct).approval_status := approversTable2In(ct).approval_status;
2214 approversTableOut(ct).approval_type_id := approversTable2In(ct).action_type_id;
2215 approversTableOut(ct).group_or_chain_id := approversTable2In(ct).group_or_chain_id;
2216 approversTableOut(ct).occurrence := approversTable2In(ct).occurrence;
2217 approversTableOut(ct).source := approversTable2In(ct).source;
2218 end loop;
2219 exception
2220 when wrongOrigSystem then
2221 errorCode := -20001;
2222 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2223 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
2224 tokenNameOneIn => 'ORIG_SYSTEM_ID',
2225 tokenValueOneIn => tempOrigSystem);
2226 ame_util.runtimeException(packageNameIn => 'ame_util',
2227 routineNameIn => 'apprTable2ToApprTable',
2228 exceptionNumberIn => errorCode,
2229 exceptionStringIn => errorMessage);
2230 approversTableOut.delete;
2231 raise_application_error(errorCode,
2232 errorMessage);
2233 when wrongCategory then
2234 errorCode := -20001;
2235 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2236 messageNameIn => 'AME_400416_APPR_FYI',
2237 tokenNameOneIn => 'NAME',
2238 tokenValueOneIn =>errorWfName );
2239 ame_util.runtimeException(packageNameIn => 'ame_util',
2240 routineNameIn => 'apprTable2ToApprTable',
2241 exceptionNumberIn => errorCode,
2242 exceptionStringIn => errorMessage);
2243 approversTableOut.delete;
2244 raise_application_error(errorCode,
2245 errorMessage);
2246 when wrongItemClass then
2247 errorCode := -20001;
2248 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
2249 messageNameIn => 'AME_400417_APPR_NOT_HEADER_IC');
2250 ame_util.runtimeException(packageNameIn => 'ame_util',
2251 routineNameIn => 'apprTable2ToApprTable',
2252 exceptionNumberIn => errorCode,
2253 exceptionStringIn => errorMessage);
2254 approversTableOut.delete;
2255 raise_application_error(errorCode,
2256 errorMessage);
2257 when others then
2258 ame_util.runtimeException(packageNameIn => 'ame_util',
2259 routineNameIn => 'apprTable2ToApprTable',
2260 exceptionNumberIn => sqlcode,
2261 exceptionStringIn => sqlerrm);
2262 approversTableOut.delete;
2263 end apprTable2ToApprTable;
2264 procedure autonomousLog(logIdIn number,
2265 packageNameIn in varchar2,
2266 routineNameIn in varchar2,
2267 exceptionNumberIn in integer,
2268 exceptionStringIn in varchar2,
2269 transactionIdIn in varchar2 default null,
2270 applicationIdIn in integer default null) as
2271 pragma autonomous_transaction;
2272 begin
2273 insert into ame_exceptions_log(
2274 log_id,
2275 package_name,
2276 routine_name,
2277 transaction_id,
2278 application_id,
2279 exception_number,
2280 exception_string) values(
2281 logIdIn,
2282 substrb(packageNameIn, 1, 50),
2283 substrb(routineNameIn, 1, 50),
2284 transactionIdIn,
2285 applicationIdIn,
2286 exceptionNumberIn,
2287 substrb(to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')||exceptionStringIn, 1, 4000));
2288 commit;
2289 exception
2290 when others then
2291 rollback;
2292 raise;
2293 end autonomousLog;
2294 procedure checkForSqlInjection(queryStringIn in varchar2) as
2295 errorCode integer;
2296 errorMessage ame_util.longestStringType;
2297 invalidStringException exception;
2298 keyword ame_util.stringType;
2299 keywordFoundException exception;
2300 keywords ame_util.stringList;
2301 lowerQueryString ame_util.longestStringType;
2302 tempLowerQueryString ame_util.longestStringType;
2303 tempConnectPos number ;
2304 begin
2305 if(upper(queryStringIn) like '%<SCRIPT>%') then
2306 raise invalidStringException;
2307 end if;
2308 lowerQueryString := removeReturns(stringIn => lower(queryStringIn),
2309 replaceWithSpaces => true);
2310 /* keywords lists all prohibited keywords, in lower case. */
2311 keywords(1) := 'delete';
2312 keywords(2) := 'insert';
2313 keywords(3) := 'update';
2314 keywords(4) := 'truncate';
2315 keywords(5) := 'drop';
2316 keywords(6) := 'grant';
2317 keywords(7) := 'execute';
2318 keywords(8) := 'set';
2319 keywords(9) := 'lock';
2320 keywords(10) := 'create';
2321 keywords(11) := 'alter';
2322 keywords(12) := 'commit';
2323 keywords(13) := 'connect';
2324 keywords(14) := 'rollback';
2325 keywords(15) := 'dbms_sql';
2326 keywords(16) := 'dbms_output';
2327 keywords(17) := 'htp';
2328 keywords(18) := 'htf';
2329 keywords(19) := 'owa_util';
2330 keywords(20) := 'owa_cookie';
2331 tempConnectPos := 0;
2332 for i in 1 .. keywords.count loop
2333 if lowerQueryString like 'connect ' or
2334 instrb(lowerQueryString, 'connect ') > 0 then
2335 tempLowerQueryString := lowerQueryString;
2336 tempLowerQueryString := replace(tempLowerQueryString,' ',' ');
2337 tempConnectPos := instrb(tempLowerQueryString,'connect ',1);
2338 while tempConnectPos > 0 loop
2339 tempLowerQueryString := trim(substr(tempLowerQueryString,tempConnectPos +7));
2340 if substrb(tempLowerQueryString,1,3) <> 'by ' then
2341 keyword := 'connect';
2342 raise keywordFoundException;
2343 end if;
2344 tempConnectPos := instrb(tempLowerQueryString,'connect ',1);
2345 end loop;
2346 elsif(lowerQueryString like (keywords(i) || ' ') or
2347 instrb(lowerQueryString, (keywords(i) || ' ')) > 0) then
2348 keyword := keywords(i);
2349 raise keywordFoundException;
2350 end if;
2351 end loop;
2352 exception
2353 when invalidStringException then
2354 errorCode := -20001;
2355 errorMessage :=
2356 ame_util.getMessage(applicationShortNameIn => 'PER',
2357 messageNameIn => 'AME_400456_NO_SCRIPT_TAG');
2358 runtimeException(packageNameIn => 'ame_util',
2359 routineNameIn => 'checkForSqlInjection',
2360 exceptionNumberIn => errorCode,
2361 exceptionStringIn => errorMessage);
2362 raise_application_error(errorCode,
2363 errorMessage);
2364 when keywordFoundException then
2365 errorCode := -20001;
2366 errorMessage := -- pa message
2367 'The following prohibited keyword occurs in the query you submitted: ' ||
2368 keyword ||
2369 '. ';
2370 runtimeException(packageNameIn => 'ame_util',
2371 routineNameIn => 'checkForSqlInjection',
2372 exceptionNumberIn => errorCode,
2373 exceptionStringIn => errorMessage);
2374 raise_application_error(errorCode, errorMessage);
2375 when others then
2376 runtimeException(packageNameIn => 'ame_util',
2377 routineNameIn => 'checkForSqlInjection',
2378 exceptionNumberIn => sqlcode,
2379 exceptionStringIn => sqlerrm);
2380 raise;
2381 end checkForSqlInjection;
2382 procedure compactIdList(idListInOut in out nocopy idList) as
2383 tempNextIndex integer;
2384 upperLimit integer;
2385 begin
2386 /* First, if the list is of size n, copy the list down into its first n slots. */
2387 upperLimit := idListInOut.count;
2388 if(upperLimit = 0) then
2389 return;
2390 end if;
2391 tempNextIndex := idListInOut.first;
2392 idListInOut(1) := idListInOut(tempNextIndex);
2393 if(upperLimit > 1) then
2394 for i in 2 .. upperLimit loop
2395 tempNextIndex := idListInOut.next(tempNextIndex);
2396 idListInOut(i) := idListInOut(tempNextIndex);
2397 end loop;
2398 end if;
2399 /* Second, delete all slots beyond the nth slot. */
2400 loop
2401 tempNextIndex := idListInOut.next(upperLimit);
2402 if(tempNextIndex is null) then
2403 exit;
2404 end if;
2405 idListInOut.delete(tempNextIndex);
2406 end loop;
2407 exception
2408 when others then
2409 runtimeException(packageNameIn => 'ame_util',
2410 routineNameIn => 'compactIdList',
2411 exceptionNumberIn => sqlcode,
2412 exceptionStringIn => sqlerrm);
2413 raise;
2414 end compactIdList;
2415 procedure compactLongestStringList(longestStringListInOut in out nocopy ame_util.longestStringList) as
2416 tempNextIndex integer;
2417 upperLimit integer;
2418 begin
2419 /* First, if the list is of size n, copy the list down into its first n slots. */
2420 upperLimit := longestStringListInOut.count;
2421 if(upperLimit = 0) then
2422 return;
2423 end if;
2424 tempNextIndex := longestStringListInOut.first;
2425 longestStringListInOut(1) := longestStringListInOut(tempNextIndex);
2426 if(upperLimit > 1) then
2427 for i in 2 .. upperLimit loop
2428 tempNextIndex := longestStringListInOut.next(tempNextIndex);
2429 longestStringListInOut(i) := longestStringListInOut(tempNextIndex);
2430 end loop;
2431 end if;
2432 /* Second, delete all slots beyond the nth slot. */
2433 loop
2434 tempNextIndex := longestStringListInOut.next(upperLimit);
2435 if(tempNextIndex is null) then
2436 exit;
2437 end if;
2438 longestStringListInOut.delete(tempNextIndex);
2439 end loop;
2440 exception
2441 when others then
2442 runtimeException(packageNameIn => 'ame_util',
2443 routineNameIn => 'compactLongestStringList',
2444 exceptionNumberIn => sqlcode,
2445 exceptionStringIn => sqlerrm );
2446 raise;
2447 end compactLongestStringList;
2448 procedure compactLongStringList(longStringListInOut in out nocopy ame_util.longStringList) as
2449 tempNextIndex integer;
2450 upperLimit integer;
2451 begin
2452 /* First, if the list is of size n, copy the list down into its first n slots. */
2453 upperLimit := longStringListInOut.count;
2454 if(upperLimit = 0) then
2455 return;
2456 end if;
2457 tempNextIndex := longStringListInOut.first;
2458 longStringListInOut(1) := longStringListInOut(tempNextIndex);
2459 if(upperLimit > 1) then
2460 for i in 2 .. upperLimit loop
2461 tempNextIndex := longStringListInOut.next(tempNextIndex);
2462 longStringListInOut(i) := longStringListInOut(tempNextIndex);
2463 end loop;
2464 end if;
2465 /* Second, delete all slots beyond the nth slot. */
2466 loop
2467 tempNextIndex := longStringListInOut.next(upperLimit);
2468 if(tempNextIndex is null) then
2469 exit;
2470 end if;
2471 longStringListInOut.delete(tempNextIndex);
2472 end loop;
2473 exception
2474 when others then
2475 runtimeException(packageNameIn => 'ame_util',
2476 routineNameIn => 'compactLongStringList',
2477 exceptionNumberIn => sqlcode,
2478 exceptionStringIn => sqlerrm );
2479 raise;
2480 end compactLongStringList;
2481 procedure compactStringList(stringListInOut in out nocopy ame_util.stringList) as
2482 tempNextIndex integer;
2483 upperLimit integer;
2484 begin
2485 /* First, if the list is of size n, copy the list down into its first n slots. */
2486 upperLimit := stringListInOut.count;
2487 if(upperLimit = 0) then
2488 return;
2489 end if;
2490 tempNextIndex := stringListInOut.first;
2491 stringListInOut(1) := stringListInOut(tempNextIndex);
2492 if(upperLimit > 1) then
2493 for i in 2 .. upperLimit loop
2494 tempNextIndex := stringListInOut.next(tempNextIndex);
2495 stringListInOut(i) := stringListInOut(tempNextIndex);
2496 end loop;
2497 end if;
2498 /* Second, delete all slots beyond the nth slot. */
2499 loop
2500 tempNextIndex := stringListInOut.next(upperLimit);
2501 if(tempNextIndex is null) then
2502 exit;
2503 end if;
2504 stringListInOut.delete(tempNextIndex);
2505 end loop;
2506 exception
2507 when others then
2508 runtimeException(packageNameIn => 'ame_util',
2509 routineNameIn => 'compactStringList',
2510 exceptionNumberIn => sqlcode,
2511 exceptionStringIn => sqlerrm );
2512 raise;
2513 end compactStringList;
2514 procedure convertApproversTableToValues(approversTableIn in ame_util.approversTable,
2515 personIdValuesOut out nocopy ame_util.idList,
2516 userIdValuesOut out nocopy ame_util.idList,
2517 apiInsertionValuesOut out nocopy ame_util.charList,
2518 authorityValuesOut out nocopy ame_util.charList,
2519 approvalTypeIdValuesOut out nocopy ame_util.idList,
2520 groupOrChainIdValuesOut out nocopy ame_util.idList,
2521 occurrenceValuesOut out nocopy ame_util.idList,
2522 sourceValuesOut out nocopy ame_util.longStringList,
2523 statusValuesOut out nocopy ame_util.stringList) as
2524 upperLimit integer;
2525 begin
2526 upperLimit := approversTableIn.count;
2527 for i in 1 .. upperLimit loop
2528 personIdValuesOut(i) := approversTableIn(i).person_id;
2529 userIdValuesOut(i) := approversTableIn(i).user_id;
2530 apiInsertionValuesOut(i) := approversTableIn(i).api_insertion;
2531 authorityValuesOut(i) := approversTableIn(i).authority;
2532 approvalTypeIdValuesOut(i) := approversTableIn(i).approval_type_id;
2533 groupOrChainIdValuesOut(i) := approversTableIn(i).group_or_chain_id;
2534 occurrenceValuesOut(i) := approversTableIn(i).occurrence;
2535 sourceValuesOut(i) := approversTableIn(i).source;
2536 statusValuesOut(i) := approversTableIn(i).approval_status;
2537 end loop;
2538 exception
2539 when others then
2540 runtimeException(packageNameIn => 'ame_util',
2541 routineNameIn => 'convertApproverTableToValues',
2542 exceptionNumberIn => sqlcode,
2543 exceptionStringIn => sqlerrm);
2544 apiInsertionValuesOut.delete;
2545 authorityValuesOut.delete;
2546 personIdValuesOut.delete;
2547 statusValuesOut.delete;
2548 userIdValuesOut.delete;
2549 approvalTypeIdValuesOut.delete;
2550 groupOrChainIdValuesOut.delete;
2551 occurrenceValuesOut.delete;
2552 sourceValuesOut.delete;
2553 raise;
2554 end convertApproversTableToValues;
2555 procedure convertApproversTable2ToValues(approversTableIn in ame_util.approversTable2,
2556 namesOut out nocopy ame_util.longStringList,
2557 itemClassesOut out nocopy ame_util.stringList,
2558 itemIdsOut out nocopy ame_util.stringList,
2559 apiInsertionsOut out nocopy ame_util.charList,
2560 authoritiesOut out nocopy ame_util.charList,
2561 actionTypeIdsOut out nocopy ame_util.idList,
2562 groupOrChainIdsOut out nocopy ame_util.idList,
2563 occurrencesOut out nocopy ame_util.idList,
2564 approverCategoriesOut out nocopy ame_util.charList,
2565 statusesOut out nocopy ame_util.stringList) as
2566 begin
2567 for i in 1 .. approversTableIn.count loop
2568 namesOut(i) := approversTableIn(i).name;
2569 itemClassesOut(i) := approversTableIn(i).item_class;
2570 itemIdsOut(i) := approversTableIn(i).item_id;
2571 apiInsertionsOut(i) := approversTableIn(i).api_insertion;
2572 authoritiesOut(i) := approversTableIn(i).authority;
2573 actionTypeIdsOut(i) := approversTableIn(i).action_type_id;
2574 groupOrChainIdsOut(i) := approversTableIn(i).group_or_chain_id;
2575 occurrencesOut(i) := approversTableIn(i).occurrence;
2576 approverCategoriesOut(i) := approversTableIn(i).approver_category;
2577 statusesOut(i) := approversTableIn(i).approval_status;
2578 end loop;
2579 exception
2580 when others then
2581 runtimeException(packageNameIn => 'ame_util',
2582 routineNameIn => 'convertApproversTable2ToValues',
2583 exceptionNumberIn => sqlcode,
2584 exceptionStringIn => sqlerrm);
2585 raise;
2586 end convertApproversTable2ToValues;
2587 procedure convertValuesToApproversTable(personIdValuesIn in ame_util.idList,
2588 userIdValuesIn in ame_util.idList,
2589 apiInsertionValuesIn in ame_util.charList,
2590 authorityValuesIn in ame_util.charList,
2591 approvalTypeIdValuesIn in ame_util.idList,
2592 groupOrChainIdValuesIn in ame_util.idList,
2593 occurrenceValuesIn in ame_util.idList,
2594 sourceValuesIn in ame_util.longStringList,
2595 statusValuesIn in ame_util.stringList,
2596 approversTableOut out nocopy ame_util.approversTable) as
2597 badCountException exception;
2598 errorCode integer;
2599 errorMessage ame_util.longestStringType;
2600 upperLimit integer;
2601 begin
2602 upperLimit := personIdValuesIn.count;
2603 if(upperLimit <> userIdValuesIn.count or
2604 upperLimit <> apiInsertionValuesIn.count or
2605 upperLimit <> authorityValuesIn.count or
2606 upperLimit <> approvalTypeIdValuesIn.count or
2607 upperLimit <> groupOrChainIdValuesIn.count or
2608 upperLimit <> occurrenceValuesIn.count or
2609 upperLimit <> sourceValuesIn.count or
2610 upperLimit <> statusValuesIn.count) then
2611 raise badCountException;
2612 end if;
2613 for i in 1 .. upperLimit loop
2614 approversTableOut(i).person_id := personIdValuesIn(i);
2615 approversTableOut(i).user_id := userIdValuesIn(i);
2616 approversTableOut(i).api_insertion := apiInsertionValuesIn(i);
2617 approversTableOut(i).authority := authorityValuesIn(i);
2618 approversTableOut(i).approval_type_id := approvalTypeIdValuesIn(i);
2619 approversTableOut(i).group_or_chain_id := groupOrChainIdValuesIn(i);
2620 approversTableOut(i).occurrence := occurrenceValuesIn(i);
2621 approversTableOut(i).source := sourceValuesIn(i);
2622 approversTableOut(i).approval_status := statusValuesIn(i);
2623 approversTableOut(i).first_name := null;
2624 approversTableOut(i).last_name := null;
2625 end loop;
2626 exception
2627 when badCountException then
2628 errorCode := -20001;
2629 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2630 messageNameIn => 'AME_400222_UTL_TAB_DIFF_SIZE');
2631 runtimeException(packageNameIn => 'ame_util',
2632 routineNameIn => 'convertValuesToApproversTable',
2633 exceptionNumberIn => errorCode,
2634 exceptionStringIn => errorMessage);
2635 raise_application_error(errorCode, errorMessage);
2636 when others then
2637 runtimeException(packageNameIn => 'ame_util',
2638 routineNameIn => 'convertValuesToApproversTable',
2639 exceptionNumberIn => sqlcode,
2640 exceptionStringIn => sqlerrm);
2641 approversTableOut.delete;
2642 raise;
2643 end convertValuesToApproversTable;
2644 procedure convertValuesToApproversTable2(nameValuesIn in ame_util.longStringList,
2645 approverCategoryValuesIn in ame_util.charList,
2646 apiInsertionValuesIn in ame_util.charList,
2647 authorityValuesIn in ame_util.charList,
2648 approvalTypeIdValuesIn in ame_util.idList,
2649 groupOrChainIdValuesIn in ame_util.idList,
2650 occurrenceValuesIn in ame_util.idList,
2651 sourceValuesIn in ame_util.longStringList,
2652 statusValuesIn in ame_util.stringList,
2653 approversTableOut out nocopy ame_util.approversTable2) as
2654 badCountException exception;
2655 errorCode integer;
2656 errorMessage ame_util.longestStringType;
2657 upperLimit integer;
2658 begin
2659 upperLimit := nameValuesIn.count;
2660 if(upperLimit <> approverCategoryValuesIn.count or
2661 upperLimit <> apiInsertionValuesIn.count or
2662 upperLimit <> authorityValuesIn.count or
2663 upperLimit <> approvalTypeIdValuesIn.count or
2664 upperLimit <> groupOrChainIdValuesIn.count or
2665 upperLimit <> occurrenceValuesIn.count or
2666 upperLimit <> sourceValuesIn.count or
2667 upperLimit <> statusValuesIn.count) then
2668 raise badCountException;
2669 end if;
2670 for i in 1 .. upperLimit loop
2671 approversTableOut(i).name := nameValuesIn(i);
2672 approversTableOut(i).approver_category := approverCategoryValuesIn(i);
2673 approversTableOut(i).api_insertion := apiInsertionValuesIn(i);
2674 approversTableOut(i).authority := authorityValuesIn(i);
2675 approversTableOut(i).action_type_id := approvalTypeIdValuesIn(i);
2676 approversTableOut(i).group_or_chain_id := groupOrChainIdValuesIn(i);
2677 approversTableOut(i).occurrence := occurrenceValuesIn(i);
2678 approversTableOut(i).source := sourceValuesIn(i);
2679 approversTableOut(i).approval_status := statusValuesIn(i);
2680 approversTableOut(i).orig_system := null;
2681 approversTableOut(i).orig_system_id := null;
2682 approversTableOut(i).display_name := null;
2683 end loop;
2684 exception
2685 when badCountException then
2686 errorCode := -20001;
2687 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
2688 messageNameIn => 'AME_400222_UTL_TAB_DIFF_SIZE');
2689 runtimeException(packageNameIn => 'ame_util',
2690 routineNameIn => 'convertValuesToApproversTable',
2691 exceptionNumberIn => errorCode,
2692 exceptionStringIn => errorMessage);
2693 raise_application_error(errorCode, errorMessage);
2694 when others then
2695 runtimeException(packageNameIn => 'ame_util',
2696 routineNameIn => 'convertValuesToApproversTable',
2697 exceptionNumberIn => sqlcode,
2698 exceptionStringIn => sqlerrm);
2699 approversTableOut.delete;
2700 raise;
2701 end convertValuesToApproversTable2;
2702 procedure copyApproverRecord2(approverRecord2In in approverRecord2,
2703 approverRecord2Out out nocopy approverRecord2) as
2704 begin
2705 approverRecord2Out.name := approverRecord2In.name;
2706 approverRecord2Out.orig_system := approverRecord2In.orig_system;
2707 approverRecord2Out.orig_system_id := approverRecord2In.orig_system_id;
2708 approverRecord2Out.display_name := approverRecord2In.display_name;
2709 approverRecord2Out.approver_category := approverRecord2In.approver_category;
2710 approverRecord2Out.api_insertion := approverRecord2In.api_insertion;
2711 approverRecord2Out.authority := approverRecord2In.authority;
2712 approverRecord2Out.approval_status := approverRecord2In.approval_status;
2713 approverRecord2Out.action_type_id := approverRecord2In.action_type_id;
2714 approverRecord2Out.group_or_chain_id := approverRecord2In.group_or_chain_id;
2715 approverRecord2Out.occurrence := approverRecord2In.occurrence;
2716 approverRecord2Out.source := approverRecord2In.source;
2717 approverRecord2Out.item_class := approverRecord2In.item_class;
2718 approverRecord2Out.item_id := approverRecord2In.item_id;
2719 approverRecord2Out.item_class_order_number := approverRecord2In.item_class_order_number;
2720 approverRecord2Out.item_order_number := approverRecord2In.item_order_number;
2721 approverRecord2Out.sub_list_order_number := approverRecord2In.sub_list_order_number;
2722 approverRecord2Out.action_type_order_number := approverRecord2In.action_type_order_number;
2723 approverRecord2Out.group_or_chain_order_number := approverRecord2In.group_or_chain_order_number;
2724 approverRecord2Out.member_order_number := approverRecord2In.member_order_number;
2725 approverRecord2Out.approver_order_number := approverRecord2In.approver_order_number;
2726 exception
2727 when others then
2728 runtimeException(packageNameIn => 'ame_util',
2729 routineNameIn => 'copyApproverRecord2',
2730 exceptionNumberIn => sqlcode,
2731 exceptionStringIn => sqlerrm);
2732 raise;
2733 end copyApproverRecord2;
2734 procedure copyApproversTable2(approversTable2In in approversTable2,
2735 approversTable2Out out nocopy approversTable2) as
2736 tempIndex integer;
2737 begin
2738 tempIndex := approversTable2In.first;
2739 while (tempIndex is not null) loop
2740 approversTable2Out(tempIndex).name := approversTable2In(tempIndex).name;
2741 approversTable2Out(tempIndex).orig_system := approversTable2In(tempIndex).orig_system;
2742 approversTable2Out(tempIndex).orig_system_id := approversTable2In(tempIndex).orig_system_id;
2743 approversTable2Out(tempIndex).display_name := approversTable2In(tempIndex).display_name;
2744 approversTable2Out(tempIndex).approver_category := approversTable2In(tempIndex).approver_category;
2745 approversTable2Out(tempIndex).api_insertion := approversTable2In(tempIndex).api_insertion;
2746 approversTable2Out(tempIndex).authority := approversTable2In(tempIndex).authority;
2747 approversTable2Out(tempIndex).approval_status := approversTable2In(tempIndex).approval_status;
2748 approversTable2Out(tempIndex).action_type_id := approversTable2In(tempIndex).action_type_id;
2749 approversTable2Out(tempIndex).group_or_chain_id := approversTable2In(tempIndex).group_or_chain_id;
2750 approversTable2Out(tempIndex).occurrence := approversTable2In(tempIndex).occurrence;
2751 approversTable2Out(tempIndex).source := approversTable2In(tempIndex).source;
2752 approversTable2Out(tempIndex).item_class := approversTable2In(tempIndex).item_class;
2753 approversTable2Out(tempIndex).item_id := approversTable2In(tempIndex).item_id;
2754 approversTable2Out(tempIndex).item_class_order_number := approversTable2In(tempIndex).item_class_order_number;
2755 approversTable2Out(tempIndex).item_order_number := approversTable2In(tempIndex).item_order_number;
2756 approversTable2Out(tempIndex).sub_list_order_number := approversTable2In(tempIndex).sub_list_order_number;
2757 approversTable2Out(tempIndex).action_type_order_number := approversTable2In(tempIndex).action_type_order_number;
2758 approversTable2Out(tempIndex).group_or_chain_order_number := approversTable2In(tempIndex).group_or_chain_order_number;
2759 approversTable2Out(tempIndex).member_order_number := approversTable2In(tempIndex).member_order_number;
2760 approversTable2Out(tempIndex).approver_order_number := approversTable2In(tempIndex).approver_order_number;
2761 tempIndex := approversTable2In.next(tempIndex);
2762 end loop;
2763 exception
2764 when others then
2765 runtimeException(packageNameIn => 'ame_util',
2766 routineNameIn => 'copyApproversTable2',
2767 exceptionNumberIn => sqlcode,
2768 exceptionStringIn => sqlerrm);
2769 approversTable2Out.delete;
2770 raise;
2771 end copyApproversTable2;
2772 procedure copyCharList(charListIn in charList,
2773 charListOut out nocopy charList) as
2774 tempIndex integer;
2775 begin
2776 tempIndex := charListIn.first;
2777 while (tempIndex is not null) loop
2778 charListOut(tempIndex) := charListIn(tempIndex);
2779 tempIndex := charListIn.next(tempIndex);
2780 end loop;
2781 exception
2782 when others then
2783 runtimeException(packageNameIn => 'ame_util',
2784 routineNameIn => 'copyCharList',
2785 exceptionNumberIn => sqlcode,
2786 exceptionStringIn => sqlerrm);
2787 charListOut.delete;
2788 raise;
2789 end copyCharList;
2790 procedure copyIdList(idListIn in idList,
2791 idListOut out nocopy idList) as
2792 tempIndex integer;
2793 begin
2794 tempIndex := idListIn.first;
2795 while (tempIndex is not null) loop
2796 idListOut(tempIndex) := idListIn(tempIndex);
2797 tempIndex := idListIn.next(tempIndex);
2798 end loop;
2799 exception
2800 when others then
2801 runtimeException(packageNameIn => 'ame_util',
2802 routineNameIn => 'copyIdList',
2803 exceptionNumberIn => sqlcode,
2804 exceptionStringIn => sqlerrm);
2805 idListOut.delete;
2806 raise;
2807 end copyIdList;
2808 procedure copyLongStringList(longStringListIn in longStringList,
2809 longStringListOut out nocopy longStringList) as
2810 tempIndex integer;
2811 begin
2812 tempIndex := longStringListIn.first;
2813 while (tempIndex is not null) loop
2814 longStringListOut(tempIndex) := longStringListIn(tempIndex);
2815 tempIndex := longStringListIn.next(tempIndex);
2816 end loop;
2817 exception
2818 when others then
2819 runtimeException(packageNameIn => 'ame_util',
2820 routineNameIn => 'copyLongStringList',
2821 exceptionNumberIn => sqlcode,
2822 exceptionStringIn => sqlerrm);
2823 longStringListOut.delete;
2824 raise;
2825 end copyLongStringList;
2826 procedure copyStringList(stringListIn in stringList,
2827 stringListOut out nocopy stringList) as
2828 tempIndex integer;
2829 begin
2830 tempIndex := stringListIn.first;
2831 while (tempIndex is not null) loop
2832 stringListOut(tempIndex) := stringListIn(tempIndex);
2833 tempIndex := stringListIn.next(tempIndex);
2834 end loop;
2835 exception
2836 when others then
2837 runtimeException(packageNameIn => 'ame_util',
2838 routineNameIn => 'copyStringList',
2839 exceptionNumberIn => sqlcode,
2840 exceptionStringIn => sqlerrm);
2841 stringListOut.delete;
2842 raise;
2843 end copyStringList;
2844 procedure deserializeLongStringList(longStringListIn in varchar2,
2845 longStringListOut out nocopy longStringList) as
2846 currentRecordEnd integer;
2847 currentRecordStart integer;
2848 inputLength integer;
2849 tempIndex integer;
2850 recordDelimiter varchar2(1);
2851 begin
2852 recordDelimiter := ame_util.recordDelimiter;
2853 tempIndex := 0;
2854 currentRecordEnd := 0;
2855 inputLength := lengthb(longStringListIn);
2856 loop
2857 /* Find the next record or exit. */
2858 if(currentRecordEnd = inputLength) then
2859 exit;
2860 end if;
2861 currentRecordStart := currentRecordEnd + 1;
2862 currentRecordEnd := instrb(longStringListIn, recordDelimiter, currentRecordStart, 1);
2863 tempIndex := tempIndex + 1;
2864 longStringListOut(tempIndex) :=
2865 substrb(longStringListIn, currentRecordStart, currentRecordEnd - currentRecordStart);
2866 end loop;
2867 exception
2868 when others then
2869 runtimeException(packageNameIn => 'ame_util',
2870 routineNameIn => 'deserializeLongStringList',
2871 exceptionNumberIn => sqlcode,
2872 exceptionStringIn => sqlerrm);
2873 longStringListOut := emptyLongStringList;
2874 raise;
2875 end deserializeLongStringList;
2876 procedure getAllowedAppIds(applicationIdsOut out nocopy ame_util.stringList,
2877 applicationNamesOut out nocopy ame_util.stringList) as
2878 cursor callingAppCursor is
2879 select
2880 application_id,
2881 application_name
2882 from ame_calling_apps
2883 where
2884 sysdate between start_date and
2885 nvl(end_date - ame_util.oneSecond, sysdate)
2886 order by application_name;
2887 cursor securedAttributesCursor(userId in integer) is
2888 select
2889 ak_web_user_sec_attr_values.number_value,
2890 ame_calling_apps.application_name
2891 from ak_web_user_sec_attr_values,
2892 ame_calling_apps
2893 where ak_web_user_sec_attr_values.number_value = ame_calling_apps.application_id and
2894 web_user_id = userId and
2895 sysdate between ame_calling_apps.start_date and
2896 nvl(ame_calling_apps.end_date - ame_util.oneSecond, sysdate)
2897 order by application_name;
2898 tempIndex integer;
2899 badRespException exception;
2900 noRespoTransException exception;
2901 noTransactionTypeException exception;
2902 errorCode integer;
2903 errorMessage ame_util.longestStringType;
2904 highestResponsibility integer;
2905 securedAttributeList icx_sec.g_num_tbl_type;
2906 responsibilityCount integer;
2907 responsibilityList icx_sec.g_responsibility_list;
2908 userId fnd_user.user_id%type;
2909 begin
2910 highestResponsibility := getHighestResponsibility;
2911 userId := fnd_global.user_id;
2912 tempIndex := 1;
2913 if highestResponsibility = ame_util.noResponsibility then
2914 applicationIdsOut := ame_util.emptyStringList;
2915 applicationNamesOut := ame_util.emptyStringList;
2916 elsif highestResponsibility = ame_util.limBusResponsibility then
2917 for securedAttributes in securedAttributesCursor(userId => userId) loop
2918 /* The explicit conversion below lets nocopy work. */
2919 applicationIdsOut(tempIndex) := to_char(securedAttributes.number_value);
2920 applicationNamesOut(tempIndex) := securedAttributes.application_name;
2921 tempIndex := tempIndex + 1;
2922 end loop;
2923 if tempIndex = 1 then
2924 raise noRespoTransException;
2925 end if;
2926 elsif
2927 (highestResponsibility = ame_util.genBusResponsibility or
2928 highestResponsibility = ame_util.appAdminResponsibility or
2929 highestResponsibility = ame_util.developerResponsibility) then
2930 for tempApp in callingAppCursor loop
2931 /* The explicit conversion below lets nocopy work. */
2932 applicationIdsOut(tempIndex) := to_char(tempApp.application_id);
2933 applicationNamesOut(tempIndex) := tempApp.application_name;
2934 tempIndex := tempIndex + 1;
2935 end loop;
2936 if tempIndex = 1 then
2937 raise noTransactionTypeException;
2938 end if;
2939 else
2940 raise badRespException;
2941 end if;
2942 exception
2943 when noRespoTransException then
2944 errorCode := -20001;
2945 errorMessage :=
2946 ame_util.getMessage(applicationShortNameIn => 'PER',
2947 messageNameIn => 'AME_400133_UIN_NO_APPL_ACC');
2948 runtimeException(packageNameIn => 'ame_util',
2949 routineNameIn => 'getAllowedAppIds',
2950 exceptionNumberIn => errorCode,
2951 exceptionStringIn => errorMessage);
2952 raise_application_error(errorCode,
2953 errorMessage);
2954 when noTransactionTypeException then
2955 errorCode := -20001;
2956 errorMessage :=
2957 ame_util.getMessage(applicationShortNameIn => 'PER',
2958 messageNameIn => 'AME_400321_UIN_NO_TRANS_TYPE');
2959 runtimeException(packageNameIn => 'ame_util',
2960 routineNameIn => 'getAllowedAppIds',
2961 exceptionNumberIn => errorCode,
2962 exceptionStringIn => errorMessage);
2963 raise_application_error(errorCode,
2964 errorMessage);
2965 when badRespException then
2966 errorCode := -20001;
2967 errorMessage :=
2968 ame_util.getMessage(applicationShortNameIn => 'PER',
2969 messageNameIn => 'AME_400223_UTL_UNREC_ERROR');
2970 runtimeException(packageNameIn => 'ame_util',
2971 routineNameIn => 'getAllowedAppIds',
2972 exceptionNumberIn => errorCode,
2973 exceptionStringIn => errorMessage);
2974 raise_application_error(errorCode,
2975 errorMessage);
2976 when others then
2977 runtimeException(packageNameIn => 'ame_util',
2978 routineNameIn => 'getAllowedAppIds',
2979 exceptionNumberIn => sqlcode,
2980 exceptionStringIn => sqlerrm);
2981 applicationIdsOut := emptyStringList;
2982 applicationNamesOut := emptyStringList;
2983 raise;
2984 end getAllowedAppIds;
2985 procedure getApplicationList(applicationListOut out nocopy idStringTable) as
2986 cursor callingAppCursor is
2987 select
2988 application_id,
2989 application_name
2990 from ame_calling_apps
2991 where
2992 sysdate between start_date and
2993 nvl(end_date - ame_util.oneSecond, sysdate)
2994 order by application_name;
2995 tempIndex integer;
2996 begin
2997 tempIndex := 1;
2998 for tempApp in callingAppCursor loop
2999 applicationListOut(tempIndex).id := tempApp.application_id;
3000 applicationListOut(tempIndex).string := tempApp.application_name;
3001 tempIndex := tempIndex + 1;
3002 end loop;
3003 exception
3004 when others then
3005 runtimeException(packageNameIn => 'ame_util',
3006 routineNameIn => 'getApplicationList',
3007 exceptionNumberIn => sqlcode,
3008 exceptionStringIn => sqlerrm);
3009 applicationListOut := emptyIdStringTable;
3010 raise;
3011 end getApplicationList;
3012 procedure getApplicationList2(applicationIdListOut out nocopy stringList,
3013 applicationNameListOut out nocopy stringList) as
3014 cursor callingAppCursor is
3015 select
3016 application_id,
3017 application_name
3018 from ame_calling_apps
3019 where
3020 sysdate between start_date and
3021 nvl(end_date - ame_util.oneSecond, sysdate)
3022 order by application_name;
3023 tempIndex integer;
3024 begin
3025 tempIndex := 1;
3026 for tempApp in callingAppCursor loop
3027 /* The explicit conversion below lets nocopy work. */
3028 applicationIdListOut(tempIndex) := to_char(tempApp.application_id);
3029 applicationNameListOut(tempIndex) := tempApp.application_name;
3030 tempIndex := tempIndex + 1;
3031 end loop;
3032 exception
3033 when others then
3034 runtimeException(packageNameIn => 'ame_util',
3035 routineNameIn => 'getApplicationList2',
3036 exceptionNumberIn => sqlcode,
3037 exceptionStringIn => sqlerrm);
3038 applicationIdListOut := emptyStringList;
3039 applicationNameListOut := emptyStringList;
3040 raise;
3041 end getApplicationList2;
3042 procedure getApplicationList3(applicationIdIn in integer,
3043 applicationIdListOut out nocopy stringList,
3044 applicationNameListOut out nocopy stringList) as
3045 cursor callingAppCursor(applicationIdIn in integer) is
3046 select
3047 application_id,
3048 application_name
3049 from ame_calling_apps
3050 where
3051 application_id <> applicationIdIn and
3052 sysdate between start_date and
3053 nvl(end_date - ame_util.oneSecond, sysdate)
3054 order by application_name;
3055 tempIndex integer;
3056 begin
3057 tempIndex := 1;
3058 for tempApp in callingAppCursor(applicationIdIn => applicationIdIn) loop
3059 /* The explicit conversion below lets nocopy work. */
3060 applicationIdListOut(tempIndex) := to_char(tempApp.application_id);
3061 applicationNameListOut(tempIndex) := tempApp.application_name;
3062 tempIndex := tempIndex + 1;
3063 end loop;
3064 exception
3065 when others then
3066 runtimeException(packageNameIn => 'ame_util',
3067 routineNameIn => 'getApplicationList3',
3068 exceptionNumberIn => sqlcode,
3069 exceptionStringIn => sqlerrm);
3070 applicationIdListOut := emptyStringList;
3071 applicationNameListOut := emptyStringList;
3072 raise;
3073 end getApplicationList3;
3074 procedure getConversionTypes(conversionTypesOut out nocopy ame_util.stringList) as
3075 cursor conversionTypeCursor is
3076 select distinct conversion_type
3077 from gl_daily_conversion_types;
3078 tempIndex integer;
3079 begin
3080 tempIndex := 1;
3081 for tempType in conversionTypeCursor loop
3082 conversionTypesOut(tempIndex) := tempType.conversion_type;
3083 tempIndex := tempIndex + 1;
3084 end loop;
3085 exception
3086 when others then
3087 runtimeException(packageNameIn => 'ame_util',
3088 routineNameIn => 'getConversionTypes',
3089 exceptionNumberIn => sqlcode,
3090 exceptionStringIn => sqlerrm);
3091 conversionTypesOut := emptyStringList;
3092 raise;
3093 end getConversionTypes;
3094 procedure getCurrencyCodes(currencyCodesOut out nocopy ame_util.stringList) as
3095 cursor codeCursor is
3096 select currency_code
3097 from fnd_currencies_active_v;
3098 tempIndex integer;
3099 begin
3100 tempIndex := 1;
3101 for tempCurrencyCode in codeCursor loop
3102 currencyCodesOut(tempIndex) := tempCurrencyCode.currency_code;
3103 tempIndex := tempIndex + 1;
3104 end loop;
3105 exception
3106 when others then
3107 runtimeException(packageNameIn => 'ame_util',
3108 routineNameIn => 'getCurrencyCodes',
3109 exceptionNumberIn => sqlcode,
3110 exceptionStringIn => sqlerrm);
3111 currencyCodesOut := emptyStringList;
3112 raise;
3113 end getCurrencyCodes;
3114 procedure getCurrencies(currencyCodesOut out nocopy ame_util.stringList,
3115 currencyNamesOut out nocopy ame_util.stringList) as
3116 cursor currencyCursor is
3117 select
3118 name,
3119 currency_code
3120 from
3121 fnd_currencies_active_v
3122 order by currency_code;
3123 tempIndex integer;
3124 begin
3125 tempIndex := 1;
3126 for tempCurrency in currencyCursor loop
3127 currencyCodesOut(tempIndex) := tempCurrency.currency_code;
3128 currencyNamesOut(tempIndex) :=
3129 tempCurrency.currency_code ||
3130 ' (' || tempCurrency.name || ')';
3131 tempIndex := tempIndex + 1;
3132 end loop;
3133 exception
3134 when others then
3135 runtimeException(packageNameIn => 'ame_util',
3136 routineNameIn => 'getCurrencies',
3137 exceptionNumberIn => sqlcode,
3138 exceptionStringIn => sqlerrm);
3139 currencyCodesOut := emptyStringList;
3140 currencyCodesOut := ame_util.emptyStringList;
3141 currencyNamesOut := ame_util.emptyStringList;
3142 raise;
3143 end getCurrencies;
3144 procedure getFndApplicationId(applicationIdIn in integer,
3145 fndApplicationIdOut out nocopy integer,
3146 transactionTypeIdOut out nocopy varchar2) as
3147 begin
3148 select
3149 fnd_application_id,
3150 transaction_type_id
3151 into
3152 fndApplicationIdOut,
3153 transactionTypeIdOut
3154 from ame_calling_apps
3155 where
3156 application_id = applicationIdIn and
3157 /* Don't use tempEffectiveRuleDate here. */
3158 sysdate between
3159 start_date and
3160 nvl(end_date - ame_util.oneSecond, sysdate);
3161 exception
3162 when others then
3163 runtimeException(packageNameIn => 'ame_util',
3164 routineNameIn => 'getFndApplicationId',
3165 exceptionNumberIn => sqlcode,
3166 exceptionStringIn => sqlerrm);
3167 fndApplicationIdOut := null;
3168 transactionTypeIdOut := null;
3169 raise;
3170 end getFndApplicationId;
3171 procedure getWorkflowAttributeValues(applicationIdIn in integer,
3172 transactionIdIn in varchar2,
3173 workflowItemKeyOut out nocopy varchar2,
3174 workflowItemTypeOut out nocopy varchar2) as
3175 attributeName ame_attributes.name%type;
3176 dynamicQuery varchar2(4000);
3177 errorCode integer;
3178 errorMessage longestStringType;
3179 workflowItemKeyAttId integer;
3180 workflowItemTypeAttId integer;
3181 begin
3182 /* Fetch the Workflow item key. */
3183 attributeName := workflowItemKeyAttribute;
3184 workflowItemKeyAttId := ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.workflowItemKeyAttribute);
3185 dynamicQuery := ame_attribute_pkg.getQueryString(attributeIdIn => workflowItemKeyAttId,
3186 applicationIdIn => applicationIdIn);
3187 if(ame_attribute_pkg.getStaticUsage(attributeIdIn => workflowItemKeyAttId,
3188 applicationIdIn => applicationIdIn) = ame_util.booleanTrue) then
3189 workflowItemKeyOut := dynamicQuery;
3190 else
3191 if(instrb(dynamicQuery, ame_util.transactionIdPlaceholder) = 0) then /* The bind variable is not present. */
3192 execute immediate dynamicQuery into workflowItemKeyOut;
3193 else /* The bind variable is present. */
3194 execute immediate dynamicQuery into workflowItemKeyOut using in transactionIdIn;
3195 end if;
3196 end if;
3197 /* Fetch the Workflow item type. */
3198 attributeName := workflowItemTypeAttribute;
3199 workflowItemTypeAttId := ame_attribute_pkg.getIdByName(attributeNameIn => ame_util.workflowItemTypeAttribute);
3200 dynamicQuery := ame_attribute_pkg.getQueryString(attributeIdIn => workflowItemTypeAttId,
3201 applicationIdIn => applicationIdIn);
3202 if(ame_attribute_pkg.getStaticUsage(attributeIdIn => workflowItemTypeAttId,
3203 applicationIdIn => applicationIdIn) = ame_util.booleanTrue) then
3204 workflowItemTypeOut := dynamicQuery;
3205 else
3206 if(instrb(dynamicQuery, ame_util.transactionIdPlaceholder) = 0) then /* The bind variable is not present. */
3207 execute immediate dynamicQuery into workflowItemTypeOut;
3208 else /* The bind variable is present. */
3209 execute immediate dynamicQuery into workflowItemTypeOut using in transactionIdIn;
3210 end if;
3211 end if;
3212 exception
3213 when others then
3214 errorCode := -20001;
3215 errorMessage :=
3216 ame_util.getMessage(applicationShortNameIn => 'PER',
3217 messageNameIn => 'AME_400224_UTL_INV_ATT_QRY_STG',
3218 tokenNameOneIn => 'ATTRIBUTE',
3219 tokenValueOneIn => attributeName,
3220 tokenNameTwoIn => 'SQLCODE',
3221 tokenValueTwoIn => sqlcode,
3222 tokenNameThreeIn => 'SQLERRM',
3223 tokenValueThreeIn => sqlerrm);
3224 runtimeException(packageNameIn => 'ame_util',
3225 routineNameIn => 'getWorkflowAttributeValues',
3226 exceptionNumberIn => sqlcode,
3227 exceptionStringIn => sqlerrm);
3228 workflowItemKeyOut := null;
3229 workflowItemTypeOut := null;
3230 raise_application_error(errorCode,
3231 errorMessage);
3232 end getWorkflowAttributeValues;
3233 procedure identArrToIdList(identArrIn in owa_util.ident_arr,
3234 startIndexIn in integer default 2,
3235 idListOut out nocopy idList) as
3236 identArrLimit integer;
3237 begin
3238 identArrLimit := identArrIn.last;
3239 for tempIndex in startIndexIn .. identArrLimit loop
3240 idListOut(tempIndex - startIndexIn + 1) := to_number(identArrIn(tempIndex));
3241 end loop;
3242 exception
3243 when others then
3244 runtimeException(packageNameIn => 'ame_util',
3245 routineNameIn => 'identArrToIdList',
3246 exceptionNumberIn => sqlcode,
3247 exceptionStringIn => sqlerrm);
3248 raise;
3249 end identArrToIdList;
3250 procedure identArrToLongestStringList(identArrIn in owa_util.ident_arr,
3251 startIndexIn in integer default 2,
3252 longestStringListOut out nocopy longestStringList) as
3253 identArrLimit integer;
3254 begin
3255 identArrLimit := identArrIn.last;
3256 for tempIndex in startIndexIn .. identArrLimit loop
3257 longestStringListOut(tempIndex - startIndexIn + 1) := identArrIn(tempIndex);
3258 end loop;
3259 exception
3260 when others then
3261 runtimeException(packageNameIn => 'ame_util',
3262 routineNameIn => 'identArrToLongestStringList',
3263 exceptionNumberIn => sqlcode,
3264 exceptionStringIn => sqlerrm);
3265 raise;
3266 end identArrToLongestStringList;
3267 procedure identArrToStringList(identArrIn in owa_util.ident_arr,
3268 startIndexIn in integer default 2,
3269 stringListOut out nocopy stringList) as
3270 identArrLimit integer;
3271 begin
3272 identArrLimit := identArrIn.last;
3273 for tempIndex in startIndexIn .. identArrLimit loop
3274 stringListOut(tempIndex - startIndexIn + 1) := identArrIn(tempIndex);
3275 end loop;
3276 exception
3277 when others then
3278 runtimeException(packageNameIn => 'ame_util',
3279 routineNameIn => 'identArrToStringList',
3280 exceptionNumberIn => sqlcode,
3281 exceptionStringIn => sqlerrm);
3282 raise;
3283 end identArrToStringList;
3284 procedure idListToStringList(idListIn in idList,
3285 stringListOut out nocopy stringList) as
3286 tempIndex integer;
3287 tempIndex2 integer;
3288 begin
3289 tempIndex := idListIn.first;
3290 tempIndex2 := 0;
3291 loop
3292 if(tempIndex is null) then
3293 exit;
3294 end if;
3295 tempIndex2 := tempIndex2 + 1;
3296 stringListOut(tempIndex2) := to_char(idListIn(tempIndex));
3297 tempIndex := idListIn.next(tempIndex);
3298 end loop;
3299 exception
3300 when others then
3301 runtimeException(packageNameIn => 'ame_util',
3302 routineNameIn => 'idListToStringList',
3303 exceptionNumberIn => sqlcode,
3304 exceptionStringIn => sqlerrm);
3305 raise;
3306 end idListToStringList;
3307 procedure insTable2ToInsTable(insertionsTable2In in ame_util.insertionsTable2,
3308 insertionsTableOut out nocopy ame_util.insertionsTable) as
3309 errorCode integer;
3310 errorMessage ame_util.longestStringType;
3311 tempInteger1 integer;
3312 tempInteger2 integer;
3313 tempIndex integer;
3314 insertionTargetActionId ame_util.StringType;
3315 insertionTargetApprover ame_util.longStringType;
3316 insertionTargetGrpChainId ame_util.longStringType;
3317 insertionTargetItemClass ame_util.StringType;
3318 insertionTargetItemId ame_util.StringType;
3319 insertionTargetOccurrence ame_util.StringType;
3320 tempOrigSystem ame_util.stringType;
3321 apprName ame_util.longStringType;
3322 unchgParam ame_util.longStringType;
3323 wrongOrigSystem exception;
3324 wrongItemClass exception;
3325 begin
3326 for i in 1..insertionsTable2In.count loop
3327 if insertionsTable2In(i).item_class <> ame_util.headerItemClassName then
3328 raise wrongItemClass;
3329 end if;
3330 insertionsTableOut(i).order_type := insertionsTable2In(i).order_type ;
3331 insertionsTableOut(i).api_insertion := insertionsTable2In(i).api_insertion ;
3332 insertionsTableOut(i).authority := insertionsTable2In(i).authority ;
3333 insertionsTableOut(i).description := insertionsTable2In(i).description ;
3334 if (insertionsTableOut(i).order_type = ame_util.absoluteOrder) then
3335 /* The parameter is unchanged */
3336 insertionsTableOut(i).parameter := insertionsTable2In(i).parameter ;
3337 elsif (insertionsTableOut(i).order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
3338 /* The in parameter has the format :
3339 approvers(positionIn - 1).name || ame_util.fieldDelimiter ||
3340 approvers(positionIn - 1).item_class || ame_util.fieldDelimiter ||
3341 approvers(positionIn - 1).item_id || ame_util.fieldDelimiter ||
3342 approvers(positionIn - 1).action_type_id || ame_util.fieldDelimiter ||
3343 approvers(positionIn - 1).group_or_chain_id || ame_util.fieldDelimiter ||
3344 approvers(positionIn - 1).occurrence;
3345 the out parameter has the format :
3346 {ame_util.approverUserId,ame_util.approverPersonId} || ':' ||
3347 {approverList(positionIn - 1).user_id, approverList(positionIn - 1).person_id}
3348 ':' || approverList(positionIn - 1).approval_type_id || ':' ||
3349 approverList(positionIn - 1).group_or_chain_id || ':' ||
3350 approverList(positionIn - 1).occurrence; */
3351 tempInteger1 := instrb(insertionsTable2In(i).parameter,ame_util.fieldDelimiter, 1);
3352 insertionTargetApprover := substrb(insertionsTable2In(i).parameter, 1, tempInteger1 - 1);
3353 tempInteger1 := tempInteger1 + 1;
3354 tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
3355 insertionTargetItemClass := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3356 tempInteger1 := tempInteger2 + 1;
3357 tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
3358 insertionTargetItemId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3359 tempInteger1 := tempInteger2 + 1;
3360 tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
3361 insertionTargetActionId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3362 tempInteger1 := tempInteger2 + 1;
3363 tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
3364 insertionTargetGrpChainId := substrb(insertionsTable2In(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3365 tempInteger1 := tempInteger2 + 1;
3366 tempInteger2 := instrb(insertionsTable2In(i).parameter, ame_util.fieldDelimiter, tempInteger1);
3367 insertionTargetOccurrence := substrb(insertionsTable2In(i).parameter, tempInteger1);
3368 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => insertionTargetApprover,
3369 origSystemOut => tempOrigSystem,
3370 origSystemIdOut => tempIndex);
3371 if tempOrigSystem = ame_util.perOrigSystem then
3372 tempOrigSystem := ame_util.approverPersonId ;
3373 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
3374 tempOrigSystem := ame_util.approverUserId;
3375 else
3376 raise wrongOrigSystem;
3377 end if;
3378 insertionsTableOut(i).parameter := tempOrigSystem || ':' || tempIndex || ':' ||
3379 insertionTargetActionId || ':' || insertionTargetGrpChainId || ':' ||
3380 insertionTargetOccurrence ;
3381 elsif (insertionsTableOut(i).order_type = ame_util.firstPreApprover) then
3382 /* the out parameter has the format :
3383 ame_util.firstPreApprover
3384 */
3385 insertionsTableOut(i).parameter := ame_util.firstPreApprover;
3386 elsif (insertionsTableOut(i).order_type = ame_util.lastPreApprover) then
3387 /* the out parameter has the format :
3388 ame_util.lastPreApprover
3389 */
3390 insertionsTableOut(i).parameter := ame_util.lastPreApprover;
3391 elsif (insertionsTableOut(i).order_type = ame_util.firstPostApprover) then
3392 /* the out parameter has the format :
3393 ame_util.firstPostApprover
3394 */
3395 insertionsTableOut(i).parameter := ame_util.firstPostApprover;
3396 elsif (insertionsTableOut(i).order_type = ame_util.lastPostApprover) then
3397 /* the out parameter has the format :
3398 ame_util.lastPostApprover
3399 */
3400 insertionsTableOut(i).parameter := ame_util.lastPostApprover;
3401 elsif (insertionsTableOut(i).order_type = ame_util.firstAuthority) then
3402 /* the out parameter has the format :
3403 ame_util.firstAuthority
3404 */
3405 insertionsTableOut(i).parameter := ame_util.firstAuthority;
3406 end if;
3407 end loop;
3408 exception
3409 when wrongOrigSystem then
3410 errorCode := -20001;
3411 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3412 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
3413 tokenNameOneIn => 'ORIG_SYSTEM_ID',
3414 tokenValueOneIn => tempOrigSystem);
3415 ame_util.runtimeException(packageNameIn => 'ame_util',
3416 routineNameIn => 'insTable2ToInsTable',
3417 exceptionNumberIn => errorCode,
3418 exceptionStringIn => errorMessage);
3419 raise_application_error(errorCode,
3420 errorMessage);
3421 when wrongItemClass then
3422 errorCode := -20001;
3423 errorMessage := ame_util.getMessage(applicationShortNameIn =>'PER',
3424 messageNameIn => 'AME_400417_APPR_NOT_HEADER_IC');
3425 ame_util.runtimeException(packageNameIn => 'ame_util',
3426 routineNameIn => 'insTable2ToInsTable',
3427 exceptionNumberIn => errorCode,
3428 exceptionStringIn => errorMessage);
3429 raise_application_error(errorCode,
3430 errorMessage);
3431 when others then
3432 ame_util.runtimeException(packageNameIn => 'ame_util',
3433 routineNameIn => 'insTable2ToInsTable',
3434 exceptionNumberIn => sqlcode,
3435 exceptionStringIn => sqlerrm);
3436 raise;
3437 end insTable2ToInsTable;
3438 procedure insTableToInsTable2(insertionsTableIn in ame_util.insertionsTable,
3439 transactionIdIn in varchar2,
3440 insertionsTable2Out out nocopy ame_util.insertionsTable2) as
3441 errorCode integer;
3442 errorMessage ame_util.longestStringType;
3443 tempInteger1 integer;
3444 tempInteger2 integer;
3445 tempIndex integer;
3446 insertionTargetActionId ame_util.StringType;
3447 insertionTargetApprover ame_util.longStringType;
3448 insertionTargetGrpChainId ame_util.longStringType;
3449 insertionTargetItemClass ame_util.StringType;
3450 insertionTargetItemId ame_util.StringType;
3451 insertionTargetOccurrence ame_util.StringType;
3452 tempOrigSystem ame_util.stringType;
3453 apprName ame_util.longStringType;
3454 wrongOrigSystem exception;
3455 begin
3456 for i in 1..insertionsTableIn.count loop
3457 insertionsTable2Out(i).item_class := ame_util.headerItemClassName ;
3458 insertionsTable2Out(i).item_id := transactionIdIn ;
3459 insertionsTable2Out(i).order_type := insertionsTableIn(i).order_type ;
3460 insertionsTable2Out(i).api_insertion := insertionsTableIn(i).api_insertion ;
3461 insertionsTable2Out(i).authority := insertionsTableIn(i).authority ;
3462 insertionsTable2Out(i).description := insertionsTableIn(i).description ;
3463 insertionsTable2Out(i).action_type_id := ame_util.nullInsertionActionTypeId ;
3464 insertionsTable2Out(i).group_or_chain_id := ame_util.nullInsertionGroupOrChainId ;
3465 if (insertionsTable2Out(i).order_type = ame_util.absoluteOrder) then
3466 /* The parameter is unchanged */
3467 insertionsTable2Out(i).parameter := insertionsTableIn(i).parameter ;
3468 elsif (insertionsTable2Out(i).order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
3469 /* The in parameter has the format :
3470 {ame_util.approverUserId,ame_util.approverPersonId} || ':' ||
3471 {approverList(positionIn - 1).user_id, approverList(positionIn - 1).person_id}
3472 ':' || approverList(positionIn - 1).approval_type_id || ':' ||
3473 approverList(positionIn - 1).group_or_chain_id || ':' ||
3474 approverList(positionIn - 1).occurrence;
3475 the out parameter has the format :
3476 approvers(positionIn - 1).name || ame_util.fieldDelimiter ||
3477 approvers(positionIn - 1).item_class || ame_util.fieldDelimiter ||
3478 approvers(positionIn - 1).item_id || ame_util.fieldDelimiter ||
3479 approvers(positionIn - 1).action_type_id || ame_util.fieldDelimiter ||
3480 approvers(positionIn - 1).group_or_chain_id || ame_util.fieldDelimiter ||
3481 approvers(positionIn - 1).occurrence;
3482 */
3483 tempInteger1 := instrb(insertionsTableIn(i).parameter, ':', 1, 1);
3484 insertionTargetApprover := substrb(insertionsTableIn(i).parameter, 1, tempInteger1 - 1);
3485 tempInteger1 := tempInteger1 + 1;
3486 tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
3487 tempIndex := to_number(substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1));
3488 if insertionTargetApprover = ame_util.approverPersonId then
3489 tempOrigSystem := ame_util.perOrigSystem;
3490 elsif insertionTargetApprover = ame_util.approverUserId then
3491 tempOrigSystem := ame_util.fndUserOrigSystem;
3492 else
3493 raise wrongOrigSystem;
3494 end if;
3495 apprName:= ame_approver_type_pkg.getWfRolesName(origSystemIn => tempOrigSystem,
3496 origSystemIdIn=>tempIndex);
3497 insertionTargetItemClass := ame_util.headerItemClassName;
3498 insertionTargetItemId := transactionIdIn;
3499 tempInteger1 := tempInteger2 + 1;
3500 tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
3501 insertionTargetActionId := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3502 tempInteger1 := tempInteger2 + 1;
3503 tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
3504 insertionTargetGrpChainId := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3505 tempInteger1 := tempInteger2 + 1;
3506 tempInteger2 := instrb(insertionsTableIn(i).parameter, ':', tempInteger1, 1);
3507 insertionTargetOccurrence := substrb(insertionsTableIn(i).parameter, tempInteger1, tempInteger2 - tempInteger1);
3508 insertionsTable2Out(i).parameter := apprName || ame_util.fieldDelimiter || insertionTargetItemClass||
3509 ame_util.fieldDelimiter ||insertionTargetItemId||
3510 ame_util.fieldDelimiter ||insertionTargetActionId ||
3511 ame_util.fieldDelimiter || insertionTargetGrpChainId ||
3512 ame_util.fieldDelimiter||insertionTargetOccurrence;
3513 insertionsTable2Out(i).action_type_id := insertionTargetActionId;
3514 insertionsTable2Out(i).group_or_chain_id := insertionTargetGrpChainId;
3515 elsif (insertionsTable2Out(i).order_type = ame_util.firstPreApprover) then
3516 /* the in parameter has the format :
3517 ame_util.firstPreApprover
3518 the out format is:
3519 ame_util.firstPreApprover ||
3520 ame_util.fieldDelimiter ||
3521 ame_util.headerItemClassName ||
3522 ame_util.fieldDelimiter ||
3523 transactionIdIn;
3524 */
3525 insertionsTable2Out(i).parameter := ame_util.firstPreApprover ||
3526 ame_util.fieldDelimiter ||
3527 ame_util.headerItemClassName ||
3528 ame_util.fieldDelimiter ||
3529 transactionIdIn;
3530 elsif (insertionsTable2Out(i).order_type = ame_util.lastPreApprover) then
3531 /* the in parameter has the format :
3532 ame_util.lastPreApprover
3533 the out format is:
3534 ame_util.lastPreApprover ||
3535 ame_util.fieldDelimiter ||
3536 ame_util.headerItemClassName ||
3537 ame_util.fieldDelimiter ||
3538 transactionIdIn;
3539 */
3540 insertionsTable2Out(i).parameter := ame_util.lastPreApprover ||
3541 ame_util.fieldDelimiter ||
3542 ame_util.headerItemClassName ||
3543 ame_util.fieldDelimiter ||
3544 transactionIdIn;
3545 elsif (insertionsTable2Out(i).order_type = ame_util.firstPostApprover) then
3546 /* the in parameter has the format :
3547 ame_util.firstPostApprover
3548 the out format is:
3549 ame_util.firstPostApprover ||
3550 ame_util.fieldDelimiter ||
3551 ame_util.headerItemClassName ||
3552 ame_util.fieldDelimiter ||
3553 transactionIdIn;
3554 */
3555 insertionsTable2Out(i).parameter := ame_util.firstPostApprover ||
3556 ame_util.fieldDelimiter ||
3557 ame_util.headerItemClassName ||
3558 ame_util.fieldDelimiter ||
3559 transactionIdIn;
3560 elsif (insertionsTable2Out(i).order_type = ame_util.lastPostApprover) then
3561 /* the in parameter has the format :
3562 ame_util.lastPostApprover
3563 the out format is:
3564 ame_util.lastPostApprover ||
3565 ame_util.fieldDelimiter ||
3566 ame_util.headerItemClassName ||
3567 ame_util.fieldDelimiter ||
3568 transactionIdIn;
3569 */
3570 insertionsTable2Out(i).parameter := ame_util.lastPostApprover ||
3571 ame_util.fieldDelimiter ||
3572 ame_util.headerItemClassName ||
3573 ame_util.fieldDelimiter ||
3574 transactionIdIn;
3575 elsif (insertionsTable2Out(i).order_type = ame_util.firstAuthority) then
3576 /* the in parameter has the format :
3577 ame_util.firstAuthority
3578 the out format is:
3579 ame_util.firstAuthority ||
3580 ame_util.fieldDelimiter ||
3581 ame_util.headerItemClassName ||
3582 ame_util.fieldDelimiter ||
3583 transactionIdIn;
3584 */
3585 insertionsTable2Out(i).parameter := ame_util.firstAuthority ||
3586 ame_util.fieldDelimiter ||
3587 ame_util.headerItemClassName ||
3588 ame_util.fieldDelimiter ||
3589 transactionIdIn;
3590 end if;
3591 end loop;
3592 exception
3593 when wrongOrigSystem then
3594 errorCode := -20001;
3595 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3596 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
3597 tokenNameOneIn => 'ORIG_SYSTEM_ID',
3598 tokenValueOneIn => tempOrigSystem);
3599 ame_util.runtimeException(packageNameIn => 'ame_util',
3600 routineNameIn => 'insTableToInsTable2',
3601 exceptionNumberIn => errorCode,
3602 exceptionStringIn => errorMessage);
3603 raise_application_error(errorCode,
3604 errorMessage);
3605 when others then
3606 ame_util.runtimeException(packageNameIn => 'ame_util',
3607 routineNameIn => 'insTableToInsTable2',
3608 exceptionNumberIn => sqlcode,
3609 exceptionStringIn => sqlerrm);
3610 raise;
3611 end insTableToInsTable2;
3612 procedure makeEven(numberInOut in out nocopy integer) as
3613 begin
3614 if(not isAnEvenNumber(numberIn => numberInOut)) then
3615 numberInOut := numberInOut + 1;
3616 end if;
3617 exception
3618 when others then
3619 ame_util.runtimeException(packageNameIn => 'ame_util',
3620 routineNameIn => 'makeEven',
3621 exceptionNumberIn => sqlcode,
3622 exceptionStringIn => sqlerrm);
3623 raise;
3624 end makeEven;
3625 procedure makeOdd(numberInOut in out nocopy integer) as
3626 begin
3627 if(isAnEvenNumber(numberIn => numberInOut)) then
3628 numberInOut := numberInOut + 1;
3629 end if;
3630 exception
3631 when others then
3632 ame_util.runtimeException(packageNameIn => 'ame_util',
3633 routineNameIn => 'makeOdd',
3634 exceptionNumberIn => sqlcode,
3635 exceptionStringIn => sqlerrm);
3636 raise;
3637 end makeOdd;
3638 procedure nonautonomousLog(logIdIn number,
3639 packageNameIn in varchar2,
3640 routineNameIn in varchar2,
3641 exceptionNumberIn in integer,
3642 exceptionStringIn in varchar2,
3643 transactionIdIn in varchar2 default null,
3644 applicationIdIn in integer default null) as
3645 begin
3646 insert into ame_exceptions_log(
3647 log_id,
3648 package_name,
3649 routine_name,
3650 transaction_id,
3651 application_id,
3652 exception_number,
3653 exception_string) values(
3654 logIdIn,
3655 substrb(packageNameIn, 1, 50),
3656 substrb(routineNameIn, 1, 50),
3657 transactionIdIn,
3658 applicationIdIn,
3659 exceptionNumberIn,
3660 substrb(to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')||exceptionStringIn, 1, 4000));
3661 commit;
3662 exception
3663 when others then
3664 rollback;
3665 raise;
3666 end nonautonomousLog;
3667 procedure ordRecordToInsRecord2(orderRecordIn in ame_util.orderRecord,
3668 transactionIdIn in varchar2,
3669 approverIn in ame_util.approverRecord,
3670 insertionRecord2Out out nocopy ame_util.insertionRecord2) as
3671 errorCode integer;
3672 errorMessage ame_util.longestStringType;
3673 tempInteger1 integer;
3674 tempInteger2 integer;
3675 tempIndex integer;
3676 insertionTargetActionId ame_util.StringType;
3677 insertionTargetApprover ame_util.longStringType;
3678 insertionTargetGrpChainId ame_util.longStringType;
3679 insertionTargetItemClass ame_util.StringType;
3680 insertionTargetItemId ame_util.StringType;
3681 insertionTargetOccurrence ame_util.StringType;
3682 tempOrigSystem ame_util.stringType;
3683 apprName ame_util.longStringType;
3684 wrongOrigSystem exception;
3685 begin
3686 insertionRecord2Out.item_class := ame_util.headerItemClassName ;
3687 insertionRecord2Out.item_id := transactionIdIn ;
3688 insertionRecord2Out.order_type := orderRecordIn.order_type ;
3689 insertionRecord2Out.api_insertion := approverIn.api_insertion ;
3690 insertionRecord2Out.authority := approverIn.authority ;
3691 insertionRecord2Out.description := orderRecordIn.description ;
3692 insertionRecord2Out.action_type_id := ame_util.nullInsertionActionTypeId ;
3693 insertionRecord2Out.group_or_chain_id := ame_util.nullInsertionGroupOrChainId ;
3694 if (insertionRecord2Out.order_type = ame_util.absoluteOrder) then
3695 /* The parameter is unchanged */
3696 insertionRecord2Out.parameter := orderRecordIn.parameter ;
3697 elsif (insertionRecord2Out.order_type in (ame_util.afterApprover,ame_util.beforeApprover)) then
3698 /* The in parameter has the format :
3699 {ame_util.approverUserId,ame_util.approverPersonId} || ':' ||
3700 {approverList(positionIn - 1).user_id, approverList(positionIn - 1).person_id}
3701 ':' || approverList(positionIn - 1).approval_type_id || ':' ||
3702 approverList(positionIn - 1).group_or_chain_id || ':' ||
3703 approverList(positionIn - 1).occurrence;
3704 the out parameter has the format :
3705 approvers(positionIn - 1).name || ame_util.fieldDelimiter ||
3706 approvers(positionIn - 1).item_class || ame_util.fieldDelimiter ||
3707 approvers(positionIn - 1).item_id || ame_util.fieldDelimiter ||
3708 approvers(positionIn - 1).action_type_id || ame_util.fieldDelimiter ||
3709 approvers(positionIn - 1).group_or_chain_id || ame_util.fieldDelimiter ||
3710 approvers(positionIn - 1).occurrence;
3711 */
3712 tempInteger1 := instrb(orderRecordIn.parameter, ':', 1, 1);
3713 insertionTargetApprover := substrb(orderRecordIn.parameter, 1, tempInteger1 - 1);
3714 tempInteger1 := tempInteger1 + 1;
3715 tempInteger2 := instrb(orderRecordIn.parameter, ':', tempInteger1, 1);
3716 tempIndex := to_number(substrb(orderRecordIn.parameter, tempInteger1, tempInteger2 - tempInteger1));
3717 if insertionTargetApprover = ame_util.approverPersonId then
3718 tempOrigSystem := ame_util.perOrigSystem;
3719 elsif insertionTargetApprover = ame_util.approverUserId then
3720 tempOrigSystem := ame_util.fndUserOrigSystem;
3721 else
3722 raise wrongOrigSystem;
3723 end if;
3724 apprName:= ame_approver_type_pkg.getWfRolesName(origSystemIn => tempOrigSystem,
3725 origSystemIdIn=>tempIndex);
3726 insertionTargetItemClass := ame_util.headerItemClassName;
3727 insertionTargetItemId := transactionIdIn;
3728 tempInteger1 := tempInteger2 + 1;
3729 tempInteger2 := instrb(orderRecordIn.parameter, ':', tempInteger1, 1);
3730 insertionTargetActionId := substrb(orderRecordIn.parameter, tempInteger1, tempInteger2 - tempInteger1);
3731 tempInteger1 := tempInteger2 + 1;
3732 tempInteger2 := instrb(orderRecordIn.parameter, ':', tempInteger1, 1);
3733 insertionTargetGrpChainId := substrb(orderRecordIn.parameter, tempInteger1, tempInteger2 - tempInteger1);
3734 tempInteger1 := tempInteger2 + 1;
3735 insertionTargetOccurrence := substrb(orderRecordIn.parameter, tempInteger1);
3736 insertionRecord2Out.parameter := apprName || ame_util.fieldDelimiter || insertionTargetItemClass||
3737 ame_util.fieldDelimiter ||insertionTargetItemId||
3738 ame_util.fieldDelimiter ||insertionTargetActionId ||
3739 ame_util.fieldDelimiter || insertionTargetGrpChainId ||
3740 ame_util.fieldDelimiter||insertionTargetOccurrence;
3741 insertionRecord2Out.action_type_id := insertionTargetActionId;
3742 insertionRecord2Out.group_or_chain_id := insertionTargetGrpChainId;
3743 elsif (insertionRecord2Out.order_type = ame_util.firstPreApprover) then
3744 /* the in parameter has the format :
3745 ame_util.firstPreApprover
3746 the out format is:
3747 ame_util.firstPreApprover ||
3748 ame_util.fieldDelimiter ||
3749 ame_util.headerItemClassName ||
3750 ame_util.fieldDelimiter ||
3751 transactionIdIn;
3752 */
3753 insertionRecord2Out.parameter := ame_util.firstPreApprover ||
3754 ame_util.fieldDelimiter ||
3755 ame_util.headerItemClassName ||
3756 ame_util.fieldDelimiter ||
3757 transactionIdIn;
3758 elsif (insertionRecord2Out.order_type = ame_util.lastPreApprover) then
3759 /* the in parameter has the format :
3760 ame_util.lastPreApprover
3761 the out format is:
3762 ame_util.lastPreApprover ||
3763 ame_util.fieldDelimiter ||
3764 ame_util.headerItemClassName ||
3765 ame_util.fieldDelimiter ||
3766 transactionIdIn;
3767 */
3768 insertionRecord2Out.parameter := ame_util.lastPreApprover ||
3769 ame_util.fieldDelimiter ||
3770 ame_util.headerItemClassName ||
3771 ame_util.fieldDelimiter ||
3772 transactionIdIn;
3773 elsif (insertionRecord2Out.order_type = ame_util.firstPostApprover) then
3774 /* the in parameter has the format :
3775 ame_util.firstPostApprover
3776 the out format is:
3777 ame_util.firstPostApprover ||
3778 ame_util.fieldDelimiter ||
3779 ame_util.headerItemClassName ||
3780 ame_util.fieldDelimiter ||
3781 transactionIdIn;
3782 */
3783 insertionRecord2Out.parameter := ame_util.firstPostApprover ||
3784 ame_util.fieldDelimiter ||
3785 ame_util.headerItemClassName ||
3786 ame_util.fieldDelimiter ||
3787 transactionIdIn;
3788 elsif (insertionRecord2Out.order_type = ame_util.lastPostApprover) then
3789 /* the in parameter has the format :
3790 ame_util.lastPostApprover
3791 the out format is:
3792 ame_util.lastPostApprover ||
3793 ame_util.fieldDelimiter ||
3794 ame_util.headerItemClassName ||
3795 ame_util.fieldDelimiter ||
3796 transactionIdIn;
3797 */
3798 insertionRecord2Out.parameter := ame_util.lastPostApprover ||
3799 ame_util.fieldDelimiter ||
3800 ame_util.headerItemClassName ||
3801 ame_util.fieldDelimiter ||
3802 transactionIdIn;
3803 elsif (insertionRecord2Out.order_type = ame_util.firstAuthority) then
3804 /* the in parameter has the format :
3805 ame_util.firstAuthority
3806 the out format is:
3807 ame_util.firstAuthority ||
3808 ame_util.fieldDelimiter ||
3809 ame_util.headerItemClassName ||
3810 ame_util.fieldDelimiter ||
3811 transactionIdIn;
3812 */
3813 insertionRecord2Out.parameter := ame_util.firstAuthority ||
3814 ame_util.fieldDelimiter ||
3815 ame_util.headerItemClassName ||
3816 ame_util.fieldDelimiter ||
3817 transactionIdIn;
3818 end if;
3819 exception
3820 when wrongOrigSystem then
3821 errorCode := -20001;
3822 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
3823 messageNameIn => 'AME_400415_APPROVER_NOT_FOUND',
3824 tokenNameOneIn => 'ORIG_SYSTEM_ID',
3825 tokenValueOneIn => tempOrigSystem);
3826 ame_util.runtimeException(packageNameIn => 'ame_util',
3827 routineNameIn => 'ordRecordToInsRecord2',
3828 exceptionNumberIn => errorCode,
3829 exceptionStringIn => errorMessage);
3830 raise_application_error(errorCode,
3831 errorMessage);
3832 when others then
3833 ame_util.runtimeException(packageNameIn => 'ame_util',
3834 routineNameIn => 'ordRecordToInsRecord2',
3835 exceptionNumberIn => sqlcode,
3836 exceptionStringIn => sqlerrm);
3837 raise;
3838 end ordRecordToInsRecord2;
3839 procedure parseSourceValue(sourceValueIn in varchar2,
3840 sourceDescriptionOut out nocopy varchar2,
3841 ruleIdListOut out nocopy ame_util.idList) as
3842 ruleIdIndex integer;
3843 sourceValueInLength integer;
3844 tempFieldDelimiterLocation integer;
3845 tempLength integer;
3846 tempRuleIdLocation integer;
3847 sourceSubstring ame_util.stringType;
3848 begin
3849 sourceValueInLength := lengthb(sourceValueIn);
3850 /* Handle the null case first (even though the null case should not typically arise). */
3851 if(sourceValueInLength is null or sourceValueInLength = 0) then
3852 return;
3853 end if;
3854 /* Now handle the non-null case. */
3855 tempFieldDelimiterLocation := instrb(sourceValueIn, fieldDelimiter, 1, 1);
3856 if(tempFieldDelimiterLocation = 0) then
3857 tempLength := sourceValueInLength;
3858 else
3859 tempLength := tempFieldDelimiterLocation - 1;
3860 end if;
3861 sourceSubstring := substrb(sourceValueIn, 1, tempLength);
3862 if(sourceSubstring in (approveAndForwardInsertion,
3863 forwardInsertion,
3864 specialForwardInsertion)) then
3865 sourceDescriptionOut := forwardeeSource;
3866 elsif(sourceSubstring = surrogateInsertion) then
3867 sourceDescriptionOut := surrogateSource;
3868 elsif(sourceSubstring = otherInsertion) then
3869 sourceDescriptionOut := inserteeSource;
3870 elsif(sourceSubstring = apiSuppression) then
3871 sourceDescriptionOut := suppressionSource;
3872 else /* rule-ID list */
3873 sourceDescriptionOut := ruleGeneratedSource;
3874 /* Parse sourceValueIn as a rule-ID list. */
3875 tempRuleIdLocation := 1;
3876 ruleIdIndex := 1; /* post-increment */
3877 loop
3878 tempFieldDelimiterLocation := instrb(sourceValueIn, fieldDelimiter, tempRuleIdLocation);
3879 if(tempFieldDelimiterLocation = 0) then
3880 tempFieldDelimiterLocation := sourceValueInLength + 1;
3881 end if;
3882 ruleIdListOut(ruleIdIndex) := to_number(substrb(sourceValueIn,
3883 tempRuleIdLocation,
3884 tempFieldDelimiterLocation - tempRuleIdLocation));
3885 if(tempFieldDelimiterLocation > sourceValueInLength) then
3886 exit;
3887 end if;
3888 ruleIdIndex := ruleIdIndex + 1;
3889 tempRuleIdLocation := tempFieldDelimiterLocation + 1;
3890 end loop;
3891 end if;
3892 exception
3893 when others then
3894 ame_util.runtimeException(packageNameIn => 'ame_util',
3895 routineNameIn => 'parseSourceValue',
3896 exceptionNumberIn => sqlcode,
3897 exceptionStringIn => sqlerrm);
3898 raise;
3899 end parseSourceValue;
3900 procedure parseStaticCurAttValue(applicationIdIn in integer,
3901 attributeIdIn in integer,
3902 attributeValueIn in varchar2,
3903 localErrorIn in boolean,
3904 amountOut out nocopy varchar2,
3905 currencyOut out nocopy varchar2,
3906 conversionTypeOut out nocopy varchar2) as
3907 attributeName ame_attributes.name%type;
3908 badStaticCurUsageException exception;
3909 comma1Location integer;
3910 comma2Location integer;
3911 errorCode integer;
3912 errorMessage ame_util.longestStringType;
3913 transactionType ame_calling_apps.application_name%type;
3914 begin
3915 /*
3916 Static currency usages must be null or look like this: '5000.00,USD,Corporate'
3917 or '666,66,USD,Daily'. The amount can be formatted per the currency's requirements;
3918 this procedure looks for the last two commas in the string, and assumes that they
3919 are the parse points.
3920 */
3921 if(attributeValueIn is null) then
3922 amountOut := null;
3923 currencyOut := null;
3924 conversionTypeOut := null;
3925 return;
3926 end if;
3927 comma1Location := instrb(attributeValueIn, ',', -1, 2);
3928 comma2Location := instrb(attributeValueIn, ',', -1, 1);
3929 if(comma1Location = 0 or
3930 comma2Location = 0 or
3931 comma1Location < 2 or
3932 comma2Location < 4) then
3933 attributeName := ame_attribute_pkg.getName(attributeIdIn => attributeIdIn);
3934 raise badStaticCurUsageException;
3935 end if;
3936 amountOut := substrb(attributeValueIn, 1, comma1Location - 1);
3937 currencyOut := substrb(attributeValueIn, comma1Location + 1, comma2Location - comma1Location - 1);
3938 conversionTypeOut := substrb(attributeValueIn, comma2Location + 1, lengthb(attributeValueIn) - comma2Location);
3939 exception
3940 when badStaticCurUsageException then
3941 transactionType := ame_admin_pkg.getApplicationName(applicationIdIn => applicationIdIn);
3942 errorCode := -20001;
3943 errorMessage :=
3944 ame_util.getMessage(applicationShortNameIn => 'PER',
3945 messageNameIn => 'AME_400127_ENG_BAD_ST_USG',
3946 tokenNameOneIn => 'TRANSACTION_TYPE',
3947 tokenValueOneIn => transactionType,
3948 tokenNameTwoIn => 'ATTRIBUTE',
3949 tokenValueTwoIn => attributeName);
3950 runtimeException(packageNameIn => 'ame_util',
3951 routineNameIn => 'parseStaticCurAttValue',
3952 exceptionNumberIn => errorCode,
3953 exceptionStringIn => errorMessage);
3954 raise_application_error(errorCode,
3955 errorMessage);
3956 when others then
3957 runtimeException(packageNameIn => 'ame_util',
3958 routineNameIn => 'parseStaticCurAttValue',
3959 exceptionNumberIn => sqlcode,
3960 exceptionStringIn => sqlerrm);
3961 raise;
3962 end parseStaticCurAttValue;
3963 procedure purgeOldTempData as
3964 cursor transactionTypeCursor is
3965 select application_id
3966 from ame_calling_apps
3967 where
3968 sysdate between start_date and
3969 nvl(end_date - ame_util.oneSecond, sysdate);
3970 cursor attributeUsageCursor (applicationIdIn integer) is
3971 select attribute_id
3972 from ame_attribute_usages
3973 where
3974 application_id = applicationIdIn and
3975 sysdate between start_date and
3976 nvl(end_date - ame_util.oneSecond, sysdate);
3977 applicationId integer;
3978 attributeId integer;
3979 lastDateToSave date;
3980 errbuf varchar2(4000);
3981 retcode number;
3982 transactionId ame_temp_transactions.transaction_id%type;
3983 begin
3984 for tempTransType in transactionTypeCursor loop
3985 applicationId := tempTransType.application_id;
3986 -- Make a call the ame_trans_data_purge.purgeTransdata
3987 --
3988 ame_trans_data_purge.purgeTransData(errbuf => errbuf,
3989 retcode => retcode,
3990 applicationIdIn => applicationId,
3991 purgeTypeIn => 'A');
3992 end loop;
3993 end purgeOldTempData;
3994 procedure purgeOldTempData2(errbuf out nocopy varchar2,
3995 retcode out nocopy varchar2) as
3996 begin
3997 purgeOldTempData;
3998 retcode := 0;
3999 errbuf := null;
4000 exception
4001 when others then
4002 runtimeException(packageNameIn => 'ame_util',
4003 routineNameIn => 'purgeOldTempData2',
4004 exceptionNumberIn => sqlcode,
4005 exceptionStringIn => sqlerrm);
4006 retcode := 2;
4007 errbuf := sqlerrm;
4008 raise;
4009 end purgeOldTempData2;
4010 procedure purgeOldTransLocks(errbuf out nocopy varchar2,
4011 retcode out nocopy varchar2) as
4012 begin
4013 delete from ame_temp_trans_locks
4014 where row_timestamp < sysdate - 1/24;
4015 commit;
4016 retcode := 0;
4017 errbuf := null;
4018 exception
4019 when others then
4020 runtimeException(packageNameIn => 'ame_util',
4021 routineNameIn => 'purgeOldTransLocks',
4022 exceptionNumberIn => sqlcode,
4023 exceptionStringIn => sqlerrm);
4024 retcode := 2;
4025 errbuf := sqlerrm;
4026 raise;
4027 end purgeOldTransLocks;
4028 procedure runtimeException(packageNameIn in varchar2,
4029 routineNameIn in varchar2,
4030 exceptionNumberIn in integer,
4031 exceptionStringIn in varchar2) as
4032 applicationId integer;
4033 distributedEnvironment ame_config_vars.variable_value%type;
4034 localError boolean;
4035 logId integer;
4036 transactionId ame_temp_transactions.transaction_id%type;
4037 useWorkflow boolean;
4038 begin
4039 begin
4040 /*
4041 This block avoids infinite looping. See bug 2219719.
4042 */
4043 applicationId := ame_engine.getAmeApplicationId;
4044 transactionId := ame_engine.getTransactionId;
4045 localError := ame_engine.isLocalTransaction;
4046 exception
4047 when others then
4048 applicationId := null;
4049 transactionId := null;
4050 localError := true;
4051 end;
4052 distributedEnvironment := getConfigVar(variableNameIn => ame_util.distEnvConfigVar);
4053 useWorkflow := ame_util.useWorkflow(transactionIdIn => transactionId,
4054 applicationIdIn => applicationId);
4055 select ame_exceptions_log_s.nextval into logId from dual;
4056 /*
4057 Log the following exceptions locally:
4058 1. Local (pseudo-runtime, test) transactions (from the test tab).
4059 2. Transactions not using Workflow.
4060 3. Transactions not in a distributed environment.
4061 */
4062 if(localError or
4063 distributedEnvironment <> ame_util.yes or
4064 not useWorkflow) then
4065 if(distributedEnvironment = ame_util.yes) then
4066 /* commit, but not in an autonomous transaction */
4067 nonautonomousLog(logIdIn => logId,
4068 packageNameIn => packageNameIn,
4069 routineNameIn => routineNameIn,
4070 exceptionNumberIn => exceptionNumberIn,
4071 exceptionStringIn => exceptionStringIn,
4072 transactionIdIn => transactionId,
4073 applicationIdIn => applicationId);
4074 else
4075 /* commit in an autonomous transaction */
4076 autonomousLog(logIdIn => logId,
4077 packageNameIn => packageNameIn,
4078 routineNameIn => routineNameIn,
4079 exceptionNumberIn => exceptionNumberIn,
4080 exceptionStringIn => exceptionStringIn,
4081 transactionIdIn => transactionId,
4082 applicationIdIn => applicationId);
4083 end if;
4084 end if;
4085 /* Log genuine runtime exceptions in Workflow when using Workflow. */
4086 if(not localError and
4087 useWorkflow) then
4088 /*
4089 The wf_item_activity_statuses_v.error_name and error_message columns store the exception.
4090 The query against that view is per item type, which should be the
4091 ame_calling_aps.transaction_type_id, and per item key, which should be the transaction ID
4092 in the OAM schema and code. So all we need is the package name, routine name, and log ID,
4093 which will let us order the call stack in our admin UI. The call to wf_core.context
4094 puts the package name in upper case to make the query against wf_item_activity_statuses_v
4095 easier (comparing 'AME%' without having to uppercase anything in the query).
4096 */
4097 wf_core.context(pkg_name => upper(packageNameIn),
4098 proc_name => routineNameIn,
4099 arg1 => logId);
4100 end if;
4101 exception
4102 when others then
4103 if(localError) then
4104 rollback;
4105 end if;
4106 raise;
4107 end runtimeException;
4108 procedure serializeApprovers(approverNamesIn in ame_util.longStringList,
4109 approverDescriptionsIn in ame_util.longStringList,
4110 maxOutputLengthIn in integer,
4111 approverNamesOut out nocopy varchar2,
4112 approverDescriptionsOut out nocopy varchar2) as
4113 errorCode varchar2(10);
4114 errorMessage ame_util.longestStringType;
4115 upperLimit integer;
4116 recordDelimiter varchar2(1);
4117 begin
4118 recordDelimiter := ame_util.recordDelimiter;
4119 upperLimit := approverNamesIn.count;
4120 if(upperLimit = 0) then
4121 raise ame_util.zeroApproversException;
4122 end if;
4123 approverNamesOut := '';
4124 approverDescriptionsOut := '';
4125 for i in 1 .. upperLimit loop
4126 approverNamesOut := approverNamesOut || approverNamesIn(i) || recordDelimiter;
4127 approverDescriptionsOut:=
4128 approverDescriptionsOut || approverDescriptionsIn(i) || recordDelimiter;
4129 end loop;
4130 if((length(approverNamesOut) > maxOutputLengthIn) or
4131 (length(approverDescriptionsOut) > maxOutputLengthIn)) then
4132 raise ame_util.tooManyApproversException;
4133 end if;
4134 exception
4135 when ame_util.tooManyApproversException then
4136 errorCode := -20001;
4137 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4138 messageNameIn => 'AME_400111_UIN_MANY_ROWS');
4139 runtimeException(packageNameIn => 'ame_util',
4140 routineNameIn => 'serializeApprovers',
4141 exceptionNumberIn => errorCode,
4142 exceptionStringIn => errorMessage);
4143 approverNamesOut := null;
4144 approverDescriptionsOut := null;
4145 raise_application_error(errorCode, errorMessage);
4146 when ame_util.zeroApproversException then
4147 errorCode := -20001;
4148 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4149 messageNameIn => 'AME_400110_UIN_NO_CURR_EMP');
4150 runtimeException(packageNameIn => 'ame_util',
4151 routineNameIn => 'serializeApprovers',
4152 exceptionNumberIn => errorCode,
4153 exceptionStringIn => errorMessage);
4154 approverNamesOut := null;
4155 approverDescriptionsOut := null;
4156 raise_application_error(errorCode, errorMessage);
4157 when others then
4158 runtimeException(packageNameIn => 'ame_util',
4159 routineNameIn => 'serializeApprovers',
4160 exceptionNumberIn => sqlcode,
4161 exceptionStringIn => sqlerrm);
4162 approverNamesOut := null;
4163 approverDescriptionsOut := null;
4164 raise;
4165 end serializeApprovers;
4166 procedure setConfigVar(variableNameIn in varchar2,
4167 variableValueIn in varchar2,
4168 applicationIdIn in integer default null) as
4169 currentUserId integer;
4170 description ame_config_vars.description%type;
4171 errorCode varchar2(10);
4172 errorMessage ame_util.longestStringType;
4173 found varchar2(1);
4174 invalidDayException exception;
4175 nullVariableValue exception;
4176 variableNameTooLong exception;
4177 variableValueTooLong exception;
4178 begin
4179 if(variableNameIn = 'purgeFrequency' or
4180 variableNameIn = 'currencyConversionWindow') then
4181 if(instrb(variableValueIn, ',') <> 0 or
4182 instrb(variableValueIn, '.') <> 0 or
4183 (variableValueIn <= 0)) then
4184 raise invalidDayException;
4185 end if;
4186 end if;
4187 if(variableValueIn is null) then
4188 raise nullVariableValue;
4189 end if;
4190 select description
4191 into description
4192 from ame_config_vars
4193 where variable_name = variableNameIn and
4194 (application_id is null or application_id = 0) and
4195 end_date is null;
4196 currentUserId := ame_util.getCurrentUserId;
4197 if (ame_util.isArgumentTooLong(tableNameIn => 'ame_config_vars',
4198 columnNameIn => 'variable_name',
4199 argumentIn => variableNameIn)) then
4200 raise variableNameTooLong;
4201 end if;
4202 if (ame_util.isArgumentTooLong(tableNameIn => 'ame_config_vars',
4203 columnNameIn => 'variable_value',
4204 argumentIn => variableValueIn)) then
4205 raise variableValueTooLong;
4206 end if;
4207 update ame_config_vars
4208 set
4209 last_updated_by = currentUserId,
4210 last_update_date = sysdate,
4211 last_update_login = currentUserId,
4212 end_date = sysdate
4213 where variable_name = variableNameIn and
4214 ((applicationIdIn is null and (application_id is null or application_id = 0)) or
4215 application_id = applicationIdIn) and
4216 sysdate between start_date and
4217 nvl(end_date - (ame_util.oneSecond), sysdate);
4218 insert into ame_config_vars(variable_name,
4219 variable_value,
4220 description,
4221 created_by,
4222 creation_date,
4223 last_updated_by,
4224 last_update_date,
4225 last_update_login,
4226 start_date,
4227 application_id)
4228 values(variableNameIn,
4229 variableValueIn,
4230 description,
4231 currentUserId,
4232 sysdate,
4233 currentUserId,
4234 sysdate,
4235 currentUserId,
4236 sysdate,
4237 applicationIdIn);
4238 commit;
4239 exception
4240 when invalidDayException then
4241 errorCode := -20001;
4242 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4243 messageNameIn => 'AME_400225_UTL_CFGVAR_POS_INT');
4244 runtimeException(packageNameIn => 'ame_util',
4245 routineNameIn => 'setConfigVar',
4246 exceptionNumberIn => errorCode,
4247 exceptionStringIn => errorMessage);
4248 raise_application_error(errorCode, errorMessage);
4249 when nullVariableValue then
4250 errorCode := -20001;
4251 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4252 messageNameIn => 'AME_400409_MUST_ENT_CFGVAR_VAL');
4253 runtimeException(packageNameIn => 'ame_util',
4254 routineNameIn => 'setConfigVar',
4255 exceptionNumberIn => errorCode,
4256 exceptionStringIn => errorMessage);
4257 raise_application_error(errorCode, errorMessage);
4258 when variableNameTooLong then
4259 errorCode := -20001;
4260 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4261 messageNameIn => 'AME_400296_UTL_VAN_LONG');
4262 runtimeException(packageNameIn => 'ame_util',
4263 routineNameIn => 'setConfigVar',
4264 exceptionNumberIn => errorCode,
4265 exceptionStringIn => errorMessage);
4266 raise_application_error(errorCode, errorMessage);
4267 when variableValueTooLong then
4268 errorCode := -20001;
4269 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4270 messageNameIn => 'AME_400226_UTL_VAR_LONG');
4271 runtimeException(packageNameIn => 'ame_util',
4272 routineNameIn => 'setConfigVar',
4273 exceptionNumberIn => errorCode,
4274 exceptionStringIn => errorMessage);
4275 raise_application_error(errorCode, errorMessage);
4276 when others then
4277 runtimeException(packageNameIn => 'ame_util',
4278 routineNameIn => 'setConfigVar',
4279 exceptionNumberIn => sqlcode,
4280 exceptionStringIn => sqlerrm);
4281 raise;
4282 end setConfigVar;
4283 /*
4284 AME_STRIPING
4285 procedure setCurrentStripeSetId(applicationIdIn in integer,
4286 stripeSetIdIn in integer) as
4287 begin
4288 owa_util.mime_header('text/html',
4289 false);
4290 owa_cookie.send(name => ame_util.getStripeSetCookieName(applicationIdIn => applicationIdIn),
4291 value => to_char(stripeSetIdIn),
4292 expires => sysdate + 365);
4293 owa_util.http_header_close;
4294 exception
4295 when others then
4296 runtimeException(packageNameIn => 'ame_util',
4297 routineNameIn => 'setCurrentStripeSetId',
4298 exceptionNumberIn => sqlcode,
4299 exceptionStringIn => sqlerrm);
4300 raise;
4301 end setCurrentStripeSetId;
4302 */
4303 procedure setTransTypeCookie(applicationIdIn in integer) as
4304 userId integer;
4305 begin
4306 userId := ame_util.getCurrentUserId;
4307 owa_util.mime_header('text/html',
4308 false);
4309 owa_cookie.send(name => ame_util.transactionTypeCookie || ':' || userId,
4310 value => applicationIdIn,
4311 expires => sysdate + (10 * 365),
4312 path => ame_util.getPlsqlDadPath,
4313 domain => substrb(ame_util.getServerName, 8));
4314 owa_util.http_header_close;
4315 exception
4316 when others then
4317 runtimeException(packageNameIn => 'ame_util',
4318 routineNameIn => 'setTransTypeCookie',
4319 exceptionNumberIn => sqlcode,
4320 exceptionStringIn => sqlerrm);
4321 raise;
4322 end setTransTypeCookie;
4323 procedure sortIdListInPlace(idListInOut in out nocopy idList) as
4324 tempId integer;
4325 upperLimit integer;
4326 begin
4327 for i in 2 .. idListInOut.count loop
4328 upperLimit := i - 1;
4329 for j in 1 .. upperLimit loop
4330 if(idListInOut(i) < idListInOut(j)) then
4331 tempId := idListInOut(i);
4332 idListInOut(i) := idListInOut(j);
4333 idListInOut(j) := tempId;
4334 end if;
4335 end loop;
4336 end loop;
4337 exception
4338 when others then
4339 runtimeException(packageNameIn => 'ame_util',
4340 routineNameIn => 'sortIdListInPlace',
4341 exceptionNumberIn => sqlcode,
4342 exceptionStringIn => sqlerrm);
4343 raise;
4344 end sortIdListInPlace;
4345 procedure sortLongStringListInPlace(longStringListInOut in out nocopy longStringList) as
4346 tempLongStringType ame_util.longStringType;
4347 upperLimit integer;
4348 begin
4349 for i in 2 .. longStringListInOut.count loop
4350 upperLimit := i - 1;
4351 for j in 1 .. upperLimit loop
4352 if(longStringListInOut(i) < longStringListInOut(j)) then
4353 tempLongStringType := longStringListInOut(i);
4354 longStringListInOut(i) := longStringListInOut(j);
4355 longStringListInOut(j) := tempLongStringType;
4356 end if;
4357 end loop;
4358 end loop;
4359 exception
4360 when others then
4361 runtimeException(packageNameIn => 'ame_util',
4362 routineNameIn => 'sortLongStringListInPlace',
4363 exceptionNumberIn => sqlcode,
4364 exceptionStringIn => sqlerrm);
4365 raise;
4366 end sortLongStringListInPlace;
4367 procedure sortLongestStringListInPlace(longestStringListInOut in out nocopy longestStringList) as
4368 tempLongestStringType ame_util.longestStringType;
4369 upperLimit integer;
4370 begin
4371 for i in 2 .. longestStringListInOut.count loop
4372 upperLimit := i - 1;
4373 for j in 1 .. upperLimit loop
4374 if(longestStringListInOut(i) < longestStringListInOut(j)) then
4375 tempLongestStringType := longestStringListInOut(i);
4376 longestStringListInOut(i) := longestStringListInOut(j);
4377 longestStringListInOut(j) := tempLongestStringType;
4378 end if;
4379 end loop;
4380 end loop;
4381 exception
4382 when others then
4383 runtimeException(packageNameIn => 'ame_util',
4384 routineNameIn => 'sortLongestStringListInPlace',
4385 exceptionNumberIn => sqlcode,
4386 exceptionStringIn => sqlerrm);
4387 raise;
4388 end sortLongestStringListInPlace;
4389 procedure sortStringListInPlace(stringListInOut in out nocopy stringList) as
4390 tempStringType integer;
4391 upperLimit integer;
4392 begin
4393 for i in 2 .. stringListInOut.count loop
4394 upperLimit := i - 1;
4395 for j in 1 .. upperLimit loop
4396 if(stringListInOut(i) < stringListInOut(j)) then
4397 tempStringType := stringListInOut(i);
4398 stringListInOut(i) := stringListInOut(j);
4399 stringListInOut(j) := tempStringType;
4400 end if;
4401 end loop;
4402 end loop;
4403 exception
4404 when others then
4405 runtimeException(packageNameIn => 'ame_util',
4406 routineNameIn => 'sortStringListInPlace',
4407 exceptionNumberIn => sqlcode,
4408 exceptionStringIn => sqlerrm);
4409 raise;
4410 end sortStringListInPlace;
4411 procedure stringListToIdList(stringListIn in stringList,
4412 idListOut out nocopy idList) as
4413 tempIndex integer;
4414 tempIndex2 integer;
4415 begin
4416 tempIndex := stringListIn.first;
4417 tempIndex2 := 0;
4418 loop
4419 if(tempIndex is null) then
4420 exit;
4421 end if;
4422 tempIndex2 := tempIndex2 + 1;
4423 idListOut(tempIndex2) := to_number(stringListIn(tempIndex));
4424 tempIndex := stringListIn.next(tempIndex);
4425 end loop;
4426 exception
4427 when others then
4428 runtimeException(packageNameIn => 'ame_util',
4429 routineNameIn => 'stringListToIdList',
4430 exceptionNumberIn => sqlcode,
4431 exceptionStringIn => sqlerrm);
4432 raise;
4433 end stringListToIdList;
4434 procedure substituteStrings(stringIn in varchar2,
4435 targetStringsIn in ame_util.stringList,
4436 substitutionStringsIn in ame_util.stringList,
4437 stringOut out nocopy varchar2) as
4438 errorCode integer;
4439 errorMessage ame_util.longestStringType;
4440 listMismatchException exception;
4441 upperLimit integer;
4442 begin
4443 upperLimit := targetStringsIn.count;
4444 if(upperLimit <> substitutionStringsIn.count) then
4445 raise listMismatchException;
4446 end if;
4447 stringOut := stringIn;
4448 for i in 1 .. upperLimit loop
4449 stringOut := replace(stringOut, targetStringsIn(i), substitutionStringsIn(i));
4450 end loop;
4451 exception
4452 when listMismatchException then
4453 errorCode := -20001;
4454 errorMessage := ame_util.getMessage(applicationShortNameIn => 'PER',
4455 messageNameIn => 'AME_400227_UTL_INP_STG_SIZE');
4456 runtimeException(packageNameIn => 'ame_util',
4457 routineNameIn => 'substituteStrings',
4458 exceptionNumberIn => sqlcode,
4459 exceptionStringIn => sqlerrm);
4460 raise_application_error(errorCode,
4461 errorMessage);
4462 when others then
4463 runtimeException(packageNameIn => 'ame_util',
4464 routineNameIn => 'substituteStrings',
4465 exceptionNumberIn => sqlcode,
4466 exceptionStringIn => sqlerrm);
4467 raise;
4468 end substituteStrings;
4469 end ame_util;