[Home] [Help]
PACKAGE BODY: APPS.ARP_ARXVASUM2
Source
1 PACKAGE BODY ARP_ARXVASUM2 AS
2 /* $Header: ARCESU2B.pls 120.2 2005/10/30 04:14:07 appldev noship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 procedure get_amounts( p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
7 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
8 p_start_date IN gl_period_statuses.start_date%TYPE,
9 p_end_date IN gl_period_statuses.end_date%TYPE,
10 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
11 p_inv_count IN OUT NOCOPY NUMBER,
12 p_dm_count IN OUT NOCOPY NUMBER,
13 p_cb_count IN OUT NOCOPY NUMBER,
14 p_dep_count IN OUT NOCOPY NUMBER,
15 p_guar_count IN OUT NOCOPY NUMBER,
16 p_pmt_count IN OUT NOCOPY NUMBER,
17 p_cm_count IN OUT NOCOPY NUMBER,
18 p_risk_count IN OUT NOCOPY NUMBER,
19 p_br_count IN OUT NOCOPY NUMBER, /* 18-MAY-2000 J Rautiainen BR Implementation */
20 p_sum_inv_ori_amt IN OUT NOCOPY NUMBER,
21 p_sum_dm_ori_amt IN OUT NOCOPY NUMBER,
22 p_sum_cb_ori_amt IN OUT NOCOPY NUMBER,
23 p_sum_dep_ori_amt IN OUT NOCOPY NUMBER,
24 p_sum_guar_ori_amt IN OUT NOCOPY NUMBER,
25 p_sum_pmt_ori_amt IN OUT NOCOPY NUMBER,
26 p_sum_cm_ori_amt IN OUT NOCOPY NUMBER,
27 p_sum_risk_ori_amt IN OUT NOCOPY NUMBER,
28 p_sum_br_ori_amt IN OUT NOCOPY NUMBER, /* 18-MAY-2000 J Rautiainen BR Implementation */
29 p_sum_inv_rem_amt IN OUT NOCOPY NUMBER,
30 p_sum_dm_rem_amt IN OUT NOCOPY NUMBER,
31 p_sum_cb_rem_amt IN OUT NOCOPY NUMBER,
32 p_sum_dep_rem_amt IN OUT NOCOPY NUMBER,
33 p_sum_guar_rem_amt IN OUT NOCOPY NUMBER,
34 p_sum_pmt_rem_amt IN OUT NOCOPY NUMBER,
35 p_sum_cm_rem_amt IN OUT NOCOPY NUMBER,
36 p_sum_risk_rem_amt IN OUT NOCOPY NUMBER,
37 p_sum_br_rem_amt IN OUT NOCOPY NUMBER, /* 18-MAY-2000 J Rautiainen BR Implementation */
38 p_sum_inv_func_ori_amt IN OUT NOCOPY NUMBER,
39 p_sum_dm_func_ori_amt IN OUT NOCOPY NUMBER,
40 p_sum_cb_func_ori_amt IN OUT NOCOPY NUMBER,
41 p_sum_dep_func_ori_amt IN OUT NOCOPY NUMBER,
42 p_sum_guar_func_ori_amt IN OUT NOCOPY NUMBER,
43 p_sum_pmt_func_ori_amt IN OUT NOCOPY NUMBER,
44 p_sum_cm_func_ori_amt IN OUT NOCOPY NUMBER,
45 p_sum_risk_func_ori_amt IN OUT NOCOPY NUMBER,
46 p_sum_br_func_ori_amt IN OUT NOCOPY NUMBER, /* 18-MAY-2000 J Rautiainen BR Implementation */
47 p_sum_inv_func_rem_amt IN OUT NOCOPY NUMBER,
48 p_sum_dm_func_rem_amt IN OUT NOCOPY NUMBER,
49 p_sum_cb_func_rem_amt IN OUT NOCOPY NUMBER,
50 p_sum_dep_func_rem_amt IN OUT NOCOPY NUMBER,
51 p_sum_guar_func_rem_amt IN OUT NOCOPY NUMBER,
52 p_sum_pmt_func_rem_amt IN OUT NOCOPY NUMBER,
53 p_sum_cm_func_rem_amt IN OUT NOCOPY NUMBER,
54 p_sum_risk_func_rem_amt IN OUT NOCOPY NUMBER,
55 p_sum_br_func_rem_amt IN OUT NOCOPY NUMBER, /* 18-MAY-2000 J Rautiainen BR Implementation */
56 p_func_curr IN VARCHAR2,
57 p_exc_rate IN NUMBER,
58 p_precision IN NUMBER,
59 p_min_acc_unit IN NUMBER,
60 p_status IN ar_payment_schedules.status%TYPE,
61 p_incl_rct_spmenu IN VARCHAR2
62 ) IS
63 BEGIN
64 SELECT decode(p_currency_code,
65 NULL , NULL ,
66 nvl(sum(decode(ps.class, 'INV' , ps.amount_due_original, 0)),0)
67 ),
68 decode(p_currency_code,
69 NULL , NULL ,
70 nvl(sum(decode(ps.class, 'DM' , ps.amount_due_original, 0)),0)
71 ),
72 decode(p_currency_code,
73 NULL , NULL ,
74 nvl(sum(decode(ps.class, 'CB' , ps.amount_due_original, 0)),0)
75 ),
76 decode(p_currency_code,
77 NULL , NULL ,
78 nvl(sum(decode(ps.class, 'DEP' , ps.amount_due_original, 0)),0)
79 ),
80 decode(p_currency_code,
81 NULL , NULL ,
82 nvl(sum(decode(ps.class, 'GUAR' , ps.amount_due_original, 0)),0)
83 ),
84 decode(p_currency_code,
85 NULL , NULL ,
86 nvl(sum(decode(ps.class, 'CM' , ps.amount_due_original, 0)),0)
87 ),
88 /* 18-MAY-2000 J Rautiainen BR Implementation */
89 decode(p_currency_code,
90 NULL , NULL ,
91 nvl(sum(decode(ps.class, 'BR' , ps.amount_due_original, 0)),0)
92 ),
93 decode(p_currency_code,
94 NULL , NULL ,
95 nvl(sum(decode(ps.class, 'INV' , ps.amount_due_remaining, 0)),0)
96 ),
97 decode(p_currency_code,
98 NULL , NULL ,
99 nvl(sum(decode(ps.class, 'DM' , ps.amount_due_remaining, 0)),0)
100 ),
101 decode(p_currency_code,
102 NULL , NULL ,
103 nvl(sum(decode(ps.class, 'CB' , ps.amount_due_remaining, 0)),0)
104 ),
105 decode(p_currency_code,
106 NULL , NULL ,
107 nvl(sum(decode(ps.class, 'DEP' , ps.amount_due_remaining, 0)),0)
108 ),
109 decode(p_currency_code,
110 NULL , NULL ,
111 nvl(sum(decode(ps.class, 'GUAR' , ps.amount_due_remaining, 0)),0)
112 ),
113 decode(p_currency_code,
114 NULL , NULL ,
115 nvl(sum(decode(ps.class, 'CM' , ps.amount_due_remaining, 0)),0)
116 ),
117 /* 18-MAY-2000 J Rautiainen BR Implementation */
118 decode(p_currency_code,
119 NULL , NULL ,
120 nvl(sum(decode(ps.class, 'BR' , ps.amount_due_remaining, 0)),0)
121 ),
122 nvl(sum(decode(ps.class,
123 'INV' , arpcurr.functional_amount( ps.amount_due_original,
124 p_func_curr,
125 nvl(p_exc_rate,ps.exchange_rate),
126 p_precision,
127 p_min_acc_unit
128 ),
129 0)
130 ), 0),
131 nvl(sum(decode(ps.class,
132 'DM' , arpcurr.functional_amount( ps.amount_due_original,
133 p_func_curr,
134 nvl(p_exc_rate,ps.exchange_rate),
135 p_precision,
136 p_min_acc_unit
137 ),
138 0)
139 ), 0),
140 nvl(sum(decode(ps.class,
141 'CB' , arpcurr.functional_amount( ps.amount_due_original,
142 p_func_curr,
143 nvl(p_exc_rate, ps.exchange_rate),
144 p_precision,
145 p_min_acc_unit ), 0)), 0),
146 nvl(sum(decode(ps.class,
147 'DEP' , arpcurr.functional_amount( ps.amount_due_original,
148 p_func_curr,
149 nvl(p_exc_rate, ps.exchange_rate),
150 p_precision,
151 p_min_acc_unit ), 0)), 0),
152 nvl(sum(decode(ps.class,
153 'GUAR' , arpcurr.functional_amount( ps.amount_due_original,
154 p_func_curr,
155 nvl(p_exc_rate, ps.exchange_rate),
156 p_precision,
157 p_min_acc_unit ), 0)), 0),
158 nvl(sum(decode(ps.class,
159 'CM' , arpcurr.functional_amount( ps.amount_due_original,
160 p_func_curr,
161 nvl(p_exc_rate, ps.exchange_rate),
162 p_precision,
163 p_min_acc_unit ), 0)), 0),
164 /* 18-MAY-2000 J Rautiainen BR Implementation */
165 nvl(sum(decode(ps.class,
166 'BR' , arpcurr.functional_amount( ps.amount_due_original,
167 p_func_curr,
168 nvl(p_exc_rate, ps.exchange_rate),
169 p_precision,
170 p_min_acc_unit ), 0)), 0),
171 nvl(sum(decode(ps.class,
172 'INV' , decode(p_exc_rate,
173 NULL, ps.acctd_amount_due_remaining,
174 arpcurr.functional_amount( ps.amount_due_remaining,
175 p_func_curr,
176 p_exc_rate,
177 p_precision,
178 p_min_acc_unit )) ,0)), 0),
179 nvl(sum(decode(ps.class,
180 'DM' , decode(p_exc_rate,
181 NULL, ps.acctd_amount_due_remaining,
182 arpcurr.functional_amount( ps.amount_due_remaining,
183 p_func_curr,
184 p_exc_rate,
185 p_precision,
186 p_min_acc_unit ) ), 0)), 0),
187 nvl(sum(decode(ps.class,
188 'CB' , decode(p_exc_rate,
189 NULL, ps.acctd_amount_due_remaining,
190 arpcurr.functional_amount( ps.amount_due_remaining,
191 p_func_curr,
192 p_exc_rate,
193 p_precision,
194 p_min_acc_unit )), 0)), 0),
195 nvl(sum(decode(ps.class,
196 'DEP' , decode(p_exc_rate,
197 NULL, ps.acctd_amount_due_remaining,
198 arpcurr.functional_amount( ps.amount_due_remaining,
199 p_func_curr,
200 p_exc_rate,
201 p_precision,
202 p_min_acc_unit )), 0)), 0),
203 nvl(sum(decode(ps.class,
204 'GUAR' , decode(p_exc_rate,
205 NULL, ps.acctd_amount_due_remaining,
206 arpcurr.functional_amount( ps.amount_due_remaining,
207 p_func_curr,
208 p_exc_rate,
209 p_precision,
210 p_min_acc_unit )), 0)), 0),
211 nvl(sum(decode(ps.class,
212 'CM' , decode(p_exc_rate,
213 NULL, ps.acctd_amount_due_remaining,
214 arpcurr.functional_amount( ps.amount_due_remaining,
215 p_func_curr,
216 p_exc_rate,
217 p_precision,
218 p_min_acc_unit )), 0)), 0),
219 /* 18-MAY-2000 J Rautiainen BR Implementation */
220 nvl(sum(decode(ps.class,
221 'BR' , decode(p_exc_rate,
222 NULL, ps.acctd_amount_due_remaining,
223 arpcurr.functional_amount( ps.amount_due_remaining,
224 p_func_curr,
225 p_exc_rate,
226 p_precision,
227 p_min_acc_unit )), 0)), 0),
228 nvl(sum(decode(ps.class,
229 'INV' , 1, 0)),0),
230 nvl(sum(decode(ps.class,
231 'DM' , 1, 0)),0),
232 nvl(sum(decode(ps.class,
233 'CB' , 1, 0)),0),
234 nvl(sum(decode(ps.class,
235 'DEP' , 1, 0)),0),
236 nvl(sum(decode(ps.class,
237 'GUAR' , 1, 0)),0),
238 nvl(sum(decode(ps.class,
239 'CM' , 1, 0)),0),
240 /* 18-MAY-2000 J Rautiainen BR Implementation */
241 nvl(sum(decode(ps.class,
242 'BR' , 1, 0)),0)
243 into p_sum_inv_ori_amt,
244 p_sum_dm_ori_amt,
245 p_sum_cb_ori_amt,
246 p_sum_dep_ori_amt,
247 p_sum_guar_ori_amt,
248 p_sum_cm_ori_amt,
249 /* 18-MAY-2000 J Rautiainen BR Implementation */
250 p_sum_br_ori_amt,
251 p_sum_inv_rem_amt,
252 p_sum_dm_rem_amt,
253 p_sum_cb_rem_amt,
254 p_sum_dep_rem_amt,
255 p_sum_guar_rem_amt,
256 p_sum_cm_rem_amt,
257 /* 18-MAY-2000 J Rautiainen BR Implementation */
258 p_sum_br_rem_amt,
259 p_sum_inv_func_ori_amt,
260 p_sum_dm_func_ori_amt,
261 p_sum_cb_func_ori_amt,
262 p_sum_dep_func_ori_amt,
263 p_sum_guar_func_ori_amt,
264 p_sum_cm_func_ori_amt,
265 /* 18-MAY-2000 J Rautiainen BR Implementation */
266 p_sum_br_func_ori_amt,
267 p_sum_inv_func_rem_amt,
268 p_sum_dm_func_rem_amt,
269 p_sum_cb_func_rem_amt,
270 p_sum_dep_func_rem_amt,
271 p_sum_guar_func_rem_amt,
272 p_sum_cm_func_rem_amt,
273 /* 18-MAY-2000 J Rautiainen BR Implementation */
274 p_sum_br_func_rem_amt,
275 p_inv_count,
276 p_dm_count,
277 p_cb_count,
278 p_dep_count,
279 p_guar_count,
280 p_cm_count,
281 /* 18-MAY-2000 J Rautiainen BR Implementation */
282 p_br_count
283 from ar_payment_schedules ps
284 where nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
285 and ps.gl_date between p_start_date and p_end_date
286 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
287 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
288 and ps.cash_receipt_id is NULL
289 and ps.status = nvl(p_status, ps.status);
290 /*===================================================================================+
291 | The above procedure is now split into two. The top SQL would fetch totals and |
292 | counts for transactions of type "INV", "GUAR", "CM", "DEP", "DM" and "CB". The |
293 | SQL below would fetch total and counts for transaction type of "PMT" and would |
294 | reject a "PMT" if it is REVERSED. Required as a Bug Fix : 486920 |
295 | |
296 +===================================================================================*/
297
298 select decode(p_currency_code,
299 NULL , NULL ,
300 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
301 ), /* Sum of Original Amount */
302 decode(p_currency_code,
303 NULL , NULL ,
304 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
305 ), /* Sum of Amount Due Remaining */
306 nvl(sum(decode(ps.class,
307 'PMT', arpcurr.functional_amount( ps.amount_due_original,
308 p_func_curr,
309 nvl(p_exc_rate, ps.exchange_rate),
310 p_precision,
311 p_min_acc_unit
312 ),
313 0)), 0), /* Sum of Functional Original Amount */
314 nvl(sum(decode(ps.class,
315 'PMT', decode(p_exc_rate,
316 NULL, ps.acctd_amount_due_remaining,
317 arpcurr.functional_amount( ps.amount_due_remaining,
318 p_func_curr,
319 p_exc_rate,
320 p_precision,
321 p_min_acc_unit
322 )
323 ), 0)
324 ), 0), /* Sum of Functional Amount Due Remaining */
325 nvl(sum(decode(ps.class,
326 'PMT' , 1, 0
327 )
328 ),0), /* Count of Receipts */
329 NULL,
330 NULL,
331 0,
332 0,
333 0
334 into p_sum_pmt_ori_amt,
335 p_sum_pmt_rem_amt,
336 p_sum_pmt_func_ori_amt,
337 p_sum_pmt_func_rem_amt,
338 p_pmt_count,
339 p_sum_risk_ori_amt,
340 p_sum_risk_rem_amt,
341 p_sum_risk_func_ori_amt,
342 p_sum_risk_func_rem_amt,
343 p_risk_count
344 from ar_cash_receipts cr,
345 ar_payment_schedules ps
346 where ps.cash_receipt_id = cr.cash_receipt_id
347 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
348 and ps.gl_date between p_start_date and p_end_date
349 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
350 and (nvl(cr.reversal_category, cr.status||'X') <> cr.status OR
351 (nvl(cr.reversal_category, cr.status||'X') = cr.status AND
352 'Y' = (SELECT 'Y'
353 FROM ar_payment_schedules PS_DM,
354 ra_cust_trx_types CTT_DM,
355 ra_customer_trx CT_DM,
356 ra_cust_trx_line_gl_dist DM_GLD
357 WHERE PS_DM.reversed_cash_receipt_id = cr.cash_receipt_id
358 AND PS_DM.class = 'DM'
359 AND PS_DM.cust_trx_type_id = CTT_DM.cust_trx_type_id
360 AND PS_DM.customer_trx_id = CT_DM.customer_trx_id
361 AND DM_GLD.customer_trx_id = PS_DM.customer_trx_id
362 AND DM_GLD.account_class = 'REC'
363 AND DM_GLD.latest_rec_flag = 'Y')))
364 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
365 and ps.status = nvl(p_status, ps.status);
366
367 /* If include receipts at risk special menu is Y, find out NOCOPY amounts
368 for receipts at risk */
369 IF p_incl_rct_spmenu = 'Y' THEN
370 select decode(p_currency_code,
371 NULL , NULL ,
372 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
373 ), /* Sum of Original Amount */
374 decode(p_currency_code,
375 NULL , NULL ,
376 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
377 ), /* Sum of Amount Due Remaining */
378 nvl(sum(decode(ps.class,
379 'PMT', arpcurr.functional_amount( ps.amount_due_original,
380 p_func_curr,
381 nvl(p_exc_rate, ps.exchange_rate),
382 p_precision,
383 p_min_acc_unit
384 ),
385 0)), 0), /* Sum of Functional Original Amount */
386 nvl(sum(decode(ps.class,
387 'PMT', decode(p_exc_rate,
388 NULL, ps.acctd_amount_due_remaining,
389 arpcurr.functional_amount( ps.amount_due_remaining,
390 p_func_curr,
391 p_exc_rate,
392 p_precision,
393 p_min_acc_unit
394 )
395 ), 0)
396 ), 0), /* Sum of Functional Amount Due Remaining */
397 nvl(sum(decode(ps.class,
398 'PMT' , 1, 0
399 )
400 ),0) /* Count of Receipts */
401 into p_sum_risk_ori_amt,
402 p_sum_risk_rem_amt,
403 p_sum_risk_func_ori_amt,
404 p_sum_risk_func_rem_amt,
405 p_risk_count
406 from ar_cash_receipts cr,
407 ar_payment_schedules ps,
408 ar_cash_receipt_history crh
409 where ps.cash_receipt_id = cr.cash_receipt_id
410 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
411 and ps.gl_date between p_start_date and p_end_date
412 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
413 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
414 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
415 and ps.status = nvl(p_status, ps.status)
416 and cr.cash_receipt_id = crh.cash_receipt_id
417 and crh.current_record_flag||'' = 'Y'
418 and crh.status not in (decode (crh.factor_flag,
419 'Y', 'RISK_ELIMINATED',
420 'N', 'CLEARED'), 'REVERSED')
421 /* 06-AUG-2000 J Rautiainen BR Implementation
422 * Short term debt applications are not considered as receipts at risk */
423 and not exists (select 'X'
424 from ar_receivable_applications rap
425 where rap.cash_receipt_id = cr.cash_receipt_id
426 and rap.applied_payment_schedule_id = -2
427 and rap.display = 'Y');
428
429 END IF;
430 -- arp_standard.enable_debug;
431 EXCEPTION
432 WHEN OTHERS THEN
433 arp_standard.debug( 'Exception:');
434
435 end;
436
437
438 procedure get_payments_ontime(
439 p_payments_late_count IN OUT NOCOPY NUMBER,
440 p_payments_ontime_count IN OUT NOCOPY NUMBER,
441 p_payments_late_amount IN OUT NOCOPY NUMBER,
442 p_payments_ontime_amount IN OUT NOCOPY NUMBER,
443 p_payments_late_func_amt IN OUT NOCOPY NUMBER,
444 p_payments_ontime_func_amt IN OUT NOCOPY NUMBER,
445 p_start_date IN gl_period_statuses.start_date%TYPE,
446 p_end_date IN gl_period_statuses.end_date%TYPE,
447 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
448 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
449 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
450 p_func_curr IN VARCHAR2,
451 p_exc_rate IN NUMBER,
452 p_precision IN NUMBER,
453 p_min_acc_unit IN NUMBER,
454 p_status IN ar_payment_schedules.status%TYPE
455 ) is
456 begin
457
458 select nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 1,0)),0),
459 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,1)),0),
460 decode(p_currency_code,
461 NULL , NULL ,
462 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, ra.amount_applied,0)),0)),
463 decode(p_currency_code,
464 NULL , NULL ,
465 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,ra.amount_applied)),0)),
466 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, decode(p_exc_rate,
467 NULL, ra.acctd_amount_applied_from,
468 arpcurr.functional_amount(ra.amount_applied,
469 p_func_curr,
470 p_exc_rate,
471 p_precision,
472 p_min_acc_unit
473 )),0
474 )),0),
475 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0, decode(p_exc_rate,
476 NULL, ra.acctd_amount_applied_from,
477 arpcurr.functional_amount(ra.amount_applied,
478 p_func_curr,
479 p_exc_rate,
480 p_precision,
481 p_min_acc_unit
482 ))
483 )),0)
484 into p_payments_late_count,
485 p_payments_ontime_count,
486 p_payments_late_amount,
487 p_payments_ontime_amount,
488 p_payments_late_func_amt,
489 p_payments_ontime_func_amt
490 from ar_receivable_applications ra,
491 ar_payment_schedules ps
492 where ra.applied_payment_schedule_id = ps.payment_schedule_id
493 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
494 AND ra.apply_date between p_start_date and p_end_date
495 and ra.status = 'APP'
496 and ra.display = 'Y'
497 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
498 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
499 AND ps.status = nvl(p_status, ps.status);
500
501 --arp_standard.enable_debug;
502 EXCEPTION
503 WHEN OTHERS THEN
504 arp_standard.debug( 'Exception:');
505 end;
506
507
508 procedure get_nsf_stop(
509 p_nsf_stop_amount IN OUT NOCOPY NUMBER,
510 p_nsf_stop_func_amt IN OUT NOCOPY NUMBER,
511 p_nsf_stop_count IN OUT NOCOPY NUMBER,
512 p_start_date IN gl_period_statuses.start_date%TYPE,
513 p_end_date IN gl_period_statuses.end_date%TYPE,
514 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
515 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
516 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
517 p_func_curr IN VARCHAR2,
518 p_exc_rate IN NUMBER,
519 p_precision IN NUMBER,
520 p_min_acc_unit NUMBER,
521 p_status IN ar_payment_schedules.status%TYPE
522 ) is
523 begin
524 SELECT decode(p_currency_code,
525 NULL , NULL ,
526 nvl(sum(cr.amount),0)
527 ),
528 nvl(sum(arpcurr.functional_amount( cr.amount,
529 p_func_curr,
530 nvl(p_exc_rate,ps.exchange_rate),
531 p_precision,
532 p_min_acc_unit
533 )
534 ),0),
535 count(cr.amount)
536 INTO p_nsf_stop_amount,
537 p_nsf_stop_func_amt,
538 p_nsf_stop_count
539 FROM ar_cash_receipts cr,
540 ar_payment_schedules ps
541 WHERE ps.gl_date between p_start_date and p_end_date
542 AND ps.cash_receipt_id = cr.cash_receipt_id
543 AND cr.reversal_category in ('NSF','STOP')
544 and nvl(cr.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(cr.customer_site_use_id, -10) )
545 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
546 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
547 AND ps.status = nvl(p_status, ps.status);
548
549 --arp_standard.enable_debug;
550 EXCEPTION
551 WHEN OTHERS THEN
552 arp_standard.debug( 'Exception:');
553 end;
554
555
556 procedure get_adjustments(
557 p_adjustment_amount IN OUT NOCOPY NUMBER,
558 p_adjustment_func_amt IN OUT NOCOPY NUMBER,
559 p_adjustment_count IN OUT NOCOPY NUMBER,
560 p_start_date IN gl_period_statuses.start_date%TYPE,
561 p_end_date IN gl_period_statuses.end_date%TYPE,
562 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
563 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
564 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
565 p_func_curr IN VARCHAR2,
566 p_exc_rate IN NUMBER,
567 p_precision IN NUMBER,
568 p_min_acc_unit IN NUMBER,
569 p_status IN ar_payment_schedules.status%TYPE
570 ) is
571 begin
572 select decode(p_currency_code,
573 NULL , NULL , nvl(sum(a.amount),0)
574 ),
575 nvl(sum( decode(p_exc_rate,
576 NULL, a.acctd_amount,
577 arpcurr.functional_amount(a.amount,
578 p_func_curr,
579 p_exc_rate,
580 p_precision,
581 p_min_acc_unit
582 )
583 )),0),
584 count(a.amount)
585 into p_adjustment_amount,
586 p_adjustment_func_amt,
587 p_adjustment_count
588 from ar_adjustments a,
589 ar_receivables_trx rt,
590 ar_payment_schedules ps
591 where a.gl_date between p_start_date and p_end_date
592 and nvl(a.postable,'Y') = 'Y'
593 and a.payment_schedule_id = ps.payment_schedule_id
594 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
595 and a.receivables_trx_id = rt.receivables_trx_id
596 and nvl(rt.type,'X') <> 'FINCHRG'
597 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
598 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
599 AND ps.status = nvl(p_status, ps.status);
600
601 --arp_standard.enable_debug;
602 EXCEPTION
603 WHEN OTHERS THEN
604 arp_standard.debug( 'Exception:');
605 end;
606
607
608
609 procedure get_financecharg(
610 p_financecharg_amount IN OUT NOCOPY NUMBER,
611 p_financecharg_func_amt IN OUT NOCOPY NUMBER,
612 p_financecharg_count IN OUT NOCOPY NUMBER,
613 p_start_date IN gl_period_statuses.start_date%TYPE,
614 p_end_date IN gl_period_statuses.end_date%TYPE,
615 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
616 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
617 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
618 p_func_curr IN VARCHAR2,
619 p_exc_rate IN NUMBER,
620 p_precision IN NUMBER,
621 p_min_acc_unit IN NUMBER,
622 p_status IN ar_payment_schedules.status%TYPE
623 ) is
624 begin
625 select decode(p_currency_code,
626 NULL , NULL , nvl(sum(a.amount),0)
627 ),
628 nvl(sum( decode(p_exc_rate,
629 NULL, a.acctd_amount,
630 arpcurr.functional_amount(a.amount,
631 p_func_curr,
632 p_exc_rate,
633 p_precision,
634 p_min_acc_unit
635 )
636 )),0),
637 count(a.amount)
638 into p_financecharg_amount,
639 p_financecharg_func_amt,
640 p_financecharg_count
641 from ar_adjustments a,
642 ar_receivables_trx rt,
643 ar_payment_schedules ps
644 where a.gl_date between p_start_date and p_end_date
645 and nvl(a.postable,'Y') = 'Y'
646 and a.payment_schedule_id = ps.payment_schedule_id
647 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
648 and a.receivables_trx_id = rt.receivables_trx_id
649 and nvl(rt.type,'X') = 'FINCHRG'
650 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
651 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
652 AND ps.status = nvl(p_status, ps.status);
653
654 --arp_standard.enable_debug;
655 EXCEPTION
656 WHEN OTHERS THEN
657 arp_standard.debug( 'Exception:');
658 end;
659
660
661 procedure get_discounts(
662 p_earned_discounts IN OUT NOCOPY NUMBER,
663 p_unearned_discounts IN OUT NOCOPY NUMBER,
664 p_earned_func_disc IN OUT NOCOPY NUMBER,
665 p_unearned_func_disc IN OUT NOCOPY NUMBER,
666 p_earned_disc_count IN OUT NOCOPY NUMBER,
667 p_unearned_disc_count IN OUT NOCOPY NUMBER,
668 p_start_date IN gl_period_statuses.start_date%TYPE,
669 p_end_date IN gl_period_statuses.end_date%TYPE,
670 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
671 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
672 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
673 p_func_curr IN VARCHAR2,
674 p_exc_rate IN NUMBER,
675 p_precision IN NUMBER,
676 p_min_acc_unit IN NUMBER,
677 p_status IN ar_payment_schedules.status%TYPE
678 ) is
679 begin
680 SELECT decode(p_currency_code,
681 NULL , NULL ,
682 nvl(sum(ra.earned_discount_taken),0)
683 ),
684 decode(p_currency_code,
685 NULL , NULL ,
686 nvl(sum(ra.unearned_discount_taken),0)
687 ),
688 nvl(sum( decode(p_exc_rate,
689 NULL, ra.acctd_earned_discount_taken,
690 arpcurr.functional_amount( nvl(ra.earned_discount_taken,0),
691 p_func_curr,
692 p_exc_rate, p_precision,
693 p_min_acc_unit
694 )
695 )
696 ),0),
697 nvl(sum( decode(p_exc_rate,
698 NULL, ra.acctd_unearned_discount_taken,
699 arpcurr.functional_amount( nvl(ra.unearned_discount_taken,0),
700 p_func_curr,
701 p_exc_rate,
702 p_precision,
703 p_min_acc_unit
704 )
705 )),0),
706 count(decode(ra.earned_discount_taken,
707 0, NULL,
708 ra.earned_discount_taken
709 )
710 ),
711 count(decode(ra.unearned_discount_taken,
712 0, NULL,
713 ra.unearned_discount_taken
714 )
715 )
716 INTO p_earned_discounts,
717 p_unearned_discounts,
718 p_earned_func_disc,
719 p_unearned_func_disc,
720 p_earned_disc_count,
721 p_unearned_disc_count
722 FROM ar_receivable_applications ra,
723 ar_payment_schedules ps
724 where ra.gl_date between p_start_date and p_end_date
725 and ps.payment_schedule_id = ra.applied_payment_schedule_id
726 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
727 and ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
728 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
729 and ps.status = nvl(p_status, ps.status);
730
731 --arp_standard.enable_debug;
732 EXCEPTION
733 WHEN OTHERS THEN
734 arp_standard.debug( 'Exception:');
735 end;
736
737
738 /* BOE: get information about receipts that are waiting to be confirmed */
739 procedure get_pending_confirmation(
740 p_pend_confirm_amt IN OUT NOCOPY NUMBER,
741 p_pend_confirm_func_amt IN OUT NOCOPY NUMBER,
742 p_pend_confirm_count IN OUT NOCOPY NUMBER,
743 p_start_date IN gl_period_statuses.start_date%TYPE,
744 p_end_date IN gl_period_statuses.end_date%TYPE,
745 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
746 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
747 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
748 p_func_curr IN VARCHAR2,
749 p_exc_rate IN NUMBER,
750 p_precision IN NUMBER,
751 p_min_acc_unit IN NUMBER,
752 p_status IN ar_payment_schedules.status%TYPE
753 ) is
754 begin
755 select decode(p_currency_code,
756 NULL , NULL ,
757 nvl(sum(cr.amount),0)
758 ),
759 nvl(sum(arpcurr.functional_amount( cr.amount,
760 p_func_curr,
761 nvl(p_exc_rate,ps.exchange_rate),
762 p_precision,
763 p_min_acc_unit
764 )
765 ),0),
766 count(cr.amount)
767 into p_pend_confirm_amt,
768 p_pend_confirm_func_amt,
769 p_pend_confirm_count
770 from ar_payment_schedules ps,
771 ar_cash_receipts cr,
772 ar_cash_receipt_history crh
773 where ps.cash_receipt_id = cr.cash_receipt_id
774 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id,
775 -10) )
776 and ps.gl_date between p_start_date and p_end_date
777 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
778 and ps.status = nvl(p_status, ps.status)
779 and cr.cash_receipt_id = crh.cash_receipt_id
780 and crh.current_record_flag||'' = 'Y'
781 and crh.status = 'APPROVED';
782
783 EXCEPTION
784 WHEN OTHERS THEN
785 arp_standard.debug( 'Exception:');
786 end;
787
788
789 /* BOE: get information about receipts that are waiting to be remitted,
790 separate receipts into BOE, notes receivable and others */
791 procedure get_pending_remit(
792 p_boe_ori_amt IN OUT NOCOPY NUMBER,
793 p_boe_func_ori_amt IN OUT NOCOPY NUMBER,
794 p_boe_rem_amt IN OUT NOCOPY NUMBER,
795 p_boe_func_rem_amt IN OUT NOCOPY NUMBER,
796 p_boe_count IN OUT NOCOPY NUMBER,
797 p_note_ori_amt IN OUT NOCOPY NUMBER,
798 p_note_func_ori_amt IN OUT NOCOPY NUMBER,
799 p_note_rem_amt IN OUT NOCOPY NUMBER,
800 p_note_func_rem_amt IN OUT NOCOPY NUMBER,
801 p_note_count IN OUT NOCOPY NUMBER,
802 p_other_ori_amt IN OUT NOCOPY NUMBER,
803 p_other_func_ori_amt IN OUT NOCOPY NUMBER,
804 p_other_rem_amt IN OUT NOCOPY NUMBER,
805 p_other_func_rem_amt IN OUT NOCOPY NUMBER,
806 p_other_count IN OUT NOCOPY NUMBER,
807 p_start_date IN gl_period_statuses.start_date%TYPE,
808 p_end_date IN gl_period_statuses.end_date%TYPE,
809 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
810 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
811 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
812 p_func_curr IN VARCHAR2,
813 p_exc_rate IN NUMBER,
814 p_precision IN NUMBER,
815 p_min_acc_unit IN NUMBER,
816 p_status IN ar_payment_schedules.status%TYPE,
817 p_incl_rct_spmenu IN VARCHAR2
818 ) is
819 l_ori_amount number;
820 l_func_ori_amt number;
821 l_rem_amount number;
822 l_func_rem_amt number;
823 l_count number;
824 l_counter number := 0;
825 l_type varchar2(8);
826 begin
827 IF p_incl_rct_spmenu = 'Y' THEN /* Calculate different pending remittance amounts
828 only if include receipts at risk special menu
829 is checked. */
830 WHILE l_counter < 3 LOOP
831 IF l_counter = 0 THEN
832 l_type := 'BOE';
833 ELSIF l_counter = 1 THEN
834 l_type := 'NOTES';
835 ELSE
836 l_type := 'OTHER';
837 END IF;
838
839 select decode(p_currency_code,
840 NULL , NULL ,
841 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
842 ), /* Sum of Original Amount */
843 decode(p_currency_code,
844 NULL , NULL ,
845 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
846 ), /* Sum of Amount Due Remaining */
847 nvl(sum(decode(ps.class,
848 'PMT', arpcurr.functional_amount( ps.amount_due_original,
849 p_func_curr,
850 nvl(p_exc_rate, ps.exchange_rate),
851 p_precision,
852 p_min_acc_unit
853 ),
854 0)), 0), /* Sum of Functional Original Amount */
855 nvl(sum(decode(ps.class,
856 'PMT', decode(p_exc_rate,
857 NULL, ps.acctd_amount_due_remaining,
858 arpcurr.functional_amount( ps.amount_due_remaining,
859 p_func_curr,
860 p_exc_rate,
861 p_precision,
862 p_min_acc_unit
863 )
864 ), 0)
865 ), 0), /* Sum of Functional Amount Due Remaining */
866 nvl(sum(decode(ps.class,
867 'PMT' , 1, 0
868 )
869 ),0) /* Count of Receipts */
870 into l_ori_amount,
871 l_rem_amount,
872 l_func_ori_amt,
873 l_func_rem_amt,
874 l_count
875 from ar_cash_receipts cr,
876 ar_payment_schedules ps,
877 ar_cash_receipt_history crh,
878 ar_receipt_methods rm,
879 ar_receipt_classes rc
880 where ps.cash_receipt_id = cr.cash_receipt_id
881 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
882 and ps.gl_date between p_start_date and p_end_date
883 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
884 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
885 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
886 and ps.status = nvl(p_status, ps.status)
887 and cr.cash_receipt_id = crh.cash_receipt_id
888 and crh.current_record_flag||'' = 'Y'
889 and crh.status = 'CONFIRMED'
890 and cr.receipt_method_id = rm.receipt_method_id
891 and rm.receipt_class_id = rc.receipt_class_id
892 and nvl(rc.bill_of_exchange_flag, 'N') = decode(l_type, 'BOE', 'Y', 'N')
893 and nvl(rc.notes_receivable, 'N') = decode(l_type, 'NOTES', 'Y', 'N');
894
895 IF l_type = 'BOE' THEN
896 p_boe_ori_amt := l_ori_amount * -1;
897 p_boe_rem_amt := l_rem_amount * -1;
898 p_boe_func_ori_amt := l_func_ori_amt * -1;
899 p_boe_func_rem_amt := l_func_rem_amt * -1;
900 p_boe_count := l_count;
901 ELSIF l_type = 'NOTES' THEN
902 p_note_ori_amt := l_ori_amount * -1;
903 p_note_rem_amt := l_rem_amount * -1;
904 p_note_func_ori_amt := l_func_ori_amt * -1;
905 p_note_func_rem_amt := l_func_rem_amt * -1;
906 p_note_count := l_count;
907 ELSE
908 p_other_ori_amt := l_ori_amount * -1;
909 p_other_rem_amt := l_rem_amount * -1;
910 p_other_func_ori_amt := l_func_ori_amt * -1;
911 p_other_func_rem_amt := l_func_rem_amt * -1;
912 p_other_count := l_count;
913 END IF;
914
915 l_counter := l_counter + 1;
916 END LOOP;
917 ELSE
918 p_boe_ori_amt := NULL;
919 p_boe_rem_amt := NULL;
920 p_boe_func_ori_amt := 0;
921 p_boe_func_rem_amt := 0;
922 p_boe_count := 0;
923 p_note_ori_amt := NULL;
924 p_note_rem_amt := NULL;
925 p_note_func_ori_amt := 0;
926 p_note_func_rem_amt := 0;
927 p_note_count := 0;
928 p_other_ori_amt := NULL;
929 p_other_rem_amt := NULL;
930 p_other_func_ori_amt := 0;
931 p_other_func_rem_amt := 0;
932 p_other_count := 0;
933 END IF;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 arp_standard.debug( 'Exception:');
938 end;
939
940
941 /* BOE: get information about remitted receipts that are not cleared */
942 procedure get_remitted(
943 p_standard_ori_amt IN OUT NOCOPY NUMBER,
944 p_standard_func_ori_amt IN OUT NOCOPY NUMBER,
945 p_standard_rem_amt IN OUT NOCOPY NUMBER,
946 p_standard_func_rem_amt IN OUT NOCOPY NUMBER,
947 p_standard_count IN OUT NOCOPY NUMBER,
948 p_factored_ori_amt IN OUT NOCOPY NUMBER,
949 p_factored_func_ori_amt IN OUT NOCOPY NUMBER,
950 p_factored_rem_amt IN OUT NOCOPY NUMBER,
951 p_factored_func_rem_amt IN OUT NOCOPY NUMBER,
952 p_factored_count IN OUT NOCOPY NUMBER,
953 p_start_date IN gl_period_statuses.start_date%TYPE,
954 p_end_date IN gl_period_statuses.end_date%TYPE,
955 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
956 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
957 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
958 p_func_curr IN VARCHAR2,
959 p_exc_rate IN NUMBER,
960 p_precision IN NUMBER,
961 p_min_acc_unit IN NUMBER,
962 p_status IN ar_payment_schedules.status%TYPE,
963 p_incl_rct_spmenu IN VARCHAR2
964 ) is
965 l_ori_amount number;
966 l_func_ori_amt number;
967 l_rem_amount number;
968 l_func_rem_amt number;
969 l_count number;
970 l_counter number := 0;
971 l_type varchar2(10);
972 begin
973 IF p_incl_rct_spmenu = 'Y' THEN /* Calculate different pending remittance amounts
974 only if include receipts at risk special menu
975 is checked. */
976 WHILE l_counter < 2 LOOP
977 IF l_counter = 0 THEN
978 l_type := 'STANDARD';
979 ELSIF l_counter = 1 THEN
980 l_type := 'FACTORED';
981 END IF;
982
983 select decode(p_currency_code,
984 NULL , NULL ,
985 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
986 ), /* Sum of Original Amount */
987 decode(p_currency_code,
988 NULL , NULL ,
989 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
990 ), /* Sum of Amount Due Remaining */
991 nvl(sum(decode(ps.class,
992 'PMT', arpcurr.functional_amount( ps.amount_due_original,
993 p_func_curr,
994 nvl(p_exc_rate, ps.exchange_rate),
995 p_precision,
996 p_min_acc_unit
997 ),
998 0)), 0), /* Sum of Functional Original Amount */
999 nvl(sum(decode(ps.class,
1000 'PMT', decode(p_exc_rate,
1001 NULL, ps.acctd_amount_due_remaining,
1002 arpcurr.functional_amount( ps.amount_due_remaining,
1003 p_func_curr,
1004 p_exc_rate,
1005 p_precision,
1006 p_min_acc_unit
1007 )
1008 ), 0)
1009 ), 0), /* Sum of Functional Amount Due Remaining */
1010 nvl(sum(decode(ps.class,
1011 'PMT' , 1, 0
1012 )
1013 ),0) /* Count of Receipts */
1014 into l_ori_amount,
1015 l_rem_amount,
1016 l_func_ori_amt,
1017 l_func_rem_amt,
1018 l_count
1019 from ar_cash_receipts cr,
1020 ar_payment_schedules ps,
1021 ar_cash_receipt_history crh
1022 where ps.cash_receipt_id = cr.cash_receipt_id
1023 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1024 and ps.gl_date between p_start_date and p_end_date
1025 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
1026 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
1027 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
1028 and ps.status = nvl(p_status, ps.status)
1029 and cr.cash_receipt_id = crh.cash_receipt_id
1030 and crh.current_record_flag||'' = 'Y'
1031 and crh.status = 'REMITTED'
1032 and crh.factor_flag = decode(l_type, 'STANDARD',
1033 'N', 'Y');
1034
1035 IF l_type = 'STANDARD' THEN
1036 p_standard_ori_amt := l_ori_amount * -1;
1037 p_standard_rem_amt := l_rem_amount * -1;
1038 p_standard_func_ori_amt := l_func_ori_amt * -1;
1039 p_standard_func_rem_amt := l_func_rem_amt * -1;
1040 p_standard_count := l_count;
1041 ELSE
1042 p_factored_ori_amt := l_ori_amount * -1;
1043 p_factored_rem_amt := l_rem_amount * -1;
1044 p_factored_func_ori_amt := l_func_ori_amt * -1;
1045 p_factored_func_rem_amt := l_func_rem_amt * -1;
1046 p_factored_count := l_count;
1047 END IF;
1048
1049 l_counter := l_counter + 1;
1050 END LOOP;
1051 ELSE
1052 p_standard_ori_amt := NULL;
1053 p_standard_rem_amt := NULL;
1054 p_standard_func_ori_amt := 0;
1055 p_standard_func_rem_amt := 0;
1056 p_standard_count := 0;
1057 p_factored_ori_amt := NULL;
1058 p_factored_rem_amt := NULL;
1059 p_factored_func_ori_amt := 0;
1060 p_factored_func_rem_amt := 0;
1061 p_factored_count := 0;
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 arp_standard.debug( 'Exception:');
1067 end;
1068
1069
1070 procedure get_protested_BR(p_BR_protested_amt IN OUT NOCOPY NUMBER,
1071 p_BR_protested_func_amt IN OUT NOCOPY NUMBER,
1072 p_BR_protested_count IN OUT NOCOPY NUMBER,
1073 p_start_date IN gl_period_statuses.start_date%TYPE,
1074 p_end_date IN gl_period_statuses.end_date%TYPE,
1075 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1076 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1077 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1078 p_func_curr IN VARCHAR2,
1079 p_exc_rate IN NUMBER,
1080 p_precision IN NUMBER,
1081 p_min_acc_unit IN NUMBER,
1082 p_status IN ar_payment_schedules.status%TYPE) IS
1083 BEGIN
1084 IF PG_DEBUG in ('Y', 'C') THEN
1085 arp_standard.debug( 'ARP_ARXVASUM.get_protested_BR()+ ');
1086 END IF;
1087
1088 select decode(p_currency_code,
1089 NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
1090 ),
1091 nvl(sum( decode(p_exc_rate,
1092 NULL, ps.acctd_amount_due_remaining,
1093 arpcurr.functional_amount(ps.amount_due_remaining,
1094 p_func_curr,
1095 p_exc_rate,
1096 p_precision,
1097 p_min_acc_unit
1098 )
1099 )),0),
1100 count(ps.amount_due_remaining)
1101 into p_BR_protested_amt,
1102 p_BR_protested_func_amt,
1103 p_BR_protested_count
1104 from ar_transaction_history trh,
1105 ar_payment_schedules ps
1106 where trh.gl_date between p_start_date and p_end_date
1107 and trh.status = 'PROTESTED'
1108 and nvl(trh.current_record_flag,'Y') = 'Y'
1109 and ps.customer_trx_id = trh.customer_trx_id
1110 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1111 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
1112 AND ps.status = nvl(p_status, ps.status);
1113
1114 IF PG_DEBUG in ('Y', 'C') THEN
1115 arp_standard.debug( 'ARP_ARXVASUM.get_protested_BR()- ');
1116 END IF;
1117
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 IF PG_DEBUG in ('Y', 'C') THEN
1121 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_protested_BR ');
1122 END IF;
1123 RAISE;
1124
1125 END get_protested_BR;
1126
1127 procedure get_unpaid_BR(p_BR_unpaid_amt IN OUT NOCOPY NUMBER,
1128 p_BR_unpaid_func_amt IN OUT NOCOPY NUMBER,
1129 p_BR_unpaid_count IN OUT NOCOPY NUMBER,
1130 p_start_date IN gl_period_statuses.start_date%TYPE,
1131 p_end_date IN gl_period_statuses.end_date%TYPE,
1132 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1133 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1134 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1135 p_func_curr IN VARCHAR2,
1136 p_exc_rate IN NUMBER,
1137 p_precision IN NUMBER,
1138 p_min_acc_unit IN NUMBER,
1139 p_status IN ar_payment_schedules.status%TYPE) IS
1140 BEGIN
1141
1142 IF PG_DEBUG in ('Y', 'C') THEN
1143 arp_standard.debug( 'ARP_ARXVASUM.get_unpaid_BR()+ ');
1144 END IF;
1145
1146 select decode(p_currency_code,
1147 NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
1148 ),
1149 nvl(sum( decode(p_exc_rate,
1150 NULL, ps.acctd_amount_due_remaining,
1151 arpcurr.functional_amount(ps.amount_due_remaining,
1152 p_func_curr,
1153 p_exc_rate,
1154 p_precision,
1155 p_min_acc_unit
1156 )
1157 )),0),
1158 count(ps.amount_due_remaining)
1159 into p_BR_unpaid_amt,
1160 p_BR_unpaid_func_amt,
1161 p_BR_unpaid_count
1162 from ar_transaction_history trh,
1163 ar_payment_schedules ps
1164 where trh.gl_date between p_start_date and p_end_date
1165 and trh.status = 'UNPAID'
1166 and nvl(trh.current_record_flag,'Y') = 'Y'
1167 and ps.customer_trx_id = trh.customer_trx_id
1168 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1169 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
1170 AND ps.status = nvl(p_status, ps.status);
1171
1172 IF PG_DEBUG in ('Y', 'C') THEN
1173 arp_standard.debug( 'ARP_ARXVASUM.get_unpaid_BR()- ');
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 IF PG_DEBUG in ('Y', 'C') THEN
1179 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_unpaid_BR');
1180 END IF;
1181 RAISE;
1182
1183 END get_unpaid_BR;
1184
1185 procedure get_pend_acceptance_BR(p_BR_pend_acceptance_amt IN OUT NOCOPY NUMBER,
1186 p_BR_pend_acceptance_func_amt IN OUT NOCOPY NUMBER,
1187 p_BR_pend_acceptance_count IN OUT NOCOPY NUMBER,
1188 p_start_date IN gl_period_statuses.start_date%TYPE,
1189 p_end_date IN gl_period_statuses.end_date%TYPE,
1190 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1191 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1192 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1193 p_func_curr IN VARCHAR2,
1194 p_exc_rate IN NUMBER,
1195 p_precision IN NUMBER,
1196 p_min_acc_unit IN NUMBER) IS
1197 BEGIN
1198
1199 IF PG_DEBUG in ('Y', 'C') THEN
1200 arp_standard.debug( 'ARP_ARXVASUM.get_pend_acceptance_BR()+ ');
1201 END IF;
1202
1203 select decode(p_currency_code,
1204 NULL , NULL , nvl(sum(ctl.extended_amount),0)
1205 ),
1206 nvl(sum( decode(p_exc_rate,
1207 NULL, ctl.extended_acctd_amount,
1208 arpcurr.functional_amount(ctl.extended_amount,
1209 p_func_curr,
1210 p_exc_rate,
1211 p_precision,
1212 p_min_acc_unit
1213 )
1214 )),0),
1215 count(distinct ctl.customer_trx_id)
1216 into p_BR_pend_acceptance_amt,
1217 p_BR_pend_acceptance_func_amt,
1218 p_BR_pend_acceptance_count
1219 from ar_transaction_history trh,
1220 ra_customer_trx ct,
1221 ra_customer_trx_lines ctl
1222 where trh.gl_date between p_start_date and p_end_date
1223 and trh.status = 'PENDING_ACCEPTANCE'
1224 and nvl(trh.current_record_flag,'Y') = 'Y'
1225 and ct.customer_trx_id = trh.customer_trx_id
1226 AND ct.invoice_currency_code = nvl(p_currency_code, ct.invoice_currency_code)
1227 and nvl(ct.drawee_site_use_id, -10) = nvl(p_site_use_id, nvl(ct.drawee_site_use_id, -10) )
1228 and ctl.customer_trx_id = ct.customer_trx_id;
1229
1230 IF PG_DEBUG in ('Y', 'C') THEN
1231 arp_standard.debug( 'ARP_ARXVASUM.get_pend_acceptance_BR()- ');
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 IF PG_DEBUG in ('Y', 'C') THEN
1237 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_pend_acceptance_BR ');
1238 END IF;
1239 RAISE;
1240
1241 END get_pend_acceptance_BR;
1242
1243 end;