[Home] [Help]
PACKAGE BODY: APPS.AR_GET_CUSTOMER_BALANCE_PKG
Source
1 package body AR_GET_CUSTOMER_BALANCE_PKG as
2 /* $Header: arxcoblb.pls 120.7.12010000.3 2008/08/22 05:53:05 rasarasw ship $ */
3 /*------------------------------------------------------------------------------
4 Procedure AR_GET_CUSTOMER_BALANCE calulates the customer balance
5 based on the parameters entered. It then inserts one row for each invoice,
6 credit memo, unapplied receipts,on account receipts and uncleared receipts
7 used for calculating the customer balance into the table AR_CUSTOMER_BALANCE_ITF
8 --------------------------------------------------------------------------------*/
9
10 /* bug2466471 : Re-create PROCEDURE ar_get_customer_balance.
11
12 At first, get amount_due_original of ps of transactions.
13
14 After that, get application and adjustment information of which gl_date is earliner than p_as_of_date in order to get invoice balance as of p_as_of_date.
15
16 Also, get unapplied and on-account receipts of which gl_date is earlier than p_as_of_date. (Of course, don't get application , unapplied and on-account information of uncleared receipt if p_uncleared_receipts is not 'Y'.)
17
18 And get customer balance on basis of unapplied and on-account receipts and invoice balance as of p_as_of_date.
19
20 Finally, insert these record into AR_CUSTOMER_BALANCE_ITF table.
21 */
22
23 /* bug 2657118 Changed the logic, instead of comparing as_of_date with gl_date of
24 transactions and as well as receipts , we'll now compare as_of_date with trx_date and apply_date whatever may be applicable.
25 */
26
27 PROCEDURE ar_get_customer_balance ( p_request_id in number
28 ,p_set_of_books_id in number
29 ,p_as_of_date in date
30 ,p_customer_name_from in varchar
31 ,p_customer_name_to in varchar
32 ,P_CUSTOMER_NUMBER_LOW in varchar
33 ,P_CUSTOMER_NUMBER_HIGH in varchar
34 ,p_currency in varchar
35 ,p_min_invoice_balance in number
36 ,p_min_open_balance in number
37 ,p_account_credits varchar
38 ,p_account_receipts varchar
39 ,p_unapp_receipts varchar
40 ,p_uncleared_receipts varchar
41 ,p_ref_no varchar
42 ,p_debug_flag in varchar
43 ,p_trace_flag in varchar
44 ) is
45 l_organization_name gl_sets_of_books.name%TYPE;
46 l_functional_currency_code gl_sets_of_books.currency_code%TYPE;
47 l_session_language varchar2(40);/* For MLS changes */
48 l_base_language varchar2(40); /*For MLS changes*/
49
50 l_cus_open_bal NUMBER ;
51 l_inv_open_bal NUMBER ;
52
53 l_amount_adj NUMBER ;
54 l_amount_applied NUMBER ;
55 l_earned_discount_taken NUMBER ;
56 l_unearned_discount_taken NUMBER ;
57 l_rev_amount_applied NUMBER ;
58 l_rev_earned_discount_taken NUMBER ;
59 l_rev_unearned_discount_taken NUMBER ;
60 l_amount_credited NUMBER ;
61 l_on_acct_receipts NUMBER ;
62 l_unapp_receipts NUMBER ;
63 l_rev_on_acct_receipts NUMBER ;
64 l_rev_unapp_receipts NUMBER ;
65 l_amount_applied_cm NUMBER; /*bug4502121*/
66
67 -- Selects the customer id in the given range of parameters
68
69 CURSOR cusinfo IS
70 SELECT cust_acct.cust_account_id customer_id ,
71 cust_acct.account_number ,
72 party.party_name ,
73 party.tax_reference
74 FROM hz_cust_accounts cust_acct,
75 hz_parties party
76 WHERE cust_acct.party_id = party.party_id
77 AND (p_customer_name_from is null or
78 upper(party.party_name) >= upper(p_customer_name_from))
79 AND (p_customer_name_to is null or
80 upper(party.party_name) <= upper(p_customer_name_to))
81 AND (p_customer_number_low is null or
82 upper(cust_acct.account_number) >= upper(p_customer_number_low))
83 AND (p_customer_number_high is null or
84 upper(cust_acct.account_number) <= upper(p_customer_number_high));
85
86
87 CURSOR siteinfo(p_customer_id ar_payment_schedules.customer_id%TYPE,
88 p_base_language hz_locations.language%TYPE,
89 p_session_language hz_locations.language%TYPE ) IS
90 SELECT site_uses.site_use_id ,
91 acct_site.translated_customer_name,
92 loc.address1,
93 loc.address2,
94 loc.address3,
95 loc.address4,
96 loc.city,
97 loc.state,
98 loc.postal_code,
99 loc.country
100 FROM hz_cust_acct_sites acct_site,
101 hz_party_sites party_site,
102 hz_locations loc,
103 hz_cust_site_uses site_uses
104 WHERE acct_site.cust_account_id =p_customer_id
105 AND nvl(acct_site.status,'A') = 'A'
106 AND acct_site.bill_to_flag in ( 'Y', 'P' )
107 AND acct_site.party_site_id = party_site.party_site_id
108 AND loc.location_id = party_site.location_id
109 AND nvl(site_uses.status,'A') = 'A'
110 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
111 AND site_uses.site_use_code = 'BILL_TO'
112 AND nvl(loc.language,p_base_language)=p_session_language;
113
114 --Selects all the currency for a customer site
115 CURSOR curinfo(p_customer_id ar_payment_schedules.customer_id%TYPE,
116 p_site_use_id ar_payment_schedules.customer_site_use_id%TYPE) IS
117 SELECT distinct(invoice_currency_code) currency_code
118 FROM ar_payment_schedules ps
119 WHERE ps.customer_id=p_customer_id
120 AND PS.customer_site_use_id=p_site_use_id
121 AND ps.invoice_currency_code=nvl(p_currency,ps.invoice_currency_code);
122
123 --selects invoices
124 CURSOR tot_inv(p_customer_id ar_payment_schedules.customer_id%TYPE,
125 p_site_use_id ar_payment_schedules.customer_site_use_id%TYPE,
126 p_currency ar_payment_schedules.invoice_currency_code%TYPE,
127 p_as_of_date ar_payment_schedules.trx_date%TYPE ) is
128 SELECT payment_schedule_id ,
129 class,
130 trx_number ,
131 trx_date ,
132 invoice_currency_code,
133 amount_due_original
134 FROM ar_payment_schedules ps
135 WHERE TRUNC(ps.trx_date) <= p_as_of_date
136 AND ps.class not in ( 'PMT' ,decode(p_account_credits, 'Y', 'PMT','CM') )
137 AND ps.invoice_currency_code=p_currency
138 AND ps.customer_id= p_customer_id
139 AND PS.customer_site_use_id=p_site_use_id
140 AND ps.actual_date_closed > p_as_of_date ;
141
142
143 --AR CUSTOMER BALANCE PROC BEGINS
144 BEGIN
145
146 SELECT substrb(userenv('LANG'),1,4)
147 INTO l_session_language
148 FROM dual;
149
150 SELECT language_code
151 INTO l_base_language
152 FROM fnd_languages
153 WHERE installed_flag='B';
154
155 SELECT sob.name
156 INTO l_organization_name
157 FROM gl_sets_of_books sob,ar_system_parameters ar
158 WHERE sob.set_of_books_id = ar.set_of_books_id;
159
160 SELECT currency_code
161 INTO l_functional_currency_code
162 FROM gl_sets_of_books sob,ar_system_parameters ar
163 WHERE sob.set_of_books_id = ar.set_of_books_id;
164
165 -- customer
166 FOR cusinfo_rec IN cusinfo
167 LOOP
168
169 -- site
170 FOR siteinfo_rec IN siteinfo( cusinfo_rec.customer_id ,
171 l_base_language,
172 l_session_language )
173 LOOP
174
175 -- currency
176 FOR currency_rec IN curinfo(cusinfo_rec.customer_id ,
177 siteinfo_rec.site_use_id )
178 LOOP
179 l_cus_open_bal:=0;
180
181 l_unapp_receipts := 0 ;
182 l_on_acct_receipts := 0 ;
183 l_rev_unapp_receipts := 0 ;
184 l_rev_on_acct_receipts := 0 ;
185
186 -- invoice
187 FOR tot_inv_rec IN tot_inv(cusinfo_rec.customer_id,
188 siteinfo_rec.site_use_id,
189 currency_rec.currency_code,
190 p_as_of_date )
191 LOOP
192
193 BEGIN
194 l_inv_open_bal := 0;
195 l_amount_applied := 0;
196 l_earned_discount_taken := 0;
197 l_unearned_discount_taken := 0;
198 l_rev_amount_applied := 0;
199 l_rev_earned_discount_taken := 0;
200 l_rev_unearned_discount_taken := 0;
201 l_amount_credited := 0;
202 l_amount_applied_cm :=0; /*bug4502121*/
203
204 -- For CM, get application
205 SELECT NVL(SUM(amount_applied) , 0 ) amount_applied
206 INTO l_amount_applied_cm /*bug4502121*/
207 FROM ar_receivable_applications
208 WHERE payment_schedule_id = tot_inv_rec.payment_schedule_id
209 AND apply_date <= p_as_of_date
210 AND status||'' = 'APP';
211
212 IF upper(p_uncleared_receipts)='Y'
213 THEN
214 -- Cash Application
215 SELECT NVL(SUM(amount_applied) , 0 ) amount_applied,
216 NVL(SUM(earned_discount_taken) ,0) earned_discount_taken,
217 NVL(SUM(unearned_discount_taken) ,0) unearned_discount_taken
218 INTO l_amount_applied
219 , l_earned_discount_taken
220 , l_unearned_discount_taken
221 FROM ar_receivable_applications ra
222 WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
223 AND apply_date <= p_as_of_date
224 AND status||'' = 'APP'
225 AND application_type= 'CASH'
226 AND NOT EXISTS (
227 SELECT 'reversed'
228 FROM ar_cash_receipt_history crh
229 WHERE ra.cash_receipt_id = crh.cash_receipt_id
230 AND crh.status = 'REVERSED'
231 AND crh.trx_date+0 <= p_as_of_date ) ;
232
233 ELSE
234
235 -- Cash Application
236 SELECT NVL(SUM(amount_applied) , 0 ) amount_applied,
237 NVL(SUM(earned_discount_taken) ,0) earned_discount_taken,
238 NVL(SUM(unearned_discount_taken) ,0) unearned_discount_taken
239 INTO l_amount_applied
240 , l_earned_discount_taken
241 , l_unearned_discount_taken
242 FROM ar_receivable_applications ra
243 WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
244 AND apply_date<= p_as_of_date
245 AND status||'' = 'APP'
246 AND application_type= 'CASH'
247 AND NOT EXISTS (
248 SELECT 'reversed'
249 FROM ar_cash_receipt_history crh
250 WHERE ra.cash_receipt_id = crh.cash_receipt_id
251 AND crh.status = 'REVERSED'
252 AND crh.trx_date+0 <= p_as_of_date )
253 AND EXISTS (
254 SELECT 'cleared'
255 FROM ar_cash_receipt_history crh
256 WHERE ra.cash_receipt_id = crh.cash_receipt_id
257 AND crh.status = 'CLEARED'
258 AND crh.trx_date+0 <= p_as_of_date ) ;
259
260 END IF ;
261
262 l_amount_applied := l_amount_applied + l_rev_amount_applied ;
263
264 -- CM Application
265 SELECT NVL(SUM(amount_applied) , 0 ) amount_applied
266 INTO l_amount_credited
267 FROM ar_receivable_applications
268 WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
269 AND apply_date <= p_as_of_date
270 AND status||'' = 'APP'
271 AND application_type= 'CM' ;
272
273 -- Adjustment
274 SELECT NVL(SUM(amount),0)
275 INTO l_amount_adj
276 FROM ar_adjustments
277 WHERE payment_schedule_id = tot_inv_rec.payment_schedule_id
278 AND apply_date+0 <= p_as_of_date
279 AND status = 'A';
280
281 -- invoice balance
282 l_inv_open_bal := tot_inv_rec.amount_due_original
283 - l_amount_applied
284 - l_earned_discount_taken
285 - l_unearned_discount_taken
286 - l_amount_credited
287 + l_amount_applied_cm /*bug4502121*/
288 + l_amount_adj ;
289
290 -- p_min_invoice_balance is not effective in on-account cm
291 IF ( l_inv_open_bal>=nvl(p_min_invoice_balance,0)
292 AND l_inv_open_bal <> 0 )
293 OR ( tot_inv_rec.class = 'CM')
294 THEN
295
296 l_cus_open_bal := l_cus_open_bal + l_inv_open_bal ;
297
298 INSERT INTO AR_CUSTOMER_BALANCE_ITF(Request_id,
299 as_of_date,
300 organization_name,
301 functional_currency_code,
302 customer_name,
303 customer_number,
304 tax_reference_num,
305 address_line1,
306 address_line2,
307 address_line3,
308 address_line4,
309 city,
310 state,
311 zip,
312 country,
313 trans_type,
314 trx_number,
315 transaction_date,
316 trx_currency_code,
317 trans_amount,
318 trans_amount_remaining,
319 receipt_amount,
320 adjustment_amount,
321 earned_discount_amount,
322 unearned_discount_amount,
323 invoice_credit_amount,
324 bank_charge,
325 on_account_credit_amount,
326 on_account_receipts,
327 unapplied_receipts)
328 VALUES (p_request_id,
329 p_as_of_date,
330 l_organization_name,
331 l_functional_currency_code,
332 nvl(siteinfo_rec.translated_customer_name,cusinfo_rec.party_name),
333 cusinfo_rec.account_number,
334 cusinfo_rec.tax_reference,
335 siteinfo_rec.address1,
336 siteinfo_rec.address2,
337 siteinfo_rec.address3,
338 siteinfo_rec.address4,
339 siteinfo_rec.city,
340 siteinfo_rec.state,
341 siteinfo_rec.postal_code,
342 siteinfo_rec.country,
343 tot_inv_rec.class ,
344 tot_inv_rec.trx_number,
345 tot_inv_rec.trx_date,
346 tot_inv_rec.invoice_currency_code ,
347 decode(tot_inv_rec.class , 'CM', 0, nvl(tot_inv_rec.amount_due_original,0)) ,
351 nvl(l_earned_discount_taken ,0),
348 decode(tot_inv_rec.class , 'CM', 0, nvl(l_inv_open_bal,0) ) ,
349 nvl(l_amount_applied,0),
350 nvl(l_amount_adj,0),
352 nvl(l_unearned_discount_taken ,0),
353 nvl(l_amount_credited,0),
354 0,
355 decode(tot_inv_rec.class , 'CM', nvl(l_inv_open_bal ,0) , 0 ) ,
356 0,
357 0 ) ;
358 END IF;
359
360 END;
361
362 -- tot_inv
363 END LOOP;
364
365 -- unapplied receipt and on account receipt
366 IF upper(p_uncleared_receipts)='Y' then
367
368 SELECT nvl(sum(decode(ra.status , 'ACC', amount_applied, 0 )),0),
369 nvl(sum(decode(ra.status ,'UNAPP',amount_applied, 0 )),0)
370 INTO l_on_acct_receipts ,
371 l_unapp_receipts
372 FROM ar_receivable_applications ra,
373 ar_cash_receipts cr
374 WHERE ra.cash_receipt_id = cr.cash_receipt_id
375 AND cr.pay_from_customer = cusinfo_rec.customer_id
376 AND cr.customer_site_use_id = siteinfo_rec.site_use_id
377 AND cr.currency_code = currency_rec.currency_code
378 AND ra.apply_date+0 <= p_as_of_date
379 AND ra.status in ('ACC' , 'UNAPP' )
380 AND NOT EXISTS (
381 SELECT 'reversed'
382 FROM ar_cash_receipt_history crh
383 WHERE ra.cash_receipt_id = crh.cash_receipt_id
384 AND crh.status = 'REVERSED'
385 AND crh.trx_date+0 <= p_as_of_date ) ;
386
387 ELSE
388 /* bug3692732 : Added cr.pay_from_customer = cusinfo_rec.customer_id to where clause
389 to prevent FTS on table ar_cash_receipts */
390
391 SELECT nvl(sum(decode(ra.status , 'ACC', amount_applied, 0 )),0),
392 nvl(sum(decode(ra.status , 'UNAPP', amount_applied, 0)),0)
393 INTO l_on_acct_receipts ,
394 l_unapp_receipts
395 FROM ar_receivable_applications ra,
396 ar_cash_receipts cr
397 WHERE ra.cash_receipt_id = cr.cash_receipt_id
398 AND cr.pay_from_customer = cusinfo_rec.customer_id
399 AND cr.currency_code = currency_rec.currency_code
400 AND cr.customer_site_use_id = siteinfo_rec.site_use_id
401 AND apply_date+0 <= p_as_of_date
402 AND ra.status in ('ACC' , 'UNAPP' )
403 AND NOT EXISTS (
404 SELECT 'reversed'
405 FROM ar_cash_receipt_history crh
406 WHERE ra.cash_receipt_id = crh.cash_receipt_id
407 AND crh.status = 'REVERSED'
408 AND crh.trx_date+0 <= p_as_of_date )
409 AND EXISTS (
410 SELECT 'cleared'
411 FROM ar_cash_receipt_history crh
412 WHERE ra.cash_receipt_id = crh.cash_receipt_id
413 AND crh.status = 'CLEARED'
414 AND crh.trx_date+0 <= p_as_of_date ) ;
415
416 END IF;
417
418
419 IF upper(p_account_receipts)='Y' then
420 l_on_acct_receipts := l_on_acct_receipts
421 + l_rev_on_acct_receipts ;
422 ELSE
423 l_on_acct_receipts := 0 ;
424 END IF;
425
426 IF upper(p_unapp_receipts)='Y' then
427 l_unapp_receipts := l_unapp_receipts
428 + l_rev_unapp_receipts ;
429 ELSE
430 l_unapp_receipts := 0 ;
431 END IF;
432
433 l_cus_open_bal := l_cus_open_bal
434 - l_on_acct_receipts
435 - l_unapp_receipts ;
436
437 IF ( l_unapp_receipts <> 0 ) OR ( l_on_acct_receipts <> 0 )
438 THEN
439 INSERT INTO AR_CUSTOMER_BALANCE_ITF(Request_id,
440 as_of_date,
441 organization_name,
442 functional_currency_code,
443 customer_name,
444 customer_number,
445 tax_reference_num,
446 address_line1,
447 address_line2,
448 address_line3,
449 address_line4,
450 city,
451 state,
452 zip,
453 country,
454 trans_type,
455 trx_number,
456 transaction_date,
457 trx_currency_code,
461 adjustment_amount,
458 trans_amount,
459 trans_amount_remaining,
460 receipt_amount,
462 earned_discount_amount,
463 unearned_discount_amount,
464 invoice_credit_amount,
465 bank_charge,
466 on_account_credit_amount,
467 on_account_receipts,
468 unapplied_receipts)
469 VALUES (p_request_id,
470 p_as_of_date,
471 l_organization_name,
472 l_functional_currency_code,
473 nvl(siteinfo_rec.translated_customer_name,cusinfo_rec.party_name),
474 cusinfo_rec.account_number,
475 cusinfo_rec.tax_reference,
476 siteinfo_rec.address1,
477 siteinfo_rec.address2,
478 siteinfo_rec.address3,
479 siteinfo_rec.address4,
480 siteinfo_rec.city,
481 siteinfo_rec.state,
482 siteinfo_rec.postal_code,
483 siteinfo_rec.country,
484 'PMT' ,
485 'On Account Receipt' ,
486 p_as_of_date,
487 currency_rec.currency_code,
488 0,
489 0,
490 0,
491 0,
492 0,
493 0,
494 0,
495 0,
496 0,
497 l_on_acct_receipts*(-1),
498 l_unapp_receipts*(-1) ) ;
499
500 END IF;
501
502 /* changes for bug 7274982 - start
503 changes done: 1. control goes in only if the open balance is not 0.
504 2. open balance is checked with minimum balance only if p_min_open_balance is not null. */
505
506 IF l_cus_open_bal <> 0 THEN
507 IF p_min_open_balance IS NOT NULL THEN
508 IF (l_cus_open_bal >= p_min_open_balance) THEN
509 COMMIT;
510 ELSE
511 -- rollback for all inserted record for this site.
512 ROLLBACK;
513 END IF;
514 ELSE
515 COMMIT;
516 END IF;
517 ELSE
518 -- rollback for all inserted record for this site.
519 ROLLBACK;
520 END IF;
521 -- changes for bug 7274982 - end
522
523 -- currency
524 END LOOP;
525
526 -- siteinfo
527 END LOOP;
528
529 -- cusinfo
530 END LOOP;
531
532 END ;
533
534
535 --End AR_CUSTOMER_BALANCE
536 end AR_GET_CUSTOMER_BALANCE_PKG ;
537 --End Package