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