[Home] [Help]
PACKAGE BODY: APPS.AR_ARXCCS_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXCCS_XMLP_PKG AS
2 /* $Header: ARXCCSB.pls 120.0 2007/12/27 13:38:59 abraghun noship $ */
3
4 function BeforeReport return boolean is
5
6
7 l_ld_sp varchar2(1);
8 begin
9
10 /*SRW.USER_EXIT('FND SRWINIT');*/null;
11
12
13
14 rp_message:=null;
15 IF to_number(p_reporting_level) = 1000 THEN
16 l_ld_sp:= mo_utils.check_ledger_in_sp(TO_NUMBER(p_reporting_entity_id));
17
18 IF l_ld_sp = 'N' THEN
19 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
20 rp_message:= FND_MESSAGE.get;
21 END IF;
22 END IF;
23
24
25
26 get_boiler_plates ;
27
28
29 rp_unavailable:= ARP_STANDARD.FND_MESSAGE('AR_REPORTS_UNAVAILABLE');
30 rp_years:= ARP_STANDARD.FND_MESSAGE('AR_REPORTS_YEARS');
31 rp_none:= ARP_STANDARD.FND_MESSAGE('AR_REPORTS_NONE');
32 rp_na_upper:= ARP_STANDARD.FND_MESSAGE('AR_REPORTS_NA_UPPER');
33 rp_no_limit:= ARP_STANDARD.FND_MESSAGE('AR_REPORTS_NO_LIMIT');
34
35 get_bucket_data;
36
37 return (TRUE);
38 end;
39
40 function AfterReport return boolean is
41 begin
42
43 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
44 return (TRUE);
45 end;
46
47 function report_nameformula(Company_Name in varchar2) return varchar2 is
48 l_report_name VARCHAR2(240);
49 BEGIN
50
51
52 RP_Company_Name:= Company_Name;
53
54 SELECT cp.user_concurrent_program_name
55 INTO l_report_name
56 FROM FND_CONCURRENT_PROGRAMS_VL cp,
57 FND_CONCURRENT_REQUESTS cr
58 WHERE cr.request_id = P_CONC_REQUEST_ID
59 AND cp.application_id = cr.program_application_id
60 AND cp.concurrent_program_id = cr.concurrent_program_id;
61
62 RP_Report_Name:= l_report_name;
63
64 RETURN(l_report_name);
65
66 RETURN NULL; EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RP_REPORT_NAME:= NULL;
69 RETURN(NULL);
70 END;
71
72 function c_address_ageformula(address_age in number) return varchar2 is
73 begin
74
75 /*srw.reference (address_age);*/null;
76
77 if address_age is NULL then
78 return (rp_unavailable);
79 else
80 return ( to_char(address_age)|| ' ' || rp_years);
81 end if ;
82 RETURN NULL; end;
83
84 function c_customer_ageformula(customer_age in number) return varchar2 is
85 begin
86
87 /*srw.reference (customer_age);*/null;
88
89 if customer_age is NULL then
90 return (rp_unavailable);
91 else
92 return ( to_char(customer_age)||' ' || rp_years);
93 end if ;
94 RETURN NULL; end;
95
96 function c_city_state_zipformula(city in varchar2, state in varchar2, postal_code in varchar2) return varchar2 is
97 begin
98
99 /*srw.reference (city);*/null;
100
101 /*srw.reference (state);*/null;
102
103 /*srw.reference (postal_code);*/null;
104
105 return (city|| ' , '||state||' '||postal_code);
106 end;
107
108 function C_CALC_PERCENTFormula(aging_balance_outstanding in number,total_cust_b0 in number,total_cust_b1 in number,total_cust_b2 in number,total_cust_b3 in number,total_cust_b4 in number,total_cust_b5 in number,total_cust_b6 in number) return Number is
109 begin
110
111 DECLARE
112 l_percent VARCHAR2 (100);
113 BEGIN
114 /*srw.reference (aging_balance_outstanding);*/null;
115
116 /*srw.reference (total_cust_b0);*/null;
117
118 /*srw.reference (total_cust_b1);*/null;
119
120 /*srw.reference (total_cust_b2);*/null;
121
122 /*srw.reference (total_cust_b3);*/null;
123
124 /*srw.reference (total_cust_b4);*/null;
125
126 /*srw.reference (total_cust_b5);*/null;
127
128 /*srw.reference (total_cust_b6);*/null;
129
130
131 c_percent_b0:= '';
132 c_percent_b1:= '';
133 c_percent_b2:= '';
134 c_percent_b3:= '';
135 c_percent_b4:= '';
136 c_percent_b5:= '';
137 c_percent_b6:= '';
138
139
140 if nvl(aging_balance_outstanding,0) = 0
141 then
142 c_percent_b0:= '.00';
143 c_percent_b1:= '.00';
144 c_percent_b2:= '.00';
145 c_percent_b3:= '.00';
146 c_percent_b4:= '.00';
147 c_percent_b5:= '.00';
148 c_percent_b6:= '.00';
149 else
150 c_percent_b0 := to_char((total_cust_b0 * 100)/(aging_balance_outstanding),'990.00');
151 c_percent_b1 := to_char((total_cust_b1 * 100)/(aging_balance_outstanding),'990.00');
152 c_percent_b2 := to_char((total_cust_b2 * 100)/(aging_balance_outstanding),'990.00');
153 c_percent_b3 := to_char((total_cust_b3 * 100)/(aging_balance_outstanding),'990.00');
154 c_percent_b4 := to_char((total_cust_b4 * 100)/(aging_balance_outstanding),'990.00');
155 c_percent_b5 := to_char((total_cust_b5 * 100)/(aging_balance_outstanding),'990.00');
156 c_percent_b6 := to_char((total_cust_b6 * 100)/(aging_balance_outstanding),'990.00');
157 end if;
158
159 return (0);
160
161 END ;
162
163 RETURN NULL; end;
164
165 function c_compute_amtformula(functional_currency in varchar2, customer_id in number, site_use_id in number, Currency_Bucket in varchar2, aging_balance_outstanding in number) return number is
166 begin
167
168 DECLARE
169 l_aging_on_account NUMBER ;
170 l_aging_unapplied NUMBER ;
171 l_aging_convert_on_account VARCHAR2(1);
172 l_aging_convert_unapplied VARCHAR2 (1);
173 l_aging_credit NUMBER ;
174 l_aging_convert_credit VARCHAR2 (1) ;
175 l_aging_in_collection NUMBER ;
176 l_aging_convert_collection VARCHAR2 (1);
177
178 BEGIN
179
180 /*srw.reference (customer_id);*/null;
181
182 /*srw.reference (site_use_id);*/null;
183
184 /*srw.reference (Currency_Bucket);*/null;
185
186 /*srw.reference (aging_balance_outstanding);*/null;
187
188 /*srw.reference (functional_currency);*/null;
189
190
191
192 c_adjusted_balance:= 0 ;
193 l_aging_on_account:= 0 ;
194 l_aging_unapplied := 0 ;
195 l_aging_convert_on_account:= '';
196 l_aging_convert_unapplied := '';
197 l_aging_credit:= 0 ;
198 l_aging_convert_credit:= '';
199 l_aging_in_collection:= 0 ;
200 l_aging_convert_collection:= '';
201
202 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
203 -AMOUNT_APPLIED, 0)),
204 0) on_account,
205 nvl(max(decode(ar_receivable_applications.status, 'ACC',
206 decode(ar_cash_receipts.currency_code, functional_currency, ' ',
207 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
208 ' ')), ' ') account_convert,
209 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
210 -amount_applied, 0)),
211 0) unapplied,
212 nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
213 decode(ar_cash_receipts.currency_code, functional_currency, ' ',
214 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
215 ' ')), ' ') unapp_convert
216 into l_aging_on_account,
217 l_aging_convert_on_account,
218 l_aging_unapplied,
219 l_aging_convert_unapplied
220 from ar_receivable_applications,
221 ar_cash_receipts
222 where ar_receivable_applications.cash_receipt_id =
223 ar_cash_receipts.cash_receipt_id
224 and ar_cash_receipts.pay_from_customer = customer_id
225 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
226 and ar_cash_receipts.currency_code = Currency_Bucket
227 and ar_receivable_applications.gl_date <= sysdate
228 and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y'
229 ;
230
231 c_aging_on_account:= l_aging_on_account ;
232 c_aging_unapplied:= l_aging_unapplied ;
233 c_aging_convert_on_account:= l_aging_convert_on_account ;
234 c_aging_convert_unapplied:= l_aging_convert_unapplied ;
235
236 select nvl(sum(amount_due_remaining ), 0),
237 nvl(max(decode(invoice_currency_code,
238 functional_currency, ' ',
239 decode(exchange_rate,
240 NULL, '*', ' '))), ' ')
241 into l_aging_credit,
242 l_aging_convert_credit
243 from ar_payment_schedules
244 where customer_id = customer_id
245 and customer_site_use_id = site_use_id
246 and invoice_currency_code = Currency_Bucket
247 and class = 'CM'
248 and gl_date <= sysdate
249 ;
250
251 c_aging_credit:= l_aging_credit ;
252 c_aging_convert_credit:= l_aging_convert_credit;
253
254 c_adjusted_balance:=
255 aging_balance_outstanding + c_aging_on_account + c_aging_unapplied + c_aging_credit ;
256
257
258 SELECT NVL(SUM(DECODE(IN_COLLECTION, 'Y',
259 AMOUNT_DUE_REMAINING, 0)),0) COLLECT,
260 NVL(MAX(DECODE(IN_COLLECTION, 'Y',
261 DECODE(INVOICE_CURRENCY_CODE, functional_currency, ' ',
262 DECODE(EXCHANGE_RATE, NULL, '*', ' ')), ' ')), ' ') CCONV
263 INTO l_aging_in_collection,
264 l_aging_convert_collection
265 FROM AR_PAYMENT_SCHEDULES
266 WHERE CUSTOMER_ID = CUSTOMER_ID
267 AND CUSTOMER_SITE_USE_ID = site_use_id
268 AND INVOICE_CURRENCY_CODE = Currency_Bucket
269 AND STATUS = 'OP'
270 ;
271 c_aging_in_collection:= l_aging_in_collection ;
272 c_aging_convert_collection:= l_aging_convert_collection ;
273 return (0);
274
275 END ;
276
277 RETURN NULL; end;
278
279 function c_high_inv_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, SITE_USE_ID in number, Currency_Bucket in varchar2) return number is
280 begin
281
282 DECLARE
283 l_cust_hist_high_invoice_amt NUMBER ;
284 l_cust_hist_conv_high_invoice VARCHAR2 (1);
285 l_cust_hist_high_invoice_date VARCHAR2 (11);
286
287
288 CURSOR C_HIGH_INV IS
289 SELECT NVL(AMOUNT_DUE_ORIGINAL, 0),
290 DECODE(INVOICE_CURRENCY_CODE, functional_currency, ' ',
291 DECODE(EXCHANGE_RATE, NULL, '*', ' ')),
292 TRX_DATE
293 FROM AR_PAYMENT_SCHEDULES
294 WHERE CUSTOMER_ID = CUSTOMER_ID
295 AND CUSTOMER_SITE_USE_ID = SITE_USE_ID
296 AND INVOICE_CURRENCY_CODE = Currency_Bucket
297 AND CLASS NOT IN ('CM', 'PMT')
298 ORDER BY AMOUNT_DUE_ORIGINAL DESC,
299 TRX_DATE DESC
300 ;
301 BEGIN
302
303 /*srw.reference (CUSTOMER_ID );*/null;
304
305 /*srw.reference (SITE_USE_ID );*/null;
306
307 /*srw.reference (Currency_Bucket );*/null;
308
309
310
311 l_cust_hist_high_invoice_amt:= 0 ;
312 l_cust_hist_conv_high_invoice:= '' ;
313 l_cust_hist_high_invoice_date:= '';
314
315 OPEN C_HIGH_INV ;
316 FETCH C_HIGH_INV
317 INTO l_cust_hist_high_invoice_amt,
318 l_cust_hist_conv_high_invoice,
319 l_cust_hist_high_invoice_date
320 ;
321 c_cust_hist_high_invoice_amt:=l_cust_hist_high_invoice_amt;
322 c_cust_hist_conv_high_invoice:=l_cust_hist_conv_high_invoice;
323 c_cust_hist_high_invoice_date:=l_cust_hist_high_invoice_date;
324
325 CLOSE C_HIGH_INV ;
326
327
328
329 return (0);
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 l_cust_hist_high_invoice_amt:= 0;
334 return (0);
335
336 WHEN OTHERS THEN
337 /*SRW.MESSAGE (1000,'Error in executing formula column for HIGH INV');*/null;
338
339 return (0);
340
341 END ;
342
343 RETURN NULL; end;
344
345 function c_cust_hist_high_limit_amtform(CUSTOMER_ID_1 in number, site_use_id_1 in number) return number is
346 begin
347
348
349 DECLARE
350 l_cust_hist_high_limit_amt NUMBER ;
351 l_cust_hist_high_limit_date DATE;
352
353 CURSOR C_CREDIT_LIMIT IS
354 SELECT H.CREDIT_LIMIT,
355 H.LAST_UPDATE_DATE
356 FROM AR_CREDIT_HISTORIES H
357 WHERE H.CUSTOMER_ID = CUSTOMER_ID_1
358 AND (H.SITE_USE_ID = site_use_id_1
359 OR
360 to_char(site_use_id) is null
361 OR
362 ( H.SITE_USE_ID IS NULL
363 AND NOT EXISTS (SELECT 1
364 FROM AR_CREDIT_HISTORIES H2
365 WHERE H2.SITE_USE_ID =
369 )
366 site_use_id_1
367 AND H2.CUSTOMER_ID =
368 customer_id_1
370 )
371 )
372 AND H.CREDIT_LIMIT IS NOT NULL
373 ORDER BY H.CREDIT_LIMIT DESC,
374 H.LAST_UPDATE_DATE DESC
375 ;
376
377 CURSOR C_CREDIT_LIMIT2 IS
378 select overall_credit_limit,
379 last_update_date
380 from hz_cust_profile_amts
381 where cust_account_profile_id = c_customer_profile_id
382 and currency_code = CP_limit_currency;
383
384
385 BEGIN
386 /*srw.reference (customer_id);*/null;
387
388 /*srw.reference (site_use_id);*/null;
389
390
391 OPEN C_CREDIT_LIMIT ;
392
393 FETCH C_CREDIT_LIMIT
394 INTO l_cust_hist_high_limit_amt,
395 l_cust_hist_high_limit_date
396 ;
397
398 if c_credit_limit%NOTFOUND then
399 BEGIN
400 open c_credit_limit2;
401
402 fetch c_credit_limit2
403 into l_cust_hist_high_limit_amt,
404 l_cust_hist_high_limit_date;
405
406 if c_credit_limit2%NOTFOUND then
407 return(0);
408 end if;
409
410 close c_credit_limit2;
411
412 c_cust_hist_high_limit_date:= l_cust_hist_high_limit_date;
413 return(l_cust_hist_high_limit_amt);
414 END;
415
416 end if;
417 CLOSE C_CREDIT_LIMIT ;
418
419 c_cust_hist_high_limit_date:= l_cust_hist_high_limit_date ;
420 return (l_cust_hist_high_limit_amt) ;
421
422
423 EXCEPTION WHEN NO_DATA_FOUND THEN
424 return (0);
425
426 WHEN OTHERS THEN
427 /*SRW.MESSAGE (1100, 'Error in Formula column for CREDIT LIMIT ');*/null;
428
429 RAISE;
430 return (0);
431 END ;
432
433 RETURN NULL; end;
434
435 --function c_rolling_summary_calcformula(functional_currency in varchar2, customer_id in number, site_use_id in --number, Currency_Bucket in varchar2) return number is
436 function c_rolling_summary_calcformula(functional_currency in varchar2, customer_id_1 in number, site_use_id in number, Currency_Bucket in varchar2) return number is
437 begin
438
439 DECLARE
440 l_ytd_sales_amount NUMBER ;
441 l_ytd_convert_sales VARCHAR2 (1);
442 l_ytd_sales_count NUMBER (20);
443 d_ytd_finance_charge_amount NUMBER ;
444 d_ytd_finance_charge_convert VARCHAR2 (1);
445 l_ytd_payment_amount NUMBER ;
446 l_ytd_convert_payment VARCHAR2 (1);
447 l_ytd_payment_count NUMBER (20);
448 l_ytd_credit_amount NUMBER ;
449 l_ytd_convert_credit VARCHAR2 (1);
450 l_ytd_credit_count NUMBER (20);
451 l_ytd_finance_charge_amount NUMBER ;
452 l_ytd_convert_finance_charge VARCHAR2 (1);
453 l_ytd_finance_charge_count NUMBER (20);
454 l_ytd_writeoff_amount NUMBER ;
455 l_ytd_convert_writeoff VARCHAR2 (1);
456 l_ytd_earned_discount_amount NUMBER ;
457 l_ytd_convert_earned_discount VARCHAR2 (1);
458 l_ytd_unearned_discount_amount NUMBER ;
459 l_ytd_conv_unearned_discount VARCHAR2 (1);
460 l_ytd_average_payment_days NUMBER (20);
461 l_ytd_average_days_late NUMBER (20);
462 l_ytd_late_payments_count NUMBER (20);
463 l_ytd_on_time_payments_count NUMBER (20);
464 l_ytd_nsf_amount NUMBER ;
465 l_ytd_convert_nsf VARCHAR2 (1);
466 l_ytd_nsf_count NUMBER (20);
467
468 BEGIN
469
470 /*srw.reference (Currency_Bucket);*/null;
471
472 /*srw.reference (customer_id);*/null;
473
474 /*srw.reference (site_use_id );*/null;
475
476
477 l_ytd_writeoff_amount:= 0 ;
478 l_ytd_convert_writeoff:= '';
479 l_ytd_sales_amount:= 0 ;
480 l_ytd_convert_sales:= '';
481 l_ytd_sales_count:= 0 ;
482 d_ytd_finance_charge_amount:= 0;
483 d_ytd_finance_charge_convert:= 0 ;
484 l_ytd_payment_amount:= 0 ;
485 l_ytd_convert_payment:= '';
486 l_ytd_payment_count:= 0 ;
487 l_ytd_credit_amount:= 0 ;
488 l_ytd_convert_credit:= '';
489 l_ytd_credit_count := 0 ;
490 l_ytd_finance_charge_amount:= 0 ;
491 l_ytd_convert_finance_charge:= '';
492 l_ytd_finance_charge_count := 0 ;
493 l_ytd_average_payment_days := 0 ;
494 l_ytd_average_days_late := 0 ;
495 l_ytd_late_payments_count := 0 ;
496 l_ytd_on_time_payments_count := 0 ;
497 l_ytd_earned_discount_amount := 0 ;
498 l_ytd_convert_earned_discount := '';
499 l_ytd_unearned_discount_amount := 0 ;
500 l_ytd_conv_unearned_discount := '';
501 l_ytd_nsf_amount := 0 ;
502 l_ytd_convert_nsf := '';
503 l_ytd_nsf_count := 0 ;
504
505 select nvl(sum(amount_due_original),0),
506 max(decode(invoice_currency_code,functional_currency, ' ',
507 decode(exchange_rate, NULL, '*', ' '))),
508 count(amount_due_original),
509 nvl(sum(receivables_charges_charged),0),
510 max(decode(invoice_currency_code,functional_currency, ' ',
511 decode(exchange_rate, NULL, '*', ' ')))
512 into l_ytd_sales_amount,
513 l_ytd_convert_sales,
514 l_ytd_sales_count,
515 d_ytd_finance_charge_amount,
516 d_ytd_finance_charge_convert
517 from ar_payment_schedules
518 --where ar_payment_schedules.customer_id = customer_id
519 where ar_payment_schedules.customer_id = customer_id_1
520 and customer_site_use_id = site_use_id
521 and invoice_currency_code = Currency_Bucket
522 and ar_payment_schedules.trx_date between
526
523 add_months(sysdate, -12) and sysdate
524 and ar_payment_schedules.class not in ('CM', 'PMT')
525 ;
527 select nvl(sum(-amount), 0) payment_amount,
528 nvl(max(decode(currency_code, functional_currency, ' ',
529 decode(exchange_rate, NULL, '*', ' '))), ' '),
530 nvl(count(amount),0) payment_count
531 into l_ytd_payment_amount,
532 l_ytd_convert_payment,
533 l_ytd_payment_count
534 from ar_cash_receipts
535 --where ar_cash_receipts.pay_from_customer = customer_id
536 where ar_cash_receipts.pay_from_customer = customer_id_1
537 and customer_site_use_id = site_use_id
538 and currency_code = Currency_Bucket
539 and ar_cash_receipts.creation_date between
540 add_months(sysdate, -12) and sysdate
541 and ar_cash_receipts.status <> 'REV'
542 and nvl(ar_cash_receipts.confirmed_flag,'Y') = 'Y'
543 ;
544 select nvl(sum(amount_due_original ), 0),
545 nvl(max(decode(ar_payment_schedules.invoice_currency_code,
546 functional_currency, ' ',
547 decode(ar_payment_schedules.exchange_rate,
548 NULL, '*', ' '))), ' '),
549 count(customer_trx_id)
550 into l_ytd_credit_amount,
551 l_ytd_convert_credit,
552 l_ytd_credit_count
553 from ar_payment_schedules
554 where ar_payment_schedules.trx_date between add_months(sysdate,-12) and
555 sysdate
556 --and ar_payment_schedules.customer_id = customer_id
557 and ar_payment_schedules.customer_id = customer_id_1
558 and customer_site_use_id = site_use_id
559 and invoice_currency_code = Currency_Bucket
560 and ar_payment_schedules.class = 'CM'
561 ;
562
563 select nvl(sum(amount), 0),
564 nvl(max(decode(ar_payment_schedules.invoice_currency_code,
565 functional_currency, ' ',
566 decode(ar_payment_schedules.exchange_rate,
567 NULL, '*', ' '))), ' '),
568 count(adjustment_id)
569 into l_ytd_finance_charge_amount,
570 l_ytd_convert_finance_charge,
571 l_ytd_finance_charge_count
572 from ar_adjustments,
573 ar_receivables_trx,
574 ar_payment_schedules
575 where ar_adjustments.payment_schedule_id=
576 ar_payment_schedules.payment_schedule_id
577 and ar_payment_schedules.customer_site_use_id = site_use_id
578 and ar_payment_schedules.invoice_currency_code = Currency_Bucket
579 and ar_adjustments.receivables_trx_id=ar_receivables_trx.receivables_trx_id
580 and ar_receivables_trx.type='FINCHRG'
581 and ar_adjustments.apply_date between add_months(sysdate,-12) and sysdate
582 and nvl(ar_adjustments.postable,'Y')='Y'
583 --and ar_payment_schedules.customer_id=customer_id
584 and ar_payment_schedules.customer_id=customer_id_1
585 ;
586
587 select nvl(sum(ar_adjustments.amount), 0),
588 nvl(max(decode(ar_payment_schedules.invoice_currency_code,
589 functional_currency, ' ',
590 decode(ar_payment_schedules.exchange_rate,
591 NULL, '*', ' '))), ' ')
592 into l_ytd_writeoff_amount,
593 l_ytd_convert_writeoff
594 from ar_adjustments,
595 ar_lookups lk,
596 ar_payment_schedules
597 where ar_adjustments.reason_code
598 = lk.lookup_code(+)
599 and lk.lookup_code(+) = 'WRITE OFF'
600 and lk.lookup_type(+) = 'ADJUST_REASON'
601 --and ar_payment_schedules.customer_id = customer_id
602 and ar_payment_schedules.customer_id = customer_id_1
603 and ar_payment_schedules.customer_site_use_id = site_use_id
604 and ar_payment_schedules.invoice_currency_code = Currency_Bucket
605 and ar_adjustments.payment_schedule_id =
606 ar_payment_schedules.payment_schedule_id
607 and ar_adjustments.apply_date between
608 add_months(sysdate, -12) and sysdate
609 and nvl(ar_adjustments.postable, 'Y') = 'Y'
610 ;
611
612 select nvl(sum(ar_receivable_applications.earned_discount_taken ), 0) earned,
613 nvl(max(decode(nvl(ar_receivable_applications.earned_discount_taken, 0),
614 0, ' ',
615 decode(ar_payment_schedules.invoice_currency_code,
616 functional_currency, ' ',
617 decode(ar_payment_schedules.exchange_rate,
618 NULL, '*', ' ')))), ' ') earned_cvt,
619 nvl(sum(ar_receivable_applications.unearned_discount_taken), 0) unearned,
620 nvl(max(decode(nvl(ar_receivable_applications.unearned_discount_taken, 0),
621 0, ' ',
622 decode(ar_payment_schedules.invoice_currency_code,
623 functional_currency, ' ',
624 decode(ar_payment_schedules.exchange_rate,
625 NULL, '*', ' ')))), ' ') unearned_cvt,
626 decode(count(ar_receivable_applications.apply_date), 0, 0,
627 round(sum(ar_receivable_applications.apply_date -
628 ar_payment_schedules.trx_date) /
629 count(ar_receivable_applications.apply_date))) avgdays,
630 decode(count(ar_receivable_applications.apply_date), 0, 0,
631 round(sum(ar_receivable_applications.apply_date -
632 ar_payment_schedules.due_date) /
633 count(ar_receivable_applications.apply_date))) avgdayslate,
634 nvl(sum(decode(sign(ar_receivable_applications.apply_date -
635 ar_payment_schedules.due_date),
636 1, 1, 0)), 0) newlate,
637 nvl(sum( decode(sign(ar_receivable_applications.apply_date -
638 ar_payment_schedules.due_date),
639 1, 0, 1)), 0) newontime
640 into l_ytd_earned_discount_amount,
641 l_ytd_convert_earned_discount,
642 l_ytd_unearned_discount_amount,
643 l_ytd_conv_unearned_discount,
644 l_ytd_average_payment_days,
645 l_ytd_average_days_late,
646 l_ytd_late_payments_count,
647 l_ytd_on_time_payments_count
648 from ar_receivable_applications, ar_payment_schedules
649 where ar_receivable_applications.applied_payment_schedule_id =
650 ar_payment_schedules.payment_schedule_id
651 and ar_payment_schedules.customer_id = customer_id
652 and ar_payment_schedules.customer_site_use_id = site_use_id
653 and ar_payment_schedules.invoice_currency_code = Currency_Bucket
654 and ar_receivable_applications.apply_date between
655 add_months(sysdate, -12) and sysdate
656 and ar_receivable_applications.status = 'APP'
660
657 and ar_receivable_applications.display = 'Y'
658 and nvl(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y'
659 ;
661 select nvl(sum(ROUND(decode(ar_cash_receipts.status,'NSF', acrh.acctd_amount, 'STOP', acrh.acctd_amount, 0)
662 , 2)), 0) nsf_amount,
663 nvl(max(decode(currency_code, functional_currency, ' ',
664 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' '))), ' '),
665 nvl(sum(decode(ar_cash_receipts.status,'NSF', 1, 'STOP', 1, 0)), 0) nsf_count
666 into l_ytd_nsf_amount,
667 l_ytd_convert_nsf,
668 l_ytd_nsf_count
669 from ar_cash_receipts,
670 ar_cash_receipt_history acrh
671 --where ar_cash_receipts.pay_from_customer = customer_id
672 where ar_cash_receipts.pay_from_customer = customer_id_1
673 and ar_cash_receipts.cash_receipt_id = acrh.cash_receipt_id
674 and acrh.first_posted_record_flag = 'Y'
675 and customer_site_use_id = site_use_id
676 and currency_code = Currency_Bucket
677 and ar_cash_receipts.reversal_date between
678 add_months(sysdate, -12) and sysdate
679
680 ;
681
682 c_ytd_writeoff_amount:= l_ytd_writeoff_amount ;
683 c_ytd_convert_writeoff:= l_ytd_convert_writeoff ;
684 c_ytd_sales_amount:= l_ytd_sales_amount ;
685 c_ytd_convert_sales:= l_ytd_convert_sales ;
686 c_ytd_sales_count:= l_ytd_sales_count ;
687 c_ytd_payment_amount:= l_ytd_payment_amount ;
688 c_ytd_convert_payment:= l_ytd_convert_payment ;
689 c_ytd_payment_count:= l_ytd_payment_count ;
690 c_ytd_credit_amount:= l_ytd_credit_amount ;
691 c_ytd_convert_credit:= l_ytd_convert_credit ;
692 c_ytd_credit_count:= l_ytd_credit_count ;
693 c_ytd_finance_charge_amount:= l_ytd_finance_charge_amount ;
694 c_ytd_convert_finance_charge:= l_ytd_convert_finance_charge ;
695 c_ytd_finance_charge_count:= l_ytd_finance_charge_count ;
696 c_ytd_average_payment_days:= l_ytd_average_payment_days ;
697 c_ytd_average_days_late:= l_ytd_average_days_late ;
698 c_ytd_late_payments_count:= l_ytd_late_payments_count ;
699 c_ytd_on_time_payments_count:= l_ytd_on_time_payments_count ;
700 c_ytd_earned_discount_amount:= l_ytd_earned_discount_amount ;
701 c_ytd_convert_earned_discount:= l_ytd_convert_earned_discount ;
702 c_ytd_unearned_discount_amount:= l_ytd_unearned_discount_amount ;
703 c_ytd_conv_unearned_discount:= l_ytd_conv_unearned_discount ;
704 c_ytd_nsf_amount:= l_ytd_nsf_amount ;
705 c_ytd_convert_nsf:= l_ytd_convert_nsf ;
706 c_ytd_nsf_count:= l_ytd_nsf_count ;
707
708 return (0);
709
710 EXCEPTION WHEN OTHERS THEN
711 return (0);
712
713 END ;
714
715
716 RETURN NULL; end;
717
718 function AfterPForm return boolean is
719 L_Collector_Min varchar2(30);
720 L_Collector_Max varchar2(30);
721
722 Begin
723
724 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
725 /*srw.message(100, 'DEBUG: p_reporting_entity_id: '||p_reporting_entity_id);*/null;
726
727 /*srw.message(1, 'After Call XLA_MO_REPORTING_API.Initialize');*/null;
728
729
733 p_org_where_param:= XLA_MO_REPORTING_API.Get_Predicate('PARAM',null);
730 p_org_where_ps:= XLA_MO_REPORTING_API.Get_Predicate('ps', null);
731 /*srw.message(100, 'DEBUG: ps: '||p_org_where_ps);*/null;
732
734 /*srw.message(100, 'DEBUG: ps: '||p_org_where_param);*/null;
735
736 p_org_where_ad:= XLA_MO_REPORTING_API.Get_Predicate('acct_site', null);
737 /*srw.message(100, 'DEBUG: ad: '||p_org_where_ad);*/null;
738
739 p_org_where_su:= XLA_MO_REPORTING_API.Get_Predicate('site_uses', null);
740 /*srw.message(100, 'DEBUG: su: '||p_org_where_su);*/null;
741
742 p_reporting_entity_name := XLA_MO_REPORTING_API.get_reporting_entity_name ;
743 /*srw.message(100, 'DEBUG: Reporting Entity Name: '||p_reporting_entity_name);*/null;
744
745 p_reporting_level_name := XLA_MO_REPORTING_API.get_reporting_level_name;
746 /*srw.message(100, 'DEBUG: Reporting Level Name : '||p_reporting_level_name);*/null;
747
748
749
750
751 /*srw.message (100, 'DEBUG: Customer Name (Low): ' || p_customer_name_low);*/null;
752
753 /*srw.message (100, 'DEBUG: Customer Name (High): ' || p_customer_name_high);*/null;
754
755
756 if P_Customer_Name_Low is NOT NULL then
757
758
759
760 lp_customer_name_low:= ' and party.party_name >= :p_customer_Name_Low';
761 else
762 lp_customer_name_low:=' ';
763 end if ;
764
765 if p_customer_name_high is NOT NULL then
766
767
768 lp_customer_name_high:= ' and party.party_name <= :p_customer_name_high';
769 else
770 lp_customer_name_high:=' ';
771 end if ;
772
773 /*srw.message (100, 'DEBUG: Q - Customer Name (Low): ' || lp_customer_name_low);*/null;
774
775 /*srw.message (100, 'DEBUG: Q - Customer Name (High): ' || lp_customer_name_high);*/null;
776
777
778
779 /*srw.message (100, 'DEBUG: Customer Number (Low): ' || p_customer_number_low);*/null;
780
781 /*srw.message (100, 'DEBUG: Customer Number (High): ' || p_customer_number_high);*/null;
782
783
784 if p_customer_number_low is NOT NULL then
785 lp_customer_number_low:= 'and cust_acct.account_number >= ''' || p_customer_number_low || '''';
786 else
787 lp_customer_number_low:=' ';
788 end if ;
789
790 if p_customer_number_high is NOT NULL then
791 lp_customer_number_high:= 'and cust_acct.account_number <= ''' || p_customer_number_high || '''';
792 else
793 lp_customer_number_high:=' ';
794 end if ;
795
796 /*srw.message (100, 'DEBUG: Q - Customer Number (Low): ' || lp_customer_number_low);*/null;
797
798 /*srw.message (100, 'DEBUG: Q - Customer Number (High): ' || lp_customer_number_high);*/null;
799
800
801
802 /*srw.message (100, 'DEBUG: Collector Name (Low): ' || p_collector_low);*/null;
803
804 /*srw.message (100, 'DEBUG: Collector Name (High): ' || p_collector_high);*/null;
805
806
807 if p_collector_low is NULL then
808 SELECT min(name)
809 INTO l_collector_min
810 FROM ar_collectors;
811
812 p_collector_min:= l_collector_min ;
813
814 /*srw.message (100, 'DEBUG: Collector Name (MIN): ' || p_collector_min);*/null;
815
816 end if ;
817
818 if p_collector_high is NULL then
819 SELECT max(name)
820 into l_collector_max
821 from ar_collectors;
822
823 p_collector_max := l_collector_max ;
824
825 /*srw.message (100, 'DEBUG: Collector Name (MAX): ' || p_collector_max);*/null;
826
827 end if ;
828
829 if p_collector_low is NOT NULL then
830 lp_coll_name_low := 'and coll.name >= ''' || p_collector_low || '''';
831 else
832 lp_coll_name_low:=' ';
833 end if;
834 /*srw.message (100, 'DEBUG: coll_name_low: ' || lp_coll_name_low);*/null;
835
836 if p_collector_high is NOT NULL then
837 lp_coll_name_high := 'and coll.name <= ''' || p_collector_high || '''';
838 else
839 lp_coll_name_high:=' ';
840 end if;
841 /*srw.message (100, 'DEBUG: coll_name_high: ' || lp_coll_name_high);*/null;
842
843
844 return (TRUE);
845 end;
846
847 function c_credit_summaryformula(customer_id in number, site_use_id_1 in number) return number is
848 begin
849
850 DECLARE
851
852 l_cred_summ_limit_tolerance VARCHAR2(30);
853 l_cred_summ_credit_rating VARCHAR2(30);
854 l_cred_summ_risk_code VARCHAR2(30);
855 l_cred_summ_credit_hold ar_lookups.meaning%type;
856 l_cred_summ_account_status VARCHAR2(30);
857 l_cred_summ_terms VARCHAR2(20);
858 l_cred_summ_exempt_dun ar_lookups.meaning%type;
859 l_cred_summ_collector VARCHAR2(30);
860 l_customer_profile_id NUMBER(30);
861 l_profile_site_use_id NUMBER(30);
862 ct_prof NUMBER(2);
863 yes ar_lookups.meaning%type;
864 no ar_lookups.meaning%type;
865
866 BEGIN
867
868 /*srw.reference (customer_id);*/null;
869
870 /*srw.message(999,'Credit Summary Cust Ref : '||customer_id);*/null;
871
872
873 /*srw.reference (site_use_id);*/null;
874
875 /*srw.message(999,'Credit Summary Site Ref : '||site_use_id);*/null;
876
877
878
879 select count(*)
880 into ct_prof
881 from hz_cust_profile_amts cpa,
882 hz_customer_profiles cp
883 where cp.cust_account_id = customer_id
884 -- and cp.site_use_id = site_use_id
885 and cp.site_use_id = site_use_id_1
889 --c_profile_site_use_id := site_use_id;
886 and cp.cust_account_profile_id = cpa.cust_account_profile_id;
887
888 if ct_prof > 0 then
890 c_profile_site_use_id := site_use_id_1;
891 else
892 c_profile_site_use_id := null;
893 end if;
894
895
896
897
898
899 SELECT INITCAP(YES.MEANING) yes,
900 INITCAP(NO.MEANING) no
901 INTO yes,
902 no
903 FROM AR_LOOKUPS YES,
904 AR_LOOKUPS NO
905 WHERE YES.LOOKUP_TYPE = 'YES/NO' AND
906 YES.LOOKUP_CODE = 'Y' AND
907 NO.LOOKUP_TYPE = 'YES/NO' AND
908 NO.LOOKUP_CODE = 'N';
909
910 if c_profile_site_use_id is NULL then
911
912 Select
913 to_char(nvl(cp.tolerance, 0), '990') || '%',
914 substr(nvl(cp.credit_rating, rp_na_upper),1,30),
915 cp.risk_code,
916 lk.meaning,
917 cp.account_status,
918 substr(nvl(term.name, rp_none),1,20),
919 decode(cp.dunning_letters, 'Y', no, yes),
920 coll.name,
921 cp.cust_account_profile_id
922 into
923 l_cred_summ_limit_tolerance,
924 l_cred_summ_credit_rating,
925 l_cred_summ_risk_code,
926 l_cred_summ_credit_hold,
927 l_cred_summ_account_status,
928 l_cred_summ_terms,
929 l_cred_summ_exempt_dun,
930 l_cred_summ_collector,
931 l_customer_profile_id
932 from hz_customer_profiles cp,
933 ar_collectors coll,
934 ar_lookups lk,
935 ra_terms term
936 where cp.collector_id = coll.collector_id
937 and cp.standard_terms = term.term_id (+)
938 and cp.cust_account_id = customer_id
939 and cp.site_use_id is null
940 and coll.name between nvl(p_collector_low,p_collector_min)
941 and nvl(p_collector_high,p_collector_max)
942 and nvl(cp.credit_hold,'N') = lk.lookup_code
943 and lk.lookup_type = 'YES/NO' ;
944
945
946 else
947
948 Select
949 to_char(nvl(cp.tolerance, 0), '990') || '%',
950 substr(nvl(cp.credit_rating,rp_na_upper),1,30),
951 cp.risk_code,
952 lk.meaning,
953 cp.account_status,
954 substr(nvl(term.name, rp_none),1,20),
955 decode(cp.dunning_letters, 'Y', no, yes),
956 coll.name,
957 cp.cust_account_profile_id
958 into
959 l_cred_summ_limit_tolerance,
960 l_cred_summ_credit_rating,
961 l_cred_summ_risk_code,
962 l_cred_summ_credit_hold,
963 l_cred_summ_account_status,
964 l_cred_summ_terms,
965 l_cred_summ_exempt_dun,
966 l_cred_summ_collector,
967 l_customer_profile_id
968
969 from hz_customer_profiles cp,
970 ar_collectors coll,
971 ar_lookups lk,
972 ra_terms term
973
974 where cp.collector_id = coll.collector_id
975 and cp.standard_terms = term.term_id (+)
976 and cp.cust_account_id = customer_id
977 --and cp.site_use_id = site_use_id
978 and cp.site_use_id = site_use_id_1
979 and coll.name between nvl(p_collector_low,p_collector_min)
980 and nvl(p_collector_high,p_collector_max)
981 and nvl(cp.credit_hold,'N') = lk.lookup_code
982 and lk.lookup_type = 'YES/NO'
983 ;
984
985 end if ;
986
987
988 c_cred_summ_limit_tolerance := l_cred_summ_limit_tolerance ;
989 c_cred_summ_credit_rating := l_cred_summ_credit_rating ;
990 c_cred_summ_risk_code := nvl(l_cred_summ_risk_code,rp_na_upper) ;
991 c_cred_summ_credit_hold := l_cred_summ_credit_hold ;
992 c_cred_summ_account_status := nvl(l_cred_summ_account_status,rp_na_upper);
993 c_cred_summ_terms := l_cred_summ_terms ;
994 c_cred_summ_exempt_dun := l_cred_summ_exempt_dun ;
995 c_cred_summ_collector := l_cred_summ_collector ;
996 c_customer_profile_id := l_customer_profile_id ;
997
998
999 return (0);
1000
1001 EXCEPTION WHEN NO_DATA_FOUND THEN
1002 return (0);
1003
1004 END ;
1005
1006 RETURN (0);
1007 end;
1008
1009 --function c_credit_amounts_calcformula(qc_customer in number, qc_site in number) return number is
1010 function c_credit_amounts_calcformula(qc_customer in number,qc_site in number,functional_currency varchar2,currency_credit in varchar2,credit_limit in number) return number is
1011 l_aging_balance_os_profile NUMBER ;
1012 adjusted_balance NUMBER;
1013 l_aging_convert_os_profile VARCHAR2(1);
1014 l_aging_on_account_profile NUMBER ;
1015 l_aging_conv_on_ac_profile VARCHAR2(1);
1016 l_aging_unapplied_profile NUMBER ;
1017 l_aging_conv_unap_prof VARCHAR2(1);
1018 l_cred_summ_avail_credit NUMBER ;
1019 l_dummy NUMBER(1);
1020 trx_curr VARCHAR2(15);
1021 trx_amount NUMBER;
1022 base_amount NUMBER;
1023 curr_exists NUMBER;
1024 CURSOR ps_trx IS
1025 SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due,exchange_rate_type
1026 from ar_payment_schedules ps
1027 where ps.customer_id = qc_customer
1028 and ps.customer_site_use_id = NVL(qc_site,ps.customer_site_use_id)
1029 and ps.status = 'OP'
1030 and ps.class not in ('CM', 'PMT')
1031 group by ps.invoice_currency_code,exchange_rate_type;
1032
1033 l_loop VARCHAR2(1);
1034 BEGIN
1035
1036 /*srw.message (593, 'related curr '||cp_related_currencies);*/null;
1037
1038 /*srw.message (593, 'site is '||qc_site);*/null;
1039
1040
1041 l_cred_summ_avail_credit := 0;
1042 l_aging_balance_os_profile := 0;
1043 adjusted_balance := 0;
1044 c_cred_summ_convert_limit := '' ;
1045 c_cred_summ_available := l_dummy;
1046 c_cred_summ_exceeded := l_dummy ;
1047 /*srw.reference (customer_id);*/null;
1048
1049 /*srw.reference (Currency_Credit);*/null;
1050
1051 /*srw.reference (site_use_id);*/null;
1052
1053 /*srw.reference (credit_limit);*/null;
1054
1055
1056
1057
1058 l_aging_on_account_profile:=0;
1059 l_aging_unapplied_profile:=0;
1060 l_loop:='N';
1061
1062 SELECT
1063 NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1064 AMOUNT_APPLIED, 0)),
1065 0) on_account,
1066 nvl(max(decode(ar_receivable_applications.status, 'ACC',
1067 decode(ar_cash_receipts.currency_code,functional_currency, ' ',
1068 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
1069 ' ')), ' ') account_convert,
1070 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1071 amount_applied, 0)),
1072 0) unapplied,
1073 nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
1074 decode(ar_cash_receipts.currency_code,functional_currency, ' ',
1075 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
1076 ' ')), ' ') unapp_convert
1077 into
1078 l_aging_on_account_profile,
1079 l_aging_conv_on_ac_profile,
1080 l_aging_unapplied_profile,
1081 l_aging_conv_unap_prof
1082 from
1083 ar_receivable_applications,
1084 ar_cash_receipts
1085 where
1086 ar_receivable_applications.cash_receipt_id =
1087 ar_cash_receipts.cash_receipt_id
1088 and ar_cash_receipts.pay_from_customer = qc_customer
1089 and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1090 NVL(qc_site, ar_cash_receipts.customer_site_use_id)
1091 and ar_cash_receipts.currency_code = currency_credit
1092 and ar_receivable_applications.gl_date <= sysdate;
1093
1094 for trx_rec in ps_trx loop
1095 curr_exists := instr(CP_related_currencies,trx_rec.invoice_currency_code);
1099
1096 /*srw.message(593,'in loop - curr_exists = '||curr_exists);*/null;
1097
1098 if curr_exists <> 0 then
1100 l_aging_on_account_profile:=0;
1101 l_aging_unapplied_profile:=0;
1102 l_loop:='Y';
1103
1104 trx_curr := trx_rec.invoice_currency_code;
1105
1106
1107 SELECT
1108 NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1109 AMOUNT_APPLIED, 0)), 0) on_account,
1110 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1111 amount_applied, 0)), 0) unapplied
1112 into
1113 l_aging_on_account_profile,
1114 l_aging_unapplied_profile
1115 from
1116 ar_receivable_applications,
1117 ar_cash_receipts
1118 where
1119 ar_receivable_applications.cash_receipt_id =
1120 ar_cash_receipts.cash_receipt_id
1121 and ar_cash_receipts.pay_from_customer = qc_customer
1122 and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1123 NVL(qc_site, ar_cash_receipts.customer_site_use_id)
1124 and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
1125 and ar_receivable_applications.gl_date <= sysdate;
1126
1127 trx_amount := trx_rec.ammount_due -
1128 l_aging_unapplied_profile -
1129 l_aging_on_account_profile;
1130
1131 base_amount :=
1132 gl_currency_api.convert_amount_sql
1133 ( trx_curr,
1134 CP_limit_currency,
1135 SYSDATE,
1136 'Corporate',
1137 trx_amount
1138 );
1139 if base_amount = -1 then
1140 base_amount:=0;
1141 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
1142 FND_MESSAGE.SET_TOKEN('CC',trx_curr,FALSE);
1143 /*SRW.MESSAGE(201,FND_MESSAGE.GET);*/null;
1144
1145
1146 elsif base_amount = -2 then
1147 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
1148 /*SRW.MESSAGE(200,FND_MESSAGE.GET);*/null;
1149
1150 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
1151
1152 end if;
1153 /*srw.message (200, 'DEBUG: base_amount: ' || to_char(base_amount));*/null;
1154
1155
1156
1157 adjusted_balance := Adjusted_balance + base_amount;
1158 end if;
1159 end loop;
1160
1161 if (credit_limit is NOT NULL ) then
1162
1163 If l_loop = 'Y' then
1164 l_cred_summ_avail_credit := credit_limit - Adjusted_balance;
1165 Else
1166 l_cred_summ_avail_credit := credit_limit + (l_aging_unapplied_profile+l_aging_on_account_profile);
1167 End if;
1168
1169 if (( l_aging_conv_on_ac_profile = '*' ) or
1170 (l_aging_conv_unap_prof = '*')) then
1171 c_cred_summ_convert_limit := '*';
1172 end if ;
1173 if l_cred_summ_avail_credit < 0 then
1174 c_cred_summ_available := 0 ;
1175 c_cred_summ_exceeded := l_cred_summ_avail_credit ;
1176 else
1177 c_cred_summ_exceeded := 0 ;
1178 c_cred_summ_available := l_cred_summ_avail_credit ;
1179 end if ;
1180
1181 end if ;
1182
1183 /*srw.message (777, 'available credit= '||TO_CHAR(l_cred_summ_avail_credit));*/null;
1184
1185 return (0);
1186 end;
1187
1188 function c_last_invoice_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1189 begin
1190
1191 DECLARE
1192 l_last_invoice_number VARCHAR2 (100);
1193 l_last_invoice_type VARCHAR2 (100);
1194 l_last_invoice_currency VARCHAR2 (15);
1195 l_last_invoice_amount NUMBER;
1196 l_last_invoice_converted VARCHAR2 (1);
1197 l_last_invoice_date VARCHAR2 (11);
1198 l_last_invoice_days_since VARCHAR2 (100);
1199 l_dummy NUMBER (1);
1200
1201 CURSOR C_LAST_INVOICE IS
1202 SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
1203 RA_CUST_TRX_TYPES.NAME,
1204 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
1205 AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
1206 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
1207 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1208 RA_CUSTOMER_TRX.TRX_DATE,
1209 TO_CHAR(ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE))
1210 FROM RA_CUST_TRX_TYPES,
1211 AR_PAYMENT_SCHEDULES,
1212 RA_CUSTOMER_TRX
1213 WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
1214 AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
1215 AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
1216 AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
1217 AND AR_PAYMENT_SCHEDULES.CLASS || '' = 'INV'
1218 ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
1219 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
1220 ;
1221
1222
1223 BEGIN
1224 /*srw.reference (customer_id);*/null;
1225
1226 /*srw.reference (site_use_id);*/null;
1227
1228
1229
1230 c_last_invoice_number := '' ;
1231 c_last_invoice_type := '' ;
1232 c_last_invoice_currency := '' ;
1233 c_last_invoice_amount := l_dummy ;
1234 c_last_invoice_converted := '' ;
1235 c_last_invoice_date := '' ;
1239 OPEN C_LAST_INVOICE ;
1236 c_last_invoice_days_since := '' ;
1237
1238
1240
1241 FETCH C_LAST_INVOICE
1242 INTO
1243 l_last_invoice_number,
1244 l_last_invoice_type,
1245 l_last_invoice_currency,
1246 l_last_invoice_amount,
1247 l_last_invoice_converted,
1248 l_last_invoice_date,
1249 l_last_invoice_days_since
1250 ;
1251
1252
1253 if l_last_invoice_number is NOT NULL then
1254
1255 c_last_invoice_number := l_last_invoice_number ;
1256 c_last_invoice_type := l_last_invoice_type ;
1257 c_last_invoice_currency := l_last_invoice_currency ;
1258 c_last_invoice_amount := l_last_invoice_amount ;
1259 c_last_invoice_converted := l_last_invoice_converted ;
1260 c_last_invoice_date := l_last_invoice_date ;
1261 c_last_invoice_days_since := l_last_invoice_days_since ;
1262 else
1263 c_last_invoice_number := rp_none ;
1264
1265 end if ;
1266
1267
1268 CLOSE C_LAST_INVOICE ;
1269 return (0);
1270
1271 EXCEPTION WHEN NO_DATA_FOUND THEN
1272
1273 c_last_invoice_number := rp_none ;
1274 c_last_invoice_type := l_last_invoice_type ;
1275 c_last_invoice_currency := l_last_invoice_currency ;
1276 c_last_invoice_amount := l_last_invoice_amount ;
1277 c_last_invoice_converted := l_last_invoice_converted ;
1278 c_last_invoice_date := l_last_invoice_date ;
1279 c_last_invoice_days_since := l_last_invoice_days_since ;
1280
1281 WHEN OTHERS THEN
1282 /*SRW.MESSAGE (1000,to_char(SQLCODE)||' Error in Last Invoice Formula column');*/null;
1283
1284
1285 return (0);
1286
1287 END ;
1288
1289 RETURN NULL; end;
1290
1291 --function c_last_credit_memo_formulaform(CUSTOMER_ID in number, site_use_id in number) return number is
1292 function c_last_credit_memo_formulaform(functional_currency varchar2,CUSTOMER_ID_1 in number, site_use_id_1 in number) return number is
1293 begin
1294
1295 DECLARE
1296 l_last_cm_number VARCHAR2 (100);
1297 l_last_cm_type VARCHAR2 (100);
1298 l_last_cm_currency VARCHAR2 (20);
1299 l_last_cm_date VARCHAR2 (10);
1300 l_last_cm_days_since VARCHAR2 (20);
1301 l_last_cm_prev_trx NUMBER (20);
1302 l_last_cm_id NUMBER (20);
1303 l_dummy NUMBER (1);
1304
1305 CURSOR C_CREDIT_MEMO IS
1306 SELECT TRX1.TRX_NUMBER,
1307 TYPES.NAME,
1308 PS.INVOICE_CURRENCY_CODE,
1309 TRX1.TRX_DATE,
1310 ROUND(TRUNC(SYSDATE) - TRX1.TRX_DATE),
1311 TRX2.CUSTOMER_TRX_ID,
1312 TRX1.CUSTOMER_TRX_ID
1313 FROM RA_CUST_TRX_TYPES TYPES, RA_CUSTOMER_TRX TRX1, AR_PAYMENT_SCHEDULES PS,
1314 RA_CUSTOMER_TRX TRX2, AR_RECEIVABLE_APPLICATIONS APP
1315 WHERE TRX1.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
1316 AND TRX1.BILL_TO_CUSTOMER_ID = CUSTOMER_ID_1
1317 AND APP.APPLIED_CUSTOMER_TRX_ID = TRX2.CUSTOMER_TRX_ID (+)
1318 AND APP.CUSTOMER_TRX_ID (+) = TRX1.CUSTOMER_TRX_ID
1322 ORDER BY TRX1.TRX_DATE DESC,
1319 AND PS.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID
1320 AND PS.CUSTOMER_SITE_USE_ID = site_use_id_1
1321 AND PS.CLASS = 'CM'
1323 TRX1.CUSTOMER_TRX_ID DESC
1324 ;
1325
1326 BEGIN
1327 /*srw.reference (customer_id);*/null;
1328
1329 /*srw.reference (site_use_id);*/null;
1330
1331
1332 c_last_cm_number := '' ;
1333 c_last_cm_type := '' ;
1334 c_last_cm_currency := '' ;
1335 c_last_cm_date := '' ;
1336 c_last_cm_days_since := '' ;
1337 c_last_cm_prev_trx := l_dummy ;
1338 c_last_cm_id := l_dummy ;
1339 c_last_cm_rel_invoice :='' ;
1340 c_last_cm_amount := l_dummy ;
1341 c_last_cm_converted := '' ;
1342 OPEN C_CREDIT_MEMO ;
1343
1344 FETCH C_CREDIT_MEMO
1345 INTO l_last_cm_number ,
1346 l_last_cm_type,
1347 l_last_cm_currency,
1348 l_last_cm_date,
1349 l_last_cm_days_since,
1350 l_last_cm_prev_trx,
1351 l_last_cm_id
1352 ;
1353
1354
1355 if l_last_cm_number is NOT NULL then
1356 c_last_cm_number := l_last_cm_number ;
1357 c_last_cm_type := l_last_cm_type ;
1358 c_last_cm_currency := l_last_cm_currency ;
1359 c_last_cm_date := l_last_cm_date ;
1360 c_last_cm_days_since := l_last_cm_days_since ;
1361 c_last_cm_prev_trx := l_last_cm_prev_trx ;
1362 c_last_cm_id := l_last_cm_id ;
1363
1364 DECLARE
1365 l_last_cm_rel_invoice VARCHAR2 (20);
1366 BEGIN
1367 /*SRW.REFERENCE (CUSTOMER_ID);*/null;
1368
1369 /*SRW.REFERENCE (site_use_id);*/null;
1370
1371
1372 SELECT TRX_NUMBER
1373 INTO l_last_cm_rel_invoice
1374 FROM RA_CUSTOMER_TRX
1375 WHERE CUSTOMER_TRX_ID = c_last_cm_prev_trx
1376 AND BILL_TO_SITE_USE_ID = SITE_USE_ID_1
1377 ;
1378
1379 c_last_cm_rel_invoice := l_last_cm_rel_invoice ;
1380
1381 EXCEPTION WHEN NO_DATA_FOUND THEN
1382 c_last_cm_rel_invoice := l_last_cm_rel_invoice ;
1383 END ;
1384
1385 DECLARE
1386 l_last_cm_amount NUMBER ;
1387 l_last_cm_converted VARCHAR2 (1);
1388 BEGIN
1389
1390
1391 SELECT NVL( SUM( P.AMOUNT_DUE_ORIGINAL) , 0),
1392 MAX(DECODE(P.INVOICE_CURRENCY_CODE,functional_currency, ' ',
1393 DECODE(P.EXCHANGE_RATE,NULL, '*', ' ')))
1394 INTO l_last_cm_amount,
1395 l_last_cm_converted
1396 FROM AR_PAYMENT_SCHEDULES P
1397 WHERE P.CUSTOMER_TRX_ID = c_last_cm_id
1398 AND P.CUSTOMER_SITE_USE_ID = SITE_USE_ID_1
1399 ;
1400 c_last_cm_amount := l_last_cm_amount ;
1401 c_last_cm_converted := l_last_cm_converted ;
1402
1403 EXCEPTION WHEN NO_DATA_FOUND THEN
1404 c_last_cm_amount := l_last_cm_amount ;
1405 c_last_cm_converted := l_last_cm_converted ;
1406
1407 END ;
1408
1409
1410 else
1411 c_last_cm_number := rp_none;
1412 end if ;
1413
1414 CLOSE C_CREDIT_MEMO ;
1415 return (0);
1416
1417 EXCEPTION WHEN NO_DATA_FOUND THEN
1418 c_last_cm_number := rp_none ;
1419 c_last_cm_type := l_last_cm_type ;
1420 c_last_cm_currency := l_last_cm_currency ;
1421 c_last_cm_date := l_last_cm_date ;
1422 c_last_cm_days_since := l_last_cm_days_since ;
1423 c_last_cm_prev_trx := l_last_cm_prev_trx ;
1424 c_last_cm_id := l_last_cm_id ;
1425
1426 WHEN OTHERS THEN
1427 /*srw.message (1006 , 'Error in LAST_CREDIT_MEMO_FORMULA');*/null;
1428
1429 return (0);
1430 END ;
1431
1432 RETURN NULL; end;
1433
1434 function c_guarantee_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1435 begin
1436
1437 DECLARE
1438 l_last_guar_number VARCHAR2 (100);
1439 l_last_guar_type VARCHAR2 (100);
1440 l_last_guar_currency VARCHAR2 (20);
1441 l_last_guar_amount NUMBER ;
1442 l_last_guar_converted VARCHAR2 (1);
1443 l_last_guar_date VARCHAR2 (11);
1444 l_last_guar_days_since VARCHAR2 (11);
1445 l_dummy NUMBER (1);
1446
1447 CURSOR C_guar IS
1448 SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
1449 RA_CUST_TRX_TYPES.NAME,
1450 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
1451 AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
1452 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
1453 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1454 RA_CUSTOMER_TRX.TRX_DATE,
1455 ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
1456 FROM RA_CUST_TRX_TYPES,
1457 RA_CUSTOMER_TRX,
1458 AR_PAYMENT_SCHEDULES
1459 WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
1460 AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
1461 AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
1462 AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
1463 AND AR_PAYMENT_SCHEDULES.CLASS = 'GUAR'
1464 ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
1465 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
1466 ;
1467
1468 BEGIN
1469 c_last_guar_number := '' ;
1470 c_last_guar_type := '' ;
1471 c_last_guar_currency := '' ;
1472 c_last_guar_amount := l_dummy ;
1473 c_last_guar_converted := '' ;
1474 c_last_guar_date := '' ;
1475 c_last_guar_days_since := '' ;
1476
1477 /*srw.reference (customer_id);*/null;
1478
1479 /*srw.reference (site_use_id);*/null;
1480
1481
1482 OPEN C_guar ;
1483
1484 FETCH C_guar
1485 INTO
1486 l_last_guar_number ,
1487 l_last_guar_type ,
1488 l_last_guar_currency,
1489 l_last_guar_amount,
1490 l_last_guar_converted ,
1491 l_last_guar_date ,
1495 if l_last_guar_number is NOT NULL then
1492 l_last_guar_days_since
1493 ;
1494
1496 c_last_guar_number := l_last_guar_number ;
1497 c_last_guar_type := l_last_guar_type ;
1498 c_last_guar_currency := l_last_guar_currency ;
1499 c_last_guar_amount := l_last_guar_amount ;
1500 c_last_guar_converted := l_last_guar_converted ;
1501 c_last_guar_date := l_last_guar_date ;
1502 c_last_guar_days_since := l_last_guar_days_since ;
1503 else
1504 c_last_guar_number := rp_none ;
1505 end if ;
1506
1507 CLOSE C_guar ;
1508
1509
1510 return (0);
1511
1512 EXCEPTION WHEN NO_DATA_FOUND THEN
1513 c_last_guar_number := rp_none;
1514 c_last_guar_type := l_last_guar_type ;
1515 c_last_guar_currency := l_last_guar_currency ;
1516 c_last_guar_amount := l_last_guar_amount ;
1517 c_last_guar_converted := l_last_guar_converted ;
1518 c_last_guar_date := l_last_guar_date ;
1519 c_last_guar_days_since := l_last_guar_days_since ;
1520
1521 WHEN OTHERS THEN
1522 /*SRW.MESSAGE (1010,'Error in C_GUAR FORMULA');*/null;
1523
1524 return (0);
1525 END ;
1526
1527 RETURN NULL; end;
1528
1529 function c_last_deposit_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1530 begin
1531
1532 DECLARE
1533 l_last_dep_number VARCHAR2 (100);
1534 l_last_dep_type VARCHAR2 (100);
1535 l_last_dep_currency VARCHAR2 (20);
1536 l_last_dep_amount NUMBER ;
1537 l_last_dep_converted VARCHAR2 (1);
1538 l_last_dep_date VARCHAR2 (11);
1539 l_last_dep_days_since VARCHAR2 (11);
1540 l_dummy NUMBER (1);
1541
1542 CURSOR C_DEP IS
1543 SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
1544 RA_CUST_TRX_TYPES.NAME,
1545 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
1546 AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
1547 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
1548 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1549 RA_CUSTOMER_TRX.TRX_DATE,
1550 ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
1551 FROM RA_CUST_TRX_TYPES,
1552 RA_CUSTOMER_TRX,
1553 AR_PAYMENT_SCHEDULES
1554 WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
1555 AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
1556 AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
1557 AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
1558 AND AR_PAYMENT_SCHEDULES.CLASS = 'DEP'
1559 ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
1560 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
1561 ;
1562
1563 BEGIN
1564
1565 /*srw.reference (customer_id);*/null;
1566
1567 /*srw.reference (site_use_id);*/null;
1568
1569 c_last_dep_number := '' ;
1570 c_last_dep_type := '' ;
1571 c_last_dep_currency := '' ;
1572 c_last_dep_amount := l_dummy ;
1573 c_last_dep_converted := '' ;
1574 c_last_dep_date := '' ;
1575 c_last_dep_days_since := '' ;
1576
1577 OPEN C_DEP ;
1578
1579 FETCH C_DEP
1580 INTO
1581 l_last_dep_number ,
1582 l_last_dep_type ,
1583 l_last_dep_currency,
1584 l_last_dep_amount,
1585 l_last_dep_converted ,
1586 l_last_dep_date ,
1587 l_last_dep_days_since
1588 ;
1589 CLOSE C_DEP ;
1590
1591 if l_last_dep_number is NOT NULL then
1592 c_last_dep_number := l_last_dep_number ;
1593 c_last_dep_type := l_last_dep_type ;
1594 c_last_dep_currency := l_last_dep_currency ;
1595 c_last_dep_amount := l_last_dep_amount ;
1596 c_last_dep_converted := l_last_dep_converted ;
1597 c_last_dep_date := l_last_dep_date ;
1598 c_last_dep_days_since := l_last_dep_days_since ;
1599 else
1600 c_last_dep_number := rp_none ;
1601 end if ;
1602 return (0);
1603
1604 EXCEPTION WHEN NO_DATA_FOUND THEN
1605 c_last_dep_number := rp_none;
1606 c_last_dep_type := l_last_dep_type ;
1607 c_last_dep_currency := l_last_dep_currency ;
1608 c_last_dep_amount := l_last_dep_amount ;
1609 c_last_dep_converted := l_last_dep_converted ;
1610 c_last_dep_date := l_last_dep_date ;
1611 c_last_dep_days_since := l_last_dep_days_since ;
1612
1613 WHEN OTHERS THEN
1614 /*SRW.MESSAGE (1010,'Error in C_DEP FORMULA');*/null;
1615
1616 return (0);
1617 END ;
1618
1619 RETURN NULL; end;
1620
1621 function c_last_dm_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1622 begin
1623
1624 DECLARE
1625 l_last_dm_number VARCHAR2 (100);
1626 l_last_dm_type VARCHAR2 (100);
1627 l_last_dm_currency VARCHAR2 (20);
1628 l_last_dm_amount NUMBER ;
1629 l_last_dm_converted VARCHAR2 (1);
1630 l_last_dm_date VARCHAR2 (11);
1631 l_last_dm_days_since VARCHAR2 (11);
1632 l_dummy NUMBER (1);
1633
1634 CURSOR C_DM IS
1635 SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
1636 RA_CUST_TRX_TYPES.NAME,
1637 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
1638 AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
1639 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
1640 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1641 RA_CUSTOMER_TRX.TRX_DATE,
1642 ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
1643 FROM RA_CUST_TRX_TYPES,
1644 RA_CUSTOMER_TRX,
1645 AR_PAYMENT_SCHEDULES
1646 WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
1650 AND AR_PAYMENT_SCHEDULES.CLASS = 'DM'
1647 AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
1648 AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
1649 AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
1651 ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
1652 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
1653 ;
1654
1655 BEGIN
1656
1657
1658 c_last_dm_number := '' ;
1659 c_last_dm_type := '' ;
1660 c_last_dm_currency := '' ;
1661 c_last_dm_amount := l_dummy ;
1662 c_last_dm_converted := '' ;
1663 c_last_dm_date := '' ;
1664 c_last_dm_days_since := '' ;
1665 /*srw.reference (site_use_id);*/null;
1666
1667 /*srw.reference (customer_id);*/null;
1668
1669 OPEN C_DM ;
1670
1671 FETCH C_DM
1672 INTO
1673 l_last_dm_number ,
1674 l_last_dm_type ,
1675 l_last_dm_currency,
1676 l_last_dm_amount,
1677 l_last_dm_converted ,
1678 l_last_dm_date ,
1679 l_last_dm_days_since
1680 ;
1681
1682
1683 CLOSE C_DM ;
1684
1685 if l_last_dm_number is NOT NULL then
1686 c_last_dm_number := l_last_dm_number ;
1687 c_last_dm_type := l_last_dm_type ;
1688 c_last_dm_currency := l_last_dm_currency ;
1689 c_last_dm_amount := l_last_dm_amount ;
1690 c_last_dm_converted := l_last_dm_converted ;
1691 c_last_dm_date := l_last_dm_date ;
1692 c_last_dm_days_since := l_last_dm_days_since ;
1693 else
1694 c_last_dm_number := rp_none ;
1695 end if ;
1696
1697 return (0);
1698 EXCEPTION WHEN NO_DATA_FOUND THEN
1699 c_last_dm_number := rp_none;
1700 c_last_dm_type := l_last_dm_type ;
1701 c_last_dm_currency := l_last_dm_currency ;
1702 c_last_dm_amount := l_last_dm_amount ;
1703 c_last_dm_converted := l_last_dm_converted ;
1704 c_last_dm_date := l_last_dm_date ;
1705 c_last_dm_days_since := l_last_dm_days_since ;
1706
1707 WHEN OTHERS THEN
1708 /*SRW.MESSAGE (1010,'Error in C_DM FORMULA');*/null;
1709
1710 return (0);
1711 END ;
1712
1713 RETURN NULL; end;
1714
1715 function c_last_cb_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1716 begin
1717
1718 DECLARE
1719 l_last_cb_number VARCHAR2 (100);
1720 l_last_cb_type VARCHAR2 (100);
1721 l_last_cb_currency VARCHAR2 (20);
1722 l_last_cb_amount NUMBER ;
1723 l_last_cb_converted VARCHAR2 (1);
1724 l_last_cb_date VARCHAR2 (11);
1725 l_last_cb_days_since VARCHAR2 (11);
1726 l_dummy NUMBER (1);
1727
1728 CURSOR C_CB IS
1729 SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
1730 RA_CUST_TRX_TYPES.NAME,
1731 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
1732 AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
1733 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
1734 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1735 RA_CUSTOMER_TRX.TRX_DATE,
1736 ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
1737 FROM RA_CUST_TRX_TYPES,
1738 RA_CUSTOMER_TRX,
1739 AR_PAYMENT_SCHEDULES
1740 WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
1741 AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
1742 AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
1743 AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
1744 AND AR_PAYMENT_SCHEDULES.CLASS = 'CB'
1745 ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
1746 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
1747 ;
1748
1749 BEGIN
1750
1751 /*srw.reference (customer_id);*/null;
1752
1753 /*srw.reference (site_use_id);*/null;
1754
1755 c_last_cb_number := '' ;
1756 c_last_cb_type := '' ;
1757 c_last_cb_currency := '' ;
1758 c_last_cb_amount := l_dummy ;
1759 c_last_cb_converted := '' ;
1760 c_last_cb_date := '' ;
1761 c_last_cb_days_since := '' ;
1762
1763 OPEN C_CB ;
1764
1765 FETCH C_CB
1766 INTO
1767 l_last_cb_number ,
1768 l_last_cb_type ,
1769 l_last_cb_currency,
1770 l_last_cb_amount,
1771 l_last_cb_converted ,
1772 l_last_cb_date ,
1773 l_last_cb_days_since
1774 ;
1775 CLOSE C_CB ;
1776 if l_last_cb_number is NOT NULL then
1777 c_last_cb_number := l_last_cb_number ;
1778 c_last_cb_type := l_last_cb_type ;
1779 c_last_cb_currency := l_last_cb_currency ;
1780 c_last_cb_amount := l_last_cb_amount ;
1781 c_last_cb_converted := l_last_cb_converted ;
1782 c_last_cb_date := l_last_cb_date ;
1783 c_last_cb_days_since := l_last_cb_days_since ;
1784 else
1785 c_last_cb_number := rp_none ;
1786 end if ;
1787
1788 return (0);
1789 EXCEPTION WHEN NO_DATA_FOUND THEN
1790 c_last_cb_number := rp_none;
1791 c_last_cb_type := l_last_cb_type ;
1792 c_last_cb_currency := l_last_cb_currency ;
1793 c_last_cb_amount := l_last_cb_amount ;
1794 c_last_cb_converted := l_last_cb_converted ;
1795 c_last_cb_date := l_last_cb_date ;
1796 c_last_cb_days_since := l_last_cb_days_since ;
1797
1798 WHEN OTHERS THEN
1799 /*SRW.MESSAGE (1010,'Error in C_CB FORMULA');*/null;
1800
1801 return (0);
1802 END ;
1803
1804 RETURN NULL; end;
1805
1806 function c_last_payment_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1807 begin
1808
1809 DECLARE
1810
1814 l_last_payment_amount NUMBER ;
1811 l_last_payment_number VARCHAR2 (100);
1812 l_last_payment_type VARCHAR2 (100);
1813 l_last_payment_currency VARCHAR2 (20);
1815 l_last_payment_converted VARCHAR2 (1);
1816 l_last_payment_date VARCHAR2 (11);
1817 l_last_payment_days_since VARCHAR2 (11);
1818 l_last_payment_rel_invoice VARCHAR2 (100);
1819 l_dummy NUMBER (1);
1820
1821 CURSOR C_PAYMENT IS
1822 SELECT AR_CASH_RECEIPTS.RECEIPT_NUMBER,
1823 AR_LOOKUPS.MEANING,
1824 AR_CASH_RECEIPTS.CURRENCY_CODE,
1825 AR_CASH_RECEIPTS.AMOUNT,
1826 DECODE(AR_CASH_RECEIPTS.CURRENCY_CODE, functional_currency, ' ',
1827 DECODE(AR_CASH_RECEIPTS.EXCHANGE_RATE, NULL, '*', ' ')),
1828 CRH.GL_DATE,
1829 ROUND(TRUNC(SYSDATE) - CRH.GL_DATE),
1830 RA_CUSTOMER_TRX.TRX_NUMBER
1831 FROM AR_LOOKUPS,
1832 AR_CASH_RECEIPTS,
1833 AR_CASH_RECEIPT_HISTORY CRH,
1834 AR_RECEIVABLE_APPLICATIONS,
1835 RA_CUSTOMER_TRX
1836 WHERE NVL(AR_CASH_RECEIPTS.TYPE, 'CASH') = AR_LOOKUPS.LOOKUP_CODE
1837 AND AR_LOOKUPS.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
1838 AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID
1839 AND AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID = site_use_id
1840 AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID =
1841 AR_RECEIVABLE_APPLICATIONS.CASH_RECEIPT_ID
1842 AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
1843 AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
1844 AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =
1845 RA_CUSTOMER_TRX.CUSTOMER_TRX_ID (+)
1846 ORDER BY AR_CASH_RECEIPTS.CREATION_DATE DESC,
1847 AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC,
1848 AR_RECEIVABLE_APPLICATIONS.CREATION_DATE DESC
1849 ;
1850
1851 BEGIN
1852
1853 /*srw.reference (customer_id);*/null;
1854
1855 /*srw.reference (site_use_id);*/null;
1856
1857 c_last_payment_number := '' ;
1858 c_last_payment_type := '' ;
1859 c_last_payment_currency := '' ;
1860 c_last_payment_amount := l_dummy ;
1861 c_last_payment_converted := '' ;
1862 c_last_payment_date := '' ;
1863 c_last_payment_days_since := '' ;
1864 c_last_payment_rel_invoice := '' ;
1865
1866 OPEN C_PAYMENT ;
1867
1868 FETCH C_PAYMENT INTO
1869 l_last_payment_number ,
1870 l_last_payment_type,
1871 l_last_payment_currency ,
1872 l_last_payment_amount,
1873 l_last_payment_converted,
1874 l_last_payment_date ,
1875 l_last_payment_days_since ,
1876 l_last_payment_rel_invoice
1877 ;
1878 CLOSE C_PAYMENT ;
1879
1880 if l_last_payment_number is NOT NULL then
1881
1882 c_last_payment_number := l_last_payment_number ;
1883 c_last_payment_type := l_last_payment_type ;
1884 c_last_payment_currency := l_last_payment_currency ;
1885 c_last_payment_amount := l_last_payment_amount ;
1886 c_last_payment_converted := l_last_payment_converted ;
1887 c_last_payment_date := l_last_payment_date ;
1888 c_last_payment_days_since := l_last_payment_days_since ;
1889 c_last_payment_rel_invoice := l_last_payment_rel_invoice ;
1890
1891 else
1892
1893 c_last_payment_number := rp_none ;
1894 end if ;
1895
1896 return (0);
1897
1898 EXCEPTION WHEN NO_DATA_FOUND THEN
1899 c_last_payment_number := rp_none ;
1900 c_last_payment_type := l_last_payment_type ;
1901 c_last_payment_currency := l_last_payment_currency ;
1902 c_last_payment_amount := l_last_payment_amount ;
1903 c_last_payment_converted := l_last_payment_converted ;
1904 c_last_payment_date := l_last_payment_date ;
1905 c_last_payment_days_since := l_last_payment_days_since ;
1906 c_last_payment_rel_invoice := l_last_payment_rel_invoice ;
1907
1908 return (0);
1909
1910 END ;
1911
1912 RETURN NULL; end;
1913
1914 function c_last_adj_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
1915 begin
1916
1917 DECLARE
1918 l_last_adj_type VARCHAR2 (100);
1919 l_last_adj_rel_invoice VARCHAR2 (100);
1920 l_last_adj_currency VARCHAR2 (20);
1921 l_last_adj_amount NUMBER ;
1922 l_last_adj_converted VARCHAR2 (1);
1923 l_last_adj_date VARCHAR2 (11);
1924 l_last_adj_days_since VARCHAR2 (11);
1925 l_dummy NUMBER (1);
1926
1927 CURSOR C_ADJUSTMENT IS
1928 SELECT LK.MEANING,
1929 AR_PAYMENT_SCHEDULES.TRX_NUMBER,
1930 ar_payment_schedules.invoice_currency_code,
1931 AR_ADJUSTMENTS.AMOUNT,
1932 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
1933 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
1934 AR_ADJUSTMENTS.APPLY_DATE,
1935 ROUND(TRUNC(SYSDATE) - AR_ADJUSTMENTS.APPLY_DATE)
1936 FROM AR_ADJUSTMENTS,
1937 AR_LOOKUPS LK,
1938 AR_PAYMENT_SCHEDULES
1939 WHERE AR_ADJUSTMENTS.REASON_CODE = LK.LOOKUP_CODE (+)
1940 AND LK.LOOKUP_TYPE = 'ADJUST_REASON'
1941 AND AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID =
1942 AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID
1943 AND NVL(AR_ADJUSTMENTS.POSTABLE, 'Y') = 'Y'
1944 AND AR_PAYMENT_SCHEDULES.CUSTOMER_ID = CUSTOMER_ID
1945 AND AR_PAYMENT_SCHEDULES.CUSTOMER_SITE_USE_ID = site_use_id
1946 ORDER BY AR_ADJUSTMENTS.CREATION_DATE DESC,
1947 AR_ADJUSTMENTS.ADJUSTMENT_ID DESC
1948 ;
1949
1950
1951 BEGIN
1952
1953 /*srw.reference (customer_id);*/null;
1954
1955 /*srw.reference (site_use_id);*/null;
1956
1957 c_last_adj_number := '' ;
1958 c_last_adj_type := '' ;
1959 c_last_adj_rel_invoice := '' ;
1960 c_last_adj_currency := '' ;
1961 c_last_adj_amount := l_dummy ;
1962 c_last_adj_converted := '' ;
1963 c_last_adj_date := '' ;
1964 c_last_adj_days_since := '' ;
1965
1966 OPEN C_ADJUSTMENT ;
1967
1968 FETCH C_ADJUSTMENT INTO
1969 l_last_adj_type ,
1970 l_last_adj_rel_invoice ,
1971 l_last_adj_currency ,
1972 l_last_adj_amount ,
1973 l_last_adj_converted ,
1974 l_last_adj_date ,
1975 l_last_adj_days_since
1976 ;
1977
1978 if l_last_adj_amount is NOT NULL then
1979
1980 c_last_adj_number := rp_na_upper ;
1981 c_last_adj_type := l_last_adj_type ;
1982 c_last_adj_rel_invoice := l_last_adj_rel_invoice ;
1983 c_last_adj_currency := l_last_adj_currency ;
1984 c_last_adj_amount := l_last_adj_amount ;
1985 c_last_adj_converted := l_last_adj_converted ;
1986 c_last_adj_date := l_last_adj_date ;
1987 c_last_adj_days_since := l_last_adj_days_since ;
1988 else
1989 c_last_adj_number := rp_none ;
1990 end if ;
1991
1992 return (0);
1993
1994 EXCEPTION WHEN NO_DATA_FOUND THEN
1995 c_last_adj_number := rp_none ;
1996 c_last_adj_type := l_last_adj_type ;
1997 c_last_adj_rel_invoice := l_last_adj_rel_invoice ;
1998 c_last_adj_currency := l_last_adj_currency ;
1999 c_last_adj_amount := l_last_adj_amount ;
2000 c_last_adj_converted := l_last_adj_converted ;
2001 c_last_adj_date := l_last_adj_date ;
2002 c_last_adj_days_since := l_last_adj_days_since ;
2003
2004 return (0);
2005
2006 END ;
2007
2008
2009 RETURN NULL; end;
2010
2011 function c_last_writeoff_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
2012 begin
2013
2014 DECLARE
2015 l_last_wo_type VARCHAR2 (100);
2016 l_last_wo_rel_invoice VARCHAR2 (100);
2017 l_last_wo_currency VARCHAR2 (100);
2018 l_last_wo_amount NUMBER ;
2019 l_last_wo_converted VARCHAR2 (1);
2020 l_last_wo_date VARCHAR2 (11);
2021 l_last_wo_days_since VARCHAR2 (11);
2022 l_dummy NUMBER (1);
2023
2024 CURSOR C_WRITEOFF IS
2025 SELECT LK.LOOKUP_CODE,
2026 AR_PAYMENT_SCHEDULES.TRX_NUMBER,
2027 AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
2028 AR_ADJUSTMENTS.AMOUNT,
2029 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
2030 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
2031 AR_ADJUSTMENTS.APPLY_DATE,
2032 ROUND(TRUNC(SYSDATE) - AR_ADJUSTMENTS.APPLY_DATE)
2033 FROM AR_ADJUSTMENTS,
2034 AR_LOOKUPS LK,
2035 AR_PAYMENT_SCHEDULES
2036 WHERE AR_ADJUSTMENTS.REASON_CODE
2037 = LK.LOOKUP_CODE(+)
2038 AND AR_ADJUSTMENTS.CUSTOMER_TRX_ID = AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID
2039 AND NVL(AR_ADJUSTMENTS.POSTABLE, 'Y') = 'Y'
2040 AND LK.LOOKUP_CODE(+) = 'WRITE OFF'
2041 AND LK.LOOKUP_TYPE(+) = 'ADJUST_REASON'
2042 AND AR_PAYMENT_SCHEDULES.CUSTOMER_ID = CUSTOMER_ID
2043 AND AR_PAYMENT_SCHEDULES.CUSTOMER_SITE_USE_ID = site_use_id
2044 ORDER BY AR_ADJUSTMENTS.CREATION_DATE DESC,
2045 AR_ADJUSTMENTS.ADJUSTMENT_ID DESC
2046 ;
2047
2048 BEGIN
2049
2050
2051 /*srw.reference (customer_id);*/null;
2052
2053 /*srw.reference (site_use_id);*/null;
2054
2055 c_last_wo_number := '' ;
2059 c_last_wo_amount := l_dummy ;
2056 c_last_wo_type := '' ;
2057 c_last_wo_rel_invoice := '' ;
2058 c_last_wo_currency := '' ;
2060 c_last_wo_converted := '' ;
2061 c_last_wo_date := '' ;
2062 c_last_wo_days_since := '' ;
2063
2064 OPEN C_WRITEOFF ;
2065
2066
2067 FETCH C_WRITEOFF INTO
2068 l_last_wo_type ,
2069 l_last_wo_rel_invoice ,
2070 l_last_wo_currency,
2071 l_last_wo_amount ,
2072 l_last_wo_converted ,
2073 l_last_wo_date ,
2074 l_last_wo_days_since
2075 ;
2076
2077 if l_last_wo_amount is NOT NULL THEN
2078 c_last_wo_number := rp_na_upper ;
2079 c_last_wo_type := l_last_wo_type ;
2080 c_last_wo_rel_invoice := l_last_wo_rel_invoice ;
2081 c_last_wo_currency := l_last_wo_currency ;
2082 c_last_wo_amount := l_last_wo_amount ;
2083 c_last_wo_converted := l_last_wo_converted ;
2084 c_last_wo_date := l_last_wo_date ;
2085 c_last_wo_days_since := l_last_wo_days_since ;
2086 else
2087 c_last_wo_number := rp_none ;
2088 end if ;
2089
2090 CLOSE C_WRITEOFF ;
2091 return (0);
2092
2093 EXCEPTION WHEN NO_DATA_FOUND THEN
2094 c_last_wo_number := rp_none ;
2095 c_last_wo_type := l_last_wo_type ;
2096 c_last_wo_rel_invoice := l_last_wo_rel_invoice ;
2097 c_last_wo_currency := l_last_wo_currency ;
2098 c_last_wo_amount := l_last_wo_amount ;
2099 c_last_wo_converted := l_last_wo_converted ;
2100 c_last_wo_date := l_last_wo_date ;
2101 c_last_wo_days_since := l_last_wo_days_since ;
2102 return (0);
2103 WHEN OTHERS THEN
2104 /*srw.message (1020,' Error in Write off Formula ');*/null;
2105
2106 return (0);
2107
2108 END ;
2109
2110
2111
2112
2113 RETURN NULL; end;
2114
2115 function c_last_statement_formulaformul(CUSTOMER_ID in number, site_use_id in number) return number is
2116 begin
2117
2118 DECLARE
2119
2120 l_last_st_type VARCHAR2 (100);
2121 l_last_st_date VARCHAR2 (11);
2122 l_last_st_days_since VARCHAR2 (100);
2123
2124 CURSOR C_STATEMENT IS
2125 SELECT AR_STATEMENT_CYCLES.NAME,
2126 AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2127 TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2128 FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2129 AR_STATEMENT_CYCLE_DATES
2130 WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2131 AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2132 AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2133 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2134 AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2135 AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2136 AND HZ_CUSTOMER_PROFILES.site_use_id = site_use_id
2137 ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2138 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC ;
2139
2140 CURSOR C_STATEMENT_DEFAULT IS
2141 SELECT AR_STATEMENT_CYCLES.NAME,
2142 AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2143 TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2144 FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2145 AR_STATEMENT_CYCLE_DATES
2146 WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2147 AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2148 AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2149 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2150 AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2151 AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2152 AND HZ_CUSTOMER_PROFILES.site_use_id is null
2153 ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2154 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC
2155 ;
2156
2157 BEGIN
2158
2159 /*srw.reference (customer_id );*/null;
2160
2161 /*srw.reference (site_use_id);*/null;
2162
2163 c_last_st_number := '' ;
2164 c_last_st_type := '';
2165 c_last_st_date := '';
2166 c_last_st_days_since := '' ;
2167 c_last_stmnt_next_trx_date := '';
2168 if c_profile_site_use_id is NOT NULL then
2169
2170 OPEN C_STATEMENT ;
2171
2172 FETCH C_STATEMENT
2173 INTO
2174 l_last_st_type ,
2175 l_last_st_date ,
2176 l_last_st_days_since
2177 ;
2178 CLOSE C_STATEMENT ;
2179 else
2180 OPEN C_STATEMENT_DEFAULT ;
2181
2182 FETCH C_STATEMENT_DEFAULT
2183 INTO
2184 l_last_st_type ,
2185 l_last_st_date ,
2186 l_last_st_days_since
2187 ;
2188 CLOSE C_STATEMENT_DEFAULT ;
2189 end if ;
2190
2191 if l_last_st_date is NOT NULL then
2192 c_last_st_number := rp_na_upper ;
2193 c_last_st_type := l_last_st_type ;
2194 c_last_st_date := l_last_st_date;
2195 c_last_st_days_since := l_last_st_days_since ;
2196
2197 DECLARE
2198 l_last_stmnt_next_trx_date VARCHAR2 (11);
2199
2200 BEGIN
2201 SELECT MIN(AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2202 INTO l_last_stmnt_next_trx_date
2203 FROM HZ_CUSTOMER_PROFILES,
2204 AR_STATEMENT_CYCLE_DATES
2205 WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2206 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2207 AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
2208 AND AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE > to_date(c_last_st_date,'DD-MM-YYYY')
2209 AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2210 AND HZ_CUSTOMER_PROFILES.SITE_USE_ID IS NULL
2211 ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2212 AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID
2213 ;
2214
2215 c_last_stmnt_next_trx_date := nvl(l_last_stmnt_next_trx_date,rp_none) ;
2216 EXCEPTION WHEN NO_DATA_FOUND THEN
2220 c_last_st_number := rp_none ;
2217 c_last_stmnt_next_trx_date := rp_none;
2218 END ;
2219 else
2221 end if ;
2222
2223 return (0);
2224
2225 EXCEPTION WHEN NO_DATA_FOUND THEN
2226
2227 c_last_st_number := rp_none ;
2228 c_last_st_type := l_last_st_type ;
2229 c_last_st_date := l_last_st_date;
2230 c_last_st_days_since := l_last_st_days_since ;
2231
2232 return (0);
2233 WHEN OTHERS THEN
2234 /*SRW.MESSAGE (10023,' Error in STATEMENT Formula');*/null;
2235
2236 raise;
2237 END ;
2238
2239
2240 RETURN NULL; end;
2241
2242 --function c_last_dn_formulaformula(CUSTOMER_ID in number, site_use_id_1 in number) return number is
2243 function c_last_dn_formulaformula(CUSTOMER_ID_1 in number, site_use_id_1 in number) return number is
2244 begin
2245
2246 DECLARE
2247 l_last_dn_amount NUMBER ;
2248 l_last_dn_type VARCHAR2 (100);
2249 l_last_dn_currency VARCHAR2 (20);
2250 l_last_dn_date VARCHAR2 (11);
2251 l_last_dn_days_since VARCHAR2 (11);
2252 l_dummy NUMBER (1);
2253 l_iex_creation_date VARCHAR2 (11);
2254 l_iex_last_dn_days_since VARCHAR2 (11);
2255
2256 CURSOR IEX_DUNNING IS
2257 SELECT MAX(creation_date),
2258 TRUNC(SYSDATE) - MAX(creation_date)
2259 FROM iex_dunnings
2260 --WHERE (dunning_level = 'ACCOUNT' AND dunning_object_id = CUSTOMER_ID)
2261 WHERE (dunning_level = 'ACCOUNT' AND dunning_object_id = CUSTOMER_ID_1)
2262 --OR (dunning_level = 'BILL_TO' AND dunning_object_id = site_use_id);
2263 OR (dunning_level = 'BILL_TO' AND dunning_object_id = site_use_id_1);
2264
2265
2266 CURSOR C_DUNNING IS
2267 SELECT ROUND(SUM(CPS.AMOUNT_DUE_REMAINING + CPS.AMOUNT_ACCRUE +
2268 CPS.AMOUNT_UNACCRUE), 2),
2269 CORR.CORRESPONDENCE_TYPE,
2270 ps.invoice_currency_code,
2271 MAX(CORR.CORRESPONDENCE_DATE),
2272 TRUNC(SYSDATE) - MAX(CORR.CORRESPONDENCE_DATE)
2273 FROM ar_payment_schedules ps,
2274 ar_correspondence_pay_sched cps,
2275 AR_DUNNING_LETTERS DUNN,
2276 AR_CORRESPONDENCES CORR
2277 WHERE CORR.REFERENCE1 = DUNN.DUNNING_LETTER_ID
2278 AND CORR.CORRESPONDENCE_ID = CPS.CORRESPONDENCE_ID
2279 --and CORR.site_use_id = site_use_id
2280 and CORR.site_use_id = site_use_id_1
2281 and cps.payment_schedule_id = ps.payment_schedule_id
2282 AND CORR.CORRESPONDENCE_TYPE = 'DUNNING'
2283 --AND CORR.CUSTOMER_ID = CUSTOMER_ID
2284 AND CORR.CUSTOMER_ID = CUSTOMER_ID_1
2285 GROUP BY ps.invoice_currency_code, CORR.CORRESPONDENCE_TYPE,
2286 CPS.AMOUNT_DUE_REMAINING,
2287 CPS.AMOUNT_ACCRUE, DUNN.LETTER_NAME, CORR.CORRESPONDENCE_DATE
2288 ORDER BY CORR.CORRESPONDENCE_DATE DESC ;
2289
2290 BEGIN
2291
2292 /*srw.reference (customer_id);*/null;
2293
2294 /*srw.reference (site_use_id);*/null;
2295
2296
2297 c_last_dn_number := '' ;
2298 c_last_dn_amount := l_dummy ;
2299 c_last_dn_type := '' ;
2300 c_last_dn_currency := '' ;
2301 c_last_dn_date := '' ;
2302 c_last_dn_days_since := '' ;
2303
2304 OPEN IEX_DUNNING;
2305
2306 FETCH IEX_DUNNING INTO
2307 l_iex_creation_date,
2308 l_iex_last_dn_days_since;
2309 CLOSE IEX_DUNNING;
2310
2311 IF l_iex_creation_date IS NOT NULL then
2312 c_last_dn_number := rp_na_upper;
2313 c_last_dn_amount := null;
2314 c_last_dn_type := 'DUNNING';
2315 c_last_dn_currency := null;
2316 c_last_dn_date := l_iex_creation_date;
2317 c_last_dn_days_since := l_iex_last_dn_days_since;
2318
2319 ELSE
2320
2321 OPEN C_DUNNING ;
2322
2323 FETCH C_DUNNING INTO
2324 l_last_dn_amount ,
2325 l_last_dn_type,
2326 l_last_dn_currency ,
2327 l_last_dn_date ,
2328 l_last_dn_days_since ;
2329 CLOSE C_DUNNING ;
2330
2331 if l_last_dn_type is NOT NULL then
2332
2333 c_last_dn_number := rp_na_upper ;
2334 c_last_dn_amount := l_last_dn_amount ;
2335 c_last_dn_type := l_last_dn_type ;
2336 c_last_dn_currency := l_last_dn_currency ;
2337 c_last_dn_date := l_last_dn_date ;
2338 c_last_dn_days_since := l_last_dn_days_since ;
2339 else
2340
2341 c_last_dn_number := rp_none ;
2342 end if ;
2343 END IF;
2344 return (0);
2345
2346 EXCEPTION WHEN NO_DATA_FOUND THEN
2347
2348 c_last_dn_number := rp_none ;
2349 c_last_dn_amount := l_last_dn_amount ;
2350 c_last_dn_type := l_last_dn_type ;
2351 c_last_dn_currency := l_last_dn_currency ;
2352 c_last_dn_date := l_last_dn_date ;
2353 c_last_dn_days_since := l_last_dn_days_since ;
2354 return (0);
2355
2356 WHEN OTHERS THEN
2357 /*srw.message (10000,'Error in Dunning formula column');*/null;
2358
2359 return (0);
2360
2361 END ;
2362
2363 RETURN NULL; end;
2364
2365 function c_last_nsf_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in number) return number is
2366 begin
2367
2368 DECLARE
2369 l_last_nsf_number VARCHAR2 (100);
2370 l_last_nsf_type VARCHAR2 (100);
2371 l_last_nsf_currency VARCHAR2 (20);
2372 l_last_nsf_amount NUMBER ;
2373 l_last_nsf_converted VARCHAR2 (1);
2374 l_last_nsf_date VARCHAR2 (11);
2375 l_last_nsf_days_since VARCHAR2 (11);
2376 l_dummy NUMBER (1);
2377
2378 CURSOR C_NSF IS
2379 SELECT AR_CASH_RECEIPTS.RECEIPT_NUMBER,
2380 AR_CASH_RECEIPTS.STATUS,
2381 currency_code,
2382 AR_CASH_RECEIPTS.AMOUNT,
2383 DECODE(AR_CASH_RECEIPTS.CURRENCY_CODE,functional_currency, ' ',
2384 DECODE(AR_CASH_RECEIPTS.EXCHANGE_RATE, NULL, '*', ' ')),
2388 WHERE AR_CASH_RECEIPTS.STATUS IN ('NSF','STOP')
2385 AR_CASH_RECEIPTS.REVERSAL_DATE,
2386 ROUND(TRUNC(SYSDATE) - AR_CASH_RECEIPTS.REVERSAL_DATE)
2387 FROM AR_CASH_RECEIPTS
2389 AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID
2390 AND AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID = site_use_id
2391 ORDER BY AR_CASH_RECEIPTS.REVERSAL_DATE DESC,
2392 AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC
2393 ;
2394
2395 BEGIN
2396
2397
2398 c_last_nsf_number := '' ;
2399 c_last_nsf_type := '' ;
2400 c_last_nsf_currency := '' ;
2401 c_last_nsf_amount := l_dummy ;
2402 c_last_nsf_converted := '' ;
2403 c_last_nsf_date := '' ;
2404 c_last_nsf_days_since := '' ;
2405 /*srw.reference (site_use_id);*/null;
2406
2407 /*srw.reference (customer_id);*/null;
2408
2409 /*srw.message (500, 'DEBUG: Customer id: ' || to_char(customer_id));*/null;
2410
2411 /*srw.message (500, 'DEBUG: Site Use id: ' || to_char(site_use_id));*/null;
2412
2413 OPEN C_NSF ;
2414
2415 FETCH C_NSF
2416 INTO
2417 l_last_nsf_number ,
2418 l_last_nsf_type ,
2419 l_last_nsf_currency,
2420 l_last_nsf_amount,
2421 l_last_nsf_converted ,
2422 l_last_nsf_date ,
2423 l_last_nsf_days_since
2424 ;
2425
2426
2427 CLOSE C_NSF ;
2428
2429 if l_last_nsf_number is NOT NULL then
2430 c_last_nsf_number := l_last_nsf_number ;
2431 c_last_nsf_type := l_last_nsf_type ;
2432 c_last_nsf_currency := l_last_nsf_currency ;
2433 c_last_nsf_amount := l_last_nsf_amount ;
2434 c_last_nsf_converted := l_last_nsf_converted ;
2435 c_last_nsf_date := l_last_nsf_date ;
2436 c_last_nsf_days_since := l_last_nsf_days_since ;
2437 else
2438 c_last_nsf_number := rp_none ;
2439 end if ;
2440
2441 return (0);
2442 EXCEPTION WHEN NO_DATA_FOUND THEN
2443 c_last_nsf_number := rp_none;
2444 c_last_nsf_type := l_last_nsf_type ;
2445 c_last_nsf_currency := l_last_nsf_currency ;
2446 c_last_nsf_amount := l_last_nsf_amount ;
2447 c_last_nsf_converted := l_last_nsf_converted ;
2448 c_last_nsf_date := l_last_nsf_date ;
2449 c_last_nsf_days_since := l_last_nsf_days_since ;
2450
2451 WHEN OTHERS THEN
2452 /*SRW.MESSAGE (1010,'Error in C_NSF FORMULA');*/null;
2453
2454 return (0);
2455 END ;
2456
2457 RETURN NULL; end;
2458
2459 --function c_last_contact_formulaformula(functional_currency in varchar2, CUSTOMER_ID in number, site_use_id in --number) return number is
2460 function c_last_contact_formulaformula(functional_currency in varchar2, CUSTOMER_ID_1 in number, site_use_id_1 in number) return number is
2461 begin
2462
2463 DECLARE
2464 l_last_contact_number VARCHAR2 (100);
2465 l_last_contact_rel_invoice VARCHAR2 (100);
2466 l_last_contact_currency VARCHAR2 (20);
2467 l_last_contact_amount NUMBER ;
2468 l_last_contact_converted VARCHAR2 (1);
2469 l_last_contact_date VARCHAR2 (11);
2470 l_last_contact_days_since VARCHAR2 (11);
2471 l_dummy NUMBER (1);
2472
2473 CURSOR C_CONTACT IS
2474
2475 SELECT cont_point.phone_area_code||'-' ||
2476 RTRIM(RPAD(decode(cont_point.contact_point_type,
2477 'TLX', cont_point.telex_number,
2478 cont_point.phone_number),15)),
2479 AR_PAYMENT_SCHEDULES.TRX_NUMBER,
2480 AR_PAYMENT_SCHEDULES.invoice_currency_code,
2481 AR_CALL_ACTIONS.ACTION_AMOUNT,
2482 DECODE(AR_CALL_ACTIONS.ACTION_AMOUNT, NULL, NULL,
2483 DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
2484 DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' '))),
2485 AR_CUSTOMER_CALL_TOPICS.CALL_DATE,
2486 ROUND(TRUNC(SYSDATE) - AR_CUSTOMER_CALL_TOPICS.CALL_DATE)
2487 FROM hz_contact_points cont_point,
2488 hz_cust_account_roles car, AR_LOOKUPS LKUPS,
2489 AR_PAYMENT_SCHEDULES, AR_CUSTOMER_CALL_TOPICS, AR_CALL_ACTIONS
2490 --WHERE AR_CUSTOMER_CALL_TOPICS.CUSTOMER_ID = CUSTOMER_ID
2491 WHERE AR_CUSTOMER_CALL_TOPICS.CUSTOMER_ID = CUSTOMER_ID_1
2492 --and AR_CUSTOMER_CALL_TOPICS.site_use_id(+) = site_use_id
2493 and AR_CUSTOMER_CALL_TOPICS.site_use_id(+) = site_use_id_1
2494 AND AR_CUSTOMER_CALL_TOPICS.PHONE_ID = cont_point.contact_point_id
2495 AND AR_CUSTOMER_CALL_TOPICS.CONTACT_ID = car.cust_account_role_id
2496 AND car.party_id = cont_point.owner_table_id
2497 AND cont_point.owner_table_name = 'HZ_PARTIES'
2498 AND cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
2499 AND AR_CUSTOMER_CALL_TOPICS.CUSTOMER_TRX_ID =
2500 AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID (+)
2501 AND AR_CUSTOMER_CALL_TOPICS.CUSTOMER_CALL_TOPIC_ID =
2502 AR_CALL_ACTIONS.CUSTOMER_CALL_TOPIC_ID (+)
2503 AND AR_CUSTOMER_CALL_TOPICS.FOLLOW_UP_ACTION =
2504 LKUPS.LOOKUP_CODE (+)
2505 ORDER BY AR_CUSTOMER_CALL_TOPICS.CALL_DATE DESC,
2506 AR_CUSTOMER_CALL_TOPICS.CUSTOMER_CALL_ID DESC
2507 ;
2508
2509 BEGIN
2510
2511
2512 c_last_contact_number := '' ;
2513 c_last_contact_rel_invoice := '' ;
2514 c_last_contact_currency := '' ;
2515 c_last_contact_amount := l_dummy ;
2516 c_last_contact_converted := '' ;
2517 c_last_contact_date := '' ;
2518 c_last_contact_days_since := '' ;
2519 /*srw.reference (site_use_id);*/null;
2520
2521 /*srw.reference (customer_id);*/null;
2522
2523 OPEN C_CONTACT ;
2524
2525 FETCH C_CONTACT
2526 INTO
2527 l_last_contact_number ,
2528 l_last_contact_rel_invoice ,
2529 l_last_contact_currency,
2530 l_last_contact_amount,
2531 l_last_contact_converted ,
2532 l_last_contact_date ,
2533 l_last_contact_days_since
2537 CLOSE C_CONTACT ;
2534 ;
2535
2536
2538
2539 if l_last_contact_date is NOT NULL then
2540 c_last_contact_number := l_last_contact_number ;
2541 c_last_contact_rel_invoice := l_last_contact_rel_invoice ;
2542 if l_last_contact_amount is not null and l_last_contact_currency is null then
2543 c_last_contact_currency := functional_currency;
2544 else
2545 c_last_contact_currency := l_last_contact_currency ;
2546 end if;
2547 c_last_contact_amount := l_last_contact_amount ;
2548 c_last_contact_converted := l_last_contact_converted ;
2549 c_last_contact_date := l_last_contact_date ;
2550 c_last_contact_days_since := l_last_contact_days_since ;
2551 else
2552 c_last_contact_number := rp_none ;
2553 end if ;
2554
2555 return (0);
2556 EXCEPTION WHEN NO_DATA_FOUND THEN
2557 c_last_contact_number := rp_none;
2558 c_last_contact_rel_invoice := l_last_contact_rel_invoice ;
2559 c_last_contact_currency := l_last_contact_currency ;
2560 c_last_contact_amount := l_last_contact_amount ;
2561 c_last_contact_converted := l_last_contact_converted ;
2562 c_last_contact_date := l_last_contact_date ;
2563 c_last_contact_days_since := l_last_contact_days_since ;
2564
2565 WHEN OTHERS THEN
2566 /*SRW.MESSAGE (1010,'Error in C_CONTACT FORMULA');*/null;
2567
2568 return (0);
2569 END ;
2570
2571 RETURN NULL; end;
2572
2573 --function c_last_hold_formulaformula(CUSTOMER_ID in number, site_use_id in number) return number is
2574 function c_last_hold_formulaformula(CUSTOMER_ID_1 in number, site_use_id_1 in number) return number is
2575 begin
2576
2577 DECLARE
2578 l_last_hold_number VARCHAR2 (100);
2579 l_last_hold_amount NUMBER ;
2580 l_last_hold_date VARCHAR2 (11);
2581 l_last_hold_days_since VARCHAR2 (11);
2582 l_dummy NUMBER (1);
2583
2584 CURSOR C_HOLD IS
2585
2586 SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
2587 AR_CREDIT_HISTORIES.HOLD_DATE,
2588 ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589
2590 FROM AR_CREDIT_HISTORIES
2591 --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592 --and (ar_credit_histories.site_use_id = site_use_id
2593 WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594 and (ar_credit_histories.site_use_id = site_use_id_1
2595 or
2596 site_use_id_1 is null
2597 or
2598 ( ar_credit_histories.site_use_id is null
2599 and not exists (select 1
2600 from ar_credit_histories h2
2601 -- where h2.site_use_id = site_use_id
2602 where h2.site_use_id = site_use_id_1
2603 -- and h2.customer_id =
2604 -- customer_id
2605 and h2.customer_id =
2606 customer_id_1
2607 )
2608 )
2609 )
2610 AND AR_CREDIT_HISTORIES.ON_HOLD = 'Y'
2611 ORDER BY AR_CREDIT_HISTORIES.HOLD_DATE DESC,
2612 AR_CREDIT_HISTORIES.CREDIT_HISTORY_ID DESC
2613 ;
2614
2615 BEGIN
2616
2617
2618 c_last_hold_number := '' ;
2619 c_last_hold_amount := l_dummy ;
2620 c_last_hold_date := '' ;
2621 c_last_hold_days_since := '' ;
2622 /*srw.reference (site_use_id);*/null;
2623
2624 /*srw.reference (customer_id);*/null;
2625
2626 OPEN C_HOLD ;
2627
2628 FETCH C_HOLD
2629 INTO
2630 l_last_hold_amount ,
2631 l_last_hold_date ,
2632 l_last_hold_days_since
2633 ;
2634
2635
2636 CLOSE C_HOLD ;
2637
2638 if l_last_hold_date is NOT NULL then
2639 c_last_hold_number := rp_na_upper ;
2640 c_last_hold_amount := l_last_hold_amount ;
2641 c_last_hold_date := l_last_hold_date ;
2642 c_last_hold_days_since := l_last_hold_days_since ;
2643 else
2644 c_last_hold_number := rp_none ;
2645 end if ;
2646
2647 return (0);
2648 EXCEPTION WHEN NO_DATA_FOUND THEN
2649 c_last_hold_number := rp_none;
2650 c_last_hold_amount := l_last_hold_amount ;
2651 c_last_hold_date := l_last_hold_date ;
2652 c_last_hold_days_since := l_last_hold_days_since ;
2653
2654 WHEN OTHERS THEN
2655 /*SRW.MESSAGE (1010,'Error in C_HOLD FORMULA');*/null;
2656
2657 return (0);
2658 END ;
2659
2660 RETURN NULL; end;
2661
2662 function c_data_not_foundformula(customer_name in varchar2) return number is
2663 begin
2664
2665 rp_data_found := customer_name ;
2666 return (0);
2667
2668 end;
2669
2670 procedure get_boiler_plates is
2671
2672 w_industry_code varchar2(20);
2673 w_industry_stat varchar2(20);
2674
2675 begin
2676
2677 if fnd_installation.get(0, 0,
2678 w_industry_stat,
2679 w_industry_code) then
2680 if w_industry_code = 'C' then
2681 c_sales_title := null ;
2682 else
2683 get_lookup_meaning('IND_SALES',
2684 w_industry_code,
2685 c_sales_title);
2686 end if;
2687 end if;
2688
2689 c_industry_code := w_Industry_code ;
2690
2691 end ;
2692
2693 procedure get_lookup_meaning(p_lookup_type in varchar2,
2694 p_lookup_code in varchar2,
2695 p_lookup_meaning in out NOCOPY varchar2)
2696 is
2697
2698 w_meaning varchar2(80);
2699
2700 begin
2701
2702 select meaning
2706 and lookup_code = p_lookup_code ;
2703 into w_meaning
2704 from fnd_lookups
2705 where lookup_type = p_lookup_type
2707
2708 p_lookup_meaning := w_meaning ;
2709
2710 exception
2711 when no_data_found then
2712 p_lookup_meaning := null ;
2713
2714 end ;
2715
2716 function set_display_for_core return boolean is
2717
2718 begin
2719
2720 if c_industry_code = 'C' then
2721 return(TRUE);
2722 else
2723 if c_sales_title is not null then
2724 return(FALSE);
2725 else
2726 return(TRUE);
2727 end if;
2728 end if;
2729
2730 RETURN NULL; end;
2731
2732 function set_display_for_gov return boolean is
2733
2734 begin
2735
2736
2737 if c_industry_code = 'C' then
2738 return(FALSE);
2739 else
2740 if c_sales_title is not null then
2741 return(TRUE);
2742 else
2743 return(FALSE);
2744 end if;
2745 end if;
2746
2747 RETURN NULL; end ;
2748
2749 function sel_contactformula(Address_id in number) return varchar2 is
2750 begin
2751
2752 declare
2753 contact VARCHAR2(81);
2754 phone_number VARCHAR2(73);
2755
2756
2757 cursor c1 is
2758 select decode( party.person_pre_name_adjunct , null,
2759 substrb(party.person_first_name,1,40) || ' ' ||
2760 substrb(party.person_last_name,1,50),
2761 ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CONTACT_TITLE',ORG_CONT.TITLE)
2762 || ' ' || substrb(party.person_first_name,1,40) || ' '||
2763 substrb(party.person_last_name,1,50)),
2764 cont_point.phone_area_code || ' ' ||
2765 RTRIM(RPAD(decode(cont_point.contact_point_type,'TLX',
2766 cont_point.telex_number,
2767 cont_point.phone_number), 15))
2768 from hz_cust_account_roles acct_role,
2769 hz_parties party,
2770 hz_relationships rel,
2771 hz_org_contacts org_cont,
2772 hz_contact_points cont_point,
2773 hz_cust_account_roles car
2774 where acct_role.cust_acct_site_id = Address_id
2775 and acct_role.party_id = rel.party_id
2776 and acct_role.role_type = 'CONTACT'
2777 and org_cont.party_relationship_id = rel.relationship_id
2778 and rel.subject_id = party.party_id
2779 and rel.subject_table_name = 'HZ_PARTIES'
2780 and rel.object_table_name = 'HZ_PARTIES'
2781 and rel.directional_flag = 'F'
2782 and acct_role.cust_account_role_id = car.cust_account_role_id(+)
2783 and car.party_id = cont_point.owner_table_id(+)
2784 and cont_point.owner_table_name(+) = 'HZ_PARTIES'
2785 and cont_point.contact_point_type(+) NOT IN ('EDI','EMAIL','WEB')
2786 and nvl(nvl(cont_point.phone_line_type,
2787 cont_point.contact_point_type), 'GEN') = 'GEN'
2788 and nvl( acct_role.status,'A') = 'A'
2789 order by cont_point.primary_flag desc;
2790 begin
2791 /*srw.reference(Address_id);*/null;
2792
2793
2794 c_contact := NULL;
2795 c_phone_number := NULL;
2796 contact := NULL;
2797 phone_number := NULL;
2798
2799 OPEN c1;
2800
2801 FETCH c1 INTO contact, phone_number;
2802
2803 c_contact := contact;
2804 c_phone_number := phone_number;
2805
2806 CLOSE c1;
2807
2808 return(' ');
2809
2810 EXCEPTION
2811 WHEN NO_DATA_FOUND THEN
2812 c_contact := contact;
2813 c_phone_number := phone_number;
2814 return(' ');
2815
2816 WHEN OTHERS THEN
2817 c_contact := contact;
2818 c_phone_number := phone_number;
2819 return(' ');
2820 end;
2821
2822 RETURN NULL; end;
2823
2824 PROCEDURE Get_Bucket_Data IS
2825 l_bucket_line_type VARCHAR2 (30);
2826 l_bucket_days_from NUMBER (16);
2827 l_bucket_days_to NUMBER (16);
2828 l_bucket_category VARCHAR2 (30);
2829 l_bucket_title VARCHAR2 (31);
2830
2831 CURSOR C_Sel_Bucket_Data is
2832 select lines.days_start,
2833 lines.days_to,
2834 report_heading1 || ' ' || report_heading2 ,
2835 lines.type
2836 from ar_aging_bucket_lines lines,
2837 ar_aging_buckets buckets
2838 where lines.aging_bucket_id = buckets.aging_bucket_id
2839 and upper(buckets.bucket_name) = upper(p_bucket_name_low)
2840 order by lines.bucket_sequence_num;
2841
2842 BEGIN
2843
2844 OPEN C_Sel_Bucket_Data ;
2845
2846
2847 LOOP
2848
2849 FETCH C_Sel_Bucket_Data
2850 INTO l_bucket_days_from ,
2851 l_bucket_days_to ,
2852 l_bucket_title ,
2853 l_bucket_line_type;
2854
2855 EXIT WHEN C_Sel_Bucket_Data%NOTFOUND ;
2856
2857 if rp_bucket_line_type_0 is NULL then
2858 rp_bucket_days_from_0 := l_bucket_days_from ;
2859 rp_bucket_days_to_0 := l_bucket_days_to ;
2860 rp_bucket_line_type_0 := l_bucket_line_type ;
2861 rp_bucket_title0 := l_bucket_title ;
2862 elsif rp_bucket_line_type_1 is NULL then
2863 rp_bucket_days_from_1 := l_bucket_days_from ;
2864 rp_bucket_days_to_1 := l_bucket_days_to ;
2865 rp_bucket_line_type_1 := l_bucket_line_type ;
2866 rp_bucket_title1 := l_bucket_title ;
2867 elsif rp_bucket_line_type_2 is NULL then
2868 rp_bucket_days_from_2 := l_bucket_days_from ;
2869 rp_bucket_days_to_2 := l_bucket_days_to ;
2870 rp_bucket_line_type_2 := l_bucket_line_type ;
2871 rp_bucket_title2 := l_bucket_title ;
2875 rp_bucket_line_type_3 := l_bucket_line_type ;
2872 elsif rp_bucket_line_type_3 is NULL then
2873 rp_bucket_days_from_3 := l_bucket_days_from ;
2874 rp_bucket_days_to_3 := l_bucket_days_to ;
2876 rp_bucket_title3 := l_bucket_title ;
2877 elsif rp_bucket_line_type_4 is NULL then
2878 rp_bucket_days_from_4 := l_bucket_days_from ;
2879 rp_bucket_days_to_4 := l_bucket_days_to ;
2880 rp_bucket_line_type_4 := l_bucket_line_type ;
2881 rp_bucket_title4 := l_bucket_title ;
2882 elsif rp_bucket_line_type_5 is NULL then
2883 rp_bucket_days_from_5 := l_bucket_days_from ;
2884 rp_bucket_days_to_5 := l_bucket_days_to ;
2885 rp_bucket_line_type_5 := l_bucket_line_type ;
2886 rp_bucket_title5 := l_bucket_title ;
2887 elsif rp_bucket_line_type_6 is NULL then
2888 rp_bucket_days_from_6 := l_bucket_days_from ;
2889 rp_bucket_days_to_6 := l_bucket_days_to ;
2890 rp_bucket_line_type_6 := l_bucket_line_type ;
2891 rp_bucket_title6 := l_bucket_title ;
2892 end if ;
2893
2894 if (l_bucket_line_type = 'DISPUTE_ONLY') OR
2895 (l_bucket_line_type = 'PENDADJ_ONLY') OR
2896 (l_bucket_line_type = 'DISPUTE_PENDADJ') then
2897
2898 rp_bucket_category := l_bucket_line_type ;
2899 end if ;
2900
2901 l_bucket_days_from := 0 ;
2902 l_bucket_days_to := 0 ;
2903 l_bucket_line_type := '';
2904
2905 END LOOP ;
2906
2907 CLOSE C_Sel_Bucket_Data ;
2908
2909 /*srw.message (593, 'rp_bucket_line_type_0 = '||rp_bucket_line_type_0);*/null;
2910
2911 /*srw.message (593, 'rp_bucket_line_type_1 = '||rp_bucket_line_type_1);*/null;
2912
2913 /*srw.message (593, 'rp_bucket_line_type_2 = '||rp_bucket_line_type_2);*/null;
2914
2915 /*srw.message (593, 'rp_bucket_line_type_3 = '||rp_bucket_line_type_3);*/null;
2916
2917 /*srw.message (593, 'rp_bucket_line_type_4 = '||rp_bucket_line_type_4);*/null;
2918
2919 /*srw.message (593, 'rp_bucket_line_type_5 = '||rp_bucket_line_type_5);*/null;
2920
2921 /*srw.message (593, 'rp_bucket_line_type_6 = '||rp_bucket_line_type_6);*/null;
2922
2923
2924 /*srw.message (593, 'rp_bucket_days_from_0 = '||rp_bucket_days_from_0);*/null;
2925
2926 /*srw.message (593, 'rp_bucket_days_to_0 = '||rp_bucket_days_to_0);*/null;
2927
2928 /*srw.message (593, 'rp_bucket_days_from_1 = '||rp_bucket_days_from_1);*/null;
2929
2930 /*srw.message (593, 'rp_bucket_days_to_1 = '||rp_bucket_days_to_1);*/null;
2931
2932 /*srw.message (593, 'rp_bucket_days_from_5 = '||rp_bucket_days_from_5);*/null;
2933
2934 /*srw.message (593, 'rp_bucket_days_to_5 = '||rp_bucket_days_to_5);*/null;
2935
2936 /*srw.message (593, 'rp_bucket_days_from_6 = '||rp_bucket_days_from_6);*/null;
2937
2938 /*srw.message (593, 'rp_bucket_days_to_6 = '||rp_bucket_days_to_6);*/null;
2939
2940
2941
2942 END;
2943
2944 function c_currency_lookupformula(site_use_id in number, currency_bucket in varchar2) return number is
2945 return_Curr_list VARCHAR2(100);
2946 l_entity_type VARCHAR2(20) := 'SITE';
2947 l_entity_id NUMBER := site_use_id;
2948 l_trx_curr_code VARCHAR2(15) := currency_bucket;
2949 l_default_flag VARCHAR2(1) := 'Y' ;
2950 l_limit_curr_code VARCHAR2(15);
2951 l_customer_id number;
2952 l_site_use_id number;
2953 begin
2954 select decode (site_use_id, null, 'CUSTOMER', 'SITE'),
2955 decode (site_use_id, null, cust_account_id, site_use_id),
2956 cust_account_id,
2957 site_use_id
2958 into l_entity_type,
2959 l_entity_id,
2960 l_customer_id
2961 ,l_site_use_id
2962 from hz_customer_profiles
2963 where cust_account_profile_id = c_customer_profile_id;
2964 /*srw.message(592, 'calling gli for '||l_entity_type||' '||l_entity_id||' '||l_trx_curr_code);*/null;
2965
2966 oe_credit_check_pvt.currency_list
2967 (l_entity_type
2968 ,l_entity_id
2969 ,l_trx_curr_code
2970 ,l_limit_curr_code
2971 ,l_default_flag
2972 ,return_curr_list);
2973 cp_related_currencies := return_Curr_list;
2974
2975 IF cp_related_currencies is NULL THEN
2976 cp_related_currencies:=CP_CF_RELATED_CURRENCY(l_customer_id,l_site_use_id);
2977 /*srw.message(500,' cp_related_currency ' || cp_related_currencies);*/null;
2978
2979 END IF;
2980 cp_limit_currency := l_limit_curr_code;
2981 cp_default_flag := l_default_flag;
2982
2983 /*srw.message(592, 'after gli limit curr is '||l_limit_curr_code||' and def flg is '||l_default_flag);*/null;
2984
2985
2986
2987 if l_entity_type = 'SITE' and l_limit_curr_code is null then
2988 /*srw.message(592, 'calling gli-II for '||l_entity_type||' '||l_entity_id||' '||l_trx_curr_code);*/null;
2989
2990 oe_credit_check_pvt.currency_list
2991 ('CUSTOMER'
2992 ,l_customer_id
2993 ,l_trx_curr_code
2994 ,l_limit_curr_code
2995 ,l_default_flag
2996 ,return_curr_list);
2997 cp_related_currencies := return_Curr_list;
2998
2999 IF cp_related_currencies is NULL THEN
3000 cp_related_currencies:=CP_CF_RELATED_CURRENCY(l_customer_id,l_site_use_id);
3001 /*srw.message(500,' II - cp_related_currency ' || cp_related_currencies);*/null;
3002
3003 END IF;
3004 cp_limit_currency := l_limit_curr_code;
3005 cp_default_flag := l_default_flag;
3006 /*srw.message(592, 'after gli limit curr is '||l_limit_curr_code||' and def flg is '||l_default_flag);*/null;
3007
3008 end if;
3009 if cp_limit_currency is not null then
3013 if instr(txn_currency, cp_limit_currency) = 0 then
3010 cp_txn_cur := cp_limit_currency||',';
3011 -- if instr(currency_list.txn_currency, cp_limit_currency) = 0 then
3012 --currency_list.txn_currency := currency_list.txn_currency||cp_txn_cur;
3014 txn_currency := txn_currency||cp_txn_cur;
3015 end if;
3016 end if;
3017 return(0);
3018 end;
3019
3020 function cf_calc_rate_amountformula(trx_cur in varchar2, customer_id in number, site_use_id in number, trx_amount_due in number) return number is
3021 begin
3022 declare
3023 present number;
3024 xchg_rate number;
3025 l_aging_on_account_profile NUMBER ;
3026 l_aging_unapplied_profile NUMBER ;
3027
3028
3029 begin
3030
3031 /*srw.message(300,'DEBUG: related currencies :'||CP_related_currencies);*/null;
3032
3033 Select instr( CP_related_currencies, trx_cur)
3034 INTO present
3035 from dual;
3036 if present <> 0 then
3037 /*srw.message(300,'DEBUG: trx_curr :'||trx_cur);*/null;
3038
3039 /*srw.message(300,'DEBUG: cp_limit_currency :'||CP_limit_currency);*/null;
3040
3041 Xchg_rate := gl_currency_api.get_rate_sql
3042 (trx_cur,CP_limit_currency,SYSDATE,'Corporate');
3043
3044 if Xchg_rate = -1 then
3045
3046 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3047 FND_MESSAGE.SET_TOKEN('CC',trx_cur,FALSE);
3048 /*SRW.MESSAGE(301,FND_MESSAGE.GET);*/null;
3049
3050
3051
3052 elsif xchg_rate = -2 then
3053 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3054 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3055
3056 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3057
3058 end if;
3059
3060
3061 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3062 AMOUNT_APPLIED, 0)),
3063 0) on_account,
3064 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3065 amount_applied, 0)),
3066 0) unapplied
3067 into l_aging_on_account_profile,
3068 l_aging_unapplied_profile
3069 from ar_receivable_applications,
3070 ar_cash_receipts
3071 where ar_receivable_applications.cash_receipt_id =
3072 ar_cash_receipts.cash_receipt_id
3073 and ar_cash_receipts.pay_from_customer = customer_id
3074 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3075 and ar_cash_receipts.currency_code = trx_cur
3076 and ar_receivable_applications.gl_date <= sysdate;
3077
3078
3079 cp_adjusted_amount := trx_amount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3080
3081 CP_limit_curr_amt := gl_currency_api.convert_amount_sql(trx_cur,CP_limit_currency,SYSDATE,'Corporate',CP_adjusted_amount);
3082
3083 if CP_limit_curr_amt = -1 then
3084 CP_limit_curr_amt:=TO_NUMBER(null);
3085
3086 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3087 FND_MESSAGE.SET_TOKEN('CC',trx_cur,FALSE);
3088 /*SRW.MESSAGE(301,FND_MESSAGE.GET);*/null;
3089
3090 elsif Cp_limit_curr_amt = -2 then
3091 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3092 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3093
3094 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3095
3096 end if;
3097 IF xchg_rate=-1
3098 THEN
3099 CP_rate:=TO_NUMBER(null);
3100 ELSE
3101 CP_rate := xchg_rate;
3102 END IF;
3103 end if;
3104 end;
3105 RETURN NULL; end;
3106
3107 function cf_calc_rate_amount_formula1fo(trx_cur2 in varchar2, customer_id in number, site_use_id in number, trx_amount_due1 in number) return number is
3108 begin
3109 declare
3110 present number;
3111 xchg_rate number;
3112 l_aging_on_account_profile NUMBER ;
3113 l_aging_unapplied_profile NUMBER ;
3114
3115
3116 begin
3117
3118 /*srw.reference(trx_cur2);*/null;
3119
3120 /*srw.reference(customer_id);*/null;
3121
3122 /*srw.reference(site_use_id);*/null;
3123
3124
3125 IF limit_currency is not null and related_currencies is not null then
3126
3127
3128 /*srw.message(300,' CALC RATE Customer ID :'||to_char(customer_id));*/null;
3129
3130 /*srw.message(300,' CALC RATE Site Id :'||to_char(site_use_id));*/null;
3131
3132 /*srw.message(300,' CALC RATE Transaction Currency :'||trx_cur2);*/null;
3133
3134 /*srw.message(300,' CALC RATE related currencies :'||related_currencies);*/null;
3135
3136 /*srw.message(300,' CALC RATE Limit Currency : '||limit_currency);*/null;
3137
3138
3139 Select instr( related_currencies, trx_cur2)
3140 INTO present
3141 from dual;
3142 /*srw.message(300, 'DEBUG: present : '|| to_char(present));*/null;
3143
3144
3145 if present <> 0 then
3146 Xchg_rate := gl_currency_api.get_rate_sql
3147 (trx_cur2,limit_currency,SYSDATE,'Corporate');
3148 if Xchg_rate = -1 then
3149
3150 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3151 FND_MESSAGE.SET_TOKEN('CC',trx_cur2,FALSE);
3152 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3153
3154 elsif xchg_rate = -2 then
3155 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3156 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3157
3158 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3159
3160 end if;
3161
3162
3163 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3164 AMOUNT_APPLIED, 0)),
3165 0) on_account,
3166 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3170 l_aging_unapplied_profile
3167 amount_applied, 0)),
3168 0) unapplied
3169 into l_aging_on_account_profile,
3171 from ar_receivable_applications,
3172 ar_cash_receipts
3173 where ar_receivable_applications.cash_receipt_id =
3174 ar_cash_receipts.cash_receipt_id
3175 and ar_cash_receipts.pay_from_customer = customer_id
3176 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3177 and ar_cash_receipts.currency_code = trx_cur2
3178 and ar_receivable_applications.gl_date <= sysdate;
3179
3180 /*srw.message(300, 'DEBUG: adjusted amount : '|| to_char(cp_adjusted_amount1));*/null;
3181
3182 /*srw.message(300, 'DEBUG: unapplied profile : '|| to_char(l_aging_unapplied_profile));*/null;
3183
3184 /*srw.message(300, 'DEBUG: on account profile : '|| to_char(l_aging_on_account_profile));*/null;
3185
3186
3187 cp_adjusted_amount1 := trx_amount_due1 - l_aging_unapplied_profile - l_aging_on_account_profile;
3188
3189 Cp_limit_curr_amt1 := gl_currency_api.convert_amount_sql(trx_cur2,limit_currency,SYSDATE,NULL,CP_adjusted_amount1);
3190
3191 if Cp_limit_curr_amt1 = -1 then
3192 Cp_limit_curr_amt1:=TO_NUMBER(null);
3193 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3194 FND_MESSAGE.SET_TOKEN('CC',trx_cur2,FALSE);
3195 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3196
3197 elsif Cp_limit_curr_amt1 = -2 then
3198 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3199 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3200
3201 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3202
3203 end if;
3204
3205 if xchg_rate=-1
3206 then
3207 CP_rate1:=TO_NUMBER(null);
3208 else
3209 CP_rate1 := xchg_rate;
3210 end if;
3211 else
3212 /*srw.message(300, trx_cur2 ||' does not have usage for limit currency - '||limit_currency);*/null;
3213
3214
3215 end if;
3216 END IF;
3217 end;
3218
3219 RETURN NULL; end;
3220 --function c_org_credit_calcformula(customer_id in number, site_use_id in number) return number is
3221 function c_org_credit_calcformula(customer_id in number, site_use_id in number,functional_currency varchar2,Org_Currency_Code varchar2,org_overall_limit number) return number is
3222 begin
3223
3224 DECLARE
3225
3226 l_aging_balance_os_profile NUMBER ;
3227 Adjusted_balance NUMBER;
3228 l_aging_convert_os_profile VARCHAR2 (1);
3229 l_aging_on_account_profile NUMBER ;
3230 l_aging_conv_on_ac_profile VARCHAR2 (1);
3231 l_aging_unapplied_profile NUMBER ;
3232 l_aging_conv_unap_prof VARCHAR2 (1);
3233 l_cred_summ_avail_credit NUMBER ;
3234 l_dummy NUMBER (1);
3235 trx_curr VARCHAR2(15);
3236 trx_amount NUMBER;
3237 base_amount NUMBER;
3238 curr_exists NUMBER;
3239
3240 CURSOR ps_trx IS
3241 SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due
3242 from ar_payment_schedules ps
3243 where ps.customer_id = customer_id
3244 and ps.customer_site_use_id = site_use_id
3245 and ps.status = 'OP'
3246 and ps.class not in ('CM', 'PMT')
3247 group by ps.invoice_currency_code;
3248
3249
3250 l_loop VARCHAR2(1);
3251
3252 BEGIN
3253
3254 /*srw.message (200, 'DEBUG: credit_calc: ' || to_char(customer_id) ||' : '||to_char(site_use_id));*/null;
3255
3256
3257 l_cred_summ_avail_credit := 0;
3258 l_aging_balance_os_profile := 0;
3259 Adjusted_balance := 0;
3260 c_cred_convert_limit2 := '' ;
3261 c_cred_summ_available2 := l_dummy;
3262 c_cred_summ_exceeded2 := l_dummy ;
3263
3264 /*srw.reference (customer_id);*/null;
3265
3266 /*srw.reference (Org_Currency_Code);*/null;
3267
3268 /*srw.reference (site_use_id);*/null;
3269
3270 /*srw.reference (org_overall_limit);*/null;
3271
3272
3273
3274
3275 l_aging_on_account_profile:=0;
3276 l_aging_unapplied_profile:=0;
3277 l_loop:='N';
3278
3279
3280 SELECT
3281 NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3282 AMOUNT_APPLIED, 0)),
3283 0) on_account,
3284 nvl(max(decode(ar_receivable_applications.status, 'ACC',
3285 decode(ar_cash_receipts.currency_code,functional_currency, ' ',
3286 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3287 ' ')), ' ') account_convert,
3288 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3289 amount_applied, 0)),
3290 0) unapplied,
3291 nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3292 decode(ar_cash_receipts.currency_code,functional_currency, ' ',
3293 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3294 ' ')), ' ') unapp_convert
3295 into
3296 l_aging_on_account_profile,
3297 l_aging_conv_on_ac_profile,
3298 l_aging_unapplied_profile,
3299 l_aging_conv_unap_prof
3300 from ar_receivable_applications,
3301 ar_cash_receipts
3302 where ar_receivable_applications.cash_receipt_id =
3303 ar_cash_receipts.cash_receipt_id
3304
3305
3306 and ar_cash_receipts.pay_from_customer = customer_id
3307 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3308 and ar_cash_receipts.currency_code = org_currency_code
3309 and ar_receivable_applications.gl_date <= sysdate;
3310
3311
3312 FOR trx_rec IN ps_trx
3313 LOOP
3314 SELECT instr(related_currencies,trx_rec.invoice_currency_code)
3315 INTO curr_exists
3316 FROM DUAL;
3317 IF curr_exists <> 0 THEN
3318 trx_curr := trx_rec.invoice_currency_code;
3319
3320
3324
3321 l_aging_on_account_profile:=0;
3322 l_aging_unapplied_profile:=0;
3323 l_loop:='Y';
3325 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3326 AMOUNT_APPLIED, 0)),
3327 0) on_account,
3328 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3329 amount_applied, 0)),
3330 0) unapplied
3331 into l_aging_on_account_profile,
3332 l_aging_unapplied_profile
3333 from ar_receivable_applications,
3334 ar_cash_receipts
3335 where ar_receivable_applications.cash_receipt_id =
3336 ar_cash_receipts.cash_receipt_id
3337 and ar_cash_receipts.pay_from_customer = customer_id
3338 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3339 and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
3340 and ar_receivable_applications.gl_date <= sysdate;
3341
3342 trx_amount := trx_rec.ammount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3343
3344 base_amount := gl_currency_api.convert_amount_sql(trx_curr,limit_currency,SYSDATE,'Corporate',trx_amount);
3345 IF base_amount = -1 THEN
3346 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3347 FND_MESSAGE.SET_TOKEN('CC',trx_curr,FALSE);
3348 /*SRW.MESSAGE(200,FND_MESSAGE.GET);*/null;
3349
3350 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3351
3352
3353 END IF;
3354 IF base_amount = -2 THEN
3355 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3356 /*SRW.MESSAGE(200,FND_MESSAGE.GET);*/null;
3357
3358 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3359
3360 END IF;
3361
3362 Adjusted_balance := Adjusted_balance + base_amount;
3363 END IF;
3364 END LOOP;
3365
3366
3367
3368 if (org_overall_limit is NOT NULL ) then
3369
3370 If l_loop = 'Y' then
3371 l_cred_summ_avail_credit := org_overall_limit - Adjusted_balance;
3372 Else
3373 l_cred_summ_avail_credit := org_overall_limit + (l_aging_unapplied_profile + l_aging_on_account_profile);
3374 End If;
3375
3376 if (( l_aging_conv_on_ac_profile = '*' ) OR
3377 (l_aging_conv_unap_prof = '*')) then
3378 c_cred_convert_limit2 := '*';
3379 end if ;
3380
3381 if l_cred_summ_avail_credit < 0 then
3382 c_cred_summ_available2 := 0 ;
3383 c_cred_summ_exceeded2 := l_cred_summ_avail_credit ;
3384 else
3385 c_cred_summ_exceeded2 := 0 ;
3386 c_cred_summ_available2 := l_cred_summ_avail_credit ;
3387 end if ;
3388
3389 end if ;
3390
3391 return (0);
3392 END ;
3393
3394 end;
3395
3396 --function cf_org_calc_amountformula(org_trx_cur in varchar2, customer_id in number, site_use_id in number, --trx_amount_due2 in number) return number is
3397 function cf_org_calc_amountformula(org_trx_cur in varchar2, customer_id in number, site_use_id in number, trx_amount_due2 in number) return number is
3398 begin
3399 declare
3400 present number;
3401 xchg_rate number;
3402 l_aging_on_account_profile NUMBER ;
3403 l_aging_unapplied_profile NUMBER ;
3404
3405
3406 begin
3407
3408 /*srw.reference(org_trx_cur);*/null;
3409
3410 /*srw.reference(customer_id);*/null;
3411
3412 /*srw.reference(site_use_id);*/null;
3413
3414 /*srw.reference(default_flag);*/null;
3415
3416
3417 IF limit_currency is not null and related_currencies is not null
3418 and default_flag = 'Y' then
3419
3420
3421 /*srw.message(900,' CALC RATE Customer ID :'||to_char(customer_id));*/null;
3422
3423 /*srw.message(900,' CALC RATE Site Id :'||to_char(site_use_id));*/null;
3424
3425 /*srw.message(900,' CALC RATE Transaction Currency :'||org_trx_cur);*/null;
3426
3427 /*srw.message(900,' CALC RATE related currencies :'||related_currencies);*/null;
3428
3429 /*srw.message(900,' CALC RATE Limit Currency : '||limit_currency);*/null;
3430
3431
3432 Select instr( related_currencies, org_trx_cur)
3433 INTO present
3434 from dual;
3435 /*srw.message(300, 'DEBUG: present : '|| to_char(present));*/null;
3436
3437
3438 if present <> 0 then
3439 Xchg_rate := gl_currency_api.get_rate_sql(org_trx_cur,limit_currency,SYSDATE,NULL);
3440 if Xchg_rate = -1 then
3441 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3442 FND_MESSAGE.SET_TOKEN('CC',org_trx_cur,FALSE);
3443 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3444
3445 elsif xchg_rate = -2 then
3446 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3447 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3448
3449 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3450
3451 end if;
3452
3453
3454 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3455 AMOUNT_APPLIED, 0)),
3456 0) on_account,
3457 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3458 amount_applied, 0)),
3459 0) unapplied
3460 into l_aging_on_account_profile,
3461 l_aging_unapplied_profile
3462 from ar_receivable_applications,
3463 ar_cash_receipts
3464 where ar_receivable_applications.cash_receipt_id =
3465 ar_cash_receipts.cash_receipt_id
3466 and ar_cash_receipts.pay_from_customer = customer_id
3467 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3468 and ar_cash_receipts.currency_code = org_trx_cur
3469 and ar_receivable_applications.gl_date <= sysdate;
3470
3471 /*srw.message(300, 'DEBUG: adjusted amount : '|| to_char(cp_adjusted_amount2));*/null;
3472
3473 /*srw.message(300, 'DEBUG: unapplied profile : '|| to_char(l_aging_unapplied_profile));*/null;
3474
3478 cp_adjusted_amount2 := trx_amount_due2 - l_aging_unapplied_profile - l_aging_on_account_profile;
3475 /*srw.message(300, 'DEBUG: on account profile : '|| to_char(l_aging_on_account_profile));*/null;
3476
3477
3479
3480 Cp_limit_curr_amt2 := gl_currency_api.convert_amount_sql(org_trx_cur,limit_currency,SYSDATE,NULL,CP_adjusted_amount2);
3481
3482 if Cp_limit_curr_amt2 = -1 then
3483 Cp_limit_curr_amt2:=TO_NUMBER(null);
3484 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3485 FND_MESSAGE.SET_TOKEN('CC',org_trx_cur,FALSE);
3486 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3487
3488 elsif Cp_limit_curr_amt2 = -2 then
3489 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3490 /*SRW.MESSAGE(300,FND_MESSAGE.GET);*/null;
3491
3492 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3493
3494 end if;
3495
3496 IF xchg_rate=-1
3497 THEN
3498 CP_rate2 :=TO_NUMBER(null);
3499 ELSE
3500 CP_rate2 := xchg_rate;
3501 END IF;
3502 else
3503 /*srw.message(300, org_trx_cur ||' does not have usage for limit currency - '||limit_currency);*/null;
3504
3505
3506 end if;
3507 END IF;
3508 return(0);
3509 end;
3510
3511 end;
3512
3513 --function C_org_profileFormula return Number is
3514 function C_org_profileFormula return Number is
3515 begin
3516 DECLARE
3517
3518 l_cred_summ_limit_tolerance VARCHAR2(100);
3519 l_cred_summ_credit_rating VARCHAR2(100);
3520 l_cred_summ_credit_hold VARCHAR2(100);
3521 l_credit_profile_id NUMBER(20);
3522 l_profile_site_use_id NUMBER(20);
3523 ct_prof NUMBER(2);
3524 yes VARCHAR2(3);
3525 no VARCHAR2(3);
3526 BEGIN
3527 /*srw.reference (p_reporting_entity_id);*/null;
3528
3529 /*srw.message(999,'Credit Summary Cust Ref : '||customer_id);*/null;
3530
3531 /*srw.reference (site_use_id);*/null;
3532
3533 /*srw.message(999,'Credit Summary Site Ref : '||site_use_id);*/null;
3534
3535
3536 select count(*)
3537 into ct_prof
3538 from hz_credit_profile_amts cpa
3539 where credit_profile_id IN (Select credit_profile_id
3540 From HZ_Credit_Profiles
3541 Where organization_id = p_reporting_entity_id
3542 and effective_date_from <= SYSDATE
3543 and effective_date_to >= SYSDATE);
3544
3545
3546 SELECT substr(INITCAP(YES.MEANING),1,3) yes,
3547 substr(INITCAP(NO.MEANING),1,3) no
3548 INTO yes,
3549 no
3550 FROM AR_LOOKUPS YES,
3551 AR_LOOKUPS NO
3552 WHERE YES.LOOKUP_TYPE = 'YES/NO' AND
3553 YES.LOOKUP_CODE = 'Y' AND
3554 NO.LOOKUP_TYPE = 'YES/NO' AND
3555 NO.LOOKUP_CODE = 'N';
3556
3557 Select
3558 to_char(nvl(cp.tolerance, 0), '990') || '%',
3559 substr(nvl(cp.credit_rating, rp_na_upper),1,30),
3560 lk.meaning,
3561 cp.credit_profile_id
3562 into
3563 l_cred_summ_limit_tolerance,
3564 l_cred_summ_credit_rating,
3565 l_cred_summ_credit_hold,
3566 l_credit_profile_id
3567 from HZ_credit_profiles cp,
3568 ar_lookups lk
3569 where
3570 nvl(cp.credit_hold,'N') = lk.lookup_code
3571 and lk.lookup_type = 'YES/NO'
3572 and cp.organization_id = p_reporting_entity_id
3573 and NVL(cp.effective_date_from, sysdate-1) > sysdate
3574 and NVL(cp.effective_date_to, sysdate+1) < sysdate
3575 and NVL(cp.enable_flag, 'N') = 'Y';
3576
3577
3578 c_org_limit_tolerance := l_cred_summ_limit_tolerance ;
3579 c_org_credit_rating := l_cred_summ_credit_rating ;
3580 c_org_credit_hold := substr(l_cred_summ_credit_hold,1,4) ;
3581 c_credit_profile_id := l_credit_profile_id ;
3582
3583 --currency_list.txn_currency := NULL;
3584 txn_currency := NULL;
3585
3586
3587 EXCEPTION WHEN NO_DATA_FOUND THEN
3588 return (0);
3589
3590
3591 end;
3592
3593
3594 end;
3595
3596 function G_CREDIT_AMOUNTSGroupFilter return boolean is
3597 begin
3598 /*srw.message(592, 'in fmt trg of g_credit_amounts');*/null;
3599
3600 return (TRUE);
3601 end;
3602
3603 function G_customer_limitGroupFilter return boolean is
3604 begin
3605 return (TRUE);
3606 end;
3607
3608 function cf_currency_lookupformula(qc_customer1 in number, currency_credit1 in varchar2, qc_site1 in number, Site_use_id in number) return number is
3609 begin
3610
3611 declare
3612 return_Curr_list VARCHAR2(100);
3613 l_entity_type VARCHAR2(20) := 'CUSTOMER';
3614 l_entity_id NUMBER := qc_customer1;
3615 l_trx_curr_code VARCHAR2(15) := currency_credit1;
3616 l_limit_curr_code VARCHAR2(15);
3617 l_default_limit_flag VARCHAR2(1);
3618 begin
3619
3620 /*srw.reference(qc_customer1);*/null;
3621
3622 /*srw.reference(qc_site1);*/null;
3623
3624 /*srw.reference(currency_credit1);*/null;
3625
3626
3627 if qc_site1 is NULL then
3628 oe_credit_check_pvt.currency_list
3629 (l_entity_type
3630 ,l_entity_id
3631 ,l_trx_curr_code
3632 ,l_limit_curr_code
3633 ,l_default_limit_flag
3634 ,return_curr_list);
3635
3636 if l_limit_curr_code is NULL then
3637 limit_currency := l_trx_curr_code;
3638 else
3639 limit_currency := l_limit_curr_code;
3640 end if;
3641 related_currencies := return_Curr_list;
3642
3646 default_flag:= l_default_limit_flag;
3643 IF related_currencies IS NULL THEN
3644 related_currencies:=Currency_credit1;
3645 END IF;
3647
3648
3649
3650 else
3651
3652 l_entity_type:='SITE';
3653 l_entity_id:=Site_use_id;
3654 oe_credit_check_pvt.currency_list
3655 (l_entity_type
3656 ,l_entity_id
3657 ,l_trx_curr_code
3658 ,l_limit_curr_code
3659 ,l_default_limit_flag
3660 ,return_curr_list);
3661
3662 if l_limit_curr_code is NULL then
3663 limit_currency := l_trx_curr_code;
3664 else
3665 limit_currency := l_limit_curr_code;
3666 end if;
3667 IF return_curr_list is null THEN
3668 related_currencies:=currency_credit1;
3669 ELSE
3670 related_currencies := return_curr_list;
3671 END IF;
3672 end if;
3673 /*srw.message(513,'related_currency ' || related_currencies || ' Customer ' || to_char(qc_customer1) ||
3674 to_Char(site_use_id));*/null;
3675
3676 end;
3677 return(0);
3678 end;
3679
3680 --function c_credit_amounts_calcformu0114(customer_id in number, site_use_id in number) return number is
3681 function c_credit_amounts_calcformu0114(customer_id in number,site_use_id in number,functional_currency varchar2,Currency_Credit1 varchar2,credit_limit1 number) return number is
3682
3683 begin
3684
3685
3686 DECLARE
3687
3688 l_aging_balance_os_profile NUMBER ;
3689 Adjusted_balance NUMBER;
3690 l_aging_convert_os_profile VARCHAR2 (1);
3691 l_aging_on_account_profile NUMBER ;
3692 l_aging_conv_on_ac_profile VARCHAR2 (1);
3693 l_aging_unapplied_profile NUMBER ;
3694 l_aging_conv_unap_prof VARCHAR2 (1);
3695 l_cred_summ_avail_credit NUMBER ;
3696 l_dummy NUMBER (1);
3697 trx_curr VARCHAR2(15);
3698 trx_amount NUMBER;
3699 base_amount NUMBER;
3700 curr_exists NUMBER;
3701
3702 CURSOR ps_trx IS
3703 SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due,exchange_rate_type
3704 from ar_payment_schedules ps
3705 where ps.customer_id = customer_id
3706 and ps.customer_site_use_id = site_use_id
3707 and ps.status = 'OP'
3708 and ps.class not in ('CM', 'PMT')
3709 group by ps.invoice_currency_code,exchange_rate_type;
3710
3711
3712 l_loop VARCHAR2(1);
3713
3714 BEGIN
3715
3716 /*srw.message (200, 'DEBUG: credit_calc: ' || to_char(customer_id) ||' : '||to_char(site_use_id));*/null;
3717
3718
3719 l_cred_summ_avail_credit := 0 ;
3720 l_aging_balance_os_profile := 0;
3721 Adjusted_balance := 0;
3722 c_cred_convert_limit1 := '' ;
3723 c_cred_summ_available1 := l_dummy;
3724 c_cred_summ_exceeded1 := l_dummy ;
3725
3726 /*srw.reference (customer_id);*/null;
3727
3728 /*srw.reference (Currency_Credit1);*/null;
3729
3730 /*srw.reference (site_use_id);*/null;
3731
3732 /*srw.reference (credit_limit1);*/null;
3733
3734
3735
3736
3737 l_loop:='N';
3738 l_aging_on_account_profile:=0;
3739 l_aging_unapplied_profile:=0;
3740
3741
3742 SELECT
3743 NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3744 AMOUNT_APPLIED, 0)),
3745 0) on_account,
3746 nvl(max(decode(ar_receivable_applications.status, 'ACC',
3747 decode(ar_cash_receipts.currency_code, functional_currency, ' ',
3748 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3749 ' ')), ' ') account_convert,
3750 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3751 amount_applied, 0)),
3752 0) unapplied,
3753 nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3754 decode(ar_cash_receipts.currency_code, functional_currency, ' ',
3755 decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3756 ' ')), ' ') unapp_convert
3757 into
3758 l_aging_on_account_profile,
3759 l_aging_conv_on_ac_profile,
3760 l_aging_unapplied_profile,
3761 l_aging_conv_unap_prof
3762 from ar_receivable_applications,
3763 ar_cash_receipts
3764 where ar_receivable_applications.cash_receipt_id =
3765 ar_cash_receipts.cash_receipt_id
3766
3767 and ar_cash_receipts.pay_from_customer = customer_id
3768 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3769 and ar_cash_receipts.currency_code = currency_credit1
3770 and ar_receivable_applications.gl_date <= sysdate;
3771
3772
3773 FOR trx_rec IN ps_trx
3774 LOOP
3775 SELECT instr(related_currencies,trx_rec.invoice_currency_code)
3776 INTO curr_exists
3777 FROM DUAL;
3778 IF curr_exists <> 0 THEN
3779 trx_curr := trx_rec.invoice_currency_code;
3780
3781 l_loop:='Y';
3782 l_aging_on_account_profile:=0;
3783 l_aging_unapplied_profile:=0;
3784
3785 SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3786 AMOUNT_APPLIED, 0)),
3787 0) on_account,
3788 nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3789 amount_applied, 0)),
3790 0) unapplied
3791 into l_aging_on_account_profile,
3792 l_aging_unapplied_profile
3793 from ar_receivable_applications,
3794 ar_cash_receipts
3795 where ar_receivable_applications.cash_receipt_id =
3796 ar_cash_receipts.cash_receipt_id
3797 and ar_cash_receipts.pay_from_customer = customer_id
3798 and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3799 and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
3800 and ar_receivable_applications.gl_date <= sysdate;
3801
3805 (trx_curr,limit_currency,SYSDATE,'Corporate',trx_amount);
3802 trx_amount := trx_rec.ammount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3803
3804 base_amount := gl_currency_api.convert_amount_sql
3806 IF base_amount = -1 THEN
3807 base_amount := 0;
3808 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_EXCHANGE_RATE');
3809 FND_MESSAGE.SET_TOKEN('CC',trx_curr,FALSE);
3810 /*SRW.MESSAGE(201,FND_MESSAGE.GET);*/null;
3811
3812 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3813
3814
3815 END IF;
3816 IF base_amount = -2 THEN
3817 FND_MESSAGE.SET_NAME('AR','AR_CC_INVALID_CURRENCY');
3818 /*SRW.MESSAGE(200,FND_MESSAGE.GET);*/null;
3819
3820 raise_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
3821
3822 END IF;
3823
3824 /*srw.message (200, 'DEBUG: base_amount: ' || to_char(base_amount));*/null;
3825
3826 Adjusted_balance := Adjusted_balance + base_amount;
3827 END IF;
3828 END LOOP;
3829
3830
3831
3832 if (credit_limit1 is NOT NULL ) then
3833
3834 If l_loop = 'Y' then
3835 l_cred_summ_avail_credit := credit_limit1 - Adjusted_balance;
3836 Else
3837 l_cred_summ_avail_credit := credit_limit1 + (l_aging_unapplied_profile + l_aging_on_account_profile);
3838 End if;
3839
3840 if (( l_aging_conv_on_ac_profile = '*' ) OR
3841 (l_aging_conv_unap_prof = '*')) then
3842 c_cred_convert_limit1 := '*';
3843 end if ;
3844
3845 if l_cred_summ_avail_credit < 0 then
3846 c_cred_summ_available1 := 0 ;
3847 c_cred_summ_exceeded1 := l_cred_summ_avail_credit ;
3848 else
3849 c_cred_summ_exceeded1 := 0 ;
3850 c_cred_summ_available1 := l_cred_summ_avail_credit ;
3851 end if ;
3852
3853 end if ;
3854
3855 return (0);
3856 END ;
3857
3858
3859
3860 RETURN NULL; end;
3861
3862 --function cp_cf_related_currency(p_cust_acct_id number,p_site_use_id number)(p_site_use_id number' p_cust_acct_id number) return varchar2 is
3863 FUNCTION cp_cf_related_currency(p_cust_acct_id number,p_site_use_id number) RETURN VARCHAR2 IS
3864 l_ret_string varchar2(2000);
3865 l_cnt integer;
3866 BEGIN
3867 l_ret_string:=NULL;
3868 l_cnt:=0;
3869 for i in (select distinct currency_code from
3870 hz_cust_profile_amts where cust_account_id=p_cust_acct_id
3871 and NVL(site_use_id,-9) = NVL(p_site_use_id,NVL(site_use_id,-9)))
3872 Loop
3873 l_cnt:=l_cnt+1;
3874 IF l_cnt=1 THEN NULL;
3875 ELSE
3876 l_ret_string:=concat(l_ret_string,',');
3877 END IF;
3878 l_ret_string:=concat(l_ret_string,i.currency_code);
3879 END LOOP;
3880 /*srw.message(513,' Return String ' || l_ret_string);*/null;
3881
3882 return (l_ret_string);
3883 END;
3884
3885 --Functions to refer Oracle report placeholders--
3886
3887 Function C_Contact_p return varchar2 is
3888 Begin
3889 return C_Contact;
3890 END;
3891 Function C_Phone_Number_p return varchar2 is
3892 Begin
3893 return C_Phone_Number;
3894 END;
3895 Function c_profile_site_use_id_p return number is
3896 Begin
3897 return c_profile_site_use_id;
3898 END;
3899 Function c_customer_profile_id_p return number is
3900 Begin
3901 return c_customer_profile_id;
3902 END;
3903 Function c_cred_summ_collector_p return varchar2 is
3904 Begin
3905 return c_cred_summ_collector;
3906 END;
3907 Function c_cred_summ_exempt_dun_p return varchar2 is
3908 Begin
3909 return c_cred_summ_exempt_dun;
3910 END;
3911 Function c_cred_summ_terms_p return varchar2 is
3912 Begin
3913 return c_cred_summ_terms;
3914 END;
3915 Function c_cred_summ_account_status_p return varchar2 is
3916 Begin
3917 return c_cred_summ_account_status;
3918 END;
3919 Function c_cred_summ_credit_hold_p return varchar2 is
3920 Begin
3921 return c_cred_summ_credit_hold;
3922 END;
3923 Function c_cred_summ_risk_code_p return varchar2 is
3924 Begin
3925 return c_cred_summ_risk_code;
3926 END;
3927 Function c_cred_summ_credit_rating_p return varchar2 is
3928 Begin
3929 return c_cred_summ_credit_rating;
3930 END;
3931 Function c_cred_summ_limit_tolerance_p return varchar2 is
3932 Begin
3933 return c_cred_summ_limit_tolerance;
3934 END;
3935 -- Function c_cred_summ_limit_expire_date return varchar2 is
3936 Function c_cred_summ_lt_exp_date_p return varchar2 is
3937 Begin
3938 return c_cred_summ_limit_expire_date;
3939 END;
3940 Function c_last_invoice_days_since_p return varchar2 is
3941 Begin
3942 return c_last_invoice_days_since;
3943 END;
3944 Function c_last_invoice_date_p return varchar2 is
3945 Begin
3946 return c_last_invoice_date;
3947 END;
3948 Function c_last_invoice_converted_p return varchar2 is
3949 Begin
3950 return c_last_invoice_converted;
3951 END;
3952 Function c_last_invoice_amount_p return number is
3953 Begin
3954 return c_last_invoice_amount;
3955 END;
3956 Function c_last_invoice_currency_p return varchar2 is
3957 Begin
3958 return c_last_invoice_currency;
3959 END;
3960 Function c_last_invoice_type_p return varchar2 is
3961 Begin
3962 return c_last_invoice_type;
3963 END;
3964 Function c_last_invoice_number_p return varchar2 is
3965 Begin
3966 return c_last_invoice_number;
3967 END;
3968 Function c_last_cm_rel_invoice_p return varchar2 is
3969 Begin
3970 return c_last_cm_rel_invoice;
3971 END;
3972 Function c_last_cm_converted_p return varchar2 is
3973 Begin
3974 return c_last_cm_converted;
3978 return c_last_cm_amount;
3975 END;
3976 Function c_last_cm_amount_p return number is
3977 Begin
3979 END;
3980 Function c_last_cm_id_p return number is
3981 Begin
3982 return c_last_cm_id;
3983 END;
3984 Function c_last_cm_prev_trx_p return number is
3985 Begin
3986 return c_last_cm_prev_trx;
3987 END;
3988 Function c_last_cm_days_since_p return varchar2 is
3989 Begin
3990 return c_last_cm_days_since;
3991 END;
3992 Function c_last_cm_date_p return varchar2 is
3993 Begin
3994 return c_last_cm_date;
3995 END;
3996 Function c_last_cm_currency_p return varchar2 is
3997 Begin
3998 return c_last_cm_currency;
3999 END;
4000 Function c_last_cm_type_p return varchar2 is
4001 Begin
4002 return c_last_cm_type;
4003 END;
4004 Function c_last_cm_number_p return varchar2 is
4005 Begin
4006 return c_last_cm_number;
4007 END;
4008 Function c_last_guar_days_since_p return varchar2 is
4009 Begin
4010 return c_last_guar_days_since;
4011 END;
4012 Function c_last_guar_date_p return varchar2 is
4013 Begin
4014 return c_last_guar_date;
4015 END;
4016 Function c_last_guar_converted_p return varchar2 is
4017 Begin
4018 return c_last_guar_converted;
4019 END;
4020 Function c_last_guar_amount_p return number is
4021 Begin
4022 return c_last_guar_amount;
4023 END;
4024 Function c_last_guar_currency_p return varchar2 is
4025 Begin
4026 return c_last_guar_currency;
4027 END;
4028 Function c_last_guar_type_p return varchar2 is
4029 Begin
4030 return c_last_guar_type;
4031 END;
4032 Function c_last_guar_number_p return varchar2 is
4033 Begin
4034 return c_last_guar_number;
4035 END;
4036 Function c_last_dep_days_since_p return varchar2 is
4037 Begin
4038 return c_last_dep_days_since;
4039 END;
4040 Function c_last_dep_date_p return varchar2 is
4041 Begin
4042 return c_last_dep_date;
4043 END;
4044 Function c_last_dep_converted_p return varchar2 is
4045 Begin
4046 return c_last_dep_converted;
4047 END;
4048 Function c_last_dep_amount_p return number is
4049 Begin
4050 return c_last_dep_amount;
4051 END;
4052 Function c_last_dep_currency_p return varchar2 is
4053 Begin
4054 return c_last_dep_currency;
4055 END;
4056 Function c_last_dep_type_p return varchar2 is
4057 Begin
4058 return c_last_dep_type;
4059 END;
4060 Function c_last_dep_number_p return varchar2 is
4061 Begin
4062 return c_last_dep_number;
4063 END;
4064 Function c_last_dm_days_since_p return varchar2 is
4065 Begin
4066 return c_last_dm_days_since;
4067 END;
4068 Function c_last_dm_date_p return varchar2 is
4069 Begin
4070 return c_last_dm_date;
4071 END;
4072 Function c_last_dm_converted_p return varchar2 is
4073 Begin
4074 return c_last_dm_converted;
4075 END;
4076 Function c_last_dm_amount_p return number is
4077 Begin
4078 return c_last_dm_amount;
4079 END;
4080 Function c_last_dm_currency_p return varchar2 is
4081 Begin
4082 return c_last_dm_currency;
4083 END;
4084 Function c_last_dm_type_p return varchar2 is
4085 Begin
4086 return c_last_dm_type;
4087 END;
4088 Function c_last_dm_number_p return varchar2 is
4089 Begin
4090 return c_last_dm_number;
4091 END;
4092 Function c_last_cb_days_since_p return number is
4093 Begin
4094 return c_last_cb_days_since;
4095 END;
4096 Function c_last_cb_date_p return varchar2 is
4097 Begin
4098 return c_last_cb_date;
4099 END;
4100 Function c_last_cb_converted_p return varchar2 is
4101 Begin
4102 return c_last_cb_converted;
4103 END;
4104 Function c_last_cb_amount_p return number is
4105 Begin
4106 return c_last_cb_amount;
4107 END;
4108 Function c_last_cb_currency_p return varchar2 is
4109 Begin
4110 return c_last_cb_currency;
4111 END;
4112 Function c_last_cb_type_p return varchar2 is
4113 Begin
4114 return c_last_cb_type;
4115 END;
4116 Function c_last_cb_number_p return varchar2 is
4117 Begin
4118 return c_last_cb_number;
4119 END;
4120 Function c_last_payment_rel_invoice_p return varchar2 is
4121 Begin
4122 return c_last_payment_rel_invoice;
4123 END;
4124 Function c_last_payment_days_since_p return varchar2 is
4125 Begin
4126 return c_last_payment_days_since;
4127 END;
4128 Function c_last_payment_date_p return varchar2 is
4129 Begin
4130 return c_last_payment_date;
4131 END;
4132 Function c_last_payment_converted_p return varchar2 is
4133 Begin
4134 return c_last_payment_converted;
4135 END;
4136 Function c_last_payment_amount_p return number is
4137 Begin
4138 return c_last_payment_amount;
4139 END;
4140 Function c_last_payment_currency_p return varchar2 is
4141 Begin
4142 return c_last_payment_currency;
4143 END;
4144 Function c_last_payment_type_p return varchar2 is
4145 Begin
4146 return c_last_payment_type;
4147 END;
4148 Function c_last_payment_number_p return varchar2 is
4149 Begin
4150 return c_last_payment_number;
4151 END;
4152 Function c_last_adj_days_since_p return varchar2 is
4153 Begin
4154 return c_last_adj_days_since;
4155 END;
4156 Function c_last_adj_date_p return varchar2 is
4157 Begin
4158 return c_last_adj_date;
4159 END;
4160 Function c_last_adj_converted_p return varchar2 is
4161 Begin
4162 return c_last_adj_converted;
4163 END;
4164 Function c_last_adj_amount_p return number is
4165 Begin
4169 Begin
4166 return c_last_adj_amount;
4167 END;
4168 Function c_last_adj_currency_p return varchar2 is
4170 return c_last_adj_currency;
4171 END;
4172 Function c_last_adj_rel_invoice_p return varchar2 is
4173 Begin
4174 return c_last_adj_rel_invoice;
4175 END;
4176 Function c_last_adj_type_p return varchar2 is
4177 Begin
4178 return c_last_adj_type;
4179 END;
4180 Function c_last_adj_number_p return varchar2 is
4181 Begin
4182 return c_last_adj_number;
4183 END;
4184 Function c_last_wo_days_since_p return varchar2 is
4185 Begin
4186 return c_last_wo_days_since;
4187 END;
4188 Function c_last_wo_date_p return varchar2 is
4189 Begin
4190 return c_last_wo_date;
4191 END;
4192 Function c_last_wo_converted_p return varchar2 is
4193 Begin
4194 return c_last_wo_converted;
4195 END;
4196 Function c_last_wo_amount_p return number is
4197 Begin
4198 return c_last_wo_amount;
4199 END;
4200 Function c_last_wo_currency_p return varchar2 is
4201 Begin
4202 return c_last_wo_currency;
4203 END;
4204 Function c_last_wo_rel_invoice_p return varchar2 is
4205 Begin
4206 return c_last_wo_rel_invoice;
4207 END;
4208 Function c_last_wo_type_p return varchar2 is
4209 Begin
4210 return c_last_wo_type;
4211 END;
4212 Function c_last_wo_number_p return varchar2 is
4213 Begin
4214 return c_last_wo_number;
4215 END;
4216 Function c_last_stmnt_next_trx_date_p return varchar2 is
4217 Begin
4218 return c_last_stmnt_next_trx_date;
4219 END;
4220 Function c_last_st_date_p return varchar2 is
4221 Begin
4222 return c_last_st_date;
4223 END;
4224 Function c_last_st_type_p return varchar2 is
4225 Begin
4226 return c_last_st_type;
4227 END;
4228 Function c_last_st_number_p return varchar2 is
4229 Begin
4230 return c_last_st_number;
4231 END;
4232 Function c_last_st_days_since_p return varchar2 is
4233 Begin
4234 return c_last_st_days_since;
4235 END;
4236 Function c_last_dn_days_since_p return varchar2 is
4237 Begin
4238 return c_last_dn_days_since;
4239 END;
4240 Function c_last_dn_date_p return varchar2 is
4241 Begin
4242 return c_last_dn_date;
4243 END;
4244 Function c_last_dn_currency_p return varchar2 is
4245 Begin
4246 return c_last_dn_currency;
4247 END;
4248 Function c_last_dn_amount_p return number is
4249 Begin
4250 return c_last_dn_amount;
4251 END;
4252 Function c_last_dn_type_p return varchar2 is
4253 Begin
4254 return c_last_dn_type;
4255 END;
4256 Function c_last_dn_number_p return varchar2 is
4257 Begin
4258 return c_last_dn_number;
4259 END;
4260 Function c_last_nsf_currency_p return varchar2 is
4261 Begin
4262 return c_last_nsf_currency;
4263 END;
4264 Function c_last_nsf_days_since_p return varchar2 is
4265 Begin
4266 return c_last_nsf_days_since;
4267 END;
4268 Function c_last_nsf_date_p return varchar2 is
4269 Begin
4270 return c_last_nsf_date;
4271 END;
4272 Function c_last_nsf_converted_p return varchar2 is
4273 Begin
4274 return c_last_nsf_converted;
4275 END;
4276 Function c_last_nsf_amount_p return number is
4277 Begin
4278 return c_last_nsf_amount;
4279 END;
4280 Function c_last_nsf_type_p return varchar2 is
4281 Begin
4282 return c_last_nsf_type;
4283 END;
4284 Function c_last_nsf_number_p return varchar2 is
4285 Begin
4286 return c_last_nsf_number;
4287 END;
4288 Function c_last_contact_days_since_p return varchar2 is
4289 Begin
4290 return c_last_contact_days_since;
4291 END;
4292 Function c_last_contact_date_p return varchar2 is
4293 Begin
4294 return c_last_contact_date;
4295 END;
4296 Function c_last_contact_amount_p return number is
4297 Begin
4298 return c_last_contact_amount;
4299 END;
4300 Function c_last_contact_converted_p return varchar2 is
4301 Begin
4302 return c_last_contact_converted;
4303 END;
4304 Function c_last_contact_currency_p return varchar2 is
4305 Begin
4306 return c_last_contact_currency;
4307 END;
4308 Function c_last_contact_rel_invoice_p return varchar2 is
4309 Begin
4310 return c_last_contact_rel_invoice;
4311 END;
4312 Function c_last_contact_number_p return varchar2 is
4313 Begin
4314 return c_last_contact_number;
4315 END;
4316 Function c_last_hold_days_since_p return varchar2 is
4317 Begin
4318 return c_last_hold_days_since;
4319 END;
4320 Function c_last_hold_date_p return varchar2 is
4321 Begin
4322 return c_last_hold_date;
4323 END;
4324 Function c_last_hold_amount_p return number is
4325 Begin
4326 return c_last_hold_amount;
4327 END;
4328 Function c_last_hold_number_p return varchar2 is
4329 Begin
4330 return c_last_hold_number;
4331 END;
4332 Function CP_DEFAULT_FLAG_p return varchar2 is
4333 Begin
4334 return CP_DEFAULT_FLAG;
4335 END;
4336 Function c_percent_b0_p return varchar2 is
4337 Begin
4338 return c_percent_b0;
4339 END;
4340 Function c_percent_b1_p return varchar2 is
4341 Begin
4342 return c_percent_b1;
4343 END;
4344 Function c_percent_b2_p return varchar2 is
4345 Begin
4346 return c_percent_b2;
4347 END;
4348 Function c_percent_b3_p return varchar2 is
4349 Begin
4350 return c_percent_b3;
4351 END;
4352 Function c_percent_b4_p return varchar2 is
4353 Begin
4354 return c_percent_b4;
4355 END;
4356 Function c_percent_b5_p return varchar2 is
4360 Function c_percent_b6_p return varchar2 is
4357 Begin
4358 return c_percent_b5;
4359 END;
4361 Begin
4362 return c_percent_b6;
4363 END;
4364 Function c_aging_on_account_p return number is
4365 Begin
4366 return c_aging_on_account;
4367 END;
4368 Function c_aging_unapplied_p return number is
4369 Begin
4370 return c_aging_unapplied;
4371 END;
4372 Function c_aging_convert_on_account_p return varchar2 is
4373 Begin
4374 return c_aging_convert_on_account;
4375 END;
4376 Function c_aging_convert_unapplied_p return varchar2 is
4377 Begin
4378 return c_aging_convert_unapplied;
4379 END;
4380 Function c_aging_credit_p return number is
4381 Begin
4382 return c_aging_credit;
4383 END;
4384 Function c_aging_convert_credit_p return varchar2 is
4385 Begin
4386 return c_aging_convert_credit;
4387 END;
4388 Function c_adjusted_balance_p return number is
4389 Begin
4390 return c_adjusted_balance;
4391 END;
4392 Function c_aging_convert_collection_p return varchar2 is
4393 Begin
4394 return c_aging_convert_collection;
4395 END;
4396 Function c_aging_in_collection_p return number is
4397 Begin
4398 return c_aging_in_collection;
4399 END;
4400 Function c_cust_hist_high_invoice_amt_p return number is
4401 Begin
4402 return c_cust_hist_high_invoice_amt;
4403 END;
4404 --Function c_cust_hist_conv_high_invoice return varchar2 is
4405 Function c_cust_hist_conv_high_inv_p return varchar2 is
4406 Begin
4407 return c_cust_hist_conv_high_invoice;
4408 END;
4409 -- Function c_cust_hist_high_invoice_date return varchar2 is
4410 Function c_cust_hist_high_inv_date_p return varchar2 is
4411
4412 Begin
4413 return c_cust_hist_high_invoice_date;
4414 END;
4415 Function c_cust_hist_high_limit_date_p return date is
4416 Begin
4417 return c_cust_hist_high_limit_date;
4418 END;
4419 Function c_ytd_nsf_count_p return number is
4420 Begin
4421 return c_ytd_nsf_count;
4422 END;
4423 Function c_ytd_convert_nsf_p return varchar2 is
4424 Begin
4425 return c_ytd_convert_nsf;
4426 END;
4427 Function c_ytd_nsf_amount_p return number is
4428 Begin
4429 return c_ytd_nsf_amount;
4430 END;
4431 Function c_ytd_conv_unearned_discount_p return varchar2 is
4432 Begin
4433 return c_ytd_conv_unearned_discount;
4434 END;
4435 Function c_ytd_unearned_discount_amoun return number is
4436 Begin
4437 return c_ytd_unearned_discount_amount;
4438 END;
4439 --Function c_ytd_convert_earned_discount return varchar2 is
4440 Function c_ytd_convert_earned_dis_p return varchar2 is
4441 Begin
4442 return c_ytd_convert_earned_discount;
4443 END;
4444 Function c_ytd_earned_discount_amount_p return number is
4445 Begin
4446 return c_ytd_earned_discount_amount;
4447 END;
4448 Function c_ytd_on_time_payments_count_p return number is
4449 Begin
4450 return c_ytd_on_time_payments_count;
4451 END;
4452 Function c_ytd_late_payments_count_p return number is
4453 Begin
4454 return c_ytd_late_payments_count;
4455 END;
4456 Function c_ytd_average_days_late_p return number is
4457 Begin
4458 return c_ytd_average_days_late;
4459 END;
4460 Function c_ytd_average_payment_days_p return number is
4461 Begin
4462 return c_ytd_average_payment_days;
4463 END;
4464 Function c_ytd_finance_charge_count_p return number is
4465 Begin
4466 return c_ytd_finance_charge_count;
4467 END;
4468 Function c_ytd_convert_finance_charge_p return varchar2 is
4469 Begin
4470 return c_ytd_convert_finance_charge;
4471 END;
4472 Function c_ytd_finance_charge_amount_p return number is
4473 Begin
4474 return c_ytd_finance_charge_amount;
4475 END;
4476 Function c_ytd_credit_count_p return number is
4477 Begin
4478 return c_ytd_credit_count;
4479 END;
4480 Function c_ytd_convert_credit_p return varchar2 is
4481 Begin
4482 return c_ytd_convert_credit;
4483 END;
4484 Function c_ytd_credit_amount_p return number is
4485 Begin
4486 return c_ytd_credit_amount;
4487 END;
4488 Function c_ytd_payment_count_p return number is
4489 Begin
4490 return c_ytd_payment_count;
4491 END;
4492 Function c_ytd_convert_payment_p return varchar2 is
4493 Begin
4494 return c_ytd_convert_payment;
4495 END;
4496 Function c_ytd_payment_amount_p return number is
4497 Begin
4498 return c_ytd_payment_amount;
4499 END;
4500 Function c_ytd_sales_count_p return number is
4501 Begin
4502 return c_ytd_sales_count;
4503 END;
4504 Function c_ytd_convert_sales_p return varchar2 is
4505 Begin
4506 return c_ytd_convert_sales;
4507 END;
4508 Function c_ytd_sales_amount_p return number is
4509 Begin
4510 return c_ytd_sales_amount;
4511 END;
4512 Function c_ytd_convert_writeoff_p return varchar2 is
4513 Begin
4514 return c_ytd_convert_writeoff;
4515 END;
4516 Function c_ytd_writeoff_amount_p return number is
4517 Begin
4518 return c_ytd_writeoff_amount;
4519 END;
4520 Function CP_limit_currency_p return varchar2 is
4521 Begin
4522 return CP_limit_currency;
4523 END;
4524 Function CP_related_currencies_p return varchar2 is
4525 Begin
4526 return CP_related_currencies;
4527 END;
4528 Function CP_txn_cur_p return varchar2 is
4529 Begin
4530 return CP_txn_cur;
4531 END;
4532 Function c_cred_summ_convert_limit_p return varchar2 is
4533 Begin
4534 return c_cred_summ_convert_limit;
4535 END;
4536 Function c_cred_summ_exceeded_p return number is
4537 Begin
4541 Begin
4538 return c_cred_summ_exceeded;
4539 END;
4540 Function c_cred_summ_available_p return number is
4542 return c_cred_summ_available;
4543 END;
4544 Function CP_trx_amount_p return number is
4545 Begin
4546 return CP_trx_amount;
4547 END;
4548 Function CP_trx_curr_p return number is
4549 Begin
4550 return CP_trx_curr;
4551 END;
4552 Function CP_limit_curr_amt_p return number is
4553 Begin
4554 return CP_limit_curr_amt;
4555 END;
4556 Function CP_rate_p return number is
4557 Begin
4558 return CP_rate;
4559 END;
4560 Function CP_adjusted_amount_p return number is
4561 Begin
4562 return CP_adjusted_amount;
4563 END;
4564 Function CP_limit_curr_amt1_p return number is
4565 Begin
4566 return CP_limit_curr_amt1;
4567 END;
4568 Function CP_rate1_p return number is
4569 Begin
4570 return CP_rate1;
4571 END;
4572 Function CP_adjusted_amount1_p return number is
4573 Begin
4574 return CP_adjusted_amount1;
4575 END;
4576 Function C_cred_summ_exceeded2_p return number is
4577 Begin
4578 return C_cred_summ_exceeded2;
4579 END;
4580 Function C_cred_summ_available2_p return number is
4581 Begin
4582 return C_cred_summ_available2;
4583 END;
4584 Function C_cred_convert_limit2_p return varchar2 is
4585 Begin
4586 return C_cred_convert_limit2;
4587 END;
4588 Function CP_trx_amount2_p return number is
4589 Begin
4590 return CP_trx_amount2;
4591 END;
4592 Function CP_trx_cur2_p return varchar2 is
4593 Begin
4594 return CP_trx_cur2;
4595 END;
4596 Function C_org_credit_hold_p return varchar2 is
4597 Begin
4598 return C_org_credit_hold;
4599 END;
4600 Function C_org_credit_rating_p return varchar2 is
4601 Begin
4602 return C_org_credit_rating;
4603 END;
4604 Function C_Org_limit_tolerance_p return varchar2 is
4605 Begin
4606 return C_Org_limit_tolerance;
4607 END;
4608 Function C_credit_profile_id_p return number is
4609 Begin
4610 return C_credit_profile_id;
4611 END;
4612 Function CP_limit_curr_amt2_p return number is
4613 Begin
4614 return CP_limit_curr_amt2;
4615 END;
4616 Function CP_Rate2_p return number is
4617 Begin
4618 return CP_Rate2;
4619 END;
4620 Function CP_adjusted_amount2_p return number is
4621 Begin
4622 return CP_adjusted_amount2;
4623 END;
4624 Function CP_TRX_AMOUNT1_p return number is
4625 Begin
4626 return CP_TRX_AMOUNT1;
4627 END;
4628 Function C_CRED_SUMM_AVAILABLE1_p return number is
4629 Begin
4630 return C_CRED_SUMM_AVAILABLE1;
4631 END;
4632 Function C_CRED_CONVERT_LIMIT1_p return varchar2 is
4633 Begin
4634 return C_CRED_CONVERT_LIMIT1;
4635 END;
4636 Function C_CRED_SUMM_EXCEEDED1_p return number is
4637 Begin
4638 return C_CRED_SUMM_EXCEEDED1;
4639 END;
4640 Function CP_TRX_CUR1_p return varchar2 is
4641 Begin
4642 return CP_TRX_CUR1;
4643 END;
4644 Function DEFAULT_FLAG_p return varchar2 is
4645 Begin
4646 return DEFAULT_FLAG;
4647 END;
4648 Function LIMIT_CURRENCY_p return varchar2 is
4649 Begin
4650 return LIMIT_CURRENCY;
4651 END;
4652 Function RELATED_CURRENCIES_p return varchar2 is
4653 Begin
4654 return RELATED_CURRENCIES;
4655 END;
4656 Function RP_COMPANY_NAME_p return varchar2 is
4657 Begin
4658 return RP_COMPANY_NAME;
4659 END;
4660 Function RP_REPORT_NAME_p return varchar2 is
4661 Begin
4662 return RP_REPORT_NAME;
4663 END;
4664 Function RP_DATA_FOUND_p return varchar2 is
4665 Begin
4666 return RP_DATA_FOUND;
4667 END;
4668 Function RP_DATE_RANGE_p return varchar2 is
4669 Begin
4670 return RP_DATE_RANGE;
4671 END;
4672 Function RP_BUCKET_DAYS_FROM_0_p return number is
4673 Begin
4674 return RP_BUCKET_DAYS_FROM_0;
4675 END;
4676 Function RP_BUCKET_DAYS_FROM_1_p return number is
4677 Begin
4678 return RP_BUCKET_DAYS_FROM_1;
4679 END;
4680 Function RP_BUCKET_DAYS_FROM_2_p return number is
4681 Begin
4682 return RP_BUCKET_DAYS_FROM_2;
4683 END;
4684 Function RP_BUCKET_DAYS_FROM_3_p return number is
4685 Begin
4686 return RP_BUCKET_DAYS_FROM_3;
4687 END;
4688 Function RP_BUCKET_DAYS_FROM_4_p return number is
4689 Begin
4690 return RP_BUCKET_DAYS_FROM_4;
4691 END;
4692 Function RP_BUCKET_DAYS_FROM_5_p return number is
4693 Begin
4694 return RP_BUCKET_DAYS_FROM_5;
4695 END;
4696 Function RP_BUCKET_DAYS_TO_0_p return number is
4697 Begin
4698 return RP_BUCKET_DAYS_TO_0;
4699 END;
4700 Function RP_BUCKET_DAYS_TO_1_p return number is
4701 Begin
4702 return RP_BUCKET_DAYS_TO_1;
4703 END;
4704 Function RP_BUCKET_DAYS_TO_2_p return number is
4705 Begin
4706 return RP_BUCKET_DAYS_TO_2;
4707 END;
4708 Function RP_BUCKET_DAYS_TO_3_p return number is
4709 Begin
4710 return RP_BUCKET_DAYS_TO_3;
4711 END;
4712 Function RP_BUCKET_DAYS_TO_4_p return number is
4713 Begin
4714 return RP_BUCKET_DAYS_TO_4;
4715 END;
4716 Function RP_BUCKET_DAYS_TO_5_p return number is
4717 Begin
4718 return RP_BUCKET_DAYS_TO_5;
4719 END;
4720 Function RP_BUCKET_LINE_TYPE_0_p return varchar2 is
4721 Begin
4722 return RP_BUCKET_LINE_TYPE_0;
4723 END;
4724 Function RP_BUCKET_LINE_TYPE_1_p return varchar2 is
4725 Begin
4726 return RP_BUCKET_LINE_TYPE_1;
4727 END;
4728 Function RP_BUCKET_LINE_TYPE_2_p return varchar2 is
4729 Begin
4730 return RP_BUCKET_LINE_TYPE_2;
4731 END;
4735 END;
4732 Function RP_BUCKET_LINE_TYPE_3_p return varchar2 is
4733 Begin
4734 return RP_BUCKET_LINE_TYPE_3;
4736 Function RP_BUCKET_LINE_TYPE_4_p return varchar2 is
4737 Begin
4738 return RP_BUCKET_LINE_TYPE_4;
4739 END;
4740 Function RP_BUCKET_LINE_TYPE_5_p return varchar2 is
4741 Begin
4742 return RP_BUCKET_LINE_TYPE_5;
4743 END;
4744 Function RP_BUCKET_TITLE0_p return varchar2 is
4745 Begin
4746 return RP_BUCKET_TITLE0;
4747 END;
4748 Function RP_BUCKET_TITLE1_p return varchar2 is
4749 Begin
4750 return RP_BUCKET_TITLE1;
4751 END;
4752 Function RP_BUCKET_TITLE2_p return varchar2 is
4753 Begin
4754 return RP_BUCKET_TITLE2;
4755 END;
4756 Function RP_BUCKET_TITLE3_p return varchar2 is
4757 Begin
4758 return RP_BUCKET_TITLE3;
4759 END;
4760 Function RP_BUCKET_TITLE4_p return varchar2 is
4761 Begin
4762 return RP_BUCKET_TITLE4;
4763 END;
4764 Function RP_BUCKET_TITLE5_p return varchar2 is
4765 Begin
4766 return RP_BUCKET_TITLE5;
4767 END;
4768 Function RP_BUCKET_CATEGORY_p return varchar2 is
4769 Begin
4770 return RP_BUCKET_CATEGORY;
4771 END;
4772 Function RP_BUCKET_DAYS_FROM_6_p return number is
4773 Begin
4774 return RP_BUCKET_DAYS_FROM_6;
4775 END;
4776 Function RP_BUCKET_DAYS_TO_6_p return number is
4777 Begin
4778 return RP_BUCKET_DAYS_TO_6;
4779 END;
4780 Function RP_BUCKET_LINE_TYPE_6_p return varchar2 is
4781 Begin
4782 return RP_BUCKET_LINE_TYPE_6;
4783 END;
4784 Function RP_BUCKET_TITLE6_p return varchar2 is
4785 Begin
4786 return RP_BUCKET_TITLE6;
4787 END;
4788 Function C_industry_code_p return varchar2 is
4789 Begin
4790 return C_industry_code;
4791 END;
4792 Function C_sales_title_p return varchar2 is
4793 Begin
4794 return C_sales_title;
4795 END;
4796 Function RP_UNAVAILABLE_p return varchar2 is
4797 Begin
4798 return RP_UNAVAILABLE;
4799 END;
4800 Function RP_YEARS_p return varchar2 is
4801 Begin
4802 return RP_YEARS;
4803 END;
4804 Function RP_NONE_p return varchar2 is
4805 Begin
4806 return RP_NONE;
4807 END;
4808 Function RP_NA_UPPER_p return varchar2 is
4809 Begin
4810 return RP_NA_UPPER;
4811 END;
4812 Function RP_NO_LIMIT_p return varchar2 is
4813 Begin
4814 return RP_NO_LIMIT;
4815 END;
4816 Function RP_MESSAGE_p return varchar2 is
4817 Begin
4818 return RP_MESSAGE;
4819 END;
4820 END AR_ARXCCS_XMLP_PKG ;
4821
4822