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