[Home] [Help]
PACKAGE BODY: APPS.ARP_ARXVASUM
Source
1 PACKAGE BODY ARP_ARXVASUM AS
2 /* $Header: ARCESUMB.pls 120.5 2005/10/30 04:14:10 appldev ship $ */
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 ps.customer_id = p_customer_id /* bug1963032 */
285 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
286 and ps.gl_date between p_start_date and p_end_date
287 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
288 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
289 and ps.cash_receipt_id is NULL
290 and ps.status = nvl(p_status, ps.status);
291 /*===================================================================================+
292 | The above procedure is now split into two. The top SQL would fetch totals and |
293 | counts for transactions of type "INV", "GUAR", "CM", "DEP", "DM" and "CB". The |
294 | SQL below would fetch total and counts for transaction type of "PMT" and would |
295 | reject a "PMT" if it is REVERSED. Required as a Bug Fix : 486920 |
296 | |
297 +===================================================================================*/
298
299 select decode(p_currency_code,
300 NULL , NULL ,
301 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
302 ), /* Sum of Original Amount */
303 decode(p_currency_code,
304 NULL , NULL ,
305 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
306 ), /* Sum of Amount Due Remaining */
307 nvl(sum(decode(ps.class,
308 'PMT', arpcurr.functional_amount( ps.amount_due_original,
309 p_func_curr,
310 nvl(p_exc_rate, ps.exchange_rate),
311 p_precision,
312 p_min_acc_unit
313 ),
314 0)), 0), /* Sum of Functional Original Amount */
315 nvl(sum(decode(ps.class,
316 'PMT', decode(p_exc_rate,
317 NULL, ps.acctd_amount_due_remaining,
318 arpcurr.functional_amount( ps.amount_due_remaining,
319 p_func_curr,
320 p_exc_rate,
321 p_precision,
322 p_min_acc_unit
323 )
324 ), 0)
325 ), 0), /* Sum of Functional Amount Due Remaining */
326 nvl(sum(decode(ps.class,
327 'PMT' , 1, 0
328 )
329 ),0), /* Count of Receipts */
330 NULL,
331 NULL,
332 0,
333 0,
334 0
335 into p_sum_pmt_ori_amt,
336 p_sum_pmt_rem_amt,
337 p_sum_pmt_func_ori_amt,
338 p_sum_pmt_func_rem_amt,
339 p_pmt_count,
340 p_sum_risk_ori_amt,
341 p_sum_risk_rem_amt,
342 p_sum_risk_func_ori_amt,
343 p_sum_risk_func_rem_amt,
344 p_risk_count
345 from ar_cash_receipts cr,
346 ar_payment_schedules ps
347 where ps.customer_id = p_customer_id /* Bug 1963032 */
348 and ps.cash_receipt_id = cr.cash_receipt_id
349 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
350 and ps.gl_date between p_start_date and p_end_date
351 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
352 and (nvl(cr.reversal_category, cr.status||'X') <> cr.status OR
353 (nvl(cr.reversal_category, cr.status||'X') = cr.status AND
354 'Y' = (SELECT 'Y'
355 FROM ar_payment_schedules PS_DM,
356 ra_cust_trx_types CTT_DM,
357 ra_customer_trx CT_DM,
358 ra_cust_trx_line_gl_dist DM_GLD
359 WHERE PS_DM.reversed_cash_receipt_id = cr.cash_receipt_id
360 AND PS_DM.class = 'DM'
361 AND PS_DM.cust_trx_type_id = CTT_DM.cust_trx_type_id
362 AND PS_DM.customer_trx_id = CT_DM.customer_trx_id
363 AND DM_GLD.customer_trx_id = PS_DM.customer_trx_id
364 AND DM_GLD.account_class = 'REC'
365 AND DM_GLD.latest_rec_flag = 'Y')))
366 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
367 and ps.status = nvl(p_status, ps.status);
368
369 /* If include receipts at risk special menu is Y, find out NOCOPY amounts
370 for receipts at risk */
371 IF p_incl_rct_spmenu = 'Y' THEN
372 select decode(p_currency_code,
373 NULL , NULL ,
374 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
375 ), /* Sum of Original Amount */
376 decode(p_currency_code,
377 NULL , NULL ,
378 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
379 ), /* Sum of Amount Due Remaining */
380 nvl(sum(decode(ps.class,
381 'PMT', arpcurr.functional_amount( ps.amount_due_original,
382 p_func_curr,
383 nvl(p_exc_rate, ps.exchange_rate),
384 p_precision,
385 p_min_acc_unit
386 ),
387 0)), 0), /* Sum of Functional Original Amount */
388 nvl(sum(decode(ps.class,
389 'PMT', decode(p_exc_rate,
390 NULL, ps.acctd_amount_due_remaining,
391 arpcurr.functional_amount( ps.amount_due_remaining,
392 p_func_curr,
393 p_exc_rate,
394 p_precision,
395 p_min_acc_unit
396 )
397 ), 0)
398 ), 0), /* Sum of Functional Amount Due Remaining */
399 nvl(sum(decode(ps.class,
400 'PMT' , 1, 0
401 )
402 ),0) /* Count of Receipts */
403 into p_sum_risk_ori_amt,
404 p_sum_risk_rem_amt,
405 p_sum_risk_func_ori_amt,
406 p_sum_risk_func_rem_amt,
407 p_risk_count
408 from ar_cash_receipts cr,
409 ar_payment_schedules ps,
410 ar_cash_receipt_history crh
411 where ps.customer_id = p_customer_id /* Bug 1963032 */
412 and ps.cash_receipt_id = cr.cash_receipt_id
413 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
414 and ps.gl_date between p_start_date and p_end_date
415 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
416 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
417 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
418 and ps.status = nvl(p_status, ps.status)
419 and cr.cash_receipt_id = crh.cash_receipt_id
420 and crh.current_record_flag||'' = 'Y'
421 and crh.status not in (decode (crh.factor_flag,
422 'Y', 'RISK_ELIMINATED',
423 'N', 'CLEARED'), 'REVERSED')
424 /* 06-AUG-2000 J Rautiainen BR Implementation
425 * Short term debt applications are not considered as receipts at risk */
426 and not exists (select 'X'
427 from ar_receivable_applications rap
428 where rap.cash_receipt_id = cr.cash_receipt_id
429 and rap.applied_payment_schedule_id = -2
430 and rap.display = 'Y');
431
432 END IF;
433 -- arp_standard.enable_debug;
434 EXCEPTION
435 WHEN OTHERS THEN
436 arp_standard.debug( 'Exception:');
437
438 end;
439
440
441 procedure get_payments_ontime(
442 p_payments_late_count IN OUT NOCOPY NUMBER,
443 p_payments_ontime_count IN OUT NOCOPY NUMBER,
444 p_payments_late_amount IN OUT NOCOPY NUMBER,
445 p_payments_ontime_amount IN OUT NOCOPY NUMBER,
446 p_payments_late_func_amt IN OUT NOCOPY NUMBER,
447 p_payments_ontime_func_amt IN OUT NOCOPY NUMBER,
448 p_start_date IN gl_period_statuses.start_date%TYPE,
449 p_end_date IN gl_period_statuses.end_date%TYPE,
450 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
451 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
452 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
453 p_func_curr IN VARCHAR2,
454 p_exc_rate IN NUMBER,
455 p_precision IN NUMBER,
456 p_min_acc_unit IN NUMBER,
457 p_status IN ar_payment_schedules.status%TYPE
458 ) is
459 begin
460
461 select nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 1,0)),0),
462 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,1)),0),
463 decode(p_currency_code,
464 NULL , NULL ,
465 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, ra.amount_applied,0)),0)),
466 decode(p_currency_code,
467 NULL , NULL ,
468 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0,ra.amount_applied)),0)),
469 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, decode(p_exc_rate,
470 NULL, ra.acctd_amount_applied_from,
471 arpcurr.functional_amount(ra.amount_applied,
472 p_func_curr,
473 p_exc_rate,
474 p_precision,
475 p_min_acc_unit
476 )),0
477 )),0),
478 nvl(sum(decode(sign(ra.apply_date - ps.due_date),1, 0, decode(p_exc_rate,
479 NULL, ra.acctd_amount_applied_from,
480 arpcurr.functional_amount(ra.amount_applied,
481 p_func_curr,
482 p_exc_rate,
483 p_precision,
484 p_min_acc_unit
485 ))
486 )),0)
487 into p_payments_late_count,
488 p_payments_ontime_count,
489 p_payments_late_amount,
490 p_payments_ontime_amount,
491 p_payments_late_func_amt,
492 p_payments_ontime_func_amt
493 from ar_receivable_applications ra,
494 ar_payment_schedules ps
495 where ra.applied_payment_schedule_id = ps.payment_schedule_id
496 and ps.customer_id = p_customer_id /* bug1963032 */
497 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
498 AND ra.apply_date between p_start_date and p_end_date
499 and ra.status = 'APP'
500 and ra.display = 'Y'
501 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
502 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
503 AND ps.status = nvl(p_status, ps.status);
504
505 --arp_standard.enable_debug;
506 EXCEPTION
507 WHEN OTHERS THEN
508 arp_standard.debug( 'Exception:');
509 end;
510
511
512 procedure get_nsf_stop(
513 p_nsf_stop_amount IN OUT NOCOPY NUMBER,
514 p_nsf_stop_func_amt IN OUT NOCOPY NUMBER,
515 p_nsf_stop_count IN OUT NOCOPY NUMBER,
516 p_start_date IN gl_period_statuses.start_date%TYPE,
517 p_end_date IN gl_period_statuses.end_date%TYPE,
518 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
519 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
520 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
521 p_func_curr IN VARCHAR2,
522 p_exc_rate IN NUMBER,
523 p_precision IN NUMBER,
524 p_min_acc_unit NUMBER,
525 p_status IN ar_payment_schedules.status%TYPE
526 ) is
527 begin
528 SELECT decode(p_currency_code,
529 NULL , NULL ,
530 nvl(sum(cr.amount),0)
531 ),
532 nvl(sum(arpcurr.functional_amount( cr.amount,
533 p_func_curr,
534 nvl(p_exc_rate,ps.exchange_rate),
535 p_precision,
536 p_min_acc_unit
537 )
538 ),0),
539 count(cr.amount)
540 INTO p_nsf_stop_amount,
541 p_nsf_stop_func_amt,
542 p_nsf_stop_count
543 FROM ar_cash_receipts cr,
544 ar_payment_schedules ps
545 WHERE ps.gl_date between p_start_date and p_end_date
546 AND ps.cash_receipt_id = cr.cash_receipt_id
547 AND cr.reversal_category in ('NSF','STOP')
548 AND cr.pay_from_customer = p_customer_id /* bug1963032 */
549 and nvl(cr.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(cr.customer_site_use_id, -10) )
550 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
551 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
552 AND ps.status = nvl(p_status, ps.status);
553
554 --arp_standard.enable_debug;
555 EXCEPTION
556 WHEN OTHERS THEN
557 arp_standard.debug( 'Exception:');
558 end;
559
560
561 procedure get_adjustments(
562 p_adjustment_amount IN OUT NOCOPY NUMBER,
563 p_adjustment_func_amt IN OUT NOCOPY NUMBER,
564 p_adjustment_count IN OUT NOCOPY NUMBER,
565 p_start_date IN gl_period_statuses.start_date%TYPE,
566 p_end_date IN gl_period_statuses.end_date%TYPE,
567 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
568 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
569 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
570 p_func_curr IN VARCHAR2,
571 p_exc_rate IN NUMBER,
572 p_precision IN NUMBER,
573 p_min_acc_unit IN NUMBER,
574 p_status IN ar_payment_schedules.status%TYPE
575 ) is
576 begin
577 select decode(p_currency_code,
578 NULL , NULL , nvl(sum(a.amount),0)
579 ),
580 nvl(sum( decode(p_exc_rate,
581 NULL, a.acctd_amount,
582 arpcurr.functional_amount(a.amount,
583 p_func_curr,
584 p_exc_rate,
585 p_precision,
586 p_min_acc_unit
587 )
588 )),0),
589 count(a.amount)
590 into p_adjustment_amount,
591 p_adjustment_func_amt,
592 p_adjustment_count
593 from ar_adjustments a,
594 ar_receivables_trx rt,
595 ar_payment_schedules ps
596 where a.gl_date between p_start_date and p_end_date
597 and nvl(a.postable,'Y') = 'Y'
598 and a.payment_schedule_id = ps.payment_schedule_id
599 and ps.customer_id = p_customer_id /* bug1963032 */
600 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
601 and a.receivables_trx_id = rt.receivables_trx_id
602 and nvl(rt.type,'X') <> 'FINCHRG'
603 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
604 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
605 AND ps.status = nvl(p_status, ps.status);
606
607 --arp_standard.enable_debug;
608 EXCEPTION
609 WHEN OTHERS THEN
610 arp_standard.debug( 'Exception:');
611 end;
612
613
614
615 procedure get_financecharg(
616 p_financecharg_amount IN OUT NOCOPY NUMBER,
617 p_financecharg_func_amt IN OUT NOCOPY NUMBER,
618 p_financecharg_count IN OUT NOCOPY NUMBER,
619 p_start_date IN gl_period_statuses.start_date%TYPE,
620 p_end_date IN gl_period_statuses.end_date%TYPE,
621 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
622 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
623 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
624 p_func_curr IN VARCHAR2,
625 p_exc_rate IN NUMBER,
626 p_precision IN NUMBER,
627 p_min_acc_unit IN NUMBER,
628 p_status IN ar_payment_schedules.status%TYPE
629 ) is
630 begin
631 select decode(p_currency_code,
632 NULL , NULL , nvl(sum(a.amount),0)
633 ),
634 nvl(sum( decode(p_exc_rate,
635 NULL, a.acctd_amount,
636 arpcurr.functional_amount(a.amount,
637 p_func_curr,
638 p_exc_rate,
639 p_precision,
640 p_min_acc_unit
641 )
642 )),0),
643 count(a.amount)
644 into p_financecharg_amount,
645 p_financecharg_func_amt,
646 p_financecharg_count
647 from ar_adjustments a,
648 ar_receivables_trx rt,
649 ar_payment_schedules ps
650 where a.gl_date between p_start_date and p_end_date
651 and nvl(a.postable,'Y') = 'Y'
652 and a.payment_schedule_id = ps.payment_schedule_id
653 and ps.customer_id = p_customer_id /* bug1963032 */
654 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
655 and a.receivables_trx_id = rt.receivables_trx_id
656 and nvl(rt.type,'X') = 'FINCHRG'
657 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
658 AND nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
659 AND ps.status = nvl(p_status, ps.status);
660
661 --arp_standard.enable_debug;
662 EXCEPTION
663 WHEN OTHERS THEN
664 arp_standard.debug( 'Exception:');
665 end;
666
667
668 procedure get_discounts(
669 p_earned_discounts IN OUT NOCOPY NUMBER,
670 p_unearned_discounts IN OUT NOCOPY NUMBER,
671 p_earned_func_disc IN OUT NOCOPY NUMBER,
672 p_unearned_func_disc IN OUT NOCOPY NUMBER,
673 p_earned_disc_count IN OUT NOCOPY NUMBER,
674 p_unearned_disc_count IN OUT NOCOPY NUMBER,
675 p_start_date IN gl_period_statuses.start_date%TYPE,
676 p_end_date IN gl_period_statuses.end_date%TYPE,
677 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
678 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
679 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
680 p_func_curr IN VARCHAR2,
681 p_exc_rate IN NUMBER,
682 p_precision IN NUMBER,
683 p_min_acc_unit IN NUMBER,
684 p_status IN ar_payment_schedules.status%TYPE
685 ) is
686 begin
687 SELECT decode(p_currency_code,
688 NULL , NULL ,
689 nvl(sum(ra.earned_discount_taken),0)
690 ),
691 decode(p_currency_code,
692 NULL , NULL ,
693 nvl(sum(ra.unearned_discount_taken),0)
694 ),
695 nvl(sum( decode(p_exc_rate,
696 NULL, ra.acctd_earned_discount_taken,
697 arpcurr.functional_amount( nvl(ra.earned_discount_taken,0),
698 p_func_curr,
699 p_exc_rate, p_precision,
700 p_min_acc_unit
701 )
702 )
703 ),0),
704 nvl(sum( decode(p_exc_rate,
705 NULL, ra.acctd_unearned_discount_taken,
706 arpcurr.functional_amount( nvl(ra.unearned_discount_taken,0),
707 p_func_curr,
708 p_exc_rate,
709 p_precision,
710 p_min_acc_unit
711 )
712 )),0),
713 count(decode(ra.earned_discount_taken,
714 0, NULL,
715 ra.earned_discount_taken
716 )
717 ),
718 count(decode(ra.unearned_discount_taken,
719 0, NULL,
720 ra.unearned_discount_taken
721 )
722 )
723 INTO p_earned_discounts,
724 p_unearned_discounts,
725 p_earned_func_disc,
726 p_unearned_func_disc,
727 p_earned_disc_count,
728 p_unearned_disc_count
729 FROM ar_receivable_applications ra,
730 ar_payment_schedules ps
731 where ra.gl_date between p_start_date and p_end_date
732 and ps.payment_schedule_id = ra.applied_payment_schedule_id
733 and ps.customer_id = p_customer_id /* bug1963032 */
734 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
735 and ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
736 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
737 and ps.status = nvl(p_status, ps.status);
738
739 --arp_standard.enable_debug;
740 EXCEPTION
741 WHEN OTHERS THEN
742 arp_standard.debug( 'Exception:');
743 end;
744
745
746 /* BOE: get information about receipts that are waiting to be confirmed */
747 procedure get_pending_confirmation(
748 p_pend_confirm_amt IN OUT NOCOPY NUMBER,
749 p_pend_confirm_func_amt IN OUT NOCOPY NUMBER,
750 p_pend_confirm_count IN OUT NOCOPY NUMBER,
751 p_start_date IN gl_period_statuses.start_date%TYPE,
752 p_end_date IN gl_period_statuses.end_date%TYPE,
753 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
754 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
755 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
756 p_func_curr IN VARCHAR2,
757 p_exc_rate IN NUMBER,
758 p_precision IN NUMBER,
759 p_min_acc_unit IN NUMBER,
760 p_status IN ar_payment_schedules.status%TYPE
761 ) is
762 begin
763 select decode(p_currency_code,
764 NULL , NULL ,
765 nvl(sum(cr.amount),0)
766 ),
767 nvl(sum(arpcurr.functional_amount( cr.amount,
768 p_func_curr,
769 nvl(p_exc_rate,ps.exchange_rate),
770 p_precision,
771 p_min_acc_unit
772 )
773 ),0),
774 count(cr.amount)
775 into p_pend_confirm_amt,
776 p_pend_confirm_func_amt,
777 p_pend_confirm_count
778 from ar_payment_schedules ps,
779 ar_cash_receipts cr,
780 ar_cash_receipt_history crh
781 where ps.customer_id = p_customer_id /* bug1963032 */
782 and ps.cash_receipt_id = cr.cash_receipt_id
783 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id,
784 -10) )
785 and ps.gl_date between p_start_date and p_end_date
786 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
787 and ps.status = nvl(p_status, ps.status)
788 and cr.cash_receipt_id = crh.cash_receipt_id
789 and crh.current_record_flag||'' = 'Y'
790 and crh.status = 'APPROVED';
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 arp_standard.debug( 'Exception:');
795 end;
796
797
798 /* BOE: get information about receipts that are waiting to be remitted,
799 separate receipts into BOE, notes receivable and others */
800 procedure get_pending_remit(
801 p_boe_ori_amt IN OUT NOCOPY NUMBER,
802 p_boe_func_ori_amt IN OUT NOCOPY NUMBER,
803 p_boe_rem_amt IN OUT NOCOPY NUMBER,
804 p_boe_func_rem_amt IN OUT NOCOPY NUMBER,
805 p_boe_count IN OUT NOCOPY NUMBER,
806 p_note_ori_amt IN OUT NOCOPY NUMBER,
807 p_note_func_ori_amt IN OUT NOCOPY NUMBER,
808 p_note_rem_amt IN OUT NOCOPY NUMBER,
809 p_note_func_rem_amt IN OUT NOCOPY NUMBER,
810 p_note_count IN OUT NOCOPY NUMBER,
811 p_other_ori_amt IN OUT NOCOPY NUMBER,
812 p_other_func_ori_amt IN OUT NOCOPY NUMBER,
813 p_other_rem_amt IN OUT NOCOPY NUMBER,
814 p_other_func_rem_amt IN OUT NOCOPY NUMBER,
815 p_other_count IN OUT NOCOPY NUMBER,
816 p_start_date IN gl_period_statuses.start_date%TYPE,
817 p_end_date IN gl_period_statuses.end_date%TYPE,
818 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
819 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
820 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
821 p_func_curr IN VARCHAR2,
822 p_exc_rate IN NUMBER,
823 p_precision IN NUMBER,
824 p_min_acc_unit IN NUMBER,
825 p_status IN ar_payment_schedules.status%TYPE,
826 p_incl_rct_spmenu IN VARCHAR2
827 ) is
828 l_ori_amount number;
829 l_func_ori_amt number;
830 l_rem_amount number;
831 l_func_rem_amt number;
832 l_count number;
833 l_counter number := 0;
834 l_type varchar2(8);
835 begin
836 IF p_incl_rct_spmenu = 'Y' THEN /* Calculate different pending remittance amounts
837 only if include receipts at risk special menu
838 is checked. */
839 WHILE l_counter < 3 LOOP
840 IF l_counter = 0 THEN
841 l_type := 'BOE';
842 ELSIF l_counter = 1 THEN
843 l_type := 'NOTES';
844 ELSE
845 l_type := 'OTHER';
846 END IF;
847
848 select decode(p_currency_code,
849 NULL , NULL ,
850 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
851 ), /* Sum of Original Amount */
852 decode(p_currency_code,
853 NULL , NULL ,
854 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
855 ), /* Sum of Amount Due Remaining */
856 nvl(sum(decode(ps.class,
857 'PMT', arpcurr.functional_amount( ps.amount_due_original,
858 p_func_curr,
859 nvl(p_exc_rate, ps.exchange_rate),
860 p_precision,
861 p_min_acc_unit
862 ),
863 0)), 0), /* Sum of Functional Original Amount */
864 nvl(sum(decode(ps.class,
865 'PMT', decode(p_exc_rate,
866 NULL, ps.acctd_amount_due_remaining,
867 arpcurr.functional_amount( ps.amount_due_remaining,
868 p_func_curr,
869 p_exc_rate,
870 p_precision,
871 p_min_acc_unit
872 )
873 ), 0)
874 ), 0), /* Sum of Functional Amount Due Remaining */
875 nvl(sum(decode(ps.class,
876 'PMT' , 1, 0
877 )
878 ),0) /* Count of Receipts */
879 into l_ori_amount,
880 l_rem_amount,
881 l_func_ori_amt,
882 l_func_rem_amt,
883 l_count
884 from ar_cash_receipts cr,
885 ar_payment_schedules ps,
886 ar_cash_receipt_history crh,
887 ar_receipt_methods rm,
888 ar_receipt_classes rc
889 where ps.customer_id = p_customer_id /* bug1963032 */
890 and ps.cash_receipt_id = cr.cash_receipt_id
891 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
892 and ps.gl_date between p_start_date and p_end_date
893 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
894 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
895 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
896 and ps.status = nvl(p_status, ps.status)
897 and cr.cash_receipt_id = crh.cash_receipt_id
898 and crh.current_record_flag||'' = 'Y'
899 and crh.status = 'CONFIRMED'
900 and cr.receipt_method_id = rm.receipt_method_id
901 and rm.receipt_class_id = rc.receipt_class_id
902 and nvl(rc.bill_of_exchange_flag, 'N') = decode(l_type, 'BOE', 'Y', 'N')
903 and nvl(rc.notes_receivable, 'N') = decode(l_type, 'NOTES', 'Y', 'N');
904
905 IF l_type = 'BOE' THEN
906 p_boe_ori_amt := l_ori_amount * -1;
907 p_boe_rem_amt := l_rem_amount * -1;
908 p_boe_func_ori_amt := l_func_ori_amt * -1;
909 p_boe_func_rem_amt := l_func_rem_amt * -1;
910 p_boe_count := l_count;
911 ELSIF l_type = 'NOTES' THEN
912 p_note_ori_amt := l_ori_amount * -1;
913 p_note_rem_amt := l_rem_amount * -1;
914 p_note_func_ori_amt := l_func_ori_amt * -1;
915 p_note_func_rem_amt := l_func_rem_amt * -1;
916 p_note_count := l_count;
917 ELSE
918 p_other_ori_amt := l_ori_amount * -1;
919 p_other_rem_amt := l_rem_amount * -1;
920 p_other_func_ori_amt := l_func_ori_amt * -1;
921 p_other_func_rem_amt := l_func_rem_amt * -1;
922 p_other_count := l_count;
923 END IF;
924
925 l_counter := l_counter + 1;
926 END LOOP;
927 ELSE
928 p_boe_ori_amt := NULL;
929 p_boe_rem_amt := NULL;
930 p_boe_func_ori_amt := 0;
931 p_boe_func_rem_amt := 0;
932 p_boe_count := 0;
933 p_note_ori_amt := NULL;
934 p_note_rem_amt := NULL;
935 p_note_func_ori_amt := 0;
936 p_note_func_rem_amt := 0;
937 p_note_count := 0;
938 p_other_ori_amt := NULL;
939 p_other_rem_amt := NULL;
940 p_other_func_ori_amt := 0;
941 p_other_func_rem_amt := 0;
942 p_other_count := 0;
943 END IF;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 arp_standard.debug( 'Exception:');
948 end;
949
950
951 /* BOE: get information about remitted receipts that are not cleared */
952 procedure get_remitted(
953 p_standard_ori_amt IN OUT NOCOPY NUMBER,
954 p_standard_func_ori_amt IN OUT NOCOPY NUMBER,
955 p_standard_rem_amt IN OUT NOCOPY NUMBER,
956 p_standard_func_rem_amt IN OUT NOCOPY NUMBER,
957 p_standard_count IN OUT NOCOPY NUMBER,
958 p_factored_ori_amt IN OUT NOCOPY NUMBER,
959 p_factored_func_ori_amt IN OUT NOCOPY NUMBER,
960 p_factored_rem_amt IN OUT NOCOPY NUMBER,
961 p_factored_func_rem_amt IN OUT NOCOPY NUMBER,
962 p_factored_count IN OUT NOCOPY NUMBER,
963 p_start_date IN gl_period_statuses.start_date%TYPE,
964 p_end_date IN gl_period_statuses.end_date%TYPE,
965 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
966 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
967 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
968 p_func_curr IN VARCHAR2,
969 p_exc_rate IN NUMBER,
970 p_precision IN NUMBER,
971 p_min_acc_unit IN NUMBER,
972 p_status IN ar_payment_schedules.status%TYPE,
973 p_incl_rct_spmenu IN VARCHAR2
974 ) is
975 l_ori_amount number;
976 l_func_ori_amt number;
977 l_rem_amount number;
978 l_func_rem_amt number;
979 l_count number;
980 l_counter number := 0;
981 l_type varchar2(10);
982 begin
983 IF p_incl_rct_spmenu = 'Y' THEN /* Calculate different pending remittance amounts
984 only if include receipts at risk special menu
985 is checked. */
986 WHILE l_counter < 2 LOOP
987 IF l_counter = 0 THEN
988 l_type := 'STANDARD';
989 ELSIF l_counter = 1 THEN
990 l_type := 'FACTORED';
991 END IF;
992
993 select decode(p_currency_code,
994 NULL , NULL ,
995 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_original, 0)),0)
996 ), /* Sum of Original Amount */
997 decode(p_currency_code,
998 NULL , NULL ,
999 nvl(sum(decode(ps.class, 'PMT' , ps.amount_due_remaining, 0)),0)
1000 ), /* Sum of Amount Due Remaining */
1001 nvl(sum(decode(ps.class,
1002 'PMT', arpcurr.functional_amount( ps.amount_due_original,
1003 p_func_curr,
1004 nvl(p_exc_rate, ps.exchange_rate),
1005 p_precision,
1006 p_min_acc_unit
1007 ),
1008 0)), 0), /* Sum of Functional Original Amount */
1009 nvl(sum(decode(ps.class,
1010 'PMT', decode(p_exc_rate,
1011 NULL, ps.acctd_amount_due_remaining,
1012 arpcurr.functional_amount( ps.amount_due_remaining,
1013 p_func_curr,
1014 p_exc_rate,
1015 p_precision,
1016 p_min_acc_unit
1017 )
1018 ), 0)
1019 ), 0), /* Sum of Functional Amount Due Remaining */
1020 nvl(sum(decode(ps.class,
1021 'PMT' , 1, 0
1022 )
1023 ),0) /* Count of Receipts */
1024 into l_ori_amount,
1025 l_rem_amount,
1026 l_func_ori_amt,
1027 l_func_rem_amt,
1028 l_count
1029 from ar_cash_receipts cr,
1030 ar_payment_schedules ps,
1031 ar_cash_receipt_history crh
1032 where ps.customer_id = p_customer_id /* bug1963032 */
1033 and ps.cash_receipt_id = cr.cash_receipt_id
1034 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1035 and ps.gl_date between p_start_date and p_end_date
1036 and ps.invoice_currency_code = nvl(p_currency_code,ps.invoice_currency_code)
1037 and nvl(cr.reversal_category, cr.status||'X') <> cr.status
1038 and nvl(ps.receipt_confirmed_flag,'Y') = 'Y'
1039 and ps.status = nvl(p_status, ps.status)
1040 and cr.cash_receipt_id = crh.cash_receipt_id
1041 and crh.current_record_flag||'' = 'Y'
1042 and crh.status = 'REMITTED'
1043 and crh.factor_flag = decode(l_type, 'STANDARD',
1044 'N', 'Y');
1045
1046 IF l_type = 'STANDARD' THEN
1047 p_standard_ori_amt := l_ori_amount * -1;
1048 p_standard_rem_amt := l_rem_amount * -1;
1049 p_standard_func_ori_amt := l_func_ori_amt * -1;
1050 p_standard_func_rem_amt := l_func_rem_amt * -1;
1051 p_standard_count := l_count;
1052 ELSE
1053 p_factored_ori_amt := l_ori_amount * -1;
1054 p_factored_rem_amt := l_rem_amount * -1;
1055 p_factored_func_ori_amt := l_func_ori_amt * -1;
1056 p_factored_func_rem_amt := l_func_rem_amt * -1;
1057 p_factored_count := l_count;
1058 END IF;
1059
1060 l_counter := l_counter + 1;
1061 END LOOP;
1062 ELSE
1063 p_standard_ori_amt := NULL;
1064 p_standard_rem_amt := NULL;
1065 p_standard_func_ori_amt := 0;
1066 p_standard_func_rem_amt := 0;
1067 p_standard_count := 0;
1068 p_factored_ori_amt := NULL;
1069 p_factored_rem_amt := NULL;
1070 p_factored_func_ori_amt := 0;
1071 p_factored_func_rem_amt := 0;
1072 p_factored_count := 0;
1073 END IF;
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 arp_standard.debug( 'Exception:');
1078 end;
1079
1080
1081 procedure get_protested_BR(p_BR_protested_amt IN OUT NOCOPY NUMBER,
1082 p_BR_protested_func_amt IN OUT NOCOPY NUMBER,
1083 p_BR_protested_count IN OUT NOCOPY NUMBER,
1084 p_start_date IN gl_period_statuses.start_date%TYPE,
1085 p_end_date IN gl_period_statuses.end_date%TYPE,
1086 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1087 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1088 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1089 p_func_curr IN VARCHAR2,
1090 p_exc_rate IN NUMBER,
1091 p_precision IN NUMBER,
1092 p_min_acc_unit IN NUMBER,
1093 p_status IN ar_payment_schedules.status%TYPE) IS
1094 BEGIN
1095 IF PG_DEBUG in ('Y', 'C') THEN
1096 arp_standard.debug( 'ARP_ARXVASUM.get_protested_BR()+ ');
1097 END IF;
1098
1099 select decode(p_currency_code,
1100 NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
1101 ),
1102 nvl(sum( decode(p_exc_rate,
1103 NULL, ps.acctd_amount_due_remaining,
1104 arpcurr.functional_amount(ps.amount_due_remaining,
1105 p_func_curr,
1106 p_exc_rate,
1107 p_precision,
1108 p_min_acc_unit
1109 )
1110 )),0),
1111 count(ps.amount_due_remaining)
1112 into p_BR_protested_amt,
1113 p_BR_protested_func_amt,
1114 p_BR_protested_count
1115 from ar_transaction_history trh,
1116 ar_payment_schedules ps
1117 where trh.gl_date between p_start_date and p_end_date
1118 and trh.status = 'PROTESTED'
1119 and nvl(trh.current_record_flag,'Y') = 'Y'
1120 and ps.customer_trx_id = trh.customer_trx_id
1121 and ps.customer_id = p_customer_id /* bug1963032 */
1122 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1123 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
1124 AND ps.status = nvl(p_status, ps.status);
1125
1126 IF PG_DEBUG in ('Y', 'C') THEN
1127 arp_standard.debug( 'ARP_ARXVASUM.get_protested_BR()- ');
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 IF PG_DEBUG in ('Y', 'C') THEN
1133 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_protested_BR ');
1134 END IF;
1135 RAISE;
1136
1137 END get_protested_BR;
1138
1139 procedure get_unpaid_BR(p_BR_unpaid_amt IN OUT NOCOPY NUMBER,
1140 p_BR_unpaid_func_amt IN OUT NOCOPY NUMBER,
1141 p_BR_unpaid_count IN OUT NOCOPY NUMBER,
1142 p_start_date IN gl_period_statuses.start_date%TYPE,
1143 p_end_date IN gl_period_statuses.end_date%TYPE,
1144 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1145 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1146 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1147 p_func_curr IN VARCHAR2,
1148 p_exc_rate IN NUMBER,
1149 p_precision IN NUMBER,
1150 p_min_acc_unit IN NUMBER,
1151 p_status IN ar_payment_schedules.status%TYPE) IS
1152 BEGIN
1153
1154 IF PG_DEBUG in ('Y', 'C') THEN
1155 arp_standard.debug( 'ARP_ARXVASUM.get_unpaid_BR()+ ');
1156 END IF;
1157
1158 select decode(p_currency_code,
1159 NULL , NULL , nvl(sum(ps.amount_due_remaining),0)
1160 ),
1161 nvl(sum( decode(p_exc_rate,
1162 NULL, ps.acctd_amount_due_remaining,
1163 arpcurr.functional_amount(ps.amount_due_remaining,
1164 p_func_curr,
1165 p_exc_rate,
1166 p_precision,
1167 p_min_acc_unit
1168 )
1169 )),0),
1170 count(ps.amount_due_remaining)
1171 into p_BR_unpaid_amt,
1172 p_BR_unpaid_func_amt,
1173 p_BR_unpaid_count
1174 from ar_transaction_history trh,
1175 ar_payment_schedules ps
1176 where trh.gl_date between p_start_date and p_end_date
1177 and trh.status = 'UNPAID'
1178 and nvl(trh.current_record_flag,'Y') = 'Y'
1179 and ps.customer_trx_id = trh.customer_trx_id
1180 and ps.customer_id = p_customer_id /* bug1963032 */
1181 and nvl(ps.customer_site_use_id, -10) = nvl(p_site_use_id, nvl(ps.customer_site_use_id, -10) )
1182 AND ps.invoice_currency_code = nvl(p_currency_code, ps.invoice_currency_code)
1183 AND ps.status = nvl(p_status, ps.status);
1184
1185 IF PG_DEBUG in ('Y', 'C') THEN
1186 arp_standard.debug( 'ARP_ARXVASUM.get_unpaid_BR()- ');
1187 END IF;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_unpaid_BR');
1193 END IF;
1194 RAISE;
1195
1196 END get_unpaid_BR;
1197
1198 procedure get_pend_acceptance_BR(p_BR_pend_acceptance_amt IN OUT NOCOPY NUMBER,
1199 p_BR_pend_acceptance_func_amt IN OUT NOCOPY NUMBER,
1200 p_BR_pend_acceptance_count IN OUT NOCOPY NUMBER,
1201 p_start_date IN gl_period_statuses.start_date%TYPE,
1202 p_end_date IN gl_period_statuses.end_date%TYPE,
1203 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1204 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1205 p_currency_code IN ar_payment_schedules.invoice_currency_code%TYPE,
1206 p_func_curr IN VARCHAR2,
1207 p_exc_rate IN NUMBER,
1208 p_precision IN NUMBER,
1209 p_min_acc_unit IN NUMBER) IS
1210 BEGIN
1211
1212 IF PG_DEBUG in ('Y', 'C') THEN
1213 arp_standard.debug( 'ARP_ARXVASUM.get_pend_acceptance_BR()+ ');
1214 END IF;
1215
1216 select decode(p_currency_code,
1217 NULL , NULL , nvl(sum(ctl.extended_amount),0)
1218 ),
1219 nvl(sum( decode(p_exc_rate,
1220 NULL, ctl.extended_acctd_amount,
1221 arpcurr.functional_amount(ctl.extended_amount,
1222 p_func_curr,
1223 p_exc_rate,
1224 p_precision,
1225 p_min_acc_unit
1226 )
1227 )),0),
1228 count(distinct ctl.customer_trx_id)
1229 into p_BR_pend_acceptance_amt,
1230 p_BR_pend_acceptance_func_amt,
1231 p_BR_pend_acceptance_count
1232 from ar_transaction_history trh,
1233 ra_customer_trx ct,
1234 ra_customer_trx_lines ctl
1235 where trh.gl_date between p_start_date and p_end_date
1236 and trh.status = 'PENDING_ACCEPTANCE'
1237 and nvl(trh.current_record_flag,'Y') = 'Y'
1238 and ct.customer_trx_id = trh.customer_trx_id
1239 and ct.drawee_id = p_customer_id /* bug1963032 */
1240 AND ct.invoice_currency_code = nvl(p_currency_code, ct.invoice_currency_code)
1241 and nvl(ct.drawee_site_use_id, -10) = nvl(p_site_use_id, nvl(ct.drawee_site_use_id, -10) )
1242 and ctl.customer_trx_id = ct.customer_trx_id;
1243
1244 IF PG_DEBUG in ('Y', 'C') THEN
1245 arp_standard.debug( 'ARP_ARXVASUM.get_pend_acceptance_BR()- ');
1246 END IF;
1247
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 IF PG_DEBUG in ('Y', 'C') THEN
1251 arp_standard.debug( 'Exception: ARP_ARXVASUM.get_pend_acceptance_BR ');
1252 END IF;
1253 RAISE;
1254
1255 END get_pend_acceptance_BR;
1256
1257 end;