[Home] [Help]
PACKAGE BODY: APPS.JL_AR_RECEIVABLE_APPLICATIONS
Source
1 PACKAGE BODY jl_ar_receivable_applications AS
2 /* $Header: jlbrrrab.pls 120.19 2006/12/15 22:51:47 appradha ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PRIVATE FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9
10 PROCEDURE adjustment_generation (
11 x_user_id IN NUMBER,
12 x_rectrx_id IN NUMBER,
13 x_acc_id IN NUMBER,
14 x_amount IN NUMBER,
15 x_receipt_date IN DATE,
16 x_payment_schedule_id IN NUMBER,
17 x_cash_receipt_id IN NUMBER,
18 x_customer_trx_id IN NUMBER
19 )
20 IS
21
22 adj_rec ar_adjustments%ROWTYPE;
23 x_approved_amount VARCHAR2(1);
24 x_adjustment_id ar_adjustments.adjustment_id%TYPE;
25 x_adjustment_number ar_adjustments.adjustment_number%TYPE;
26 x_msg_count NUMBER :=0;
27 x_msg_data VARCHAR2(2000);
28 x_return_status VARCHAR2(1);
29 x_data VARCHAR2(4000);
30 x_msgindex_out NUMBER;
31 x_trans_date Date;
32 x_rcpt_out Date;
33 x_def_rule varchar2(100);
34 x_err_msg varchar2(1000);
35 l_result boolean;
36
37
38 BEGIN
39
40 /* Check if the adjustment amount is within the limit from the user */
41 fnd_file.put_line(fnd_file.log,'adjgen1');
42 fnd_file.put_line(fnd_file.log,' adjustment generation');
43
44 BEGIN
45 SELECT 'Y'
46 INTO x_approved_amount
47 FROM ar_approval_user_limits araul,
48 gl_sets_of_books glsb,
49 ar_system_parameters arsp
50 WHERE araul.user_id = x_user_id
51 AND araul.document_type = 'ADJ'
52 AND glsb.set_of_books_id = arsp.set_of_books_id
53 AND araul.currency_code = glsb.currency_code
54 AND araul.amount_to >= x_amount
55 AND araul.amount_from <= x_amount;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 x_approved_amount:= 'N';
59 END;
60
61 SELECT trx_date
62 INTO x_trans_date
63 FROM ra_customer_trx
64 WHERE customer_trx_id = x_customer_trx_id;
65
66 l_result := ARP_STANDARD.validate_and_default_gl_date(x_receipt_date,x_trans_date,null,null,null,null,null,null,null,null,null,null,x_rcpt_out,x_def_rule,x_err_msg);
67
68 fnd_file.put_line(fnd_file.log,'After validate and def');
69 SELECT x_user_id,
70 sysdate,
71 x_user_id,
72 x_user_id,
73 sysdate,
74 x_amount,
75 sysdate,
76 x_rcpt_out,
77 arsp.set_of_books_id,
78 x_acc_id,
79 'CHARGES',
80 decode(x_approved_amount,'Y','A','M'),
81 decode(x_approved_amount,'Y','A','W'),
82 x_cash_receipt_id,
83 x_customer_trx_id,
84 x_payment_schedule_id,
85 x_rectrx_id,
86 'ARXCAERA',
87 decode(x_approved_amount,'Y','Y','N'),
88 decode(x_approved_amount,'Y',x_user_id,NULL),
89 -3,
90 x_amount,
91 org_id
92 INTO adj_rec.LAST_UPDATED_BY,
93 adj_rec.LAST_UPDATE_DATE,
94 adj_rec.LAST_UPDATE_LOGIN,
95 adj_rec.CREATED_BY,
96 adj_rec.CREATION_DATE,
97 adj_rec.AMOUNT,
98 adj_rec.APPLY_DATE,
99 adj_rec.GL_DATE,
100 adj_rec.SET_OF_BOOKS_ID,
101 adj_rec.CODE_COMBINATION_ID,
102 adj_rec.TYPE,
103 adj_rec.ADJUSTMENT_TYPE,
104 adj_rec.STATUS,
105 adj_rec.ASSOCIATED_CASH_RECEIPT_ID,
106 adj_rec.CUSTOMER_TRX_ID,
107 adj_rec.PAYMENT_SCHEDULE_ID,
108 adj_rec.RECEIVABLES_TRX_ID,
109 adj_rec.CREATED_FROM,
110 adj_rec.POSTABLE,
111 adj_rec.APPROVED_BY,
112 adj_rec.POSTING_CONTROL_ID,
113 adj_rec.ACCTD_AMOUNT,
114 adj_rec.ORG_ID
115 FROM ar_system_parameters arsp;
116 fnd_file.put_line(fnd_file.log,'After ar_system_params');
117
118 arp_util.enable_debug;
119
120 fnd_file.put_line(fnd_file.log,'Before create_adjustment');
121 ar_adjust_pub.create_adjustment( p_api_name => 'AR_ADJUST_PUB',
122 p_api_version => 1.0,
123 p_msg_count => x_msg_count,
124 p_msg_data => x_msg_data,
125 p_return_status => x_return_status,
126 p_adj_rec => adj_rec,
127 p_new_adjust_number => x_adjustment_number,
128 p_new_adjust_id => x_adjustment_id);
129 fnd_file.put_line(fnd_file.log,'After create_adjustment');
130
131 IF PG_DEBUG in ('Y', 'C') THEN
132 arp_util.debug ('adjustment_generation: ' || 'return status: ' || x_return_status);
133 arp_util.debug ('adjustment_generation: ' || 'msg_count : ' || x_msg_count);
134 arp_util.debug ('adjustment_generation: ' || 'msg_data : ' || x_msg_data);
135 END IF;
136
137
138 IF (x_return_status <> 'S') THEN
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug('adjustment_generation: ' || '>>>>>>>>>> Problems during Adjustment Creation');
141 arp_util.debug('adjustment_generation: ' || 'x_return_status : ' || x_return_status);
142 arp_util.debug('adjustment_generation: ' || 'x_msg_count : ' || x_msg_count);
143 arp_util.debug('adjustment_generation: ' || 'x_msg_data : ' || x_msg_data);
144 END IF;
145
146 IF (x_msg_count > 0) THEN
147 FND_MSG_PUB.Get(FND_MSG_PUB.G_FIRST,FND_API.G_TRUE,x_data,x_msgindex_out);
148 FND_MESSAGE.Set_Encoded(x_data);
149 app_exception.raise_exception;
150 ELSE
151 app_exception.raise_exception;
152 END IF;
153
154 END IF;
155
156 IF PG_DEBUG in ('Y', 'C') THEN
157 arp_util.debug ('adjustment_generation: ' || 'Adjustment ID created : ' || x_adjustment_id);
158 arp_util.debug ('adjustment_generation: ' || 'Adjustment Number created : ' || x_adjustment_number);
159 END IF;
160
161 END adjustment_generation;
162
163 PROCEDURE get_accounts (x_rcpt_method_id IN NUMBER,
164 x_bank_acct_id IN NUMBER,
165 x_writeoff_tolerance IN OUT NOCOPY NUMBER,
166 x_writeoff_amount IN OUT NOCOPY NUMBER,
167 x_writeoff_ccid IN OUT NOCOPY NUMBER,
168 x_writeoff_rectrx_id IN OUT NOCOPY NUMBER,
169 x_calc_interest_ccid IN OUT NOCOPY NUMBER,
170 x_calc_interest_rectrx_id IN OUT NOCOPY NUMBER,
171 x_int_revenue_ccid IN OUT NOCOPY NUMBER,
172 x_int_revenue_rectrx_id IN OUT NOCOPY NUMBER,
173 x_return IN OUT NOCOPY NUMBER) IS
174 BEGIN
175
176 x_return := 0;
177 SELECT writeoff_perc_tolerance,
178 writeoff_amount_tolerance,
179 interest_writeoff_rectrx_id,
180 interest_writeoff_ccid,
181 interest_revenue_rectrx_id,
182 interest_revenue_ccid,
183 calculated_interest_ccid,
184 calculated_interest_rectrx_id
185 INTO x_writeoff_tolerance,
186 x_writeoff_amount,
187 x_writeoff_rectrx_id,
188 x_writeoff_ccid,
189 x_int_revenue_rectrx_id,
190 x_int_revenue_ccid,
191 x_calc_interest_ccid,
192 x_calc_interest_rectrx_id
193 FROM jl_br_ar_rec_met_accts_ext
194 WHERE receipt_method_id = x_rcpt_method_id
195 AND bank_acct_use_id = x_bank_acct_id;
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN x_return := -1;
198 END get_accounts;
199
200 PROCEDURE get_ps_parameters(x_payment_schedule_id IN NUMBER,
201 x_amount_due_original IN OUT NOCOPY NUMBER,
202 x_amount_due_remaining IN OUT NOCOPY NUMBER,
203 x_return IN OUT NOCOPY NUMBER) IS
204 BEGIN
205 x_return := 0;
206 SELECT amount_due_original, amount_due_remaining
207 INTO x_amount_due_original, x_amount_due_remaining
208 FROM ar_payment_schedules
209 WHERE payment_schedule_id = x_payment_schedule_id;
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN x_return := -1;
212 END get_ps_parameters;
213
214 PROCEDURE calc_greaterthan_rec( x_writeoff_tolerance IN NUMBER,
215 x_writeoff_amount IN NUMBER,
216 x_calculated_interest IN NUMBER,
217 x_received_interest IN NUMBER,
218 x_payment_amount IN NUMBER,
219 x_rcpt_date IN DATE,
220 x_invoice_amount IN NUMBER,
221 x_payment_schedule_id IN NUMBER,
222 x_writeoff_ccid IN NUMBER,
223 x_writeoff_rectrx_id IN NUMBER,
224 x_calc_interest_ccid IN NUMBER,
225 x_calc_interest_rectrx_id IN NUMBER,
226 x_cash_receipt_id IN NUMBER,
227 x_trx_type_idm IN NUMBER,
228 x_batch_source_idm IN NUMBER,
229 x_receipt_method_idm IN NUMBER,
230 x_user_id IN NUMBER,
231 x_customer_trx_id IN NUMBER,
232 x_interest_difference_action IN VARCHAR2,
233 x_writeoff_date OUT NOCOPY VARCHAR2,
234 x_int_revenue_ccid IN NUMBER) IS
235
236 x_status VARCHAR2(1);
237 x_return VARCHAR2(1);
238
239 x_rcpt_date_char VARCHAR2(30);
240 x_rcpt_date_mm date;
241 x_rcpt_date_mm_char varchar2(11);
242 l_error_code NUMBER;
243 l_error_msg VARCHAR2(2000);
244 l_error_token VARCHAR2(100);
245
246 BEGIN
247
248 if NVL(x_interest_difference_action,'$') = 'WRITEOFF' then
249 IF (( x_writeoff_tolerance IS NOT NULL ) AND
250 (((x_calculated_interest - x_received_interest )/
251 x_invoice_amount) <= x_writeoff_tolerance ))
252 OR
253 (( x_writeoff_amount IS NOT NULL ) AND
254 ((x_calculated_interest - x_received_interest ) <=
255 x_writeoff_amount ))
256 THEN
257 fnd_file.put_line(fnd_file.log,'Before adjustment generation(1)');
258 adjustment_generation (
259 fnd_global.user_id,
260 x_calc_interest_rectrx_id,
261 x_calc_interest_ccid,
262 x_calculated_interest,
263 x_rcpt_date,
264 x_payment_schedule_id,
265 x_cash_receipt_id,
266 x_customer_trx_id
267 );
268
269 fnd_file.put_line(fnd_file.log,'Before adjustment generation(2)');
270 adjustment_generation (
271 fnd_global.user_id,
272 x_writeoff_rectrx_id,
273 x_writeoff_ccid,
274 x_received_interest - x_calculated_interest,
275 x_rcpt_date,
276 x_payment_schedule_id,
277 x_cash_receipt_id,
278 x_customer_trx_id
279 );
280 ELSE
281 fnd_file.put_line(fnd_file.log,'Before adjustment generation(3)');
282 adjustment_generation (
283 fnd_global.user_id,
284 x_calc_interest_rectrx_id,
285 x_calc_interest_ccid,
286 x_received_interest,
287 x_rcpt_date,
288 x_payment_schedule_id,
289 x_cash_receipt_id,
290 x_customer_trx_id
291 );
292 fnd_file.put_line(fnd_file.log,'Before adjustment generation(4)');
293 adjustment_generation (
294 fnd_global.user_id,
295 x_calc_interest_rectrx_id,
296 x_calc_interest_ccid,
297 x_calculated_interest - x_received_interest,
298 x_rcpt_date,
299 x_payment_schedule_id,
300 x_cash_receipt_id,
301 x_customer_trx_id
302 );
303 fnd_file.put_line(fnd_file.log,'Before adjustment generation(5)');
304 adjustment_generation (
305 fnd_global.user_id,
306 x_writeoff_rectrx_id,
307 x_writeoff_ccid,
308 x_received_interest - x_calculated_interest,
309 x_rcpt_date,
310 x_payment_schedule_id,
311 x_cash_receipt_id,
312 x_customer_trx_id
313 );
314 END IF;
315 x_writeoff_date := fnd_date.date_to_canonical(x_rcpt_date);
316
317 elsif NVL(x_interest_difference_action,'$') = 'GENERATE_IDM' then
318 fnd_file.put_line(fnd_file.log,'Before generate idm');
319 adjustment_generation (
320 fnd_global.user_id,
321 x_calc_interest_rectrx_id,
322 x_calc_interest_ccid,
323 x_received_interest,
324 x_rcpt_date,
325 x_payment_schedule_id,
326 x_cash_receipt_id,
327 x_customer_trx_id
328 );
329
330 x_rcpt_date_char := to_char(x_rcpt_date,'DD-MON-YYYY');
331
332
333 /* Date format for Stored Procedures */
334 x_rcpt_date_mm := to_date(x_rcpt_date_char,'DD-MM-YYYY');
335 x_rcpt_date_mm_char := to_char(x_rcpt_date_mm,'DD-MM-YYYY');
336
337 fnd_file.put_line(fnd_file.log,'Before jlbrinterestdebitmemo');
338
339 jl_br_ar_generate_debit_memo.jl_br_interest_debit_memo (x_customer_trx_id,
340 x_calculated_interest - x_received_interest ,
341 x_user_id,
342 x_trx_type_idm,
343 x_batch_source_idm,
344 x_receipt_method_idm,
345 x_payment_schedule_id ,
346 x_rcpt_date_mm_char,
347 x_return,
348 x_int_revenue_ccid,
349 l_error_code,
350 l_error_msg,
351 l_error_token);
352 fnd_file.put_line(fnd_file.log,'After jlbrinterestdebitmemo'||x_return);
353 IF x_return = '1' THEN
354 fnd_file.put_line(fnd_file.log,'After jlbrinterestdebitmemo');
355 IF PG_DEBUG in ('Y', 'C') THEN
356 arp_util.debug('calc_greaterthan_rec: ' || 'PROBLEMA NA CRIACAO DA NOTA DE DEBITO DE JUROS !!!');
357 END IF;
358 END IF;
359
360 end if;
361 END calc_greaterthan_rec;
362
363
364 PROCEDURE calc_greaterthan_rec_tol( x_writeoff_tolerance IN NUMBER,
365 x_writeoff_amount IN NUMBER,
366 x_calculated_interest IN NUMBER,
367 x_received_interest IN NUMBER,
368 x_payment_amount IN NUMBER,
369 x_rcpt_date IN DATE,
370 x_invoice_amount IN NUMBER,
371 x_payment_schedule_id IN NUMBER,
372 x_writeoff_ccid IN NUMBER,
373 x_writeoff_rectrx_id IN NUMBER,
374 x_calc_interest_ccid IN NUMBER,
375 x_calc_interest_rectrx_id IN NUMBER,
376 x_cash_receipt_id IN NUMBER,
377 x_trx_type_idm IN NUMBER,
378 x_batch_source_idm IN NUMBER,
379 x_receipt_method_idm IN NUMBER,
380 x_user_id IN NUMBER,
381 x_customer_trx_id IN NUMBER,
382 x_writeoff_date OUT NOCOPY VARCHAR2,
383 x_int_revenue_ccid IN NUMBER) IS
384 x_return NUMBER;
385 l_error_code NUMBER;
386 l_error_msg VARCHAR2(2000);
387 l_error_token VARCHAR2(100);
388 BEGIN
389 fnd_file.put_line(fnd_file.log,'Before calc greaterthan rec tol');
390 IF (( x_writeoff_tolerance IS NOT NULL ) AND
391 (((x_calculated_interest - x_received_interest )/
392 x_invoice_amount) <= x_writeoff_tolerance ))
393 OR
394 (( x_writeoff_amount IS NOT NULL ) AND
395 ((x_calculated_interest - x_received_interest ) <=
396 x_writeoff_amount ))
397 THEN
398 fnd_file.put_line(fnd_file.log,'Before adjustment generation 6');
399 adjustment_generation (
400 fnd_global.user_id,
401 x_calc_interest_rectrx_id,
402 x_calc_interest_ccid,
403 x_calculated_interest,
404 x_rcpt_date,
405 x_payment_schedule_id,
406 x_cash_receipt_id,
407 x_customer_trx_id);
408
409 fnd_file.put_line(fnd_file.log,'Before adjustment generation 7');
410 fnd_file.put_line(fnd_file.log,'After 1st adjustment');
411 adjustment_generation (
412 fnd_global.user_id,
413 x_writeoff_rectrx_id,
414 x_writeoff_ccid,
415 x_received_interest - x_calculated_interest,
416 x_rcpt_date,
417 x_payment_schedule_id,
418 x_cash_receipt_id,
419 x_customer_trx_id);
420 fnd_file.put_line(fnd_file.log,'After 2nd adjustment');
421
422 x_writeoff_date := fnd_date.date_to_canonical(x_rcpt_date);
423
424 fnd_file.put_line(fnd_file.log,'After 3rd adjustment');
425
426 ELSE
427 IF nvl(x_received_interest,0) > 0 THEN
428 fnd_file.put_line(fnd_file.log,'Before adjustment generation 8');
429 adjustment_generation (
430 fnd_global.user_id,
431 x_calc_interest_rectrx_id,
432 x_calc_interest_ccid,
433 x_received_interest,
434 x_rcpt_date,
435 x_payment_schedule_id,
436 x_cash_receipt_id,
437 x_customer_trx_id);
438 END IF;
439
440 fnd_file.put_line(fnd_file.log,'Before debit memogeneration 8');
441 jl_br_ar_generate_debit_memo.jl_br_interest_debit_memo (
442 x_customer_trx_id,
443 x_calculated_interest - x_received_interest ,
444 x_user_id,
445 x_trx_type_idm,
446 x_batch_source_idm,
447 x_receipt_method_idm,
448 x_payment_schedule_id ,
449 to_char(x_rcpt_date,'DD-MM-YYYY'),
450 x_return,
451 x_int_revenue_ccid,
452 l_error_code,
453 l_error_msg,
454 l_error_token
455 );
456
457 IF x_return = '1' THEN
458 IF PG_DEBUG in ('Y', 'C') THEN
459 arp_util.debug('calc_greaterthan_rec: ' || 'PROBLEMA NA CRIACAO DA NOTA DE DEBITO DE JUROS !!!');
460 END IF;
461 END IF;
462 END IF;
463 END calc_greaterthan_rec_tol;
464
465 PROCEDURE calc_lessthan_rec( x_writeoff_tolerance IN NUMBER,
466 x_writeoff_amount IN NUMBER,
467 x_calculated_interest IN NUMBER,
468 x_received_interest IN NUMBER,
469 x_payment_amount IN NUMBER,
470 x_rcpt_date IN DATE,
471 x_payment_schedule_id IN NUMBER,
472 x_int_revenue_ccid IN NUMBER,
473 x_int_revenue_rectrx_id IN NUMBER,
474 x_calc_interest_ccid IN NUMBER,
475 x_calc_interest_rectrx_id IN NUMBER,
476 x_cash_receipt_id IN NUMBER,
477 x_user_id IN NUMBER,
478 x_customer_trx_id IN NUMBER) IS
479 BEGIN
480 IF NVL(x_calculated_interest ,0) > 0 THEN
481 fnd_file.put_line(fnd_file.log,'Before adjustment generation 9');
482 adjustment_generation (
483 fnd_global.user_id,
484 x_calc_interest_rectrx_id,
485 x_calc_interest_ccid,
486 x_calculated_interest ,
487 x_rcpt_date,
488 x_payment_schedule_id,
489 x_cash_receipt_id,
490 x_customer_trx_id );
491 END IF;
492 fnd_file.put_line(fnd_file.log,'Before adjustment generation 10');
493 adjustment_generation (
494 fnd_global.user_id,
495 x_int_revenue_rectrx_id,
496 x_int_revenue_ccid,
497 x_received_interest - x_calculated_interest ,
498 x_rcpt_date,
499 x_payment_schedule_id,
500 x_cash_receipt_id,
501 x_customer_trx_id );
502 END calc_lessthan_rec;
503
504 PROCEDURE calc_equal_rec( x_writeoff_tolerance IN NUMBER,
505 x_writeoff_amount IN NUMBER,
506 x_calculated_interest IN NUMBER,
507 x_received_interest IN NUMBER,
508 x_payment_amount IN NUMBER,
509 x_rcpt_date IN DATE,
510 x_payment_schedule_id IN NUMBER,
511 x_int_revenue_ccid IN NUMBER,
512 x_int_revenue_rectrx_id IN NUMBER,
513 x_calc_interest_ccid IN NUMBER,
514 x_calc_interest_rectrx_id IN NUMBER,
515 x_cash_receipt_id IN NUMBER,
516 x_user_id IN NUMBER,
517 x_customer_trx_id IN NUMBER) IS
518 BEGIN
519 fnd_file.put_line(fnd_file.log,'Before adjustment generation 11');
520 adjustment_generation (
521 fnd_global.user_id,
522 x_calc_interest_rectrx_id,
523 x_calc_interest_ccid,
524 x_calculated_interest ,
525 x_rcpt_date,
526 x_payment_schedule_id,
527 x_cash_receipt_id,
528 x_customer_trx_id );
529 END calc_equal_rec;
530
531 PROCEDURE interest_treatment (
532 x_payment_schedule_id IN NUMBER,
533 x_customer_trx_id IN NUMBER,
534 x_payment_amount IN NUMBER,
535 x_due_date IN DATE,
536 x_calc_interest IN VARCHAR2,
537 x_rec_interest IN VARCHAR2,
538 x_main_amount_received IN VARCHAR2,
539 x_base_interest_calc IN VARCHAR2,
540 x_interest_payment_date IN VARCHAR2,
541 x_interest_diff_action VARCHAR2,
542 x_cash_receipt_id IN NUMBER,
543 x_rcpt_date IN DATE,
544 x_rcpt_method_id IN NUMBER,
545 x_trx_type_idm IN NUMBER,
546 x_batch_source_idm IN NUMBER,
547 x_receipt_method_idm IN NUMBER,
548 x_user_id IN NUMBER,
549 x_remit_bank_acct_id IN NUMBER,
550 x_writeoff_date OUT NOCOPY VARCHAR2
551 )
552 IS
553 x_writeoff_tolerance NUMBER;
554 x_writeoff_amount NUMBER;
555 x_writeoff_ccid NUMBER(15);
556 x_writeoff_rectrx_id NUMBER(15);
557 x_calc_interest_ccid NUMBER(15);
558 x_calc_interest_rectrx_id NUMBER(15);
559 x_int_revenue_ccid NUMBER(15);
560 x_int_revenue_rectrx_id NUMBER(15);
561 x_return NUMBER;
562 x_amount_due_original NUMBER;
563 x_amount_due_remaining NUMBER;
564 x_calculated_interest NUMBER;
565 x_received_interest NUMBER;
566 BEGIN
567
568 x_calculated_interest := fnd_number.canonical_to_number(x_calc_interest);
569 x_received_interest := fnd_number.canonical_to_number(x_rec_interest);
570
571 fnd_file.put_line(fnd_file.log,'Inside Int treatment');
572 fnd_file.put_line(fnd_file.log,'calc int'||to_char(x_calculated_interest));
573 fnd_file.put_line(fnd_file.log,'rec int'||to_char(x_received_interest));
574
575 fnd_file.put_line(fnd_file.log,'Before get_accounts()');
576 get_accounts(x_rcpt_method_id,
577 x_remit_bank_acct_id,
578 x_writeoff_tolerance,
579 x_writeoff_amount,
580 x_writeoff_ccid,
581 x_writeoff_rectrx_id,
582 x_calc_interest_ccid,
583 x_calc_interest_rectrx_id,
584 x_int_revenue_ccid,
585 x_int_revenue_rectrx_id,
586 x_return);
587
588 fnd_file.put_line(fnd_file.log,'After get_accounts');
589
590 get_ps_parameters(x_payment_schedule_id,
591 x_amount_due_original,
592 x_amount_due_remaining,
593 x_return);
594
595 fnd_file.put_line(fnd_file.log,'After get_psparams');
596
597 IF NVL(x_calculated_interest ,0) > NVL(x_received_interest ,0) THEN
598 IF x_interest_diff_action is NOT NULL THEN
599 fnd_file.put_line(fnd_file.log,'calc_greater_than_rec');
600 calc_greaterthan_rec( x_writeoff_tolerance,
601 x_writeoff_amount,
602 x_calculated_interest,
603 x_received_interest,
604 x_payment_amount,
605 x_rcpt_date,
606 x_amount_due_original,
607 x_payment_schedule_id,
608 x_writeoff_ccid,
609 x_writeoff_rectrx_id,
610 x_calc_interest_ccid,
611 x_calc_interest_rectrx_id,
612 x_cash_receipt_id,
613 x_trx_type_idm,
614 x_batch_source_idm,
615 x_receipt_method_idm,
616 x_user_id,
617 x_customer_trx_id,
618 x_interest_diff_action,
619 x_writeoff_date,
620 x_int_revenue_ccid);
621 ELSE
622 fnd_file.put_line(fnd_file.log,'calc_greater_than_rec_tol');
623 calc_greaterthan_rec_tol( x_writeoff_tolerance,
624 x_writeoff_amount,
625 x_calculated_interest,
626 x_received_interest,
627 x_payment_amount,
628 x_rcpt_date,
629 x_amount_due_original,
630 x_payment_schedule_id,
631 x_writeoff_ccid,
632 x_writeoff_rectrx_id,
633 x_calc_interest_ccid,
634 x_calc_interest_rectrx_id,
635 x_cash_receipt_id,
636 x_trx_type_idm,
637 x_batch_source_idm,
638 x_receipt_method_idm,
639 x_user_id,
640 x_customer_trx_id,
641 x_writeoff_date,
642 x_int_revenue_ccid);
643 END IF;
644
645 ELSIF NVL(x_calculated_interest ,0) < NVL(x_received_interest ,0) THEN
646 fnd_file.put_line(fnd_file.log,'calc_less_than_rec_tol');
647 calc_lessthan_rec( x_writeoff_tolerance,
648 x_writeoff_amount,
649 x_calculated_interest,
650 x_received_interest,
651 x_payment_amount,
652 x_rcpt_date,
653 x_payment_schedule_id,
654 x_int_revenue_ccid,
655 x_int_revenue_rectrx_id,
656 x_calc_interest_ccid,
657 x_calc_interest_rectrx_id,
658 x_cash_receipt_id,
659 x_user_id,
660 x_customer_trx_id);
661 ELSIF x_received_interest > 0 THEN
662 fnd_file.put_line(fnd_file.log,'calc_equal_rec');
663 calc_equal_rec( x_writeoff_tolerance,
664 x_writeoff_amount,
665 x_calculated_interest,
666 x_received_interest,
667 x_payment_amount,
668 x_rcpt_date,
669 x_payment_schedule_id,
670 x_int_revenue_ccid,
671 x_int_revenue_rectrx_id,
672 x_calc_interest_ccid,
673 x_calc_interest_rectrx_id,
674 x_cash_receipt_id,
675 x_user_id,
676 x_customer_trx_id);
677 END IF;
678 END interest_treatment;
679
680 PROCEDURE Apply_br(p_apply_before_after IN VARCHAR2 ,
681 p_global_attribute_category IN VARCHAR2 ,
682 p_set_of_books_id IN NUMBER ,
683 p_cash_receipt_id IN VARCHAR2 ,
684 p_receipt_date IN DATE ,
685 p_applied_payment_schedule_id IN NUMBER ,
686 p_amount_applied IN NUMBER ,
687 p_unapplied_amount IN NUMBER ,
688 p_due_date IN DATE ,
689 p_receipt_method_id IN NUMBER ,
690 p_remittance_bank_account_id IN NUMBER ,
691 p_global_attribute1 IN OUT NOCOPY VARCHAR2 ,
692 p_global_attribute2 IN OUT NOCOPY VARCHAR2 ,
693 p_global_attribute3 IN OUT NOCOPY VARCHAR2 ,
694 p_global_attribute4 IN OUT NOCOPY VARCHAR2 ,
695 p_global_attribute5 IN OUT NOCOPY VARCHAR2 ,
696 p_global_attribute6 IN OUT NOCOPY VARCHAR2 ,
697 p_global_attribute7 IN OUT NOCOPY VARCHAR2 ,
698 p_global_attribute8 IN OUT NOCOPY VARCHAR2 ,
699 p_global_attribute9 IN OUT NOCOPY VARCHAR2 ,
700 p_global_attribute10 IN OUT NOCOPY VARCHAR2 ,
701 p_global_attribute11 IN OUT NOCOPY VARCHAR2 ,
702 p_global_attribute12 IN OUT NOCOPY VARCHAR2 ,
703 p_global_attribute13 IN OUT NOCOPY VARCHAR2 ,
704 p_global_attribute14 IN OUT NOCOPY VARCHAR2 ,
705 p_global_attribute15 IN OUT NOCOPY VARCHAR2 ,
706 p_global_attribute16 IN OUT NOCOPY VARCHAR2 ,
707 p_global_attribute17 IN OUT NOCOPY VARCHAR2 ,
708 p_global_attribute18 IN OUT NOCOPY VARCHAR2 ,
709 p_global_attribute19 IN OUT NOCOPY VARCHAR2 ,
710 p_global_attribute20 IN OUT NOCOPY VARCHAR2 ,
711 p_return_status OUT NOCOPY VARCHAR2) IS
712
713 validation_error exception;
714
715 x_amount_due_remaining NUMBER;
716 x_customer_trx_id NUMBER;
717 x_interest_type VARCHAR2(30);
718 x_interest_rate_amount NUMBER(38,2);
719 x_interest_period NUMBER;
720 x_interest_formula VARCHAR2(30);
721 x_interest_grace_days NUMBER;
722 x_penalty_type VARCHAR2(30);
723 x_penalty_rate_amount NUMBER(38,2);
724 x_invoice_amount NUMBER(38,2);
725 x_calculated_interest NUMBER(38,2);
726 x_calculated_interest_out NUMBER(38,2);
727 x_city VARCHAR2(80);
728 x_state VARCHAR2(60);
729 x_calendar VARCHAR2(30);
730 x_payment_action VARCHAR2(30);
731 x_days_late NUMBER;
732 x_payment_date DATE;
733 x_trans_date DATE;
734 x_trx_type_idm NUMBER;
735 x_batch_source_idm NUMBER;
736 x_receipt_method_idm NUMBER;
737 x_exit_status NUMBER;
738 x_org_id ar_payment_schedules.org_id%TYPE;
739 errcode NUMBER;
740 l_ps_rec ar_payment_schedules%ROWTYPE;
741
742 BEGIN
743
744 IF PG_DEBUG in ('Y', 'C') THEN
745 arp_util.debug('jl_ar_receivable_applications.Apply_br()+');
746 END IF;
747
748 p_return_status := FND_API.G_RET_STS_SUCCESS;
749
750 IF p_apply_before_after = 'BEFORE' THEN
751
752 IF p_due_date < p_receipt_date THEN
753
754
755 x_calendar := jl_zz_sys_options_pkg.get_calendar(mo_global.get_current_org_id);
756
757 /* Bug 2374054 : Multiorg changes */
758 BEGIN
759 SELECT org_id
760 INTO x_org_id
761 FROM ar_payment_schedules
762 WHERE payment_schedule_id = p_applied_payment_schedule_id;
763 EXCEPTION
764 WHEN OTHERS THEN
765 IF PG_DEBUG in ('Y', 'C') THEN
766 arp_util.debug('Apply_br: ' || to_char(SQLCODE));
767 END IF;
768 END;
769 -- fnd_profile.get('JLBR_PAYMENT_ACTION',x_payment_action);
770 x_payment_action := jl_zz_sys_options_pkg.get_payment_action_AR(x_org_id);
771
772 IF x_calendar IS Null THEN
773 FND_MESSAGE.SET_NAME('JL','JL_BR_CALENDAR_PROFILE');
774 FND_MSG_PUB.Add;
775 RAISE validation_error;
776 END IF;
777 IF x_payment_action IS Null THEN
778 FND_MESSAGE.SET_NAME('JL','JL_BR_PAYMENT_ACTION_PROFILE');
779 FND_MSG_PUB.Add;
780 RAISE validation_error;
781 END IF;
782
783 JL_ZZ_AR_LIBRARY_1_PKG.get_idm_profiles_from_syspa (
784 x_trx_type_idm,
785 x_batch_source_idm,
786 x_receipt_method_idm,
787 1,
788 errcode);
789
790 IF errcode <> 0 THEN
791 IF PG_DEBUG in ('Y', 'C') THEN
792 arp_util.debug('Apply_br: ' || to_char(SQLCODE));
793 END IF;
794 RAISE validation_error;
795 END IF;
796
797 IF x_trx_type_idm IS Null THEN
798 FND_MESSAGE.SET_NAME('JL','JL_BR_AR_TRX_TYPE_PROFILE');
799 FND_MSG_PUB.Add;
800 RAISE validation_error;
801 END IF;
802
803 IF x_batch_source_idm IS Null THEN
804 FND_MESSAGE.SET_NAME('JL','JL_BR_AR_BATCH_SOURCE_PROFILE');
805 FND_MSG_PUB.Add;
806 RAISE validation_error;
807 END IF;
808
809 IF x_receipt_method_idm IS Null THEN
810 FND_MESSAGE.SET_NAME('JL','JL_BR_AR_REC_METHOD_PROFILE');
811 FND_MSG_PUB.Add;
812 RAISE validation_error;
813 END IF;
814
815 IF p_global_attribute1 < 0 THEN
816 FND_MESSAGE.SET_NAME('JL','JL_BR_INVALID_MAIN_AMOUNT');
817 FND_MSG_PUB.Add;
818 RAISE validation_error;
819 END IF;
820
821 IF p_global_attribute4 < 0 THEN
822 FND_MESSAGE.SET_NAME('JL','JL_BR_INVALID_INT_AMOUNT');
823 FND_MSG_PUB.Add;
824 RAISE validation_error;
825 END IF;
826
827 IF p_global_attribute1 is NULL THEN
828 p_global_attribute1 := p_amount_applied - nvl(p_global_attribute4,0);
829 END IF;
830
831 IF p_global_attribute2 is NULL THEN
832 p_global_attribute2 := 'TOTAL';
833 END IF;
834
835 IF p_global_attribute1 + nvl(p_global_attribute4,0) > p_amount_applied
836 THEN
837 FND_MESSAGE.SET_NAME('JL','JL_BR_INVALID_MAIN_RCVD_SUM');
838 FND_MSG_PUB.Add;
839 RAISE validation_error;
840 END IF;
841
842 BEGIN
843
844 SELECT amount_due_remaining,
845 global_attribute7
846 INTO x_amount_due_remaining,
847 p_global_attribute11
848 FROM ar_payment_schedules
849 WHERE payment_schedule_id = p_applied_payment_schedule_id;
850
851 EXCEPTION
852 WHEN OTHERS THEN
853 RAISE validation_error;
854 END;
855
856 x_payment_date := fnd_date.canonical_to_date(p_global_attribute11);
857
858 JL_ZZ_AR_LIBRARY_1_PKG.get_customer_trx_id(
859 p_applied_payment_schedule_id,
860 x_customer_trx_id,
861 x_trans_date,
862 1,
863 errcode);
864 IF errcode <> 0 THEN
865 IF PG_DEBUG in ('Y', 'C') THEN
866 arp_util.debug('Apply_br: ' || to_char(SQLCODE));
867 END IF;
868 RAISE validation_error;
869 END IF;
870
871 JL_ZZ_AR_LIBRARY_1_PKG.get_customer_interest_dtls (
872 x_customer_trx_id,
873 x_interest_type,
874 x_interest_rate_amount,
875 x_interest_period,
876 x_interest_formula,
877 x_interest_grace_days,
878 x_penalty_type,
879 x_penalty_rate_amount,
880 1,
881 errcode);
882 IF errcode <> 0 THEN
883 IF PG_DEBUG in ('Y', 'C') THEN
884 arp_util.debug('Apply_br: ' || to_char(SQLCODE));
885 END IF;
886 RAISE validation_error;
887 END IF;
888
889 JL_ZZ_AR_LIBRARY_1_PKG.get_city_from_ra_addresses (
890 p_applied_payment_schedule_id,
891 x_city,
892 1,
893 errcode,
894 x_state); --Bug # 2319552
895 IF errcode <> 0 THEN
896 IF PG_DEBUG in ('Y', 'C') THEN
897 arp_util.debug('Apply_br: ' || to_char(SQLCODE));
898 END IF;
899 RAISE validation_error;
900 END IF;
901
902 IF p_global_attribute2 = 'TOTAL' THEN
903 x_invoice_amount := x_amount_due_remaining;
904 ELSIF p_global_attribute2 = 'PARTIAL' THEN
905 x_invoice_amount := p_global_attribute1;
906 END IF;
907
908 JL_BR_INTEREST_HANDLING.JL_BR_INTEREST(
909 x_interest_type,
910 x_interest_rate_amount,
911 x_interest_period,
912 x_interest_formula,
913 x_interest_grace_days,
914 x_penalty_type,
915 x_penalty_rate_amount,
916 p_due_date,
917 x_payment_date,
918 x_invoice_amount,
919 x_calendar,
920 x_city,
921 x_payment_action,
922 x_calculated_interest,
923 x_days_late,
924 x_exit_status,
925 x_state); --Bug # 2319552
926
927 IF x_exit_status = 0 THEN
928 x_calculated_interest_out := NVL(x_calculated_interest,0);
929 ELSE
930 FND_MESSAGE.SET_NAME('JL','JL_BR_INCONSISTENT_DATE');
931 FND_MSG_PUB.Add;
932 RAISE validation_error;
933 END IF;
934
935 p_global_attribute3 := x_calculated_interest_out;
936
937 IF (p_global_attribute2 = 'TOTAL')
938 AND (x_calculated_interest_out <> 0) THEN
939 p_global_attribute7 := p_receipt_date;
940 ELSE
941 p_global_attribute7 := '';
942 END IF;
943
944 interest_treatment (
945 p_applied_payment_schedule_id,
946 x_customer_trx_id,
947 p_amount_applied,
948 p_due_date,
949 fnd_number.canonical_to_number(p_global_attribute3),
950 fnd_number.canonical_to_number(p_global_attribute4),
951 fnd_number.canonical_to_number(p_global_attribute1),
952 p_global_attribute2,
953 fnd_date.canonical_to_date(p_global_attribute7),
954 p_global_attribute5,
955 p_cash_receipt_id,
956 p_receipt_date,
957 p_receipt_method_id,
958 x_trx_type_idm,
959 x_batch_source_idm,
960 x_receipt_method_idm,
961 fnd_global.user_id,
962 p_remittance_bank_account_id,
963 p_global_attribute8);
964
965 ELSE
966
967 p_global_attribute1 := '';
968 p_global_attribute2 := '';
969 p_global_attribute3 := '';
970 p_global_attribute4 := '';
971 p_global_attribute5 := '';
972 p_global_attribute6 := '';
973 p_global_attribute7 := '';
974 p_global_attribute8 := '';
975 p_global_attribute9 := '';
976 p_global_attribute10 := '';
977 p_global_attribute11 := '';
978
979 END IF;
980
981 ELSIF p_apply_before_after = 'AFTER' THEN
982
983 /* UPDATE ar_payment_schedules
984 SET global_attribute1 = p_global_attribute1,
985 global_attribute2 = p_global_attribute2,
986 global_attribute3 = p_global_attribute3,
987 global_attribute4 = p_global_attribute4,
988 global_attribute5 = p_global_attribute5,
989 global_attribute6 = p_global_attribute6,
990 global_attribute7 = nvl(p_global_attribute7,p_global_attribute11),
991 global_attribute15 = p_global_attribute8
992 WHERE payment_schedule_id = p_applied_payment_schedule_id;
993 */
994
995 /* Replace Update by AR's table handlers. Bug # 2249731 */
996
997 arp_ps_pkg.fetch_p(p_applied_payment_schedule_id, l_ps_rec);
998 arp_ps_pkg.lock_p(p_applied_payment_schedule_id);
999 l_ps_rec.global_attribute1 := p_global_attribute1;
1000 l_ps_rec.global_attribute2 := p_global_attribute2;
1001 l_ps_rec.global_attribute3 := p_global_attribute3;
1002 l_ps_rec.global_attribute4 := p_global_attribute4;
1003 l_ps_rec.global_attribute5 := p_global_attribute5;
1004 l_ps_rec.global_attribute6 := p_global_attribute6;
1005 l_ps_rec.global_attribute7 := nvl(p_global_attribute7, p_global_attribute11);
1006 l_ps_rec.global_attribute15 := p_global_attribute8;
1007 arp_ps_pkg.update_p(l_ps_rec, p_applied_payment_schedule_id);
1008
1009 END IF;
1010
1011 IF PG_DEBUG in ('Y', 'C') THEN
1012 arp_util.debug('jl_ar_receivable_applications.Apply_br()-');
1013 END IF;
1014
1015 EXCEPTION
1016 WHEN validation_error THEN
1017 p_return_status := FND_API.G_RET_STS_ERROR;
1018
1019 END Apply_br;
1020 PROCEDURE Unapply_br(
1021 p_cash_receipt_id IN VARCHAR2 ,
1022 p_applied_payment_schedule_id IN NUMBER ,
1023 p_return_status OUT NOCOPY VARCHAR2) IS
1024
1025 x_apply_date DATE;
1026 x_main_amnt_rec VARCHAR2(30);
1027 x_base_int_calc VARCHAR2(30);
1028 x_calculated_interest VARCHAR2(30);
1029 x_received_interest VARCHAR2(30);
1030 x_int_diff_action VARCHAR2(30);
1031 x_int_writeoff_reason VARCHAR2(30);
1032 x_payment_date VARCHAR2(30);
1033 x_writeoff_date VARCHAR2(80);
1034 x_error_code NUMBER;
1035 l_ps_rec ar_payment_schedules%ROWTYPE;
1036
1037 BEGIN
1038
1039 IF PG_DEBUG in ('Y', 'C') THEN
1040 arp_util.debug('jl_ar_receivable_applications.Unapply_br()+');
1041 END IF;
1042
1043 p_return_status := FND_API.G_RET_STS_SUCCESS;
1044
1045 BEGIN
1046 SELECT apply_date
1047 INTO x_apply_date
1048 FROM ar_receivable_applications
1049 WHERE cash_receipt_id = p_cash_receipt_id
1050 AND applied_payment_schedule_id = p_applied_payment_schedule_id;
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 IF PG_DEBUG in ('Y', 'C') THEN
1054 arp_util.debug('Unapply_br: ' || to_char(SQLCODE));
1055 END IF;
1056 p_return_status := FND_API.G_RET_STS_ERROR;
1057 END;
1058
1059 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
1060
1061 jl_zz_ar_library_1_pkg.get_prev_interest_values(p_applied_payment_schedule_id,
1062 p_cash_receipt_id,
1063 x_apply_date,
1064 x_main_amnt_rec,
1065 x_base_int_calc,
1066 x_calculated_interest,
1067 x_received_interest,
1068 x_int_diff_action,
1069 x_int_writeoff_reason,
1070 x_payment_date,
1071 x_writeoff_date,
1072 x_error_code);
1073
1074 IF x_error_code = 0 then
1075
1076 /* UPDATE ar_payment_schedules
1077 SET global_attribute1 = x_main_amnt_rec,
1078 global_attribute2 = x_base_int_calc,
1079 global_attribute3 = x_calculated_interest,
1080 global_attribute4 = x_received_interest,
1081 global_attribute5 = x_int_diff_action,
1082 global_attribute6 = x_int_writeoff_reason,
1083 global_attribute7 = x_payment_date,
1084 global_attribute15 = x_writeoff_date
1085 WHERE payment_schedule_id = p_applied_payment_schedule_id;
1086 */
1087
1088 /* Replace Update by AR's table handlers. Bug # 2249731 */
1089
1090 arp_ps_pkg.fetch_p(p_applied_payment_schedule_id, l_ps_rec);
1091 arp_ps_pkg.lock_p(p_applied_payment_schedule_id);
1092 l_ps_rec.global_attribute1 := x_main_amnt_rec;
1093 l_ps_rec.global_attribute2 := x_base_int_calc;
1094 l_ps_rec.global_attribute3 := x_calculated_interest;
1095 l_ps_rec.global_attribute4 := x_received_interest;
1096 l_ps_rec.global_attribute5 := x_int_diff_action;
1097 l_ps_rec.global_attribute6 := x_int_writeoff_reason;
1098 l_ps_rec.global_attribute7 := x_payment_date;
1099 l_ps_rec.global_attribute15 := x_writeoff_date;
1100 arp_ps_pkg.update_p(l_ps_rec, p_applied_payment_schedule_id);
1101
1102 ELSE
1103 p_return_status := FND_API.G_RET_STS_ERROR;
1104 END IF;
1105
1106 END IF;
1107
1108 IF PG_DEBUG in ('Y', 'C') THEN
1109 arp_util.debug('jl_ar_receivable_applications.Unapply_br()-');
1110 END IF;
1111
1112 END Unapply_br;
1113
1114 PROCEDURE Reverse_br(
1115 p_cash_receipt_id IN NUMBER,
1116 p_return_status OUT NOCOPY VARCHAR2) IS
1117
1118 Cursor pay_sched is
1119 Select applied_payment_schedule_id pay_sched_id,
1120 global_attribute3 calculated_interest,
1121 apply_date
1122 from ar_receivable_applications
1123 where status = 'APP'
1124 and cash_receipt_id = p_cash_receipt_id;
1125
1126 ps_rec pay_sched%ROWTYPE;
1127
1128 x_main_amnt_rec VARCHAR2(30);
1129 x_base_int_calc VARCHAR2(30);
1130 x_calculated_interest VARCHAR2(30);
1131 x_received_interest VARCHAR2(30);
1132 x_int_diff_action VARCHAR2(30);
1133 x_int_writeoff_reason VARCHAR2(30);
1134 x_payment_date VARCHAR2(30);
1135 x_writeoff_date VARCHAR2(30);
1136
1137 x_error_code NUMBER;
1138 x_interest_reversal BOOLEAN;
1139 l_ps_rec ar_payment_schedules%ROWTYPE;
1140
1141 BEGIN
1142
1143 IF PG_DEBUG in ('Y', 'C') THEN
1144 arp_util.debug('jl_ar_receivable_applications.Reverse_br()+');
1145 END IF;
1146
1147 jl_zz_ar_library_1_pkg.get_interest_reversal_flag(p_cash_receipt_id,
1148 x_interest_reversal,
1149 x_error_code);
1150
1151 IF x_error_code = 0 then
1152
1153 IF x_interest_reversal THEN
1154 p_return_status := FND_API.G_RET_STS_SUCCESS;
1155 OPEN pay_sched;
1156 IF PG_DEBUG in ('Y', 'C') THEN
1157 arp_util.debug('Reverse_br: ' || 'Cursor pay_sched +');
1158 END IF;
1159 LOOP
1160
1161 FETCH pay_sched INTO ps_rec;
1162 EXIT when pay_sched%NOTFOUND
1163 or pay_sched%NOTFOUND is NULL;
1164
1165 IF ps_rec.calculated_interest IS NOT NULL THEN
1166
1167 jl_zz_ar_library_1_pkg.get_prev_interest_values(ps_rec.pay_sched_id,
1168 p_cash_receipt_id,
1169 ps_rec.apply_date,
1170 x_main_amnt_rec,
1171 x_base_int_calc,
1172 x_calculated_interest,
1173 x_received_interest,
1174 x_int_diff_action,
1175 x_int_writeoff_reason,
1176 x_payment_date,
1177 x_writeoff_date,
1178 x_error_code);
1179
1180 IF x_error_code = 0 then
1181 /* UPDATE ar_payment_schedules
1182 SET global_attribute1 = x_main_amnt_rec,
1183 global_attribute2 = x_base_int_calc,
1184 global_attribute3 = x_calculated_interest,
1185 global_attribute4 = x_received_interest,
1186 global_attribute5 = x_int_diff_action,
1187 global_attribute6 = x_int_writeoff_reason,
1188 global_attribute7 = x_payment_date,
1189 global_attribute15 = x_writeoff_date
1190 WHERE payment_schedule_id = ps_rec.pay_sched_id;
1191 */
1192
1193 /* Replace Update by AR's table handlers. Bug # 2249731 */
1194
1195 arp_ps_pkg.fetch_p(ps_rec.pay_sched_id, l_ps_rec);
1196 arp_ps_pkg.lock_p(ps_rec.pay_sched_id);
1197 l_ps_rec.global_attribute1 := x_main_amnt_rec;
1198 l_ps_rec.global_attribute2 := x_base_int_calc;
1199 l_ps_rec.global_attribute3 := x_calculated_interest;
1200 l_ps_rec.global_attribute4 := x_received_interest;
1201 l_ps_rec.global_attribute5 := x_int_diff_action;
1202 l_ps_rec.global_attribute6 := x_int_writeoff_reason;
1203 l_ps_rec.global_attribute7 := x_payment_date;
1204 l_ps_rec.global_attribute15 := x_writeoff_date;
1205 arp_ps_pkg.update_p(l_ps_rec, ps_rec.pay_sched_id);
1206
1207 ELSE
1208 p_return_status := FND_API.G_RET_STS_ERROR;
1209 END IF;
1210
1211 END IF;
1212
1213 END LOOP;
1214 IF PG_DEBUG in ('Y', 'C') THEN
1215 arp_util.debug('Reverse_br: ' || 'Cursor pay_sched -');
1216 END IF;
1217 CLOSE pay_sched;
1218 ELSE
1219 FND_MESSAGE.SET_NAME('JL','JL_BR_AR_STD_REV');
1220 FND_MSG_PUB.Add;
1221 p_return_status := FND_API.G_RET_STS_ERROR;
1222 END IF;
1223 ELSE
1224 p_return_status := FND_API.G_RET_STS_ERROR;
1225 END IF;
1226
1227 IF PG_DEBUG in ('Y', 'C') THEN
1228 arp_util.debug('jl_ar_receivable_applications.Reverse_br()-');
1229 END IF;
1230
1231 END Reverse_br;
1232
1233 /*----------------------------------------------------------------------------*
1234 | PUBLIC FUNCTIONS/PROCEDURES |
1235 *----------------------------------------------------------------------------*/
1236
1237 PROCEDURE Apply(p_apply_before_after IN VARCHAR2 ,
1238 p_global_attribute_category IN VARCHAR2 ,
1239 p_set_of_books_id IN NUMBER ,
1240 p_cash_receipt_id IN VARCHAR2 ,
1241 p_receipt_date IN DATE ,
1242 p_applied_payment_schedule_id IN NUMBER ,
1243 p_amount_applied IN NUMBER ,
1244 p_unapplied_amount IN NUMBER ,
1245 p_due_date IN DATE ,
1246 p_receipt_method_id IN NUMBER ,
1247 p_remittance_bank_account_id IN NUMBER ,
1248 p_global_attribute1 IN OUT NOCOPY VARCHAR2 ,
1249 p_global_attribute2 IN OUT NOCOPY VARCHAR2 ,
1250 p_global_attribute3 IN OUT NOCOPY VARCHAR2 ,
1251 p_global_attribute4 IN OUT NOCOPY VARCHAR2 ,
1252 p_global_attribute5 IN OUT NOCOPY VARCHAR2 ,
1253 p_global_attribute6 IN OUT NOCOPY VARCHAR2 ,
1254 p_global_attribute7 IN OUT NOCOPY VARCHAR2 ,
1255 p_global_attribute8 IN OUT NOCOPY VARCHAR2 ,
1256 p_global_attribute9 IN OUT NOCOPY VARCHAR2 ,
1257 p_global_attribute10 IN OUT NOCOPY VARCHAR2 ,
1258 p_global_attribute11 IN OUT NOCOPY VARCHAR2 ,
1259 p_global_attribute12 IN OUT NOCOPY VARCHAR2 ,
1260 p_global_attribute13 IN OUT NOCOPY VARCHAR2 ,
1261 p_global_attribute14 IN OUT NOCOPY VARCHAR2 ,
1262 p_global_attribute15 IN OUT NOCOPY VARCHAR2 ,
1263 p_global_attribute16 IN OUT NOCOPY VARCHAR2 ,
1264 p_global_attribute17 IN OUT NOCOPY VARCHAR2 ,
1265 p_global_attribute18 IN OUT NOCOPY VARCHAR2 ,
1266 p_global_attribute19 IN OUT NOCOPY VARCHAR2 ,
1267 p_global_attribute20 IN OUT NOCOPY VARCHAR2 ,
1268 p_return_status OUT NOCOPY VARCHAR2) IS
1269
1270
1271 l_country_code VARCHAR2(2);
1272 l_org_id NUMBER;
1273 x_gac_valid BOOLEAN;
1274
1275 BEGIN
1276
1277
1278 IF PG_DEBUG in ('Y', 'C') THEN
1279 arp_util.debug('jl_ar_receivable_applications.Apply()+');
1280 END IF;
1281
1282 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1283 l_org_id := MO_GLOBAL.get_current_org_id;
1284
1285 l_country_code := JG_ZZ_SHARED_PKG.get_country(l_org_id,null);
1286
1287 IF l_country_code IS NULL THEN
1288
1289 p_return_status := FND_API.G_RET_STS_ERROR;
1290
1291 ELSIF l_country_code = 'BR' THEN
1292
1293 jg_zz_global_flex_vald_pkg.validate_global_attb_cat(
1294 p_global_attribute_category,
1295 'JL',
1296 'BR',
1297 'ARXRWMAI',
1298 x_gac_valid);
1299
1300 IF x_gac_valid THEN
1301
1302 Apply_br(p_apply_before_after,
1303 p_global_attribute_category,
1304 p_set_of_books_id,
1305 p_cash_receipt_id,
1306 p_receipt_date,
1307 p_applied_payment_schedule_id,
1308 p_amount_applied,
1309 p_unapplied_amount,
1310 p_due_date,
1311 p_receipt_method_id,
1312 p_remittance_bank_account_id,
1313 p_global_attribute1,
1314 p_global_attribute2,
1315 p_global_attribute3,
1316 p_global_attribute4,
1317 p_global_attribute5,
1318 p_global_attribute6,
1319 p_global_attribute7,
1320 p_global_attribute8,
1321 p_global_attribute9,
1322 p_global_attribute10,
1323 p_global_attribute11,
1324 p_global_attribute12,
1325 p_global_attribute13,
1326 p_global_attribute14,
1327 p_global_attribute15,
1328 p_global_attribute16,
1329 p_global_attribute17,
1330 p_global_attribute18,
1331 p_global_attribute19,
1332 p_global_attribute20,
1333 p_return_status);
1334
1335 ELSE
1336
1337 FND_MESSAGE.SET_NAME('JG','JG_ZZ_INVALID_GLOBAL_ATTB_CAT');
1338 FND_MSG_PUB.Add;
1339 p_return_status := FND_API.G_RET_STS_ERROR;
1340
1341 END IF;
1342 END IF;
1343
1344 IF PG_DEBUG in ('Y', 'C') THEN
1345 arp_util.debug('jl_ar_receivable_applications.Apply()-');
1346 END IF;
1347
1348
1349 END Apply;
1350
1351 PROCEDURE Unapply(
1352 p_cash_receipt_id IN VARCHAR2 ,
1353 p_applied_payment_schedule_id IN NUMBER ,
1354 p_return_status OUT NOCOPY VARCHAR2) IS
1355
1356 l_country_code VARCHAR2(2);
1357 l_org_id NUMBER;
1358
1359 BEGIN
1360
1361 IF PG_DEBUG in ('Y', 'C') THEN
1362 arp_util.debug('jl_ar_receivable_applications.Unapply()+');
1363 END IF;
1364
1365 l_org_id := MO_GLOBAL.get_current_org_id;
1366
1367 l_country_code := JG_ZZ_SHARED_PKG.get_country(l_org_id,null);
1368
1369 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1370
1371 IF l_country_code IS NULL THEN
1372 p_return_status := FND_API.G_RET_STS_SUCCESS;
1373 ELSIF l_country_code = 'BR' THEN
1374 Unapply_br(
1375 p_cash_receipt_id,
1376 p_applied_payment_schedule_id,
1377 p_return_status);
1378 END IF;
1379
1380 IF PG_DEBUG in ('Y', 'C') THEN
1381 arp_util.debug('jl_ar_receivable_applications.Unapply()-');
1382 END IF;
1383
1384
1385 END Unapply;
1386
1387 PROCEDURE Reverse(
1388 p_cash_receipt_id IN NUMBER,
1389 p_return_status OUT NOCOPY VARCHAR2) IS
1390
1391 l_country_code VARCHAR2(2);
1392 l_org_id NUMBER;
1393
1394 BEGIN
1395
1396 IF PG_DEBUG in ('Y', 'C') THEN
1397 arp_util.debug('jl_ar_receivable_applications.Reverse()+');
1398 END IF;
1399
1400 l_org_id := MO_GLOBAL.get_current_org_id;
1401
1402 l_country_code := JG_ZZ_SHARED_PKG.get_country(l_org_id,null);
1403 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1404
1405 IF l_country_code IS NULL THEN
1406 p_return_status := FND_API.G_RET_STS_SUCCESS;
1407 ELSIF l_country_code = 'BR' THEN
1408 Reverse_br(
1409 p_cash_receipt_id,
1410 p_return_status);
1411 END IF;
1412
1413 IF PG_DEBUG in ('Y', 'C') THEN
1414 arp_util.debug('jl_ar_receivable_applications.Reverse()-');
1415 END IF;
1416
1417 END Reverse;
1418
1419 PROCEDURE create_interest_adjustment (
1420 p_post_quickcash_req_id IN NUMBER,
1421 x_return_status OUT NOCOPY VARCHAR2) IS
1422 l_country_code VARCHAR2(2);
1423 l_interest_instruction VARCHAR2(2);
1424 l_interest_occurrence VARCHAR2(2);
1425 l_city VARCHAR2(100);
1426 l_state VARCHAR2(15);
1427 l_calculated_interest NUMBER;
1428 l_interest_type VARCHAR2(15);
1429 l_interest_rate_amount NUMBER;
1430 l_penalty_type VARCHAR2(15);
1431 l_penalty_rate_amount NUMBER;
1432 l_period_days NUMBER;
1433 l_interest_formula VARCHAR2(100);
1434 l_grace_days NUMBER;
1435 l_due_date DATE;
1436 l_occurrence_date DATE;
1437 l_days_late NUMBER;
1438 x_exit_code VARCHAR2(2);
1439 l_inttrxid NUMBER;
1440 l_intccid NUMBER;
1441 l_trx_type_id NUMBER;
1442 l_batch_source_id NUMBER;
1443 l_int_writeoff_rectrx_id NUMBER;
1444 l_int_writeoff_ccid NUMBER;
1445 l_writeoff_tolerance NUMBER;
1446 l_writeoff_amount NUMBER;
1447 l_writeoff_date VARCHAR2(30);
1448 l_trade_note_amount NUMBER;
1449 l_abate_rev_rectrx_id NUMBER;
1450 l_int_rev_rectrx_id NUMBER;
1451 l_int_rev_ccid NUMBER;
1452 l_abate_rev_ccid NUMBER;
1453 l_cust_trx_id NUMBER;
1454 l_amount_due_remaining NUMBER;
1455 l_payment_amount NUMBER;
1456 l_trx_type_idm NUMBER;
1457 l_batch_source_idm NUMBER;
1458 l_receipt_method_idm NUMBER;
1459 x_return varchar2(1);
1460 l_error_code NUMBER;
1461 l_error_msg VARCHAR2(2000);
1462 l_error_token VARCHAR2(100);
1463 l_ps_rec ar_payment_schedules%ROWTYPE;
1464 l_payment_action VARCHAR2(100);
1465 l_calendar_name VARCHAR2(100);
1466 l_pay_sch_id NUMBER;
1467 l_cash_rec_id NUMBER;
1468 l_count NUMBER;
1469
1470 Cursor c_int(p_request_id IN NUMBER) is
1471 select ara.receivable_application_id,
1472 acr.cash_receipt_id,
1473 ara.applied_payment_schedule_id,
1474 acr.receipt_method_id,
1475 acr.receipt_date,
1476 acr.remit_bank_acct_use_id,
1477 aicl.global_attribute1,
1478 aicl.global_attribute2,
1479 aicl.global_attribute3,
1480 aicl.global_attribute4,
1481 aicl.global_attribute5,
1482 aicl.global_attribute6,
1483 aicl.global_attribute7,
1484 aicl.global_attribute8,
1485 aicl.global_attribute10
1486 from ar_receivable_applications_all ara,
1487 ar_cash_receipts_all acr,
1488 ar_interim_cash_rcpt_lines_all aicl
1489 --ar_interim_cash_receipts_all aic
1490 where ara.request_id = p_request_id
1491 AND ara.cash_receipt_id = acr.cash_receipt_id
1492 AND ara.cash_receipt_id = aicl.cash_receipt_id
1493 AND ara.applied_payment_schedule_id = aicl.payment_schedule_id
1494 AND ara.global_attribute_category is NULL;
1495
1496 cursor p(p_request_id IN NUMBER) is
1497 select ara.cash_receipt_id,
1498 ara.applied_payment_schedule_id
1499 from ar_receivable_applications_all ara
1500 where request_id = p_request_id;
1501
1502
1503 BEGIN
1504
1505 --IF PG_DEBUG in ('Y', 'C') THEN
1506 fnd_file.put_line(fnd_file.log,'Inside3 jl_ar_receivable_applications.Create_interest_adjustment()+');
1507 --END IF;
1508
1509 fnd_file.put_line(fnd_file.log,'Rec Appl Id :'||to_char(p_post_quickcash_req_id));
1510
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512 For rint in c_int(p_post_quickcash_req_id)
1513 Loop
1514
1515 fnd_file.put_line(fnd_file.log,'After opening the cursor()+');
1516
1517 update ar_receivable_applications_all
1518 set global_attribute1 = rint.global_attribute3 ,
1519 global_attribute2 = rint.global_attribute4 ,
1520 global_attribute3 = rint.global_attribute1 ,
1521 global_attribute4 = rint.global_attribute2 ,
1522 global_attribute7 = rint.global_attribute5 ,
1523 global_attribute9 = rint.global_attribute6 ,
1524 global_attribute10 = rint.global_attribute7,
1525 global_attribute11 = rint.global_attribute8,
1526 global_attribute12 = rint.global_attribute10,
1527 global_attribute_category = 'JL.BR.ARXRWMAI.Additional Info'
1528 where receivable_application_id = rint.receivable_application_id;
1529
1530 fnd_file.put_line(fnd_file.log,'After update statement()+');
1531
1532 /* ---------------------------------------------------------------------- */
1533 /* Interest Treatment */
1534 /* ---------------------------------------------------------------------- */
1535 fnd_file.put_line(fnd_file.log,'Before get_idm_profiles');
1536 fnd_file.put_line(fnd_file.log,'User Id '||to_char(fnd_global.user_id));
1537
1538 JL_ZZ_AR_LIBRARY_1_PKG.get_idm_profiles_from_syspa (
1539 l_trx_type_idm,
1540 l_batch_source_idm,
1541 l_receipt_method_idm,
1542 1,
1543 l_error_code);
1544
1545 SELECT ract.cust_trx_type_id,
1546 ract.batch_source_id,
1547 arps.due_date,
1548 nvl(arps.amount_due_remaining,0),
1549 ract.customer_trx_id,
1550 arps.amount_applied
1551 INTO
1552 l_trx_type_id,
1553 l_batch_source_id,
1554 l_due_date,
1555 l_amount_due_remaining,
1556 l_cust_trx_id,
1557 l_payment_amount
1558 FROM ra_customer_trx ract,
1559 ar_payment_schedules arps
1560 WHERE arps.payment_schedule_id = rint.applied_payment_schedule_id
1561 AND ract.customer_trx_id = arps.customer_trx_id;
1562
1563 fnd_file.put_line(fnd_file.log,'calc int'||rint.global_attribute1);
1564 fnd_file.put_line(fnd_file.log,'rec int'||rint.global_attribute2);
1565 fnd_file.put_line(fnd_file.log,'main amt'||rint.global_attribute3);
1566
1567 Interest_treatment (
1568 rint.applied_payment_schedule_id,
1569 l_cust_trx_id,
1570 l_payment_amount,
1571 l_due_date,
1572 nvl(rint.global_attribute1,0),
1573 nvl(rint.global_attribute2,0),
1574 nvl(rint.global_attribute3,0),
1575 rint.global_attribute4,
1576 rint.global_attribute5,
1577 NULL,
1578 rint.cash_receipt_id,
1579 rint.receipt_date,
1580 rint.receipt_method_id,
1581 l_trx_type_idm,
1582 l_batch_source_idm,
1583 l_receipt_method_idm,
1584 fnd_global.user_id,
1585 rint.remit_bank_acct_use_id,
1586 l_writeoff_date);
1587
1588 fnd_file.put_line(fnd_file.log,'After interest_treatment');
1589
1590 End Loop;
1591
1592 --IF PG_DEBUG in ('Y', 'C') THEN
1593 arp_util.debug('jl_ar_receivable_applications.Create_interest_adjustments()-');
1594 --END IF;
1595
1596 END Create_interest_adjustment;
1597
1598 PROCEDURE delete_interest_adjustment (
1599 p_cash_receipt_id IN NUMBER,
1600 x_return_status OUT NOCOPY VARCHAR2) IS
1601 BEGIN
1602 delete from ar_adjustments_all
1603 where associated_cash_receipt_id = p_cash_receipt_id;
1604 END delete_interest_adjustment;
1605
1606 END jl_ar_receivable_applications;