[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_UTILITIES_PKG
Source
1 PACKAGE BODY AP_WEB_UTILITIES_PKG AS
2 /* $Header: apwxutlb.pls 120.45 2011/11/17 09:30:56 meesubra ship $ */
3
4 -- Error field and message delimiters
5 C_OpenDelimit CONSTANT VARCHAR2(1) := '{';
6 C_CloseDelimit CONSTANT VARCHAR2(1) := '}';
7
8 -- Number of minimum days (must be a multiple of 7) displayed in
9 -- enter receipts calendar
10 C_NumOfMinDaysInCal CONSTANT NUMBER := 35;
11
12 -- Prefix to desc flex and pseudo desc flex variables
13 C_InputObjectPrefix CONSTANT VARCHAR2(10) := 'FLEX';
14 C_InputPseudoObjectPrefix
15 CONSTANT VARCHAR2(10) := 'PFLEX';
16
17
18 GIsMobileApp BOOLEAN := null;
19 ---------------------------------------------------------------------
20 -- DESCRIPTION:
21 -- Since BOOLEAN is not a valid SQL Type, this used to return a NUMBER
22 -- This is called by oracle.apps.ap.oie.utility.OIEUtil
23 ---------------------------------------------------------------------
24 FUNCTION GetIsMobileApp RETURN NUMBER
25 IS
26 BEGIN
27 if (IsMobileApp) then
28 return 1;
29 else
30 return 0;
31 end if;
32 END;
33
34 FUNCTION IsMobileApp RETURN BOOLEAN
35 IS
36 l_version varchar2(1);
37 BEGIN
38
39 if (GIsMobileApp is null) then
40 begin
41 select nvl(version, C_WebApplicationVersion)
42 into l_version
43 from fnd_responsibility
44 where application_id = fnd_global.resp_appl_id() and responsibility_id = fnd_global.resp_id();
45 exception
46 when no_data_found then
47 null;
48 end;
49
50 if (l_version = C_MobileApplicationVersion) then
51 GIsMobileApp := true;
52 else
53 GIsMobileApp := false;
54 end if;
55 end if;
56
57 return GIsMobileApp;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 BEGIN
62 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
63 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
64 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'IsMobileApp');
65 APP_EXCEPTION.RAISE_EXCEPTION;
66 END;
67 END;
68
69 function isCreditCardEnabled(p_employeeId in AP_WEB_DB_CCARD_PKG.cards_employeeID) return boolean
70 IS
71 l_has VARCHAR2(1);
72 l_cCardEnabled VARCHAR2(1);
73 l_FNDUserID AP_WEB_DB_HR_INT_PKG.fndUser_userID;
74 l_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
75
76 BEGIN
77 -- get Credit Card enable option
78 IF ( AP_WEB_DB_HR_INT_PKG.GetUserIdForEmpCursor(
79 p_employeeId,
80 l_userIdCursor) = TRUE ) THEN
81 LOOP
82 FETCH l_userIdCursor INTO l_FNDUserID;
83 l_cCardEnabled := VALUE_SPECIFIC(
84 p_name => 'SSE_ENABLE_CREDIT_CARD',
85 p_user_id => l_FNDUserID,
86 p_resp_id => FND_PROFILE.VALUE('RESP_ID'),
87 p_apps_id => FND_PROFILE.VALUE('RESP_APPL_ID') );
88 EXIT WHEN (l_userIdCursor%NOTFOUND) OR (nvl(l_cCardEnabled,'N') = 'Y');
89 END LOOP;
90 CLOSE l_userIdCursor;
91 ELSE
92 FND_PROFILE.GET('SSE_ENABLE_CREDIT_CARD', l_cCardEnabled);
93 END IF;
94
95 return (AP_WEB_DB_CCARD_PKG.UserHasCreditCard(p_employeeId, l_has) AND
96 l_has = 'Y' AND l_cCardEnabled = 'Y');
97 EXCEPTION
98 WHEN OTHERS THEN
99 BEGIN
100 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
101 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
102 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'isCreditCardEnabled');
103 DisplayException(fnd_message.get);
104 END;
105 END isCreditCardEnabled;
106
107
108 PROCEDURE InitDayOfWeekArray(p_day_of_week_array IN OUT NOCOPY Number_Array);
109
110
111 ------------------------------------------------------------------------
112 -- DisplayException displays web server exception
113 ------------------------------------------------------------------------
114 PROCEDURE DisplayException (P_ErrorText Long) IS
115 BEGIN
116 htp.htmlOpen;
117 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_GO_BACK');
118 htp.p('<BODY BGCOLOR="#F8F8F8">');
119 htp.p(replace(P_ErrorText,'
120 ',' '));
121 htp.p('<p><a href="javascript:history.back()">');
122 htp.p('<IMG SRC="'||AP_WEB_INFRASTRUCTURE_PKG.getImagePath||
123 'APWBKFR.gif" BORDER=0 HEIGHT=30 WIDTH=30>');
124 htp.p(fnd_message.get||'</a>');
125 htp.p('</BODY>');
126 htp.htmlClose;
127 EXCEPTION
128 WHEN OTHERS THEN
129 BEGIN
130 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
131 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
132 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DisplayException' || P_ErrorText);
133 APP_EXCEPTION.RAISE_EXCEPTION;
134 END;
135
136 END;
137
138
139 PROCEDURE GetUserAgent(p_user_agent IN OUT NOCOPY VARCHAR2) IS
140 l_user_agent VARCHAR2(100) := OWA_UTIL.get_cgi_env('HTTP_USER_AGENT');
141 BEGIN
142 -- dtong added NS45
143 IF (instrb(l_user_agent, 'MSIE') <> 0) THEN
144 p_user_agent := 'IE30';
145 ELSIF ((instrb(l_user_agent, '3.0') = 0) AND
146 (instrb(l_user_agent, '4.') = 0)) THEN
147 p_user_agent := 'NS20';
148 ELSIF (instrb(l_user_agent, '4.0') > 0) then
149 p_user_agent := 'NS40';
150 ELSIF (instrb(l_user_agent, '4.5') >0) then
151 p_user_agent := 'NS45';
152 ELSE
153 p_user_agent := 'NS30';
154 END IF;
155
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 BEGIN
160 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
161 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
162 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetUserAgent');
163 DisplayException(fnd_message.get);
164 END;
165
166 END GetUserAgent;
167
168 -----------------------------------
169 PROCEDURE PopulateCurrencyArray IS
170 -----------------------------------
171 l_curr_code AP_WEB_DB_COUNTRY_PKG.curr_currCode;
172 l_curr_name AP_WEB_DB_COUNTRY_PKG.curr_name;
173 l_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
174 l_minimum_acct_unit AP_WEB_DB_COUNTRY_PKG.curr_minAcctUnit;
175 l_derive_factor AP_WEB_DB_COUNTRY_PKG.curr_deriveFactor;
176 l_derive_effective AP_WEB_DB_COUNTRY_PKG.curr_deriveEffective;
177 l_curr_count NUMBER := 0;
178 l_date_format VARCHAR2(20);
179
180 l_curr_cursor AP_WEB_DB_COUNTRY_PKG.CurrencyInfoCursor;
181
182 BEGIN
183
184 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
185 htp.p('var gC=g_arrCurrency;');
186 IF (AP_WEB_DB_COUNTRY_PKG.GetCurrencyInfoCursor(l_curr_cursor)) THEN
187 LOOP
188 FETCH l_curr_cursor INTO l_curr_code,
189 l_curr_name,
190 l_precision,
191 l_minimum_acct_unit,
192 l_derive_factor,
193 l_derive_effective;
194 EXIT WHEN l_curr_cursor%NOTFOUND;
195 -- g_arrCurrency is zero-based
196 htp.p('gC[' || to_char(l_curr_count) ||
197 ']=new top.objCurrencyInfo("' || l_curr_code || '","'
198 || l_curr_name || '",'|| to_char(l_precision) || ','
199 || nvl(to_char(l_minimum_acct_unit),'""') || ','
200 || nvl(to_char(l_derive_factor), '0') || ','
201 || '"'
202 || nvl(to_char(l_derive_effective, l_date_format), '')
203 || '"'
204 || ');');
205
206 l_curr_count := l_curr_count + 1;
207
208 END LOOP;
209 htp.p('gC['||to_char(l_curr_count) || ']=new top.objCurrencyInfo("OTHER","OTHER",2,"",0,"");');
210
211 -- total number of entries in currencyArray
212 htp.p('g_arrCurrency.len = '|| to_char(l_curr_count+1) || ';
213 top.objExpenseReport.header.setReimbursCurr(top.g_arrCurrency[0].currency);
214 ');
215 END IF;
216 CLOSE l_curr_cursor;
217 EXCEPTION
218 WHEN OTHERS THEN
219 BEGIN
220 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
221 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
222 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'PopulateCurrencyArray');
223 DisplayException(fnd_message.get);
224 END;
225
226 END PopulateCurrencyArray;
227
228 ----------------------
229 PROCEDURE MakeArray IS
230 ----------------------
231 BEGIN
232 htp.p(' function MakeArray (n)
233 {
234 this.len = n;
235 for (var i=1; i<=n; i++)
236 this[i] = 0;
237 }');
238 END MakeArray;
239
240
241 ------------------------------------------------------------------
242 PROCEDURE GetEmployeeInfo(p_employee_name IN OUT NOCOPY VARCHAR2,
243 p_employee_num IN OUT NOCOPY VARCHAR2,
244 p_cost_center IN OUT NOCOPY VARCHAR2,
245 p_employee_id IN NUMBER) IS
246 -------------------------------------------------------------------
247 l_EmpInfoRec AP_WEB_DB_HR_INT_PKG.EmployeeInfoRec;
248 BEGIN
249
250 IF (AP_WEB_DB_HR_INT_PKG.GetEmployeeInfo(p_employee_id,
251 l_EmpInfoRec)) THEN
252 p_employee_name := l_EmpInfoRec.employee_name;
253 p_employee_num := l_EmpInfoRec.employee_num;
254 END IF;
255
256 AP_WEB_ACCTG_PKG.GetEmployeeCostCenter(
257 p_employee_id => p_employee_id,
258 p_emp_ccid => l_EmpInfoRec.emp_ccid,
259 p_cost_center => p_cost_center);
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 p_employee_name := NULL;
264 p_employee_num := NULL;
265 p_cost_center := NULL;
266 END GetEmployeeInfo;
267
268 ----------------------------------------------------------
269 PROCEDURE ExitExpenseReport IS
270 ----------------------------------------------------------
271
272 BEGIN
273
274 htp.p('
275 if (top.opener)
276 parent.window.close();
277 else{
278 if (g_dcdName.charAt(g_dcdName.length-1) == "/") l_dcdName = g_dcdName.substring(0,g_dcdName.length-1);
279 else l_dcdName = g_dcdName;
280 location = l_dcdName+"/OracleApps.DMM";
281 }
282 ');
283 END;
284
285 ----------------------------------------------------------
286 PROCEDURE CancelExpenseReport IS
287 ----------------------------------------------------------
288
289 BEGIN
290
291 htp.p('
292 function CancelExpenseReport() {
293 ');
294
295 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_CANCEL_REPORT');
296
297 htp.p('
298 if (!confirm("'||AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get, TRUE)||'"))
299 return;
300 ');
301 ExitExpenseReport;
302 htp.p('
303 }
304 ');
305 END;
306
307 PROCEDURE GoBack IS
308 BEGIN
309 htp.p('function goBack(){
310 history.back();
311 }');
312 END GoBack;
313
314
315 -----------------------------------------------------
316 PROCEDURE SetReceiptWarningErrorMessage
317 -----------------------------------------------------
318 IS
319 BEGIN
320
321 -- determine receipt offset on client
322 htp.p('function SetReceiptWarningError(index, w_message, w_field, e_message, e_field) {
323 for(var i=1;i<=parent.ArrayCount;i++) {
324 if (top.receipt[i].ReceiptNumber == index) {
325 top.receipt[i].warning_message = w_message;
326 top.receipt[i].warning_field = w_field;
327 top.receipt[i].error_message = e_message;
328 top.receipt[i].error_field = e_field;
329 return;
330 }
331 }
332 }');
333
334 END SetReceiptWarningErrorMessage;
335
336
337 ---------------------------------------------------------------------------
338 PROCEDURE InitDayOfWeekArray (p_day_of_week_array IN OUT NOCOPY Number_Array) IS
339 ---------------------------------------------------------------------------
340 BEGIN
341
342 p_day_of_week_array(1) := 1;
343 p_day_of_week_array(2) := 2;
344 p_day_of_week_array(3) := 3;
345 p_day_of_week_array(4) := 4;
346 p_day_of_week_array(5) := 5;
347 p_day_of_week_array(6) := 6;
348 p_day_of_week_array(7) := 7;
349 p_day_of_week_array(8) := 1;
350 p_day_of_week_array(9) := 2;
351 p_day_of_week_array(10) := 3;
352 p_day_of_week_array(11) := 4;
353 p_day_of_week_array(12) := 5;
354 p_day_of_week_array(13) := 6;
355 p_day_of_week_array(14) := 7;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 BEGIN
360 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
361 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
362 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'InitDayOFWeekArray');
363 DisplayException(fnd_message.get);
364 END;
365
366 END InitDayOFWeekArray;
367
368 ------------------------------------------------------
369 FUNCTION NDaysInCalendar(p_cal_end_date IN DATE,
370 p_start_dow IN NUMBER) RETURN NUMBER
371 IS
372 ------------------------------------------------------
373 l_dow_cal_end_date NUMBER;
374 l_day_of_week_num NUMBER;
375
376 BEGIN
377 --------------------------------------------------------------------
378 -- Calculate day_of_week value of the calendar_end_date, depending on
379 -- what the start_day of the week is (What cell does the last day land
380 -- on in the last row)
381 --------------------------------------------------------------------
382
383 l_dow_cal_end_date := p_cal_end_date - trunc(p_cal_end_date, 'DAY') + 1;
384 l_day_of_week_num := mod(7 + l_dow_cal_end_date - p_start_dow - 1, 7) + 1;
385 RETURN (C_NumOfMinDaysInCal + l_day_of_week_num);
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 BEGIN
390 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
391 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
392 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'NDaysInCalendar');
393 DisplayException(fnd_message.get);
394 END;
395
396 END;
397
398
399
400
401 ------------------------------------------------------------------
402 PROCEDURE PopulateEquation (p_multicurr_flag IN VARCHAR2,
403 p_inv_rate_flag IN VARCHAR2,
404 p_reimbCurr IN VARCHAR2,
405 p_receiptCurr IN VARCHAR2,
406 p_rate IN VARCHAR2,
407 p_equation IN OUT NOCOPY VARCHAR2) IS
408 ------------------------------------------------------------------
409 BEGIN
410
411 IF (p_multicurr_flag = 'Y') THEN
412
413 IF (p_inv_rate_flag = 'Y') THEN
414
415 p_equation := '1 ' || p_reimbCurr || '=' || substrb(to_char(1/to_number(p_rate)),1,18) || ' ' || p_receiptCurr;
416 ELSE
417 p_equation := '1 ' || p_receiptCurr || '=' || p_rate || ' ' || p_reimbCurr;
418 END IF;
419
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 BEGIN
424 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
425 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
426 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'PopulateEquation');
427 DisplayException(fnd_message.get);
428 END;
429
430 END PopulateEquation;
431
432
433
434 -------------------------------
435 PROCEDURE JustifFlagElement IS
436 -------------------------------
437 BEGIN
438 htp.p(' function justifFlagElement(exp_parameter_id, exp_parameter_name, justif_req_flag)
439 {
440 this.parameter_id = exp_parameter_id;
441 this.parameter_name = exp_parameter_name;
442 this.justif_req_flag = justif_req_flag;
443 }');
444 END JustifFlagElement;
445
446 -------------------------------
447 PROCEDURE RetrieveJustifFlag IS
448 -------------------------------
449 BEGIN
450 htp.p('function retrieveJustifFlag(parameterId)
451 {
452 for (var i=0; i < top.justifFlagArray.len; i++){
453
454 if (top.justifFlagArray[i].parameter_id == parameterId)
455 return(top.justifFlagArray[i].justif_req_flag);
456 }
457
458 return ("");
459 }');
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 BEGIN
464 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
465 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
466 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'RetrieveJustifFlag');
467 DisplayException(fnd_message.get);
468 END;
469
470 END RetrieveJustifFlag;
471
472 ------------------------------------
473 PROCEDURE RetrieveJustifFlagIndex IS
474 ------------------------------------
475 BEGIN
476 htp.p('function retrieveJustifFlagIndex(parameterId)
477 {
478 for (var i=0; i < top.justifFlagArray.len; i++){
479
480 if (top.justifFlagArray[i].parameter_id == parameterId)
481 return(i);
482 }
483
484 return ("");
485 }');
486 EXCEPTION
487 WHEN OTHERS THEN
488 BEGIN
489 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
490 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
491 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'RetrieveJustifFlagIndex');
492 DisplayException(fnd_message.get);
493 END;
494
495 END RetrieveJustifFlagIndex;
496
497 --------------------------
498 PROCEDURE CurrencyInfo IS
499 --------------------------
500 BEGIN
501 htp.p(' function currencyInfo(currency, name, precision,
502 minimum_acct_unit,
503 euro_rate, effective_date)
504 {
505 this.currency = currency;
506 this.name = name;
507 this.precision = precision;
508 this.minimum_acct_unit = minimum_acct_unit;
509 this.euro_rate = euro_rate;
510 this.effective_date = effective_date;
511
512 }
513 ');
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 BEGIN
518 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
519 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
520 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CurrencyInfo');
521 DisplayException(fnd_message.get);
522 END;
523
524 END CurrencyInfo;
525
526 -----------------------------------
527 PROCEDURE RetrieveCurrencyIndex IS
528 -----------------------------------
529 BEGIN
530 htp.p(' function retrieveCurrencyIndex(currency)
531 {
532 var high = top.currencyArray.len - 1;
533 var low = 0;
534 var mid;
535
536 while (low <= high) {
537 mid = Math.floor((high + low)/2);
538 if (top.currencyArray[mid].currency < currency) {
539 low = mid + 1;
540 } else if (top.currencyArray[mid].currency > currency) {
541 high = mid - 1;
542 } else {
543 return mid;
544 }
545 }
546 return -1;
547
548 }');
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 BEGIN
553 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
554 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
555 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'RetrieveCurrencyIndex');
556 DisplayException(fnd_message.get);
557 END;
558
559 END RetrieveCurrencyIndex;
560
561 -------------------------
562 PROCEDURE MoneyFormat IS
563 -------------------------
564 l_credit_line_profile_option varchar2(1) := 'N';
565 l_allow_credit_lines Boolean := FALSE;
566 BEGIN
567 FND_PROFILE.GET('AP_WEB_ALLOW_CREDIT_LINES',
568 l_credit_line_profile_option);
569
570 if (l_credit_line_profile_option = 'Y') then
571 l_allow_credit_lines := TRUE;
572 else
573 l_allow_credit_lines := FALSE;
574 end if;
575
576 if (l_allow_credit_lines) then
577 htp.p('
578
579
580
581 function moneyFormat(input, currency) {
582
583 var index = top.retrieveCurrencyIndex(currency);
584 var minimum_acct_unit = top.currencyArray[index].minimum_acct_unit;
585 var precision = top.currencyArray[index].precision;
586
587 var V_input = input + "";
588
589 if (V_input == "")
590 return("");
591
592 if ((eval(V_input) == 0) || (eval(V_input) == 0.0) || (eval(V_input) == 0.00)){
593 if (precision <= 0)
594 return("0");
595 else if (precision == 1)
596 return("0.0");
597 else if (precision == 2)
598 return("0.00");
599 else if (precision == 3)
600 return("0.000");
601
602 }
603 var prefix;
604 if ((eval(input) < 0) && (eval(input) > -1)) {
605 prefix = "-";
606 } else {
607 prefix = "";
608 }
609
610 if (minimum_acct_unit != ""){
611 var amount = fMultiply( Math.round(V_input/minimum_acct_unit), minimum_acct_unit );
612 return (amount);
613 } else {
614 if (precision == 0) {
615 return Math.round(input);
616 }
617 var dollars;
618 var tmp;
619 var multiplier;
620
621 if (eval(V_input) >= 0) {
622 dollars = Math.floor(V_input);
623 } else {
624 dollars = Math.ceil(V_input);
625 }
626 }
627 tmp = V_input + "0";
628 multiplier = 10;
629
630 for (var decimalAt = 0; decimalAt < tmp.length; decimalAt++) {
631 if (tmp.charAt(decimalAt)==".") break
632 }
633 for (var i= 0; i < precision-1; i++){
634 multiplier = fMultiply( 10, multiplier );
635 }
636 var cents = "" + Math.floor(fMultiply( Math.abs(V_input), multiplier ))
637 cents = cents.substring(cents.length-precision, cents.length)
638 if ((eval(V_input) < 1) && (eval(V_input) > -1)){
639 cents = "" + eval(cents) / multiplier;
640 if (cents.charAt(0)=="0")
641 cents = cents.substring(2,cents.length);
642 else
643 cents = cents.substring(1,cents.length);
644 }
645 dollars += ((tmp.charAt(decimalAt+precision)=="9")&&(cents=="00"))? 1 : 0;
646
647 return prefix + dollars + "." + cents
648
649 }
650 ');
651 else
652 htp.p('function moneyFormat(input, currency) {
653
654 var index = top.retrieveCurrencyIndex(currency);
655 var minimum_acct_unit = top.currencyArray[index].minimum_acct_unit;
656 var precision = top.currencyArray[index].precision;
657
658 var V_input = input + "";
659
660 if (V_input == "")
661 return("");
662
663 if ((eval(V_input) == 0) || (eval(V_input) == 0.0) || (eval(V_input) == 0.00)){
664 if (precision <= 0)
665 return("0");
666 else if (precision == 1)
667 return("0.0");
668 else if (precision == 2)
669 return("0.00");
670 else if (precision == 3)
671 return("0.000");
672
673 }
674
675 if (minimum_acct_unit != ""){
676 var amount = fMultiply( Math.round(V_input/minimum_acct_unit), minimum_acct_unit );
677 return (amount);
678 }else{
679 if (precision == 0) {
680 return Math.round(input);
681 }
682 var dollars = Math.floor(V_input)
683 var tmp = V_input + "0"
684 var multiplier = 10;
685
686 for (var decimalAt = 0; decimalAt < tmp.length; decimalAt++) {
687 if (tmp.charAt(decimalAt)==".") break
688 }
689 for (var i= 0; i < precision-1; i++){
690 multiplier = fMultiply( 10, multiplier );
691 }
692 var cents = "" + Math.round(fMultiply( V_input, multiplier ))
693 cents = cents.substring(cents.length-precision, cents.length)
694 if (eval(V_input) < 1) {
695 cents = "" + eval(cents) / multiplier;
696 if (cents.charAt(0)=="0")
697 cents = cents.substring(2,cents.length);
698 else
699 cents = cents.substring(1,cents.length);
700 }
701 dollars += ((tmp.charAt(decimalAt+precision)=="9")&&(cents=="00"))? 1 : 0;
702 if (precision == 0)
703 return Math.round(input);
704 else
705 if ((eval(dollars)==0) && ((eval(cents)==0)||(cents==""))){
706 // for bug 1032095
707 // 0.0 or 0. case
708 if (precision <= 0)
709 return("0");
710 else if (precision == 1)
711 return("0.0");
712 else if (precision == 2)
713 return("0.00");
714 else if (precision == 3)
715 return("0.000");
716 }
717 else
718 return dollars + "." + cents
719
720 }
721 }
722 ');
723 end if;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 BEGIN
728 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
729 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
730 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MoneyFormat');
731 DisplayException(fnd_message.get);
732 END;
733
734 END MoneyFormat;
735
736
737 -- This procedure is introduced to get around a Javascript eval function
738 -- bug. When eval a string like "00%99", eval erros out. This new function
739 -- is used only in moneyFormat2.
740 -- If the string passed is all 0, return T else return F.
741
742 -------------------------
743 PROCEDURE MoneyFormat2 IS
744 -------------------------
745 -- Used for formatting the currency exchange rate.
746 l_user_agent varchar2(100);
747
748 BEGIN
749 htp.p('function moneyFormat2(input) {
750
751 var V_input = input + "";
752 var tmp_input = eval(V_input); ');
753
754 GetUserAgent(l_user_agent);
755
756 -- MSIE starts to use scientific notation when the number is less than .00001.
757 IF (l_user_agent = 'IE30') THEN
758 htp.p('if (tmp_input <= 0.00001)
759 return V_input; ');
760 END IF;
761
762 htp.p('if (V_input == "")
763 return("");
764
765 if (tmp_input >= 100)
766 precision = 2;
767 else if (tmp_input >= 10)
768 precision = 3;
769 else if (tmp_input >= 1)
770 precision = 4;
771 else {
772 var mult = 10;
773 precision = 4;
774 for (var i = 1; i < 5; i++) {
775 if (fMultiply(tmp_input, mult) >= 1)
776 break;
777 else {
778 mult = fMultiply( mult, 10 );
779 precision++;
780 }
781 }
782 }
783
784
785 /* if ((eval(V_input) == 0) || (eval(V_input) == 0.0) || (eval(V_input) == 0.00)){
786 if (precision <= 0)
787 return("0");
788 else if (precision == 1)
789 return("0.0");
790 else if (precision == 2)
791 return("0.00");
792 else if (precision == 3)
793 return("0.000");
794 } */
795
796 var dollars = Math.floor(V_input)
797 var tmp = V_input + "0"
798 var multiplier = 10;
799
800 for (var decimalAt = 0; decimalAt < tmp.length; decimalAt++) {
801 if (tmp.charAt(decimalAt)==".") break
802 }
803 for (var i= 0; i < precision-1; i++){
804 multiplier = fMultiply( 10, multiplier );
805 }
806 var cents = "" + Math.round(fMultiply(V_input, multiplier))
807 cents = cents.substring(cents.length-precision, cents.length)
808 if (eval(V_input) < 1) {
809 cents = "" + eval(cents) / multiplier;
810 if (cents.charAt(0)=="0")
811 cents = cents.substring(2,cents.length);
812 else
813 cents = cents.substring(1,cents.length);
814 }
815 dollars += ((tmp.charAt(decimalAt+precision)=="9")&&(top.allZeroString(cents)=="T")) ? 1 : 0;
816 if (precision == 0)
817 return Math.round(input);;
818 else
819 return dollars + "." + cents;
820 }
821 ');
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 BEGIN
826 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
827 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
828 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MoneyFormat2');
829 DisplayException(fnd_message.get);
830 END;
831
832 END MoneyFormat2;
833
834
835 ----------------------
836 PROCEDURE DisplayHelp(v_defHlp IN VARCHAR2) IS
837 ------------------------
838
839 BEGIN
840
841 AP_WEB_WRAPPER_PKG.ICXAdminSig_helpWinScript(v_defHlp);
842
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 BEGIN
847 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
848 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
849 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DisplayHelp');
850 DisplayException(fnd_message.get);
851 END;
852
853 END DisplayHelp;
854
855 --------------------------------
856 PROCEDURE OverrideRequired(p_apprReqCC IN varchar2,
857 p_overrideReq IN varchar2) IS
858 --------------------------------
859
860 BEGIN
861
862 htp.p('function overrideRequired() { ');
863 IF (p_overrideReq = 'Y') THEN
864 htp.p('return true;');
865 ELSIF (p_apprReqCC = 'Y') THEN
866 htp.p('if ((top.ccChanged) || (top.ccOrig != top.tabs.document.startReportForm.CostCenter.value))
867 return true;
868 else return false; ');
869 ELSE
870 htp.p('return false;');
871 END IF;
872 htp.p('}');
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 BEGIN
877 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
878 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
879 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'OverrideRequired');
880 DisplayException(fnd_message.get);
881 END;
882
883 END OverrideRequired;
884
885
886
887 --------------------------------------
888 --- Prepare arguments to be passed when calling a function from
889 --- html. Examples: when fin_parent.splits out the frames, the src
890 --- tag calls a plsql stored procedure with arguments. These args need to
891 --- be "preparg"ed.
892 --------------------------------------
893 PROCEDURE PrepArg(p_arg in out nocopy long) IS
894 BEGIN
895 p_arg := replace(p_arg, '%', '%25');
896 p_arg := replace(p_arg, '&', '%26');
897 p_arg := replace(p_arg, '+', '%2B');
898 p_arg := replace(p_arg, '=', '%3D');
899 p_arg := replace(p_arg, '"', '%22');
900 p_arg := replace(p_arg, '?', '%3F');
901 p_arg := replace(p_arg, '/', '%2F');
902 p_arg := replace(p_arg, ';', '%3B');
903
904 p_arg := replace(p_arg, ' ', '+');
905 p_arg := replace(p_arg, '<', '%3C');
906 p_arg := replace(p_arg, '>', '%3E');
907 p_arg := replace(p_arg, '#', '%23');
908 p_arg := replace(p_arg, '@', '%40');
909 p_arg := replace(p_arg, ':', '%3A');
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 BEGIN
914 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
915 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
916 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'PrepArg');
917 DisplayException(fnd_message.get);
918 END;
919
920 END PrepArg;
921
922
923
924 PROCEDURE DownloadHTML(P_FileName IN VARCHAR2)
925 IS
926 V_FuncCode VARCHAR2(20) := 'AP_WEB_DOWNLOAD';
927 V_FileName VARCHAR2(100);
928 BEGIN
929
930 IF (AP_WEB_INFRASTRUCTURE_PKG.ValidateSession(V_FuncCode)) THEN
931
932 V_FileName := ICX_CALL.decrypt2(P_FileName);
933
934 -- Bug 899146:
935 -- To avoid using http directy, we need to call the
936 -- FND_WEB_CONFIG library to be sure we use the right protocol.
937 OWA_UTIL.Redirect_URL( FND_WEB_CONFIG.WEB_SERVER ||
938 substrb(AP_WEB_INFRASTRUCTURE_PKG.getCSSPath, 2) ||
939 V_FileName);
940 END IF;
941
942 EXCEPTION
943 WHEN OTHERS THEN
944 BEGIN
945 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
946 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
947 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DownloadHTML');
948 DisplayException(fnd_message.get);
949 END;
950
951 END DownloadHTML;
952
953 PROCEDURE GetNextDelimited(
954 P_InputStr IN VARCHAR2,
955 P_StrOffset IN OUT NOCOPY INTEGER,
956 P_DelimitedStr OUT NOCOPY VARCHAR2,
957 P_FoundDelimited OUT NOCOPY BOOLEAN)
958 IS
959 --
960 -- Find the next string delimited in InputStr and write it to DelimitedStr.
961 -- Increments StrOffset set to the offset of the character after the closing
962 -- delimiter.
963 -- If delimiters are not found, then DelimitedStr := NULL and the StrOffset
964 -- is set to length(InputStr)+1 to indicate the string has been processed.
965 -- Does not handle nested delimiters.
966 --
967 OpenDelimitOffset INTEGER;
968 CloseDelimitOffset INTEGER;
969 BEGIN
970 -- Find opening and closing delimiters
971 OpenDelimitOffset := instrb(P_InputStr,C_OpenDelimit,P_StrOffset);
972 CloseDelimitOffset := instrb(P_InputStr,C_CloseDelimit,P_StrOffset);
973
974 -- Check if valid
975 IF (OpenDelimitOffset = 0) OR
976 (CloseDelimitOffset = 0) OR
977 (OpenDelimitOffset >= CloseDelimitOffset) THEN
978 P_DelimitedStr := NULL;
979 P_FoundDelimited := FALSE;
980 RETURN;
981 END IF;
982
983 -- Extract substring
984 P_DelimitedStr := substrb(P_InputStr,P_StrOffset+1,
985 CloseDelimitOffset-OpenDelimitOffset-1);
986
987 -- Update offset
988 P_StrOffset := P_StrOffset + CloseDelimitOffset - OpenDelimitOffset + 1;
989
990 P_FoundDelimited := TRUE;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 BEGIN
995 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
996 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
997 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetNextDelimited');
998 DisplayException(fnd_message.get);
999 END;
1000
1001 END GetNextDelimited;
1002
1003 FUNCTION ContainsError(
1004 P_MessageArray IN receipt_error_stack)
1005 RETURN BOOLEAN
1006
1007 -- Sets P_ReceiptContainsError to 'Y' if P_ReceiptNumber has an error
1008 -- otherwise returns 'N'. Assumes that messages are in order of receipt
1009 -- number
1010
1011 IS
1012 V_NumOfMessages INTEGER;
1013 V_ContainsError VARCHAR2(1);
1014 BEGIN
1015
1016 -- Assume there are no errors
1017 V_ContainsError := 'N';
1018
1019 -- Set which receipts have errors
1020 V_NumOfMessages := P_MessageArray.count;
1021 for I in 1..V_NumOfMessages loop
1022
1023 -- Check for empty messages
1024 if NOT (P_MessageArray(I).error_text IS NULL) then
1025 V_ContainsError := 'Y';
1026 end if;
1027
1028 end loop;
1029
1030 return V_ContainsError = 'Y';
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 BEGIN
1035 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1036 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1037 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ContainsError');
1038 DisplayException(fnd_message.get);
1039 END;
1040
1041
1042 END ContainsError;
1043
1044 FUNCTION ContainsWarning(
1045 P_MessageArray IN receipt_error_stack)
1046 RETURN BOOLEAN
1047
1048 -- Sets P_ReceiptContainsError to 'Y' if P_ReceiptNumber has an error
1049 -- otherwise returns 'N'. Assumes that messages are in order of receipt
1050 -- number
1051
1052 IS
1053 V_NumOfMessages INTEGER;
1054 V_ContainsWarning VARCHAR2(1);
1055 BEGIN
1056
1057 -- Assume there are no errors
1058 V_ContainsWarning := 'N';
1059
1060 -- Set which receipts have errors
1061 V_NumOfMessages := P_MessageArray.count;
1062 for I in 1..V_NumOfMessages loop
1063
1064 -- Check for empty messages
1065 if NOT (P_MessageArray(I).warning_text IS NULL) then
1066 V_ContainsWarning := 'Y';
1067 end if;
1068
1069 end loop;
1070
1071 return V_ContainsWarning = 'Y';
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 BEGIN
1076 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1077 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1078 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ContainsWarning');
1079 DisplayException(fnd_message.get);
1080 END;
1081
1082 END ContainsWarning;
1083
1084 FUNCTION ContainsErrorOrWarning(
1085 P_MessageArray IN receipt_error_stack)
1086 RETURN BOOLEAN
1087
1088 -- Sets P_ReceiptContainsError to 'Y' if P_ReceiptNumber has an error
1089 -- otherwise returns 'N'. Assumes that messages are in order of receipt
1090 -- number
1091
1092 IS
1093 I BINARY_INTEGER;
1094
1095 BEGIN
1096 --chiho:1330572:
1097 IF ( P_MessageArray.COUNT > 0 ) THEN
1098 I := P_MessageArray.FIRST;
1099 LOOP
1100 IF ((P_MessageArray(I).warning_text IS NOT NULL) OR
1101 (P_MessageArray(I).error_text IS NOT NULL)) THEN
1102 RETURN TRUE;
1103 END IF;
1104
1105 EXIT WHEN I = P_MessageArray.LAST;
1106
1107 I := P_MessageArray.NEXT( I );
1108
1109 END LOOP;
1110 END IF;
1111
1112 RETURN FALSE;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 BEGIN
1117 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1118 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1119 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ContainsErrorOrWarning');
1120 DisplayException(fnd_message.get);
1121 END;
1122
1123 END ContainsErrorOrWarning;
1124
1125 FUNCTION ReceiptContainsError(
1126 P_MessageArray IN receipt_error_stack,
1127 P_ReceiptNumber IN INTEGER)
1128 RETURN BOOLEAN
1129
1130 -- Returns TRUE if P_ReceiptNumber has an error
1131 -- otherwise returns FALSE. Assumes that messages are in order of receipt
1132 -- number
1133
1134 IS
1135 BEGIN
1136
1137 -- htp.p('receipt contains error ' || p_receiptnumber || ', ' || p_messagearray.count
1138 -- || ', ' || p_messagearray(p_receiptnumber).error_text || '<BR>');
1139 --chiho:1330572:
1140 IF ( P_MessageArray.EXISTS(P_ReceiptNumber) ) THEN
1141
1142 -- Set which receipts have errors
1143 return (P_MessageArray(P_ReceiptNumber).error_text IS NOT NULL);
1144
1145 ELSE
1146 return FALSE;
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 BEGIN
1152 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1153 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1154 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ReceiptContainsError');
1155 DisplayException(fnd_message.get);
1156 END;
1157
1158 END ReceiptContainsError;
1159
1160 FUNCTION ReceiptContainsWarning(
1161 P_MessageArray IN receipt_error_stack,
1162 P_ReceiptNumber IN INTEGER)
1163 RETURN BOOLEAN
1164
1165 -- Returns TRUE if P_ReceiptNumber has an error
1166 -- otherwise returns FALSE. Assumes that messages are in order of receipt
1167 -- number
1168
1169 IS
1170 BEGIN
1171
1172 -- chiho:1330572:
1173 IF (P_MessageArray.EXISTS(P_ReceiptNumber)) THEN
1174 -- Set which receipts have errors
1175 RETURN ( P_MessageArray(P_ReceiptNumber).warning_text IS NOT NULL);
1176 ELSE
1177 RETURN FALSE;
1178 END IF;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 BEGIN
1183 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1184 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1185 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ReceiptContainsWarning');
1186 DisplayException(fnd_message.get);
1187 END;
1188
1189 END ReceiptContainsWarning;
1190
1191 FUNCTION FieldContainsError(
1192 P_MessageArray IN receipt_error_stack,
1193 P_ReceiptNumber IN INTEGER,
1194 P_FieldNumber IN VARCHAR2)
1195 RETURN BOOLEAN
1196
1197 -- Returns TRUE if P_ReceiptNumber has an error
1198 -- otherwise returns FALSE. Assumes that messages are in order of receipt
1199 -- number
1200
1201 IS
1202 V_NumOfMessages INTEGER;
1203 V_FieldContainsError VARCHAR2(1);
1204 BEGIN
1205
1206 -- Assume there are no errors
1207 V_FieldContainsError := 'N';
1208
1209 -- Set which receipts have errors
1210 if (instrb(P_MessageArray(P_ReceiptNumber).error_fields, P_FieldNumber) > 0) then
1211 V_FieldContainsError := 'Y';
1212 end if;
1213
1214 return V_FieldContainsError = 'Y';
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 BEGIN
1218 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1219 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1220 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'FieldContainsError');
1221 DisplayException(fnd_message.get);
1222 END;
1223
1224 END FieldContainsError;
1225
1226 FUNCTION NumOfReceiptWithError(
1227 P_MessageArray IN receipt_error_stack)
1228 RETURN NUMBER
1229 IS
1230 V_NumOfReceiptWithError INTEGER;
1231 V_NumOfMessages INTEGER;
1232 V_NumOfReceipts INTEGER;
1233 V_MaxReceiptNum INTEGER;
1234 V_TempArray Number_Array;
1235 I BINARY_INTEGER;
1236 BEGIN
1237
1238 V_NumOfReceiptWithError := 0;
1239
1240 -- Get max number of receipts
1241 V_NumOfMessages := P_MessageArray.count;
1242 V_MaxReceiptNum := 0;
1243 V_NumOfReceiptWithError := 0;
1244
1245 --chiho:1330572:
1246 IF ( P_MessageArray.COUNT > 0 ) THEN
1247 I := P_MessageArray.FIRST;
1248
1249 LOOP
1250 -- check for empty receipts
1251 IF (ReceiptContainsError(P_MessageArray, I)) THEN
1252 V_NumOfReceiptWithError := V_NumOfReceiptWithError + 1;
1253 END IF;
1254 EXIT WHEN I = P_MessageArray.LAST;
1255 I := P_MessageArray.NEXT( I );
1256
1257 END LOOP;
1258 END IF;
1259
1260 RETURN V_NumOfReceiptWithError;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 BEGIN
1265 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1266 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1267 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'NumOfReceiptWithError');
1268 DisplayException(fnd_message.get);
1269 END;
1270
1271 END NumOfReceiptWithError;
1272
1273 FUNCTION NumOfReceiptWithWarning(
1274 P_MessageArray IN receipt_error_stack)
1275 RETURN NUMBER
1276 IS
1277 V_NumOfMessages INTEGER;
1278 V_NumOfReceiptWithWarning INTEGER;
1279 I INTEGER;
1280 BEGIN
1281
1282 V_NumOfReceiptWithWarning := 0;
1283
1284 -- Get max number of receipts
1285 V_NumOfMessages := P_MessageArray.count;
1286 for I in 1..V_NumOfMessages loop
1287 -- check for empty receipts
1288 if (ReceiptContainsWarning(P_MessageArray, I)) then
1289 V_NumOfReceiptWithWarning := V_NumOfReceiptWithWarning + 1;
1290 end if;
1291 end loop;
1292
1293 return V_NumOfReceiptWithWarning;
1294
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 BEGIN
1298 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1299 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1300 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'NumOfReceiptWithWarning' );
1301 DisplayException(fnd_message.get);
1302 END;
1303
1304 END NumOfReceiptWithWarning;
1305
1306 FUNCTION NumOfValidReceipt(
1307 P_MessageArray IN receipt_error_stack)
1308 RETURN NUMBER
1309 IS
1310 V_NumOfValidReceipt INTEGER;
1311 V_NumOfMessages INTEGER;
1312 V_NumOfReceipts INTEGER;
1313 V_MaxReceiptNum INTEGER;
1314 V_TempArray Number_Array;
1315 I INTEGER;
1316 BEGIN
1317
1318 V_NumOfValidReceipt := 0;
1319
1320 -- Get max number of receipts
1321 V_NumOfMessages := P_MessageArray.count;
1322 for I in 1..V_NumOfMessages loop
1323 -- check for empty receipts
1324
1325 if ((not ReceiptContainsError(P_MessageArray, I)) and
1326 (not ReceiptContainsWarning(P_MessageArray, I))) then
1327 V_NumOfValidReceipt := V_NumOfValidReceipt + 1;
1328 end if;
1329 end loop;
1330
1331 return V_NumOfValidReceipt;
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 BEGIN
1335 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1336 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1337 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'NumOfValidReceipt');
1338 DisplayException(fnd_message.get);
1339 END;
1340
1341 END NumOfValidReceipt;
1342
1343 PROCEDURE AddMessage(
1344 P_MessageArray IN OUT NOCOPY receipt_error_stack,
1345 P_ReceiptNum IN INTEGER,
1346 P_MessageType IN VARCHAR2,
1347 P_MessageText IN VARCHAR2,
1348 P_Field1 IN VARCHAR2,
1349 P_Field2 IN VARCHAR2,
1350 P_Field3 IN VARCHAR2,
1351 P_Field4 IN VARCHAR2,
1352 P_Field5 IN VARCHAR2)
1353
1354 -- Inserts a message into P_MessageArray so that messages are in the
1355 -- order of receipt number
1356
1357 IS
1358 V_MessageFields VARCHAR2(100);
1359 V_MessageText VARCHAR2(2000); -- New variable for NewUI
1360 BEGIN
1361
1362 -- Added for NewUI
1363 -- Replaced P_MessageText with V_MessageText
1364
1365
1366 fnd_message.set_encoded(P_MessageText);
1367 V_MessageText := fnd_message.get();
1368 fnd_message.set_encoded(P_MessageText);
1369 fnd_msg_pub.add();
1370
1371
1372 -- Append message fields
1373 if NOT P_Field1 IS NULL then
1374 V_MessageFields := V_MessageFields ||
1375 C_OpenDelimit || P_Field1 || C_CloseDelimit;
1376 end if;
1377 if NOT P_Field2 IS NULL then
1378 V_MessageFields := V_MessageFields ||
1379 C_OpenDelimit || P_Field2 || C_CloseDelimit;
1380 end if;
1381 if NOT P_Field3 IS NULL then
1382 V_MessageFields := V_MessageFields ||
1383 C_OpenDelimit || P_Field3 || C_CloseDelimit;
1384 end if;
1385 if NOT P_Field4 IS NULL then
1386 V_MessageFields := V_MessageFields ||
1387 C_OpenDelimit || P_Field4 || C_CloseDelimit;
1388 end if;
1389 if NOT P_Field5 IS NULL then
1390 V_MessageFields := V_MessageFields ||
1391 C_OpenDelimit || P_Field5 || C_CloseDelimit;
1392 end if;
1393
1394 if P_MessageType = C_ErrorMessageType then
1395 -- chiho:1203036:get the sub-string up to the maxima length:
1396 -- Append message text
1397 IF ( LENGTH(P_MessageArray(P_ReceiptNum).error_text ||
1398 C_OpenDelimit || V_MessageText || C_CloseDelimit) <= C_MSG_TEXT_LEN ) THEN
1399 P_MessageArray(P_ReceiptNum).error_text := P_MessageArray(P_ReceiptNum).error_text ||
1400 C_OpenDelimit || V_MessageText || C_CloseDelimit;
1401 END IF;
1402 -- Append message fields
1403 IF ( LENGTH(P_MessageArray(P_ReceiptNum).error_fields || C_OpenDelimit || V_MessageFields || C_CloseDelimit) <= C_MSG_FIELD_LEN ) THEN
1404 P_MessageArray(P_ReceiptNum).error_fields :=
1405 P_MessageArray(P_ReceiptNum).error_fields ||
1406 C_OpenDelimit || V_MessageFields || C_CloseDelimit;
1407 END IF;
1408
1409 elsif P_MessageType = C_WarningMessageType then
1410
1411 -- Append message text
1412 IF ( LENGTH(P_MessageArray(P_ReceiptNum).warning_text ||
1413 C_OpenDelimit || V_MessageText || C_CloseDelimit) <= C_MSG_TEXT_LEN ) THEN
1414 P_MessageArray(P_ReceiptNum).warning_text :=
1415 P_MessageArray(P_ReceiptNum).warning_text ||
1416 C_OpenDelimit || V_MessageText || C_CloseDelimit;
1417 END IF;
1418
1419 -- Append message fields
1420 IF ( LENGTH(P_MessageArray(P_ReceiptNum).warning_fields ||
1421 C_OpenDelimit || V_MessageFields || C_CloseDelimit) <= C_MSG_FIELD_LEN ) THEN
1422 P_MessageArray(P_ReceiptNum).warning_fields :=
1423 P_MessageArray(P_ReceiptNum).warning_fields || C_OpenDelimit || V_MessageFields || C_CloseDelimit;
1424 END IF;
1425 end if;
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 BEGIN
1429 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1430 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1431 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'AddMessage');
1432 DisplayException(fnd_message.get);
1433 END;
1434
1435 END AddMessage;
1436
1437
1438 ------------------------------------------------------------------------
1439 -- rlangi: Diagnostic Logging wrappers
1440 ------------------------------------------------------------------------
1441 PROCEDURE LogException(p_pkgname IN VARCHAR2,
1442 p_message IN VARCHAR2) IS
1443 BEGIN
1444 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1445 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, p_pkgname, p_message);
1446 end if;
1447 END LogException;
1448
1449 PROCEDURE LogEvent (p_pkgname IN VARCHAR2,
1450 p_message IN VARCHAR2) IS
1451 BEGIN
1452 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1453 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, p_pkgname, p_message);
1454 end if;
1455 END LogEvent;
1456
1457 PROCEDURE LogProcedure(p_pkgname IN VARCHAR2,
1458 p_message IN VARCHAR2) IS
1459 BEGIN
1460 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1461 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, p_pkgname, p_message);
1462 end if;
1463 END LogProcedure;
1464
1465 PROCEDURE LogStatement(p_pkgname IN VARCHAR2,
1466 p_message IN VARCHAR2) IS
1467 BEGIN
1468 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1469 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_pkgname, p_message);
1470 end if;
1471 END LogStatement;
1472
1473
1474 ------------------------------------------------------------------------
1475 -- Add an expError Record type
1476 ------------------------------------------------------------------------
1477 PROCEDURE AddExpError(p_errors IN OUT NOCOPY expError,
1478 p_text IN VARCHAR2,
1479 p_type IN VARCHAR2,
1480 p_field IN VARCHAR2,
1481 p_index IN BINARY_INTEGER,
1482 p_MessageCategory IN VARCHAR2,
1483 p_IsMobileApp IN BOOLEAN) IS
1484 l_count INTEGER := 1;
1485 -- for bug 1827501
1486 l_text varchar2(2000);
1487 BEGIN
1488
1489 l_text := p_text;
1490
1491 -- Introduced the set_encoded() and fnd_msg_pub.add() for the newUI
1492
1493 l_count := p_errors.COUNT + 1;
1494 fnd_message.set_encoded(l_text);
1495
1496 if (p_IsMobileApp = true AND p_MessageCategory <> C_OtherMessageCategory) then
1497 if (p_MessageCategory = C_PAMessageCategory) then
1498 fnd_message.set_name('SQLAP', 'AP_OME_PA_ERROR');
1499 p_errors(l_count).text := fnd_message.get_encoded();
1500 l_text := p_errors(l_count).text;
1501 elsif (p_MessageCategory = C_PATCMessageCategory) then
1502 fnd_message.set_name('SQLAP', 'AP_OME_PATC_ERROR');
1503 p_errors(l_count).text := fnd_message.get_encoded();
1504 l_text := p_errors(l_count).text;
1505 elsif (p_MessageCategory = C_TaxMessageCategory) then
1506 fnd_message.set_name('SQLAP', 'AP_OME_TAX_ERROR');
1507 p_errors(l_count).text := fnd_message.get_encoded();
1508 l_text := p_errors(l_count).text;
1509 elsif (p_MessageCategory = C_ItemizationMessageCategory) then
1510 fnd_message.set_name('SQLAP', 'AP_OME_ITEMIZATION_ERROR');
1511 p_errors(l_count).text := fnd_message.get_encoded();
1512 l_text := p_errors(l_count).text;
1513 elsif (p_MessageCategory = C_DFFMessageCategory) then
1514 fnd_message.set_name('SQLAP', 'AP_OME_DFF_ERROR');
1515 p_errors(l_count).text := fnd_message.get_encoded();
1516 l_text := p_errors(l_count).text;
1517 end if;
1518 else
1519 if (p_MessageCategory = C_PATCMessageCategory) then
1520 fnd_message.set_name('SQLAP', 'OIE_PATC_MSG');
1521 fnd_message.set_token('MESSAGE', p_text);
1522 l_text := fnd_message.get_encoded();
1523 p_errors(l_count).text := AP_WEB_DB_UTIL_PKG.jsPrepString(p_text);
1524 elsif (p_MessageCategory = C_GMSMessageCategory) then
1525 fnd_message.set_name('SQLAP', 'OIE_GMS_MSG');
1526 fnd_message.set_token('MESSAGE', p_text);
1527 l_text := fnd_message.get_encoded();
1528 p_errors(l_count).text := AP_WEB_DB_UTIL_PKG.jsPrepString(p_text);
1529 else
1530 p_errors(l_count).text := AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get());
1531 end if;
1532 end if;
1533
1534 p_errors(l_count).type := p_type;
1535 p_errors(l_count).field := p_field;
1536 p_errors(l_count).ind := p_index;
1537 fnd_message.set_encoded(l_text);
1538 fnd_msg_pub.add();
1539
1540 END AddExpError;
1541
1542
1543 ------------------------------------------------------------------------
1544 -- Add an expError Record type with an unencoded/hardcoded message
1545 ------------------------------------------------------------------------
1546 PROCEDURE AddExpErrorNotEncoded(p_errors IN OUT NOCOPY expError,
1547 p_text IN VARCHAR2,
1548 p_type IN VARCHAR2,
1549 p_field IN VARCHAR2,
1550 p_index IN BINARY_INTEGER,
1551 p_MessageCategory IN VARCHAR2)
1552 IS
1553
1554 l_count INTEGER := 1;
1555 l_IsMobileApp BOOLEAN;
1556
1557 BEGIN
1558
1559 l_IsMobileApp := IsMobileApp;
1560 fnd_message.set_name('SQLAP', 'OIE_NOT_ENCODED_MSG');
1561 fnd_message.set_token('MESSAGE', p_text);
1562 AddExpError(p_errors,
1563 fnd_message.get_encoded(),
1564 p_type,
1565 p_field,
1566 p_index,
1567 p_MessageCategory,
1568 l_IsMobileApp);
1569
1570 END AddExpErrorNotEncoded;
1571
1572
1573 PROCEDURE PrintMessages(P_SrcReceiptStack IN
1574 receipt_error_stack)
1575 IS
1576 I INTEGER;
1577 BEGIN
1578 FOR I IN 1..P_SrcReceiptStack.count LOOP
1579
1580 htp.p(
1581 to_char(I) || ', ' ||
1582 P_SrcReceiptStack(I).error_text || ', ' ||
1583 P_SrcReceiptStack(I).error_fields || '. ' ||
1584 P_SrcReceiptStack(I).warning_text || ', ' ||
1585 P_SrcReceiptStack(I).warning_fields || '.<BR>');
1586
1587 END LOOP;
1588
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 BEGIN
1592 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1593 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1594 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'PrintMessage');
1595 DisplayException(fnd_message.get);
1596 END;
1597
1598 END PrintMessages;
1599
1600 PROCEDURE InitMessages(P_NumOfReceipts IN INTEGER,
1601 P_SrcReceiptStack OUT
1602 receipt_error_stack)
1603 IS
1604 I INTEGER;
1605 BEGIN
1606 FOR I IN 1..P_NumOfReceipts LOOP
1607 P_SrcReceiptStack(I) := NULL;
1608 END LOOP;
1609 END InitMessages;
1610
1611 PROCEDURE MergeErrorStacks(P_ReceiptNum IN INTEGER,
1612 P_Src1ReceiptStack IN
1613 receipt_error_stack,
1614 P_Src2ReceiptStack IN
1615 receipt_error_stack,
1616 P_TargetReceiptStack IN OUT
1617 receipt_error_stack)
1618 IS
1619 I INTEGER;
1620 BEGIN
1621
1622 FOR I IN 1..P_ReceiptNum LOOP
1623
1624 -- Append message text
1625 IF ( LENGTH(P_Src1ReceiptStack(I).error_text ||
1626 P_Src2ReceiptStack(I).error_text ) < C_MSG_TEXT_LEN ) THEN
1627 P_TargetReceiptStack(I).error_text :=
1628 P_Src1ReceiptStack(I).error_text ||
1629 P_Src2ReceiptStack(I).error_text;
1630 END IF;
1631
1632 -- Append message fields
1633 IF ( LENGTH( P_Src1ReceiptStack(I).error_fields ||
1634 P_Src2ReceiptStack(I).error_fields ) < C_MSG_FIELD_LEN ) THEN
1635 P_TargetReceiptStack(I).error_fields :=
1636 P_Src1ReceiptStack(I).error_fields ||
1637 P_Src2ReceiptStack(I).error_fields;
1638 END IF;
1639
1640 -- Append message text
1641 IF ( LENGTH(P_Src1ReceiptStack(I).warning_text ||
1642 P_Src2ReceiptStack(I).warning_text) < C_MSG_TEXT_LEN ) THEN
1643 P_TargetReceiptStack(I).warning_text :=
1644 P_Src1ReceiptStack(I).warning_text ||
1645 P_Src2ReceiptStack(I).warning_text;
1646 END IF;
1647
1648 -- Append message fields
1649 IF ( LENGTH(P_Src1ReceiptStack(I).warning_fields ||
1650 P_Src2ReceiptStack(I).warning_fields) < C_MSG_FIELD_LEN ) THEN
1651 P_TargetReceiptStack(I).warning_fields :=
1652 P_Src1ReceiptStack(I).warning_fields ||
1653 P_Src2ReceiptStack(I).warning_fields;
1654 END IF;
1655
1656 END LOOP;
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 BEGIN
1660 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1661 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1662 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MergeErrorStacks');
1663 DisplayException(fnd_message.get);
1664 END;
1665
1666 END MergeErrorStacks;
1667
1668 PROCEDURE MergeErrors(P_ExpErrors IN expError,
1669 P_TargetReceiptStack IN OUT NOCOPY receipt_error_stack)
1670 IS
1671 I INTEGER;
1672 J INTEGER := 0;
1673 BEGIN
1674
1675 FOR I IN 1..P_ExpErrors.COUNT LOOP
1676 J := P_ExpErrors(I).ind;
1677
1678 IF (J IS NULL OR NOT (J> 0)) THEN
1679 J := P_TargetReceiptStack.COUNT + 1;
1680 END IF;
1681
1682 BEGIN
1683 IF (P_ExpErrors(I).type = C_ErrorMessageType) THEN
1684 -- Append message text
1685 P_TargetReceiptStack(J).error_text :=
1686 P_TargetReceiptStack(J).error_text || C_openDelimit ||
1687 P_ExpErrors(I).text || C_closeDelimit;
1688
1689 -- Append message fields
1690 P_TargetReceiptStack(J).error_fields :=
1691 P_TargetReceiptStack(J).error_fields || C_openDelimit ||
1692 P_ExpErrors(I).field || C_closeDelimit;
1693 ELSE --if noi type then assume that expError is a warning
1694 -- Append message text
1695 P_TargetReceiptStack(J).warning_text :=
1696 P_TargetReceiptStack(J).warning_text || C_openDelimit ||
1697 P_ExpErrors(I).text || C_closeDelimit;
1698
1699 -- Append message fields
1700 P_TargetReceiptStack(J).warning_fields :=
1701 P_TargetReceiptStack(J).warning_fields || C_openDelimit ||
1702 P_ExpErrors(I).field || C_closeDelimit;
1703 END IF;
1704 EXCEPTION WHEN NO_DATA_FOUND THEN
1705 NULL; --leave the target receipt stack as is
1706 END;
1707 END LOOP;
1708
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 BEGIN
1712 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1713 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1714 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MergeErrors');
1715 DisplayException(fnd_message.get);
1716 END;
1717
1718 END MergeErrors;
1719
1720 PROCEDURE MergeExpErrors(P_Src1 IN OUT NOCOPY expError,
1721 P_Src2 IN expError)
1722 IS
1723 I INTEGER;
1724 J INTEGER;
1725 L_ReceiptNum INTEGER := 0;
1726 L_Src1Count INTEGER := 0;
1727 BEGIN
1728
1729 L_Src1Count := P_Src1.Count;
1730 L_ReceiptNum := P_Src1.Count + P_Src2.Count;
1731 J := 1;
1732
1733 FOR I IN (L_Src1Count+1)..L_ReceiptNum LOOP
1734 -- Append message text
1735 P_Src1(I).text := P_Src2(J).text;
1736 -- Append message fields
1737 P_Src1(I).field := P_Src2(J).field;
1738 -- Append message type
1739 P_Src1(I).type := P_Src2(J).type;
1740 -- Append message index
1741 P_Src1(I).ind := P_Src2(J).ind;
1742
1743 J := J + 1;
1744 END LOOP;
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 BEGIN
1748 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1749 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1750 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MergeExpErrors');
1751 DisplayException(fnd_message.get);
1752 END;
1753
1754 END MergeExpErrors;
1755
1756
1757 PROCEDURE ClearMessages(
1758 P_TargetReceiptStack OUT NOCOPY receipt_error_stack)
1759 IS
1760 V_NumMessages INTEGER;
1761 I INTEGER;
1762 BEGIN
1763
1764 -- Copy each receipt
1765 V_NumMessages := P_TargetReceiptStack.count;
1766 FOR I IN 1..V_NumMessages LOOP
1767
1768 P_TargetReceiptStack(I).error_text := NULL;
1769 P_TargetReceiptStack(I).error_fields := NULL;
1770 P_TargetReceiptStack(I).warning_text := NULL;
1771 P_TargetReceiptStack(I).warning_fields := NULL;
1772
1773 END LOOP;
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 BEGIN
1777 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1778 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1779 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ClearMessages');
1780 DisplayException(fnd_message.get);
1781 END;
1782
1783 END ClearMessages;
1784
1785 PROCEDURE CopyMessages(
1786 P_SrcReceiptStack IN receipt_error_stack,
1787 P_TargetReceiptStack IN OUT NOCOPY receipt_error_stack)
1788 IS
1789 V_NumMessages INTEGER;
1790 V_NumMessagesCopied INTEGER;
1791 I INTEGER;
1792 BEGIN
1793
1794 -- Clear out the target
1795 ClearMessages(P_TargetReceiptStack);
1796
1797 -- Copy each receipt
1798 V_NumMessages := P_SrcReceiptStack.count;
1799 FOR I IN 1..V_NumMessages LOOP
1800
1801 P_TargetReceiptStack(I).error_text :=
1802 P_SrcReceiptStack(I).error_text;
1803 P_TargetReceiptStack(I).error_fields :=
1804 P_SrcReceiptStack(I).error_fields;
1805
1806 P_TargetReceiptStack(I).warning_text :=
1807 P_SrcReceiptStack(I).warning_text;
1808 P_TargetReceiptStack(I).warning_fields :=
1809 P_SrcReceiptStack(I).warning_fields;
1810
1811 END LOOP;
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 BEGIN
1815 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1816 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1817 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CopyMessages');
1818 DisplayException(fnd_message.get);
1819 END;
1820
1821 END CopyMessages;
1822
1823 PROCEDURE ArrayifyFields(P_ErrorField IN VARCHAR2,
1824 P_ErrorFieldArray OUT NOCOPY Number_Array)
1825 IS
1826
1827 I INTEGER;
1828 V_ErrorOffset INTEGER; -- Offset into P_ErrorField.error_fields
1829 -- which keeps track of portion processed.
1830 V_ErrorLength INTEGER; -- Length of P_ErrorField.error_fields
1831 V_ErrorFieldNum VARCHAR2(10);-- Field wth error.
1832
1833 V_MessageCount INTEGER;
1834 V_MessageNum INTEGER;
1835 V_FoundDelimited BOOLEAN;
1836 V_MaxNumOfErrorField INTEGER := 5;
1837 BEGIN
1838
1839 -- Parse P_ErrorField and set error fields to TRUE
1840 V_ErrorOffset := 1;
1841 V_ErrorLength := Length(P_ErrorField);
1842 V_MessageNum := 1;
1843
1844 I := 1;
1845 WHILE (V_ErrorOffset <= V_ErrorLength) LOOP
1846
1847 -- Remove grouping open delimiter
1848 IF (substrb(P_ErrorField, 1, 1) = C_OpenDelimit) THEN
1849 V_ErrorOffset := V_ErrorOffset + 1;
1850 ELSE
1851 EXIT; -- break if error
1852 END IF;
1853
1854 -- Parse fields for message
1855 V_FoundDelimited := TRUE;
1856 WHILE (V_ErrorOffset <= V_ErrorLength) AND V_FoundDelimited LOOP
1857
1858 --- Get next error field and check whether error occurred during parsing
1859 GetNextDelimited(P_ErrorField, V_ErrorOffset, V_ErrorFieldNum,
1860 V_FoundDelimited);
1861
1862 IF NOT V_FoundDelimited THEN
1863 EXIT;
1864 END IF;
1865
1866 P_ErrorFieldArray(I) := TO_NUMBER(V_ErrorFieldNum);
1867 I := I+1;
1868
1869 END LOOP;
1870
1871 -- Remove grouping close delimiter
1872 IF (substrb(P_ErrorField, v_erroroffset, 1) = C_CloseDelimit) THEN
1873 V_ErrorOffset := V_ErrorOffset + 1;
1874 ELSE
1875 EXIT; -- break if error
1876 END IF;
1877
1878 END LOOP;
1879
1880 EXCEPTION
1881 WHEN VALUE_ERROR THEN
1882 -- If cannot convert ErrorFieldNum to a number then
1883 -- do not specify that field as highlighted. Ignore the
1884 -- rest of the string;
1885 RETURN;
1886
1887 END ArrayifyFields;
1888
1889 PROCEDURE ArrayifyErrorFields(P_ReceiptErrors IN receipt_error_stack,
1890 P_ReceiptNum IN INTEGER,
1891 P_ErrorFieldArray OUT NOCOPY Number_Array)
1892 IS
1893 BEGIN
1894 ArrayifyFields(P_ReceiptErrors(P_ReceiptNum).error_fields,
1895 P_ErrorFieldArray);
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 BEGIN
1900 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1901 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1902 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ArrayifErrorFields');
1903 DisplayException(fnd_message.get);
1904 END;
1905
1906 END ArrayifyErrorFields;
1907
1908 PROCEDURE ArrayifyWarningFields(P_ReceiptErrors IN receipt_error_stack,
1909 P_ReceiptNum IN INTEGER,
1910 P_ErrorFieldArray OUT NOCOPY Number_Array)
1911 IS
1912 BEGIN
1913 ArrayifyFields(P_ReceiptErrors(P_ReceiptNum).warning_fields,
1914 P_ErrorFieldArray);
1915
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 BEGIN
1919 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1920 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1921 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ArrayifWarningFields');
1922 DisplayException(fnd_message.get);
1923 END;
1924
1925 END ArrayifyWarningFields;
1926
1927 PROCEDURE ArrayifyText(P_ErrorText IN LONG,
1928 P_ErrorTextArray OUT NOCOPY LongString_Array)
1929 IS
1930
1931 I INTEGER;
1932 V_ErrorOffset INTEGER; -- Offset into P_ErrorField.error_fields
1933 -- which keeps track of portion processed.
1934 V_ErrorLength INTEGER; -- Length of P_ErrorField.error_fields
1935 V_ErrorFieldNum VARCHAR2(10);-- Field wth error.
1936
1937 V_MessageCount INTEGER;
1938 V_MessageNum INTEGER;
1939 V_FoundDelimited BOOLEAN;
1940 V_MaxNumOfErrorField INTEGER := 5;
1941 V_NewMessageIndex NUMBER;
1942 V_NewMessageCount NUMBER;
1943
1944 V_ErrorText LONG;
1945 BEGIN
1946
1947 -- Parse error message text first
1948 V_ErrorOffset := 1;
1949 V_ErrorLength := Length(P_ErrorText);
1950
1951 -- Loop through error message
1952 V_FoundDelimited := TRUE;
1953 V_NewMessageCount := 1;
1954 WHILE (V_ErrorOffset <= V_ErrorLength) AND V_FoundDelimited LOOP
1955
1956 --- Get next error field and check whether error occurred during parsing
1957 GetNextDelimited(P_ErrorText, V_ErrorOffset, V_ErrorText, V_FoundDelimited);
1958
1959 -- Set field values in structure
1960 P_ErrorTextArray(V_NewMessageCount) := substrb(V_ErrorText,1,1000);
1961 V_NewMessageCount := V_NewMessageCount + 1;
1962 END LOOP;
1963
1964 EXCEPTION
1965 WHEN VALUE_ERROR THEN
1966 -- If cannot convert ErrorFieldNum to a number then
1967 -- do not specify that field as highlighted. Ignore the
1968 -- rest of the string;
1969 RETURN;
1970
1971 END ArrayifyText;
1972
1973 PROCEDURE ArrayifyErrorText(P_ReceiptErrors IN receipt_error_stack,
1974 P_ReceiptNum IN INTEGER,
1975 P_ErrorTextArray OUT NOCOPY LongString_Array)
1976 IS
1977 BEGIN
1978 ArrayifyText(P_ReceiptErrors(P_ReceiptNum).error_text, P_ErrorTextArray);
1979
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 BEGIN
1983 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1984 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1985 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ArrayifErrorText');
1986 DisplayException(fnd_message.get);
1987 END;
1988
1989 END ArrayifyErrorText;
1990
1991 PROCEDURE ArrayifyWarningText(P_ReceiptErrors IN receipt_error_stack,
1992 P_ReceiptNum IN INTEGER,
1993 P_ErrorTextArray OUT NOCOPY LongString_Array)
1994 IS
1995 BEGIN
1996 ArrayifyText(P_ReceiptErrors(P_ReceiptNum).warning_text, P_ErrorTextArray);
1997
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000 BEGIN
2001 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2002 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2003 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ArrayifWarningText');
2004 DisplayException(fnd_message.get);
2005 END;
2006
2007 END ArrayifyWarningText;
2008
2009
2010
2011
2012 PROCEDURE ConvertDate IS
2013
2014 l_jan VARCHAR2(12);
2015 l_feb VARCHAR2(12);
2016 l_mar VARCHAR2(12);
2017 l_apr VARCHAR2(12);
2018 l_may VARCHAR2(12);
2019 l_jun VARCHAR2(12);
2020 l_jul VARCHAR2(12);
2021 l_aug VARCHAR2(12);
2022 l_sep VARCHAR2(12);
2023 l_oct VARCHAR2(12);
2024 l_nov VARCHAR2(12);
2025 l_dec VARCHAR2(12);
2026
2027 l_index_d1 NUMBER := 0; -- Day
2028 l_index_d2 NUMBER := 0;
2029 l_index_m1 NUMBER := 0; -- Month
2030 l_index_m2 NUMBER := 0;
2031 l_index_y1 NUMBER := 0; -- Year
2032 l_index_y2 NUMBER := 0;
2033 l_index_del1 NUMBER := 0; -- delimeter 1.
2034 l_index_del2 NUMBER := 0; -- delimeter 2.
2035 l_mon_format Boolean := FALSE;
2036
2037 l_delimeter VARCHAR2(1) := NULL ; -- delimeter.
2038 l_date_format VARCHAR2(20);
2039 l_invalid_date_msg VARCHAR2(2000);
2040 l_date_not_allowed_msg VARCHAR2(2000);
2041 debug_info VARCHAR2(100);
2042
2043 BEGIN
2044
2045 -- chiho:1283146:get the icx info directly rather through any package routines
2046 -- which seems to have some problems...
2047 debug_info := 'Getting Date Format';
2048 l_date_format := icx_sec.getID( icx_sec.PV_DATE_FORMAT );
2049
2050 -- l_date_format := AP_WEB_INFRASTRUCTURE_PKG.getDateFormat;
2051 debug_info := 'Getting FND Message';
2052 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_INVALID_DATE');
2053 debug_info := 'Setting FND Token';
2054 FND_MESSAGE.SET_TOKEN('format', l_date_format);
2055 debug_info := 'invalid date msg';
2056 l_invalid_date_msg :=
2057 AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get, TRUE);
2058 debug_info := 'date not allowed';
2059 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_DATE_NOT_ALLOWED');
2060 debug_info := 'date not allowed msg';
2061 l_date_not_allowed_msg :=
2062 AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get, TRUE);
2063 -- Get year string. RRRR
2064 -- dtong added UPPER to ignore year's case
2065 l_index_y1 := instrb(UPPER(l_date_format), 'RRRR');
2066 if (l_index_y1 = 0) then
2067 l_index_y1 := instrb(UPPER(l_date_format),'YYYY');
2068 if (l_index_y1 <> 0) then
2069 l_index_y2 := l_index_y1 +3;
2070 else l_index_y2 := l_index_y1 + 1;
2071 end if;
2072 else l_index_y2 := l_index_y1 + 3;
2073 end if;
2074
2075 -- Get Day string DD
2076 l_index_d1 := instrb(l_date_format, 'DD');
2077 l_index_d2 := l_index_d1 + 1;
2078
2079 -- Get Month String either MM or MON
2080 l_index_m1 := instrb(l_date_format, 'MON');
2081 if (l_index_m1 <> 0) then
2082 l_mon_format := TRUE;
2083 l_index_m2 := l_index_m1 + 2;
2084 else
2085 l_index_m1 := instrb(l_date_format, 'Mon');
2086 if (l_index_m1 <> 0) then
2087 l_index_m2 := l_index_m1 + 2;
2088 else
2089 l_index_m1 := instrb(l_date_format, 'mon');
2090 if (l_index_m1 <> 0) then
2091 l_index_m2 := l_index_m1 + 2;
2092 else
2093 l_index_m1 := instrb(l_date_format, 'MM');
2094 l_index_m2 := l_index_m1 + 1;
2095 end if;
2096 end if;
2097 end if;
2098
2099 -- get delimeter of the date format string.
2100 -- can be either - or . or /
2101 l_index_del1 := instrb(l_date_format, '-', 1, 1);
2102 IF (l_index_del1 <> 0) THEN /* delimeter - */
2103 l_delimeter := '-';
2104 ELSE
2105 l_index_del1 := instrb(l_date_format, '.', 1,1);
2106 IF (l_index_del1 <> 0) THEN /* delimeter . */
2107 l_delimeter := '.';
2108 ELSE
2109 l_index_del1 := instrb(l_date_format, '/', 1,1);
2110 IF (l_index_del1 <> 0) THEN
2111 l_delimeter := '/';
2112 END IF;
2113 END IF; -- IF (l_index_del1 <> 0)
2114
2115 END IF; -- IF (l_index_del1 <> 0)
2116
2117 IF (l_delimeter is NOT NULL) THEN
2118 l_index_del2 := instrb(l_date_format, l_delimeter, 1,2);
2119
2120 END IF; /* IF (l_delimeter is NOT NULL) */
2121 IF (l_mon_format) THEN
2122 l_jan := substrb(TO_CHAR(TO_DATE('01/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2123 l_feb := substrb(TO_CHAR(TO_DATE('02/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2124 l_mar := substrb(TO_CHAR(TO_DATE('03/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2125 l_apr := substrb(TO_CHAR(TO_DATE('04/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2126 l_may := substrb(TO_CHAR(TO_DATE('05/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2127 l_jun := substrb(TO_CHAR(TO_DATE('06/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2128 l_jul := substrb(TO_CHAR(TO_DATE('07/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2129 l_aug := substrb(TO_CHAR(TO_DATE('08/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2130 l_sep := substrb(TO_CHAR(TO_DATE('09/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2131 l_oct := substrb(TO_CHAR(TO_DATE('10/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2132 l_nov := substrb(TO_CHAR(TO_DATE('11/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2133 l_dec := substrb(TO_CHAR(TO_DATE('12/01/1998', 'MM/DD/RRRR'), l_date_format), l_index_m1, 3);
2134
2135
2136 END IF; /* IF (l_mon_format) */
2137
2138 htp.p('function fStringToDateCheckNull(date_string, error_msg) {
2139 if (date_string == "") {
2140 alert("' || l_invalid_date_msg || '");
2141 return null;
2142 }
2143 return(top.fStringToDate(date_string, error_msg));
2144 }');
2145
2146 -- stringToDate Javascript Function.
2147
2148 htp.p('function fStringToDate(date_string, error_msg) {
2149 if (date_string == "") {
2150 return null;
2151 }
2152 var day = null;
2153 var tmp_day = null;
2154 var month = null;
2155 var tmp_month = null;
2156 var year = null;
2157 var del1 = null;
2158 var del2 = null;
2159 tmp_day = date_string.substring(' || TO_CHAR(l_index_d1-1) || ','
2160 || TO_CHAR(l_index_d2) || ');
2161
2162 tmp_month = date_string.substring(' || TO_CHAR(l_index_m1-1) || ','
2163 || TO_CHAR(l_index_m2) || ');
2164
2165 year = date_string.substring(' || TO_CHAR(l_index_y1-1) || ','
2166 || TO_CHAR(l_index_y2) || ');
2167
2168 del1 = date_string.substring(' || TO_CHAR(l_index_del1-1) || ','
2169 || TO_CHAR(l_index_del1) || ');
2170
2171 del2 = date_string.substring(' || TO_CHAR(l_index_del2-1) || ','
2172 || TO_CHAR(l_index_del2) || ');
2173
2174 var year_1 = date_string.substring(' || TO_CHAR(l_index_y1-1) || ','
2175 || TO_CHAR(l_index_y2+1) || ');
2176
2177 if (((del1 >= "0") && (del1 <= "9")) || ((del1 >= "A") && (del1 <= "z")) ||
2178 ((del2 >= "0") && (del2 <= "9")) || ((del2 >= "A") && (del2 <= "z"))) {
2179 if (error_msg)
2180 alert("' || l_invalid_date_msg || '");
2181 return null;
2182 }
2183
2184 // should not allow user to enter year more than 4 digits when year format is
2185 // set to YYYY. should not allow user to enter more than 2 digits when year
2186 // format is set to YY ');
2187 if ((l_index_y1-1) <> 0) then
2188 htp.p('
2189 if (year != fRtrim(year_1)) {
2190 alert("' || l_invalid_date_msg || '");
2191 return null;
2192 } ');
2193 end if;
2194 htp.p('
2195
2196 if (!((year.length == 2) || (year.length == 4))) {
2197 alert("' || l_invalid_date_msg || '");
2198 return null;
2199 }
2200
2201 ');
2202
2203 IF (l_mon_format) THEN
2204 htp.p('if (tmp_month.toUpperCase() == "' || l_jan || '") {
2205 month = "1";
2206 } else if (tmp_month.toUpperCase() == "' || l_feb || '") {
2207 month = "2";
2208 } else if (tmp_month.toUpperCase() == "' || l_mar || '") {
2209 month = "3";
2210 } else if (tmp_month.toUpperCase() == "' || l_apr || '") {
2211 month = "4";
2212 } else if (tmp_month.toUpperCase() == "' || l_may || '") {
2213 month = "5";
2214 } else if (tmp_month.toUpperCase() == "' || l_jun || '") {
2215 month = "6";
2216 } else if (tmp_month.toUpperCase() == "' || l_jul || '") {
2217 month = "7";
2218 } else if (tmp_month.toUpperCase() == "' || l_aug || '") {
2219 month = "8";
2220 } else if (tmp_month.toUpperCase() == "' || l_sep || '") {
2221 month = "9";
2222 } else if (tmp_month.toUpperCase() == "' || l_oct || '") {
2223 month = "10";
2224 } else if (tmp_month.toUpperCase() == "' || l_nov || '") {
2225 month = "11";
2226 } else if (tmp_month.toUpperCase() == "' || l_dec || '") {
2227 month = "12";
2228 } else {
2229 if (error_msg)
2230 alert("' || l_date_not_allowed_msg || '");
2231 return null;
2232 }');
2233
2234 ELSE
2235 htp.p('
2236 if ((tmp_month.length == 2) && (tmp_month.charAt(0) == "0")) {
2237 month = tmp_month.substring(1,2);
2238 } else {
2239 month = tmp_month;
2240 }');
2241
2242 END IF;
2243
2244 htp.p('
2245
2246 if ((tmp_day.length == 2) && (tmp_day.charAt(0) == "0")) {
2247 day = tmp_day.substring(1,2);
2248
2249 } else {
2250 day = tmp_day;
2251 }
2252
2253 if ((!top.fIsInt(day)) ||
2254 (!top.fIsInt(month)) ||
2255 (!top.fIsInt(year))) {
2256 if (error_msg)
2257 alert("' || l_invalid_date_msg || '");
2258 return null;
2259 }
2260
2261
2262 if ((month < 1) || (month > 12)) {
2263 if (error_msg)
2264 alert("' || l_date_not_allowed_msg || '");
2265 return null;
2266 }
2267 if (day < 1) {
2268 if (error_msg)
2269 alert("' || l_date_not_allowed_msg || '");
2270 return null;
2271 }
2272 if ((month == 1) || (month == 3) || (month == 5) ||
2273 (month == 7) || (month == 8) || (month == 10) ||
2274 (month == 12)) {
2275 if (day > 31) {
2276 if (error_msg)
2277 alert("' || l_date_not_allowed_msg || '");
2278 return null;
2279 }
2280 } else if (month == 2) {
2281 if ((year % 4) == 0) {
2282 if (day > 29) {
2283 if (error_msg)
2284 alert("' || l_date_not_allowed_msg || '");
2285 return null;
2286 }
2287 } else {
2288 if (day > 28) {
2289 if (error_msg)
2290 alert("' || l_date_not_allowed_msg || '");
2291 return null;
2292 }
2293 }
2294
2295 } else {
2296
2297 if (day > 30) {
2298 if (error_msg)
2299 alert("' || l_date_not_allowed_msg || '");
2300 return null;
2301 }
2302 }
2303
2304 var objDate = new Date(year, month-1, day, 00, 00, 00);
2305 return objDate;
2306
2307 }
2308
2309 ');
2310
2311 -- stringToDate_DDMMRRRR Javascript Function.
2312 htp.p('function fStringToDate_DDMMRRRR(date_string) {
2313
2314 if (date_string == "") {
2315 return null;
2316 }
2317 var day = null;
2318 var tmp_day = null;
2319 var month = null;
2320 var tmp_month = null;
2321 var year = null;
2322 var del1 = null;
2323 var del2 = null;
2324 tmp_day = date_string.substring(0,2);
2325 month = date_string.substring(3,5);
2326 year = date_string.substring(6,10);
2327 del1 = date_string.substring(2,3);
2328 del2 = date_string.substring(5,6);
2329 if (((del1 >= "0") && (del1 <= "9")) || ((del1 >= "A") && (del1 <= "z")) ||
2330 ((del2 >= "0") && (del2 <= "9")) || ((del2 >= "A") && (del2 <= "z"))) {
2331 return null;
2332 }
2333
2334 ');
2335
2336
2337 htp.p('
2338 if ((month.length == 2) && (month.charAt(0) == "0")) {
2339 month = month.substring(1,2);
2340
2341 }
2342
2343 if ((tmp_day.length == 2) && (tmp_day.charAt(0) == "0")) {
2344 day = tmp_day.substring(1,2);
2345
2346 } else {
2347 day = tmp_day;
2348 }
2349 if ((!top.fIsInt(day)) ||
2350 (!top.fIsInt(month)) ||
2351 (!top.fIsInt(year))) {
2352 return null;
2353 }
2354
2355 if ((month < 1) || (month > 12))
2356 return null;
2357
2358 if (day < 1)
2359 return null;
2360 if ((month == 1) || (month == 3) || (month == 5) ||
2361 (month == 7) || (month == 8) || (month == 10) ||
2362 (month == 12)) {
2363 if (day > 31)
2364 return null;
2365 } else if (month == 2) {
2366 if ((year % 4) == 0) {
2367 if (day > 29)
2368 return null;
2369
2370 } else {
2371 if (day > 28)
2372 return null;
2373 }
2374
2375 } else {
2376
2377 if (day > 30)
2378 return null;
2379 }
2380
2381 var objDate = new Date(year, month-1, day, 00, 00, 00);
2382 return objDate;
2383
2384 }
2385 ');
2386
2387
2388 -- dateToString Javascript function.
2389 htp.p('function fDateToString(dateobj) {
2390 if (!dateobj) return;
2391 var date = dateobj.getDate();
2392 var month;
2393 var tmp_month = dateobj.getMonth();
2394 tmp_month++;
2395 var year = dateobj.getFullYear();
2396 ');
2397 IF (l_mon_format) THEN
2398 htp.p('
2399 if (tmp_month == 1) {
2400 month = "' || l_jan || '";
2401 } else if (tmp_month == 2) {
2402 month = "' || l_feb || '";
2403 } else if (tmp_month == 3) {
2404 month = "' || l_mar || '";
2405 } else if (tmp_month == 4) {
2406 month = "' || l_apr || '";
2407 } else if (tmp_month == 5) {
2408 month = "' || l_may || '";
2409 } else if (tmp_month == 6) {
2410 month = "' || l_jun || '";
2411 } else if (tmp_month == 7) {
2412 month = "' || l_jul || '";
2413 } else if (tmp_month == 8) {
2414 month = "' || l_aug || '";
2415 } else if (tmp_month == 9) {
2416 month = "' || l_sep || '";
2417 } else if (tmp_month == 10) {
2418 month = "' || l_oct || '";
2419 } else if (tmp_month == 11) {
2420 month = "' || l_nov || '";
2421 } else if (tmp_month == 12) {
2422 month = "' || l_dec || '";
2423 }');
2424
2425 ELSE -- Not Mon format.
2426
2427 -- fix month string.
2428 htp.p('if (tmp_month <= 9) {
2429 month = "0" + tmp_month;
2430 } else {
2431 month = tmp_month;
2432 }
2433 ');
2434 END IF;
2435
2436 -- fix date string
2437 htp.p('if (date <= 9) {
2438 date = "0" + date;
2439 }
2440 ');
2441 htp.p('var result;');
2442
2443 -- d m y
2444 IF ((l_index_d1 < l_index_m1) AND (l_index_m1 < l_index_y1)) THEN
2445 htp.p('result = date + "' || l_delimeter || '" + month + "' || l_delimeter || '" + year;');
2446
2447 -- m d y
2448 ELSIF ((l_index_m1 < l_index_d1) AND (l_index_d1 < l_index_y1)) THEN
2449 htp.p('result = month + "' || l_delimeter || '" + date + "' || l_delimeter || '" + year;');
2450 -- y m d
2451 ELSIF ((l_index_y1 < l_index_m1) AND (l_index_m1 < l_index_d1)) THEN
2452 htp.p('result = year + "' || l_delimeter || '" + month + "' || l_delimeter || '" + date;');
2453 -- y d m
2454 ELSIF ((l_index_y1 < l_index_d1) AND (l_index_d1 < l_index_m1)) THEN
2455 htp.p('result = year + "' || l_delimeter || '" + date + "' || l_delimeter || '" + month;');
2456 -- m y d Not likely, but...
2457 ELSIF ((l_index_m1 < l_index_y1) AND (l_index_y1 < l_index_d1)) THEN
2458 htp.p('result = month + "' || l_delimeter || '" + year + "' || l_delimeter || '" + date;');
2459 -- d y m Not likely, but....
2460 ELSIF ((l_index_d1 < l_index_y1) AND (l_index_y1 < l_index_m1)) THEN
2461 htp.p('result = date + "' || l_delimeter || '" + year + "' || l_delimeter || '" + month;');
2462 END IF;
2463 htp.p('
2464 return result;
2465 }
2466 ');
2467
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470 BEGIN
2471 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2472 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2473 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'convertDate');
2474 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2475 DisplayException(fnd_message.get);
2476 END;
2477
2478 END convertDate;
2479
2480
2481
2482 PROCEDURE DetermineConversion IS
2483
2484 l_euro_code AP_WEB_DB_COUNTRY_PKG.curr_currCode;
2485
2486 BEGIN
2487 l_euro_code := getEuroCode;
2488
2489 htp.p('function determineConversion(recCurr, reimbCurr) {
2490
2491 if (recCurr == reimbCurr) {
2492 return 0;
2493 }
2494
2495 var compare_date;
2496 if ((top.objDate1 != null) && (top.objDate2 != null)) {
2497 compare_date = top.objDate2;
2498 } else if ((top.objDate1 != null) && (top.objDate2 == null)){
2499 compare_date = top.objDate1;
2500 } else if ((top.objDate1 == null) && (top.objDate2 == null)) {
2501 return 0;
2502 } else {
2503 return -1;
2504 }
2505 if (recCurr == "' || l_euro_code || '") {
2506 if (getFixedRate(reimbCurr) > 0) {
2507 if (compareDate(compare_date, getEffectiveDate(reimbCurr)) >= 0)
2508 return 2;
2509 else
2510 return 0;
2511 } else {
2512 return 0;
2513 }
2514
2515 } else if (reimbCurr == "' || l_euro_code || '") {
2516 if (getFixedRate(recCurr) > 0) {
2517 if (compareDate(compare_date, getEffectiveDate(recCurr)) >= 0)
2518 return 1;
2519 else
2520 return 0;
2521 } else {
2522 return 0;
2523 }
2524 } else {
2525 if ((getFixedRate(reimbCurr) > 0) ' || '&' || '&' || '
2526 (getFixedRate(recCurr) > 0)) {
2527 if ((compareDate(compare_date, getEffectiveDate(reimbCurr)) >= 0)
2528 ' || '&' || '&' || '
2529 (compareDate(compare_date, getEffectiveDate(recCurr)) >= 0)) {
2530 if (top.g_bEuroCodeDefined == false) {
2531 alert("top.g_objMessages.mstrGetMesg(\"AP_WEB_EURO_SETUP_INVALID\")");
2532 top.euro_code_invalid = true;
2533 form.currency.focus();
2534 return;
2535 }
2536 else
2537 return 3;
2538 } else {
2539 return 0;
2540 }
2541 } else {
2542 return 0;
2543 }
2544 }
2545
2546
2547 }');
2548 EXCEPTION
2549 WHEN OTHERS THEN
2550 BEGIN
2551 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2552 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2553 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DetermineConversion');
2554 DisplayException(fnd_message.get);
2555 END;
2556
2557 END DetermineConversion;
2558
2559
2560 -----------------------------
2561 FUNCTION getMinipackVersion RETURN VARCHAR2 IS
2562 -----------------------------
2563 BEGIN
2564 return 'R12.OIE.A';
2565 END;
2566
2567 -----------------------------
2568 FUNCTION getEuroCode RETURN VARCHAR2 IS
2569 -----------------------------
2570 l_euro_code AP_WEB_DB_COUNTRY_PKG.curr_currCode;
2571 BEGIN
2572 l_euro_code := GL_CURRENCY_API.get_euro_code();
2573 htp.p('top.g_bEuroCodeDefined = true');
2574 return l_euro_code;
2575
2576 EXCEPTION
2577 WHEN OTHERS THEN
2578 htp.p('top.g_bEuroCodeDefined = false');
2579 return NULL;
2580 END;
2581
2582
2583 /*------------------------------------------------------------+
2584 Fix 1435885 : To prevent pcard packages from getting
2585 Invalid. These functions are not used by SSE.
2586 Functions include IsNum, DisplayHelp, GenericButton,
2587 GenToolBarScript, GenToolBar, GenButton, StyleSheet
2588 +-------------------------------------------------------------*/
2589 ------------------
2590 PROCEDURE IsNum IS
2591 ------------------
2592 l_message VARCHAR2(2000);
2593 BEGIN
2594
2595 fnd_message.set_name('SQLAP', 'AP_WEB_NUMBER_REQUIRED');
2596 l_message := AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get, TRUE);
2597
2598 htp.p('function isNum(str, showalert){
2599 var ch=str.substring(0,1);
2600
2601 if((ch<"0" || "9"<ch) ' || '&' || '&' || ' (ch != ".") && (ch != "-")) {
2602 if (showalert)
2603 alert("' || l_message || '");
2604 return false;
2605 }
2606 for(var i=1; i<str.length; i++){
2607 var ch=str.substring(i,i+1)
2608 if((ch<"0" || "9"<ch) ' || '&' || '&' || ' ch != "."){
2609 if (showalert)
2610 alert("' || l_message || '");
2611 /* alert("You must enter a number");*/
2612 return false;
2613 }
2614 }
2615 // xx.xx.xx and xx. case
2616 if ((str.indexOf(".") != str.lastIndexOf(".")) || (str.lastIndexOf(".") == (str.length - 1))) {
2617 if (showalert)
2618 alert("' || l_message || '");
2619 return false;
2620 }
2621 // -. case
2622 if (str == "-.") {
2623 if (showalert)
2624 alert("' || l_message || '");
2625 return false;
2626 }
2627 // 00xx case
2628 if ((str.length > 1) && (str.substring(0,1) == "0") && (str.indexOf(".") == -1)) {
2629 if (showalert)
2630 alert("' || l_message || '");
2631 return false;
2632 }
2633
2634 return true;
2635 }
2636 ');
2637 END IsNum;
2638
2639 ----------------------
2640 PROCEDURE DisplayHelp IS
2641 ------------------------
2642 v_lang varchar2(5);
2643
2644 BEGIN
2645 v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
2646
2647 htp.p('function displayHelp(i) {
2648 var helpurl = "";
2649 var baseurl = self.location.protocol + "//" + self.location.host + "/OA_HTML/' || v_lang || '";
2650 if (i == 1)
2651 helpurl = baseurl + "/APWHLEX1.htm";
2652 else if (i == 2)
2653 helpurl = baseurl + "/APWHLEX2.htm";
2654 else if (i == 2.1)
2655 helpurl = baseurl + "/APWHLEX2.htm#Restoring Reports";
2656 else if (i == 2.2)
2657 helpurl = baseurl + "/APWHLEX6.htm#cctrans";
2658 else if (i == 3)
2659 helpurl = baseurl + "/APWHLEX3.htm";
2660 else if (i == 4)
2661 helpurl = baseurl + "/APWHLEX4.htm";
2662 else if (i == 4.1)
2663 helpurl = baseurl + "/APWHLEX4.htm#Reviewing Upload";
2664 else if (i == 5)
2665 helpurl = baseurl + "/APWHLEX5.htm";
2666 else if (i == 6)
2667 helpurl = baseurl + "/APWHLCC1.htm";
2668
2669 // open(helpurl, "Expense_Report_Help");
2670 open(helpurl, "Expense_Report_Help", "scrollbars=yes,resizable=yes,menubar=no,location=no,width=800,height=600");
2671 }');
2672
2673 END DisplayHelp;
2674
2675
2676 PROCEDURE DynamicButtonJS(P_ButtonText varchar2,
2677 P_ImageFileName varchar2,
2678 P_OnMouseOverText varchar2,
2679 P_HyperTextCall varchar2,
2680 P_LanguageCode varchar2,
2681 P_JavaScriptFlag boolean) IS
2682 BEGIN
2683 htp.p('top.DynamicButton("'||AP_WEB_DB_UTIL_PKG.jsPrepString(P_ButtonText)||'",
2684 "'||P_ImageFileName||'",
2685 "'||AP_WEB_DB_UTIL_PKG.jsPrepString(P_OnMouseOverText, FALSE, TRUE)||'",
2686 "'||P_HyperTextCall||'",
2687 "'||P_LanguageCode||'",
2688 false,
2689 false,self);');
2690
2691 END;
2692
2693
2694
2695 ---------------------------------------------------------------------
2696 -- PROCEDURE GenToolbarScript
2697 --
2698 -- Generates javascript for the Toolbar functionalities.
2699 ----------------------------------------------------------------------
2700 PROCEDURE GenToolbarScript IS
2701 BEGIN
2702 js.scriptOpen;
2703 htp.p('
2704 img_dir = "' || C_IMG_DIR || '";
2705 bName = navigator.appName;
2706 bVer = parseInt(navigator.appVersion);
2707 if (bVer >= 3)
2708 {
2709 save1 = new Image;
2710 save1.src = img_dir +"FNDIWSA1.gif";
2711 save2 = new Image;
2712 save2.src = img_dir +"FNDIWSAV.gif";
2713 save3 = new Image;
2714 save3.src = img_dir +"FNDIWSAD.gif";
2715 print1 = new Image;
2716 print1.src = img_dir +"FNDIWPR1.gif";
2717 refresh1 = new Image;
2718 refresh1.src = img_dir +"FNDIWRL1.gif";
2719 help1 = new Image;
2720 help1.src = img_dir +"FNDIWHL1.gif";
2721 home1 = new Image;
2722 home1.src = img_dir +"FNDIWHO1.gif";
2723
2724 }');
2725
2726 htp.p('
2727 var Restorable = false
2728 var Nav4 = ((navigator.appName == "Netscape") && (parseInt(navigator.appVersion) == 4))
2729 var Win32
2730 if (Nav4) {
2731 Win32 = ((navigator.userAgent.indexOf("Win") != -1) && (navigator.userAgent.indexOf("Win16") == -1))
2732 } else {
2733 Win32 = ((navigator.userAgent.indexOf("Windows") != -1) && (navigator.userAgent.indexOf("Windows 3.1") == -1))
2734 }
2735 function fprintFrame(wind) {
2736 // no single frame printing available for Mac
2737 if (Win32) {
2738 if (Nav4) {
2739 window.print()
2740 } else {
2741 // traps all script error messages hereafter until pagereload
2742 window.onerror = fprintErr
2743 // make sure desired frame has focus
2744 wind.focus()
2745 // change second parameter to 2 if you do not want the
2746 // print dialog to appear
2747 IEControl.ExecWB(6, 1)
2748 }
2749 } else {
2750 alert("Sorry. Printing is available only from Windows 95/98/NT.")
2751 }
2752 }
2753
2754 function fprintErr() {
2755 return true
2756 }
2757
2758 ');
2759
2760 js.scriptClose;
2761 END GenToolbarScript;
2762
2763 ----------------------------------------------------------------------------
2764 -- PROCEDURE GenToolbar
2765 --
2766 -- Generates the Toolbar
2767 -- Written by: Shuh
2768 -- Args:
2769 -- p_title Title of the Toolbar
2770 -- p_print_frame Frame to print for the Print button
2771 -- p_save_flag true if save button should be on the toolbar
2772 -- p_save_disabled_flag true if save button is disabled
2773 -- p_save_call Function called when save button is pressed, i.e.
2774 -- 'javascript:fSaveFunction()'
2775 ----------------------------------------------------------------------
2776 PROCEDURE GenToolbar(p_title VARCHAR2,
2777 p_print_frame VARCHAR2,
2778 p_save_flag BOOLEAN,
2779 p_save_disabled_flag BOOLEAN,
2780 p_save_call VARCHAR2)
2781 IS
2782 BEGIN
2783 htp.p('<TABLE width=100% Cellpadding=0 Cellspacing=0 border=0>
2784 <TR>
2785 <TD width=10></TD> <TD>
2786 <TABLE Cellpadding=0 Cellspacing=0 Border=0>');
2787
2788 htp.p('<TD rowspan=3><img src="' || C_IMG_DIR || 'FNDGTBL.gif"></TD>
2789 <TD class=white height=1 colspan=3><img src="' || C_IMG_DIR || 'FNDPX6.gif"></TD>
2790 <TD rowspan=3><img src="' || C_IMG_DIR || 'FNDGTBR.gif"></TD>
2791 </TR>
2792 <TR>
2793 <TD class=ltgrey nowrap height=30 align=middle>
2794 <A href="javascript:parent.window.close(); parent.window.opener.focus(); target=_top;"
2795 onmouseover="document.tbhome.src=home1.src; window.status=' || '&' || 'quot; Go to Main Menu' || '&' || 'quot; ; return true;"
2796 onmouseout="document.tbhome.src=' || '&' || 'quot;' || C_IMG_DIR || 'FNDIWHOM.gif' || '&' || 'quot;">
2797 <IMG name=tbhome src="' || C_IMG_DIR || 'FNDIWHOM.gif" align=middle border=0></A>
2798 <img src="' || C_IMG_DIR || 'FNDIWDVD.gif" align=absmiddle>
2799 </TD>
2800 <TD class=ltgrey nowrap height=30 align=middle>');
2801
2802 htp.p('<FONT class=dropdownmenu>' || p_title || '</FONT>
2803 </TD>
2804 <TD class=toolbar nowrap height=30 align=middle><img src="' || C_IMG_DIR || 'FNDIWDVD.gif" align=absmiddle>');
2805
2806 if (p_save_flag = TRUE) THEN --print the save button
2807 htp.p(' <A href="' || p_save_call || ';" onmouseover="document.tbsave.src=save1.src" onmouseout=' || '&' || 'quot;"document.tbsave.src=' || C_IMG_DIR || 'US/FNDIWSAV.gif' || '&' || 'quot;">
2808 <IMG name=tbsave src="' || C_IMG_DIR || 'FNDIWSAV.gif" align=absmiddle border=0></A>');
2809 elsif (p_save_disabled_flag = TRUE) THEN --save button is disabled
2810 htp.p('<IMG src="' || C_IMG_DIR || 'FNDWPMNU.gif" align=absmiddle>');
2811 end if;
2812
2813 htp.p('<A href="javascript:void fprintFrame(' || p_print_frame || ')" onmouseover="document.tbprint.src=print1.src; window.status=' || '&' || 'quot;Print' || '&' || 'quot; ;return true;"
2814 onmouseout="document.tbprint.src=' || '&' || 'quot;' || C_IMG_DIR || 'FNDIWPRT.gif' || '&' || 'quot;">
2815 <IMG name=tbprint src="' || C_IMG_DIR || 'FNDIWPRT.gif" align=absmiddle border=0></A>
2816 <img src="' || C_IMG_DIR || 'FNDIWDVD.gif" align=absmiddle>
2817 <A href="javascript:location.reload()" onmouseover="document.tbrefresh.src=refresh1.src; window.status=' || '&' || 'quot;Reload' || '&' || 'quot; ;return true;"
2818 onmouseout="document.tbrefresh.src=' || '&' || 'quot;' || C_IMG_DIR || 'FNDIWRLD.gif' || '&' || 'quot;">
2819 <IMG name=tbrefresh src="' || C_IMG_DIR || 'FNDIWRLD.gif" align=absmiddle border=0></A>
2820 <img src="' || C_IMG_DIR || 'FNDIWDVD.gif" align=absmiddle>
2821 <A href="javascript:top.help_window()" onmouseover="document.tbhelp.src=help1.src;window.status=' || '&' || 'quot;Help' || '&' || 'quot; ;return true;"
2822 onmouseout="document.tbhelp.src=' || '&' || 'quot;' || C_IMG_DIR || 'FNDIWHLP.gif' || '&' || 'quot;">
2823 <IMG name=tbhelp src="' || C_IMG_DIR || 'FNDIWHLP.gif" align=absmiddleborder=0></A>
2824 </TR>
2825 <TR>
2826 <TD class=canvas height=1 colspan=3><img src="' || C_IMG_DIR || 'FNDPX3.gif"></TD>
2827 </TR>
2828 </TABLE>
2829 </TD>
2830 <TD rowspan=5 width=100% align=right valign=top><img src="' || C_IMG_DIR || 'FNDLWAPP.gif"></TD>
2831 </TR>
2832 <TR><TD height=10></TD></TR>
2833 </TABLE>');
2834
2835 END GenToolbar;
2836
2837
2838 ---------------------------------------------------------------------
2839 -- DESCRIPTION:
2840 -- Having problems displaying buttons with the GenericButton and
2841 -- DynamicButton procedures. This is a simpler version of
2842 -- DynamicButton.
2843 -- bug 6045969 : stub the procedure as modplsql is obsolete in R12.
2844 ---------------------------------------------------------------------
2845 PROCEDURE GenButton(P_Button_Text varchar2,
2846 P_OnMouseOverText varchar2,
2847 P_HyperTextCall varchar2) IS
2848 BEGIN
2849 null;
2850 End GenButton;
2851
2852 ------------------------------------------------------------------------
2853 -- PROCEDURE StyleSheet
2854 --
2855 -- Defines the class styles and colors in the cascading style
2856 -- sheet format.
2857 -- Written By: Shuh
2858 -- This will be replaced by Dave's definitions in another file
2859 ------------------------------------------------------------------------
2860 PROCEDURE StyleSheet IS
2861 BEGIN
2862 --Cascading style sheet for the colors
2863 htp.p('<STYLE type="text/css">
2864 <!--
2865 .TOOLBAR {BACKGROUND-COLOR: #cccccc}
2866 .BUTTON {font-family: Arial, sans-serif; text-decoration:none; color:black; font-size:10pt}
2867 .DROPDOWNMENU {font-family: Arial, sans-serif; color: #003366;FONT-WEIGHT: BOLD;font-size: 14pt;}
2868 .CANVAS {background-color: #336699;}
2869 .white {background-color: #ffffff}
2870 .babyblue {background-color: #99ccff}
2871 .ltblue {background-color: #6699cc}
2872 .blue {background-color: #336699}
2873 .dkblue {background-color: #003366}
2874 .black {background-color: #000000}
2875 .ltgrey {background-color: #cccccc}
2876 .grey {background-color: #999999}
2877 .dkgrey {background-color: #666666}
2878 .ltblack {background-color: #333333}
2879 -->
2880 </STYLE>');
2881
2882 END StyleSheet;
2883
2884 ------------------------------------------------------------------------
2885 -- FUNCTION RtrimMultiByteSpaces
2886 -- Bug Fix : 2051803 - To remove all single and multibyte space
2887 -- at the end of input string.
2888 ------------------------------------------------------------------------
2889 FUNCTION RtrimMultiByteSpaces(p_input_string IN varchar2) RETURN VARCHAR2 IS
2890 l_temp AP_EXPENSE_REPORT_LINES.justification%type;
2891 BEGIN
2892 l_temp := rtrim(rtrim(rtrim(rtrim(p_input_string),to_multi_byte(' '))),to_multi_byte(' '));
2893 IF ((l_temp <> '') AND
2894 ((substr(l_temp,length(l_temp)) = ' ') OR
2895 (substr(l_temp,length(l_temp)) = to_multi_byte(' ')))) THEN
2896 l_temp := RtrimMultiByteSpaces(l_temp);
2897 END IF;
2898 return(l_temp);
2899 EXCEPTION
2900 WHEN OTHERS THEN
2901 BEGIN
2902 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2903 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2904 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'RtrimMultiByteSpaces');
2905 DisplayException(fnd_message.get);
2906 END;
2907 END;
2908
2909 ------------------------------------------------------------------------
2910 -- FUNCTION get_distance_display_value
2911 -- Returns distance assuming that the original value is given in
2912 -- kilometers
2913 -- 5/30/2002 - Kristian Widjaja
2914 ------------------------------------------------------------------------
2915 FUNCTION GetDistanceDisplayValue(p_value IN NUMBER,
2916 p_format IN VARCHAR2) RETURN NUMBER IS
2917
2918 v_return NUMBER;
2919 BEGIN
2920 if p_format = 'KM' then
2921 v_return := p_value;
2922 elsif p_format = 'MILES' then
2923 v_return := p_value * 100000/(2.54*12*5280);
2924 elsif p_format = 'SWMILES' then
2925 v_return := p_value / 10.0;
2926 end if;
2927
2928 RETURN v_return;
2929 END;
2930
2931 FUNCTION VALUE_SPECIFIC(p_name IN VARCHAR2,
2932 p_user_id IN NUMBER default null,
2933 p_resp_id IN NUMBER default null,
2934 p_apps_id IN NUMBER default null)
2935 RETURN VARCHAR2 IS
2936 l_web_user_id Number := FND_PROFILE.VALUE('USER_ID');
2937
2938 BEGIN
2939 IF (l_web_user_id = p_user_id) THEN
2940 return FND_PROFILE.VALUE(NAME => p_name);
2941 ELSE
2942 return FND_PROFILE.VALUE_SPECIFIC(NAME => p_name,
2943 USER_ID => p_user_id,
2944 RESPONSIBILITY_ID => p_resp_id,
2945 APPLICATION_ID => p_apps_id);
2946 END IF;
2947 EXCEPTION
2948 WHEN OTHERS THEN
2949 AP_WEB_DB_UTIL_PKG.RaiseException('VALUE_SPECIFIC');
2950 APP_EXCEPTION.RAISE_EXCEPTION;
2951 return null;
2952 END VALUE_SPECIFIC;
2953
2954
2955 /*
2956 Written by:
2957 Maulik Vadera
2958 Purpose:
2959 To get the override approver name, when profile option,
2960 IE:Approver Required = "Yes with Default" and approver name is not provided
2961 in the upload SpreadSheet data.
2962 Fix for bug 3786831
2963 Input:
2964 p_EmpId: Employee Id of the employee
2965 Output:
2966 Override approver Id for that Employee Id
2967 Override approver name for that Employee Id
2968 Date:
2969 21-Mar-2005
2970 */
2971
2972
2973 PROCEDURE GetOverrideApproverDetail(p_EmpId IN NUMBER,
2974 p_appreq IN VARCHAR2,
2975 p_ApproverId OUT NOCOPY HR_EMPLOYEES.employee_num%TYPE,
2976 p_OverrideApproverName OUT NOCOPY HR_EMPLOYEES.full_name%TYPE) IS
2977
2978
2979 l_DefaultCostCenter VARCHAR2(80);
2980 l_EmployeeNum HR_EMPLOYEES.employee_num%TYPE := 100;
2981 l_DefaultSource VARCHAR2(30) := NULL;
2982 l_TempBoolean BOOLEAN;
2983 l_ManagerOrgId NUMBER;
2984
2985 BEGIN
2986
2987 SELECT DEFAULT_APPROVER_ID, DEFAULT_SOURCE into p_ApproverId, l_DefaultSource
2988 FROM AP_WEB_PREFERENCES
2989 WHERE EMPLOYEE_ID = p_EmpId;
2990
2991 IF l_DefaultSource = 'PRIORREPORT' THEN
2992
2993 --If preferences's default source is Prior Report
2994
2995 SELECT OVERRIDE_APPROVER_ID into p_ApproverId
2996 FROM
2997 ( SELECT OVERRIDE_APPROVER_ID
2998 FROM AP_EXPENSE_REPORT_HEADERS_ALL
2999 WHERE EMPLOYEE_ID = p_EmpId AND BOTHPAY_PARENT_ID IS NULL
3000 ORDER BY report_header_id DESC
3001 ) WHERE ROWNUM=1;
3002
3003 IF AP_WEB_DB_HR_INT_PKG.IsPersonActive(p_ApproverId) = 'Y'
3004 AND AP_WEB_DB_HR_INT_PKG.HasValidFndUserAndWfAccount(p_ApproverId)= 'Y' THEN
3005
3006 --if approver is an active user of the system, return the approver's name
3007
3008 GetEmployeeInfo(
3009 p_OverrideApproverName,
3010 l_EmployeeNum,
3011 l_DefaultCostCenter,
3012 p_ApproverId);
3013
3014 ELSE
3015
3016 --if approver is not an active user of the system, return null
3017
3018 p_ApproverId := NULL;
3019 p_OverrideApproverName := NULL;
3020
3021 END IF;
3022
3023 ELSIF l_DefaultSource = 'PREFERENCES' AND p_ApproverId IS NOT NULL THEN
3024
3025 --If preferences's default source is Preferences and Approver's name is provided by user
3026
3027 IF AP_WEB_DB_HR_INT_PKG.IsPersonActive(p_ApproverId) = 'Y'
3028 AND AP_WEB_DB_HR_INT_PKG.HasValidFndUserAndWfAccount(p_ApproverId)= 'Y' THEN
3029
3030 --if approver is an active user of the system
3031
3032 GetEmployeeInfo(
3033 p_OverrideApproverName,
3034 l_EmployeeNum,
3035 l_DefaultCostCenter,
3036 p_ApproverId);
3037 ELSE
3038 IF p_appreq='D' THEN
3039
3040 --if approver is not an active user of the system, default approver from the HRMS
3041
3042 l_TempBoolean := AP_WEB_DB_HR_INT_PKG.GetSupervisorInfo(p_EmpId,
3043 p_ApproverId,
3044 p_OverrideApproverName,
3045 l_ManagerOrgId);
3046 ELSE
3047 p_ApproverId := NULL;
3048 p_OverrideApproverName := NULL;
3049
3050 END IF;
3051
3052 END IF;
3053
3054
3055 ELSE
3056
3057 --In case employee hasn't provided approver's name in preferences
3058
3059 IF p_appreq='D' THEN
3060
3061 l_TempBoolean := AP_WEB_DB_HR_INT_PKG.GetSupervisorInfo(p_EmpId,
3062 p_ApproverId,
3063 p_OverrideApproverName,
3064 l_ManagerOrgId);
3065 ELSE
3066
3067 p_ApproverId := NULL;
3068 p_OverrideApproverName := NULL;
3069
3070 END IF;
3071
3072
3073 END IF;
3074
3075 RETURN;
3076
3077 EXCEPTION
3078 WHEN no_data_found THEN
3079
3080 --If there is no data in the AP_WEB_PREFERENCES
3081 --then default approver from the HRMS
3082 IF l_DefaultSource IS NULL THEN
3083 IF p_appreq='D' THEN
3084 l_TempBoolean := AP_WEB_DB_HR_INT_PKG.GetSupervisorInfo(p_EmpId,
3085 p_ApproverId,
3086 p_OverrideApproverName,
3087 l_ManagerOrgId);
3088 ELSE
3089 p_ApproverId := NULL;
3090 p_OverrideApproverName := NULL;
3091 END IF;
3092 ELSE
3093
3094 p_ApproverId := NULL;
3095 p_OverrideApproverName := NULL;
3096
3097 END IF;
3098
3099 WHEN OTHERS THEN
3100 raise;
3101
3102 END GetOverrideApproverDetail;
3103
3104 /*=======================================================================
3105 | PUBLIC FUNCITON: OrgSecurity
3106 |
3107 | DESCRIPTION: This function will return the security predicate
3108 | for expense report templates and expense types table.
3109 | It ensures that the seeded template and expense types
3110 | with org_id = -99 are also picked up when querying
3111 | the secured synonym
3112 |
3113 | PARAMETERS
3114 | obj_schema IN VARCHAR2 Object Schema
3115 | obj_name IN VARCHAR2 Object Name
3116 |
3117 | RETURNS
3118 | Where clause to be appended to the object.
3119 *=======================================================================*/
3120 FUNCTION OrgSecurity ( obj_schema VARCHAR2,
3121 obj_name VARCHAR2) RETURN VARCHAR2
3122 IS
3123 l_access_mode VARCHAR2(10);
3124 BEGIN
3125 -- Get the current access mode
3126 l_access_mode := MO_GLOBAL.get_access_mode();
3127
3128 --
3129 -- Returns different predicates based on the access_mode
3130 -- The codes for access_mode are
3131 -- M - Multiple OU Access
3132 -- A - All OU Access
3133 -- S - Single OU Access
3134 -- Null - Backward Compatibility - CLIENT_INFO case --
3135 IF l_access_mode IS NOT NULL THEN
3136 IF l_access_mode = 'M' THEN
3137 RETURN 'EXISTS (SELECT 1
3138 FROM mo_glob_org_access_tmp oa
3139 WHERE oa.organization_id = org_id
3140 )
3141 OR org_id = -99';
3142 ELSIF l_access_mode = 'S' THEN
3143 RETURN 'org_id IN ( sys_context(''multi_org2'',''current_org_id''), -99)';
3144 ELSIF l_access_mode = 'A' THEN -- for future use
3145 RETURN NULL;
3146 END IF;
3147 ELSE
3148 RETURN 'org_id IN ( substrb(userenv(''CLIENT_INFO''),1,10), -99 )';
3149 END IF;
3150
3151 END OrgSecurity;
3152
3153 PROCEDURE ExpenseSetOrgContext(p_report_header_id IN NUMBER) IS
3154 l_org_id NUMBER;
3155 BEGIN
3156
3157 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(
3158 p_report_header_id,
3159 l_org_id) <> TRUE ) THEN
3160 l_org_id := NULL;
3161 END IF;
3162
3163 IF (l_org_id IS NOT NULL) THEN
3164 fnd_client_info.set_org_context( l_org_id );
3165 END IF;
3166 END ExpenseSetOrgContext;
3167
3168 -- Bug: 6220330, added a new parameter so that the trigger on ap_invoices_all can use this.
3169 PROCEDURE UpdateExpenseStatusCode(
3170 p_invoice_id AP_INVOICES_ALL.invoice_id%TYPE,
3171 p_pay_status_flag AP_INVOICES_ALL.payment_status_flag%TYPE DEFAULT NULL
3172 ) IS
3173
3174 t_paid_status CONSTANT VARCHAR2(10) := 'PAID';
3175 t_partially_paid_status CONSTANT VARCHAR2(10) := 'PARPAID';
3176 t_invoiced_status CONSTANT VARCHAR2(10) := 'INVOICED';
3177
3178 l_expenses_to_update ExpensesToUpdate;
3179
3180 l_parent_report_header_id AP_EXPENSE_REPORT_HEADERS_ALL.report_header_id%TYPE;
3181 l_parent_invoice_status varchar2(100);
3182 l_main_report_header_id AP_EXPENSE_REPORT_HEADERS_ALL.report_header_id%TYPE;
3183 l_main_invoice_status varchar2(100);
3184 l_child_report_header_id AP_EXPENSE_REPORT_HEADERS_ALL.report_header_id%TYPE;
3185 l_child_invoice_status varchar2(100);
3186
3187 l_parent_report_status VARCHAR2(100);
3188 l_main_report_status VARCHAR2(100);
3189
3190 l_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
3191
3192 l_report_header_id AP_EXPENSE_REPORT_HEADERS_ALL.report_header_id%TYPE;
3193 l_invoice_status VARCHAR2(100);
3194 l_identifier VARCHAR2(100);
3195 -- Bug: 9158198
3196 l_web_user_id Number := FND_PROFILE.VALUE('USER_ID');
3197
3198 BEGIN
3199
3200
3201 --
3202 -- initialize the status variables
3203 --
3204 l_parent_report_status := NULL; l_main_report_status := NULL;
3205
3206 --
3207 -- open the cursor and populate the variables
3208 --
3209 -- Bug: 6220330, Get the appropriate Cursor
3210 AP_WEB_UTILITIES_PKG.GetExpensesToUpdate(p_invoice_id, p_pay_status_flag, l_expenses_to_update);
3211
3212 LOOP
3213 FETCH l_expenses_to_update
3214 INTO l_identifier, l_report_header_id, l_invoice_status;
3215
3216 EXIT WHEN l_expenses_to_update%NOTFOUND;
3217
3218 IF ( l_identifier = 'PARENT' )
3219 THEN
3220 l_parent_report_header_id := l_report_header_id;
3221 l_parent_invoice_status := l_invoice_status;
3222 ELSIF (l_identifier = 'MAIN')
3223 THEN
3224 l_main_report_header_id := l_report_header_id;
3225 l_main_invoice_status := l_invoice_status;
3226 ELSE
3227 l_child_report_header_id := l_report_header_id;
3228 l_child_invoice_status := l_invoice_status;
3229 END IF;
3230
3231 END LOOP;
3232
3233 --
3234 -- handle separately if the current invoice has parent reports / child reports
3235 -- or it is a single cash based report
3236 --
3237
3238 IF ( l_parent_report_header_id IS NOT NULL )
3239 THEN
3240
3241 --
3242 -- So the current invoice has a parent which means the current invoice is of Credit Card
3243 -- in which case, the parent report status should change in the following manner
3244 -- PARENT STATUS PARENT NEW STATUS
3245 -- Invoiced --> Partially Paid
3246 -- Paid --> < main invoice status >
3247 -- Partially Paid --> Partially Paid ( no update )
3248 -- <any other status> --> ( no update )
3249 --
3250
3251 l_main_report_status := l_main_invoice_status;
3252
3253 IF (( l_parent_invoice_status = t_invoiced_status ) AND ( l_main_invoice_status = t_invoiced_status ))
3254 THEN
3255 l_parent_report_status := t_invoiced_status;
3256 ELSIF ( l_parent_invoice_status = t_partially_paid_status ) OR (l_main_invoice_status = t_partially_paid_status)
3257 OR
3258 (( l_parent_invoice_status = t_invoiced_status ) AND (l_main_invoice_status = t_paid_status))
3259 THEN
3260 l_parent_report_status := t_partially_paid_status;
3261 ELSIF ( l_parent_invoice_status = t_paid_status )
3262 THEN
3263 l_parent_report_status := l_main_invoice_status;
3264 END IF;
3265 ELSIF ( l_child_report_header_id IS NOT NULL )
3266 THEN
3267 --
3268 -- So the current invoice is the parent, so the child status should also be considered before updating
3269 -- in which case, the report status should change in the following manner
3270 -- CHILD STATUS MAIN NEW STATUS
3271 -- Invoiced/Partially Paid --> Partially Paid
3272 -- Paid --> < main invoice status >
3273 -- <any other status> --> ( no update )
3274 --
3275
3276 IF ( ( l_child_invoice_status = t_invoiced_status ) AND ( l_main_invoice_status = t_invoiced_status ) )
3277 THEN
3278 l_main_report_status := t_invoiced_status;
3279 ELSIF ( l_child_invoice_status = t_partially_paid_status ) OR ( l_child_invoice_status = t_invoiced_status ) OR
3280 (l_main_invoice_status = t_partially_paid_status)
3281 OR
3282 (( l_child_invoice_status = t_paid_status ) AND (l_main_invoice_status = t_invoiced_status))
3283 THEN
3284 l_main_report_status := t_partially_paid_status;
3285 ELSIF ( l_child_invoice_status = t_paid_status )
3286 THEN
3287 l_main_report_status := l_main_invoice_status;
3288 END IF;
3289
3290 ELSE
3291
3292 --
3293 -- A simple cash based report that does not have parent or child reports to it
3294 -- So just update this expense's status
3295 --
3296
3297 l_main_report_status := l_main_invoice_status;
3298
3299 END IF;
3300
3301 IF ( l_parent_report_status IS NOT NULL )
3302 THEN
3303
3304 -- Bug: 9158198, WHO Columns not updated.
3305 UPDATE ap_expense_report_headers_all
3306 SET expense_status_code = l_parent_report_status,
3307 last_update_date = sysdate,
3308 last_updated_by = nvl(l_web_user_id, last_updated_by),
3309 -- Bug: 10044058, to update expense_last_status_date
3310 expense_last_status_date = sysdate
3311 WHERE report_header_id = l_parent_report_header_id;
3312 END IF;
3313
3314 IF ( l_main_report_status IS NOT NULL )
3315 THEN
3316
3317 -- Bug: 9158198, WHO Columns not updated.
3318 UPDATE ap_expense_report_headers_all
3319 SET expense_status_code = l_main_report_status,
3320 last_update_date = sysdate,
3321 last_updated_by = nvl(l_web_user_id, last_updated_by),
3322 -- Bug: 10044058, to update expense_last_status_date
3323 expense_last_status_date = sysdate
3324 WHERE report_header_id = l_main_report_header_id;
3325
3326 END IF;
3327
3328 END UpdateExpenseStatusCode;
3329
3330 --------------------------------------------------------------
3331 -- Returns True if the input contains a character
3332 --------------------------------------------------------------
3333 FUNCTION ContainsChars(p_element IN VARCHAR2) RETURN BOOLEAN IS
3334 l_return_value BOOLEAN := FALSE;
3335 l_value NUMBER := 0;
3336
3337 BEGIN
3338 IF( INSTR(UPPER(p_element),'E') > 0 OR INSTR(p_element,'.') > 0 ) THEN
3339 l_return_value := TRUE;
3340 ELSE
3341 l_value := TO_NUMBER(p_element);
3342 l_return_value := FALSE;
3343 END IF;
3344
3345 RETURN l_return_value;
3346
3347 EXCEPTION WHEN VALUE_ERROR THEN
3348 RETURN TRUE;
3349
3350 END ContainsChars;
3351
3352 -- Bug: 6220330, Expenses are updated from two triggers, one on AP_INVOICES_ALL and
3353 -- one on AP_INVOICE_PAYMENTS_ALL. If p_pay_status_flag is null, the trigger is
3354 -- on AP_INVOICE_PAYMENTS_ALL, and on AP_INVOICES_ALL other wise.
3355
3356 PROCEDURE GetExpensesToUpdate(p_invoice_id IN AP_INVOICES_ALL.invoice_id%TYPE,
3357 p_pay_status_flag IN AP_INVOICES_ALL.payment_status_flag%TYPE,
3358 p_expenses_to_update OUT NOCOPY ExpensesToUpdate) IS
3359 BEGIN
3360 IF (p_pay_status_flag IS NULL) THEN
3361 -- For the trigger on AP_INVOICE_PAYMENTS_ALL
3362 OPEN p_expenses_to_update FOR
3363 -- cc in bp
3364 SELECT 'PARENT' Identifier,
3365 parent_aerh.report_header_id report_header_id,
3366 DECODE(parent_APS.GROSS_AMOUNT ,0,'PAID',
3367 DECODE(parent_AI.Payment_status_flag,
3368 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3369 FROM ap_expense_report_headers_all parent_aerh,
3370 ap_expense_report_headers_all main_aerh,
3371 ap_invoices_all parent_ai,
3372 ap_payment_schedules_all parent_aps
3373 WHERE
3374 main_aerh.bothpay_parent_id = parent_aerh.report_header_id (+) and
3375 parent_aerh.vouchno = parent_ai.invoice_id and
3376 parent_ai.invoice_id = parent_aps.invoice_id and
3377 main_aerh.vouchno = p_invoice_id
3378 UNION
3379 -- main/actual cash or cc
3380 SELECT 'MAIN' Identifier,
3381 main_aerh.report_header_id report_header_id,
3382 DECODE(main_APS.GROSS_AMOUNT ,0,'PAID',
3383 DECODE(main_AI.Payment_status_flag,
3384 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3385 FROM
3386 ap_expense_report_headers_all main_aerh,
3387 ap_invoices_all main_ai,
3388 ap_payment_schedules_all main_aps
3389 WHERE
3390 main_aerh.vouchno = main_ai.invoice_id and
3391 main_ai.invoice_id = main_aps.invoice_id and
3392 main_aerh.vouchno = p_invoice_id
3393 UNION
3394 -- cash in bp
3395 SELECT 'CHILD' Identifier,
3396 child_aerh.report_header_id report_header_id,
3397 DECODE(child_APS.GROSS_AMOUNT ,0,'PAID',
3398 DECODE(child_AI.Payment_status_flag,
3399 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3400 FROM
3401 ap_expense_report_headers_all child_aerh,
3402 ap_expense_report_headers_all main_aerh,
3403 ap_invoices_all child_ai,
3404 ap_payment_schedules_all child_aps
3405 WHERE child_aerh.bothpay_parent_id (+) = main_aerh.report_header_id and
3406 child_aerh.vouchno = child_ai.invoice_id and
3407 child_ai.invoice_id = child_aps.invoice_id and
3408 main_aerh.vouchno = p_invoice_id;
3409 ELSE
3410 OPEN p_expenses_to_update FOR
3411 -- For the trigger on AP_INVOICES_ALL
3412 -- cc in bp
3413 SELECT 'PARENT' Identifier,
3414 parent_aerh.report_header_id report_header_id,
3415 DECODE(parent_APS.GROSS_AMOUNT ,0,'PAID',
3416 DECODE(p_pay_status_flag,
3417 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3418 FROM ap_expense_report_headers_all parent_aerh,
3419 ap_expense_report_headers_all main_aerh,
3420 ap_payment_schedules_all parent_aps
3421 WHERE
3422 main_aerh.bothpay_parent_id = parent_aerh.report_header_id (+) and
3423 parent_aerh.vouchno = parent_aps.invoice_id and
3424 main_aerh.vouchno = p_invoice_id
3425 UNION
3426 -- main/actual cash or cc
3427 SELECT 'MAIN' Identifier,
3428 main_aerh.report_header_id report_header_id,
3429 DECODE(main_APS.GROSS_AMOUNT ,0,'PAID',
3430 DECODE(p_pay_status_flag,
3431 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3432 FROM
3433 ap_expense_report_headers_all main_aerh,
3434 ap_payment_schedules_all main_aps
3435 WHERE
3436 main_aerh.vouchno = main_aps.invoice_id and
3437 main_aerh.vouchno = p_invoice_id
3438 UNION
3439 -- cash in bp
3440 SELECT 'CHILD' Identifier,
3441 child_aerh.report_header_id report_header_id,
3442 DECODE(child_APS.GROSS_AMOUNT ,0,'PAID',
3443 DECODE(p_pay_status_flag,
3444 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3445 FROM
3446 ap_expense_report_headers_all child_aerh,
3447 ap_expense_report_headers_all main_aerh,
3448 ap_payment_schedules_all child_aps
3449 WHERE child_aerh.bothpay_parent_id (+) = main_aerh.report_header_id and
3450 child_aerh.vouchno = child_aps.invoice_id and
3451 main_aerh.vouchno = p_invoice_id;
3452
3453 END IF;
3454 EXCEPTION WHEN OTHERS THEN
3455 RAISE;
3456 END GetExpensesToUpdate;
3457
3458 ------------------------------------------------------------------------
3459 -- FUNCTION Oie_Round_Currency
3460 -- Bug 6136103
3461 -- Returns Amount in the Rounded format per spec in fnd_currencies
3462 -- Introduced as aputilsb.ap_round_currency errors out due to Caching.
3463 ------------------------------------------------------------------------
3464 FUNCTION Oie_Round_Currency
3465 (P_Amount IN number
3466 ,P_Currency_Code IN varchar2)
3467 RETURN number is
3468 l_rounded_amount number;
3469 BEGIN
3470 --
3471 select decode(FC.minimum_accountable_unit,
3472 null, decode(FC.precision, null, null, round(P_Amount,FC.precision)),
3473 round(P_Amount/FC.minimum_accountable_unit) *
3474 FC.minimum_accountable_unit)
3475 into l_rounded_amount
3476 from fnd_currencies FC
3477 where FC.currency_code = P_Currency_Code;
3478 --
3479 return(l_rounded_amount);
3480 --
3481 EXCEPTION
3482
3483 WHEN NO_DATA_FOUND THEN
3484 return (null);
3485 --
3486 END Oie_Round_Currency;
3487
3488 PROCEDURE UpdateImageReceiptStatus(p_report_header_id IN NUMBER) IS
3489 l_mgr_appr_flag VARCHAR2(10);
3490 l_stage_code VARCHAR2(30);
3491 l_org_count NUMBER;
3492 BEGIN
3493
3494 AP_WEB_EXPENSE_WF.CompleteReceiptsBlock(to_char(p_report_header_id));
3495
3496 SELECT nvl(workflow_approved_flag,'X') into l_mgr_appr_flag FROM AP_EXPENSE_REPORT_HEADERS_ALL
3497 WHERE report_header_id = p_report_header_id;
3498
3499 UPDATE ap_expense_report_headers_all SET image_receipts_status = 'RECEIVED',
3500 image_receipts_received_date = sysdate,
3501 last_update_date = sysdate,
3502 last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
3503 WHERE report_header_id = to_number(p_report_header_id);
3504 BEGIN
3505 SELECT count(*) INTO l_org_count
3506 FROM ap_expense_report_lines_all
3507 WHERE report_header_id = to_number(p_report_header_id)
3508 AND NVL(receipt_required_flag, 'N') = 'Y';
3509
3510 IF (l_org_count = 0) THEN
3511 UPDATE ap_expense_report_headers_all
3512 SET receipts_status = 'NOT_REQUIRED'
3513 WHERE report_header_id = to_number(p_report_header_id)
3514 and receipts_status IN ('OVERDUE', 'MISSING', 'REQUIRED');
3515 END IF;
3516 EXCEPTION
3517 WHEN OTHERS THEN
3518 NULL;
3519 END;
3520
3521 AP_WEB_RECEIPTS_WF.RaiseReceivedEvent(p_report_header_id, 'IMAGE');
3522 AP_WEB_UTILITIES_PKG.AddReportToAuditQueue(p_report_header_id);
3523 /*
3524 select nvl(rs.recpt_assign_stage_code,'X') into l_stage_code
3525 from ap_expense_report_headers_all aerh,
3526 ap_aud_rule_sets rs,
3527 ap_aud_rule_assignments_all rsa
3528 where aerh.report_header_id = p_report_header_id
3529 and aerh.org_id = rsa.org_id
3530 and rsa.rule_set_id = rs.rule_set_id
3531 and rs.rule_set_type = 'RULE'
3532 and TRUNC(SYSDATE)
3533 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
3534 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
3535
3536 IF (l_stage_code = 'RECPT_RECVD' OR (l_stage_code = 'MGR_APPR_IMG' AND l_mgr_appr_flag = 'M')) THEN
3537 AP_WEB_AUDIT_QUEUE_UTILS.enqueue_for_audit(p_report_header_id);
3538 END IF;
3539 */
3540 END UpdateImageReceiptStatus;
3541
3542 PROCEDURE AddReportToAuditQueue(p_report_header_id IN NUMBER, p_receipt_type IN VARCHAR2 DEFAULT NULL) IS
3543 l_mgr_appr_flag VARCHAR2(10);
3544 l_stage_code VARCHAR2(30);
3545 l_aud_img_required VARCHAR2(1);
3546 l_aud_org_required VARCHAR2(1);
3547 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
3548 BEGIN
3549
3550 BEGIN
3551 SELECT auditor_id INTO l_auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
3552 EXCEPTION
3553 WHEN OTHERS THEN
3554 l_auditor_id := null;
3555 END;
3556
3557 IF (l_auditor_id IS NULL) THEN
3558 SELECT nvl(workflow_approved_flag,'X') into l_mgr_appr_flag FROM AP_EXPENSE_REPORT_HEADERS_ALL
3559 WHERE report_header_id = p_report_header_id;
3560 select nvl(rs.recpt_assign_stage_code,'X'), NVL(rs.AUD_IMG_RECEIPT_REQUIRED, 'N') ,
3561 nvl(rs.AUD_PAPER_RECEIPT_REQUIRED, 'N')
3562 into l_stage_code, l_aud_img_required, l_aud_org_required
3563 from ap_expense_report_headers_all aerh,
3564 ap_aud_rule_sets rs,
3565 ap_aud_rule_assignments_all rsa
3566 where aerh.report_header_id = p_report_header_id
3567 and aerh.org_id = rsa.org_id
3568 and rsa.rule_set_id = rs.rule_set_id
3569 and rs.rule_set_type = 'RULE'
3570 and TRUNC(SYSDATE)
3571 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
3572 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
3573
3574 IF (((p_receipt_type IS NULL AND l_aud_img_required = 'Y') OR (p_receipt_type IS NOT NULL AND l_aud_org_required = 'Y'))
3575 AND (l_stage_code = 'RECPT_RECVD' OR
3576 (l_stage_code = 'MGR_APPR_IMG'
3577 AND (l_mgr_appr_flag = 'M' OR l_mgr_appr_flag = 'Y')
3578 ))
3579 ) THEN
3580 AP_WEB_AUDIT_QUEUE_UTILS.enqueue_for_audit(p_report_header_id);
3581 END IF;
3582 END IF;
3583 EXCEPTION
3584 WHEN OTHERS THEN
3585 NULL;
3586 END AddReportToAuditQueue;
3587
3588 FUNCTION GetImageAttachmentStatus(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
3589
3590 CURSOR line_cursor IS select report_line_id, NVL(image_receipt_required_flag,'N') image_receipt_required_flag
3591 FROM ap_expense_report_lines_all where report_header_id = p_report_header_id
3592 AND (itemization_parent_id is null or itemization_parent_id = -1);
3593
3594 line_rec line_cursor%ROWTYPE;
3595 l_header_attach VARCHAR2(2);
3596 l_line_attach VARCHAR2(2);
3597 l_rcpt_req_flag BOOLEAN := FALSE;
3598 l_rcpt_notreq_flag BOOLEAN := FALSE;
3599 l_count NUMBER := 0;
3600 BEGIN
3601
3602 --l_header_attach := fnd_attachment_util_pkg.get_atchmt_exists('OIE_HEADER_ATTACHMENTS', to_char(p_report_header_id));
3603 l_header_attach := GetAttachmentExists('OIE_HEADER_ATTACHMENTS', to_char(p_report_header_id));
3604
3605 IF (l_header_attach = 'Y') THEN
3606 RETURN 'RECEIVED';
3607 END IF;
3608 OPEN line_cursor;
3609 LOOP
3610 FETCH line_cursor INTO line_rec;
3611 l_rcpt_notreq_flag := FALSE;
3612 IF (line_rec.image_receipt_required_flag = 'Y') THEN
3613 l_count := l_count + 1;
3614 --l_line_attach := fnd_attachment_util_pkg.get_atchmt_exists('OIE_LINE_ATTACHMENTS', to_char(line_rec.report_line_id));
3615 l_line_attach := GetAttachmentExists('OIE_LINE_ATTACHMENTS', to_char(line_rec.report_line_id));
3616 IF (l_line_attach = 'N') THEN
3617 l_rcpt_req_flag := TRUE;
3618 END IF;
3619 ELSIF (NOT l_rcpt_req_flag AND l_count = 0 AND line_rec.image_receipt_required_flag = 'N') THEN
3620 l_rcpt_notreq_flag := TRUE;
3621 END IF;
3622 EXIT WHEN line_cursor%NOTFOUND;
3623 END LOOP;
3624 CLOSE line_cursor;
3625
3626 IF l_rcpt_req_flag THEN
3627 RETURN 'REQUIRED';
3628 ELSIF l_rcpt_notreq_flag THEN
3629 RETURN 'NOT_REQUIRED';
3630 ELSE
3631 RETURN 'RECEIVED';
3632 END IF;
3633
3634 EXCEPTION
3635 WHEN OTHERS THEN
3636 RETURN 'REQUIRED';
3637 END GetImageAttachmentStatus;
3638
3639 FUNCTION GetAttachmentExists(p_entity_name IN VARCHAR2, p_value IN VARCHAR2) RETURN VARCHAR2 IS
3640
3641 CURSOR attach_cur IS
3642 SELECT 1 FROM FND_ATTACHED_DOCUMENTS
3643 WHERE entity_name = p_entity_name AND pk1_value = p_value AND ROWNUM = 1;
3644
3645 attach_rec attach_cur%ROWTYPE;
3646
3647 BEGIN
3648
3649 OPEN attach_cur;
3650 FETCH attach_cur INTO attach_rec;
3651 IF attach_cur%FOUND THEN
3652 CLOSE attach_cur;
3653 RETURN 'Y';
3654 ELSE
3655 CLOSE attach_cur;
3656 RETURN 'N';
3657 END IF;
3658
3659 END GetAttachmentExists;
3660
3661 FUNCTION GetShortPaidReportMsg(p_report_header_id in NUMBER) RETURN VARCHAR2 IS
3662 CURSOR shortpay_reports IS
3663 SELECT invoice_num FROM AP_EXPENSE_REPORT_HEADERS_ALL
3664 WHERE shortpay_parent_id = p_report_header_id
3665 AND receipts_status = 'IN_PARENT_PACKET';
3666
3667 l_invoice_num AP_EXPENSE_REPORT_HEADERS_ALL.INVOICE_NUM%TYPE;
3668 l_concat VARCHAR2(2000) := '';
3669 BEGIN
3670 OPEN shortpay_reports;
3671 LOOP
3672 FETCH shortpay_reports INTO l_invoice_num;
3673 EXIT WHEN shortpay_reports%NOTFOUND;
3674 IF (l_concat <> '') THEN
3675 l_concat := l_concat || ',';
3676 END IF;
3677 l_concat := l_concat || l_invoice_num;
3678 END LOOP;
3679 RETURN l_concat;
3680 EXCEPTION
3681 WHEN OTHERS THEN
3682 RETURN '';
3683 END GetShortPaidReportMsg;
3684
3685 FUNCTION GetReceiptRuleExists(p_report_header_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2 IS
3686
3687 CURSOR ReceiptRuleCur(p_report_date IN DATE) IS
3688 SELECT rs.*
3689 FROM AP_AUD_RULE_SETS rs,
3690 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
3691 WHERE rsa.org_id = p_org_id
3692 AND rsa.rule_set_id = rs.rule_set_id
3693 AND rs.rule_set_type = 'RECEIPT'
3694 AND TRUNC(p_report_date) BETWEEN TRUNC(NVL(rsa.start_date,p_report_date)) AND TRUNC(NVL(rsa.end_date,p_report_date));
3695
3696 ReceiptRuleRec ReceiptRuleCur%ROWTYPE;
3697 l_report_date DATE;
3698 BEGIN
3699
3700 If (p_org_id IS NULL OR p_report_header_id IS NULL) THEN
3701 RETURN 'N';
3702 END IF;
3703
3704 IF p_report_header_id = -1 THEN
3705 l_report_date := sysdate;
3706 ELSE
3707 SELECT NVL(report_submitted_date, sysdate) INTO l_report_date
3708 FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;
3709 END IF;
3710
3711 OPEN ReceiptRuleCur(l_report_date);
3712 FETCH ReceiptRuleCur INTO ReceiptRuleRec;
3713
3714 IF ReceiptRuleCur%FOUND AND ReceiptRuleRec.image_receipt_req = 'Y' THEN
3715 RETURN 'Y';
3716 END IF;
3717
3718 CLOSE ReceiptRuleCur;
3719
3720 RETURN 'N';
3721
3722 END GetReceiptRuleExists;
3723
3724 FUNCTION GetReceiptRequiredStatus(p_report_header_id IN NUMBER, p_org_id IN NUMBER, p_receipt_type IN VARCHAR2) RETURN VARCHAR2 IS
3725
3726 l_receipts_status VARCHAR2(1) := 'N';
3727 BEGIN
3728
3729 l_receipts_status := GetReceiptRuleExists(p_report_header_id, p_org_id);
3730
3731 IF l_receipts_status = 'Y' THEN
3732
3733 IF p_receipt_type = 'ORIGINAL' THEN
3734
3735 SELECT DECODE(image_receipts_status,'NOT_REQUIRED','N','RECEIVED','N','Y') INTO l_receipts_status
3736 FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;
3737
3738 ELSIF p_receipt_type = 'IMAGE' THEN
3739
3740 SELECT DECODE(receipts_status,'NOT_REQUIRED','N','RECEIVED','N','Y') INTO l_receipts_status
3741 FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;
3742
3743 END IF;
3744
3745 END IF;
3746
3747 RETURN l_receipts_status;
3748
3749 EXCEPTION
3750 WHEN OTHERS THEN
3751 RETURN 'N';
3752
3753 END GetReceiptRequiredStatus;
3754
3755 FUNCTION ReportHasViolations(p_report_header_id IN NUMBER) RETURN BOOLEAN IS
3756 l_violation_count NUMBER;
3757 BEGIN
3758 select count(1) INTO l_violation_count
3759 from AP_POL_VIOLATIONS_ALL
3760 where report_header_id = p_report_header_id;
3761
3762
3763 IF (l_violation_count > 0) THEN
3764 RETURN TRUE;
3765 ELSE
3766 RETURN FALSE;
3767 END IF;
3768
3769 EXCEPTION
3770 WHEN OTHERS THEN
3771 RETURN FALSE;
3772
3773 END ReportHasViolations;
3774
3775 FUNCTION ReportHasOnlyCCLines(p_report_header_id IN NUMBER) RETURN BOOLEAN IS
3776 l_non_cc_line_count NUMBER;
3777 BEGIN
3778
3779 select count(1) INTO l_non_cc_line_count
3780 from ap_expense_report_lines_all aerl
3781 where aerl.report_header_id = p_report_header_id
3782 and ( CREDIT_CARD_TRX_ID is null
3783 OR ( CREDIT_CARD_TRX_ID is not null
3784 AND (NVL(receipt_required_flag,'N') = 'Y' OR NVL(image_receipt_required_flag,'N') = 'Y')
3785 )
3786 );
3787
3788 IF (l_non_cc_line_count = 0) THEN
3789 RETURN TRUE;
3790 ELSE
3791 RETURN FALSE;
3792 END IF;
3793
3794 EXCEPTION
3795 WHEN OTHERS THEN
3796 RETURN FALSE;
3797
3798 END ReportHasOnlyCCLines;
3799
3800 FUNCTION ReportHasNonRecLines(p_report_header_id IN NUMBER) RETURn BOOLEAN IS
3801 l_non_pdm_line_count NUMBER;
3802 l_rr_line_count NUMBER;
3803 BEGIN
3804
3805 select count(1) INTO l_non_pdm_line_count
3806 from ap_expense_report_lines_all aerl
3807 where aerl.report_header_id = p_report_header_id
3808 and NVL(aerl.category_code,'NONE') not in ('PER_DIEM','MILEAGE');
3809
3810 IF (l_non_pdm_line_count = 0) THEN
3811 RETURN TRUE;
3812 ELSE
3813
3814 select count(1) INTO l_rr_line_count
3815 from ap_expense_report_lines_all aerl
3816 where aerl.report_header_id = p_report_header_id
3817 and (nvl(aerl.receipt_required_flag, 'N') = 'Y' OR nvl(aerl.image_receipt_required_flag, 'N') = 'Y');
3818
3819 IF (l_rr_line_count = 0) THEN
3820 RETURN TRUE;
3821 ELSE
3822 RETURN FALSE;
3823 END IF;
3824 END IF;
3825 EXCEPTION
3826 WHEN OTHERS THEN
3827 RETURN FALSE;
3828 END ReportHasNonRecLines;
3829
3830 ----------------------------------------------------------------------------------------
3831 -- PROCEDURE ProcessImageReceiptStatus
3832 -- Called from the Expense Attachment Service
3833 ----------------------------------------------------------------------------------------
3834
3835 PROCEDURE ProcessImageReceiptStatus(p_report_header_id IN NUMBER) IS
3836 l_header_attach VARCHAR2(50);
3837 BEGIN
3838 l_header_attach := GetImageAttachmentStatus(p_report_header_id);
3839
3840 IF (l_header_attach = 'RECEIVED') THEN
3841 UpdateImageReceiptStatus(p_report_header_id);
3842 END IF;
3843 EXCEPTION
3844 WHEN OTHERS THEN
3845 RAISE;
3846
3847 END ProcessImageReceiptStatus;
3848
3849 FUNCTION GetMissingReceiptStatus(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
3850 l_receipts_status VARCHAR2(30);
3851 l_img_receipts_status VARCHAR2(30);
3852 BEGIN
3853 SELECT nvl(receipts_status, 'NOT_REQUIRED'), nvl(image_receipts_status, 'NOT_REQUIRED')
3854 INTO l_receipts_status, l_img_receipts_status
3855 FROM ap_expense_report_headers_all
3856 WHERE report_header_id = p_report_header_id;
3857
3858 IF (l_receipts_status = 'MISSING' AND l_img_receipts_status = 'MISSING') THEN
3859 RETURN 'BOTH';
3860 ELSIF(l_receipts_status = 'MISSING') THEN
3861 RETURN 'ORIGINAL';
3862 ELSIF(l_img_receipts_status = 'MISSING') THEN
3863 RETURN 'IMAGE';
3864 ELSE
3865 RETURN 'BOTH';
3866 END IF;
3867 EXCEPTION
3868 WHEN OTHERS THEN
3869 RETURN 'BOTH';
3870 END GetMissingReceiptStatus;
3871
3872 END AP_WEB_UTILITIES_PKG;