[Home] [Help]
PACKAGE BODY: APPS.ARP_PS_UTIL
Source
1 PACKAGE BODY ARP_PS_UTIL AS
2 /* $Header: ARCUPSB.pls 120.30.12020000.2 2012/07/28 00:45:15 dgaurab ship $*/
3 --
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE val_update_rev_actions( p_ps_id IN NUMBER );
7 --
8 PROCEDURE validate_args_upd_rel_cols(
9 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
10 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
11 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
12 p_gl_date IN ar_payment_schedules.gl_date%TYPE );
13 --
14 PROCEDURE validate_args_upd_inv_rel_cols(
15 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
16 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
17 p_earned_discount IN
18 ar_payment_schedules.discount_taken_earned%TYPE,
19 p_unearned_discount IN
20 ar_payment_schedules.discount_taken_unearned%TYPE,
21 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
22 p_gl_date IN ar_payment_schedules.gl_date%TYPE );
23 --
24 PROCEDURE validate_args_upd_adj_rel_cols(
25 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
26 p_line_adjusted IN
27 ar_receivable_applications.line_applied%TYPE,
28 p_tax_adjusted IN
29 ar_receivable_applications.tax_applied%TYPE,
30 p_freight_adjusted IN
31 ar_receivable_applications.freight_applied%TYPE,
32 p_charges_adjusted IN
33 ar_receivable_applications.receivables_charges_applied%TYPE,
34 p_amount_adjusted_pending IN
35 ar_payment_schedules.amount_adjusted_pending%TYPE,
36 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
37 p_gl_date IN ar_payment_schedules.gl_date%TYPE );
38 --
39 PROCEDURE validate_args_upd_adj_rel_cols(
40 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
41 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
42 p_gl_date IN ar_payment_schedules.gl_date%TYPE );
43
44 -- Bug fix 653643 commented out NOCOPY as this procedure is being declared as PUBLIC
45 --PROCEDURE populate_closed_dates(
46 -- p_gl_date IN ar_payment_schedules.gl_date%TYPE,
47 -- p_apply_date IN ar_payment_schedules.gl_date%TYPE,
48 -- p_app_type IN VARCHAR2,
49 -- p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE );
50
51 /*===========================================================================+
52 | PROCEDURE |
53 | update_reverse_actions |
54 | |
55 | DESCRIPTION |
56 | This procedure performs all actions to modify the passed in |
57 | user defined application record and prepares to update payment schedule|
58 | table |
59 | |
60 | SCOPE - PUBLIC |
61 | |
62 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
63 | arp_ps_pkg.update_p - Update a row in AR_PAYMENT_SCHEDULES table |
64 | arp_ps_pkg.fetch_p - fetch a row in AR_PAYMENT_SCHEDULES table |
65 | |
66 | ARGUMENTS : IN: |
67 | p_app_rec - Application Record structure |
68 | p_module_name _ Name of module that called this procedure|
69 | p_module_version - Version of module that called this |
70 | procedure |
71 | OUT: |
72 | |
73 | RETURNS : NONE |
74 | |
75 | NOTES |
76 | |
77 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
78 | 8/2/1996 Harri Kaukovuo Bug fix 387827, chargebacks did not get |
79 | closed when receipt was reversed. |
80 | 03/07/1997 Karen Lawrance Bug fix #446386. Discounts taken where not |
81 | included in reversing calculations, should |
82 | affect line total. |
83 | 05/15/1997 Karen Lawrance Bug fix #464203. Added to if constructs |
84 | using trx_type AR_APP and AR_CM. |
85 | Refer to comments in code. |
86 +===========================================================================*/
87 PROCEDURE update_reverse_actions(
88 p_app_rec IN arp_global.app_rec_type,
89 p_module_name IN VARCHAR2,
90 p_module_version IN VARCHAR2) IS
91
92 l_ps_rec ar_payment_schedules%ROWTYPE;
93
94 BEGIN
95 IF PG_DEBUG in ('Y', 'C') THEN
96 arp_debug.debug( 'arp_ps_util.update_reverse_actions()+' );
97 arp_debug.debug( ' p_app_rec.trx_type = ' || p_app_rec.trx_type );
98 END IF;
99
100 --
101 -- Update payment schedule table
102 --
103 arp_ps_pkg.fetch_p( p_app_rec.ps_id, l_ps_rec );
104
105 l_ps_rec.amount_due_remaining := NVL( l_ps_rec.amount_due_remaining, 0) -
106 NVL( p_app_rec.amount_applied, 0 ) -
107 NVL( p_app_rec.earned_discount_taken, 0 ) -
108 NVL( p_app_rec.unearned_discount_taken, 0 );
109
110 l_ps_rec.acctd_amount_due_remaining :=
111 NVL( l_ps_rec.acctd_amount_due_remaining, 0 ) -
112 NVL( p_app_rec.acctd_amount_applied, 0 ) -
113 NVL( p_app_rec.acctd_earned_discount_taken, 0 ) -
114 NVL( p_app_rec.acctd_unearned_discount_taken, 0 );
115 --
116 -- Update status
117 --
118
119 IF ( NVL( l_ps_rec.amount_due_remaining,0) = 0)
120 THEN
121 l_ps_rec.status := 'CL';
122 l_ps_rec.gl_date_closed := NVL( p_app_rec.gl_date_closed,
123 l_ps_rec.gl_date_closed );
124 l_ps_rec.actual_date_closed := NVL( p_app_rec.actual_date_closed,
125 l_ps_rec.actual_date_closed );
126 ELSE
127 l_ps_rec.status := 'OP';
128 l_ps_rec.gl_date_closed := ARP_GLOBAL.G_MAX_DATE;
129 l_ps_rec.actual_date_closed := ARP_GLOBAL.G_MAX_DATE;
130 END IF;
131
132 /* Bug fix #464203
133 Two values may be passed into this procedure for trx_type
134 AR_APP ... Standard application reversal
135 AR_CM ... Credit Memo application reversal
136
137 This procedure is called to update the applied transaction as
138 well as the Receipt or Credit Memo in payment schedules.
139
140 When we are updating the Receipt, Credit Memo, or the Transaction
141 for the Receipt Application, we want to reset to Amount Applied.
142
143 When we are updating the Transaction for the Credit Memo
144 Application, we want to reset the Amount Credited.
145 */
146 --
147 -- Update amount_applied
148 --
149 IF ( p_app_rec.trx_type = 'AR_APP' ) OR
150 ( p_app_rec.trx_type IN ('AR_CM','AR_CM_REF') and
151 l_ps_rec.class = 'CM' ) THEN
152 l_ps_rec.amount_applied := NVL( l_ps_rec.amount_applied, 0) +
153 NVL( p_app_rec.amount_applied, 0 );
154 l_ps_rec.selected_for_receipt_batch_id := NULL;
155 END IF;
156 --
157 -- Update amount_credited
158 --
159 IF ( p_app_rec.trx_type = 'AR_CM' and
160 l_ps_rec.class <> 'CM' ) OR
161 ( p_app_rec.trx_type = 'AR_CM_REF' ) THEN
162 l_ps_rec.amount_credited := NVL( l_ps_rec.amount_credited, 0) -
163 NVL( p_app_rec.amount_applied, 0 );
164 END IF;
165 --
166 -- Update amount_adjusted
167 --
168 IF ( p_app_rec.trx_type = 'AR_ADJ' OR
169 p_app_rec.trx_type = 'AR_CHG' ) THEN
170 l_ps_rec.amount_adjusted := NVL( l_ps_rec.amount_adjusted, 0 ) -
171 NVL( p_app_rec.amount_applied, 0 );
172 END IF;
173
174 l_ps_rec.amount_adjusted_pending := NVL( p_app_rec.amount_adjusted_pending,
175 l_ps_rec.amount_adjusted_pending );
176
177 -- ------------------------------------------------------------------
178 -- Update receivables_charges_remaining and freight_remaining and
179 -- tax_remaining and discount_taken_earned and discount_remaining and
180 -- discount_taken_unearned and receivables_charges_charged and
181 -- amount_line_items_remaining
182 -- ------------------------------------------------------------------
183
184 IF ( l_ps_rec.class = 'PMT' ) THEN
185 l_ps_rec.receivables_charges_charged := NULL;
186 l_ps_rec.receivables_charges_remaining := NULL;
187 l_ps_rec.freight_remaining := NULL;
188 l_ps_rec.tax_remaining := NULL;
189 l_ps_rec.discount_remaining := NULL;
190 l_ps_rec.discount_taken_earned := NULL;
191 l_ps_rec.discount_taken_unearned := NULL;
192 l_ps_rec.amount_line_items_remaining := NULL;
193 ELSE
194 /* Bug fix #446386
195 Add any discount taken back to the line amount.
196 In rel11 we now will know the respective discounted
197 parts so we will add them accordingly */
198
199 l_ps_rec.amount_line_items_remaining :=
200 NVL( l_ps_rec.amount_line_items_remaining, 0 ) -
201 NVL( p_app_rec.line_applied, 0) -
202 NVL( p_app_rec.line_ediscounted, 0 ) -
203 NVL( p_app_rec.line_uediscounted, 0 );
204 --
205 IF ( p_app_rec.trx_type = 'AR_ADJ' ) THEN
206 l_ps_rec.receivables_charges_charged :=
207 NVL( l_ps_rec.receivables_charges_charged, 0 ) -
208 NVL( p_app_rec.charges_type_adjusted, 0);
209 END IF;
210 IF ( p_app_rec.trx_type = 'AR_CHG' ) THEN
211 l_ps_rec.receivables_charges_charged :=
212 NVL( l_ps_rec.receivables_charges_charged, 0 ) -
213 NVL( p_app_rec.amount_applied, 0 );
214 END IF;
215 --
216 l_ps_rec.receivables_charges_remaining :=
217 NVL( l_ps_rec.receivables_charges_remaining, 0) -
218 NVL( p_app_rec.receivables_charges_applied, 0) -
219 NVL( p_app_rec.charges_ediscounted, 0 ) -
220 NVL( p_app_rec.charges_uediscounted, 0 );
221 --
222 --
223 --
224 l_ps_rec.freight_remaining :=
225 NVL( l_ps_rec.freight_remaining, 0 ) -
226 NVL( p_app_rec.freight_applied, 0 ) -
227 NVL( p_app_rec.freight_ediscounted, 0 ) -
228 NVL( p_app_rec.freight_uediscounted, 0 );
229 --
230 l_ps_rec.tax_remaining :=
231 NVL( l_ps_rec.tax_remaining, 0 ) -
232 NVL( p_app_rec.tax_applied, 0 ) -
233 NVL( p_app_rec.tax_ediscounted, 0 ) -
234 NVL( p_app_rec.tax_uediscounted, 0 );
235 --
236 --
237 l_ps_rec.discount_remaining := NVL( l_ps_rec.discount_remaining, 0 );
238 --
239 l_ps_rec.discount_taken_earned :=
240 NVL( l_ps_rec.discount_taken_earned, 0) +
241 NVL( p_app_rec.earned_discount_taken, 0 );
242 --
243 l_ps_rec.discount_taken_unearned :=
244 NVL( l_ps_rec.discount_taken_unearned, 0 ) +
245 NVL( p_app_rec.unearned_discount_taken, 0 );
246 END IF;
247
248 arp_ps_pkg.update_p( l_ps_rec );
249
250 IF PG_DEBUG in ('Y', 'C') THEN
251 arp_debug.debug( 'arp_ps_util.update_reverse_actions()-' );
252 END IF;
253 EXCEPTION
254 WHEN OTHERS THEN
255 IF PG_DEBUG in ('Y', 'C') THEN
256 arp_debug.debug(
257 'EXCEPTION: arp_ps_util.update_reverse_actions' );
258 END IF;
259 RAISE;
260 END update_reverse_actions;
261 --
262 /*===========================================================================+
263 | PROCEDURE |
264 | val_update_rev_actions |
265 | |
266 | DESCRIPTION |
267 | This procedure validated arguments passed to update_reverse_actions |
268 | |
269 | SCOPE - PRIVATE |
270 | |
271 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
272 | |
273 | ARGUMENTS : IN: |
274 | p_ps_id - Payment Schedule Id from user define appln |
275 | record |
276 | OUT: |
277 | |
278 | RETURNS : NONE |
279 | |
280 | NOTES |
281 | |
282 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
283 | |
284 +===========================================================================*/
285 PROCEDURE val_update_rev_actions( p_ps_id IN NUMBER ) IS
286 BEGIN
287 IF PG_DEBUG in ('Y', 'C') THEN
288 arp_debug.debug( 'arp_ps_util.val_update_rev_actions()+' );
289 END IF;
290 IF ( p_ps_id IS NULL ) THEN
291 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
292 APP_EXCEPTION.raise_exception;
293 END IF;
294 --
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_debug.debug( 'arp_ps_util.val_update_rev_actions()-' );
297 END IF;
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_debug.debug(
302 'EXCEPTION: arp_ps_util.val_update_rev_actions' );
303 END IF;
304 RAISE;
305 END val_update_rev_actions;
306 --
307 /*===========================================================================+
308 | PROCEDURE |
309 | update_receipt_related_columns |
310 | |
311 | DESCRIPTION |
312 | This procedure updates the receipt related rows of a payment schedule|
313 | The passed in PS ID is assumed to belong to a receipt. The procedure |
314 | sets the gl_date and gl_date_closed and amount applied. The procedure|
315 | should be called whenever a receipt is applied to an invoice. |
316 | The procedure also return the acctd_amount_applied to populate the |
317 | acctd_amount_applied_from column during AR_RA row insertion |
318 | |
319 | SCOPE - PUBLIC |
320 | |
321 | PARAMETERS : |
322 | IN : p_payment_schedule_id - payment_schedule_id of payment |
323 | schedule |
324 | p_gldate - GL date of the receipt |
325 | p_apply_date - Apply Date of the receipt |
326 | p_amount_applied - Amount of the receipt applied to the |
327 | invoice. |
328 | p_module_name - Name of module that called this routine |
329 | p_module_version - Version of module that called this routine|
330 | p_maturity_date - PS.due_date for receipt.
331 | OUT NOCOPY : p_acctd_amount_applied_out - Accounted amount applied used to|
332 | populate acctd_amount_applied_from in AR_RA table |
333 | |
334 | EXTERNAL PROCEDURES/FUNCTION |
335 | arp_util.calc_acctd_amount and arp_debug.debug |
336 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
337 | |
338 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
339 | |
340 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
341 | |
342 +===========================================================================*/
343 PROCEDURE update_receipt_related_columns(
344 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
345 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
346 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
347 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
348 p_acctd_amount_applied OUT NOCOPY
349 ar_receivable_applications.acctd_amount_applied_from%TYPE,
350 p_ps_rec IN ar_payment_schedules%ROWTYPE,
351 p_maturity_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
352 p_applied_ps_class IN ar_payment_schedules.class%TYPE DEFAULT NULL ) IS -- Bug 6924942
353 --
354 l_ps_rec ar_payment_schedules%ROWTYPE;
355 --
356 -- Variable to populate OUT NOCOPY arguments
357 --
358 l_acctd_amount_applied
359 ar_receivable_applications.acctd_amount_applied_from%TYPE;
360
361 --Introduced For Bug # 2711860
362 -- ORASHID
363 --
364 l_nocopy_amt_due_remain
365 ar_payment_schedules.amount_due_remaining%TYPE;
366 l_nocopy_acctd_amt_due_remain
367 ar_payment_schedules.acctd_amount_due_remaining%TYPE;
368
369 -- Bug 6924942 - Start
370 l_rows_accumulate_count number;
371 current_amt_app number;
372 Current_acctd_amt_app number;
373 Base_amt_due_rem number;
374 Base_acctd_amt_due_rem number;
375 l_new_adr number;
376 l_new_aadr number;
377 l_new_aapf number;
378 l_amt_app_from number;
379 l_acctd_amt_app_from number;
380 -- Bug 6924942 - End
381
382 BEGIN
383 IF PG_DEBUG IN ('Y','C')
384 THEN
385 arp_debug.debug( 'arp_ps_util.update_receipt_related_columns()+' );
386 arp_debug.debug( 'PS ID : '||p_ps_id );
387 arp_debug.debug( 'Amount Applied : '||p_amount_applied );
388 arp_debug.debug( 'Apply Date : '||TO_CHAR( p_apply_date ) );
389 arp_debug.debug( 'GL Date : '||TO_CHAR( p_gl_date ) );
390 arp_debug.debug( 'Maturity Date : '||TO_CHAR( p_maturity_date));
391 END IF;
392
393 IF ( p_ps_rec.payment_schedule_id is NOT NULL ) THEN
394 l_ps_rec := p_ps_rec;
395 validate_args_upd_rel_cols( l_ps_rec.payment_schedule_id,
396 p_amount_applied,
397 p_apply_date, p_gl_date );
398 ELSE
399 validate_args_upd_rel_cols( p_ps_id, p_amount_applied,
400 p_apply_date, p_gl_date );
401 arp_ps_pkg.fetch_p( p_ps_id, l_ps_rec );
402 END IF;
403 --
404 -- Call acctd. amount package, to calculate new ADR, acctd_ADR and
405 -- acctd. amount applied. The acctd. amount applied directly updates OUT NOCOPY
406 -- variable p_acctd_amount_applied.
407 -- Note: Receipt amount passed in negative of receipt amount
408 --
409
410 -- Modified For Bug # 2711860
411 -- ORASHID
412 --
413 /*Bug 6924942 get all the unapp rows with corresponding APP pair*/
414 select count(*) into l_rows_accumulate_count
415 from ar_receivable_applications ra,ar_payment_schedules ps
416 where ra.cash_receipt_id=l_ps_rec.cash_receipt_id
417 and ra.status='UNAPP'
418 and nvl(ra.include_in_accumulation,'Y')='Y'
419 and ra.payment_schedule_id=p_ps_id
420 and ra.payment_schedule_id = ps.payment_schedule_id
421 and ps.class='PMT' and ra.application_rule <> '60.2';
422
423 /* -- 6924942
424 In case of debit item application of invoice changed the way to calculate amount applied.
425 1. Get the amount due remaining for all application done after bumping receipt amount by
426 CM or PMT or direct updation of receipt amount.
427 2. Get total application accounted amount after bumping receipt amount.
428 3. Get total accounted amount including this application
429 4. Take differenct of amounts in 2 and 3 to get accounted amount for current application
430 */
431 IF p_applied_ps_class IN ('INV') THEN
432 IF l_rows_accumulate_count > 0 then
433 /*5473882 as in point 2 above*/
434 select nvl(sum(ra.amount_applied),0),nvl(sum(ra.acctd_amount_applied_from),0) into
435 current_amt_app,Current_acctd_amt_app
436 from ar_receivable_applications ra,ar_payment_schedules ps
437 where ra.cash_receipt_id=l_ps_rec.cash_receipt_id
438 and ra.status='UNAPP'
439 and nvl(ra.include_in_accumulation,'Y')='Y'
440 and ra.payment_schedule_id=p_ps_id
441 and ra.payment_schedule_id = ps.payment_schedule_id
442 and ps.class='PMT' and ra.application_rule <> '60.2';
443
444 arp_util.debug( 'arp_ps_util.update_receipt_related_columns : Prev applications exists.' );
445 --6924942 :- As in point 1 above
446
447 Base_amt_due_rem := l_ps_rec.amount_due_remaining + current_amt_app;
448 Base_acctd_amt_due_rem := l_ps_rec.acctd_amount_due_remaining + current_acctd_amt_app;
449
450 arp_util.debug( 'Base ADR : '||base_amt_due_rem );
451 arp_util.debug( 'Base AADR : '||Base_acctd_amt_due_rem );
452
453 arp_util.debug( 'Current amt app : '||current_amt_app );
454 arp_util.debug( 'Current Acct amt app : '||current_acctd_amt_app);
455
456 --6924942 as in point 3 above
457 arp_util.calc_acctd_amount(NULL,NULL,NULL,
458 l_ps_rec.exchange_rate,
459 '-', /** ADR must be reduced by amount_applied */
460 Base_amt_due_rem, /* Current ADR */
461 Base_acctd_amt_due_rem, /* Current Acctd. ADR */
462 (Current_amt_app-p_amount_applied), /* Receipt Amount */
463 l_new_adr,l_new_aadr,l_new_aapf);
464
465 arp_util.debug( 'New adr : '||l_new_adr );
466 arp_util.debug( 'New aadr : '||l_new_aadr );
467 arp_util.debug( 'New aapf : '||l_new_aapf );
468
469 --6924942 as in point 4 above
470 l_ps_rec.amount_due_remaining := l_new_adr;
471 l_ps_rec.acctd_amount_due_remaining := l_new_aadr;
472 l_acctd_amount_applied := l_new_aapf-current_acctd_amt_app;
473 else
474 --6924942 case of first application
475 l_amt_app_from := -p_amount_applied;
476 l_acctd_amt_app_from := arp_util.functional_amount(l_amt_app_from,
477 arpcurr.FunctionalCurrency,
478 l_ps_rec.exchange_rate,
479 NULL,
480 NULL);
481 arp_util.debug( 'AAPF : '||l_amt_app_from );
482 arp_util.debug( 'ACCTD_AAPF : '||l_acctd_amt_app_from );
483
484 arp_util.debug( 'Amt App : '||p_amount_applied );
485 l_ps_rec.amount_due_remaining := l_ps_rec.amount_due_remaining - l_amt_app_from;
486 l_ps_rec.acctd_amount_due_remaining := l_ps_rec.acctd_amount_due_remaining - l_acctd_amt_app_from;
487 l_acctd_amount_applied := l_acctd_amt_app_from;
488
489
490 end if;
491 ELSE
492
493 --6924942 same way calculation apart from Invoice
494 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
495 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
496
497 arp_util.calc_acctd_amount( NULL, NULL, NULL,
498 l_ps_rec.exchange_rate,
499 '-', /** ADR must be reduced by amount_applied */
500 l_nocopy_amt_due_remain, /* Current ADR */
501 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
502 -p_amount_applied, /* Receipt Amount */
503 l_ps_rec.amount_due_remaining, /* New ADR */
504 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
505 l_acctd_amount_applied ); /* Acct. amount_applied */
506 END IF; -- Bug 6924942
507 --
508 -- Update OUT NOCOPY variables
509 --
510 p_acctd_amount_applied := l_acctd_amount_applied;
511 --
512 -- Set the closed dates and status of PS record
513 --
514 arp_ps_util.populate_closed_dates( p_gl_date, p_apply_date, 'PMT', l_ps_rec );
515 --
516 -- Update other PS columns
517 -- Note: Amount applied added is negative of receipt amount
518 -- In reality p_amount_applied will never be NULL at this stage
519 --
520 l_ps_rec.amount_applied := NVL( l_ps_rec.amount_applied, 0) -
521 NVL( p_amount_applied, 0 );
522 l_ps_rec.selected_for_receipt_batch_id := NULL;
523 --
524 -- Update PS record
525 -- Do not CALL the table handler as this will cause the mrc
526 -- data to be updated. The mrc data is needed for processing
527 -- Receivable applications information. Instead we will do
528 -- a direct update of changed columns.
529 -- arp_ps_pkg.update_p( l_ps_rec );
530 --
531 /* Bug fix 3583503
532 gl_date and receipt_confirmed_flag also needs to be updated */
533 /* Bug fix 3721519
534 The WHO columns should be updated as in arp_ps_pkg.update_p */
535
536 /* Bug 5569488, do not update payment schedule if the receipt requires
537 confirmation. Added the If condition */
538 IF NVL(l_ps_rec.receipt_confirmed_flag,'Y') <> 'N' THEN
539 UPDATE ar_payment_schedules
540 set acctd_amount_due_remaining = l_ps_rec.acctd_amount_due_remaining,
541 amount_due_remaining = l_ps_rec.amount_due_remaining,
542 amount_applied = l_ps_rec.amount_applied,
543 selected_for_receipt_batch_id = l_ps_rec.selected_for_receipt_batch_id,
544 status = l_ps_rec.status,
545 reserved_type = l_ps_rec.reserved_type,
546 reserved_value = l_ps_rec.reserved_value,
547 gl_date_closed = l_ps_rec.gl_date_closed,
548 actual_date_closed = l_ps_rec.actual_date_closed ,
549 gl_date = l_ps_rec.gl_date,
550 receipt_confirmed_flag = l_ps_rec.receipt_confirmed_flag,
551 last_updated_by = arp_global.last_updated_by,
552 last_update_date = SYSDATE,
553 last_update_login = NVL(arp_standard.profile.last_update_login,l_ps_rec.last_update_login ),
554 request_id = NVL(arp_standard.profile.request_id,l_ps_rec.request_id),
555 program_application_id = NVL(arp_standard.profile.program_application_id,
556 l_ps_rec.program_application_id ),
557 program_id = NVL(arp_standard.profile.program_id, l_ps_rec.program_id),
558 program_update_date = DECODE(arp_standard.profile.program_id,
559 NULL, l_ps_rec.program_update_date,SYSDATE),
560 due_date = NVL(NVL(p_maturity_date, l_ps_rec.due_date),due_date)
561 where
562 payment_schedule_id = l_ps_rec.payment_schedule_id;
563 END IF ;
564
565 IF PG_DEBUG in ('Y','C')
566 THEN
567 arp_debug.debug( 'arp_ps_util.update_receipt_related_columns()-' );
568 END IF;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 arp_debug.debug(
573 'EXCEPTION: arp_ps_util.update_receipt_related_columns' );
574 RAISE;
575 END;
576 --
577 /*===========================================================================+
578 | PROCEDURE |
579 | validate_args_upd_rel_cols |
580 | |
581 | DESCRIPTION |
582 | Validate arguments passed to update_receipt_related_cols, |
583 | update_cm_related_cols and update_adj_related_cols procedure |
584 | |
585 | SCOPE - PUBLIC |
586 | |
587 | PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
588 | schedule |
589 | p_amount_applied - Inout applied amount |
590 | p_gldate - GL date of the receipt |
591 | p_apply_date - Apply Date of the receipt |
592 | |
593 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
594 | |
595 | NOTES - |
596 | |
597 +===========================================================================*/
598 PROCEDURE validate_args_upd_rel_cols(
599 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
600 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
601 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
602 p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
603 BEGIN
604 IF PG_DEBUG in ('Y', 'C') THEN
605 arp_debug.debug( 'arp_ps_util.validate_args_upd_rel_cols()+' );
606 END IF;
607 --
608 IF ( p_amount_applied IS NULL OR p_ps_id IS NULL OR
609 p_apply_date IS NULL OR p_gl_date IS NULL ) THEN
610 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
611 APP_EXCEPTION.raise_exception;
612 END IF;
613 --
614 IF PG_DEBUG in ('Y', 'C') THEN
615 arp_debug.debug( 'arp_ps_util.validate_args_upd_rel_cols()-' );
616 END IF;
617 EXCEPTION
618 WHEN OTHERS THEN
619 IF PG_DEBUG in ('Y', 'C') THEN
620 arp_debug.debug('validate_args_upd_rel_cols: ' ||
621 'EXCEPTION: arp_ps_util.validate_args_upd_rel_cols' );
622 END IF;
623 RAISE;
624 END validate_args_upd_rel_cols;
625
626 /*===========================================================================+
627 | PROCEDURE |
628 | update_invoice_related_columns |
629 | |
630 | DESCRIPTION |
631 | This procedure updates the invoice related rows of a payment schedule|
632 | The passed in PS ID is assumed to belong to a invoice. The procedure |
633 | sets the gl_date and gl_date_closed and amount(s) applied. The |
634 | procedure should be called whenever a receipt is applied to an |
635 | invoice. The procedure also returns the acctd_amount_applied, |
636 | acctd_earned_discount_taken, acctd_unearned_discount_taken columns, |
637 | line_applied, tax_applied, freight_applied, charges_applied columns |
638 | to populate the RA columns during AR_RA row insertion |
639 | insertion |
640 | |
641 | SCOPE - PUBLIC |
642 | |
643 | PARAMETERS : |
644 | IN : p_app_type - Indicates the type of application |
645 | Valid values are CASH for receipt |
646 | application and CM fro credit memo appln. |
647 | p_payment_schedule_id - payment_schedule_id of payment |
648 | schedule |
649 | p_gldate - GL date of the receipt |
650 | p_apply_date - Apply Date of the receipt |
651 | p_amount_applied - Amount of the receipt applied to the |
652 | invoice |
653 | p_discount_taken_earned - Earned discount taken(NULL if CM |
654 | appln |
655 | p_discount_taken_unearned - Unearned discount taken(NULL if CM |
656 | appln |
657 | p_ps_rec - Payment Schedule record, If this field|
658 | is not null, the PS record is not |
659 | fetched using p_ps_id. This PS record |
660 | is used |
661 | OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
662 | populate acctd_amount_applied_from in|
663 | AR_RA table |
664 | p_acctd_discount_taken_earned - Accounted discount taken earned to|
665 | populate acctd_discount_taken_earned |
666 | AR_RA table. This field is not |
667 | populated if application is of type |
668 | CM. It is NULL is app. type is CM. |
669 | p_acctd_disc_taken_unearned - Accounted discount taken unearned to|
670 | populate acctd_discount_taken_uneard |
671 | AR_RA table. This field is not |
672 | populated if application is of type |
673 | CM. It is NULL is app. type is CM. |
674 | p_tax_applied - Part of the applied amount applied to|
675 | tax |
676 | p_freight_applied - Part of the applied amount applied to|
677 | freight |
678 | p_line_applied - Part of the applied amount applied to|
679 | lines |
680 | p_charges_applied - Part of the applied amount applied to|
681 | receivable charges |
682 | |
683 | EXTERNAL PROCEDURES/FUNCTION |
684 | arp_util.calc_acctd_amount and arp_debug.debug |
685 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
686 | |
687 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
688 | |
689 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
690 | 06/23/1998 Guat Eng Tan Bug fix #658876. Added IF conditions before |
691 | the three calls to calc_acctd_amount. |
692 | |
693 | 08/29/2005 M Raymond 4566510 - Modified for etax. Made discount
694 | parameters IN/OUT and modified calls
695 | to calc_amount_and_remaining so that they
696 | would only happen if discounts were not
697 | already prorated.
698 | 12/18/2006 M Raymond 5677984 - Update l_line_remaining and
699 | l_tax_remaining after skipping
700 | calc_applied_and_remaining
701 +===========================================================================*/
702 PROCEDURE update_invoice_related_columns(
703 p_app_type IN VARCHAR2,
704 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
705 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
706 p_discount_taken_earned IN ar_payment_schedules.discount_taken_earned%TYPE,
707 p_discount_taken_unearned IN ar_payment_schedules.discount_taken_unearned%TYPE,
708 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
709 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
710 p_acctd_amount_applied OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
711 p_acctd_earned_discount_taken OUT NOCOPY ar_receivable_applications.earned_discount_taken%TYPE,
712 p_acctd_unearned_disc_taken OUT NOCOPY ar_receivable_applications.acctd_unearned_discount_taken%TYPE,
713 p_line_applied OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
714 p_tax_applied OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
715 p_freight_applied OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
716 p_charges_applied OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
717 p_line_ediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
718 p_tax_ediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
719 p_freight_ediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
720 p_charges_ediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
721 p_line_uediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
722 p_tax_uediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
723 p_freight_uediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
724 p_charges_uediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
725 p_rule_set_id OUT NOCOPY number,
726 p_ps_rec IN ar_payment_schedules%ROWTYPE) IS
727
728 l_ra_app_id NUMBER;
729 l_gt_id NUMBER;
730
731 BEGIN
732
733 /* original version, just calls other version with additional parameters
734 Note that l_ra_app_id and l_gt_id are both junk variables
735 and any return values will be ignored. */
736
737 update_invoice_related_columns(
738 p_app_type,
739 p_ps_id,
740 p_amount_applied,
741 p_discount_taken_earned,
742 p_discount_taken_unearned,
743 p_apply_date,
744 p_gl_date,
745 p_acctd_amount_applied,
746 p_acctd_earned_discount_taken,
747 p_acctd_unearned_disc_taken,
748 p_line_applied,
749 p_tax_applied,
750 p_freight_applied,
751 p_charges_applied,
752 p_line_ediscounted,
753 p_tax_ediscounted,
754 p_freight_ediscounted,
755 p_charges_ediscounted,
756 p_line_uediscounted,
757 p_tax_uediscounted,
758 p_freight_uediscounted,
759 p_charges_uediscounted,
760 p_rule_set_id,
761 p_ps_rec,
762 to_number(NULL),
763 l_ra_app_id,
764 l_gt_id);
765
766 END update_invoice_related_columns;
767 --
768 /*===========================================================================+
769 | PROCEDURE |
770 | update_invoice_related_columns |
771 | |
772 | DESCRIPTION |
773 | This procedure updates the invoice related rows of a payment schedule|
774 | The passed in PS ID is assumed to belong to a invoice. The procedure |
775 | sets the gl_date and gl_date_closed and amount(s) applied. The |
776 | procedure should be called whenever a receipt is applied to an |
777 | invoice. The procedure also returns the acctd_amount_applied, |
778 | acctd_earned_discount_taken, acctd_unearned_discount_taken columns, |
779 | line_applied, tax_applied, freight_applied, charges_applied columns |
780 | to populate the RA columns during AR_RA row insertion |
781 | insertion |
782 | |
783 | SCOPE - PUBLIC |
784 | |
785 | PARAMETERS : |
786 | IN : p_app_type - Indicates the type of application |
787 | Valid values are CASH for receipt |
788 | application and CM fro credit memo appln. |
789 | p_payment_schedule_id - payment_schedule_id of payment |
790 | schedule |
791 | p_gldate - GL date of the receipt |
792 | p_apply_date - Apply Date of the receipt |
793 | p_amount_applied - Amount of the receipt applied to the |
794 | invoice |
795 | p_discount_taken_earned - Earned discount taken(NULL if CM |
796 | appln |
797 | p_discount_taken_unearned - Unearned discount taken(NULL if CM |
798 | appln |
799 | p_ps_rec - Payment Schedule record, If this field|
800 | is not null, the PS record is not |
801 | fetched using p_ps_id. This PS record |
802 | is used |
803 | OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
804 | populate acctd_amount_applied_from in|
805 | AR_RA table |
806 | p_acctd_discount_taken_earned - Accounted discount taken earned to|
807 | populate acctd_discount_taken_earned |
808 | AR_RA table. This field is not |
809 | populated if application is of type |
810 | CM. It is NULL is app. type is CM. |
811 | p_acctd_disc_taken_unearned - Accounted discount taken unearned to|
812 | populate acctd_discount_taken_uneard |
813 | AR_RA table. This field is not |
814 | populated if application is of type |
815 | CM. It is NULL is app. type is CM. |
816 | p_tax_applied - Part of the applied amount applied to|
817 | tax |
818 | p_freight_applied - Part of the applied amount applied to|
819 | freight |
820 | p_line_applied - Part of the applied amount applied to|
821 | lines |
822 | p_charges_applied - Part of the applied amount applied to|
823 | receivable charges |
824 | |
825 | EXTERNAL PROCEDURES/FUNCTION |
826 | arp_util.calc_acctd_amount and arp_debug.debug |
827 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
828 | |
829 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
830 | |
831 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
832 | 06/23/1998 Guat Eng Tan Bug fix #658876. Added IF conditions before |
833 | the three calls to calc_acctd_amount. |
834 | |
835 | 08/29/2005 M Raymond 4566510 - Modified for etax. Made discount
836 | parameters IN/OUT and modified calls
837 | to calc_amount_and_remaining so that they
838 | would only happen if discounts were not
839 | already prorated.
840 | 12/18/2006 M Raymond 5677984 - Update l_line_remaining and
841 | l_tax_remaining after skipping
842 | calc_applied_and_remaining
843 +===========================================================================*/
844 PROCEDURE update_invoice_related_columns(
845 p_app_type IN VARCHAR2,
846 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
847 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
848 p_discount_taken_earned IN ar_payment_schedules.discount_taken_earned%TYPE,
849 p_discount_taken_unearned IN ar_payment_schedules.discount_taken_unearned%TYPE,
850 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
851 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
852 p_acctd_amount_applied OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
853 p_acctd_earned_discount_taken OUT NOCOPY ar_receivable_applications.earned_discount_taken%TYPE,
854 p_acctd_unearned_disc_taken OUT NOCOPY ar_receivable_applications.acctd_unearned_discount_taken%TYPE,
855 p_line_applied OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
856 p_tax_applied OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
857 p_freight_applied OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
858 p_charges_applied OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
859 p_line_ediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
860 p_tax_ediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
861 p_freight_ediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
862 p_charges_ediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
863 p_line_uediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
864 p_tax_uediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
865 p_freight_uediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
866 p_charges_uediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
867 p_rule_set_id OUT NOCOPY number,
868 p_ps_rec IN ar_payment_schedules%ROWTYPE,
869 p_cash_receipt_id IN ar_receivable_applications_all.cash_receipt_id%TYPE,
870 p_ra_app_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
871 p_gt_id OUT NOCOPY NUMBER) IS
872
873 --
874 -- Temp variables
875 --
876 l_ps_rec ar_payment_schedules%ROWTYPE;
877 l_discount_taken_total ar_payment_schedules.amount_applied%TYPE:=0;
878 l_tax_discounted ar_receivable_applications.tax_applied%TYPE:=0;
879 l_freight_discounted ar_receivable_applications.freight_applied%TYPE:=0;
880 l_line_discounted ar_receivable_applications.line_applied%TYPE:=0;
881 l_charges_discounted
882 ar_receivable_applications.receivables_charges_applied%TYPE:=0;
883 l_line_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
884 l_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
885 l_rec_charges_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
886 l_freight_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
887 --
888 -- Variable to populate OUT NOCOPY arguments
889 -- We need these variables, 'cos the remaining LINE, TAX, FREIGHT and CHARGES
890 -- amounts are calculated based on the applied amounts and applied discounts
891 --
892 l_tax_applied ar_receivable_applications.tax_applied%TYPE:=0;
893 l_freight_applied ar_receivable_applications.freight_applied%TYPE:=0;
894 l_line_applied ar_receivable_applications.line_applied%TYPE:=0;
895 l_charges_applied ar_receivable_applications.receivables_charges_applied%TYPE:=0;
896 l_tax_ediscounted ar_receivable_applications.tax_applied%TYPE:=0;
897 l_freight_ediscounted ar_receivable_applications.freight_applied%TYPE:=0;
898 l_line_ediscounted ar_receivable_applications.line_applied%TYPE:=0;
899 l_charges_ediscounted
900 ar_receivable_applications.receivables_charges_applied%TYPE:=0;
901 l_tax_uediscounted ar_receivable_applications.tax_applied%TYPE:=0;
902 l_freight_uediscounted ar_receivable_applications.freight_applied%TYPE:=0;
903 l_line_uediscounted ar_receivable_applications.line_applied%TYPE:=0;
904 l_charges_uediscounted
905 ar_receivable_applications.receivables_charges_applied%TYPE:=0;
906 --
907 l_acctd_amount_applied
908 ar_receivable_applications.acctd_amount_applied_from%TYPE:=0;
909 l_acctd_earned_discount_taken
910 ar_receivable_applications.earned_discount_taken%TYPE:=0;
911 l_acctd_unearned_disc_taken
912 ar_receivable_applications.acctd_unearned_discount_taken%TYPE:=0;
913 --
914
915 --Introduced For Bug # 2711860
916 -- ORASHID
917 --
918 l_nocopy_amt_due_remain
919 ar_payment_schedules.amount_due_remaining%TYPE;
920 l_nocopy_acctd_amt_due_remain
921 ar_payment_schedules.acctd_amount_due_remaining%TYPE;
922
923 -- 5569488
924 l_receipt_confirmed_flag ar_payment_schedules.receipt_confirmed_flag%TYPE ;
925
926 /* 5677984 - locals for etax calls */
927 l_ebt_customer_trx_id ar_payment_schedules.customer_trx_id%TYPE;
928 l_ebt_discount_amt NUMBER;
929 l_ebt_gt_id NUMBER;
930 l_ebt_prorated_line NUMBER;
931 l_ebt_prorated_tax NUMBER;
932 l_ebt_ra_app_id NUMBER;
933
934 BEGIN
935 IF PG_DEBUG in ('Y', 'C') THEN
936 arp_debug.debug( 'arp_ps_util.update_invoice_related_columns()+' );
937 END IF;
938 --
939 IF PG_DEBUG in ('Y', 'C') THEN
940 arp_debug.debug( 'PS ID : '||p_ps_id );
941 arp_debug.debug( 'Amount Applied : '||p_amount_applied );
942 arp_debug.debug( 'Earned Disc. Taken : '||p_discount_taken_earned );
943 arp_debug.debug( 'Unearned Disc. Taken : '||p_discount_taken_unearned );
944 arp_debug.debug( 'Apply Date : '||TO_CHAR( p_apply_date ) );
945 arp_debug.debug( 'GL Date : '||TO_CHAR( p_gl_date ) );
946 END IF;
947 --
948 IF ( p_ps_rec.payment_schedule_id is NOT NULL ) THEN
949 l_ps_rec := p_ps_rec;
950 validate_args_upd_inv_rel_cols( l_ps_rec.payment_schedule_id,
951 p_amount_applied,
952 p_discount_taken_earned,
953 p_discount_taken_unearned,
954 p_apply_date, p_gl_date );
955 ELSE
956 validate_args_upd_inv_rel_cols( p_ps_id,
957 p_amount_applied,
958 p_discount_taken_earned,
959 p_discount_taken_unearned,
960 p_apply_date, p_gl_date );
961 arp_ps_pkg.fetch_p( p_ps_id, l_ps_rec );
962 END IF;
963
964 -- Bug 13543605. Extracting the value of overapplication flag of the transaction.
965 BEGIN
966 SELECT ctt.allow_overapplication_flag
967 INTO arp_util.PG_OVERAPP_ALLOW_FLAG
968 FROM RA_CUST_TRX_TYPES ctt,
969 AR_PAYMENT_SCHEDULES ps,
970 RA_CUSTOMER_TRX ct
971 WHERE ps.customer_trx_id = ct.customer_trx_id
972 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
973 AND ct.org_id = ctt.org_id
974 AND ps.payment_schedule_id = p_ps_id;
975 EXCEPTION
976 WHEN NO_DATA_FOUND THEN
977 IF PG_DEBUG in ('Y', 'C') THEN
978 arp_debug.debug('No data Found while checking overapplication flag.');
979 END IF;
980 WHEN OTHERS THEN
981 IF PG_DEBUG in ('Y', 'C') THEN
982 arp_debug.debug('Error occurred while checking overapplication flag.');
983 END IF;
984 END;
985 --
986 -- Init. temp. variables
987 --
988 l_discount_taken_total := p_discount_taken_earned +
989 p_discount_taken_unearned;
990
991 l_line_remaining := l_ps_rec.amount_line_items_remaining;
992 l_tax_remaining := l_ps_rec.tax_remaining;
993 l_freight_remaining := l_ps_rec.freight_remaining;
994 l_rec_charges_remaining := l_ps_rec.receivables_charges_remaining;
995
996 p_rule_set_id := ARP_APP_CALC_PKG.GET_RULE_SET_ID(l_ps_rec.cust_trx_type_id);
997
998 IF nvl(p_discount_taken_earned,0) <> 0
999 THEN
1000
1001 /* Always call this logic (etax or no) to insure
1002 that freight and charges receive their fair share */
1003
1004 ARP_APP_CALC_PKG.calc_applied_and_remaining(
1005 p_discount_taken_earned
1006 ,p_rule_set_id
1007 ,l_ps_rec.invoice_currency_code
1008 ,l_line_remaining
1009 ,l_tax_remaining
1010 ,l_freight_remaining
1011 ,l_rec_charges_remaining
1012 ,l_line_ediscounted
1013 ,l_tax_ediscounted
1014 ,l_freight_ediscounted
1015 ,l_charges_ediscounted );
1016
1017 /* 5677984 - etax needs to be called */
1018 IF p_cash_receipt_id IS NOT NULL
1019 THEN
1020 /* set up local variables for call */
1021 SELECT customer_trx_id
1022 INTO l_ebt_customer_trx_id
1023 FROM ar_payment_schedules
1024 WHERE payment_schedule_id = p_ps_id;
1025
1026 l_ebt_discount_amt := l_line_ediscounted + l_tax_ediscounted;
1027
1028 /* prorate earned discount based on etax */
1029 arp_etax_util.prorate_recoverable(
1030 p_adj_id => p_cash_receipt_id,
1031 p_target_id => l_ebt_customer_trx_id,
1032 p_target_line_id => NULL,
1033 p_amount => l_ebt_discount_amt,
1034 p_apply_date => p_apply_date,
1035 p_mode => 'APP_ED',
1036 p_upd_adj_and_ps => 'N',
1037 p_gt_id => l_ebt_gt_id,
1038 p_prorated_line => l_ebt_prorated_line,
1039 p_prorated_tax => l_ebt_prorated_tax,
1040 p_ra_app_id => l_ebt_ra_app_id);
1041
1042 /* Now sort out the results so that we either
1043 used the etax or non-etax amounts for line
1044 and tax discounts */
1045 IF l_ebt_prorated_tax <> 0
1046 THEN
1047 l_line_remaining := l_line_remaining
1048 + l_line_ediscounted
1049 - l_ebt_prorated_line;
1050 l_tax_remaining := l_tax_remaining
1051 + l_tax_ediscounted
1052 - l_ebt_prorated_tax;
1053
1054 l_line_ediscounted := l_ebt_prorated_line;
1055 l_tax_ediscounted := l_ebt_prorated_tax;
1056
1057 END IF;
1058
1059 END IF;
1060
1061 END IF;
1062
1063 IF nvl(p_discount_taken_unearned,0) <> 0
1064 THEN
1065
1066 ARP_APP_CALC_PKG.calc_applied_and_remaining(
1067 p_discount_taken_unearned
1068 ,p_rule_set_id
1069 ,l_ps_rec.invoice_currency_code
1070 ,l_line_remaining
1071 ,l_tax_remaining
1072 ,l_freight_remaining
1073 ,l_rec_charges_remaining
1074 ,l_line_uediscounted
1075 ,l_tax_uediscounted
1076 ,l_freight_uediscounted
1077 ,l_charges_uediscounted );
1078
1079 /* 5677984 - etax needs to be called */
1080 IF p_cash_receipt_id IS NOT NULL
1081 THEN
1082 /* set up local variables for call */
1083 IF l_ebt_customer_trx_id IS NULL
1084 THEN
1085 SELECT customer_trx_id
1086 INTO l_ebt_customer_trx_id
1087 FROM ar_payment_schedules
1088 WHERE payment_schedule_id = p_ps_id;
1089 END IF;
1090
1091 l_ebt_discount_amt := l_line_uediscounted + l_tax_uediscounted;
1092
1093 /* prorate unearned discount based on etax */
1094 arp_etax_util.prorate_recoverable(
1095 p_adj_id => p_cash_receipt_id,
1096 p_target_id => l_ebt_customer_trx_id,
1097 p_target_line_id => NULL,
1098 p_amount => l_ebt_discount_amt,
1099 p_apply_date => p_apply_date,
1100 p_mode => 'APP_UED',
1101 p_upd_adj_and_ps => 'N',
1102 p_gt_id => l_ebt_gt_id,
1103 p_prorated_line => l_ebt_prorated_line,
1104 p_prorated_tax => l_ebt_prorated_tax,
1105 p_ra_app_id => l_ebt_ra_app_id);
1106
1107 /* Now sort out the results so that we either
1108 used the etax or non-etax amounts for line
1109 and tax discounts */
1110 IF l_ebt_prorated_tax <> 0
1111 THEN
1112 l_line_remaining := l_line_remaining
1113 + l_line_uediscounted
1114 - l_ebt_prorated_line;
1115 l_tax_remaining := l_tax_remaining
1116 + l_tax_uediscounted
1117 - l_ebt_prorated_tax;
1118
1119 l_line_uediscounted := l_ebt_prorated_line;
1120 l_tax_uediscounted := l_ebt_prorated_tax;
1121 END IF;
1122
1123 END IF;
1124
1125 END IF;
1126
1127 IF nvl(p_amount_applied,0) <> 0 THEN
1128
1129 ARP_APP_CALC_PKG.calc_applied_and_remaining(
1130 p_amount_applied
1131 ,p_rule_set_id
1132 ,l_ps_rec.invoice_currency_code
1133 ,l_line_remaining
1134 ,l_tax_remaining
1135 ,l_freight_remaining
1136 ,l_rec_charges_remaining
1137 ,l_line_applied
1138 ,l_tax_applied
1139 ,l_freight_applied
1140 ,l_charges_applied );
1141
1142 END IF;
1143
1144
1145
1146 l_line_discounted := l_line_uediscounted + l_line_ediscounted;
1147 l_tax_discounted := l_tax_uediscounted + l_tax_ediscounted ;
1148 l_freight_discounted := l_freight_uediscounted + l_freight_ediscounted;
1149 l_charges_discounted := l_charges_uediscounted + l_charges_ediscounted;
1150 --
1151 -- Calc. Acctd. Discount values if trx_type is 'CASH'
1152 --
1153 IF ( p_app_type = 'CASH' ) THEN
1154 --
1155 -- Call acctd. amount package to get acctd. earned disc. and new ADR.
1156 -- l_acctd_earned_discount_taken is used to populate AR_RA row
1157 -- Note: ADR has already been reduced by earned_discount_taken
1158 --
1159
1160 IF (p_discount_taken_earned = 0 OR p_discount_taken_earned IS NULL) THEN
1161 IF (p_discount_taken_earned = 0) THEN
1162 p_acctd_earned_discount_taken := 0;
1163 ELSE
1164 p_acctd_earned_discount_taken := NULL;
1165 END IF;
1166 ELSE
1167
1168 -- Modified For Bug # 2711860
1169 -- ORASHID
1170 --
1171 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
1172 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
1173
1174 arp_util.calc_acctd_amount( NULL, NULL, NULL,
1175 l_ps_rec.exchange_rate,
1176 '-', /** ADR must be reduced by amount_applied */
1177 l_nocopy_amt_due_remain, /* Current ADR */
1178 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
1179 p_discount_taken_earned, /* Earned discount */
1180 l_ps_rec.amount_due_remaining, /* New ADR */
1181 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
1182 l_acctd_earned_discount_taken ); /* Acct. amount_applied */
1183 --
1184 -- Update OUT NOCOPY variable p_acctd_earned_discount_taken
1185 --
1186 p_acctd_earned_discount_taken := l_acctd_earned_discount_taken;
1187 END IF;
1188
1189 --
1190 -- Call acctd. amount package to get acctd. unearned discounts and
1191 -- new ADR. l_acctd_unearned_discount_taken is used to populate RA row
1192 -- Note: ADR has already been reduced by unearned_discount_taken
1193 --
1194
1195 IF (p_discount_taken_unearned = 0 OR p_discount_taken_unearned IS NULL) THEN
1196 IF (p_discount_taken_unearned = 0) THEN
1197 p_acctd_unearned_disc_taken := 0;
1198 ELSE
1199 p_acctd_unearned_disc_taken := NULL;
1200 END IF;
1201 ELSE
1202
1203 -- Modified For Bug # 2711860
1204 -- ORASHID
1205 --
1206 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
1207 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
1208
1209 arp_util.calc_acctd_amount( NULL, NULL, NULL,
1210 l_ps_rec.exchange_rate,
1211 '-', /** ADR must be reduced by amount_applied */
1212 l_nocopy_amt_due_remain, /* Current ADR */
1213 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
1214 p_discount_taken_unearned, /* Unearned discount */
1215 l_ps_rec.amount_due_remaining, /* New ADR */
1216 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
1217 l_acctd_unearned_disc_taken ); /* Acct. amount_applied */
1218 --
1219 -- Update OUT NOCOPY variable p_acctd_unearned_disc_taken
1220 --
1221 p_acctd_unearned_disc_taken := l_acctd_unearned_disc_taken;
1222 END IF;
1223
1224 --
1225 END IF;
1226
1227 -- -------------------------------------------------------------------
1228 -- Call acctd. discounts package to get acctd. amounts and new ADR.
1229 -- l_acctd_amount_applied is used to populate AR_RA row
1230 -- If application is of type 'CASH', then ADR has already been reduced by
1231 -- earned_discount_taken and unearned_discount_taken
1232 -- Note: This procedure populates the OUT NOCOPY variable p_acctd_amount_applied
1233 -- directly
1234 -- -------------------------------------------------------------------
1235
1236 IF (p_amount_applied IS NOT NULL) THEN
1237
1238 -- Modified For Bug # 2711860
1239 -- ORASHID
1240 --
1241 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
1242 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
1243
1244 arp_util.calc_acctd_amount( NULL, NULL, NULL,
1245 l_ps_rec.exchange_rate,
1246 '-', /** ADR must be reduced by amount_applied */
1247 l_nocopy_amt_due_remain, /* Current ADR */
1248 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
1249 p_amount_applied, /* Receipt Amount */
1250 l_ps_rec.amount_due_remaining, /* New ADR */
1251 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
1252 l_acctd_amount_applied ); /* Acct. amount_applied */
1253 --
1254 -- Update OUT NOCOPY variable p_acctd_amount_applied
1255 --
1256 p_acctd_amount_applied := l_acctd_amount_applied;
1257 ELSE
1258 p_acctd_amount_applied := NULL;
1259 END IF;
1260
1261 -- -------------------------------------------------------------------
1262 -- Update PS record columns
1263 -- In case of receipt application, amount applied is added to current
1264 -- amount applied, else it is subtracted
1265 -- -------------------------------------------------------------------
1266 IF ( p_app_type = 'CASH' ) THEN
1267 l_ps_rec.amount_applied := NVL( l_ps_rec.amount_applied, 0 ) +
1268 NVL( p_amount_applied, 0 );
1269
1270 -- 4/16/1996 H.Kaukovuo Added this way to make sure we don't brake
1271 -- anything.
1272 ELSIF (p_app_type = 'CB')
1273 THEN
1274 NULL;
1275
1276 ELSE /* Transaction type is 'CM' */
1277 l_ps_rec.amount_credited := NVL( l_ps_rec.amount_credited, 0 ) -
1278 NVL( p_amount_applied, 0 );
1279
1280 END IF;
1281
1282
1283 l_ps_rec.discount_taken_earned :=
1284 NVL( l_ps_rec.discount_taken_earned, 0 ) +
1285 NVL( p_discount_taken_earned, 0 );
1286 l_ps_rec.discount_taken_unearned :=
1287 NVL( l_ps_rec.discount_taken_unearned, 0 ) +
1288 NVL( p_discount_taken_unearned, 0 );
1289 l_ps_rec.discount_remaining := NVL( l_ps_rec.discount_remaining, 0 ) -
1290 l_discount_taken_total;
1291 l_ps_rec.amount_line_items_remaining :=
1292 NVL( l_ps_rec.amount_line_items_remaining, 0 ) -
1293 ( NVL( l_line_applied, 0 ) +
1294 NVL( l_line_discounted, 0 ) );
1295 l_ps_rec.receivables_charges_remaining :=
1296 NVL (l_ps_rec.receivables_charges_remaining, 0 ) -
1297 ( NVL( l_charges_applied, 0 ) +
1298 NVL( l_charges_discounted , 0 ) );
1299 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) -
1300 ( NVL( l_tax_applied, 0 ) +
1301 NVL( l_tax_discounted, 0 ) );
1302 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) -
1303 ( NVL( l_freight_applied, 0 ) +
1304 NVL( l_freight_discounted, 0 ) );
1305 --
1306 -- Set the closed dates and status of PS record
1307 --
1308 /* 18-MAY-2000 J Rautiainen BR Implementation
1309 * Modified to pass the class instead of 'INV' since the class can be either 'BR' or 'INV' */
1310 arp_ps_util.populate_closed_dates( p_gl_date, p_apply_date, l_ps_rec.class, l_ps_rec );
1311 --
1312 -- Update PS record
1313 --
1314 /* Bug 5569488, do not update payment schedule if the receipt requires
1315 confirmation. Added the If condition */
1316 IF p_ps_rec.cash_receipt_id IS NOT NULL THEN
1317 Select receipt_confirmed_flag
1318 into l_receipt_confirmed_flag
1319 from ar_payment_schedules_all
1320 where cash_receipt_id = p_ps_rec.cash_receipt_id ;
1321 END IF ;
1322 IF NVL(l_receipt_confirmed_flag,'Y') <> 'N' THEN
1323 arp_ps_pkg.update_p( l_ps_rec );
1324 END IF ;
1325 --
1326
1327 -- Populate out variables( 'applied and 'accounted' )
1328 -- These variables will populate the RA table row
1329 -- Note: p_acctd_unearned_disc_taken and p_acctd_earned_disc_taken have
1330 -- already been populated immediately after acctd. discount calculation
1331 -- Output discounts will be populated only if transaction type is 'CASH'
1332 -- Also, note that the applied amounts could be NULL
1333 --
1334 p_line_applied := l_line_applied;
1335 p_tax_applied := l_tax_applied;
1336 p_freight_applied := l_freight_applied;
1337 p_charges_applied := l_charges_applied;
1338 p_line_ediscounted := l_line_ediscounted;
1339 p_tax_ediscounted := l_tax_ediscounted;
1340 p_freight_ediscounted := l_freight_ediscounted;
1341 p_charges_ediscounted := l_charges_ediscounted;
1342 p_line_uediscounted := l_line_uediscounted;
1343 p_tax_uediscounted := l_tax_uediscounted;
1344 p_freight_uediscounted := l_freight_uediscounted;
1345 p_charges_uediscounted := l_charges_uediscounted;
1346
1347 /* 5677984 - return parameters from etax */
1348 p_ra_app_id := l_ebt_ra_app_id;
1349 p_gt_id := l_ebt_gt_id;
1350
1351 --
1352 -- Bug 13543605, Re-setting the value of arp_util.PG_OVERAPP_ALLOW_FLAG.
1353 arp_util.PG_OVERAPP_ALLOW_FLAG := NULL;
1354 IF PG_DEBUG in ('Y', 'C') THEN
1355 arp_debug.debug( 'arp_ps_util.update_invoice_related_columns()-' );
1356 END IF;
1357 --
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 IF PG_DEBUG in ('Y', 'C') THEN
1361 arp_debug.debug(
1362 'EXCEPTION: arp_ps_util.update_invoice_related_columns' );
1363 END IF;
1364 arp_util.PG_OVERAPP_ALLOW_FLAG := NULL;
1365 RAISE;
1366 END update_invoice_related_columns;
1367
1368 --
1369 /*===========================================================================+
1370 | PROCEDURE |
1371 | validate_args_upd_inv_rel_columns |
1372 | |
1373 | DESCRIPTION |
1374 | Validate arguments passed to update_receipt_related_cols procedure |
1375 | |
1376 | SCOPE - PUBLIC |
1377 | |
1378 | PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
1379 | schedule |
1380 | p_amount_applied - Inout amount applied |
1381 | p_earned_discount - Earned discount |
1382 | p_unearned_discount - Unearned discount |
1383 | p_gldate - GL date of the receipt |
1384 | p_apply_date - Apply Date of the receipt |
1385 | |
1386 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
1387 | |
1388 | NOTES - |
1389 | |
1390 +===========================================================================*/
1391 PROCEDURE validate_args_upd_inv_rel_cols(
1392 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1393 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
1394 p_earned_discount IN
1395 ar_payment_schedules.discount_taken_earned%TYPE,
1396 p_unearned_discount IN
1397 ar_payment_schedules.discount_taken_unearned%TYPE,
1398 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
1399 p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
1400 BEGIN
1401 IF PG_DEBUG in ('Y', 'C') THEN
1402 arp_debug.debug( 'arp_ps_util.validate_args_upd_inv_rel_cols()+' );
1403 END IF;
1404 --
1405 IF ( p_ps_id IS NULL OR p_apply_date IS NULL OR p_gl_date IS NULL ) THEN
1406 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1407 APP_EXCEPTION.raise_exception;
1408 END IF;
1409 --
1410 -- At least one input amount should not be NULL
1411 --
1412 IF ( p_amount_applied IS NULL AND p_earned_discount IS NULL AND
1413 p_unearned_discount IS NULL ) THEN
1414 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1415 APP_EXCEPTION.raise_exception;
1416 END IF;
1417 --
1418 IF PG_DEBUG in ('Y', 'C') THEN
1419 arp_debug.debug( 'arp_util.validate_args_upd_inv_rel_cols()-' );
1420 END IF;
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 IF PG_DEBUG in ('Y', 'C') THEN
1424 arp_debug.debug('validate_args_upd_inv_rel_cols: ' ||
1425 'EXCEPTION: arp_ps_util.validate_args_upd_inv_rel_cols' );
1426 END IF;
1427 RAISE;
1428 END validate_args_upd_inv_rel_cols;
1429 --
1430 /*===========================================================================+
1431 | PROCEDURE |
1432 | update_cm_related_columns |
1433 | |
1434 | DESCRIPTION |
1435 | This procedure updates the CM related rows of a payment schedule|
1436 | The passed in PS ID is assumed to belong to a CM. The procedure |
1437 | sets the gl_date and gl_date_closed and amount(s) applied. The |
1438 | procedure should be called whenever a receipt is applied to an |
1439 | invoice. |
1440 | |
1441 | SCOPE - PUBLIC |
1442 | |
1443 | PARAMETERS : |
1444 | IN : p_payment_schedule_id - payment_schedule_id of Credir Memo |
1445 | p_gldate - GL date of the receipt |
1446 | p_apply_date - Apply Date of the receipt |
1447 | p_amount_applied - Amount of the CM applied to the |
1448 | invoice |
1449 | p_ps_rec - Payment Schedule record, If this field|
1450 | is not null, the PS record is not |
1451 | fetched using p_ps_id. This PS record |
1452 | is used |
1453 | p_update_credit_flag - For CM refunds, to indicate if amount |
1454 | credited should be updated |
1455 | OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
1456 | populate acctd_amount_applied_from in|
1457 | AR_RA table |
1458 | p_tax_applied - Part of the applied amount applied to|
1459 | tax, This field will populate |
1460 | TAX_REMAINING in RA table |
1461 | p_freight_applied - Part of the applied amount applied to|
1462 | freight, This field will populate |
1463 | FREIGHT_REMAINING in RA table |
1464 | p_line_applied - Part of the applied amount applied to|
1465 | lines, This field will populate |
1466 | LINE_REMAINING in RA table |
1467 | p_charges_applied - Part of the applied amount applied to|
1468 | receivable charges, This field will |
1469 | populate CHARGES_REMAINING in RA |
1470 | table |
1471 | |
1472 | EXTERNAL PROCEDURES/FUNCTION |
1473 | arp_util.calc_acctd_amount and arp_debug.debug |
1474 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
1475 | |
1476 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
1477 | |
1478 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
1479 | |
1480 +===========================================================================*/
1481 PROCEDURE update_cm_related_columns(
1482 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1483 p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
1484 p_line_applied IN ar_receivable_applications.line_applied%TYPE,
1485 p_tax_applied IN ar_receivable_applications.tax_applied%TYPE,
1486 p_freight_applied IN
1487 ar_receivable_applications.freight_applied%TYPE,
1488 p_charges_applied IN
1489 ar_receivable_applications.receivables_charges_applied%TYPE,
1490 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
1491 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
1492 p_acctd_amount_applied OUT NOCOPY
1493 ar_receivable_applications.acctd_amount_applied_to%TYPE,
1494 p_ps_rec IN ar_payment_schedules%ROWTYPE,
1495 p_update_credit_flag IN VARCHAR2 ) IS
1496 --
1497 l_ps_rec ar_payment_schedules%ROWTYPE;
1498 --
1499 -- Variable to populate OUT NOCOPY arguments
1500 --
1501 l_acctd_amount_applied
1502 ar_receivable_applications.acctd_amount_applied_from%TYPE;
1503
1504 --Introduced For Bug # 2711860
1505 -- ORASHID
1506 --
1507 l_nocopy_amt_due_remain
1508 ar_payment_schedules.amount_due_remaining%TYPE;
1509 l_nocopy_acctd_amt_due_remain
1510 ar_payment_schedules.acctd_amount_due_remaining%TYPE;
1511
1512 BEGIN
1513 IF PG_DEBUG in ('Y', 'C') THEN
1514 arp_debug.debug( 'arp_ps_util.update_cm_related_columns()+' );
1515 END IF;
1516 --
1517 IF PG_DEBUG in ('Y', 'C') THEN
1518 arp_debug.debug( 'PS ID : '||p_ps_id );
1519 arp_debug.debug( 'Amount Applied : '||p_amount_applied );
1520 arp_debug.debug( 'Apply Date : '||TO_CHAR( p_apply_date ) );
1521 arp_debug.debug( 'GL Date : '||TO_CHAR( p_gl_date ) );
1522 END IF;
1523 --
1524 IF ( p_ps_rec.payment_schedule_id is NOT NULL ) THEN
1525 l_ps_rec := p_ps_rec;
1526 validate_args_upd_rel_cols( l_ps_rec.payment_schedule_id,
1527 p_amount_applied,
1528 p_apply_date, p_gl_date );
1529 ELSE
1530 validate_args_upd_rel_cols( p_ps_id, p_amount_applied,
1531 p_apply_date, p_gl_date );
1532 arp_ps_pkg.fetch_p( p_ps_id, l_ps_rec );
1533 END IF;
1534 --
1535 -- Call acctd. amount package to get acctd. amounts. and new ADR.
1536 -- Note: This procedure populates the OUT NOCOPY variable
1537 -- p_acctd_amount_applied directly
1538 --
1539
1540 -- Modified For Bug # 2711860
1541 -- ORASHID
1542 --
1543 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
1544 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
1545
1546 arp_util.calc_acctd_amount( NULL, NULL, NULL,
1547 l_ps_rec.exchange_rate,
1548 '+', /** amount_applied must be added to ADR */
1549 l_nocopy_amt_due_remain, /* Current ADR */
1550 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
1551 p_amount_applied, /* Receipt Amount */
1552 l_ps_rec.amount_due_remaining, /* New ADR */
1553 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
1554 l_acctd_amount_applied ); /* Acct. amount_applied */
1555 --
1556 -- Update OUT NOCOPY variable p_acctd_amount_applied
1557 --
1558 p_acctd_amount_applied := l_acctd_amount_applied;
1559 --
1560 -- Update PS record columns
1561 -- Note: The amount applied is subtracted from current amount applied
1562 --
1563 l_ps_rec.amount_applied := NVL( l_ps_rec.amount_applied, 0 ) -
1564 p_amount_applied ;
1565 --
1566 -- Add applied amounts to amounts remaining
1567 --
1568 IF ( p_line_applied IS NOT NULL ) THEN
1569 l_ps_rec.amount_line_items_remaining :=
1570 NVL( l_ps_rec.amount_line_items_remaining, 0 ) +
1571 p_line_applied;
1572 END IF;
1573 --
1574 IF ( p_charges_applied IS NOT NULL ) THEN
1575 l_ps_rec.receivables_charges_remaining :=
1576 NVL (l_ps_rec.receivables_charges_remaining, 0 ) +
1577 p_charges_applied;
1578 END IF;
1579 --
1580 IF ( p_tax_applied IS NOT NULL ) THEN
1581 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) +
1582 p_tax_applied;
1583 END IF;
1584 --
1585 IF ( p_freight_applied IS NOT NULL ) THEN
1586 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) +
1587 p_freight_applied;
1588 END IF;
1589
1590 /* Bug 4122494 CM refunds */
1591 IF ( NVL(p_update_credit_flag,'N') = 'Y' ) THEN
1592 l_ps_rec.amount_credited := NVL( l_ps_rec.amount_credited, 0 ) +
1593 p_amount_applied ;
1594 END IF;
1595
1596 --
1597 -- Set the closed dates and status of PS record
1598 --
1599 arp_ps_util.populate_closed_dates( p_gl_date, p_apply_date, 'CM', l_ps_rec );
1600 --
1601 -- Update PS record
1602 --
1603 arp_ps_pkg.update_p( l_ps_rec );
1604 --
1605 IF PG_DEBUG in ('Y', 'C') THEN
1606 arp_debug.debug( 'arp_ps_util.update_cm_related_columns()-' );
1607 END IF;
1608 --
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611 IF PG_DEBUG in ('Y', 'C') THEN
1612 arp_debug.debug(
1613 'EXCEPTION: arp_ps_util.update_cm_related_columns' );
1614 END IF;
1615 RAISE;
1616 END update_cm_related_columns;
1617 --
1618 /*===========================================================================+
1619 | PROCEDURE |
1620 | update_adj_related_columns |
1621 | |
1622 | DESCRIPTION |
1623 | This procedure updates the Adjustments related rows of a PS record |
1624 | The passed in PS ID is assumed to belong to an adjustment. Procedure |
1625 | sets the gl_date and gl_date_closed and amount(s) applied. The |
1626 | procedure should be called whenever an invoice is adjusted. |
1627 | In case of an invoice adjustment, the procedure also calculates the |
1628 | line_adjusted, tax_adjusted, charges_adjusted and freight_adjusted |
1629 | amounts. |
1630 | |
1631 | SCOPE - PUBLIC |
1632 | |
1633 | PARAMETERS : |
1634 | IN : p_payment_schedule_id - payment_schedule_id of payment |
1635 | schedule |
1636 | p_type - Adjustment type - valid values are |
1637 | 'INVOICE', 'FREIGHT', 'TAX', 'LINE', |
1638 | 'CHARGES', NULL(In case of pendings only) |
1639 | There is no explicit check to make sure |
1640 | that the type value is one of the above |
1641 | p_gldate - GL date of the receipt |
1642 | p_apply_date - Apply Date of the receipt |
1643 | p_amount_adjusted - Amount adjusted if type is not 'INVOICE' |
1644 | p_amount_adjusted_pending - Amount adjusted pending if any. |
1645 | IN : p_line_adjusted - Line adjusted - In case of INVOICE adj. |
1646 | p_tax_adjusted - Tax adjusted - In case of INVOICE adj. |
1647 | p_charges_adjusted - charges adjusted - In case of INVOICE adj.|
1648 | p_freight_adjusted - freight adjusted - In case of INVOICE adj.|
1649 | |
1650 | EXTERNAL PROCEDURES/FUNCTION |
1651 | arp_util.calc_acctd_amount and arp_debug.debug |
1652 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
1653 | |
1654 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
1655 | At present this is an overloaded procedure |
1656 |
1657 | HISTORY
1658 | 8/24/1995 Ganesh Vaidee Created
1659 | 4/17/1996 Harri Kaukovuo Added functionality to handle
1660 | chargebacks properly.
1661 | 02/02/2000 Saloni Shah Modified the parameter p_freight_Adjusted
1662 | to IN OUT NOCOPY parameter.
1663 | Changes made for reverse adjustment:
1664 | when called from create_adjustments
1665 | the p_type being sent from create_adjustment
1666 | is set to REVERSE while updating the
1667 | payment schedules. When the value is set
1668 | to 'REVERSE' the amounts being passed
1669 | for p_amount_adjusted, p_tax_adjusted,
1670 | p_freight_adjusted and p_line_adjusted
1671 | should not be modified, they should be
1672 | set to the values passed by create_adjustment.
1673 | 06/15/00 Satheesh Nambiar Bug 1290698- When partial amount is passed,
1674 | default the line,tax,freight,charges adjusted
1675 | to actual values instead of taking the full
1676 | amounts from PS if the those values are not null
1677 | or zero.
1678 | 02/13/03 Ajay Pandit Made the p_ps_rec an IN OUT parameter.
1679 | 07/21/04 Ravi Sharma Receivables_charges_charged should be gross charges
1680 | inclusive of tax component in case of Activity of type
1681 | Finance Charges having tax code source attached.
1682 +===========================================================================*/
1683 PROCEDURE update_adj_related_columns(
1684 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1685 p_type IN ar_adjustments.type%TYPE,
1686 p_amount_adjusted IN ar_payment_schedules.amount_adjusted%TYPE,
1687 p_amount_adjusted_pending IN ar_payment_schedules.amount_adjusted_pending%TYPE,
1688 p_line_adjusted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
1689 p_tax_adjusted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
1690 p_freight_adjusted IN OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
1691 p_charges_adjusted IN OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
1692 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
1693 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
1694 p_acctd_amount_adjusted OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
1695 p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE) IS
1696 --
1697 -- deleted 'DEFAULT NULL' for p_ps_rec Rowtype attribute -bug460979 for Oracle8
1698
1699 l_ps_rec ar_payment_schedules%ROWTYPE;
1700 l_line_adjusted ar_payment_schedules.amount_adjusted%TYPE;
1701 l_tax_adjusted ar_payment_schedules.amount_adjusted%TYPE;
1702 l_freight_adjusted ar_payment_schedules.amount_adjusted%TYPE;
1703 l_charges_adjusted ar_payment_schedules.amount_adjusted%TYPE;
1704
1705 -- Bug 1919595
1706 -- Define four variables to hold the amounts remaining for line, tax, freight and charges
1707 l_line_for_adj ar_payment_schedules.amount_line_items_remaining%TYPE;
1708 l_tax_for_adj ar_payment_schedules.tax_remaining%TYPE;
1709 l_freight_for_adj ar_payment_schedules.freight_remaining%TYPE;
1710 l_charges_for_adj ar_payment_schedules.receivables_charges_remaining%TYPE;
1711
1712 --
1713 -- Variable to populate OUT NOCOPY arguments
1714 --
1715 l_acctd_amount_adjusted
1716 ar_receivable_applications.acctd_amount_applied_from%TYPE;
1717
1718 rem_adj_amt NUMBER;
1719
1720 BEGIN
1721 IF PG_DEBUG in ('Y', 'C') THEN
1722 arp_debug.debug( 'arp_ps_util.update_adj_related_columns()+' );
1723 END IF;
1724 --
1725 IF PG_DEBUG in ('Y', 'C') THEN
1726 arp_debug.debug( 'PS ID : '||p_ps_id );
1727 arp_debug.debug( 'PS REC ID : '||to_char(p_ps_rec.payment_schedule_id));
1728 arp_debug.debug( 'Type : '||p_type );
1729 arp_debug.debug( 'Amount adjusted : '||p_amount_adjusted );
1730 arp_debug.debug( 'Amount Adjusted Pend : '||p_amount_adjusted_pending );
1731 arp_debug.debug( 'Line Adjusted : '||p_line_adjusted);
1732 arp_debug.debug( 'Tax Adjusted : '||p_tax_adjusted);
1733 arp_debug.debug( 'Freight Adjusted : '||p_freight_adjusted);
1734 arp_debug.debug( 'Charges Adjusted : '||p_charges_adjusted);
1735 arp_debug.debug( 'Apply Date : '||TO_CHAR( p_apply_date ) );
1736 arp_debug.debug( 'GL Date : '||TO_CHAR( p_gl_date ) );
1737 END IF;
1738 --
1739 IF ( p_ps_rec.payment_schedule_id is NOT NULL ) THEN
1740 l_ps_rec := p_ps_rec;
1741 validate_args_upd_adj_rel_cols( l_ps_rec.payment_schedule_id,
1742 p_apply_date, p_gl_date );
1743 ELSE
1744 validate_args_upd_adj_rel_cols( p_ps_id, p_apply_date, p_gl_date );
1745 arp_ps_pkg.fetch_p( p_ps_id, l_ps_rec );
1746 END IF;
1747
1748
1749 IF ( p_type = 'INVOICE' ) THEN
1750
1751 -- 4/16/96 H.Kaukovuo Just a comment:
1752 -- This seems to be hard coded ...
1753
1754 -- Bug 650038: Set all adjusted amounts to zero if
1755 -- adjustment is rejected
1756
1757 IF (p_amount_adjusted is NULL) THEN
1758 l_line_adjusted := 0;
1759 l_tax_adjusted := 0;
1760 l_freight_adjusted := 0;
1761 l_charges_adjusted := 0;
1762
1763 --
1764 -- Populate OUT NOCOPY variables
1765 --
1766 p_line_adjusted := 0;
1767 p_tax_adjusted := 0;
1768 p_freight_adjusted := 0;
1769 p_charges_adjusted := 0;
1770
1771 ELSE
1772
1773 /*----------------------------------------------------------+
1774 | Bug 1290698- Fetch the PS line,tax,freight,charges and |
1775 | assign to the out NOCOPY variables only if the inputed amounts |
1776 | are null or zero |
1777 +----------------------------------------------------------*/
1778
1779 /* bug 2389772 : there are cases were amount to adjust is actually
1780 passed in as zero, in those cases we don't want the amount to
1781 re-default from remaining amount in ar_payment_schedules
1782
1783 Define rem_adj_amt to contain total adjustment amount
1784 passed as parameter, while processing each adjustment type
1785 keep subtracting amount from rem_adj_amt, once rem_adj_amt hits 0
1786 that means all remaining adj amounts should stay at zero
1787
1788 additional info: technically p_adj_type = 'INVOICE' should bring
1789 remaining amounts to zero, but BR assignments are an exception,
1790 a BR assignment creates an INVOICE adjustment *BUT* it does not
1791 have to bring invoice balance to zero
1792 */
1793
1794 rem_adj_amt := p_amount_adjusted;
1795
1796 IF NVL(p_line_adjusted,0) <> 0 THEN
1797 l_line_adjusted := p_line_adjusted;
1798 rem_adj_amt := rem_adj_amt - p_line_adjusted;
1799 ELSE
1800 -- issue : will there be cases where user intended p_line_adjusted to be zero ?
1801 l_line_adjusted := -l_ps_rec.amount_line_items_remaining;
1802 rem_adj_amt := rem_adj_amt - l_line_adjusted;
1803 END IF;
1804
1805 IF NVL(p_tax_adjusted,0) <> 0 THEN
1806 l_tax_adjusted := p_tax_adjusted;
1807 rem_adj_amt := rem_adj_amt - p_tax_adjusted;
1808 ELSE
1809 if abs(rem_adj_amt) >= abs(l_ps_rec.tax_remaining) then
1810 l_tax_adjusted := -l_ps_rec.tax_remaining;
1811 rem_adj_amt := rem_adj_amt - l_tax_adjusted;
1812 else
1813 l_tax_adjusted := 0;
1814 end if;
1815 END IF;
1816
1817 IF NVL(p_freight_adjusted,0) <> 0 THEN
1818 l_freight_adjusted := p_freight_adjusted;
1819 rem_adj_amt := rem_adj_amt - p_freight_adjusted;
1820 ELSE
1821 if abs(rem_adj_amt) >= abs(l_ps_rec.freight_remaining) then
1822 l_freight_adjusted := -l_ps_rec.freight_remaining;
1823 rem_adj_amt := rem_adj_amt - l_freight_adjusted;
1824 else
1825 l_freight_adjusted := 0;
1826 end if;
1827 END IF;
1828
1829 IF NVL(p_charges_adjusted,0) <> 0 THEN
1830 l_charges_adjusted := p_charges_adjusted;
1831 rem_adj_amt := rem_adj_amt - p_charges_adjusted;
1832 ELSE
1833 if abs(rem_adj_amt) >= abs(l_ps_rec.receivables_charges_remaining) then
1834 l_charges_adjusted := -l_ps_rec.receivables_charges_remaining;
1835 rem_adj_amt := rem_adj_amt - l_charges_adjusted;
1836 else
1837 l_charges_adjusted := 0;
1838 end if;
1839 END IF;
1840
1841 p_line_adjusted := l_line_adjusted;
1842 p_tax_adjusted := l_tax_adjusted;
1843 p_freight_adjusted := l_freight_adjusted;
1844 p_charges_adjusted := l_charges_adjusted;
1845
1846
1847 END IF;
1848
1849 -- 4/16/1996 H.Kaukovuo
1850 -- Added the code to separate Chargeback from normal invoice
1851 ELSIF (p_type = 'CB')
1852 THEN
1853 -- Bug 1919595
1854 -- Removed the existing logic and added new code which prorates the chargeback amount
1855 -- (Creates INVOICE type of adjustment)
1856 /* Bug 2399863 : Commented out NOCOPY the IF condition as it is not required */
1857 /*IF NVL(l_ps_rec.amount_due_remaining,0) > 0 THEN */
1858 l_line_for_adj := l_ps_rec.amount_line_items_remaining;
1859 l_tax_for_adj := l_ps_rec.tax_remaining;
1860 l_freight_for_adj := l_ps_rec.freight_remaining;
1861 l_charges_for_adj := l_ps_rec.receivables_charges_remaining;
1862
1863 ARP_APP_CALC_PKG.calc_applied_and_remaining(
1864 p_amount_adjusted,
1865 3, -- Prorate all
1866 l_ps_rec.invoice_currency_code,
1867 l_line_for_adj,
1868 l_tax_for_adj,
1869 l_freight_for_adj,
1870 l_charges_for_adj,
1871 l_line_adjusted,
1872 l_tax_adjusted,
1873 l_freight_adjusted,
1874 l_charges_adjusted);
1875 /*END IF; */
1876
1877 --
1878 -- Populate OUT NOCOPY variables
1879 --
1880
1881 p_line_adjusted := l_line_adjusted;
1882 p_tax_adjusted := l_tax_adjusted;
1883 p_freight_adjusted := l_freight_adjusted;
1884 p_charges_adjusted := l_charges_adjusted;
1885
1886 -- End of changes for bug 1919595
1887
1888 /* Bug 2399863 : For reversing the chargeback, the values are directly passed
1889 to this procedure */
1890 ELSIF (p_type = 'CBREV') THEN
1891 l_tax_adjusted := p_tax_adjusted;
1892 l_freight_adjusted := p_freight_adjusted;
1893 l_line_adjusted := p_line_adjusted;
1894 l_charges_adjusted := p_charges_adjusted;
1895 ELSIF( p_type = 'LINE' ) THEN
1896 /*Bug 3135248*/
1897 IF (p_amount_adjusted is NULL)
1898 THEN
1899 l_line_adjusted := 0;
1900 l_tax_adjusted := 0;
1901 p_line_adjusted := 0;
1902 p_tax_adjusted := 0;
1903
1904 /* VAT changes */
1905 ELSIF nvl(p_tax_adjusted, 0) = 0
1906 THEN
1907 l_line_adjusted := p_amount_adjusted;
1908 p_line_adjusted := p_amount_adjusted;
1909 ELSE
1910 l_line_adjusted := p_line_adjusted;
1911 l_tax_adjusted := p_tax_adjusted;
1912 END IF;
1913
1914 ELSIF( p_type = 'CHARGES' ) THEN
1915 --
1916 -- Update receivables_charges_charged
1917 --
1918 /*Bug 3135248*/
1919 IF (p_amount_adjusted is NULL)
1920 THEN
1921 l_charges_adjusted := 0;
1922 l_tax_adjusted := 0;
1923 p_charges_adjusted := 0;
1924 p_tax_adjusted := 0;
1925
1926 /* VAT changes */
1927 ELSIF nvl(p_tax_adjusted, 0) = 0 THEN
1928 l_ps_rec.receivables_charges_charged :=
1929 l_ps_rec.receivables_charges_charged +
1930 p_amount_adjusted;
1931 --
1932 l_charges_adjusted := p_amount_adjusted;
1933 p_charges_adjusted := p_amount_adjusted;
1934 ELSE
1935 l_ps_rec.receivables_charges_charged :=
1936 l_ps_rec.receivables_charges_charged +
1937 p_charges_adjusted + p_tax_adjusted ; /* Bug 3166012 */
1938 l_charges_adjusted := p_charges_adjusted;
1939 l_tax_adjusted := p_tax_adjusted;
1940 END IF;
1941
1942 ELSIF( p_type = 'TAX' ) THEN
1943 l_tax_adjusted := p_amount_adjusted;
1944 p_tax_adjusted := p_amount_adjusted;
1945
1946 ELSIF( p_type = 'FREIGHT' ) THEN
1947 l_freight_adjusted := p_amount_adjusted;
1948 p_freight_adjusted := p_amount_adjusted;
1949 ELSIF (p_type = 'REVERSE' ) THEN
1950 l_tax_adjusted := p_tax_adjusted;
1951 l_freight_adjusted := p_freight_adjusted;
1952 l_line_adjusted := p_line_adjusted;
1953 l_charges_adjusted := p_charges_adjusted;
1954 END IF;
1955 --
1956
1957 IF PG_DEBUG in ('Y', 'C') THEN
1958 arp_debug.debug( 'Line Adjusted : '||p_line_adjusted );
1959 arp_debug.debug( 'TAx Adjusted : '||p_tax_adjusted );
1960 arp_debug.debug( 'Freight Adjusted : '||p_freight_adjusted );
1961 arp_debug.debug( 'Charges Adjusted : '||p_charges_adjusted );
1962 END IF;
1963
1964 update_adj_related_columns(
1965 l_ps_rec.payment_schedule_id,
1966 l_line_adjusted,
1967 l_tax_adjusted,
1968 l_freight_adjusted,
1969 l_charges_adjusted,
1970 p_amount_adjusted_pending,
1971 p_apply_date,
1972 p_gl_date,
1973 l_acctd_amount_adjusted,
1974 l_ps_rec);
1975 --
1976 p_acctd_amount_adjusted := l_acctd_amount_adjusted;
1977 --ajay assigning back the value to p_ps_rec as it has been made a IN OUT
1978 p_ps_rec := l_ps_rec;
1979 --
1980 IF PG_DEBUG in ('Y', 'C') THEN
1981 arp_debug.debug( 'arp_ps_util.update_adj_related_columns()-' );
1982 END IF;
1983 --
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 IF PG_DEBUG in ('Y', 'C') THEN
1987 arp_debug.debug(
1988 'EXCEPTION: arp_ps_util.update_adj_related_columns' );
1989 END IF;
1990 RAISE;
1991 END update_adj_related_columns;
1992 --
1993 /*===========================================================================+
1994 | PROCEDURE |
1995 | validate_args_upd_adj_rel_cols |
1996 | |
1997 | DESCRIPTION |
1998 | Validate arguments passed to update_receipt_related_cols procedure |
1999 | |
2000 | SCOPE - PUBLIC |
2001 | |
2002 | PARAMETERS : |
2003 | IN : p_payment_schedule_id - payment_schedule_id of payment |
2004 | schedule |
2005 | p_gldate - GL date of the receipt |
2006 | p_apply_date - Apply Date of the receipt |
2007 | |
2008 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
2009 | |
2010 | NOTES - At present this is an overloaded procedure |
2011 | |
2012 +===========================================================================*/
2013 PROCEDURE validate_args_upd_adj_rel_cols(
2014 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
2015 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
2016 p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
2017 BEGIN
2018 IF PG_DEBUG in ('Y', 'C') THEN
2019 arp_debug.debug( 'arp_ps_util.validate_args_upd_adj_rel_cols()+' );
2020 END IF;
2021 --
2022 IF ( p_ps_id IS NULL OR p_apply_date IS NULL OR p_gl_date IS NULL ) THEN
2023 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
2024 APP_EXCEPTION.raise_exception;
2025 END IF;
2026 --
2027 IF PG_DEBUG in ('Y', 'C') THEN
2028 arp_debug.debug( 'arp_util.validate_args_upd_adj_rel_cols()-' );
2029 END IF;
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 IF PG_DEBUG in ('Y', 'C') THEN
2033 arp_debug.debug(
2034 'EXCEPTION: arp_ps_util.validate_args_upd_adj_rel_cols' );
2035 END IF;
2036 RAISE;
2037 END validate_args_upd_adj_rel_cols;
2038 --
2039 /*===========================================================================+
2040 | PROCEDURE |
2041 | update_adj_related_columns |
2042 | |
2043 | DESCRIPTION |
2044 | This procedure updates the Adjustments related rows of a PS record |
2045 | The passed in PS ID is assumed to belong to an adjustment. Procedure |
2046 | sets the gl_date and gl_date_closed and amount(s) applied. The |
2047 | procedure should be called whenever an invoice is adjusted. |
2048 | |
2049 | SCOPE - PUBLIC |
2050 | |
2051 | PARAMETERS : |
2052 | IN : p_payment_schedule_id - payment_schedule_id of payment |
2053 | schedule |
2054 | p_gldate - GL date of the receipt |
2055 | p_apply_date - Apply Date of the receipt |
2056 | p_tax_adjusted - Part of the adjusted amount to be applied |
2057 | to tax |
2058 | p_freight_applied - Part of the adjusted amount to be applied |
2059 | to freight |
2060 | p_line_applied - Part of the adjusted amount to be applied |
2061 | to lines |
2062 | p_charges_applied - Part of the adjusted amount to be applied |
2063 | to receivable charges |
2064 | p_amount_adjusted_pending - Amount adjsuted pending if any. |
2065 | |
2066 | EXTERNAL PROCEDURES/FUNCTION |
2067 | arp_util.calc_acctd_amount and arp_debug.debug |
2068 | arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
2069 | |
2070 | NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
2071 | |
2072 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
2073 | |
2074 |02/13/03 Ajay Pandit Made the p_ps_rec an IN OUT parameter |
2075 +===========================================================================*/
2076
2077 PROCEDURE update_adj_related_columns(
2078 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
2079 p_line_adjusted IN ar_receivable_applications.line_applied%TYPE,
2080 p_tax_adjusted IN ar_receivable_applications.tax_applied%TYPE,
2081 p_freight_adjusted IN ar_receivable_applications.freight_applied%TYPE,
2082 p_charges_adjusted IN ar_receivable_applications.receivables_charges_applied%TYPE,
2083 p_amount_adjusted_pending IN ar_payment_schedules.amount_adjusted_pending%TYPE,
2084 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
2085 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
2086 p_acctd_amount_adjusted OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
2087 p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE) IS
2088 --
2089 -- deleted 'DEFAULT NULL' for p_ps_rec Rowtype attribute -bug460979 for Oracle8
2090
2091 l_ps_rec ar_payment_schedules%ROWTYPE;
2092 l_amount_adjusted ar_payment_schedules.amount_adjusted%TYPE;
2093
2094 --
2095 -- Variable to populate OUT NOCOPY arguments
2096 --
2097 l_acctd_amount_adjusted ar_receivable_applications.acctd_amount_applied_from%TYPE;
2098
2099
2100 --Introduced For Bug # 2711860
2101 -- ORASHID
2102 --
2103 l_nocopy_amt_due_remain
2104 ar_payment_schedules.amount_due_remaining%TYPE;
2105 l_nocopy_acctd_amt_due_remain
2106 ar_payment_schedules.acctd_amount_due_remaining%TYPE;
2107
2108 BEGIN
2109 IF PG_DEBUG in ('Y', 'C') THEN
2110 arp_debug.debug( 'arp_ps_util.update_adj_related_columns()+' );
2111 END IF;
2112 --
2113 IF PG_DEBUG in ('Y', 'C') THEN
2114 arp_debug.debug( 'PS ID : '||p_ps_id );
2115 arp_debug.debug( 'Line Adjusted : '||p_line_adjusted );
2116 arp_debug.debug( 'TAx Adjusted : '||p_tax_adjusted );
2117 arp_debug.debug( 'Freight Adjusted : '||p_freight_adjusted );
2118 arp_debug.debug( 'Charges Adjusted : '||p_charges_adjusted );
2119 arp_debug.debug( 'Amount Adjusted Pend : '||p_amount_adjusted_pending );
2120 arp_debug.debug( 'Apply Date : '||TO_CHAR( p_apply_date ) );
2121 arp_debug.debug( 'GL Date : '||TO_CHAR( p_gl_date ) );
2122 END IF;
2123
2124 IF ( p_ps_rec.payment_schedule_id is NOT NULL ) THEN
2125 l_ps_rec := p_ps_rec;
2126 validate_args_upd_adj_rel_cols( l_ps_rec.payment_schedule_id,
2127 p_line_adjusted, p_tax_adjusted,
2128 p_freight_adjusted, p_charges_adjusted,
2129 p_amount_adjusted_pending,
2130 p_apply_date, p_gl_date );
2131 ELSE
2132 validate_args_upd_adj_rel_cols( p_ps_id, p_line_adjusted,
2133 p_tax_adjusted, p_freight_adjusted,
2134 p_charges_adjusted,
2135 p_amount_adjusted_pending,
2136 p_apply_date, p_gl_date );
2137 arp_ps_pkg.fetch_p( p_ps_id, l_ps_rec );
2138 END IF;
2139 --
2140
2141 l_amount_adjusted := NVL( p_line_adjusted, 0 ) +
2142 NVL( p_tax_adjusted, 0 ) +
2143 NVL( p_freight_adjusted, 0 ) +
2144 NVL( p_charges_adjusted, 0 );
2145 --
2146 -- Call acctd. amount package to get acctd. amounts. and new ADR.
2147 -- Note: This procedure populates the OUT NOCOPY variable
2148 -- p_acctd_amount_applied directly
2149 --
2150
2151 -- Modified For Bug # 2711860
2152 -- ORASHID
2153 --
2154 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
2155 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
2156
2157 arp_util.calc_acctd_amount( NULL, NULL, NULL,
2158 l_ps_rec.exchange_rate,
2159 '+', /* amount_applied must be added to ADR */
2160 l_nocopy_amt_due_remain, /* Current ADR */
2161 l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
2162 l_amount_adjusted, /* Amount adjusted */
2163 l_ps_rec.amount_due_remaining, /* New ADR */
2164 l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
2165 l_acctd_amount_adjusted ); /* Acct. amount_applied */
2166 --
2167 -- Update OUT NOCOPY variable p_acctd_amount_adjusted
2168 --
2169 p_acctd_amount_adjusted := l_acctd_amount_adjusted;
2170 --
2171 -- Add amount adjusted to current amount_adjusted and subtract
2172 -- adjusted amounts from amounts remaining
2173 --
2174 l_ps_rec.amount_adjusted := nvl(l_ps_rec.amount_adjusted, 0) +
2175 l_amount_adjusted;
2176 --
2177 IF ( p_line_adjusted IS NOT NULL ) THEN
2178 l_ps_rec.amount_line_items_remaining :=
2179 NVL( l_ps_rec.amount_line_items_remaining, 0 ) +
2180 p_line_adjusted;
2181 END IF;
2182 --
2183 IF ( p_charges_adjusted IS NOT NULL ) THEN
2184 l_ps_rec.receivables_charges_remaining :=
2185 NVL (l_ps_rec.receivables_charges_remaining, 0 ) +
2186 p_charges_adjusted;
2187 END IF;
2188 --
2189 IF ( p_tax_adjusted IS NOT NULL ) THEN
2190 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) +
2191 p_tax_adjusted;
2192 END IF;
2193 --
2194 IF ( p_freight_adjusted IS NOT NULL ) THEN
2195 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) +
2196 p_freight_adjusted;
2197 END IF;
2198 --
2199 IF ( p_amount_adjusted_pending IS NOT NULL ) THEN
2200 l_ps_rec.amount_adjusted_pending :=
2201 NVL( l_ps_rec.amount_adjusted_pending, 0 ) +
2202 p_amount_adjusted_pending;
2203 END IF;
2204 --
2205 -- Set the closed dates and status of PS record
2206 --
2207 arp_ps_util.populate_closed_dates( p_gl_date, p_apply_date, 'ADJ', l_ps_rec );
2208 --
2209 -- Update PS record
2210 --
2211 arp_ps_pkg.update_p( l_ps_rec );
2212 --
2213
2214 --ajay assigning back the value to p_ps_rec as it has been made a IN OUT
2215 p_ps_rec := l_ps_rec;
2216
2217 IF PG_DEBUG in ('Y', 'C') THEN
2218 arp_debug.debug( 'arp_ps_util.update_adj_related_columns()-' );
2219 END IF;
2220 --
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 IF PG_DEBUG in ('Y', 'C') THEN
2224 arp_debug.debug(
2225 'EXCEPTION: arp_ps_util.update_adj_related_columns' );
2226 END IF;
2227 RAISE;
2228 END update_adj_related_columns;
2229 --
2230 /*===========================================================================+
2231 | PROCEDURE |
2232 | validate_args_upd_adj_rel_cols |
2233 | |
2234 | DESCRIPTION |
2235 | Validate arguments passed to update_receipt_related_cols procedure |
2236 | |
2237 | SCOPE - PUBLIC |
2238 | |
2239 | PARAMETERS : |
2240 | IN : p_payment_schedule_id - payment_schedule_id of payment |
2241 | schedule |
2242 | p_gldate - GL date of the receipt |
2243 | p_apply_date - Apply Date of the receipt |
2244 | p_tax_adjusted - Part of the adjusted amount to be applied |
2245 | to tax |
2246 | p_freight_applied - Part of the adjusted amount to be applied |
2247 | to freight |
2248 | p_line_applied - Part of the adjusted amount to be applied |
2249 | to lines |
2250 | p_charges_applied - Part of the adjusted amount to be applied |
2251 | to receivable charges |
2252 | p_amount_adjusted_pending - Amount adjsuted pending if any. |
2253 | |
2254 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
2255 | |
2256 | NOTES - |
2257 | |
2258 +===========================================================================*/
2259 PROCEDURE validate_args_upd_adj_rel_cols(
2260 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
2261 p_line_adjusted IN
2262 ar_receivable_applications.line_applied%TYPE,
2263 p_tax_adjusted IN
2264 ar_receivable_applications.tax_applied%TYPE,
2265 p_freight_adjusted IN
2266 ar_receivable_applications.freight_applied%TYPE,
2267 p_charges_adjusted IN
2268 ar_receivable_applications.receivables_charges_applied%TYPE,
2269 p_amount_adjusted_pending IN
2270 ar_payment_schedules.amount_adjusted_pending%TYPE,
2271 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
2272 p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
2273 BEGIN
2274 IF PG_DEBUG in ('Y', 'C') THEN
2275 arp_debug.debug( 'arp_ps_util.validate_args_upd_adj_rel_cols()+' );
2276 END IF;
2277 --
2278 IF ( p_ps_id IS NULL OR p_apply_date IS NULL OR p_gl_date IS NULL ) THEN
2279 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
2280 APP_EXCEPTION.raise_exception;
2281 END IF;
2282 --
2283 -- At least one input amount should not be NULL
2284 --
2285 IF ( p_line_adjusted IS NULL AND p_tax_adjusted IS NULL AND
2286 p_freight_adjusted IS NULL AND p_charges_adjusted IS NULL AND
2287 p_amount_adjusted_pending IS NULL ) THEN
2288 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
2289 APP_EXCEPTION.raise_exception;
2290 END IF;
2291 --
2292 IF PG_DEBUG in ('Y', 'C') THEN
2293 arp_debug.debug( 'arp_util.validate_args_upd_adj_rel_cols()-' );
2294 END IF;
2295 EXCEPTION
2296 WHEN OTHERS THEN
2297 IF PG_DEBUG in ('Y', 'C') THEN
2298 arp_debug.debug('validate_args_upd_adj_rel_cols: ' ||
2299 'EXCEPTION: arp_ps_util.validate_args_upd_adj_rel_cols' );
2300 END IF;
2301 RAISE;
2302 END validate_args_upd_adj_rel_cols;
2303 --
2304 /*===========================================================================+
2305 | PROCEDURE |
2306 | populate_closed_dates |
2307 | |
2308 | DESCRIPTION |
2309 | This procedure takes in a payment schedule record structure, gl_date |
2310 | apply_date, application type and populates the payment schedule |
2311 | record structure with gl_date_closed and actual_date_closed |
2312 | |
2313 | SCOPE - PUBLIC |
2314 | |
2315 | PARAMETERS : |
2316 | IN : p_gldate - GL date of the receipt |
2317 | p_apply_date - Apply Date of the receipt |
2318 | p_app_type - Application Type - 'CASH', 'CM', 'INV' |
2319 | 'ADJ' |
2320 | IN/OUT: p_ps_rec - Payment Schedule record |
2321 | PS id to be populated in input PS record |
2322 | |
2323 | EXTERNAL PROCEDURES/FUNCTION |
2324 | get_closed_dates - This procedure is in this package |
2325 | |
2326 | NOTES - Expectes PS id to be populated in input PS record |
2327 | |
2328 | HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
2329 | 16-APR-98 GJWANG Bug fix #653643 declare this as public procedure |
2330 +===========================================================================*/
2331 PROCEDURE populate_closed_dates(
2332 p_gl_date IN ar_payment_schedules.gl_date%TYPE,
2333 p_apply_date IN ar_payment_schedules.gl_date%TYPE,
2334 p_app_type IN VARCHAR2,
2335 p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE ) IS
2336 --
2337 l_gl_date_closed ar_payment_schedules.gl_date%TYPE;
2338 l_actual_date_closed ar_payment_schedules.gl_date%TYPE;
2339 --
2340 BEGIN
2341 IF PG_DEBUG in ('Y', 'C') THEN
2342 arp_debug.debug( 'arp_util.populate_closed_dates()+' );
2343 END IF;
2344 --
2345 IF( p_ps_rec.payment_schedule_id IS NULL ) THEN
2346 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
2347 APP_EXCEPTION.raise_exception;
2348 END IF;
2349 --
2350 IF ( p_ps_rec.amount_due_remaining = 0 ) THEN
2351 --
2352 -- Get gl closing dates
2353 --
2354 arp_ps_util.get_closed_dates( p_ps_rec.payment_schedule_id,
2355 p_gl_date, p_apply_date,
2356 l_gl_date_closed, l_actual_date_closed,
2357 p_app_type );
2358 --
2359 p_ps_rec.status := 'CL';
2360
2361 /* 08-JUL-2000 J Rautiainen BR Implementation
2362 * Set the reserved_value and reserved_type to NULL if closing the BR */
2363 p_ps_rec.reserved_type := NULL;
2364 p_ps_rec.reserved_value := NULL;
2365
2366 p_ps_rec.gl_date_closed := NVL( l_gl_date_closed,
2367 p_ps_rec.gl_date_closed );
2368 p_ps_rec.actual_date_closed := NVL( l_actual_date_closed,
2369 p_ps_rec.actual_date_closed );
2370 ELSE
2371 p_ps_rec.status := 'OP';
2372 p_ps_rec.gl_date_closed := ARP_GLOBAL.G_MAX_DATE;
2373 p_ps_rec.actual_date_closed := ARP_GLOBAL.G_MAX_DATE;
2374 END IF;
2375 --
2376 IF PG_DEBUG in ('Y', 'C') THEN
2377 arp_debug.debug( 'arp_util.populate_closed_dates()-' );
2378 END IF;
2379 EXCEPTION
2380 WHEN OTHERS THEN
2381 IF PG_DEBUG in ('Y', 'C') THEN
2382 arp_debug.debug('populate_closed_dates: ' ||
2383 'EXCEPTION: arp_ps_util.populate_closed_dates' );
2384 END IF;
2385 RAISE;
2386 END populate_closed_dates;
2387
2388 /*===========================================================================+
2389 | PROCEDURE |
2390 | Get gl_date_closed and actual_date_closed |
2391 | |
2392 | DESCRIPTION |
2393 | Determines gl_date_closed and actual_date_closed for a payment |
2394 | schedule. For each of the two, it returns the greatest date from |
2395 | ar_receivable_applications, ar_adjustments, and the input current |
2396 | date. If it finds no values for a date, a null string is returned. |
2397 | |
2398 | NOTE: This function does not correctly handle applications for future|
2399 | items or future cash receipts. If an application or adjustment that |
2400 | closes an item has gl_date/apply_date less than the item's |
2401 | gl_date/trx_date or the cash receipt's gl_date/deposit_date, then the|
2402 | gl_date_closed/actual_date_closed should be the greatest dates - the |
2403 | ones from the item or cash receipt. The dates returned by armclps |
2404 | will be less than the correct ones because this function selects |
2405 | only from ar_receivable_applications, ar_adjustments and the input |
2406 | "current" dates.
2407 | |
2408 | |
2409 | SCOPE - PUBLIC |
2410 | |
2411 | PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
2412 | schedule |
2413 | p_gl_reversal_date - gl_date of current uncommitted |
2414 | transaction |
2415 | p_reversal_date - apply date of current uncommitted xtion |
2416 | p_gl_date_closed - greatest of ar_adjustments.gl_date, |
2417 | ar_receivable_applications.gl_date, and |
2418 | current_gl_date. |
2419 | p_actual_date_closed - (output) greatest of |
2420 | ar_adjustments.apply_date, |
2421 | ar_receivable_applications.apply_date, |
2422 | and current_apply_date. |
2423 | |
2424 | MODIFICATION HISTORY - |
2425 | |
2426 | 08/18/98 Sushama Borde Fixed bug 705906. Modified select statment|
2427 | that gets the max gl_date and apply_date |
2428 | from ar_receivable_applications. Now check|
2429 | s the reversal_gl_date, while selecting |
2430 | apply_date, and excludes applications that|
2431 | have been reversed.
2432 | 07/04/04 S.A.P.N.Sarma The actual_date_closed and gl_date_Closed |
2433 | values have to be retrieved only for those|
2434 | txns whose payment_Schedule_id > 0. We |
2435 | need not retrieve the values for activity |
2436 | aplications. Bug 3382570. |
2437 +===========================================================================*/
2438 PROCEDURE get_closed_dates( p_ps_id IN NUMBER,
2439 p_gl_reversal_date IN DATE,
2440 p_reversal_date IN DATE,
2441 p_gl_date_closed OUT NOCOPY DATE,
2442 p_actual_date_closed OUT NOCOPY DATE ,
2443 p_app_type IN CHAR ) IS
2444 l_gl_adj DATE;
2445 l_act_adj DATE;
2446 l_gl_app DATE;
2447 l_act_app DATE;
2448 l_max_gl_date DATE;
2449 l_max_actual_date DATE;
2450 BEGIN
2451 --
2452 IF PG_DEBUG in ('Y', 'C') THEN
2453 arp_debug.debug( '>>>>>>> arp_util.get_closed_dates' );
2454 END IF;
2455 --
2456 -- Get max dates from ar_receivable_applications
2457 --
2458
2459 IF PG_DEBUG in ('Y', 'C') THEN
2460 arp_debug.debug('get_closed_dates: ' || '>>>P_PS_ID: ' || p_ps_id );
2461 END IF;
2462
2463 IF p_ps_id > 0 THEN /* Bug 3382570 */
2464
2465 SELECT MAX(gl_date),
2466 MAX(DECODE(reversal_gl_date, NULL, apply_date, NULL)) -- Bug 705906: Not to include
2467 -- application reversals while
2468 -- getting the max apply_date.
2469 INTO l_gl_app,
2470 l_act_app
2471 FROM ar_receivable_applications
2472 WHERE ( applied_payment_schedule_id = p_ps_id -- "Trx" that was applied
2473
2474 or payment_schedule_id = p_ps_id ) -- "Payment" or "Credit Memo" being
2475 -- applied.
2476 AND nvl( confirmed_flag, 'Y' ) = 'Y';
2477 --
2478 IF PG_DEBUG in ('Y', 'C') THEN
2479 arp_debug.debug('get_closed_dates: ' || ' after select appln' );
2480 END IF;
2481 --
2482 -- Get max dates from ar_adjustments
2483 --
2484 /* 18-MAY-2000 J Rautiainen BR Implementation
2485 * Added Bills Receivable transaction */
2486 /* Bug fix 3243565 : Deposits, Debit Memos and Chargebacks can also be
2487 adjusted through the receipt application window.*/
2488 IF ( p_app_type IN ( 'INV', 'BR', 'CM', 'ADJ','CB','DEP','DM' ) ) THEN
2489 SELECT MAX(gl_date),
2490 MAX(apply_date)
2491 INTO l_gl_adj,
2492 l_act_adj
2493 FROM ar_adjustments
2494 WHERE status = 'A'
2495 AND payment_schedule_id = p_ps_id;
2496 END IF;
2497 --
2498 p_gl_date_closed := GREATEST( NVL( p_gl_reversal_date,
2499 NVL( l_gl_app, l_gl_adj)
2500 ),
2501 NVL( l_gl_app,
2502 NVL( l_gl_adj, p_gl_reversal_date )
2503 ),
2504 NVL( l_gl_adj,
2505 NVL( p_gl_reversal_date, l_gl_app )
2506 )
2507 );
2508
2509 p_actual_date_closed := GREATEST( NVL( p_reversal_date,
2510 NVL( l_act_app, l_act_adj )
2511 ),
2512 NVL( l_act_app,
2513 NVL( l_act_adj, p_reversal_date )
2514 ),
2515 NVL( l_act_adj,
2516 NVL( p_reversal_date, l_act_app )
2517 )
2518 );
2519 END IF;
2520 --
2521 IF PG_DEBUG in ('Y', 'C') THEN
2522 arp_debug.debug( '<<<<<<< arp_util.get_closed_dates' );
2523 END IF;
2524 --
2525 EXCEPTION
2526 WHEN OTHERS THEN
2527 IF PG_DEBUG in ('Y', 'C') THEN
2528 arp_debug.debug( 'EXCEPTION: arp_util.get_closed_dates' );
2529 END IF;
2530 RAISE;
2531 END get_closed_dates;
2532 --
2533 END ARP_PS_UTIL;