[Home] [Help]
PACKAGE BODY: APPS.AP_APXINAGE_XMLP_PKG
Source
1 PACKAGE BODY AP_APXINAGE_XMLP_PKG AS
2 /* $Header: APXINAGEB.pls 120.0 2007/12/27 07:47:53 vjaganat noship $ */
3
4 FUNCTION get_base_curr_data RETURN BOOLEAN IS
5
6 base_curr ap_system_parameters.base_currency_code%TYPE; prec fnd_currencies_vl.precision%TYPE; min_au fnd_currencies_vl.minimum_accountable_unit%TYPE; descr fnd_currencies_vl.description%TYPE;
7 BEGIN
8
9 base_curr := '';
10 prec := 0;
11 min_au := 0;
12 descr := '';
13
14 SELECT p.base_currency_code,
15 c.precision,
16 nvl(c.minimum_accountable_unit,0),
17 c.description
18 INTO base_curr,
19 prec,
20 min_au,
21 descr
22 FROM ap_system_parameters p,
23 fnd_currencies_vl c
24 WHERE p.base_currency_code = c.currency_code;
25
26 c_base_currency_code := base_curr;
27 c_base_precision := prec;
28 c_base_min_acct_unit := nvl(min_au,0);
29 c_base_description := descr;
30
31 RETURN (TRUE);
32
33 RETURN NULL; EXCEPTION
34
35 WHEN OTHERS THEN
36 RETURN (FALSE);
37
38 END;
39
40 FUNCTION custom_init RETURN BOOLEAN IS
41 MINDAY NUMBER(10);
42 MAXDAY NUMBER(10);
43 L_SORT_OPTION AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
44 L_SUMMARY_OPTION AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
45 L_FORMAT_OPTION AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
46 L_NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
47 L_INVOICE_TYPE AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
48 L_PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE;
49 BEGIN
50 BEGIN
51 SELECT displayed_field
52 INTO l_sort_option
53 FROM ap_lookup_codes
54 WHERE lookup_type = 'AGING_SORT_OPTION'
55 AND lookup_code = P_SORT_OPTION;
56 C_HEAD_SORT_OPTION := L_SORT_OPTION;
57 END;
58 BEGIN
59 IF (P_SUMMARY_OPTION is not NULL) then
60 SELECT Meaning
61 INTO C_HEAD_SUMMARY_OPTION
62 FROM FND_LOOKUPS
63 WHERE lookup_code = P_SUMMARY_OPTION
64 AND lookup_type = 'YES_NO';
65 END IF;
66
67 END;
68 BEGIN
69 IF (P_FORMAT_OPTION is not NULL) then
70 SELECT Meaning
71 INTO C_HEAD_FORMAT_OPTION
72 FROM FND_LOOKUPS
73 WHERE lookup_code = P_FORMAT_OPTION
74 AND lookup_type = 'YES_NO';
75 END IF;
76 END;
77
78 BEGIN
79 SELECT displayed_field
80 INTO l_nls_all
81 FROM ap_lookup_codes
82 WHERE lookup_type = 'NLS REPORT PARAMETER'
83 AND lookup_code = 'ALL';
84 END;
85 BEGIN
86 if (P_INVOICE_TYPE is null) then
87 C_INVOICE_TYPE_SELECT := '%';
88 C_HEAD_INVOICE_TYPE := L_NLS_ALL;
89 else
90 SELECT displayed_field
91 INTO l_invoice_type
92 FROM ap_lookup_codes
93 WHERE lookup_type = 'INVOICE TYPE'
94 AND lookup_code = P_INVOICE_TYPE;
95 C_HEAD_INVOICE_TYPE := L_INVOICE_TYPE;
96 C_INVOICE_TYPE_SELECT := P_INVOICE_TYPE;
97 end if;
98 END;
99 BEGIN
100 if (P_PARTY_ID is not null) then
101 SELECT hp.party_name
102 INTO l_party_name
103 FROM hz_parties hp
104 WHERE party_id = P_PARTY_ID;
105 C_VENDOR_NAME_SELECT := L_PARTY_NAME;
106 C_HEAD_VENDOR_NAME := L_PARTY_NAME;
107 P_PARTY_PREDICATE := 'AND HP.party_id = '||P_PARTY_ID;
108 else
109 C_VENDOR_NAME_SELECT := '%';
110 C_HEAD_VENDOR_NAME := L_NLS_ALL;
111 end if;
112 END;
113 BEGIN
114 SELECT min(days_start), max(days_to)
115 INTO MINDAY, MAXDAY
116 FROM ap_aging_period_lines aapl, ap_aging_periods aap
117 WHERE aapl.aging_period_id = aap.aging_period_id
118 AND upper(aap.period_name) = upper(P_PERIOD_TYPE);
119 C_MINDAYS := MINDAY;
120 C_MAXDAYS := MAXDAY;
121 END;
122
123 if P_AMOUNT_DUE_LOW is not null and P_AMOUNT_DUE_HIGH is not null then
124 P_AMOUNT_PREDICATE := 'AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) between '''
125 ||to_char(P_AMOUNT_DUE_LOW)||''' AND '''||to_char(P_AMOUNT_DUE_HIGH)||'''';
126 elsif P_AMOUNT_DUE_LOW is not null and P_AMOUNT_DUE_HIGH is null then
127 P_AMOUNT_PREDICATE := 'AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) >= '''
128 ||to_char(P_AMOUNT_DUE_LOW)||'''';
129 elsif P_AMOUNT_DUE_LOW is null and P_AMOUNT_DUE_HIGH is not null then
130 P_AMOUNT_PREDICATE := 'AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) <= '''
131 ||to_char(P_AMOUNT_DUE_HIGH)||'''';
132 end if;
133
134 RETURN (TRUE);
135
136 RETURN NULL; EXCEPTION
137
138 WHEN OTHERS THEN
139 RETURN (FALSE);
140
141 END;
142
143 FUNCTION get_cover_page_values RETURN BOOLEAN IS
144
145 BEGIN
146
147 RETURN(TRUE);
148
149 RETURN NULL; EXCEPTION
150 WHEN OTHERS THEN
151 RETURN(FALSE);
152
153 END;
154
155 FUNCTION get_nls_strings RETURN BOOLEAN IS
156 nls_all ap_lookup_codes.displayed_field%TYPE; nls_yes fnd_lookups.meaning%TYPE; nls_no fnd_lookups.meaning%TYPE;
157 BEGIN
158
159 nls_all := '';
160 nls_yes := '';
161 nls_no := '';
162
163 SELECT ly.meaning,
164 ln.meaning,
165 la.displayed_field
166 INTO nls_yes, nls_no, nls_all
167 FROM fnd_lookups ly, fnd_lookups ln, ap_lookup_codes la
168 WHERE ly.lookup_type = 'YES_NO'
169 AND ly.lookup_code = 'Y'
170 AND ln.lookup_type = 'YES_NO'
171 AND ln.lookup_code = 'N'
172 AND la.lookup_type = 'NLS REPORT PARAMETER'
173 AND la.lookup_code = 'ALL';
174
175 c_nls_yes := nls_yes;
176 c_nls_no := nls_no;
177 c_nls_all := nls_all;
178
179 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
180
181 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_no_data_exists"');*/null;
182
183 /*c_nls_no_data_exists := '*** '||c_nls_no_data_exists||' ***';*/
184 c_nls_no_data_exists := 'No Data Found';
185
186 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
187
188 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_end_of_report"');*/null;
189
190 /*c_nls_end_of_report := '*** '||c_nls_end_of_report||' ***';*/
191 c_nls_end_of_report := 'End of Report';
192
193 RETURN (TRUE);
194
195 RETURN NULL; EXCEPTION
196 WHEN OTHERS THEN
197 RETURN (FALSE);
198 END;
199
200 function BeforeReport return boolean is
201 begin
202
203 P_SORT_OPTION_UPPER := P_SORT_OPTION;
204 P_FORMAT_OPTION_UPPER := P_FORMAT_OPTION;
205
206 DECLARE
207
208 init_failure EXCEPTION;
209
210 BEGIN
211
212
213
214
215
216 /*SRW.USER_EXIT('FND SRWINIT');*/null;
217
218 IF (p_debug_switch = 'Y') THEN
219 /*SRW.MESSAGE('1','After SRWINIT');*/null;
220
221 END IF;
222
223
224 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
225 END IF;
226 IF (p_debug_switch = 'Y') THEN
227 /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
228
229 END IF;
230
231
232 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
233 END IF;
234 IF (p_debug_switch = 'Y') THEN
235 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
236
237 END IF;
238
239
240 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
241 END IF;
242 IF (p_debug_switch = 'Y') THEN
243 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
244
245 END IF;
246
247
248 IF (custom_init() <> TRUE) THEN
249 RAISE init_failure;
250 END IF;
251 IF (p_debug_switch = 'Y') THEN
252 /*SRW.MESSAGE('5','After custom_init');*/null;
253
254 END IF;
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279 BEGIN
280
281
282 SELECT sort_by_alternate_field
283 INTO SORT_BY_ALTERNATE
284 FROM AP_SYSTEM_PARAMETERS;
285
286
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 SORT_BY_ALTERNATE := 'N';
291 END;
292
293 IF(set_order_by() <> TRUE) THEN
294 RAISE init_failure;
295 END IF;
296 IF (p_debug_switch = 'Y') THEN
297 /*SRW.MESSAGE('7','After set_order_by');*/null;
298
299 END IF;
300
301 IF(get_period_info() <> TRUE) THEN
302 RAISE init_failure;
303 END IF;
304 IF (p_debug_switch = 'Y') THEN
305 /*SRW.MESSAGE('8','After Get_period_info');*/null;
306
307 END IF;
308
309
310
311
312
313 IF (p_debug_switch = 'Y') THEN
314 /*SRW.BREAK;*/null;
315
316 END IF;
317
318
319
320 RETURN (TRUE);
321
322
323
324 EXCEPTION
325
326 WHEN OTHERS THEN
327
328 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
329
330
331 END; return (TRUE);
332 end;
333
334 function AfterReport return boolean is
335 begin
336
337 BEGIN
338 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
343
344 END; return (TRUE);
345 end;
346
347 FUNCTION get_company_name RETURN BOOLEAN IS
348 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
349 l_name gl_sets_of_books.name%TYPE;
350 l_sob_id NUMBER;
351 l_report_start_date DATE;
352 BEGIN
353 l_report_start_date := sysdate; l_sob_id := p_set_of_books_id;
354 SELECT name,
355 chart_of_accounts_id
356 INTO l_name,
357 l_chart_of_accounts_id
358 FROM gl_sets_of_books
359 WHERE set_of_books_id = l_sob_id;
360
361 c_company_name_header := l_name;
362 c_chart_of_accounts_id := l_chart_of_accounts_id;
363 c_report_start_date := l_report_start_date;
364
365 RETURN (TRUE);
366
367 RETURN NULL; EXCEPTION
368
369 WHEN OTHERS THEN
370 RETURN (FALSE);
371
372 END;
373
374 FUNCTION get_flexdata RETURN BOOLEAN IS
375
376 BEGIN
377
378
379 null;
380 RETURN (TRUE);
381
382 RETURN NULL; EXCEPTION
383 WHEN OTHERS THEN
384 RETURN(FALSE);
385 END;
386
387 FUNCTION set_order_by RETURN BOOLEAN IS
388 BEGIN
389 if (upper(P_SUMMARY_OPTION) = 'N' and
390 upper(P_SORT_OPTION) = 'VENDOR NAME') then
391 P_ORDER_BY := ' ORDER BY
392 decode(:C_VENDOR_NAME_SELECT,
393 ''%'',decode(:SORT_BY_ALTERNATE, ''Y'',
394 UPPER(v.vendor_name_alt), UPPER(v.vendor_name)),
395 decode(:SORT_BY_ALTERNATE, ''Y'', v.vendor_name_alt, v.vendor_name)),
396 v.vendor_id,
397 decode(:SORT_BY_ALTERNATE, ''Y'', vs.vendor_site_code_alt, vs.vendor_site_code),
398 i.invoice_type_lookup_code,
399 ps.due_date,
400 i.invoice_num,
401 ps.payment_num';
402 RETURN (TRUE);
403 end if;
404
405 if (upper(P_SUMMARY_OPTION) = 'N' and
406 upper(P_SORT_OPTION) = 'INVOICE TYPE') then
407 P_ORDER_BY := ' ORDER BY
408 i.invoice_type_lookup_code,
409 decode(:C_VENDOR_NAME_SELECT,
410 ''%'',decode(:SORT_BY_ALTERNATE, ''Y'',
411 UPPER(v.vendor_name_alt), UPPER(v.vendor_name)),
412 decode(:SORT_BY_ALTERNATE, ''Y'', v.vendor_name_alt, v.vendor_name)),
413 v.vendor_id,
414 decode(:SORT_BY_ALTERNATE, ''Y'', vs.vendor_site_code_alt, vs.vendor_site_code),
415 i.invoice_type_lookup_code,
416 ps.due_date,
417 i.invoice_num,
418 ps.payment_num';
419 RETURN (TRUE);
420 end if;
421
422 if (upper(P_SUMMARY_OPTION) = 'Y' and
423 upper(P_SORT_OPTION) = 'VENDOR NAME') then
424 P_ORDER_BY := ' ORDER BY
425 decode(:C_VENDOR_NAME_SELECT,
426 ''%'',decode(:SORT_BY_ALTERNATE, ''Y'',
427 UPPER(v.vendor_name_alt), UPPER(v.vendor_name)),
428 decode(:SORT_BY_ALTERNATE, ''Y'', v.vendor_name_alt, v.vendor_name)),
429 v.vendor_id,
430 decode(:SORT_BY_ALTERNATE, ''Y'', vs.vendor_site_code_alt, vs.vendor_site_code),
431 i.invoice_type_lookup_code,
432 ps.due_date,
433 i.invoice_num,
434 ps.payment_num';
435 RETURN (TRUE);
436 end if;
437
438 if (upper(P_SUMMARY_OPTION) = 'Y' and
439 upper(P_SORT_OPTION) = 'INVOICE TYPE') then
440 P_ORDER_BY := ' ORDER BY
441 i.invoice_type_lookup_code,
442 decode(:C_VENDOR_NAME_SELECT,
443 ''%'',decode(:SORT_BY_ALTERNATE, ''Y'',
444 UPPER(v.vendor_name_alt), UPPER(v.vendor_name)),
445 decode(:SORT_BY_ALTERNATE, ''Y'', v.vendor_name_alt, v.vendor_name)),
446 v.vendor_id,
447 decode(:SORT_BY_ALTERNATE, ''Y'', vs.vendor_site_code_alt, vs.vendor_site_code),
448 i.invoice_type_lookup_code,
449 ps.due_date,
450 i.invoice_num,
451 ps.payment_num';
452 RETURN (TRUE);
453 end if;
454
455 RETURN NULL; EXCEPTION
456 WHEN OTHERS THEN
457 RETURN (FALSE);
458
459
460 END;
461
462 FUNCTION get_period_info RETURN BOOLEAN IS
463 l_period_days_start ap_aging_period_lines.days_start%TYPE;
464 l_period_days_to ap_aging_period_lines.days_to%TYPE;
465 l_period_seq_num ap_aging_period_lines.period_sequence_num%TYPE;
466 l_period_title1 ap_aging_period_lines.report_heading1%TYPE;
467 l_period_title2 ap_aging_period_lines.report_heading2%TYPE;
468
469 cursor period_info is
470 SELECT lines.days_start,
471 lines.days_to,
472 lines.period_sequence_num,
473 report_heading1,
474 report_heading2
475 FROM ap_aging_period_lines lines,
476 ap_aging_periods periods
477 WHERE lines.aging_period_id = periods.aging_period_id
478 AND upper(periods.period_name) = upper(p_period_type)
479 ORDER BY lines.period_sequence_num;
480
481 BEGIN
482 open period_info;
483 loop
484 fetch period_info into l_period_days_start,l_period_days_to,
485 l_period_seq_num,l_period_title1, l_period_title2;
486 exit when (period_info%NOTFOUND);
487 if (l_period_seq_num = 1) then
488 C_INV_DUE_1_HEAD_1 := l_period_title1;
489 C_INV_DUE_1_HEAD_2 := l_period_title2;
490 C_INV_DUE_1_RANGE_FR := l_period_days_start;
491 C_INV_DUE_1_RANGE_TO := l_period_days_to;
492 end if;
493
494 if (l_period_seq_num = 2) then
495 C_INV_DUE_2_HEAD_1 := l_period_title1;
496 C_INV_DUE_2_HEAD_2 := l_period_title2;
497 C_INV_DUE_2_RANGE_FR := l_period_days_start;
498 C_INV_DUE_2_RANGE_TO := l_period_days_to;
499 end if;
500
501 if (l_period_seq_num = 3) then
502 C_INV_DUE_3_HEAD_1 := l_period_title1;
503 C_INV_DUE_3_HEAD_2 := l_period_title2;
504 C_INV_DUE_3_RANGE_FR := l_period_days_start;
505 C_INV_DUE_3_RANGE_TO := l_period_days_to;
506 end if;
507
508 if (l_period_seq_num = 4) then
509 C_INV_DUE_4_HEAD_1 := l_period_title1;
510 C_INV_DUE_4_HEAD_2 := l_period_title2;
511 C_INV_DUE_4_RANGE_FR := l_period_days_start;
512 C_INV_DUE_4_RANGE_TO := l_period_days_to;
513 end if;
514
515 end loop;
516 close period_info;
517 return(TRUE);
518 null;
519 RETURN NULL; EXCEPTION
520 WHEN OTHERS THEN
521 RETURN (FALSE);
525 begin
522 END;
523
524 function c_contact_lineformula(C_CONTACT_SITE_ID in number) return varchar2 is
526
527 DECLARE
528 l_contact_name varchar2(160);
529 l_first_name varchar2(4);
530 l_last_name po_vendor_contacts.last_name%TYPE;
531 l_phone po_vendor_contacts.phone%TYPE;
532 BEGIN
533 SELECT substr(first_name,1,1), last_name,phone
534 INTO l_first_name,l_last_name,l_phone
535 FROM po_vendor_contacts
536 WHERE vendor_site_id = C_CONTACT_SITE_ID
537 AND rownum = 1;
538 if (l_first_name is not null or
539 l_last_name is not null or
540 l_phone is not null) then
541 l_contact_name := l_first_name ||'. '|| l_last_name||
542 ' '||l_phone ;
543 end if;
544 return(l_contact_name);
545 EXCEPTION
546 when NO_DATA_FOUND then null;
547 END;
548
549 RETURN NULL; end;
550
551 function c_percent_remainingformula(C_AMT_DUE_ORIGINAL in number, C_AMT_DUE_REMAINING in number) return number is
552 begin
553
554 DECLARE
555 l_calculated_value number(10,1);
556 BEGIN
557 if (nvl(C_AMT_DUE_ORIGINAL,0) > 0 or
558 nvl(C_AMT_DUE_ORIGINAL,0) < 0) then
559 l_calculated_value :=round((nvl(C_AMT_DUE_REMAINING,0)
560 /nvl(C_AMT_DUE_ORIGINAL,1))
561 *100,1);
562 return(l_calculated_value);
563 else
564 return(0);
565 end if;
566 END;
567
568 RETURN NULL; end;
569
570 function c_inv_due_amt_1formula(C_DAYS_PAST_DUE in number, C_AMT_DUE_REMAINING in number) return number is
571 begin
572
573 BEGIN
574 if (nvl(C_DAYS_PAST_DUE,0) >= C_INV_DUE_1_RANGE_FR and
575 nvl(C_DAYS_PAST_DUE,0) <= C_INV_DUE_1_RANGE_TO) then
576 return(C_AMT_DUE_REMAINING);
577 else
578 return(0);
579 end if;
580 END;
581 RETURN NULL; end;
582
583 function c_inv_due_amt_2formula(C_DAYS_PAST_DUE in number, C_AMT_DUE_REMAINING in number) return number is
584 begin
585
586 BEGIN
587 if (nvl(C_DAYS_PAST_DUE,0) >= C_INV_DUE_2_RANGE_FR and
588 nvl(C_DAYS_PAST_DUE,0) <= C_INV_DUE_2_RANGE_TO) then
589 return(C_AMT_DUE_REMAINING);
590 else
591 return(0);
592 end if;
593 END;
594 RETURN NULL; end;
595
596 function c_inv_due_amt_3formula(C_DAYS_PAST_DUE in number, C_AMT_DUE_REMAINING in number) return number is
597 begin
598
599 BEGIN
600 if (nvl(C_DAYS_PAST_DUE,0) >= C_INV_DUE_3_RANGE_FR and
601 nvl(C_DAYS_PAST_DUE,0) <= C_INV_DUE_3_RANGE_TO) then
602 return(C_AMT_DUE_REMAINING);
603 else
604 return(0);
605 end if;
606 END;
607 RETURN NULL; end;
608
609 function c_inv_due_amt_4formula(C_DAYS_PAST_DUE in number, C_AMT_DUE_REMAINING in number) return number is
610 begin
611
612 BEGIN
613 if (nvl(C_DAYS_PAST_DUE,0) >= C_INV_DUE_4_RANGE_FR and
614 nvl(C_DAYS_PAST_DUE,0) <= C_INV_DUE_4_RANGE_TO) then
615 return(C_AMT_DUE_REMAINING);
616 else
617 return(0);
618 end if;
619 END;
620 RETURN NULL; end;
621
622 function c_per_v_inv_amt_1formula(C_SUM_V_INV_AMT_1 in number, C_SUM_V_DUE_REMAINING in number) return number is
623 begin
624
625 DECLARE
626 l_percent number(10):=0;
627 BEGIN
628 l_percent := (round((nvl(C_SUM_V_INV_AMT_1,0) * 100)/
629 nvl(C_SUM_V_DUE_REMAINING,1),2));
630 return(l_percent);
631 EXCEPTION
632 WHEN ZERO_DIVIDE then
633 return(0);
634 END;
635 RETURN NULL; end;
636
637 function c_per_v_inv_amt_2formula(C_SUM_V_INV_AMT_2 in number, C_SUM_V_DUE_REMAINING in number) return number is
638 begin
639
640 DECLARE
641 l_percent number(10):=0;
642 BEGIN
643 l_percent := (round((nvl(C_SUM_V_INV_AMT_2,0) * 100)/
644 nvl(C_SUM_V_DUE_REMAINING,1),2));
645 return(l_percent);
646 EXCEPTION
647 WHEN ZERO_DIVIDE then
648 return(0);
649 END;
650 RETURN NULL; end;
651
652 function c_per_v_inv_amt_3formula(C_SUM_V_INV_AMT_3 in number, C_SUM_V_DUE_REMAINING in number) return number is
653 begin
654
655 DECLARE
656 l_percent number(10):=0;
657 BEGIN
658 l_percent := (round((nvl(C_SUM_V_INV_AMT_3,0) * 100)/
659 nvl(C_SUM_V_DUE_REMAINING,1),2));
660 return(l_percent);
661 EXCEPTION
662 WHEN ZERO_DIVIDE then
663 return(0);
664 END;
665 RETURN NULL; end;
666
667 function c_per_v_inv_amt_4formula(C_SUM_V_INV_AMT_4 in number, C_SUM_V_DUE_REMAINING in number) return number is
668 begin
669
670 DECLARE
671 l_percent number(10):=0;
672 BEGIN
673 l_percent := (round((nvl(C_SUM_V_INV_AMT_4,0) * 100)/
674 nvl(C_SUM_V_DUE_REMAINING,1),2));
675 return(l_percent);
676 EXCEPTION
677 WHEN ZERO_DIVIDE then
678 return(0);
679 END;
680 RETURN NULL; end;
681
682 function c_check_data_convertedformula(C_DATA_CONVERTED in varchar2) return number is
683 begin
684
685 BEGIN
686 if (C_DATA_CONVERTED = '*') then
687 C_REP_DATA_CONVERTED := '*';
688 return(1);
689
690 else
691 return(0);
692 end if;
693 END;
694 RETURN NULL; end;
695
696 function c_per_inv_due_amt_1formula(C_SUM_INV_DUE_AMT_1 in number, C_SUM_AMT_REMAINING in number) return number is
697 begin
698
699 DECLARE
700 l_percent number(10):=0;
701 BEGIN
702 l_percent := ((nvl(C_SUM_INV_DUE_AMT_1,0) * 100)/
706 WHEN ZERO_DIVIDE then
703 nvl(C_SUM_AMT_REMAINING,1));
704 return(l_percent);
705 EXCEPTION
707 return(0);
708
709 END;
710 RETURN NULL; end;
711
712 function c_per_inv_due_amt_2formula(C_SUM_INV_DUE_AMT_2 in number, C_SUM_AMT_REMAINING in number) return number is
713 begin
714
715 DECLARE
716 l_percent number(10):=0;
717 BEGIN
718 l_percent := ((nvl(C_SUM_INV_DUE_AMT_2,0) * 100)/
719 nvl(C_SUM_AMT_REMAINING,1));
720 return(l_percent);
721 EXCEPTION
722 WHEN ZERO_DIVIDE then
723 return(0);
724
725
726 END;
727 RETURN NULL; end;
728
729 function c_per_inv_due_amt_3formula(C_SUM_INV_DUE_AMT_3 in number, C_SUM_AMT_REMAINING in number) return number is
730 begin
731
732 DECLARE
733 l_percent number(10):=0;
734 BEGIN
735 l_percent := ((nvl(C_SUM_INV_DUE_AMT_3,0) * 100)/
736 nvl(C_SUM_AMT_REMAINING,1));
737 return(l_percent);
738 EXCEPTION
739 WHEN ZERO_DIVIDE then
740 return(0);
741
742 END;
743 RETURN NULL; end;
744
745 function c_per_inv_due_amt_4formula(C_SUM_INV_DUE_AMT_4 in number, C_SUM_AMT_REMAINING in number) return number is
746 begin
747
748 DECLARE
749 l_percent number(10):=0;
750 BEGIN
751 l_percent := ((nvl(C_SUM_INV_DUE_AMT_4,0) * 100)/
752 nvl(C_SUM_AMT_REMAINING,1));
753 return(l_percent);
754 EXCEPTION
755 WHEN ZERO_DIVIDE then
756 return(0);
757
758
759 END;
760 RETURN NULL; end;
761
762 function c_pgbrk_data_convertedformula(C_SUM_DATA_CONVERTED in number) return varchar2 is
763 begin
764
765 BEGIN
766 if (nvl(C_SUM_DATA_CONVERTED,0) > 0) then
767 return('*');
768 else
769 return(' ');
770 end if;
771 END;
772 RETURN NULL; end;
773
774 function c_v_data_convertedformula(C_SUM_V_DATA_CONVERTED in number) return varchar2 is
775 begin
776
777 BEGIN
778 if (nvl(C_SUM_V_DATA_CONVERTED,0) > 0) then
779 return('*');
780 else
781 return(' ');
782 end if;
783 END;
784 RETURN NULL; end;
785
786 function c_tot_per_inv_due_1formula(C_TOT_INV_DUE_AMT_1 in number, C_TOT_AMT_REMAINING in number) return number is
787 begin
788
789 DECLARE
790 l_percent number(10):=0;
791 BEGIN
792 l_percent := ((nvl(C_TOT_INV_DUE_AMT_1,0) * 100)/
793 nvl(C_TOT_AMT_REMAINING,1));
794 return(l_percent);
795 EXCEPTION
796 WHEN ZERO_DIVIDE then
797 return(0);
798
799
800 END;
801 RETURN NULL; end;
802
803 function c_tot_per_inv_due_2formula(C_TOT_INV_DUE_AMT_2 in number, C_TOT_AMT_REMAINING in number) return number is
804 begin
805
806 DECLARE
807 l_percent number(10):=0;
808 BEGIN
809 l_percent := ((nvl(C_TOT_INV_DUE_AMT_2,0) * 100)/
810 nvl(C_TOT_AMT_REMAINING,1));
811 return(l_percent);
812 EXCEPTION
813 WHEN ZERO_DIVIDE then
814 return(0);
815
816
817 END;
818 RETURN NULL; end;
819
820 function c_tot_per_inv_due_3formula(C_TOT_INV_DUE_AMT_3 in number, C_TOT_AMT_REMAINING in number) return number is
821 begin
822
823 DECLARE
824 l_percent number(10):=0;
825 BEGIN
826 l_percent := ((nvl(C_TOT_INV_DUE_AMT_3,0) * 100)/
827 nvl(C_TOT_AMT_REMAINING,1));
828 return(l_percent);
829 EXCEPTION
830 WHEN ZERO_DIVIDE then
831 return(0);
832
833
834 END;
835 RETURN NULL; end;
836
837 function c_tot_per_inv_due_4formula(C_TOT_INV_DUE_AMT_4 in number, C_TOT_AMT_REMAINING in number) return number is
838 begin
839
840 DECLARE
841 l_percent number(10):=0;
842 BEGIN
843 l_percent := ((nvl(C_TOT_INV_DUE_AMT_4,0) * 100)/
844 nvl(C_TOT_AMT_REMAINING,1));
845 return(l_percent);
846 EXCEPTION
847 WHEN ZERO_DIVIDE then
848 return(0);
849
850
851 END;
852 RETURN NULL; end;
853
854 --Functions to refer Oracle report placeholders--
855
856 Function C_BASE_CURRENCY_CODE_p return varchar2 is
857 Begin
858 return C_BASE_CURRENCY_CODE;
859 END;
860 Function C_BASE_PRECISION_p return number is
861 Begin
862 return C_BASE_PRECISION;
863 END;
864 Function C_BASE_MIN_ACCT_UNIT_p return number is
865 Begin
866 return C_BASE_MIN_ACCT_UNIT;
867 END;
868 Function C_BASE_DESCRIPTION_p return varchar2 is
869 Begin
870 return C_BASE_DESCRIPTION;
871 END;
872 Function C_COMPANY_NAME_HEADER_p return varchar2 is
873 Begin
874 return C_COMPANY_NAME_HEADER;
875 END;
876 Function C_REPORT_START_DATE_p return date is
877 Begin
878 return C_REPORT_START_DATE;
879 END;
880 Function C_NLS_YES_p return varchar2 is
881 Begin
882 return C_NLS_YES;
883 END;
884 Function C_NLS_NO_p return varchar2 is
885 Begin
886 return C_NLS_NO;
887 END;
888 Function C_NLS_ALL_p return varchar2 is
889 Begin
890 return C_NLS_ALL;
891 END;
892 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
893 Begin
894 return C_NLS_NO_DATA_EXISTS;
895 END;
896 Function C_REPORT_RUN_TIME_p return varchar2 is
897 Begin
898 return C_REPORT_RUN_TIME;
899 END;
900 Function C_CHART_OF_ACCOUNTS_ID_p return number is
901 Begin
902 return C_CHART_OF_ACCOUNTS_ID;
903 END;
907 END;
904 Function C_VENDOR_NAME_SELECT_p return varchar2 is
905 Begin
906 return C_VENDOR_NAME_SELECT;
908 Function C_INVOICE_TYPE_SELECT_p return varchar2 is
909 Begin
910 return C_INVOICE_TYPE_SELECT;
911 END;
912 Function C_MINDAYS_p return number is
913 Begin
914 return C_MINDAYS;
915 END;
916 Function C_MAXDAYS_p return number is
917 Begin
918 return C_MAXDAYS;
919 END;
920 Function C_INV_DUE_1_HEAD_1_p return varchar2 is
921 Begin
922 return C_INV_DUE_1_HEAD_1;
923 END;
924 Function C_INV_DUE_1_HEAD_2_p return varchar2 is
925 Begin
926 return C_INV_DUE_1_HEAD_2;
927 END;
928 Function C_INV_DUE_2_HEAD_1_p return varchar2 is
929 Begin
930 return C_INV_DUE_2_HEAD_1;
931 END;
932 Function C_INV_DUE_2_HEAD_2_p return varchar2 is
933 Begin
934 return C_INV_DUE_2_HEAD_2;
935 END;
936 Function C_INV_DUE_3_HEAD_1_p return varchar2 is
937 Begin
938 return C_INV_DUE_3_HEAD_1;
939 END;
940 Function C_INV_DUE_3_HEAD_2_p return varchar2 is
941 Begin
942 return C_INV_DUE_3_HEAD_2;
943 END;
944 Function C_INV_DUE_4_HEAD_1_p return varchar2 is
945 Begin
946 return C_INV_DUE_4_HEAD_1;
947 END;
948 Function C_INV_DUE_4_HEAD_2_p return varchar2 is
949 Begin
950 return C_INV_DUE_4_HEAD_2;
951 END;
952 Function C_INV_DUE_1_RANGE_FR_p return number is
953 Begin
954 return C_INV_DUE_1_RANGE_FR;
955 END;
956 Function C_INV_DUE_1_RANGE_TO_p return number is
957 Begin
958 return C_INV_DUE_1_RANGE_TO;
959 END;
960 Function C_INV_DUE_2_RANGE_FR_p return number is
961 Begin
962 return C_INV_DUE_2_RANGE_FR;
963 END;
964 Function C_INV_DUE_2_RANGE_TO_p return number is
965 Begin
966 return C_INV_DUE_2_RANGE_TO;
967 END;
968 Function C_INV_DUE_3_RANGE_FR_p return number is
969 Begin
970 return C_INV_DUE_3_RANGE_FR;
971 END;
972 Function C_INV_DUE_3_RANGE_TO_p return number is
973 Begin
974 return C_INV_DUE_3_RANGE_TO;
975 END;
976 Function C_INV_DUE_4_RANGE_FR_p return number is
977 Begin
978 return C_INV_DUE_4_RANGE_FR;
979 END;
980 Function C_INV_DUE_4_RANGE_TO_p return number is
981 Begin
982 return C_INV_DUE_4_RANGE_TO;
983 END;
984 Function C_HEAD_INVOICE_TYPE_p return varchar2 is
985 Begin
986 return C_HEAD_INVOICE_TYPE;
987 END;
988 Function C_HEAD_SORT_OPTION_p return varchar2 is
989 Begin
990 return C_HEAD_SORT_OPTION;
991 END;
992 Function C_HEAD_VENDOR_NAME_p return varchar2 is
993 Begin
994 return C_HEAD_VENDOR_NAME;
995 END;
996 Function C_REP_DATA_CONVERTED_p return varchar2 is
997 Begin
998 return C_REP_DATA_CONVERTED;
999 END;
1000 Function C_NLS_END_OF_REPORT_p return varchar2 is
1001 Begin
1002 return C_NLS_END_OF_REPORT;
1003 END;
1004 Function C_HEAD_SUMMARY_OPTION_p return varchar2 is
1005 Begin
1006 return C_HEAD_SUMMARY_OPTION;
1007 END;
1008 Function C_HEAD_FORMAT_OPTION_p return varchar2 is
1009 Begin
1010 return C_HEAD_FORMAT_OPTION;
1011 END;
1012 END AP_APXINAGE_XMLP_PKG ;
1013