[Home] [Help]
PACKAGE BODY: APPS.AP_R11_PREPAY_PKG
Source
1 PACKAGE BODY AP_R11_PREPAY_PKG AS
2 /*$Header: apr11ppb.pls 120.10 2006/03/27 20:49:50 hchacko noship $*/
3 --
4 -- Declare Local procedures
5 --
6 PROCEDURE ap_prepay_get_info(
7 X_prepay_id IN NUMBER,
8 X_invoice_id IN NUMBER,
9 X_amount_apply IN NUMBER,
10 X_user_id IN NUMBER,
11 X_last_update_login IN NUMBER,
12 X_gl_date IN OUT NOCOPY DATE,
13 X_period_name IN OUT NOCOPY VARCHAR2,
14 X_prepay_curr_amount_apply IN OUT NOCOPY NUMBER,
15 X_payment_cross_rate OUT NOCOPY NUMBER,
16 X_amount_positive OUT NOCOPY VARCHAR2,
17 X_orig_amount OUT NOCOPY NUMBER,
18 X_dist_item_amount OUT NOCOPY NUMBER,
19 X_dist_tax_amount OUT NOCOPY NUMBER,
20 X_currency_code OUT NOCOPY VARCHAR2,
21 X_base_currency OUT NOCOPY VARCHAR2,
22 X_min_unit OUT NOCOPY NUMBER,
23 X_precision OUT NOCOPY NUMBER,
24 X_base_min_unit OUT NOCOPY NUMBER,
25 X_base_precision OUT NOCOPY NUMBER,
26 X_pay_curr_min_unit OUT NOCOPY NUMBER,
27 X_pay_curr_precision OUT NOCOPY NUMBER,
28 X_max_dist OUT NOCOPY NUMBER,
29 X_orig_max_dist OUT NOCOPY NUMBER,
30 X_max_pay_num OUT NOCOPY NUMBER,
31 X_max_inv_pay OUT NOCOPY NUMBER,
32 X_copy_inv_pay_id OUT NOCOPY NUMBER,
33 /* Bug 3700128. MOAC Project */
34 X_org_id OUT NOCOPY NUMBER,
35 X_calling_from IN VARCHAR2,
36 X_calling_sequence IN VARCHAR2);
37
38 PROCEDURE appp_update_ap_invoices(
39 X_invoice_id IN NUMBER,
40 X_prepay_id IN NUMBER,
41 X_amount_apply IN NUMBER,
42 X_prepay_curr_amount_apply IN NUMBER,
43 X_user_id IN NUMBER,
44 X_base_currency IN VARCHAR2,
45 X_min_unit IN NUMBER,
46 X_precision IN NUMBER,
47 X_last_update_login IN NUMBER,
48 X_calling_sequence IN VARCHAR2);
49
50 PROCEDURE appp_insert_invoice_dist(
51 X_invoice_id IN NUMBER,
52 X_prepay_id IN NUMBER,
53 X_dist_line_amount IN NUMBER,
54 X_payment_cross_rate IN NUMBER,
55 X_max_dist IN OUT NOCOPY NUMBER,
56 X_copy_dist_num IN NUMBER,
57 X_user_id IN NUMBER,
58 X_min_unit IN NUMBER,
59 X_precision IN NUMBER,
60 X_base_min_unit IN NUMBER,
61 X_base_precision IN NUMBER,
62 X_gl_date IN DATE,
63 X_period_name IN VARCHAR2,
64 X_last_update_login IN NUMBER,
65 X_calling_sequence IN VARCHAR2);
66
67 PROCEDURE appp_insert_invoice_payment(
68 X_prepay_id IN NUMBER,
69 X_new_invoice_id IN NUMBER,
70 X_amount_apply IN NUMBER,
71 X_prepay_curr_amount_apply IN NUMBER,
72 X_payment_cross_rate IN NUMBER,
73 X_copy_inv_pay_id IN NUMBER,
74 X_max_inv_pay IN OUT NOCOPY NUMBER,
75 X_orig_max_dist IN NUMBER,
76 X_user_id IN NUMBER,
77 X_currency_code IN VARCHAR2,
78 X_base_currency IN VARCHAR2,
79 X_min_unit IN NUMBER,
80 X_precision IN NUMBER,
81 X_pay_curr_min_unit IN NUMBER,
82 X_pay_curr_precision IN NUMBER,
83 X_base_min_unit IN NUMBER,
84 X_base_precision IN NUMBER,
85 X_gl_date IN DATE,
86 X_period_name IN VARCHAR2,
87 X_last_update_login IN NUMBER,
88 X_calling_sequence IN VARCHAR2);
89
90
91 PROCEDURE appp_insert_payment_schedule(
92 X_prepay_id IN NUMBER,
93 X_amount_apply IN NUMBER,
94 X_prepay_curr_amount_apply IN NUMBER,
95 X_max_pay_num IN OUT NOCOPY NUMBER,
96 X_copy_payment_num IN NUMBER,
97 X_user_id IN NUMBER,
98 X_min_unit IN NUMBER,
99 X_precision IN NUMBER,
100 X_pay_curr_min_unit IN NUMBER,
101 X_pay_curr_precision IN NUMBER,
102 X_last_update_login IN NUMBER,
103 X_calling_sequence IN VARCHAR2);
104
105 PROCEDURE appp_update_payment_schedule(
106 X_invoice_id IN NUMBER,
107 X_prepay_id IN NUMBER,
108 X_amount_apply IN NUMBER,
109 X_prepay_curr_amount_apply IN NUMBER,
110 X_payment_cross_rate IN NUMBER,
111 X_amount_positive IN VARCHAR2,
112 X_copy_inv_pay_id IN NUMBER,
113 X_orig_max_dist IN NUMBER,
114 X_user_id IN NUMBER,
115 X_currency_code IN VARCHAR2,
116 X_base_currency IN VARCHAR2,
117 X_min_unit IN NUMBER,
118 X_precision IN NUMBER,
119 X_pay_curr_min_unit IN NUMBER,
120 X_pay_curr_precision IN NUMBER,
121 X_base_min_unit IN NUMBER,
122 X_base_precision IN NUMBER,
123 X_gl_date IN DATE,
124 X_period_name IN VARCHAR2,
125 X_last_update_login IN NUMBER,
126 X_calling_sequence IN VARCHAR2);
127
128
129 PROCEDURE appp_insert_invoice_prepay(
130 X_invoice_id IN NUMBER,
131 X_prepay_id IN NUMBER,
132 X_amount_apply IN NUMBER,
133 X_user_id IN NUMBER,
134 X_min_unit IN NUMBER,
135 X_precision IN NUMBER,
136 X_last_update_login IN NUMBER,
137 /* Bug 3700128. MOAC Project */
138 X_org_id IN NUMBER,
139 X_calling_sequence IN VARCHAR2);
140
141 PROCEDURE app_update_inv_distributions(
142 X_prepay_id IN NUMBER,
143 X_amount_apply IN NUMBER,
144 X_calling_sequence IN VARCHAR2);
145
146 /*========================================================================
147 * Main Procedure:
148 *
149 * This main procedure includes 9 steps and describe below.
150 * (P stands for prepayment, I for Invoice)
151 *========================================================================*/
152 PROCEDURE ap_r11_prepay(X_prepay_id IN NUMBER,
153 X_invoice_id IN NUMBER,
154 X_amount_apply IN NUMBER,
155 X_user_id IN NUMBER,
156 X_last_update_login IN NUMBER,
157 X_gl_date IN DATE,
158 X_period_name IN VARCHAR2,
159 X_calling_from IN VARCHAR2,
160 X_calling_sequence IN VARCHAR2) IS
161
162 current_calling_sequence VARCHAR2(2000);
163 P_amount_positive VARCHAR2(2);
164 P_orig_amount NUMBER;
165 P_dist_item_amount NUMBER;
166 P_dist_tax_amount NUMBER;
167 P_currency_code VARCHAR2(15);
168 P_base_currency VARCHAR2(15);
169 P_min_unit NUMBER;
170 P_precision NUMBER;
171 P_base_min_unit NUMBER;
172 P_base_precision NUMBER;
173 P_max_dist NUMBER;
174 P_orig_max_dist NUMBER;
175 P_max_pay_num NUMBER;
176 P_max_inv_pay NUMBER;
177 P_copy_inv_pay_id NUMBER;
178 P_gl_date DATE;
179 P_period_name VARCHAR(15);
180 P_prepay_curr_amount_apply NUMBER;
181 P_payment_cross_rate NUMBER;
182 P_pay_curr_min_unit NUMBER;
183 P_pay_curr_precision NUMBER;
184 /* Bug 3700128. MOAC Project */
185 p_org_id NUMBER;
186
187 BEGIN
188 -- Update the calling sequence
189 --
190 current_calling_sequence := 'AP_R11_PREPAY_PKG.ap_prepay<-'||X_calling_sequence;
191
192 /*---------------------------------------------------------------------------
193 * Step 1pi: Case p and i : for both Prepayment and Invoice:
194 * Call ap_prepay_get_info to get some parameters
195 *--------------------------------------------------------------------------*/
196 P_gl_date := X_gl_date;
197 P_period_name := X_period_name;
198
199 ap_r11_prepay_pkg.ap_prepay_get_info(
200 X_prepay_id,
201 X_invoice_id,
202 X_amount_apply,
203 X_user_id,
204 X_last_update_login,
205 P_gl_date,
206 P_period_name,
207 P_prepay_curr_amount_apply,
208 P_payment_cross_rate,
209 P_amount_positive,
210 P_orig_amount,
211 P_dist_item_amount,
212 P_dist_tax_amount,
213 P_currency_code,
214 P_base_currency,
215 P_min_unit,
216 P_precision,
217 P_base_min_unit,
218 P_base_precision,
219 P_pay_curr_min_unit,
220 P_pay_curr_precision,
221 P_max_dist,
222 P_orig_max_dist,
223 P_max_pay_num,
224 P_max_inv_pay,
225 P_copy_inv_pay_id,
226 P_org_id, /* Bug 3700128. MOAC Project */
227 X_calling_from,
228 Current_calling_sequence);
229
230 /*--------------------------------------------------------------------------
231 * -- Step 2p : case p: Prepayment: Update AP_INVOICES
232 * Call appp_update_ap_invoices:
233 * 1. Reduce the prepayment amount (invoice_amount) become
234 * (invoice_amount - amount_apply)
235 * 2. Reduce amount_paid, invoice_distribution_total, and base_amount
236 * as well
237 * (converse for Unapplication)
238 *
239 *--------------------------------------------------------------------------*/
240 ap_r11_prepay_pkg.appp_update_ap_invoices(
241 '',
242 X_prepay_id,
243 X_amount_apply,
244 P_prepay_curr_amount_apply,
245 X_user_id,
246 P_base_currency,
247 P_base_min_unit,
248 P_base_precision,
249 X_last_update_login,
250 Current_calling_sequence);
251
252 /*--------------------------------------------------------------------------
253 * -- Step 3p : case p : Prepayment: Insert AP_INVOICE_DISTRIBUTIONS (ITEM)
254 * Call appp_insert_invoice_dist:
255 * Create reversing ITEM distribution on the Prepayment, We presume dist line 1
256 * is item line. (converse for unapplication (amount_apply < 0) )
257 *--------------------------------------------------------------------------*/
258 ap_r11_prepay_pkg.appp_insert_invoice_dist(
259 X_invoice_id,
260 X_prepay_id,
261 P_dist_item_amount,
262 P_payment_cross_rate,
263 P_max_dist, /* IN/OUT parameter*/
264 1, /* Line 1 is item line */
265 X_user_id,
266 P_min_unit,
267 P_precision,
268 P_base_min_unit,
269 P_base_precision,
270 P_gl_date,
271 P_period_name,
272 X_last_update_login,
273 Current_calling_sequence);
274
275
276 /*--------------------------------------------------------------------------
277 * -- Step 4p : case p : Prepayment: Insert AP_INVOICE_DISTRIBUTIONS (TAX)
278 * Call appp_insert_invoice_dist:
279 * Create reversing TAX distribution on the Prepayment if applicable,
280 * we presume dist line 2 is tax line. (converse for unapplication)
281 *--------------------------------------------------------------------------*/
282 if (NVL(P_dist_tax_amount,0) <> 0) then
283 ap_r11_prepay_pkg.appp_insert_invoice_dist(
284 X_invoice_id,
285 X_prepay_id,
286 P_dist_tax_amount,
287 P_payment_cross_rate,
288 P_max_dist, /* Add 1 from above */ /* IN/OUT parameter*/
289 2, /* Line 2 in Tax line */
290 X_user_id,
291 P_min_unit,
292 P_precision,
293 P_base_min_unit,
294 P_base_precision,
295 P_gl_date,
296 P_period_name,
297 X_last_update_login,
298 Current_calling_sequence);
299 end if;
300
301 /*--------------------------------------------------------------------------
302 * -- Step 5p : case p : Prepayment: Insert AP_PAYMENT_SCHEDULES
303 * Call appp_insert_payment_schedule :
304 * Create additional paid Payment Schedule for the Prepayment.
305 * (converse for Unapplication)
306 *--------------------------------------------------------------------------*/
307 ap_r11_prepay_pkg.appp_insert_payment_schedule(
308 X_prepay_id,
309 X_amount_apply,
310 P_prepay_curr_amount_apply,
311 P_max_pay_num, /* IN/OUT parameter */
312 1, /* Line 1 will be copied into new line*/
313 X_user_id,
314 P_min_unit,
315 P_precision,
316 P_pay_curr_min_unit,
317 P_pay_curr_precision,
318 X_last_update_login,
319 Current_calling_sequence);
320
321
322 /*--------------------------------------------------------------------------
323 * -- Step 6p : case p : Prepayment : Insert AP_INVOICE_PAYMENTS
324 * Call appp_insert_invoice_payment :
325 * Create new positive Invoice Payments for the Prepayment (converse
326 * for Unapplication)
327 *--------------------------------------------------------------------------*/
328 ap_r11_prepay_pkg.appp_insert_invoice_payment(
329 X_invoice_id,
330 X_prepay_id,
331 X_amount_apply,
332 P_prepay_curr_amount_apply,
333 P_payment_cross_rate,
334 P_copy_inv_pay_id,
335 P_max_inv_pay, /* IN/OUT parameter*/
336 P_orig_max_dist,
337 X_user_id,
338 P_currency_code,
339 P_base_currency,
340 P_min_unit,
341 P_precision,
342 P_pay_curr_min_unit,
343 P_pay_curr_precision,
344 P_base_min_unit,
345 P_base_precision,
346 P_gl_date,
347 P_period_name,
348 X_last_update_login,
349 Current_calling_sequence);
350
351
352 /*--------------------------------------------------------------------------
353 * -- Step 7i : case i: Invoice : Update AP_INVOICES
354 * Call appp_update_ap_invoices:
355 * 1. Add the amount_apply to amount_paid for refelecting the payment
356 * amount change.
357 * 2. Update discount_amount_taken, payment_status_flag as well
358 * (converse for Unapplication)
359 * Reversed order of this and next step for Rel11 'cos calc of ROUNDING
360 * type payment distributions depends upon the payment_status_flag of the
361 * invoice. Since the next step also creates payment dists, we should
362 * first update the payment_status_flag on the invoice.
363 *--------------------------------------------------------------------------*/
364 ap_r11_prepay_pkg.appp_update_ap_invoices(
365 X_invoice_id,
366 '',
367 X_amount_apply,
368 P_prepay_curr_amount_apply,
369 X_user_id,
370 P_base_currency,
371 P_pay_curr_min_unit,
375
372 P_pay_curr_precision,
373 X_last_update_login,
374 Current_calling_sequence);
376
377 /*--------------------------------------------------------------------------
378 * -- Step 8i : case i : Invoice : Update AP_PAYMENT_SCHEDULES
379 * Call appp_update_payment_schedule :
380 *
381 * 1. Update the Payment Schedules and create new Invoice Payments on the
382 * Invoice to reflect the effective payment (converse for Unapplication)
383 * 2. Insert a new line for ap_invoice_payment to reflect the effective
384 * payment amount.
385 *--------------------------------------------------------------------------*/
386 ap_r11_prepay_pkg.appp_update_payment_schedule(
387 X_invoice_id,
388 X_prepay_id,
389 X_amount_apply,
390 P_prepay_curr_amount_apply,
391 P_payment_cross_rate,
392 P_amount_positive,
393 P_copy_inv_pay_id,
394 P_orig_max_dist,
395 X_user_id,
396 P_currency_code,
397 P_base_currency,
398 P_min_unit,
399 P_precision,
400 P_pay_curr_min_unit,
401 P_pay_curr_precision,
402 P_base_min_unit,
403 P_base_precision,
404 P_gl_date,
405 P_period_name,
406 X_last_update_login,
407 Current_calling_sequence);
408
409
410
411 /*--------------------------------------------------------------------------
412 * -- Step 9ip : case i and p: Invoice: prepayment : Update AP_INVOICE_PREPAYS
413 * Call appp_insert_invoice_prepay:
414 * 1. Update ap_invoice_prepays if there's a invoice_prepay line exit.
415 * 2. Delete record if unapply the prepayment.
416 * 3. Insert new line if there's no such record exist
417 ---------------------------------------------------------------------------*/
418 ap_r11_prepay_pkg.appp_insert_invoice_prepay(
419 X_invoice_id,
420 X_prepay_id,
421 X_amount_apply,
422 X_user_id,
423 P_pay_curr_min_unit,
424 P_pay_curr_precision,
425 X_last_update_login,
426 /* Bug 3700128. MOAC Project */
427 P_org_id,
428 Current_calling_sequence);
429
430
431 app_update_inv_distributions(
432 X_prepay_id,
433 X_amount_apply,
434 Current_calling_sequence);
435
436 END ap_r11_prepay;
437
438
439 /*==========================================================================
440 This procedure is responsible for getting values from several different
441 database column.
442
443 It includes: (same discription as above)
444 +---------------------------------------------------------------------+
445 | Variable | NULL? | Description |
446 +=====================================================================+
447 | X_gl_date | No | If the main function didn't pass any |
448 | | | value, the set it to SYSDATE |
449 +---------------------------------------------------------------------+
450 | X_period_name | No | If the main function didn't pass any |
451 | | | value, get it from gl_period_statuses |
452 +---------------------------------------------------------------------+
453 | X_amount_positive | No | Examine the sign for AMOUNT_APPLY |
454 | | | If amount_apply >0, then 'Y' - APPLY |
455 | | | <0, then 'N', means this's UNAPPLY |
456 +---------------------------------------------------------------------+
457 | X_orig_amount | No | original_prepayment_amount from |
458 | | | ap_invoice.(0 if NULL) |
459 +---------------------------------------------------------------------+
460 | X_dist_item_amount | No | (ap_invoice_distributions.amount / |
461 | | | ap_invoices.original_prepayment_amount)
462 | | | * X_amount_apply |
463 +---------------------------------------------------------------------+
464 | X_dist_tax_amount | No | X_amount_apply - X_dist_item_amount |
465 +---------------------------------------------------------------------+
466 | X_currency_code | Maybe | currency_code from ap_invoice |
467 +---------------------------------------------------------------------+
468 | X_base_currency | Maybe | currency_code from ap_system_parameter|
469 +---------------------------------------------------------------------+
470 | X_min_unit | Maybe | minimum_accountable_unit from |
471 | | | fnd_currency. |
472 +---------------------------------------------------------------------+
473 | X_precision | No | precision from fnd_currency. |
474 | | | (0 if NULL) |
475 +---------------------------------------------------------------------+
476 | X_base_min_unit | Maybe | minimum_accountable_unit from |
477 | | | fnd_currency for base_currency |
478 +---------------------------------------------------------------------+
479 | X_precision | No | precision from fnd_currency. |
480 | | | (0 if NULL)for base_currency |
481 +---------------------------------------------------------------------+
482 | X_max_dist | No(*) | max(distribution_line_number) |
483 | | | from ap_invoice_distribution |
484 | | | Use for insert a new dist line |
485 +---------------------------------------------------------------------+
486 | X_orig_max_dist | No(*) | Because X_max_dist is updatable, |
487 | | | keep a very original max_dist |
488 | | | Use only for updating ap_payment_sche.|
492 | X_max_inv_pay | No(*) | max(payment_num) from ap_invoice_paym.|
489 +---------------------------------------------------------------------+
490 | X_max_pay_num | No(*) | max(payment_num) from ap_payment_sche.|
491 +---------------------------------------------------------------------+
493 +---------------------------------------------------------------------+
494 | X_copy_inv_pay_id | No(*) | max(invoice_payment_id), it means we |
495 | | | copy the last line of invoice_payment |
496 | | | when we create a new line |
497 +---------------------------------------------------------------------+
498
499 * Currupted data if NULL.
500 *=====================================================================*/
501
502 PROCEDURE ap_prepay_get_info(
503 X_prepay_id IN NUMBER,
504 X_invoice_id IN NUMBER,
505 X_amount_apply IN NUMBER,
506 X_user_id IN NUMBER,
507 X_last_update_login IN NUMBER,
508 X_gl_date IN OUT NOCOPY DATE,
509 X_period_name IN OUT NOCOPY VARCHAR2,
510 X_prepay_curr_amount_apply IN OUT NOCOPY NUMBER,
511 X_payment_cross_rate OUT NOCOPY NUMBER,
512 X_amount_positive OUT NOCOPY VARCHAR2,
513 X_orig_amount OUT NOCOPY NUMBER,
514 X_dist_item_amount OUT NOCOPY NUMBER,
515 X_dist_tax_amount OUT NOCOPY NUMBER,
516 X_currency_code OUT NOCOPY VARCHAR2,
517 X_base_currency OUT NOCOPY VARCHAR2,
518 X_min_unit OUT NOCOPY NUMBER,
519 X_precision OUT NOCOPY NUMBER,
520 X_base_min_unit OUT NOCOPY NUMBER,
521 X_base_precision OUT NOCOPY NUMBER,
522 X_pay_curr_min_unit OUT NOCOPY NUMBER,
523 X_pay_curr_precision OUT NOCOPY NUMBER,
524 X_max_dist OUT NOCOPY NUMBER,
525 X_orig_max_dist OUT NOCOPY NUMBER,
526 X_max_pay_num OUT NOCOPY NUMBER,
527 X_max_inv_pay OUT NOCOPY NUMBER,
528 X_copy_inv_pay_id OUT NOCOPY NUMBER,
529 /* Bug 3700128. MOAC Project */
530 X_org_id OUT NOCOPY NUMBER,
531 X_calling_from IN VARCHAR2,
532 X_calling_sequence IN VARCHAR2) IS
533
534 debug_info VARCHAR2(100);
535 current_calling_sequence VARCHAR2(2000);
536 C_min_unit NUMBER;
537 C_precision NUMBER;
538 DUMMY VARCHAR2(100);
539 invoice_number VARCHAR2(50);
540 C_pay_curr_invoice_amount NUMBER;
541 C_invoice_amount NUMBER;
542 C_payment_cross_rate NUMBER;
543 C_currency_code VARCHAR2(15);
544 C_gross_amount NUMBER;
545 C_orig_prepay_amount NUMBER;
546 C_pay_curr_min_unit NUMBER;
547 C_pay_curr_precision NUMBER;
548
549 BEGIN
550 -- Update the calling sequence
551 --
552 current_calling_sequence := 'ap_prepay_get_info<-'||X_calling_sequence;
553
554
555 ---------------------------------------------------------------------
556 -- Get the amount_positive to distinguish apply or unapply prepayment
557 ---------------------------------------------------------------------
558 debug_info := 'Get the amount_positive';
559 SELECT DECODE((SIGN(X_amount_apply)), 1, 'Y', 'N')
560 INTO X_amount_positive
561 FROM sys.dual;
562
563
564 ---------------------------------------------------------------------
565 -- We need to check if the invoice has been overapplyed when
566 -- concurrent program (Invoice Import) call this procedure. Otherwise,
567 -- forms filter out NOCOPY the possibility.
568 ---------------------------------------------------------------------
569 if (X_calling_from <> 'FORM') then
570 debug_info := 'The apply amount is more than amount remaining';
571 SELECT 'Not overapplying'
572 INTO DUMMY
573 FROM ap_payment_schedules
574 WHERE invoice_id = X_invoice_id
575 GROUP BY invoice_id
576 HAVING sum(nvl(amount_remaining, 0)) >= X_amount_apply;
577 end if;
578
579
580 ---------------------------------------------------------------------
581 -- We need to check if the amount apply is greater than prepayment amount
582 -- when concurrent program (Invoice Import) call this procedure.
583 -- Otherwise, forms filter out NOCOPY the possibility.
584 ---------------------------------------------------------------------
585 if (X_calling_from <> 'FORM') then
586 debug_info := 'The apply amount is more than available amount';
587 SELECT 'Not applying more than available'
588 INTO DUMMY
589 FROM ap_invoices
590 WHERE invoice_id = X_prepay_id
591 AND invoice_amount >= X_amount_apply;
592 end if;
593
594 ---------------------------------------------------------------------
595 -- We need to check if the amount to be applied is greater than
596 -- the amount not on hold for this invoice.
597 ---------------------------------------------------------------------
598
599 debug_info := 'Get the invoice number';
600 SELECT invoice_num
601 INTO invoice_number
602 FROM ap_invoices
603 WHERE invoice_id = X_invoice_id;
604
605 debug_info := 'The apply amount is more than amount not on hold';
609 WHERE invoice_id = X_invoice_id
606 SELECT 'Not applying more than not on hold'
607 INTO DUMMY
608 FROM ap_payment_schedules
610 AND hold_flag <> 'Y'
611 GROUP BY invoice_id
612 HAVING sum(nvl(amount_remaining, 0)) >= X_amount_apply;
613
614 ---------------------------------------------------------------------
615 -- Get the invoice currency code, payment cross rate and amounts
616 ---------------------------------------------------------------------
617 debug_info := 'Get invoice currency code, payment cross rate, amounts';
618 SELECT invoice_currency_code, payment_cross_rate,
619 nvl(pay_curr_invoice_amount, invoice_amount),
620 invoice_amount, invoice_currency_code,
621 payment_cross_rate,
622 original_prepayment_amount
623 INTO X_currency_code, X_payment_cross_rate,
624 C_pay_curr_invoice_amount,
625 C_invoice_amount, C_currency_code,
626 C_payment_cross_rate,
627 C_orig_prepay_amount
628 FROM ap_invoices
629 WHERE invoice_id = X_prepay_id;
630
631
632 ---------------------------------------------------------------------
633 -- Get the base currency
634 ---------------------------------------------------------------------
635 debug_info := 'Get base currency code';
636 /* Bug 3700128. MOAC Project
637 Selected org_id also so that the same can be used for
638 insertion at later point of time */
639 SELECT base_currency_code,org_id
640 INTO X_base_currency,X_org_id
641 FROM ap_system_parameters;
642
643
644 ---------------------------------------------------------------------
645 -- Get the Min_unit and precision from ap_invoice
646 ---------------------------------------------------------------------
647 debug_info := 'Get min_unit and precision for the prepayment';
648 SELECT minimum_accountable_unit, nvl(precision,0)
649 INTO X_min_unit, X_precision
650 FROM fnd_currencies
651 WHERE currency_code = C_currency_code;
652
653
654 ---------------------------------------------------------------------
655 -- Copy into local variable - READ AGAIN !!
656 ---------------------------------------------------------------------
657 debug_info := 'Get C_min_unit and C_precision for the prepayment';
658 SELECT minimum_accountable_unit, nvl(precision,0)
659 INTO C_min_unit, C_precision
660 FROM fnd_currencies
661 WHERE currency_code = C_currency_code;
662
663
664 ---------------------------------------------------------------------
665 -- Get the Min_unit and precision from base corrency
666 ---------------------------------------------------------------------
667 debug_info :='Get min_unit and precision from base_currency';
668 SELECT MINIMUM_ACCOUNTABLE_UNIT, nvl(PRECISION,0)
669 INTO X_base_min_unit , X_base_precision
670 FROM FND_CURRENCIES
671 WHERE CURRENCY_CODE = ( SELECT BASE_CURRENCY_CODE
672 FROM AP_SYSTEM_PARAMETERS);
673
674
675 ---------------------------------------------------------------------
676 -- Get the Payment Currency Min_unit and precision from ap_invoice
677 ---------------------------------------------------------------------
678 debug_info := 'Get payment currency min_unit and precision for prepayment';
679 SELECT minimum_accountable_unit, nvl(precision,0),
680 minimum_accountable_unit, nvl(precision,0)
681 INTO X_pay_curr_min_unit, X_pay_curr_precision,
682 C_pay_curr_min_unit, C_pay_curr_precision
683 FROM fnd_currencies
684 WHERE currency_code = ( SELECT payment_currency_code
685 FROM ap_invoices
686 WHERE invoice_id = X_prepay_id);
687
688
689 ---------------------------------------------------------------------
690 -- Calculate the amount to apply in the Prepayment currency, i.e.
691 -- the invoice currency of the Prepayment
692 ---------------------------------------------------------------------
693 If (X_amount_apply > 0)
694 then
695 -- Apply case
696 If (X_amount_apply = C_pay_curr_invoice_amount)
697 then
698 -- Full application
699 X_prepay_curr_amount_apply := C_invoice_amount;
700 else
701 -- Partial application
702 X_prepay_curr_amount_apply := ap_utilities_pkg.ap_round_currency(
703 X_amount_apply / C_payment_cross_rate,
704 C_currency_code);
705 end if;
706 else
707 -- Unapply case. Get the gross_amount for first payment schedule
708 -- of the prepayment invoice.
709 debug_info := 'Get gross amount from 1st payment schedule';
710 SELECT gross_amount
711 INTO C_gross_amount
712 FROM ap_payment_schedules
713 WHERE invoice_id = X_prepay_id
714 AND payment_num = 1;
715 If (X_amount_apply = (C_pay_curr_invoice_amount - C_gross_amount))
716 then
717 -- Full unapplication
718 X_prepay_curr_amount_apply := C_invoice_amount - C_orig_prepay_amount;
719 else
720 -- Partial unapplication
721 X_prepay_curr_amount_apply := ap_utilities_pkg.ap_round_currency(
722 X_amount_apply / C_payment_cross_rate,
726
723 C_currency_code);
724 end if;
725 end if;
727
728 ---------------------------------------------------------------------
729 -- Get orig_amount, dist_item_amount, and dist_tax_amount
730 --
731 -- dist_item_amount =
732 -- D1.amount / I.original_prepayment_amount * amount_apply
733 --
734 -- dist_tax_amount =
735 -- amount_apply - (D1.amount / I.original_prepayment_amount
736 -- * amount_apply)
737 ---------------------------------------------------------------------
738
739 debug_info := 'Get orig_amount, dist_item_amount, and dist_tax_amount';
740
741 -- Perf bug 5058989
742 -- Go to base tables AP_INVIOCES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL ( only for D2 )
743 -- to eliminate MJC and reduce shared memory usage
744
745 SELECT nvl(I.original_prepayment_amount,0),
746 ap_utilities_pkg.ap_round_precision(
747 D1.amount/I.original_prepayment_amount *
748 X_amount_apply,
749 C_pay_curr_min_unit, C_pay_curr_precision),
750 DECODE(D2.line_type_lookup_code,
751 'ITEM', 0, null, 0,
752 (X_amount_apply -
753 ap_utilities_pkg.ap_round_precision(
754 D1.amount/I.original_prepayment_amount *
755 X_amount_apply,
756 C_pay_curr_min_unit, C_pay_curr_precision)))
757 INTO X_orig_amount,
758 X_dist_item_amount,
759 X_dist_tax_amount
760 FROM ap_invoices_all I, ap_invoice_distributions D1,
761 ap_invoice_distributions_all D2
762 WHERE I.invoice_id = X_prepay_id
763 AND D1.invoice_id = I.invoice_id
764 AND D1.distribution_line_number = 1
765 AND D2.invoice_id(+) = D1.invoice_id -- Perf bug 5058989 -- replace I. with D1.
766 AND D2.distribution_line_number(+) = 2;
767
768
769 ---------------------------------------------------------------------
770 -- Get max_dist from ap_invoice_distributions
771 ---------------------------------------------------------------------
772 debug_info := 'Get max_dist';
773 SELECT max(distribution_line_number),max(distribution_line_number)
774 INTO X_max_dist, X_orig_max_dist
775 FROM ap_invoice_distributions
776 WHERE invoice_id = X_prepay_id;
777
778
779 ---------------------------------------------------------------------
780 -- Get max_pay_num from ap_payment_schedules
781 ---------------------------------------------------------------------
782 debug_info := 'Get max_pay_num';
783 SELECT max(payment_num)
784 INTO X_max_pay_num
785 FROM ap_payment_schedules
786 WHERE invoice_id = X_prepay_id;
787
788
789 ---------------------------------------------------------------------
790 -- Get max_inv_pay and copy_inv_pay_id from ap_invoice_payments
791 ---------------------------------------------------------------------
792 debug_info := 'Get max_inv_pay and copy_inv_pay_id';
793 SELECT max(payment_num),
794 max(decode(payment_num,1,invoice_payment_id,0))
795 INTO X_max_inv_pay,
796 X_copy_inv_pay_id
797 FROM ap_invoice_payments
798 WHERE invoice_id = X_prepay_id;
799
800
801 ---------------------------------------------------------------------
802 -- Get period_name and gl_date if they are null
803 ---------------------------------------------------------------------
804 debug_info := 'Get gl_date';
805 if (X_gl_date IS NULL) then
806 X_gl_date := sysdate;
807 end if;
808
809 if (X_period_name IS NULL) then
810 debug_info := 'Get period_name';
811 SELECT G.period_name
812 INTO X_period_name
813 FROM gl_period_statuses G, ap_system_parameters P
814 WHERE G.application_id = 200
815 AND G.set_of_books_id = P.set_of_books_id
816 AND DECODE(X_gl_date, '',
817 sysdate, X_gl_date) between G.start_date and G.end_date
818 AND G.closing_status in ('O', 'F')
819 AND NVL(G.adjustment_period_flag, 'N') = 'N';
820 end if;
821
822
823 EXCEPTION
824 WHEN NO_DATA_FOUND then
825 if (debug_info = 'The apply amount is more than amount not on hold') then
826 FND_MESSAGE.SET_NAME('SQLAP','AP_INV_PREPAY_GT_NOT_ON_HOLD');
827 FND_MESSAGE.SET_TOKEN('INVOICE_NUM',invoice_number);
828 APP_EXCEPTION.RAISE_EXCEPTION;
829 else
830 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
831 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
832 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
833 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(X_invoice_id)
834 ||' Prepay_id = '||TO_CHAR(X_prepay_id)
835 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
836 ||' User_id = '||TO_CHAR(X_user_id)
837 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
838 ||' gl_date = '||TO_CHAR(X_gl_date)
839 ||' Period_name = '||X_period_name);
840
841 if (debug_info = 'Get min_unit and precision for the prepayment') then
842 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','No currency code for this prepayment');
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 elsif(debug_info ='Get min_unit and precision from base_currency') then
848 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','the GL_date(sysdate) is not in an open period');
845 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','No Base currency code');
846 APP_EXCEPTION.RAISE_EXCEPTION;
847 elsif(debug_info ='Get period_name') then
849 APP_EXCEPTION.RAISE_EXCEPTION;
850 else
851 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
852 APP_EXCEPTION.RAISE_EXCEPTION;
853 end if;
854 end if;
855
856 WHEN OTHERS then
857 if (SQLCODE <> -20001 ) then
858 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
859 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
860 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
861 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(X_invoice_id)
862 ||' Prepay_id = '||TO_CHAR(X_prepay_id)
863 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
864 ||' User_id = '||TO_CHAR(X_user_id)
865 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
866 ||' gl_date = '||TO_CHAR(X_gl_date)
867 ||' Period_name = '||X_period_name);
868 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
869 end if;
870
871 APP_EXCEPTION.RAISE_EXCEPTION;
872
873 END ap_prepay_get_info;
874
875
876
877
878 /*=========================================================================
879 * This procedure is used for two case (Maintain AP_INVOICES)
880 *
881 * Case 1. Prepayment:
882 * 1.1 Reduce the prepayment amount (invoice_amount) become
883 * (invoice_amount - amount_apply)
884 * 1.2 Reduce amount_paid, invoice_distribution_total, and base_amount
885 * as well
886 *
887 * Case 2. Invoice:
888 * 1.1 Add the amount_apply to amount_paid for refelecting the payment
889 * amount change.
890 * 1.2 Update discount_amount_taken, payment_status_flag as well
891 *
892 * Distingrish case for invoice or prepayment depend on Null value passing.
893 * (Invoice: prepay_id is NULL; Prepayment: invoice_id is NULL)
894 *
895 ==========================================================================*/
896
897 PROCEDURE appp_update_ap_invoices(
898 X_invoice_id IN NUMBER,
899 X_prepay_id IN NUMBER,
900 X_amount_apply IN NUMBER,
901 X_prepay_curr_amount_apply IN NUMBER,
902 X_user_id IN NUMBER,
903 X_base_currency IN VARCHAR2,
904 X_min_unit IN NUMBER,
905 X_precision IN NUMBER,
906 X_last_update_login IN NUMBER,
907 X_calling_sequence IN VARCHAR2) IS
908
909 debug_info VARCHAR2(100);
910 current_calling_sequence VARCHAR2(2000);
911
912 BEGIN
913 -- Update the calling sequence
914 --
915 current_calling_sequence := 'appp_update_ap_invoices<-'||X_calling_sequence;
916
917 if (X_invoice_id is NULL) then /* Update prepayment info */
918
919 debug_info := 'Update ap_invoice for reducing the amount';
920
921 UPDATE ap_invoices
922 SET invoice_amount = invoice_amount - X_prepay_curr_amount_apply,
923 pay_curr_invoice_amount = nvl(pay_curr_invoice_amount, invoice_amount)
924 - X_amount_apply,
925 amount_paid = amount_paid - X_amount_apply,
926 invoice_distribution_total = invoice_distribution_total -
927 X_amount_apply,
928 base_amount = DECODE(invoice_currency_code,
929 X_base_currency, base_amount,
930 base_amount -
931 ap_utilities_pkg.ap_round_precision(
932 exchange_rate * X_prepay_curr_amount_apply,
933 X_min_unit, X_precision)),
934 last_update_date = SYSDATE,
935 last_updated_by = X_user_id,
936 last_update_login = X_last_update_login
937 WHERE invoice_id = X_prepay_id;
938
939 else /* Update invoice info*/
940
941 UPDATE ap_invoices
942 SET amount_paid = nvl(amount_paid, 0) +
943 ap_utilities_pkg.ap_round_precision(
944 X_amount_apply, X_min_unit, X_precision),
945 discount_amount_taken = nvl(discount_amount_taken, 0),
946 payment_status_flag =
947 DECODE(NVL(amount_paid, 0) + NVL(discount_amount_taken, 0) +
948 ap_utilities_pkg.ap_round_precision(
949 X_amount_apply, X_min_unit, X_precision),
950 nvl(pay_curr_invoice_amount, invoice_amount), 'Y',
951 0,'N',
952 'P'),
953 last_update_date = SYSDATE,
954 last_updated_by = X_user_id,
955 last_update_login = X_last_update_login
956 WHERE invoice_id = X_invoice_id;
957
958 end if;
959
960
961 EXCEPTION
962
963 WHEN OTHERS then
964
965 if (SQLCODE <> -20001 ) then
966 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
967 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
968 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
969 FND_MESSAGE.SET_TOKEN('PARAMETERS','Prepay_id = '||TO_CHAR(X_prepay_id)
970 ||' Invoice_id = '||TO_CHAR(X_invoice_id)
971 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
972 ||' User_id = '||TO_CHAR(X_user_id)
973 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
977 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
974 ||' Base_currency = '||X_base_currency
975 ||' Min_unit = '||TO_CHAR(X_min_unit)
976 ||' Precision = '||TO_CHAR(X_precision));
978 end if;
979
980 APP_EXCEPTION.RAISE_EXCEPTION;
981
982 END appp_update_ap_invoices;
983
984
985
986
987 /*=========================================================================
988 * This procedure is used for maintain AP_INVOICES_DISTRIBUTIONS
989 *
990 * -- Only prepayment need to add new distribution line.
991 * -- the parameter X_invoice is only used for insert other_invoice_id
992 *
993 ==========================================================================*/
994 PROCEDURE appp_insert_invoice_dist(
995 X_invoice_id IN NUMBER,
996 X_prepay_id IN NUMBER,
997 X_dist_line_amount IN NUMBER,
998 X_payment_cross_rate IN NUMBER,
999 X_max_dist IN OUT NOCOPY NUMBER,
1000 X_copy_dist_num IN NUMBER,
1001 X_user_id IN NUMBER,
1002 X_min_unit IN NUMBER,
1003 X_precision IN NUMBER,
1004 X_base_min_unit IN NUMBER,
1005 X_base_precision IN NUMBER,
1006 X_gl_date IN DATE,
1007 X_period_name IN VARCHAR2,
1008 X_last_update_login IN NUMBER,
1009 X_calling_sequence IN VARCHAR2) IS
1010
1011 debug_info VARCHAR2(100);
1012 current_calling_sequence VARCHAR2(2000);
1013 new_line_num NUMBER;
1014 l_invoice_distribution_id NUMBER;
1015
1016
1017 BEGIN
1018 -- Update the calling sequence
1019 --
1020 current_calling_sequence := 'appp_insert_invoice_dist<-'||X_calling_sequence;
1021 --
1022 -- This procedure is used for creating the distribution reversals on the
1023 -- prepayment invoice
1024 --
1025
1026 X_max_dist := X_max_dist + 1;
1027 new_line_num := X_max_dist;
1028
1029 /* First get the Invoice_Distribution_Id from the sequence */
1030 SELECT ap_invoice_distributions_s.NEXTVAL
1031 INTO l_invoice_distribution_id
1032 FROM sys.dual; -- added for Invoice_Distribution_Id
1033
1034
1035 debug_info := 'Update ap_invoice_distributions for creating the distribution reversals';
1036
1037 INSERT INTO AP_INVOICE_DISTRIBUTIONS
1038 (invoice_id,
1039 dist_code_combination_id,
1040 last_update_date,
1041 last_updated_by,
1042 accounting_date,
1043 period_name,
1044 set_of_books_id,
1045 amount,
1046 description,
1047 type_1099,
1048 vat_code,
1049 posted_flag,
1050 batch_id,
1051 req_distribution_id,
1052 quantity_invoiced,
1053 unit_price,
1054 price_adjustment_flag,
1055 earliest_settlement_date,
1056 assets_addition_flag,
1057 distribution_line_number,
1058 line_type_lookup_code,
1059 base_amount,
1060 exchange_rate,
1061 exchange_rate_type,
1062 exchange_date,
1063 accrual_posted_flag,
1064 cash_posted_flag,
1065 assets_tracking_flag,
1066 pa_addition_flag,
1067 other_invoice_id,
1068 last_update_login,
1069 creation_date,
1070 created_by,
1071 invoice_distribution_id,
1072 tax_code_id,
1073 tax_code_override_flag,
1074 tax_recovery_override_flag,
1075 tax_recoverable_flag,
1076 org_id ) /* Bug 3700128. MOAC Project */
1077 SELECT invoice_id,
1078 dist_code_combination_id,
1079 SYSDATE,
1080 X_user_id,
1081 X_gl_date,
1082 X_period_name,
1083 set_of_books_id,
1084 ap_utilities_pkg.ap_round_precision(
1085 (-1) * X_dist_line_amount / X_payment_cross_rate,
1086 X_min_unit, X_precision),
1087 'Prepayment Application',
1088 type_1099,
1089 vat_code,
1090 'N',
1091 batch_id,
1092 req_distribution_id,
1093 quantity_invoiced,
1094 unit_price,
1095 price_adjustment_flag,
1096 earliest_settlement_date,
1097 'U',
1098 new_line_num,
1099 line_type_lookup_code,
1100 DECODE(base_amount, null, null,
1101 ap_utilities_pkg.ap_round_precision(
1102 (-1) * exchange_rate * X_dist_line_amount
1103 / X_payment_cross_rate,
1104 X_base_min_unit, X_base_precision)),
1105 exchange_rate,
1106 exchange_rate_type,
1107 exchange_date,
1108 'N',
1109 'N',
1110 assets_tracking_flag,
1111 'E',
1112 X_invoice_id,
1113 DECODE(X_last_update_login, -999, null, X_last_update_login),
1114 SYSDATE,
1115 X_user_id,
1116 l_invoice_distribution_id,
1117 tax_code_id,
1118 tax_code_override_flag,
1119 tax_recovery_override_flag,
1120 tax_recoverable_flag,
1121 org_id /* Bug 3700128. MOAC Project */
1122 FROM ap_invoice_distributions
1123 WHERE invoice_id = X_prepay_id
1124 AND distribution_line_number = X_copy_dist_num;
1125
1126 --Bug 4539462 DBI logging
1127 AP_DBI_PKG.Maintain_DBI_Summary
1128 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1129 p_operation => 'I',
1130 p_key_value1 => X_prepay_id,
1131 p_key_value2 => l_invoice_distribution_Id,
1135 EXCEPTION
1132 p_calling_sequence => current_calling_sequence);
1133
1134
1136 WHEN OTHERS then
1137
1138 if (SQLCODE <> -20001 ) then
1139 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1140 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1141 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1142 FND_MESSAGE.SET_TOKEN('PARAMETERS','Prepay_id = '||TO_CHAR(X_prepay_id)
1143 ||' Dist_line_amount = '||TO_CHAR(X_dist_line_amount)
1144 ||' Copy_dist_num = '||TO_CHAR(X_copy_dist_num)
1145 ||' User_id = '||TO_CHAR(X_user_id)
1146 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1147 ||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
1148 ||' Base_precision = '||TO_CHAR(X_base_precision)
1149 ||' Min_unit = '||TO_CHAR(X_min_unit)
1150 ||' Precision = '||TO_CHAR(X_precision)
1151 ||' gl_date = '||TO_CHAR(X_gl_date)
1152 ||' Period_name = '||X_period_name);
1153 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1154 end if;
1155
1156 APP_EXCEPTION.RAISE_EXCEPTION;
1157
1158 END appp_insert_invoice_dist;
1159
1160
1161
1162 /*========================================================================
1163 This procedure is used for creating an addition paid payment schedule
1164 on the prepayment invoice (Insert AP_PAYMENT_SCHEDULE)
1165 *========================================================================*/
1166 PROCEDURE appp_insert_payment_schedule(
1167 X_prepay_id IN NUMBER,
1168 X_amount_apply IN NUMBER,
1169 X_prepay_curr_amount_apply IN NUMBER,
1170 X_max_pay_num IN OUT NOCOPY NUMBER,
1171 X_copy_payment_num IN NUMBER,
1172 X_user_id IN NUMBER,
1173 X_min_unit IN NUMBER,
1174 X_precision IN NUMBER,
1175 X_pay_curr_min_unit IN NUMBER,
1176 X_pay_curr_precision IN NUMBER,
1177 X_last_update_login IN NUMBER,
1178 X_calling_sequence IN VARCHAR2) IS
1179
1180 debug_info VARCHAR2(100);
1181 current_calling_sequence VARCHAR2(2000);
1182 new_line_num NUMBER;
1183
1184 BEGIN
1185 -- Update the calling sequence
1186 --
1187 current_calling_sequence := 'appp_insert_payment_schedule<-'||X_calling_sequence;
1188
1189 X_max_pay_num := X_max_pay_num + 1;
1190 new_line_num := X_max_pay_num;
1191
1192 debug_info := 'Create an addition paid payment schedule';
1193
1194 INSERT INTO AP_PAYMENT_SCHEDULES(
1195 invoice_id,
1196 payment_num,
1197 last_update_date,
1198 last_updated_by,
1199 due_date,
1200 discount_date,
1201 gross_amount,
1202 inv_curr_gross_amount,
1203 amount_remaining,
1204 discount_amount_remaining,
1205 payment_priority,
1206 payment_method_code, --4552701
1207 hold_flag,
1208 payment_status_flag,
1209 batch_id,
1210 payment_cross_rate,
1211 future_pay_due_date,
1212 last_update_login,
1213 creation_date,
1214 created_by,
1215 org_id ) /* Bug 3700128. MOAC Project */
1216 SELECT invoice_id,
1217 new_line_num,
1218 SYSDATE,
1219 X_user_id,
1220 SYSDATE,
1221 SYSDATE,
1222 ap_utilities_pkg.ap_round_precision(
1223 (-1) * X_amount_apply, X_pay_curr_min_unit,
1224 X_pay_curr_precision),
1225 ap_utilities_pkg.ap_round_precision(
1226 (-1) * X_prepay_curr_amount_apply,
1227 X_min_unit, X_precision),
1228 0,
1229 0,
1230 payment_priority,
1231 payment_method_code, --4552701
1232 'N',
1233 'Y',
1234 batch_id,
1235 payment_cross_rate,
1236 future_pay_due_date,
1237 DECODE(X_last_update_login, -999, null, X_last_update_login),
1238 SYSDATE,
1239 X_user_id,
1240 org_id /* Bug 3700128. MOAC Project */
1241 FROM ap_payment_schedules
1242 WHERE invoice_id = X_prepay_id
1243 AND payment_num = X_copy_payment_num;
1244
1245
1246 EXCEPTION
1247 WHEN OTHERS then
1248
1249 if (SQLCODE <> -20001 ) then
1250 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1251 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1252 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1253 FND_MESSAGE.SET_TOKEN('PARAMETERS',' Prepay_id = '||TO_CHAR(X_prepay_id)
1254 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
1255 ||' Max_payment_num = '||TO_CHAR(X_max_pay_num)
1256 ||' Copy_payment_num = '||TO_CHAR(X_copy_payment_num)
1257 ||' User_id = '||TO_CHAR(X_user_id)
1258 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1259 ||' Min_unit = '||TO_CHAR(X_min_unit)
1260 ||' Precision = '||TO_CHAR(X_precision));
1261 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1262 end if;
1263
1264 APP_EXCEPTION.RAISE_EXCEPTION;
1265
1266 END appp_insert_payment_schedule;
1267
1268
1269
1270 /*===========================================================================*
1271 * This procedure is used for maintaining AP_PAYMENT_SCHEDULE
1272 * 1. Update the paid payment schedules for this invoice. *
1273 * 2. Insert a new line for ap_invoice_payment to reflect the effective *
1274 * payment amount. (call appp_insert_invoice_payment) *
1275 *===========================================================================*/
1276 PROCEDURE appp_update_payment_schedule(
1280 X_prepay_curr_amount_apply IN NUMBER,
1277 X_invoice_id IN NUMBER,
1278 X_prepay_id IN NUMBER,
1279 X_amount_apply IN NUMBER,
1281 X_payment_cross_rate IN NUMBER,
1282 X_amount_positive IN VARCHAR2,
1283 X_copy_inv_pay_id IN NUMBER,
1284 X_orig_max_dist IN NUMBER,
1285 X_user_id IN NUMBER,
1286 X_currency_code IN VARCHAR2,
1287 X_base_currency IN VARCHAR2,
1288 X_min_unit IN NUMBER,
1289 X_precision IN NUMBER,
1290 X_pay_curr_min_unit IN NUMBER,
1291 X_pay_curr_precision IN NUMBER,
1292 X_base_min_unit IN NUMBER,
1293 X_base_precision IN NUMBER,
1294 X_gl_date IN DATE,
1295 X_period_name IN VARCHAR2,
1296 X_last_update_login IN NUMBER,
1297 X_calling_sequence IN VARCHAR2) IS
1298
1299 debug_info VARCHAR2(100);
1300 current_calling_sequence VARCHAR2(2000);
1301 C_amount_apply_remaining NUMBER;
1302 C_local_pay_num NUMBER;
1303 C_local_amount NUMBER;
1304 Temp_local_pay_num NUMBER;
1305
1306 -- debug_info := 'Declare Schedule Cursor';
1307 CURSOR Schedules IS
1308 SELECT payment_num,
1309 DECODE(X_amount_positive,
1310 'N', gross_amount - amount_remaining,
1311 amount_remaining)
1312 --
1313 -- gross_amount - amount_remaining = amount_paid.<- No database column
1314 --
1315 FROM ap_payment_schedules
1316 WHERE invoice_id = X_invoice_id
1317 AND (payment_status_flag||'' = 'P'
1318 OR payment_status_flag||'' = DECODE(X_amount_positive, 'N', 'Y', 'N'))
1319 ORDER BY DECODE(X_amount_positive,
1320 'N', DECODE(payment_status_flag,'P',1,'Y',2,3),
1321 DECODE(NVL(hold_flag,'N'),'N',1,2)),
1322 DECODE(X_amount_positive,
1323 'N', due_date,
1324 NULL) DESC,
1325 DECODE(X_amount_positive,
1326 'N', NULL,
1327 due_date),
1328 DECODE(X_amount_positive,
1329 'N', DECODE(hold_flag,'N',1,'Y',2,3),
1330 DECODE(NVL(payment_status_flag,'N'),'P',1,'N',2,3));
1331
1332 BEGIN
1333 -- Update the calling sequence
1334 --
1335 current_calling_sequence := 'appp_update_payment_schedule<-'||X_calling_sequence;
1336 --
1337 -- C_amount_apply_remaining is used for recording the actually amount_apply
1338 -- left.
1339 --
1340 C_amount_apply_remaining := X_amount_apply;
1341
1342 --
1343 -- Open schedule ,fetch payment_num and amount into local variable array
1344 --
1345 debug_info := 'Open Schedule Cursor';
1346 OPEN SCHEDULES;
1347
1348 LOOP
1349
1350 debug_info := 'Fetch Schedules into local variables';
1351 FETCH SCHEDULES INTO C_local_pay_num, C_local_amount;
1352
1353 if ((((C_amount_apply_remaining - C_local_amount) <= 0) AND
1354 (X_amount_positive = 'Y')) OR
1355 (((C_amount_apply_remaining + C_local_amount) >= 0) AND
1356 (X_amount_positive = 'N'))) then
1357 /*-----------------------------------------------------------------------+
1358 * Case 1 for *
1359 * 1. In apply prepayment(amount_positive = 'Y'), the amount remaining *
1360 * is greater than amount_apply_remaining. *
1361 * 2. In unapply prepayment, the amount_apply (actually amount_unapply *
1362 * here) is greater than amount_paid (gross_amount-amount_remaining).*
1363 *
1364 * It means that this schedule line have enough amount to apply(unapply)*
1365 * the whole apply_amount. *
1366 * *
1367 * Update the amount_remaining for this payment schedule line become *
1368 * (amount_remaining - amount_apply_remaining). *
1369 +-----------------------------------------------------------------------*/
1370
1371 debug_info := 'Update ap_payment_schedule for the invoice, case 1';
1372
1373 UPDATE ap_payment_schedules
1374 SET amount_remaining = (amount_remaining -
1375 ap_utilities_pkg.ap_round_precision(
1376 C_amount_apply_remaining,
1377 X_pay_curr_min_unit, X_pay_curr_precision)),
1378 payment_status_flag =
1379 DECODE(amount_remaining -
1380 ap_utilities_pkg.ap_round_precision(
1381 C_amount_apply_remaining,
1382 X_pay_curr_min_unit, X_pay_curr_precision),
1383 0,'Y',
1384 gross_amount, 'N',
1385 'P'),
1386 last_update_date = SYSDATE,
1387 last_updated_by = X_user_id,
1388 last_update_login = X_last_update_login
1389 WHERE invoice_id = X_invoice_id
1390 AND payment_num = C_local_pay_num;
1391
1392 -- ****NOTICE**********************************
1393 -- Kludge way to prevent this function automatically add 1 for pay_num
1394 --
1395 Temp_local_pay_num := C_local_pay_num - 1;
1396
1397 debug_info := 'Call appp_insert_invoice_payment , case 1';
1398 ----------------------------------------------------------------------
1399 -- Add a new ap_invoice_payment line to adjust the amount
1400 ----------------------------------------------------------------------
1401 AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
1402 X_prepay_id,
1403 X_invoice_id,
1407 X_copy_inv_pay_id,
1404 C_amount_apply_remaining,
1405 X_prepay_curr_amount_apply,
1406 X_payment_cross_rate,
1408 Temp_local_pay_num,
1409 X_orig_max_dist,
1410 X_user_id,
1411 X_currency_code,
1412 X_base_currency,
1413 X_min_unit,
1414 X_precision,
1415 X_pay_curr_min_unit,
1416 X_pay_curr_precision,
1417 X_base_min_unit,
1418 X_base_precision,
1419 X_gl_date,
1420 X_period_name,
1421 X_last_update_login,
1422 Current_calling_sequence);
1423
1424 EXIT; /* No more amount left */
1425
1426 else
1427 /*----------------------------------------------------------------------*
1428 *Case 2 for this line don't have enough amount to apply(unapply). *
1429 * *
1430 * Update the amount_remaining to 0 and amount_apply_remaining become *
1431 * (amount_apply - amount_remaining(this line)), then go to next *
1432 * schedule line. *
1433 *----------------------------------------------------------------------*/
1434
1435 debug_info := 'Update ap_payment_schedule for the invoice, case 2';
1436 UPDATE ap_payment_schedules
1437 SET amount_remaining = DECODE(X_amount_positive,
1438 'Y', 0,
1439 gross_amount),
1440 payment_status_flag = DECODE(X_amount_positive,
1441 'Y', 'Y',
1442 'N'),
1443 last_update_date = SYSDATE,
1444 last_updated_by = X_user_id,
1445 last_update_login = X_last_update_login
1446 WHERE invoice_id = X_invoice_id
1447 AND payment_num = C_local_pay_num;
1448
1449 -- ****NOTICE**********************************
1450 -- Kludge way to prevent this function automatically add 1 for pay_num
1451 --
1452 Temp_local_pay_num := C_local_pay_num - 1;
1453
1454 if (X_amount_positive = 'Y') then
1455 -- Apply:
1456 -- Add a new ap_invoice_payment line to adjust the amount
1457 --
1458 AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
1459 X_prepay_id,
1460 X_invoice_id,
1461 C_local_amount, /* Difference from above */
1462 X_prepay_curr_amount_apply,
1463 X_payment_cross_rate,
1464 X_copy_inv_pay_id,
1465 Temp_local_pay_num, /* See notice above */
1466 X_orig_max_dist,
1467 X_user_id,
1468 X_currency_code,
1469 X_base_currency,
1470 X_min_unit,
1471 X_precision,
1472 X_pay_curr_min_unit,
1473 X_pay_curr_precision,
1474 X_base_min_unit,
1475 X_base_precision,
1476 X_gl_date,
1477 X_period_name,
1478 X_last_update_login,
1479 Current_calling_sequence);
1480 else
1481 -- Unapply:
1482 -- Add a new ap_invoice_payment line to adjust the amount
1483 --
1484 AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
1485 X_prepay_id,
1486 X_invoice_id,
1487 (-1)*C_local_amount, /* Difference from above */
1488 X_prepay_curr_amount_apply,
1489 X_payment_cross_rate,
1490 X_copy_inv_pay_id,
1491 Temp_local_pay_num, /* See notice above */
1492 X_orig_max_dist,
1493 X_user_id,
1494 X_currency_code,
1495 X_base_currency,
1496 X_min_unit,
1497 X_precision,
1498 X_pay_curr_min_unit,
1499 X_pay_curr_precision,
1500 X_base_min_unit,
1501 X_base_precision,
1502 X_gl_date,
1503 X_period_name,
1504 X_last_update_login,
1505 Current_calling_sequence);
1506 end if;
1507
1508 if (X_amount_positive = 'Y') then
1509 C_amount_apply_remaining := C_amount_apply_remaining - C_local_amount;
1510 else
1511 C_amount_apply_remaining := C_amount_apply_remaining + C_local_amount;
1512 end if;
1513
1514 end if;
1515
1516 END LOOP;
1517
1518 debug_info := 'Close Schedule Cursor';
1519 CLOSE SCHEDULES;
1520
1521
1522 EXCEPTION
1523 WHEN OTHERS then
1524
1525 if (SQLCODE <> -20001 ) then
1526 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1527 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1528 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1529 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(X_invoice_id)
1530 ||' Prepay_id = '||TO_CHAR(X_prepay_id)
1531 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
1532 ||' Amount_positive = '||X_amount_positive
1533 ||' Amount_apply_remaining = '||TO_CHAR(C_amount_apply_remaining)
1534 ||' C_local_amount = '||TO_CHAR(C_local_amount)
1535 ||' C_local_pay_num = '||TO_CHAR(C_local_pay_num)
1536 ||' Copy_inv_pay_id = '||TO_CHAR(X_copy_inv_pay_id)
1537 ||' Orig_max_dist = '||TO_CHAR(X_orig_max_dist)
1538 ||' User_id = '||TO_CHAR(X_user_id)
1539 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1540 ||' Currency_code = '||X_currency_code
1541 ||' Base_currency = '||X_base_currency
1542 ||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
1543 ||' Base_precision = '||TO_CHAR(X_base_precision)
1544 ||' Min_unit = '||TO_CHAR(X_min_unit)
1545 ||' Precision = '||TO_CHAR(X_precision)
1546 ||' gl_date = '||TO_CHAR(X_gl_date)
1547 ||' Period_name = '||X_period_name);
1551 APP_EXCEPTION.RAISE_EXCEPTION;
1548 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1549 end if;
1550
1552
1553 END appp_update_payment_schedule;
1554
1555
1556
1557
1558 /*===========================================================================
1559 This procedure is used for creating the new payment records on the check.
1560 (Maintain Table AP_INVOICE_PAYMENTS, and AP_PAYMENT_SCHEDULES)
1561
1562 It include 3 steps. In step 3, there are 2 cases:
1563
1564 1. Invoice type is prepayment: insert ap_payment_distribution. It separate
1565 into 3 steps, see below for detail.
1566 2. Invoice type is invoice: use AP_CREATE_PAY_DISTS_PKG.distribution_payment
1567 to create payment distribution line
1568 *==========================================================================*/
1569 PROCEDURE appp_insert_invoice_payment(
1570 X_prepay_id IN NUMBER,
1571 X_new_invoice_id IN NUMBER,
1572 X_amount_apply IN NUMBER,
1573 X_prepay_curr_amount_apply IN NUMBER,
1574 X_payment_cross_rate IN NUMBER,
1575 X_copy_inv_pay_id IN NUMBER,
1576 X_max_inv_pay IN OUT NOCOPY NUMBER,
1577 X_orig_max_dist IN NUMBER,
1578 X_user_id IN NUMBER,
1579 X_currency_code IN VARCHAR2,
1580 X_base_currency IN VARCHAR2,
1581 X_min_unit IN NUMBER,
1582 X_precision IN NUMBER,
1583 X_pay_curr_min_unit IN NUMBER,
1584 X_pay_curr_precision IN NUMBER,
1585 X_base_min_unit IN NUMBER,
1586 X_base_precision IN NUMBER,
1587 X_gl_date IN DATE,
1588 X_period_name IN VARCHAR2,
1589 X_last_update_login IN NUMBER,
1590 X_calling_sequence IN VARCHAR2) IS
1591
1592 debug_info VARCHAR2(100);
1593 current_calling_sequence VARCHAR2(2000);
1594 new_line_num NUMBER;
1595 C_invoice_type VARCHAR2(25);
1596 C_round_amount NUMBER;
1597 C_base_round_amount NUMBER;
1598 C_new_invoice_payment_id NUMBER;
1599 C_check_id NUMBER;
1600 DUMMY VARCHAR2(10);
1601 C_inv_curr_round_amount NUMBER;
1602 C_inv_curr_base_round_amt NUMBER;
1603 -- Bug 3907029
1604 C_payment_type VARCHAR2(25);
1605 l_accounting_event_id NUMBER;
1606
1607 BEGIN
1608 -- Update the calling sequence
1609 --
1610 current_calling_sequence := 'appp_insert_invoice_payment<-'||X_calling_sequence;
1611 --
1612 -- Add 1 to the X_max_inv_pay
1613 --
1614 X_max_inv_pay := X_max_inv_pay + 1;
1615 new_line_num := X_max_inv_pay;
1616
1617 --------------------------------------------------------------
1618 -- Step 1: Get information
1619 --------------------------------------------------------------
1620 --
1621 -- Get check_id for the copy(reference) invoice( or prepayment).
1622 --
1623 debug_info := 'Get check_id';
1624 SELECT check_id
1625 INTO C_check_id
1626 FROM ap_invoice_payments
1627 WHERE invoice_payment_id = X_copy_inv_pay_id;
1628 --
1629 -- Get the invoice_type for X_new_invoice_id (prepayment or invoice)
1630 --
1631 debug_info := 'Get invoice_type';
1632 SELECT invoice_type_lookup_code
1633 INTO C_invoice_type
1634 FROM ap_invoices
1635 WHERE invoice_id = X_new_invoice_id;
1636
1637 -- Bug 3907029. Added this sql statement to get the payment type for the
1638 -- check to pass in to the create events API
1639 --
1640 -- Get the payment_type for c_check_id
1641 --
1642 debug_info := 'Get payment_type';
1643 SELECT payment_type_flag
1644 INTO C_payment_type
1645 FROM ap_checks
1646 WHERE check_id = c_check_id;
1647
1648
1649 -- Bug 3907029. Calling the create events API to create the
1650 -- payment adjustment event.
1651 AP_ACCOUNTING_EVENTS_PKG.Create_Events
1652 ( P_event_type => 'PAYMENT ADJUSTMENT'
1653 ,P_doc_type => c_payment_type
1654 ,P_doc_id => c_check_id
1655 ,P_accounting_date => x_gl_date
1656 ,P_accounting_event_id => l_accounting_event_id
1657 ,P_checkrun_name => NULL
1658 ,P_calling_sequence => current_calling_sequence);
1659
1660 ----------------------------------------------------------------
1661 -- Step 2: Insert into ap_invoice_payments
1662 ----------------------------------------------------------------
1663 debug_info := 'Create the new payment records for ap_invoice_payments';
1664
1665 INSERT INTO AP_INVOICE_PAYMENTS(
1666 invoice_payment_id,
1667 invoice_id,
1668 payment_num,
1669 check_id,
1670 amount,
1671 last_update_date,
1672 last_updated_by,
1673 set_of_books_id,
1674 posted_flag,
1675 accrual_posted_flag,
1676 cash_posted_flag,
1677 electronic_transfer_id,
1678 accts_pay_code_combination_id,
1679 accounting_date,
1680 period_name,
1681 exchange_rate_type,
1682 exchange_rate,
1683 exchange_date,
1684 discount_lost,
1685 invoice_base_amount,
1686 payment_base_amount,
1687 asset_code_combination_id,
1688 gain_code_combination_id,
1689 loss_code_combination_id,
1690 bank_account_num,
1691 bank_num,
1695 last_update_login,
1692 bank_account_type,
1693 future_pay_code_combination_id,
1694 future_pay_posted_flag,
1696 creation_date,
1697 created_by,
1698 invoice_payment_type,
1699 other_invoice_id,
1700 org_id ) /* Bug 3700128. MOAC Project */
1701 SELECT ap_invoice_payments_s.nextval,
1702 X_new_invoice_id,
1703 new_line_num,
1704 P.check_id,
1705 DECODE(P.invoice_id, X_new_invoice_id,
1706 ap_utilities_pkg.ap_round_precision(
1707 (-1) * X_amount_apply, X_pay_curr_min_unit,
1708 X_pay_curr_precision),
1709 ap_utilities_pkg.ap_round_precision(
1710 X_amount_apply, X_pay_curr_min_unit,
1711 X_pay_curr_precision)),
1712 SYSDATE,
1713 X_user_id,
1714 P.set_of_books_id,
1715 'N',
1716 'N',
1717 'N',
1718 P.electronic_transfer_id,
1719 decode(X_new_invoice_id, P.invoice_id,
1720 P.accts_pay_code_combination_id,
1721 I.accts_pay_code_combination_id),
1722 X_gl_date,
1723 X_period_name,
1724 P.exchange_rate_type,
1725 P.exchange_rate,
1726 P.exchange_date,
1727 0,
1728 DECODE(P.invoice_id, X_new_invoice_id,
1729 ap_utilities_pkg.ap_round_precision(
1730 (-1) *
1731 decode(I.invoice_currency_code,
1732 ASP.base_currency_code,
1733 decode(I.payment_currency_code,
1734 ASP.base_currency_code,
1735 I.exchange_rate, 1),
1736 I.exchange_rate)
1737 * X_amount_apply
1738 / X_payment_cross_rate,
1739 X_base_min_unit, X_base_precision),
1740 ap_utilities_pkg.ap_round_precision(
1741 decode(I.invoice_currency_code,
1742 ASP.base_currency_code,
1743 decode(I.payment_currency_code,
1744 ASP.base_currency_code,
1745 I.exchange_rate, 1),
1746 I.exchange_rate) * X_amount_apply
1747 / X_payment_cross_rate,
1748 X_base_min_unit, X_base_precision)),
1749 DECODE(P.invoice_id, X_new_invoice_id,
1750 ap_utilities_pkg.ap_round_precision(
1751 (-1) *
1752 decode(I.payment_currency_code,
1753 ASP.base_currency_code,
1754 decode(I.invoice_currency_code,
1755 ASP.base_currency_code,
1756 P.exchange_rate, 1),
1757 P.exchange_rate)
1758 * X_amount_apply,
1759 X_base_min_unit, X_base_precision),
1760 ap_utilities_pkg.ap_round_precision(
1761 decode(I.payment_currency_code,
1762 ASP.base_currency_code,
1763 decode(I.invoice_currency_code,
1764 ASP.base_currency_code,
1765 P.exchange_rate, 1),
1766 P.exchange_rate) * X_amount_apply,
1767 X_base_min_unit, X_base_precision)),
1768 P.asset_code_combination_id,
1769 P.gain_code_combination_id,
1770 P.loss_code_combination_id,
1771 P.bank_account_num,
1772 P.bank_num,
1773 P.bank_account_type,
1774 P.future_pay_code_combination_id,
1775 'N',
1776 DECODE(X_last_update_login, -999, null, X_last_update_login),
1777 sysdate,
1778 X_user_id,
1779 'PREPAY',
1780 X_prepay_id,
1781 I.org_id /* Bug 3700128. MOAC Project */
1782 FROM ap_invoice_payments P, ap_invoices I, ap_system_parameters ASP
1783 WHERE I.invoice_id = X_new_invoice_id
1784 AND P.invoice_payment_id = X_copy_inv_pay_id
1785 AND ASP.set_of_books_id = I.set_of_books_id;
1786
1787
1788 -------------------------------------------------------------------------
1789 -- Step 3a and 3b:
1790 -- Check if invoice type is prepayment, insert ap_payment_distribution here.
1791 -- Otherwise, use AP_CREATE_PAY_DISTS_PKG.distribution_payment to create
1792 -- payment distribution line.
1793 -------------------------------------------------------------------------
1794 -- Step 3a/3b deleted because payment distributions are obsolete
1795 --
1796
1797 EXCEPTION
1798 WHEN NO_DATA_FOUND then
1799
1800 if (debug_info = 'Check flexbuilt') then
1801 FND_MESSAGE.SET_NAME('SQLAP','AP_PAY_DIST_NOT_FLEXBUILT');
1802 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1803 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1804 FND_MESSAGE.SET_TOKEN('PARAMETERS','Prepay_id = '||TO_CHAR(X_prepay_id)
1805 ||' New_invoice_id = '||TO_CHAR(X_new_invoice_id)
1806 ||' Invoice_type = '||C_invoice_type
1807 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
1808 ||' Copy_inv_pay_id = '||TO_CHAR(X_copy_inv_pay_id)
1809 ||' Max_inv_pay = '||TO_CHAR(X_max_inv_pay)
1810 ||' Orig_max_dist = '||TO_CHAR(X_orig_max_dist)
1811 ||' User_id = '||TO_CHAR(X_user_id)
1812 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1813 ||' Currency_code = '||X_currency_code
1814 ||' Base_currency = '||X_base_currency
1815 ||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
1819 ||' gl_date = '||TO_CHAR(X_gl_date)
1816 ||' Base_precision = '||TO_CHAR(X_base_precision)
1817 ||' Min_unit = '||TO_CHAR(X_min_unit)
1818 ||' Precision = '||TO_CHAR(X_precision)
1820 ||' Period_name = '||X_period_name);
1821 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1822 APP_EXCEPTION.RAISE_EXCEPTION;
1823
1824 end if;
1825
1826 WHEN OTHERS then
1827
1828 if (SQLCODE <> -20001) then
1829 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1830 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1831 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1832 FND_MESSAGE.SET_TOKEN('PARAMETERS','Prepay_id = '||TO_CHAR(X_prepay_id)
1833 ||' New_invoice_id = '||TO_CHAR(X_new_invoice_id)
1834 ||' Invoice_type = '||C_invoice_type
1835 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
1836 ||' Copy_inv_pay_id = '||TO_CHAR(X_copy_inv_pay_id)
1837 ||' Max_inv_pay = '||TO_CHAR(X_max_inv_pay)
1838 ||' Orig_max_dist = '||TO_CHAR(X_orig_max_dist)
1839 ||' User_id = '||TO_CHAR(X_user_id)
1840 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1841 ||' Currency_code = '||X_currency_code
1842 ||' Base_currency = '||X_base_currency
1843 ||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
1844 ||' Base_precision = '||TO_CHAR(X_base_precision)
1845 ||' Min_unit = '||TO_CHAR(X_min_unit)
1846 ||' Precision = '||TO_CHAR(X_precision)
1847 ||' gl_date = '||TO_CHAR(X_gl_date)
1848 ||' Period_name = '||X_period_name);
1849 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1850 end if;
1851
1852 APP_EXCEPTION.RAISE_EXCEPTION;
1853
1854 END appp_insert_invoice_payment;
1855
1856
1857
1858
1859 /*===========================================================================
1860 This precedure use for maintaining AP_INVOICE_PREPAYS
1861 1. Update ap_invoice_prepays if there's a invoice_prepay line exit.
1862 2. Delete record if unapply the prepayment.
1863 3. Insert new line if there's no such record exist
1864 ===========================================================================*/
1865 PROCEDURE appp_insert_invoice_prepay(
1866 X_invoice_id IN NUMBER,
1867 X_prepay_id IN NUMBER,
1868 X_amount_apply IN NUMBER,
1869 X_user_id IN NUMBER,
1870 X_min_unit IN NUMBER,
1871 X_precision IN NUMBER,
1872 X_last_update_login IN NUMBER,
1873 /* Bug 3700128. MOAC Project */
1874 X_org_id IN NUMBER,
1875 X_calling_sequence IN VARCHAR2) IS
1876
1877 debug_info VARCHAR2(100);
1878 current_calling_sequence VARCHAR2(2000);
1879
1880 BEGIN
1881 -- Update the calling sequence
1882 --
1883 current_calling_sequence := 'appp_insert_invoice_prepay<-'||X_calling_sequence;
1884
1885 ------------------------------------------------------------------
1886 -- Update ap_invoice_prepays if there's a invoice_prepay line exit
1887 ------------------------------------------------------------------
1888 debug_info := 'Update ap_invoice_prepays';
1889 UPDATE ap_invoice_prepays
1890 SET prepayment_amount_applied = prepayment_amount_applied +
1891 ap_utilities_pkg.ap_round_precision(
1892 X_amount_apply, X_min_unit, X_precision),
1893 last_update_date = SYSDATE,
1894 last_updated_by = X_user_id,
1895 last_update_login = X_last_update_login
1896 WHERE prepay_id = X_prepay_id
1897 AND invoice_id = X_invoice_id;
1898
1899
1900 ------------------------------------------------------------------
1901 -- Delete record if unapply the prepayment
1902 ------------------------------------------------------------------
1903 debug_info := 'Delete record from ap_invoice_prepays';
1904 if (X_amount_apply < 0)
1905 then /* Same as X_amount_positive = 'N' */
1906 DELETE FROM ap_invoice_prepays
1907 WHERE prepay_id = X_prepay_id
1908 AND invoice_id = X_invoice_id
1909 AND prepayment_amount_applied = 0;
1910
1911 else
1912 ------------------------------------------------------------------
1913 -- Insert new line if there's no record exist
1914 ------------------------------------------------------------------
1915 debug_info := 'Insert record from ap_invoice_prepays';
1916
1917 INSERT INTO ap_invoice_prepays(
1918 prepay_id,
1919 invoice_id,
1920 prepayment_amount_applied,
1921 last_update_date,
1922 last_updated_by,
1923 last_update_login,
1924 creation_date,
1925 created_by,
1926 org_id ) /* Bug 3700128. MOAC Project */
1927 SELECT X_prepay_id,
1928 X_invoice_id,
1929 ap_utilities_pkg.ap_round_precision(
1930 X_amount_apply, X_min_unit, X_precision),
1931 SYSDATE,
1932 X_user_id,
1933 DECODE(X_last_update_login, -999, null, X_last_update_login),
1934 SYSDATE,
1935 X_user_id,
1936 X_org_id /* Bug 3700128. MOAC Project */
1937 FROM SYS.DUAL
1938 WHERE NOT EXISTS (
1939 SELECT 'Already updated existing record'
1940 FROM ap_invoice_prepays
1941 WHERE prepay_id = X_prepay_id
1942 AND invoice_id = X_invoice_id);
1943 end if;
1944
1945 EXCEPTION
1946 WHEN OTHERS then
1947
1948 if (SQLCODE <> -20001) then
1952 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(X_invoice_id)
1949 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1950 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1951 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1953 ||' Prepay_id = '||TO_CHAR(X_prepay_id)
1954 ||' Amount_apply = '||TO_CHAR(X_amount_apply)
1955 ||' User_id = '||TO_CHAR(X_user_id)
1956 ||' Last_update_login = '||TO_CHAR(X_last_update_login)
1957 ||' Min_unit = '||TO_CHAR(X_min_unit)
1958 ||' Precision = '||TO_CHAR(X_precision));
1959 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1960 end if;
1961
1962 APP_EXCEPTION.RAISE_EXCEPTION;
1963
1964 END appp_insert_invoice_prepay;
1965
1966 PROCEDURE app_update_inv_distributions(
1967 X_prepay_id IN NUMBER,
1968 X_amount_apply IN NUMBER,
1969 X_calling_sequence IN VARCHAR2) IS
1970
1971 debug_info VARCHAR2(100);
1972 current_calling_sequence VARCHAR2(2000);
1973 l_prepay_amt_remaining NUMBER;
1974
1975 BEGIN
1976 -- Update the calling sequence
1977 --
1978 current_calling_sequence := 'appp_insert_invoice_prepay<-'||X_calling_sequence;
1979
1980 -------------------------------------------------------------------------------
1981 -- Update the reversal flag
1982 -------------------------------------------------------------------------------
1983
1984 UPDATE ap_invoice_distributions AID
1985 SET reversal_flag = 'Y'
1986 WHERE AID.line_type_lookup_code = 'ITEM'
1987 AND AID.distribution_line_number > 1
1988 AND AID.invoice_id = X_prepay_id;
1989
1990 -------------------------------------------------------------------------------
1991 -- Update the prepay_amount_remaining
1992 -- If the prepay_amount_remaining of the first distribution line is null
1993 -- we need to set it as the sum of amount of all the item type distribution
1994 -- lines. If the prepay_amount_remaining is not null, it means the data has
1995 -- upgraded, if by any chance user did application by using 11i style and did
1996 -- unapplication by using pre-11i style, we need to add the amount we are
1997 -- trying to unapply to the prepay_amount_remaining. Here X_amount_remaining
1998 -- is always negative because of unapplication
1999 -------------------------------------------------------------------------------
2000
2001 SELECT prepay_amount_remaining
2002 INTO l_prepay_amt_remaining
2003 FROM ap_invoice_distributions
2004 WHERE invoice_id = X_prepay_id
2005 AND distribution_line_number = 1
2006 AND line_type_lookup_code = 'ITEM';
2007
2008 IF ( l_prepay_amt_remaining IS null ) THEN
2009 UPDATE ap_invoice_distributions AID
2010 SET prepay_amount_remaining = (
2011 SELECT sum(AID2.amount)
2012 FROM ap_invoice_distributions AID2
2013 WHERE AID.invoice_id = AID2.invoice_id
2014 AND AID2.line_type_lookup_code = 'ITEM')
2015 WHERE invoice_id = X_prepay_id
2016 AND AID.distribution_line_number = 1;
2017 ELSE
2018 UPDATE ap_invoice_distributions
2019 SET prepay_amount_remaining = l_prepay_amt_remaining - X_amount_apply
2020 WHERE invoice_id = X_prepay_id
2021 AND distribution_line_number = 1;
2022 END IF;
2023 EXCEPTION
2024 WHEN OTHERS then
2025
2026 if (SQLCODE <> -20001) then
2027 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2028 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2029 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2030 FND_MESSAGE.SET_TOKEN('PARAMETERS','prepay_id = '||TO_CHAR(X_prepay_id));
2031 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2032 end if;
2033 APP_EXCEPTION.RAISE_EXCEPTION;
2034
2035 END app_update_inv_distributions;
2036
2037
2038 END AP_R11_PREPAY_PKG;