[Home] [Help]
PACKAGE BODY: APPS.AP_INTEREST_INVOICE_PKG
Source
1 PACKAGE BODY AP_INTEREST_INVOICE_PKG AS
2 /*$Header: apintinb.pls 120.40.12020000.4 2012/07/26 15:38:51 shubhgup ship $*/
3
4 -- Declare Local procedures
5
6 PROCEDURE ap_int_inv_get_info(
7 P_invoice_id IN NUMBER,
8 P_interest_amount IN NUMBER,
9 P_exchange_rate IN NUMBER,
10 P_payment_num IN NUMBER,
11 P_currency_code IN VARCHAR2,
12 P_payment_dists_flag IN VARCHAR2,
13 P_payment_mode IN VARCHAR2,
14 P_replace_flag IN VARCHAR2,
15 P_interest_accts_pay_ccid OUT NOCOPY NUMBER,
16 P_asset_account_flag OUT NOCOPY VARCHAR2,
17 P_pay_group_lookup_code OUT NOCOPY VARCHAR2,
18 P_invoice_currency_code OUT NOCOPY VARCHAR2,
19 P_payment_currency_code OUT NOCOPY VARCHAR2,
20 P_immed_terms_id OUT NOCOPY NUMBER,
21 P_terms_id OUT NOCOPY NUMBER,
22 P_terms_date OUT NOCOPY DATE,
23 P_payment_cross_rate OUT NOCOPY NUMBER,
24 P_int_invoice_base_amount OUT NOCOPY NUMBER,
25 P_int_payment_base_amount OUT NOCOPY NUMBER,
26 P_External_Bank_Account_Id OUT NOCOPY NUMBER,
27 P_legal_entity_id OUT NOCOPY NUMBER,
28 P_vendor_id IN NUMBER,
29 P_vendor_site_id IN NUMBER,
30 p_base_currency_code OUT NOCOPY VARCHAR2,
31 p_type_1099 OUT NOCOPY VARCHAR2,
32 p_income_tax_region OUT NOCOPY VARCHAR2,
33 P_calling_sequence IN VARCHAR2,
34 P_party_id OUT NOCOPY NUMBER, --4746599
35 P_party_site_id OUT NOCOPY NUMBER, --4959918
36 P_payment_priority OUT NOCOPY NUMBER); -- Bug 5139574
37
38 PROCEDURE ap_int_inv_insert_ap_invoices(
39 P_int_invoice_id IN NUMBER,
40 P_check_date IN DATE,
41 P_vendor_id IN NUMBER,
42 P_vendor_site_id IN NUMBER,
43 P_old_invoice_num IN VARCHAR2,
44 P_int_invoice_num IN VARCHAR2,
45 P_interest_amount IN NUMBER,
46 P_interest_base_amount IN NUMBER,
47 P_payment_method_code IN VARCHAR2, --4552701
48 P_doc_sequence_value IN NUMBER,
49 P_doc_sequence_id IN NUMBER,
50 P_set_of_books_id IN NUMBER,
51 P_last_updated_by IN NUMBER,
52 P_interest_accts_pay_ccid IN NUMBER,
53 P_pay_group_lookup_code IN VARCHAR2,
54 P_invoice_currency_code IN VARCHAR2,
55 P_payment_currency_code IN VARCHAR2,
56 P_immed_terms_id IN NUMBER,
57 P_terms_id IN NUMBER,
58 P_terms_date IN DATE,
59 P_payment_cross_rate IN NUMBER,
60 P_exchange_rate IN NUMBER,
61 P_exchange_rate_type IN VARCHAR2,
62 P_exchange_date IN DATE,
63 P_payment_dists_flag IN VARCHAR2,
64 P_payment_mode IN VARCHAR2,
65 P_replace_flag IN VARCHAR2,
66 P_invoice_description IN VARCHAR2,
67 P_org_id IN NUMBER,
68 P_last_update_login IN NUMBER,
69 P_calling_sequence IN VARCHAR2,
70 P_legal_entity_id IN NUMBER,
71 P_party_id IN NUMBER, -- 4746599
72 P_party_site_id IN NUMBER/*,
73 P_invoice_id IN NUMBER*/
74 -- commented p_invoice_id as part of bug 8557334
75 ); --8249618
76
77
78 PROCEDURE ap_int_inv_insert_ap_inv_rel(
79 P_invoice_id IN NUMBER,
80 P_int_invoice_id IN NUMBER,
81 P_checkrun_name IN VARCHAR2,
82 P_last_updated_by IN NUMBER,
83 P_payment_num IN NUMBER,
84 P_payment_dists_flag IN VARCHAR2,
85 P_payment_mode IN VARCHAR2,
86 P_replace_flag IN VARCHAR2,
87 P_calling_sequence IN VARCHAR2);
88
89
90 PROCEDURE ap_int_inv_insert_ap_inv_line(
91 P_int_invoice_id IN NUMBER,
92 P_accounting_date IN DATE,
93 P_old_invoice_num IN VARCHAR2,
94 P_interest_amount IN NUMBER,
95 P_interest_base_amount IN NUMBER,
96 P_period_name IN VARCHAR2,
97 P_set_of_books_id IN NUMBER,
98 P_last_updated_by IN NUMBER,
99 P_last_update_login IN NUMBER,
100 P_asset_account_flag IN VARCHAR2,
101 P_Payment_cross_rate IN NUMBER,
102 P_payment_mode IN VARCHAR2,
103 p_type_1099 IN VARCHAR2,
104 p_income_tax_region IN VARCHAR2,
105 p_org_id IN NUMBER,
106 p_calling_sequence IN VARCHAR2);
107
108
109 PROCEDURE ap_int_inv_insert_ap_inv_dist(
110 P_int_invoice_id IN NUMBER,
111 P_accounting_date IN DATE,
112 P_vendor_id IN NUMBER,
113 P_old_invoice_num IN VARCHAR2,
114 P_int_invoice_num IN VARCHAR2,
115 P_interest_amount IN NUMBER,
116 P_interest_base_amount IN NUMBER,
117 P_period_name IN VARCHAR2,
118 P_set_of_books_id IN NUMBER,
119 P_last_updated_by IN NUMBER,
120 P_interest_accts_pay_ccid IN NUMBER,
121 P_asset_account_flag IN VARCHAR2,
122 P_Payment_cross_rate IN Number,
123 P_exchange_rate IN NUMBER,
124 P_exchange_rate_type IN VARCHAR2,
125 P_exchange_date IN DATE,
126 P_payment_dists_flag IN VARCHAR2,
127 P_payment_mode IN VARCHAR2,
128 P_replace_flag IN VARCHAR2,
129 P_invoice_id IN NUMBER,
130 P_calling_sequence IN VARCHAR2,
131 P_invoice_currency_code IN VARCHAR2,
132 P_base_currency_code IN VARCHAR2,
133 P_type_1099 IN VARCHAR2,
134 P_income_tax_region IN VARCHAR2,
135 P_org_id IN NUMBER,
136 P_last_update_login IN NUMBER,
137 p_accounting_event_id IN NUMBER DEFAULT NULL);
138
139
140 PROCEDURE ap_int_inv_insert_ap_pay_sche(
141 P_int_invoice_id IN NUMBER,
142 P_check_date IN DATE,
143 P_interest_amount IN NUMBER,
144 P_payment_method_code IN VARCHAR2, --4552701
145 P_last_updated_by IN NUMBER,
146 P_payment_cross_rate IN NUMBER,
147 P_payment_priority IN NUMBER,
148 P_payment_dists_flag IN VARCHAR2,
149 P_payment_mode IN VARCHAR2,
150 P_replace_flag IN VARCHAR2,
151 P_calling_sequence IN VARCHAR2,
152 P_External_Bank_Account_Id IN NUMBER,
153 P_org_id IN NUMBER,
154 P_last_update_login IN NUMBER);
155
156
157
158 /*========================================================================
159 * Main Procedure: Create Interest Invoice and pay it
160 * Step 1. Call ap_int_inv_get_info to get some required fields
161 * Step 2. Create interest ap_invoices line
162 * Step 3. Create ap_invoice_relationships line
163 * Step 4. Create ap_invoice_lines line
164 * Step 5. Create ap_invoice_distributions line
165 * Step 6. Create ap_payment_schedules line
166 * Step 7. Create ap_invoice_payemnts line (Call ap_pay_invoice_pkg.ap_pay_
167 insert_invoice_payments)
168
169 +=============================================================================+
170 | Step | Description | Work for* |
171 +==========+====================================================+=============+
172 | Step 1: | Call ap_int_inv_get_info to get some parameters | PAY |
173 | |
174 +----------+----------------------------------------------------+-------------+
175 | Step 2: | Call ap_int_inv_insert_ap_invoices | PAY |
176 | |
177 +----------+----------------------------------------------------+-------------+
178 | Step 3: | Call ap_int_inv_insert_ap_inv_rel | PAY |
179 | |
180 +----------+----------------------------------------------------+-------------+
181 | Step 4: | Call ap_int_inv_insert_ap_inv_line | PAY |
182 | |
183 +----------+----------------------------------------------------+-------------+
184 | Step 5: | Call ap_int_inv_insert_ap_inv_dist | PAY |
185 | |
186 +----------+----------------------------------------------------+-------------+
187 | Step 6: | Call ap_int_inv_insert_ap_pay_sche | PAY |
188 | |
189 +----------+----------------------------------------------------+-------------+
190 | Step 7: | Call AP_PAY_INVOICE_PKG.ap_pay_insert_invoice | |
191 | | _payments : Insert AP_INVOICE_PAYMENTS | PAY |
192 +----------+----------------------------------------------------+-------------+
193
194 *========================================================================*/
195
196
197 PROCEDURE ap_create_interest_invoice(
198 P_invoice_id IN NUMBER,
199 P_int_invoice_id IN NUMBER,
200 P_check_id IN NUMBER,
201 P_payment_num IN NUMBER,
202 P_int_invoice_payment_id IN NUMBER,
203 P_old_invoice_payment_id IN NUMBER Default NULL,
204 P_period_name IN VARCHAR2,
205 P_invoice_type IN VARCHAR2 Default NULL,
206 P_accounting_date IN DATE,
207 P_amount IN NUMBER,
208 P_discount_taken IN NUMBER,
209 P_discount_lost IN NUMBER Default NULL,
210 P_invoice_base_amount IN NUMBER Default NULL,
211 P_payment_base_amount IN NUMBER Default NULL,
212 P_vendor_id IN NUMBER,
213 P_vendor_site_id IN NUMBER Default NULL,
214 P_old_invoice_num IN VARCHAR2,
215 P_int_invoice_num IN VARCHAR2,
216 P_interest_amount IN NUMBER,
217 P_payment_method_code IN VARCHAR2 Default NULL, --4552701
218 P_doc_sequence_value IN NUMBER Default NULL,
219 P_doc_sequence_id IN NUMBER Default NULL,
220 P_checkrun_name IN VARCHAR2 Default NULL,
221 P_payment_priority IN VARCHAR2 Default NULL,
222 P_accrual_posted_flag IN VARCHAR2,
223 P_cash_posted_flag IN VARCHAR2,
224 P_posted_flag IN VARCHAR2,
225 P_set_of_books_id IN NUMBER,
226 P_last_updated_by IN NUMBER,
227 P_last_update_login IN NUMBER Default NULL,
228 P_currency_code IN VARCHAR2 Default NULL,
229 P_base_currency_code IN VARCHAR2 Default NULL,
230 P_exchange_rate IN NUMBER Default NULL,
231 P_exchange_rate_type IN VARCHAR2 Default NULL,
232 P_exchange_date IN DATE Default NULL,
233 P_bank_account_id IN NUMBER Default NULL,
234 P_bank_account_num IN VARCHAR2 Default NULL,
235 P_bank_account_type IN VARCHAR2 Default NULL,
236 P_bank_num IN VARCHAR2 Default NULL,
237 P_future_pay_posted_flag IN VARCHAR2 Default NULL,
238 P_exclusive_payment_flag IN VARCHAR2 Default NULL,
239 P_accts_pay_ccid IN NUMBER Default NULL,
240 P_gain_ccid IN NUMBER Default NULL,
241 P_loss_ccid IN NUMBER Default NULL,
242 P_future_pay_ccid IN NUMBER Default NULL,
243 P_asset_ccid IN NUMBER Default NULL,
244 P_payment_dists_flag IN VARCHAR2 Default NULL,
245 P_payment_mode IN VARCHAR2 Default NULL,
246 P_replace_flag IN VARCHAR2 Default NULL,
247 P_invoice_description IN VARCHAR2 Default NULL,
248 P_attribute1 IN VARCHAR2 Default NULL,
249 P_attribute2 IN VARCHAR2 Default NULL,
250 P_attribute3 IN VARCHAR2 Default NULL,
251 P_attribute4 IN VARCHAR2 Default NULL,
252 P_attribute5 IN VARCHAR2 Default NULL,
253 P_attribute6 IN VARCHAR2 Default NULL,
254 P_attribute7 IN VARCHAR2 Default NULL,
255 P_attribute8 IN VARCHAR2 Default NULL,
256 P_attribute9 IN VARCHAR2 Default NULL,
257 P_attribute10 IN VARCHAR2 Default NULL,
258 P_attribute11 IN VARCHAR2 Default NULL,
259 P_attribute12 IN VARCHAR2 Default NULL,
260 P_attribute13 IN VARCHAR2 Default NULL,
261 P_attribute14 IN VARCHAR2 Default NULL,
262 P_attribute15 IN VARCHAR2 Default NULL,
263 P_attribute_category IN VARCHAR2 Default NULL,
264 P_calling_sequence IN VARCHAR2 Default NULL,
265 P_accounting_event_id IN NUMBER Default NULL,
266 P_org_id IN NUMBER Default NULL)
267 IS
268
269 current_calling_sequence VARCHAR2(2000);
270 debug_info VARCHAR2(100);
271 C_int_cc_id NUMBER;
272 C_interest_accts_pay_ccid NUMBER;
273 C_asset_account_flag VARCHAR2(1);
274 C_pay_group_lookup_code VARCHAR2(25);
275 C_invoice_currency_code VARCHAR2(15);
276 C_payment_currency_code VARCHAR2(15);
277 C_immed_terms_id NUMBER;
278 C_terms_id NUMBER;
279 C_terms_date DATE;
280 C_payment_cross_rate NUMBER;
281 C_int_invoice_base_amount NUMBER;
282 C_int_payment_base_amount NUMBER;
283 C_External_Bank_Account_Id NUMBER;
284 C_interest_base_amount NUMBER;
285 C_Legal_entity_id NUMBER;
286 c_party_id number; --4746599
287 c_party_site_id Number; --4959918
288 C_payment_priority NUMBER; -- Bug 5139574
289 l_base_currency_code VARCHAR2(15); /* Bug 4742671 */
290 l_type_1099 VARCHAR2(10);
291 l_income_tax_region VARCHAR2(150);
292
293 BEGIN
294
295 current_calling_sequence :=
296 'AP_INTEREST_INVOICE_PKG.ap_create_interest_invoice<-'||P_calling_sequence;
297 --------------------------------------------
298 -- Step 0: Return if intertest amount is 0
299 --------------------------------------------
300
301 IF (P_interest_amount = 0) THEN
302 RETURN;
303 END IF;
304
305 ---------------------------------------------------------------------------
306 -- Step 1: Case for All : for both Pay and reverse:
307 -- Call ap_int_inv_get_info to get some parameters
308 ---------------------------------------------------------------------------
309 AP_INTEREST_INVOICE_PKG.ap_int_inv_get_info(
310 P_invoice_id,
311 P_interest_amount,
312 P_exchange_rate,
313 P_payment_num,
314 P_currency_code,
315 P_payment_dists_flag,
316 P_payment_mode,
317 P_replace_flag,
318 C_interest_accts_pay_ccid,
319 C_asset_account_flag,
320 C_pay_group_lookup_code,
321 C_invoice_currency_code,
322 C_payment_currency_code,
323 C_immed_terms_id,
324 C_terms_id,
325 C_terms_date,
326 C_payment_cross_rate,
327 C_int_invoice_base_amount,
328 C_int_payment_base_amount,
329 C_External_Bank_Account_Id,
330 C_Legal_entity_id,
331 P_vendor_id,
332 P_vendor_site_id,
333 l_base_currency_code,
334 l_type_1099,
335 l_income_tax_region,
336 Current_calling_sequence,
337 c_party_id, --4746599
338 c_party_site_id,
339 C_payment_priority); --4959918
340 ---------------------------------------------------------------------------
341 -- Step 2: Case for Pay : for Pay interest invoice only
342 -- Call ap_int_inv_insert_ap_invoices: Insert AP_INVOICES
343 ---------------------------------------------------------------------------
344
345 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_invoices(
346 P_int_invoice_id,
347 P_accounting_date,
348 P_vendor_id,
349 P_vendor_site_id,
350 P_old_invoice_num,
351 P_int_invoice_num,
352 P_interest_amount,
353 C_int_payment_base_amount,
354 P_payment_method_code, --4552701
355 P_doc_sequence_value,
356 P_doc_sequence_id,
357 P_set_of_books_id,
358 P_last_updated_by,
359 C_interest_accts_pay_ccid,
360 C_pay_group_lookup_code,
361 C_invoice_currency_code,
362 C_payment_currency_code,
363 C_immed_terms_id,
364 C_terms_id,
365 C_terms_date,
366 C_payment_cross_rate,
367 P_exchange_rate,
368 P_exchange_rate_type,
369 P_exchange_date,
370 P_payment_dists_flag,
371 P_payment_mode,
372 P_replace_flag,
373 P_invoice_description,
374 P_org_id,
375 P_last_update_login,
376 Current_calling_sequence,
377 C_Legal_entity_id,
378 c_party_id, --4746599
379 c_party_site_id/*, -- 4959918
380 P_invoice_id*/
381 -- commented p_invoice_id as part of bug 8557334
382 ); --8249618
383 ---------------------------------------------------------------------------
384 -- Step 3: Case for Pay : for Pay interest invoice only
385 -- Call ap_int_inv_insert_ap_inv_rel : Insert AP_INVOICE_RELATIONSHIPS
386 ---------------------------------------------------------------------------
387
388 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_inv_rel(
389 P_invoice_id,
390 P_int_invoice_id,
391 P_checkrun_name,
392 P_last_updated_by,
393 P_payment_num,
394 P_payment_dists_flag,
395 P_payment_mode,
396 P_replace_flag,
397 Current_calling_sequence);
398 ---------------------------------------------------------------------------
399 -- Step 4: Case for Pay : for Pay interest invoice only
400 -- Call ap_int_inv_insert_ap_inv_line: Insert AP_INVOICE_LINES
401 ---------------------------------------------------------------------------
402
403 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_inv_line(
404 P_int_invoice_id,
405 P_accounting_date,
406 P_old_invoice_num,
407 P_interest_amount,
408 C_int_payment_base_amount,
409 P_period_name ,
410 P_set_of_books_id,
411 P_last_updated_by ,
412 P_last_update_login ,
413 C_asset_account_flag,
414 C_Payment_cross_rate,
415 P_payment_mode,
416 l_type_1099,
417 l_income_tax_region,
418 P_org_id,
419 P_calling_sequence);
420
421 ---------------------------------------------------------------------------
422 -- Step 5: Case for Pay : for Pay interest invoice only
423 -- Call ap_int_inv_insert_ap_inv_dist: Insert AP_INVOICE_DISTRIBUTIONS
424 ---------------------------------------------------------------------------
425
426 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_inv_dist(
427 P_int_invoice_id,
428 P_accounting_date,
429 P_vendor_id,
430 P_old_invoice_num,
431 P_int_invoice_num,
432 P_interest_amount,
433 C_int_payment_base_amount,
434 P_period_name,
435 P_set_of_books_id,
436 P_last_updated_by,
437 C_interest_accts_pay_ccid,
438 C_asset_account_flag,
439 C_payment_cross_rate,
440 P_exchange_rate,
441 P_exchange_rate_type,
442 P_exchange_date,
443 P_payment_dists_flag,
444 P_payment_mode,
445 P_replace_flag,
446 P_invoice_id,
447 current_calling_sequence,
448 C_invoice_currency_code,
449 l_base_currency_code,
450 l_type_1099,
451 l_income_tax_region,
452 P_org_id,
453 P_last_update_login,
454 p_accounting_event_id);
455 ---------------------------------------------------------------------------
456 -- Step 6: Case for Pay : for Pay interest invoice only
457 -- Call ap_int_inv_insert_ap_pay_sche : Insert AP_PAYMENT_SCHEDULES
458 ---------------------------------------------------------------------------
459
460 -- Bug 5139574
461 if P_payment_priority is not null then
462 C_payment_priority := P_payment_priority;
463 end if;
464
465 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_pay_sche(
466 P_int_invoice_id,
467 P_accounting_date,
468 P_interest_amount,
469 P_payment_method_code, --4552701
470 P_last_updated_by,
471 C_payment_cross_rate,
472 C_payment_priority,
473 P_payment_dists_flag,
474 P_payment_mode,
475 P_replace_flag,
476 Current_calling_sequence,
477 C_External_Bank_Account_Id,
478 P_org_id,
479 P_last_update_login);
480
481 ---------------------------------------------------------------------------
482 -- Step 7: Case for ALL : for Pay and rev interest invoice
483 -- Call AP_PAY_INVOICE_PKG.ap_pay_insert_invoice_payments :
484 -- Insert AP_INVOICE_PAYMENTS
485 ---------------------------------------------------------------------------
486
487 ap_pay_invoice_pkg.ap_pay_insert_invoice_payments(
488 P_int_invoice_id,
489 P_check_id,
490 1,
491 P_int_invoice_payment_id,
492 P_old_invoice_payment_id,
493 P_period_name,
494 P_accounting_date,
495 P_interest_amount,
496 0,
497 0,
498 C_int_invoice_base_amount,
499 C_int_payment_base_amount,
500 'N',
501 'N',
502 'N',
503 P_set_of_books_id,
504 P_last_updated_by,
505 P_last_update_login,
506 sysdate,
507 P_currency_code,
508 P_base_currency_code,
509 P_exchange_rate,
510 P_exchange_rate_type,
511 P_exchange_date,
512 P_bank_account_id,
513 P_bank_account_num,
514 P_bank_account_type,
515 P_bank_num,
516 '',
517 '',
518 C_interest_accts_pay_ccid,
519 P_gain_ccid,
520 P_loss_ccid,
521 P_future_pay_ccid,
522 '',
523 P_payment_dists_flag,
524 P_payment_mode,
525 P_replace_flag,
526 P_attribute1,
527 P_attribute2,
528 P_attribute3,
529 P_attribute4,
530 P_attribute5,
531 P_attribute6,
532 P_attribute7,
533 P_attribute8,
534 P_attribute9,
535 P_attribute10,
536 P_attribute11,
537 P_attribute12,
538 P_attribute13,
539 P_attribute14,
540 P_attribute15,
541 P_attribute_category,
542 '',
543 '',
544 '',
545 '',
546 '',
547 '',
548 '',
549 '',
550 '',
551 '',
552 '',
553 '',
554 '',
555 '',
556 '',
557 '',
558 '',
559 '',
560 '',
561 '',
562 '',
563 Current_calling_sequence,
564 P_accounting_event_id,
565 P_org_id);
566
567 END ap_create_interest_invoice;
568
569
570
571 /*==========================================================================
572 This procedure is called to retrieve the various required information.
573 *==========================================================================*/
574
575 PROCEDURE ap_int_inv_get_info(
576 P_invoice_id IN NUMBER,
577 P_interest_amount IN NUMBER,
578 P_exchange_rate IN NUMBER,
579 P_payment_num IN NUMBER,
580 P_currency_code IN VARCHAR2,
581 P_payment_dists_flag IN VARCHAR2,
582 P_payment_mode IN VARCHAR2,
583 P_replace_flag IN VARCHAR2,
584 P_interest_accts_pay_ccid OUT NOCOPY NUMBER,
585 P_asset_account_flag OUT NOCOPY VARCHAR2,
586 P_pay_group_lookup_code OUT NOCOPY VARCHAR2,
587 P_invoice_currency_code OUT NOCOPY VARCHAR2,
588 P_payment_currency_code OUT NOCOPY VARCHAR2,
589 P_immed_terms_id OUT NOCOPY NUMBER,
590 P_terms_id OUT NOCOPY NUMBER,
591 P_terms_date OUT NOCOPY DATE,
592 P_payment_cross_rate OUT NOCOPY NUMBER,
593 P_int_invoice_base_amount OUT NOCOPY NUMBER,
594 P_int_payment_base_amount OUT NOCOPY NUMBER,
595 P_External_Bank_Account_Id OUT NOCOPY NUMBER,
596 P_Legal_entity_id OUT NOCOPY NUMBER,
597 P_vendor_id IN NUMBER,
598 P_vendor_site_id IN NUMBER,
599 p_base_currency_code OUT NOCOPY VARCHAR2,
600 p_type_1099 OUT NOCOPY VARCHAR2,
601 p_income_tax_region OUT NOCOPY VARCHAR2,
602 P_calling_sequence IN VARCHAR2,
603 P_party_id OUT NOCOPY NUMBER, --4746599
604 P_party_site_id OUT NOCOPY NUMBER, -- 4959918
605 P_payment_priority OUT NOCOPY NUMBER) -- 5139574
606 IS
607 debug_info VARCHAR2(100);
608 current_calling_sequence VARCHAR2(2000);
609 int_invoice_base_amount NUMBER;
610 int_payment_base_amount NUMBER;
611
612 BEGIN
613
614 current_calling_sequence := 'ap_int_inv_get_info<-'||P_calling_sequence;
615
616 ----------------------------------------------------------------------------
617 -- get some required ccid from ap_system_parameters and gl_code_combinations
618 ----------------------------------------------------------------------------
619
620 -- Interest Invoices project - Invoice Lines - 11ix
621 -- Add the parameters
622 -- P_vendor_id
623 -- P_vendor_site_id
624 -- P_base_currency_code
625 -- P_type_1099
626 -- P_income_tax_region
627 -- Merge the existing SELECTs into two. One SELECT from ap_system_parameters.
628 -- The other from ap_invoices. SELECT type_1099, income_tax_region,
629 -- and base currency and pass them back to ap_create_interest_invoice_pkg.
630
631 -- Remove expense interest account. Not need to select it
632
633 debug_info := 'get some required ccid';
634
635 SELECT asp.interest_accts_pay_ccid,
636 DECODE(glcc.account_type,'A','Y','N'),
637 asp.base_currency_code,
638 pv.type_1099,
639 DECODE(pv.type_1099,
640 NULL, NULL,
641 DECODE(asp.combined_filing_flag,
642 'N', NULL,
643 DECODE(asp.income_tax_region_flag,
644 'Y', pvs.state,
645 asp.income_tax_region)))
646 INTO P_interest_accts_pay_ccid,
647 P_asset_account_flag,
648 P_base_currency_code,
649 P_type_1099,
650 P_income_tax_region
651 FROM ap_system_parameters asp,
652 gl_code_combinations glcc,
653 po_vendors pv,
654 po_vendor_sites pvs
655 WHERE glcc.code_combination_id = asp.interest_code_combination_id
656 AND pv.vendor_id = P_vendor_id
657 AND pvs.vendor_site_id = P_vendor_site_id
658 AND NVL(pvs.org_id, -999) = NVL(asp.org_id, -999);
659
660 ----------------------------------------------------------------------------
661 -- get some required information from ap_invoices
662 ----------------------------------------------------------------------------
663
664 debug_info := 'get some required field from ap_invoices';
665
666 SELECT ai.pay_group_lookup_code,
667 ai.invoice_currency_code,
668 ai.payment_currency_code,
669 ai.terms_id, ai.terms_date,
670 /* bug 5000194 */
671 (AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id /* External Bank Uptake */
672 ( ai.vendor_id,
673 ai.vendor_site_id,
674 ai.payment_function,
675 ai.org_id,
676 P_currency_code,
677 'Interest Invoice')),
678 (P_interest_amount / ps.payment_cross_rate
679 * nvl(ai.exchange_rate,1)),
680 (P_interest_amount / ps.payment_cross_rate
681 * nvl(P_exchange_rate,1)),
682 nvl(ps.payment_cross_rate,1),
683 ai.legal_entity_id,
684 ai.party_id,
685 ai.party_site_id, -- bug 4959918
686 ps.payment_priority -- Bug 5139574
687 INTO P_pay_group_lookup_code,
688 P_invoice_currency_code,
689 P_payment_currency_code,
690 P_terms_id,
691 P_terms_date,
692 P_External_Bank_Account_Id,
693 int_invoice_base_amount,
694 int_payment_base_amount,
695 P_payment_cross_rate,
696 P_Legal_Entity_ID,
697 P_party_id,
698 P_party_site_id,
699 P_payment_priority
700 FROM ap_invoices ai,
701 ap_payment_schedules ps
702 WHERE ai.invoice_id = P_invoice_id
703 AND ps.invoice_id = P_invoice_id
704 AND ps.payment_num = P_payment_num;
705
706 ----------------------------------------------------------------------------
707 -- Round base_amount
708 ----------------------------------------------------------------------------
709
710 debug_info := 'Round the P_int_invoice_base_amount';
711 P_int_invoice_base_amount := ap_utilities_pkg.ap_round_currency(
712 int_invoice_base_amount, P_currency_code);
713
714 P_int_payment_base_amount := ap_utilities_pkg.ap_round_currency(
715 int_payment_base_amount, P_currency_code);
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 IF (SQLCODE <> -20001 ) THEN
720 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
721 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
722 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
723 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
724 ||', Payment_num = '||TO_CHAR(P_payment_num)
725 ||', Interest Amount = '||TO_CHAR(P_interest_amount)
726 ||', Exchange Rate = '||TO_CHAR(P_exchange_rate)
727 ||', Currency_code = '||P_currency_code);
728 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
729 END IF;
730
731 APP_EXCEPTION.RAISE_EXCEPTION;
732
733 END ap_int_inv_get_info;
734
735
736 /*==========================================================================
737 Insert AP_INVOICES
738 *==========================================================================*/
739
740 PROCEDURE ap_int_inv_insert_ap_invoices(
741 P_int_invoice_id IN NUMBER,
742 P_check_date IN DATE,
743 P_vendor_id IN NUMBER,
744 P_vendor_site_id IN NUMBER,
745 P_old_invoice_num IN VARCHAR2,
746 P_int_invoice_num IN VARCHAR2,
747 P_interest_amount IN NUMBER,
748 P_interest_base_amount IN NUMBER,
749 P_payment_method_code IN VARCHAR2, --4552701
750 P_doc_sequence_value IN NUMBER,
751 P_doc_sequence_id IN NUMBER,
752 P_set_of_books_id IN NUMBER,
753 P_last_updated_by IN NUMBER,
754 P_interest_accts_pay_ccid IN NUMBER,
755 P_pay_group_lookup_code IN VARCHAR2,
756 P_invoice_currency_code IN VARCHAR2,
757 P_payment_currency_code IN VARCHAR2,
758 P_immed_terms_id IN NUMBER,
759 P_terms_id IN NUMBER,
760 P_terms_date IN DATE,
761 P_payment_cross_rate IN NUMBER,
762 P_exchange_rate IN NUMBER,
763 P_exchange_rate_type IN VARCHAR2,
764 P_exchange_date IN DATE,
765 P_payment_dists_flag IN VARCHAR2,
766 P_payment_mode IN VARCHAR2,
767 P_replace_flag IN VARCHAR2,
768 P_invoice_description IN VARCHAR2,
769 P_org_id IN NUMBER,
770 P_last_update_login IN NUMBER,
771 P_calling_sequence IN VARCHAR2,
772 P_Legal_Entity_ID IN NUMBER,
773 P_party_id IN NUMBER, --4746599
774 P_party_site_id IN NUMBER/*, --4959918
775 P_Invoice_id IN NUMBER */
776 -- commented p_invoice_id as part of bug 8557334
777 ) --8249618
778 IS
779 debug_info VARCHAR2(100);
780 current_calling_sequence VARCHAR2(2000);
781
782 --Start of 8249618
783 l_remit_party_id AP_INVOICES_ALL.party_id%TYPE; -- bug 8557334
784 l_remit_to_supplier_name AP_INVOICES.remit_to_supplier_name%TYPE;
785 l_remit_to_supplier_id AP_INVOICES.remit_to_supplier_id%TYPE;
786 l_remit_to_supplier_site AP_INVOICES.remit_to_supplier_site%TYPE;
787 l_remit_to_supplier_site_id AP_INVOICES.remit_to_supplier_site_id%TYPE;
788 l_relationship_id AP_INVOICES.relationship_id%TYPE;
789 --End of 8249618
790
791 BEGIN
792
793 -- Interest Invoices project - Invoice Lines. - 11ix
794 -- Add these parameters to signature and to INSERT statement
795 -- P_org_id
796 -- P_last_update_login
797 -- Pass C_int_payment_base_amount for P_interest_base_amount
798 -- instead of recalculating it.
799
800 current_calling_sequence := 'ap_int_inv_insert_ap_invoices<-'||
801 P_calling_sequence;
802
803 IF (P_payment_mode = 'PAY') THEN
804
805 --Introduced below select statement for 8249618.
806 -- modified code as per bug 8557334 starts
807
808 /*SELECT remit_to_supplier_name, remit_to_supplier_id,
809 remit_to_supplier_site, remit_to_supplier_site_id,
810 relationship_id
811 INTO l_remit_to_supplier_name, l_remit_to_supplier_id,
812 l_remit_to_supplier_site, l_remit_to_supplier_site_id,
813 l_relationship_id
814 FROM AP_INVOICES_ALL
815 WHERE INVOICE_ID = P_Invoice_id;*/
816
817 IBY_EXT_PAYEE_RELSHIPS_PKG.default_Ext_Payee_Relationship (
818 p_party_id => p_party_id,
819 p_supplier_site_id => p_vendor_site_id,
820 p_date => p_check_date,
821 x_remit_party_id => l_remit_party_id,
822 x_remit_supplier_site_id => l_remit_to_supplier_site_id,
823 x_relationship_id => l_relationship_id
824 );
825
826 BEGIN
827 IF (l_relationship_id <> -1) THEN
828 SELECT vendor_name, vendor_id
829 INTO l_remit_to_supplier_name, l_remit_to_supplier_id
830 FROM ap_suppliers
831 WHERE party_id = l_remit_party_id;
832
833 SELECT vendor_site_code
834 into l_remit_to_supplier_site
835 from ap_supplier_sites_all
836 where vendor_site_id = l_remit_to_supplier_site_id;
837 ELSE
838 l_remit_to_supplier_name := null;
839 l_remit_to_supplier_id := null;
840 l_remit_to_supplier_site := null;
841 l_remit_to_supplier_site_id := null;
842 l_relationship_id := null;
843 END IF;
844 EXCEPTION
845 WHEN OTHERS THEN
846 NULL;
847 END;
848
849 -- modified code as per bug 8557344 ends
850
851 debug_info := 'Insert into ap_invoices';
852
853 INSERT INTO AP_INVOICES(
854 INVOICE_ID,
855 LAST_UPDATE_DATE,
856 LAST_UPDATED_BY,
857 VENDOR_ID,
858 INVOICE_NUM,
859 INVOICE_AMOUNT,
860 BASE_AMOUNT,
861 PAY_CURR_INVOICE_AMOUNT,
862 VENDOR_SITE_ID,
863 AMOUNT_PAID,
864 DISCOUNT_AMOUNT_TAKEN,
865 INVOICE_DATE,
866 INVOICE_TYPE_LOOKUP_CODE,
867 DESCRIPTION,
868 AMOUNT_APPLICABLE_TO_DISCOUNT,
869 TAX_AMOUNT,
870 TERMS_ID,
871 TERMS_DATE,
872 PAY_GROUP_LOOKUP_CODE,
873 SET_OF_BOOKS_ID,
874 ACCTS_PAY_CODE_COMBINATION_ID,
875 INVOICE_CURRENCY_CODE,
876 PAYMENT_CURRENCY_CODE,
877 PAYMENT_CROSS_RATE_TYPE,
878 PAYMENT_CROSS_RATE_DATE,
879 PAYMENT_STATUS_FLAG,
880 POSTING_STATUS,
881 CREATION_DATE,
882 CREATED_BY,
883 PAYMENT_CROSS_RATE,
884 EXCHANGE_RATE,
885 EXCHANGE_RATE_TYPE,
886 EXCHANGE_DATE,
887 SOURCE,
888 PAYMENT_METHOD_CODE, --4552701
889 DOC_CATEGORY_CODE,
890 DOC_SEQUENCE_VALUE,
891 DOC_SEQUENCE_ID,
892 GL_DATE,
893 WFAPPROVAL_STATUS,
894 APPROVAL_READY_FLAG,
895 ORG_ID,
896 LAST_UPDATE_LOGIN,
897 Legal_Entity_ID,
898 AUTO_TAX_CALC_FLAG, -- BUG 3007085
899 PARTY_ID, --4746599
900 PARTY_SITE_ID, --4959918
901 --Start 8249618
902 remit_to_supplier_name,
903 remit_to_supplier_id,
904 remit_to_supplier_site,
905 remit_to_supplier_site_id,
906 relationship_id
907 -- End 8249618
908 )
909 VALUES (
910 P_int_invoice_id,
911 sysdate,
912 P_last_updated_by,
913 P_vendor_id,
914 P_int_invoice_num,
915 P_interest_amount / P_payment_cross_rate,
916 P_interest_base_amount,
917 P_interest_amount,
918 P_vendor_site_id,
919 P_interest_amount,
920 0,
921 P_check_date,
922 'INTEREST',
923 NVL(P_invoice_description, 'Interest : Overdue Invoice ' ||
924 P_old_invoice_num),
925 0,
926 0,
927 nvl(P_immed_terms_id, P_terms_id),
928 P_terms_date,
929 P_pay_group_lookup_code,
930 P_set_of_books_id,
931 P_interest_accts_pay_ccid,
932 P_invoice_currency_code,
933 P_payment_currency_code,
934 decode(P_payment_cross_rate, 1, NULL, 'EMU FIXED'),
935 P_check_date,
936 'Y',
937 'N',
938 sysdate,
939 P_last_updated_by,
940 nvl(P_payment_cross_rate,1),
941 nvl(P_exchange_rate,1),
942 P_exchange_rate_type,
943 P_exchange_date,
944 'QuickCheck',
945 P_payment_method_code, --4552701
946 'INT INV',
947 P_doc_sequence_value,
948 P_doc_sequence_id,
949 P_check_date,
950 'NOT REQUIRED',
951 'Y',
952 P_org_id,
953 P_last_update_login,
954 P_legal_entity_id,
955 'N', -- BUG 3007085
956 P_party_id, --4746599
957 P_party_site_id, -- 4959918
958 --Start 8249618
959 l_remit_to_supplier_name,
960 l_remit_to_supplier_id,
961 l_remit_to_supplier_site,
962 l_remit_to_supplier_site_id,
963 l_relationship_id
964 --End 8249618
965 );
966 AP_DBI_PKG.Maintain_DBI_Summary
967 (p_table_name => 'AP_INVOICES',
968 p_operation => 'I',
969 p_key_value1 => P_Int_Invoice_id,
970 p_calling_sequence => current_calling_sequence);
971
972 END IF;
973
974 EXCEPTION
975 WHEN OTHERS THEN
976
977 IF (SQLCODE <> -20001 ) THEN
978 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
979 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
980 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
981 FND_MESSAGE.SET_TOKEN('PARAMETERS',
982 ' Int_invoice_id = '||TO_CHAR(P_int_invoice_id)
983 ||', Check_date = '||TO_CHAR(P_check_date)
984 ||', Vendor_id = '||TO_CHAR(P_vendor_id)
985 ||', Vendor_site_id = '||TO_CHAR(P_vendor_site_id)
986 ||', Old_invoice_num = '||P_old_invoice_num
987 ||', Int_invoice_num = '||P_int_invoice_num
988 ||', Interest Amount = '||TO_CHAR(P_interest_amount)
989 ||', Payment_method_code = '||
990 P_payment_method_code
991 ||', Doc_sequence_value = '||TO_CHAR(P_doc_sequence_value)
992 ||', Doc_sequence_id = '||TO_CHAR(P_doc_sequence_id)
993 ||', Interest_accts_pay_ccid = '||
994 TO_CHAR(P_interest_accts_pay_ccid)
995 ||', Pay_group_lookup_code = '||P_pay_group_lookup_code
996 ||', Invoice_currency_code = '||P_invoice_currency_code
997 ||', Payment_currency_code = '||P_payment_currency_code
998 ||', Immed_terms_id = '||TO_CHAR(P_terms_id)
999 ||', Terms_id = '||TO_CHAR(P_terms_id)
1000 ||', Payment_cross_rate = '||TO_CHAR(P_payment_cross_rate)
1001 ||', Exchange Rate = '||TO_CHAR(P_exchange_rate)
1002 ||', Exchange Rate Type = '||P_exchange_rate_type
1003 ||', Exchange Date = '||TO_CHAR(P_exchange_date)
1004 ||', Set 0f books id = '||TO_CHAR(P_set_of_books_id)
1005 ||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
1006 ||', payment_dists_flag = '||P_payment_dists_flag
1007 ||', payment_mode = '||P_payment_mode
1008 ||', replace_flag = '||P_replace_flag);
1009
1010 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1011 END IF;
1012
1013 APP_EXCEPTION.RAISE_EXCEPTION;
1014
1015 END ap_int_inv_insert_ap_invoices;
1016
1017
1018 /*==========================================================================
1019 Insert AP_INVOICE_RELATIONSHIPS
1020 *=====================================================================*/
1021
1022 PROCEDURE ap_int_inv_insert_ap_inv_rel(
1023 P_invoice_id IN NUMBER,
1024 P_int_invoice_id IN NUMBER,
1025 P_checkrun_name IN VARCHAR2,
1026 P_last_updated_by IN NUMBER,
1027 P_payment_num IN NUMBER,
1028 P_payment_dists_flag IN VARCHAR2,
1029 P_payment_mode IN VARCHAR2,
1030 P_replace_flag IN VARCHAR2,
1031 P_calling_sequence IN VARCHAR2) IS
1032
1033 debug_info VARCHAR2(100);
1034 current_calling_sequence VARCHAR2(2000);
1035
1036 BEGIN
1037
1038 current_calling_sequence := 'ap_int_inv_insert_ap_inv_rel<-'||
1039 P_calling_sequence;
1040
1041 IF (P_payment_mode = 'PAY') THEN
1042
1043 debug_info := 'Insert into ap_invoice_relations';
1044
1045 INSERT INTO AP_INVOICE_RELATIONSHIPS(
1046 ORIGINAL_INVOICE_ID,
1047 RELATED_INVOICE_ID,
1048 CREATED_BY,
1049 CREATION_DATE,
1050 ORIGINAL_PAYMENT_NUM,
1051 LAST_UPDATED_BY,
1052 LAST_UPDATE_DATE,
1053 CHECKRUN_NAME)
1054 VALUES(
1055 P_invoice_id,
1056 P_int_invoice_id,
1057 P_last_updated_by,
1058 sysdate,
1059 P_payment_num,
1060 P_last_updated_by,
1061 sysdate,
1062 P_checkrun_name);
1063 END IF;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067
1068 IF (SQLCODE <> -20001 ) THEN
1069 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1070 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1071 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1072 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
1073 ||', Int_nvoice_id = '||TO_CHAR(P_int_invoice_id)
1074 ||', Payment_num = '||TO_CHAR(P_payment_num)
1075 ||', Checkrun_name = '||P_checkrun_name
1076 ||', Payment_num = '||TO_CHAR(P_payment_num)
1077 ||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
1078 ||', payment_dists_flag = '||P_payment_dists_flag
1079 ||', payment_mode = '||P_payment_mode
1080 ||', replace_flag = '||P_replace_flag);
1081 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1082 END IF;
1083
1084 APP_EXCEPTION.RAISE_EXCEPTION;
1085
1086 END ap_int_inv_insert_ap_inv_rel;
1087
1088
1089 /*==========================================================================
1090 Insert AP_INVOICE_LINES
1091 *=====================================================================*/
1092
1093 PROCEDURE ap_int_inv_insert_ap_inv_line(
1094 P_int_invoice_id IN NUMBER,
1095 P_accounting_date IN DATE,
1096 P_old_invoice_num IN VARCHAR2,
1097 P_interest_amount IN NUMBER,
1098 P_interest_base_amount IN NUMBER,
1099 P_period_name IN VARCHAR2,
1100 P_set_of_books_id IN NUMBER,
1101 P_last_updated_by IN NUMBER,
1102 P_last_update_login IN NUMBER,
1103 P_asset_account_flag IN VARCHAR2,
1104 P_Payment_cross_rate IN NUMBER,
1105 P_payment_mode IN VARCHAR2,
1106 p_type_1099 IN VARCHAR2,
1107 p_income_tax_region IN VARCHAR2,
1108 p_org_id IN NUMBER,
1109 p_calling_sequence IN VARCHAR2)
1110 IS
1111
1112 debug_info VARCHAR2(100);
1113 current_calling_sequence VARCHAR2(2000);
1114
1115 BEGIN
1116
1117 -- Interest Invoices project - Invoice Lines.
1118 -- This is a new procedure added with Invoice Lines to enter a single
1119 -- line for the created interest invoice regardless of the number of
1120 -- distributions created (which depends on the value of Prorate
1121 -- Across Overdue Invoice).
1122
1123 current_calling_sequence := 'ap_int_inv_insert_ap_inv_line<-'||
1124 P_calling_sequence;
1125
1126 IF (P_payment_mode in ('PAY','PAYMENTBATCH')) THEN
1127
1128 debug_info := 'Insert into ap_invoice_lines';
1129
1130 INSERT INTO AP_INVOICE_LINES
1131 (INVOICE_ID,
1132 LINE_NUMBER,
1133 LINE_TYPE_LOOKUP_CODE,
1134 LAST_UPDATE_DATE,
1135 LAST_UPDATED_BY,
1136 CREATION_DATE,
1137 CREATED_BY,
1138 LAST_UPDATE_LOGIN,
1139 ACCOUNTING_DATE,
1140 PERIOD_NAME,
1141 AMOUNT,
1142 BASE_AMOUNT,
1143 ROUNDING_AMT,
1144 DESCRIPTION,
1145 TYPE_1099,
1146 INCOME_TAX_REGION,
1147 SET_OF_BOOKS_ID,
1148 ASSETS_TRACKING_FLAG,
1149 ASSET_BOOK_TYPE_CODE,
1150 ASSET_CATEGORY_ID,
1151 LINE_SOURCE,
1152 GENERATE_DISTS,
1153 MATCH_TYPE,
1154 PRORATE_ACROSS_ALL_ITEMS,
1155 DEFERRED_ACCTG_FLAG,
1156 WFAPPROVAL_STATUS,
1157 DISCARDED_FLAG,
1158 CANCELLED_FLAG,
1159 FINAL_MATCH_FLAG,
1160 REQUESTER_ID,
1161 /*GLOBAL_ATTRIBUTE_CATEGORY,
1162 GLOBAL_ATTRIBUTE1,
1163 GLOBAL_ATTRIBUTE2,
1164 GLOBAL_ATTRIBUTE3,
1165 GLOBAL_ATTRIBUTE4,
1166 GLOBAL_ATTRIBUTE5,
1167 GLOBAL_ATTRIBUTE6,
1168 GLOBAL_ATTRIBUTE7,
1169 GLOBAL_ATTRIBUTE8,
1170 GLOBAL_ATTRIBUTE9,
1171 GLOBAL_ATTRIBUTE10,
1172 GLOBAL_ATTRIBUTE11,
1173 GLOBAL_ATTRIBUTE12,
1174 GLOBAL_ATTRIBUTE13,
1175 GLOBAL_ATTRIBUTE14,
1176 GLOBAL_ATTRIBUTE15,
1177 GLOBAL_ATTRIBUTE16,
1178 GLOBAL_ATTRIBUTE17,
1179 GLOBAL_ATTRIBUTE18,
1180 GLOBAL_ATTRIBUTE19,
1181 GLOBAL_ATTRIBUTE20,*/
1182 ORG_ID)
1183 VALUES (
1184 P_int_invoice_id, -- INVOICE_ID
1185 1, -- LINE_NUMBER
1186 'ITEM', -- LINE_TYPE_LOOKUP_CODE
1187 SYSDATE, -- LAST_UPDATE_DATE
1188 P_last_updated_by, -- LAST_UPDATED_BY
1189 SYSDATE, -- CREATION_DATE
1190 P_last_updated_by, -- CREATED_BY
1191 P_last_update_login, -- LAST_UPDATE_LOGIN
1192 P_accounting_date, -- ACCOUNTING_DATE
1193 P_period_name, -- PERIOD_NAME
1194 P_interest_amount / nvl(P_payment_cross_rate,1), -- AMOUNT
1195 P_interest_base_amount, -- BASE_AMOUNT
1196 NULL, -- ROUNDING_AMT
1197 'Interest : Overdue Invoice ' || P_old_invoice_num, -- DESCRIPTION
1198 p_type_1099, -- TYPE_1099
1199 p_income_tax_region, -- INCOME_TAX_REGION
1200 P_set_of_books_id, -- SET_OF_BOOKS_ID
1201 P_asset_account_flag, -- ASSETS_TRACKING_FLAG
1202 NULL, -- ASSET_BOOK_TYPE_CODE
1203 NULL, -- ASSET_CATEGORY_ID
1204 'AUTO INVOICE CREATION', -- LINE_SOURCE
1205 'D', -- GENERATE_DISTS
1206 'NOT_MATCHED', -- MATCH_TYPE
1207 'N', -- PRORATE_ACROSS_ALL_ITEMS
1208 'N', -- DEFERRED_ACCTG_FLAG
1209 'NOT REQUIRED', -- WFAPPROVAL_STATUS
1210 'N', -- DISCARDED_FLAG
1211 'N', -- CANCELLED_FLAG
1212 'N', -- FINAL_MATCH_FLAG
1213 NULL, -- REQUESTER_ID
1214 /*NULL, -- Global Attributes NULLified for now.
1215 NULL,
1216 NULL,
1217 NULL,
1218 NULL,
1219 NULL,
1220 NULL,
1221 NULL,
1222 NULL,
1223 NULL,
1224 NULL,
1225 NULL,
1226 NULL,
1227 NULL,
1228 NULL,
1229 NULL,
1230 NULL,
1231 NULL,
1232 NULL,
1233 NULL,
1234 NULL, */
1235 P_org_id); -- ORG_ID
1236
1237 END IF;
1238
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241
1242 IF (SQLCODE <> -20001 ) THEN
1243 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1244 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1245 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1246 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1247 'Int_invoice_id = ' ||TO_CHAR(P_int_invoice_id)
1248 ||', Account_date = ' ||TO_CHAR(P_accounting_date)
1249 ||', Old_invoice_num = ' ||P_old_invoice_num
1250 ||', Asset_account_flag = ' ||P_asset_account_flag
1251 ||', Period name = ' ||P_period_name
1252 ||', Interest Amount = ' ||TO_CHAR(P_interest_amount)
1253 ||', Set 0f books id = ' ||TO_CHAR(P_set_of_books_id)
1254 ||', Last_updated_by = ' ||TO_CHAR(P_last_updated_by)
1255 ||', payment_mode = ' ||P_payment_mode
1256 ||', type_1099 = ' ||P_type_1099
1257 ||', income_tax_region = ' ||p_income_tax_region);
1258
1259 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1260 END IF;
1261
1262 APP_EXCEPTION.RAISE_EXCEPTION;
1263
1264 END ap_int_inv_insert_ap_inv_line;
1265
1266
1267 /*==========================================================================
1268 Insert AP_INVOICE_DISTRIBUTIONS
1269 *=====================================================================*/
1270
1271 PROCEDURE ap_int_inv_insert_ap_inv_dist(
1272 P_int_invoice_id IN NUMBER,
1273 P_accounting_date IN DATE,
1274 P_vendor_id IN NUMBER,
1275 P_old_invoice_num IN VARCHAR2,
1276 P_int_invoice_num IN VARCHAR2,
1277 P_interest_amount IN NUMBER,
1278 P_interest_base_amount IN NUMBER,
1279 P_period_name IN VARCHAR2,
1280 P_set_of_books_id IN NUMBER,
1281 P_last_updated_by IN NUMBER,
1282 P_interest_accts_pay_ccid IN NUMBER,
1283 P_asset_account_flag IN VARCHAR2,
1284 P_Payment_cross_rate IN NUMBER,
1285 P_exchange_rate IN NUMBER,
1286 P_exchange_rate_type IN VARCHAR2,
1287 P_exchange_date IN DATE,
1288 P_payment_dists_flag IN VARCHAR2,
1289 P_payment_mode IN VARCHAR2,
1290 P_replace_flag IN VARCHAR2,
1291 P_invoice_id IN NUMBER,
1292 P_calling_sequence IN VARCHAR2,
1293 P_invoice_currency_code IN VARCHAR2,
1294 P_base_currency_code IN VARCHAR2,
1295 P_type_1099 IN VARCHAR2,
1296 P_income_tax_region IN VARCHAR2,
1297 P_org_id IN NUMBER,
1298 P_last_update_login IN NUMBER,
1299 p_accounting_event_id IN NUMBER DEFAULT NULL)
1300 IS
1301
1302 debug_info VARCHAR2(100);
1303 current_calling_sequence VARCHAR2(2000);
1304 l_invoice_distribution_id
1305 ap_invoice_distributions.invoice_distribution_id%TYPE;
1306 l_proration_divisor number;
1307 l_inv_distribution_line_number number;
1308 l_distribution_total number;
1309 l_distribution_base_total number;
1310 l_account_from_ap_system CHAR(1) ;
1311 l_system_interest_account ap_system_parameters_all.INTEREST_CODE_COMBINATION_ID%type;
1312
1313
1314
1315
1316 --Bug 4539462 DBI logging
1317 l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1318
1319 -- This cursor has the logic to prorate interest invoice amount across
1320 -- 'ITEM' lines of associated overdue invoice distribution lines.
1321
1322 CURSOR c_prorate_int_inv IS
1323 SELECT ap_utilities_pkg.ap_round_currency
1324 (((amount * P_interest_amount)/l_proration_divisor),P_invoice_currency_code)
1325 prorated_dist_amount ,
1326 ap_utilities_pkg.ap_round_currency(
1327 nvl(P_exchange_rate,1) *
1328 ap_utilities_pkg.ap_round_currency
1329 (((amount * P_interest_amount)/l_proration_divisor),P_invoice_currency_code) -- amount rounded
1330 / nvl(P_payment_cross_rate, 1),
1331 P_base_currency_code) prorated_dist_base_amt,
1332 dist_code_combination_id,
1333 type_1099,
1334 income_tax_region
1335 FROM ap_invoice_distributions_all AID --bug 9328384
1336 WHERE AID.invoice_id = P_invoice_id
1337 AND AID.line_type_lookup_code IN ('ITEM', 'IPV','ACCRUAL')
1338 ORDER BY AID.invoice_distribution_id;
1339
1340 rec_prorate_int_inv c_prorate_int_inv%rowtype;
1341
1342 BEGIN
1343
1344 -- Interest Invoices project - Invoice Lines - 11ix
1345 -- Add the parameters
1346 -- P_invoice_currency_code
1347 -- P_base_currency_code
1348 -- P_type_1099
1349 -- P_income_tax_region
1350 -- P_org_id
1351 -- P_last_update_login
1352 -- Modify Cursor c_prorate_int_inv:
1353 -- 1. Round interest amount depending on P_invoice_currency_code
1354 -- 2. Round interest amount before using it in calculating the base
1355 -- currency interest amount.
1356 -- 3. Add 'IPV' type along with 'ITEM' type distributions.
1357 -- Modify SELECT nvl(sum(AID.amount),0) to include 'IPV' type.
1358 -- Modify INSERT INTO AP_INVOICE_DISTRIBUTIONS:
1359 -- Add invoice_line_number, Remove exchange columns, Add a few
1360 -- other Columns.
1361
1362 -- In 11ix we will always prorate distributions. We will not have
1363 -- the case of inserting one column any more.
1364 -- For that reason we will remove Prorate Across Distributions
1365
1366 -- No Overlaying/flexbuilding needs to be done with SLA uptake,
1367 -- during the creation of the Interest Invoices. All the distributions
1368 -- that we create will have the same expense account as that of the
1369 -- parent invoice distributions.
1370
1371 current_calling_sequence := 'ap_int_inv_insert_ap_inv_dist<-'||
1372 P_calling_sequence;
1373
1374 debug_info := 'Payment Mode Is: '||p_payment_mode;
1375
1376 IF (P_payment_mode in ('PAY','PAYMENTBATCH')) THEN
1377
1378 -- Find out NOCOPY if Interest Invoice needs to be prorated
1379 -- among ITEM distributions of overdue Invoice
1380
1381 -- Get the divisor for proration from overdue invoice.
1382 -- If the proration divisor is zero then decoding value
1383 -- to 1 to avoid divide by zero error
1384
1385 select PRORATE_INT_INV_ACROSS_DISTS --start of code for bug 7112849
1386 into l_account_from_ap_system
1387 from ap_system_parameters_all
1388 where org_id = P_org_id ; -- Bug#8763764
1389
1390 -- 8453503 added NVL in the below condition
1391
1392 IF ( NVL(l_account_from_ap_system,'N') <>'Y' ) THEN
1393
1394 -- check whether PRORATE_INT_INV_ACROSS_DISTS is Y or not if it is not Y use the system account defined on payables else use the invoice account and prorate
1395 select INTEREST_CODE_COMBINATION_ID
1396 into l_system_interest_account
1397 from ap_system_parameters_all
1398 where org_id = P_org_id ; -- Bug#8763764;
1399
1400 debug_info := 'inserting tax distribution using system defined account in ap_system_parameters';
1401 l_inv_distribution_line_number :=
1402 NVL(l_inv_distribution_line_number,0) + 1;
1403 INSERT INTO AP_INVOICE_DISTRIBUTIONS_ALL --bug 9328384
1404 (INVOICE_ID,
1405 DIST_CODE_COMBINATION_ID,
1406 INVOICE_DISTRIBUTION_ID,
1407 INVOICE_LINE_NUMBER,
1408 LAST_UPDATE_DATE,
1409 LAST_UPDATED_BY,
1410 CREATION_DATE,
1411 CREATED_BY,
1412 ACCOUNTING_DATE,
1413 PERIOD_NAME,
1414 AMOUNT,
1415 BASE_AMOUNT,
1416 DESCRIPTION,
1417 TYPE_1099,
1418 INCOME_TAX_REGION,
1419 POSTED_FLAG,
1420 ASSETS_ADDITION_FLAG,
1421 SET_OF_BOOKS_ID,
1422 DISTRIBUTION_LINE_NUMBER,
1423 LINE_TYPE_LOOKUP_CODE,
1424 ACCRUAL_POSTED_FLAG,
1425 CASH_POSTED_FLAG,
1426 MATCH_STATUS_FLAG,
1427 ASSETS_TRACKING_FLAG,
1428 PA_ADDITION_FLAG,
1429 ACCTS_PAY_CODE_COMBINATION_ID,
1430 dist_match_type,
1431 distribution_class,
1432 amount_to_post,
1433 base_amount_to_post,
1434 posted_amount,
1435 posted_base_amount,
1436 upgrade_posted_amt,
1437 upgrade_base_posted_amt,
1438 rounding_amt,
1439 accounting_event_id,
1440 encumbered_flag,
1441 packet_id,
1442 reversal_flag,
1443 parent_reversal_id,
1444 cancellation_flag,
1445 asset_book_type_code,
1446 asset_category_id,
1447 last_update_login,
1448 /* TAX_CODE_OVERRIDE_FLAG, Waiting for e-tax
1449 TAX_RECOVERY_RATE,
1450 TAX_RECOVERY_OVERRIDE_FLAG,
1451 TAX_RECOVERABLE_FLAG, */
1452 ORG_ID,
1453 --Freight and Special Charges
1454 RCV_CHARGE_ADDITION_FLAG)
1455 VALUES (
1456 P_int_invoice_id,
1457 l_system_interest_account ,
1458 ap_invoice_distributions_s.NEXTVAL,
1459 1,
1460 SYSDATE,
1461 P_last_updated_by,
1462 SYSDATE,
1463 P_last_updated_by,
1464 P_accounting_date,
1465 P_period_name,
1466 P_interest_amount / nvl(P_payment_cross_rate,1), -- AMOUNT
1467 P_interest_base_amount, -- BASE_AMOUNT
1468 'Interest : Overdue Invoice ' || P_old_invoice_num,
1469 p_type_1099, -- TYPE_1099
1470 p_income_tax_region, -- INCOME_TAX_REGION
1471 'N',
1472 'U',
1473 P_set_of_books_id,
1474 l_inv_distribution_line_number,
1475 'ITEM',
1476 'N',
1477 'N',
1478 'A',
1479 P_asset_account_flag,
1480 'E',
1481 P_interest_accts_pay_ccid,
1482 'PERMANENT',
1483 null,
1484 null,
1485 null,
1486 null,
1487 null,
1488 null,
1489 null,
1490 null, -- rounding amount
1491 p_accounting_event_id, -- aid.accounting_event_id /* Bug 4742671, switching null and 'N' */
1492 'N',
1493 null,
1494 null,
1495 null,
1496 null,
1497 null,
1498 null,
1499 P_last_update_login,
1500 /* 'N',
1501 '',
1502 'N',
1503 'N', */
1504 p_org_id,
1505 'N')
1506 returning invoice_distribution_id
1507 into l_invoice_distribution_id; --end of code for bug 7112849
1508 ELSE
1509
1510 debug_info := 'Selecting distribution for proration';
1511 SELECT nvl(sum(AID.amount),0)
1512 INTO l_proration_divisor
1513 FROM ap_invoice_distributions_all AID --bug 9328384
1514 WHERE AID.invoice_id = P_invoice_id
1515 AND AID.line_type_lookup_code IN ('ITEM', 'IPV','ACCRUAL');
1516
1517 debug_info := 'Opening the prorate cursor';
1518 OPEN c_prorate_int_inv;
1519 LOOP
1520 FETCH c_prorate_int_inv into rec_prorate_int_inv;
1521
1522 EXIT WHEN c_prorate_int_inv%NOTFOUND;
1523
1524 l_inv_distribution_line_number :=
1525 NVL(l_inv_distribution_line_number,0) + 1;
1526 l_distribution_total :=
1527 NVL(l_distribution_total,0) +
1528 rec_prorate_int_inv.prorated_dist_amount;
1529 l_distribution_base_total :=
1530 NVL(l_distribution_base_total,0) +
1531 rec_prorate_int_inv.prorated_dist_base_amt;
1532
1533
1534 debug_info := 'Inserting invoice ditsributions';
1535 INSERT INTO AP_INVOICE_DISTRIBUTIONS_ALL --bug 9328384
1536 (INVOICE_ID,
1537 DIST_CODE_COMBINATION_ID,
1538 INVOICE_DISTRIBUTION_ID,
1539 INVOICE_LINE_NUMBER,
1540 LAST_UPDATE_DATE,
1541 LAST_UPDATED_BY,
1542 CREATION_DATE,
1543 CREATED_BY,
1544 ACCOUNTING_DATE,
1545 PERIOD_NAME,
1546 AMOUNT,
1547 BASE_AMOUNT,
1548 DESCRIPTION,
1549 TYPE_1099,
1550 INCOME_TAX_REGION,
1551 POSTED_FLAG,
1552 ASSETS_ADDITION_FLAG,
1553 SET_OF_BOOKS_ID,
1554 DISTRIBUTION_LINE_NUMBER,
1555 LINE_TYPE_LOOKUP_CODE,
1556 ACCRUAL_POSTED_FLAG,
1557 CASH_POSTED_FLAG,
1558 MATCH_STATUS_FLAG,
1559 ASSETS_TRACKING_FLAG,
1560 PA_ADDITION_FLAG,
1561 ACCTS_PAY_CODE_COMBINATION_ID,
1562 dist_match_type,
1563 distribution_class,
1564 amount_to_post,
1565 base_amount_to_post,
1566 posted_amount,
1567 posted_base_amount,
1568 upgrade_posted_amt,
1569 upgrade_base_posted_amt,
1570 rounding_amt,
1571 accounting_event_id,
1572 encumbered_flag,
1573 packet_id,
1574 reversal_flag,
1575 parent_reversal_id,
1576 cancellation_flag,
1577 asset_book_type_code,
1578 asset_category_id,
1579 last_update_login,
1580 /* TAX_CODE_OVERRIDE_FLAG, Waiting for e-tax
1581 TAX_RECOVERY_RATE,
1582 TAX_RECOVERY_OVERRIDE_FLAG,
1583 TAX_RECOVERABLE_FLAG, */
1584 ORG_ID,
1585 --Freight and Special Charges
1586 RCV_CHARGE_ADDITION_FLAG)
1587 VALUES (
1588 P_int_invoice_id,
1589 rec_prorate_int_inv.dist_code_combination_id,
1590 ap_invoice_distributions_s.NEXTVAL,
1591 1,
1592 SYSDATE,
1593 P_last_updated_by,
1594 SYSDATE,
1595 P_last_updated_by,
1596 P_accounting_date,
1597 P_period_name,
1598 rec_prorate_int_inv.prorated_dist_amount,
1599 rec_prorate_int_inv.prorated_dist_base_amt,
1600 'Interest : Overdue Invoice ' || P_old_invoice_num,
1601 rec_prorate_int_inv.type_1099,
1602 rec_prorate_int_inv.income_tax_region,
1603 'N',
1604 'U',
1605 P_set_of_books_id,
1606 l_inv_distribution_line_number,
1607 'ITEM',
1608 'N',
1609 'N',
1610 'A',
1611 P_asset_account_flag,
1612 'E',
1613 P_interest_accts_pay_ccid,
1614 'PERMANENT',
1615 null,
1616 null,
1617 null,
1618 null,
1619 null,
1620 null,
1621 null,
1622 null, -- rounding amount
1623 p_accounting_event_id, -- aid.accounting_event_id /* Bug 4742671, switching null and 'N' */
1624 'N',
1625 null,
1626 null,
1627 null,
1628 null,
1629 null,
1630 null,
1631 P_last_update_login,
1632 /* 'N',
1633 '',
1634 'N',
1635 'N', */
1636 p_org_id,
1637 'N')
1638 returning invoice_distribution_id
1639 into l_invoice_distribution_id;
1640
1641
1642 debug_info := 'Calling DBI Pkg';
1643 --Bug 4539462 DBI logging
1644 AP_DBI_PKG.Maintain_DBI_Summary
1645 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1646 p_operation => 'I',
1647 p_key_value1 => P_int_invoice_id,
1648 p_key_value2 => l_invoice_distribution_id,
1649 p_calling_sequence => current_calling_sequence);
1650
1651 END LOOP;
1652 CLOSE c_prorate_int_inv;
1653 END IF ;
1654
1655 -- Make sure that the total of amount and base amount in
1656 -- ap_invoice_distributions is equal to the invoice_amount
1657 -- and base_amount of ap_invoices table
1658
1659 IF (p_interest_amount <> l_distribution_total OR
1660 p_interest_base_amount <> l_distribution_base_total ) THEN
1661
1662 debug_info := 'Update AP_INVOICE_DISTRIBUTIONS_ALL (invoice_id = '||
1663 p_int_invoice_id||')';
1664
1665
1666 -- Perf Bug 5059000
1667 UPDATE ap_invoice_distributions_all aid
1668 set amount = amount - l_distribution_total + p_interest_amount,
1669 base_amount = base_amount - l_distribution_base_total
1670 + p_interest_base_amount
1671 WHERE aid.rowid =(
1672 select row_id from
1673 (
1674 select rowid row_id,
1675 rank() over(order by abs(aid3.amount) desc,
1676 aid3.distribution_line_number desc) r
1677 from ap_invoice_distributions_all aid3 --bug 9328384
1678 WHERE aid3.invoice_id = p_int_invoice_id
1679 )
1680 where r=1 )
1681 RETURNING aid.invoice_distribution_id
1682 BULK COLLECT INTO l_dbi_key_value_list; -- bug 4539462
1683
1684 /* -- Perf Bug 5059000 - commented older UPDATE below
1685 UPDATE ap_invoice_distributions aid1
1686 SET amount = amount -
1687 l_distribution_total +
1688 p_interest_amount,
1689 base_amount = base_amount -
1690 l_distribution_base_total +
1691 p_interest_base_amount
1692 WHERE invoice_id = p_int_invoice_id
1693 AND distribution_line_number =
1694 (SELECT MAX(distribution_line_number)
1695 FROM ap_invoice_distributions aid2
1696 WHERE aid2.invoice_id = p_int_invoice_id
1697 AND ABS(aid2.amount) =
1698 (SELECT MAX(ABS(aid3.amount))
1699 FROM ap_invoice_distributions aid3
1700 WHERE aid3.invoice_id = p_int_invoice_id))
1701 RETURNING aid1.invoice_distribution_id
1702 BULK COLLECT INTO l_dbi_key_value_list; -- bug 4539462
1703 */
1704 --Bug 4539462 DBI logging
1705 AP_DBI_PKG.Maintain_DBI_Summary
1706 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1707 p_operation => 'U',
1708 p_key_value1 => p_int_invoice_id,
1709 p_key_value_list => l_dbi_key_value_list,
1710 p_calling_sequence => current_calling_sequence);
1711
1712 END IF;
1713 END IF;
1714
1715 EXCEPTION
1716 WHEN OTHERS THEN
1717
1718 IF (SQLCODE <> -20001 ) THEN
1719 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1720 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1721 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1722 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1723 'Int_invoice_id = '||TO_CHAR(P_int_invoice_id)
1724 ||', Account_date = '||TO_CHAR(P_accounting_date)
1725 ||', Vendor_id = '||TO_CHAR(P_vendor_id)
1726 ||', Old_invoice_num = '||P_old_invoice_num
1727 ||', Int_invoice_num = '||P_int_invoice_num
1728 ||', Interest_accts_pay_ccid = '||
1729 TO_CHAR(P_interest_accts_pay_ccid)
1730 ||', Asset_account_flag = '||P_asset_account_flag
1731 ||', Period name = '||P_period_name
1732 ||', Interest Amount = '||TO_CHAR(P_interest_amount)
1733 ||', Exchange Rate = '||TO_CHAR(P_exchange_rate)
1734 ||', Exchange Rate Type = '||P_exchange_rate_type
1735 ||', Exchange Date = '||TO_CHAR(P_exchange_date)
1736 ||', Set 0f books id = '||TO_CHAR(P_set_of_books_id)
1737 ||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
1738 ||', payment_dists_flag = '||P_payment_dists_flag
1739 ||', payment_mode = '||P_payment_mode
1740 ||', replace_flag = '||P_replace_flag);
1741 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1742 END IF;
1743
1744 /* Start - BUG 14364091 - Logging */
1745 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
1746 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
1747 FND_FILE.PUT_LINE(FND_FILE.LOG, debug_info||' - '||systimestamp);
1748 /* End - BUG 14364091 - Logging */
1749
1750 APP_EXCEPTION.RAISE_EXCEPTION;
1751
1752 END ap_int_inv_insert_ap_inv_dist;
1753
1754
1755 /*==========================================================================
1756 Insert AP_PAYMENT_SCHEDULES
1757 *==========================================================================*/
1758
1759 PROCEDURE ap_int_inv_insert_ap_pay_sche(
1760 P_int_invoice_id IN NUMBER,
1761 P_check_date IN DATE,
1762 P_interest_amount IN NUMBER,
1763 P_payment_method_code IN VARCHAR2, --4552701
1764 P_last_updated_by IN NUMBER,
1765 P_payment_cross_rate IN NUMBER,
1766 P_payment_priority IN NUMBER,
1767 P_payment_dists_flag IN VARCHAR2,
1768 P_payment_mode IN VARCHAR2,
1769 P_replace_flag IN VARCHAR2,
1770 P_calling_sequence IN VARCHAR2,
1771 P_External_Bank_Account_Id IN NUMBER,
1772 P_org_id IN NUMBER,
1773 P_last_update_login IN NUMBER) IS
1774
1775 debug_info VARCHAR2(100);
1776 current_calling_sequence VARCHAR2(2000);
1777
1778 --Start of 8557334
1779 l_remit_to_supplier_name AP_INVOICES.remit_to_supplier_name%TYPE;
1780 l_remit_to_supplier_id AP_INVOICES.remit_to_supplier_id%TYPE;
1781 l_remit_to_supplier_site AP_INVOICES.remit_to_supplier_site%TYPE;
1782 l_remit_to_supplier_site_id AP_INVOICES.remit_to_supplier_site_id%TYPE;
1783 l_relationship_id AP_INVOICES.relationship_id%TYPE;
1784 --End of 8557334
1785
1786 BEGIN
1787
1788 -- Interest Invoices project - Invoice Lines
1789 -- Add parameters to signature and INSERT statement
1790 -- P_org_id
1791 -- P_last_update_login
1792
1793 current_calling_sequence := 'ap_int_inv_insert_ap_pay_sche<-'||
1794 P_calling_sequence;
1795
1796 IF (P_payment_mode = 'PAY') THEN
1797 debug_info := 'Insert into ap_payment_schedules';
1798
1799 -- modified code as per bug 8557334 starts
1800
1801 SELECT remit_to_supplier_name, remit_to_supplier_id,
1802 remit_to_supplier_site, remit_to_supplier_site_id,
1803 relationship_id
1804 INTO l_remit_to_supplier_name, l_remit_to_supplier_id,
1805 l_remit_to_supplier_site, l_remit_to_supplier_site_id,
1806 l_relationship_id
1807 FROM AP_INVOICES_ALL
1808 WHERE INVOICE_ID = P_int_invoice_id;
1809
1810
1811 INSERT INTO AP_PAYMENT_SCHEDULES(
1812 INVOICE_ID,
1813 PAYMENT_NUM,
1814 LAST_UPDATE_DATE,
1815 LAST_UPDATED_BY,
1816 CREATION_DATE,
1817 CREATED_BY,
1818 DUE_DATE,
1819 DISCOUNT_DATE,
1820 GROSS_AMOUNT,
1821 INV_CURR_GROSS_AMOUNT,
1822 DISCOUNT_AMOUNT_AVAILABLE,
1823 AMOUNT_REMAINING,
1824 DISCOUNT_AMOUNT_REMAINING,
1825 PAYMENT_PRIORITY,
1826 PAYMENT_STATUS_FLAG,
1827 PAYMENT_CROSS_RATE,
1828 PAYMENT_METHOD_CODE, --4552701
1829 External_Bank_Account_Id,
1830 ORG_ID,
1831 LAST_UPDATE_LOGIN,
1832 -- bug 8557334
1833 REMIT_TO_SUPPLIER_NAME,
1834 REMIT_TO_SUPPLIER_ID,
1835 REMIT_TO_SUPPLIER_SITE,
1836 REMIT_TO_SUPPLIER_SITE_ID,
1837 RELATIONSHIP_ID
1838 -- bug 8557334
1839 )
1840 VALUES (
1841 P_int_invoice_id,
1842 1,
1843 sysdate,
1844 P_last_updated_by,
1845 sysdate,
1846 P_last_updated_by,
1847 P_check_date,
1848 NULL,
1849 P_interest_amount,
1850 P_interest_amount /P_payment_cross_rate,
1851 0,
1852 0,
1853 0,
1854 P_payment_priority,
1855 'Y',
1856 P_payment_cross_rate,
1857 P_payment_method_code, --4552701
1858 P_External_Bank_Account_Id,
1859 P_org_id,
1860 P_last_update_login,
1861 -- bug 8557334
1862 l_remit_to_supplier_name,
1863 l_remit_to_supplier_id,
1864 l_remit_to_supplier_site,
1865 l_remit_to_supplier_site_id,
1866 l_relationship_id
1867 -- bug 8557334
1868 );
1869
1870 -- modified code as per bug 8557334 ends
1871
1872 --Bug 4539462 DBI logging
1873 AP_DBI_PKG.Maintain_DBI_Summary
1874 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1875 p_operation => 'I',
1876 p_key_value1 => P_int_invoice_id,
1877 p_key_value2 => 1,
1878 p_calling_sequence => current_calling_sequence);
1879 END IF;
1880
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883
1884 IF (SQLCODE <> -20001 ) THEN
1885 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1886 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1887 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1888 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1889 ' Int_invoice_id = '||TO_CHAR(P_int_invoice_id)
1890 ||', Check_date = '||TO_CHAR(P_check_date)
1891 ||', Interest Amount = '||TO_CHAR(P_interest_amount)
1892 ||', Payment_method_code = '||P_payment_method_code
1893 ||', Payment_cross_rate = '||TO_CHAR(P_payment_cross_rate)
1894 ||', Payment_priority = '||TO_CHAR(P_payment_priority)
1895 ||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
1896 ||', payment_dists_flag = '||P_payment_dists_flag
1897 ||', payment_mode = '||P_payment_mode
1898 ||', replace_flag = '||P_replace_flag);
1899
1900 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1901 END IF;
1902
1903 APP_EXCEPTION.RAISE_EXCEPTION;
1904
1905 END ap_int_inv_insert_ap_pay_sche;
1906
1907
1908 /*===================================================================
1909 Reverse Interest Invoice
1910 ===================================================================*/
1911
1912 PROCEDURE ap_reverse_Interest_Invoice (
1913 P_Check_Id IN NUMBER,
1914 P_Invoice_Payment_Id IN NUMBER,
1915 P_Check_Date IN DATE,
1916 P_Period_Name IN VARCHAR2,
1917 P_Last_Updated_By IN NUMBER,
1918 P_Calling_Sequence IN VARCHAR2,
1919 P_last_update_login IN NUMBER DEFAULT NULL )
1920 IS
1921
1922 l_Sys_Auto_Calc_Int_Flag VARCHAR2(1);
1923 l_Vendor_Auto_Calc_Int_Flag VARCHAR2(1);
1924 l_Debug_Info VARCHAR2(240);
1925 l_Curr_Calling_Sequence VARCHAR2(2000);
1926 l_invoice_distribution_id NUMBER;
1927 --Bug 4539462 DBI logging
1928 l_dbi_key_value_list1 ap_dbi_pkg.r_dbi_key_value_arr;
1929 l_dbi_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1930
1931 CURSOR C_Interest_Inv_Cur is
1932 SELECT AID.invoice_id Invoice_Id,
1933 AID.dist_code_combination_id Dist_Code_Combination_Id,
1934 ap_invoice_distributions_s.NEXTVAL Invoice_Distribution_Id,
1935 AID.invoice_line_number Invoice_Line_Number,
1936 AID.set_of_books_id Set_Of_Books_Id,
1937 0-AID.amount Amount,
1938 AID.line_type_lookup_code Line_Type_Lookup_code,
1939 0-AID.base_amount Base_Amount,
1940 ALC.displayed_field || ' '||AID.description Description,
1941 DECODE(GL.account_type, 'A', 'Y', 'N') Assets_Tracking_Flag,
1942 AID.accts_pay_code_combination_id Accts_Pay_Code_Combination_Id,
1943 AID.org_id Org_Id
1944 FROM ap_invoice_distributions AID,
1945 gl_code_combinations GL,
1946 ap_invoice_payments AIP,
1947 ap_invoice_relationships AIR,
1948 ap_lookup_codes ALC
1949 WHERE AIR.related_invoice_id = AID.invoice_id
1950 AND GL.code_combination_id = AID.dist_code_combination_id
1951 AND AID.invoice_id = AIP.invoice_id
1952 AND AIP.invoice_payment_id = P_Invoice_Payment_Id
1953 AND AIP.amount > 0
1954 AND ALC.lookup_type = 'NLS TRANSLATION'
1955 AND ALC.lookup_code = 'VOID';
1956
1957 Interest_Inv_Cur C_Interest_Inv_Cur%rowtype;
1958 l_max_dist_line_num NUMBER;
1959
1960 BEGIN
1961
1962 -- Interest Invoices project - Invoice Lines
1963 -- Add parameter P_last_update_login
1964 -- Add the UPDATE statement UPDATE ap_invoice_lines AIL and MRC call
1965 -- to Reverse the line.
1966 -- Add SELECT max(aid.distribution_line_number)
1967 -- Add cursor C_Interest_Inv_Cur and define Interest_Inv_Cur of rowtype
1968 -- Use cursor to loop and insert an equivalent number of distributions
1969 -- to the original interest invoice in order to cancel them all. It used
1970 -- insert only one distribution which is wrong.
1971 -- Modify INSERT INTO AP_INVOICE_DISTRIBUTIONS
1972
1973
1974 l_Curr_Calling_Sequence := 'AP_INTEREST_INVOICE_PKG.AP_REVERSE_INTEREST_INVOICE<-'||
1975 P_Calling_Sequence;
1976
1977 -------------------------------------------------------------------
1978 l_Debug_Info := 'Get system and vendor PPA flags';
1979
1980 SELECT APS.auto_calculate_interest_flag,
1981 PV.auto_calculate_interest_flag
1982 INTO l_Sys_Auto_Calc_Int_Flag,
1983 l_Vendor_Auto_Calc_Int_Flag
1984 FROM
1985 ap_product_setup aps,
1986 po_vendors PV,
1987 ap_checks AC
1988 WHERE AC.check_id = P_Check_Id
1989 AND AC.vendor_id = PV.vendor_id;
1990
1991 IF (l_Sys_Auto_Calc_Int_Flag = 'Y' AND
1992 l_Vendor_Auto_Calc_Int_Flag = 'Y') THEN
1993
1994 -----------------------------------------------------------------
1995 l_Debug_Info := 'Zero related payment schedules';
1996
1997 --Bug 4539462 DBI logging
1998 UPDATE ap_payment_schedules_all APS
1999 SET APS.last_updated_by = P_Last_Updated_By,
2000 APS.gross_amount = 0,
2001 APS.inv_curr_gross_amount = 0,
2002 APS.last_update_date = sysdate,
2003 APS.amount_remaining = 0
2004 WHERE APS.invoice_id IN
2005 (SELECT AIR.related_invoice_id
2006 FROM ap_invoice_relationships AIR,
2007 ap_invoice_payments_all AIP
2008 WHERE AIP.invoice_payment_id = P_Invoice_Payment_Id
2009 AND AIR.related_invoice_id = AIP.invoice_id)
2010 RETURNING APS.invoice_id
2011 BULK COLLECT INTO l_dbi_key_value_list1;
2012
2013 IF (SQL%NOTFOUND) THEN
2014 RETURN;
2015 END IF;
2016
2017 --Bug 4539462 DBI logging
2018 AP_DBI_PKG.Maintain_DBI_Summary
2019 (p_table_name => 'AP_PAYMENT_SCHEDULES',
2020 p_operation => 'U',
2021 p_key_value_list => l_dbi_key_value_list1,
2022 p_calling_sequence => l_curr_calling_sequence);
2023
2024 -----------------------------------------------------------------
2025 l_Debug_Info := 'Zero related invoice';
2026
2027 --Bug 4539462 DBI logging
2028 --Bug 5056061 Modified the update to prevent FTS
2029 UPDATE ap_invoices_all AI
2030 SET AI.description='VOID '||AI.description,
2031 AI.invoice_amount = 0,
2032 AI.pay_curr_invoice_amount = 0,
2033 AI.amount_paid = 0,
2034 AI.invoice_distribution_total = 0
2035 WHERE AI.invoice_id IN
2036 (SELECT /*+ UNNEST */ AIR.related_invoice_id
2037 FROM ap_invoice_relationships AIR,
2038 ap_invoice_payments_all AIP
2039 WHERE AIP.invoice_payment_id = P_Invoice_Payment_Id
2040 AND AIR.related_invoice_id = AIP.invoice_id)
2041 RETURNING invoice_id
2042 BULK COLLECT INTO l_dbi_key_value_list2;
2043
2044 AP_DBI_PKG.Maintain_DBI_Summary
2045 (p_table_name => 'AP_INVOICES',
2046 p_operation => 'U',
2047 p_key_value_list => l_dbi_key_value_list2,
2048 p_calling_sequence => l_curr_calling_sequence);
2049
2050
2051
2052 -----------------------------------------------------------------
2053 -- Interest Invoices project - Invoice Lines
2054
2055 l_Debug_Info := 'Zero related invoice line';
2056 --Bug 5056061 Modified the update to prevent FTS
2057 UPDATE ap_invoice_lines_all AIL
2058 SET AIL.description = 'VOID '||AIL.description,
2059 AIL.amount = 0,
2060 AIL.base_amount = 0
2061 WHERE AIL.invoice_id IN
2062 (SELECT /*+ UNNEST */ AIR.related_invoice_id
2063 FROM ap_invoice_relationships AIR,
2064 ap_invoice_payments_all AIP
2065 WHERE AIP.invoice_payment_id = P_Invoice_Payment_Id
2066 AND AIR.related_invoice_id = AIP.invoice_id);
2067
2068
2069 -----------------------------------------------------------------
2070
2071 -- Interest Invoices project - Invoice Lines
2072
2073 l_Debug_Info := 'Reverse related invoice distributions';
2074
2075 SELECT max(aid.distribution_line_number)
2076 INTO l_max_dist_line_num
2077 FROM ap_invoice_distributions aid,
2078 gl_code_combinations gl,
2079 ap_invoice_payments aip,
2080 ap_invoice_relationships air,
2081 ap_lookup_codes alc
2082 WHERE AIR.related_invoice_id = AID.invoice_id
2083 AND GL.code_combination_id = AID.dist_code_combination_id
2084 AND AID.invoice_id = AIP.invoice_id
2085 AND AIP.invoice_payment_id = P_Invoice_Payment_Id
2086 AND AIP.amount > 0
2087 AND ALC.lookup_type = 'NLS TRANSLATION'
2088 AND ALC.lookup_code = 'VOID';
2089
2090 OPEN C_Interest_Inv_Cur;
2091 LOOP
2092 FETCH C_Interest_Inv_Cur INTO Interest_Inv_Cur;
2093
2094 EXIT WHEN C_Interest_Inv_Cur%NOTFOUND;
2095
2096 l_max_dist_line_num := l_max_dist_line_num + 1;
2097
2098 INSERT INTO ap_invoice_distributions(
2099 INVOICE_ID,
2100 DIST_CODE_COMBINATION_ID,
2101 INVOICE_DISTRIBUTION_ID,
2102 INVOICE_LINE_NUMBER,
2103 LAST_UPDATED_BY,
2104 ASSETS_ADDITION_FLAG,
2105 ACCOUNTING_DATE,
2106 PERIOD_NAME,
2107 SET_OF_BOOKS_ID,
2108 AMOUNT,
2109 POSTED_FLAG,
2110 CASH_POSTED_FLAG,
2111 ACCRUAL_POSTED_FLAG,
2112 MATCH_STATUS_FLAG,
2113 DISTRIBUTION_LINE_NUMBER,
2114 LINE_TYPE_LOOKUP_CODE,
2115 BASE_AMOUNT,
2116 LAST_UPDATE_DATE,
2117 DESCRIPTION,
2118 PA_ADDITION_FLAG,
2119 CREATED_BY,
2120 CREATION_DATE,
2121 ASSETS_TRACKING_FLAG,
2122 ACCTS_PAY_CODE_COMBINATION_ID,
2123 ORG_ID,
2124 DIST_MATCH_TYPE,
2125 DISTRIBUTION_CLASS,
2126 AMOUNT_TO_POST,
2127 BASE_AMOUNT_TO_POST,
2128 POSTED_AMOUNT,
2129 POSTED_BASE_AMOUNT,
2130 UPGRADE_POSTED_AMT,
2131 UPGRADE_BASE_POSTED_AMT,
2132 ROUNDING_AMT,
2133 ACCOUNTING_EVENT_ID,
2134 ENCUMBERED_FLAG,
2135 PACKET_ID,
2136 REVERSAL_FLAG,
2137 PARENT_REVERSAL_ID,
2138 CANCELLATION_FLAG,
2139 ASSET_BOOK_TYPE_CODE,
2140 ASSET_CATEGORY_ID,
2141 LAST_UPDATE_LOGIN,
2142 --Freight and Special Charges
2143 RCV_CHARGE_ADDITION_FLAG)
2144 VALUES (
2145 Interest_Inv_Cur.invoice_id,
2146 Interest_Inv_Cur.dist_code_combination_id,
2147 Interest_Inv_Cur.invoice_distribution_id,
2148 Interest_Inv_Cur.invoice_line_number,
2149 P_Last_Updated_By,
2150 'U',
2151 P_Check_Date,
2152 P_Period_Name,
2153 Interest_Inv_Cur.set_of_books_id,
2154 0-Interest_Inv_Cur.amount,
2155 'N',
2156 'N',
2157 'N',
2158 'A',
2159 l_max_dist_line_num,
2160 Interest_Inv_Cur.line_type_lookup_code,
2161 0-Interest_Inv_Cur.base_amount,
2162 sysdate,
2163 Interest_Inv_Cur.description,
2164 'N',
2165 P_Last_Updated_By,
2166 sysdate,
2167 Interest_Inv_Cur.Assets_Tracking_Flag,
2168 Interest_Inv_Cur.accts_pay_code_combination_id,
2169 Interest_Inv_Cur.org_id,
2170 'MATCH_STATUS',
2171 'PERMANENT',
2172 NULL,
2173 NULL,
2174 NULL,
2175 NULL,
2176 NULL,
2177 NULL,
2178 NULL,
2179 NULL,
2180 'N',
2181 NULL,
2182 NULL,
2183 NULL,
2184 NULL,
2185 NULL,
2186 NULL,
2187 P_last_update_login,
2188 'N');
2189
2190 END LOOP;
2191 CLOSE C_Interest_Inv_Cur;
2192 END IF;
2193
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 IF (SQLCODE <> -20001) THEN
2197 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2198 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2199 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_Curr_Calling_Sequence);
2200 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2201 ' CHECK_ID = ' || TO_CHAR(P_Check_Id)
2202 ||', INVOICE_PAYMENT_ID = '|| TO_CHAR(P_Invoice_Payment_Id)
2203 ||', CHECK_DATE = ' || TO_CHAR(P_Check_Date)
2204 ||', PERIOD_NAME = ' || P_Period_Name
2205 ||', LAST_UPDATED_BY = ' || TO_CHAR(P_Last_Updated_By));
2206 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_Debug_Info);
2207 END IF;
2208 APP_EXCEPTION.RAISE_EXCEPTION;
2209
2210 END ap_reverse_Interest_Invoice;
2211
2212
2213 /*======================================================================
2214 Public Function: Calculate Interest Invoice
2215
2216 The function accept following parameter:
2217
2218 +---------------------------------------------------------------------+
2219 | Variable | NULL? | Type | Description |
2220 +=====================================================================+
2221 | P_invoice_id | No | NUMBER | invoice_id |
2222 +---------------------------------------------------------------------+
2223 | P_sys_auto_calc_int | | | System Profile Otion |
2224 | _flag | No | VARCHAR(1) | 'Y'/'N' |
2225 +---------------------------------------------------------------------+
2226 | P_auto_calculate | | | From each record |
2227 | _interest_flag | No | VARCHAR(1) | 'Y'/'N' |
2228 +---------------------------------------------------------------------+
2229 | P_check_date | No | DATE | Account date |
2230 +---------------------------------------------------------------------+
2231 | P_payment_num | No | NUMBER | Payment number |
2232 +---------------------------------------------------------------------+
2233 | P_amount_remaining | Maybe | NUMBER | Amount remaining |
2234 +---------------------------------------------------------------------+
2235 | P_discount_taken | Maybe | NUMBER | Discount taken |
2236 +---------------------------------------------------------------------+
2237 | P_disount_available | Maybe | NUMBER | Discount Available |
2238 +---------------------------------------------------------------------+
2239 | P_currency_code | No | VARCHAR2(15) | Currency Code |
2240 +---------------------------------------------------------------------+
2241 | P_payment_amount | No | NUMBER | Payment Amount |
2242 +---------------------------------------------------------------------+
2243 | P_calling_sequence | Maybe | VARCHAR2(2000)| Calling sequence for |
2244 | | | | debug usage |
2245 +---------------------------------------------------------------------+
2246
2247
2248 There are 3 output parameter:
2249 +---------------------------------------------------------------------+
2250 | Variable | NULL? | Type | Description |
2251 +=====================================================================+
2252 | P_interest_amount | No | NUMBER | Interest Invoice Amount
2253 +---------------------------------------------------------------------+
2254 | P_interest_invoice | | | New Int. Invoice Num |
2255 | _num | No | VARCHAR2(50) | = Inv_num||'-INT'||## |
2256 +---------------------------------------------------------------------+
2257 | P_due_date | No | DATE | Due date for this PS |
2258 +---------------------------------------------------------------------+
2259
2260
2261 ========================================================================*/
2262
2263 PROCEDURE ap_calculate_interest(
2264 P_invoice_id IN NUMBER,
2265 P_sys_auto_calc_int_flag IN VARCHAR2,
2266 P_auto_calculate_interest_flag IN VARCHAR2,
2267 P_check_date IN DATE,
2268 P_payment_num IN NUMBER,
2269 P_amount_remaining IN NUMBER,
2270 P_discount_taken IN NUMBER,
2271 P_discount_available IN NUMBER,
2272 P_currency_code IN VARCHAR2,
2273 P_interest_amount OUT NOCOPY NUMBER,
2274 P_due_date OUT NOCOPY DATE,
2275 P_interest_invoice_num OUT NOCOPY VARCHAR2,
2276 P_payment_amount IN NUMBER,
2277 P_calling_sequence IN VARCHAR2) IS
2278
2279 current_calling_sequence VARCHAR2(2000);
2280 debug_info VARCHAR2(100);
2281 C_interest_tolerance_amount NUMBER;
2282 C_int_inv_num_ext NUMBER;
2283 C_int_inv_num_ext2 NUMBER;
2284 C_interest_amount NUMBER;
2285 C_due_date DATE;
2286
2287 BEGIN
2288
2289 current_calling_sequence := 'ap_calculate_interest<-'||P_calling_sequence;
2290
2291 ---------------------------
2292 -- Get the tolerance amount
2293 ---------------------------
2294
2295 debug_info := 'Get the interest tolerance amount';
2296
2297 --4533605, modified this for moac
2298
2299 SELECT nvl(interest_tolerance_amount,0)
2300 INTO C_interest_tolerance_amount
2301 FROM ap_system_parameters_all asp,
2302 ap_invoices_all ai
2303 WHERE ai.org_id = asp.org_id
2304 AND ai.invoice_id = p_invoice_id;
2305
2306 ----------------------------------------------------------
2307 -- Get the int_inv_num_ext - Use for interest_invoice_name
2308 ----------------------------------------------------------
2309
2310 debug_info := 'Get the interest invoice NUM EXT';
2311
2312 SELECT count(*)
2313 INTO C_int_inv_num_ext
2314 FROM ap_invoice_relationships
2315 WHERE original_invoice_id = P_invoice_id;
2316
2317 -----------------------------------------------------------
2318 -- Get the int_inv_num_ext2 - Use for interest_invoice_name
2319 -----------------------------------------------------------
2320
2321 debug_info := 'Get the interest invoice NUM EXT2';
2322
2323 SELECT count(*)
2324 INTO C_int_inv_num_ext2
2325 FROM ap_selected_invoices
2326 -- CHANGES FOR BUG - 3293874 ** STARTS **
2327 --WHERE original_invoice_id = P_invoice_id;
2328 WHERE original_invoice_id = to_char(P_invoice_id);
2329 -- CHANGES FOR BUG - 3293874 ** ENDS **
2330 -------------------------------
2331 -- Set the interest invoice_num
2332 -------------------------------
2333
2334 debug_info := 'Get the interest invoice Num';
2335
2336 SELECT invoice_num|| '-INT' ||
2337 to_char(nvl(C_int_inv_num_ext, 0) + nvl(C_int_inv_num_ext2, 0) + 1)
2338 INTO P_interest_invoice_num
2339 FROM ap_invoices
2340 WHERE invoice_id = P_invoice_id;
2341
2342 ---------------------------
2343 -- Get the due date
2344 ---------------------------
2345
2346 debug_info := 'Get invoice_due_date';
2347 BEGIN
2348 SELECT due_date INTO C_due_date
2349 FROM ap_payment_schedules
2350 WHERE P_sys_auto_calc_int_flag = 'Y'
2351 AND P_auto_calculate_interest_flag = 'Y'
2352 AND trunc(P_check_date) > trunc(due_date)
2353 AND payment_num = P_payment_num
2354 AND invoice_id = P_invoice_id;
2355 /* Added the exception block for bug#12835170 Start */
2356 EXCEPTION
2357 WHEN NO_DATA_FOUND THEN
2358 P_interest_amount := 0;
2359 P_due_date := C_due_date;
2360 RETURN;
2361 END;
2362 /* Added the exception block for bug#12835170 End */
2363
2364 -- Call custom calculate interest amount. Make sure it returns both
2365 -- amount and due date and that it does appropriate rounding.
2366 -- If amount returned is null then continue with the following steps
2367 -- Else return;
2368
2369 ---------------------------------
2370 -- Call custom interest package
2371 ---------------------------------
2372
2373 debug_info := 'Calling custom interest package';
2374
2375 -- bug 4995343.To Add a code hook to call Federal
2376 -- package for interest calculation passed extra parameters
2377 -- through the below package.
2378
2379 AP_CUSTOM_INT_INV_PKG.ap_custom_calculate_interest(
2380 P_invoice_id ,
2381 P_sys_auto_calc_int_flag , --bug 4995343
2382 P_auto_calculate_interest_flag , --bug 4995343
2383 P_check_date ,
2384 P_payment_num ,
2385 P_amount_remaining , --bug 4995343
2386 P_discount_taken , --bug 4995343
2387 P_discount_available ,--bug 4995343
2388 P_currency_code ,
2389 P_payment_amount ,
2390 C_interest_amount,
2391 C_due_date );
2392
2393 IF (C_interest_amount IS NULL) THEN
2394
2395 -----------------------------------
2396 -- Calc the interest invoice amount
2397 -----------------------------------
2398
2399 debug_info := 'Get the interest invoice_amount';
2400 BEGIN
2401 SELECT (NVL(P_amount_remaining -
2402 least(nvl(P_discount_taken, 0), P_discount_available), 0) *
2403 power(1 + (annual_interest_rate / (12 * 100)),
2404 trunc((least(P_check_date, add_months(due_date, 12))
2405 -due_date) / 30)) *
2406 (1 + ((annual_interest_rate / (360 * 100)) *
2407 mod((least(P_check_date, add_months(due_date, 12))
2408 -due_date), 30)))) -
2409 NVL(P_amount_remaining - least(nvl(P_discount_taken, 0),
2410 P_discount_available), 0)
2411 INTO C_interest_amount
2412 FROM ap_payment_schedules, ap_interest_periods
2413 WHERE P_sys_auto_calc_int_flag = 'Y'
2414 AND P_auto_calculate_interest_flag = 'Y'
2415 AND TRUNC(P_check_date) > TRUNC(due_date)
2416 AND payment_num = P_payment_num
2417 AND invoice_id = P_invoice_id
2418 AND TRUNC(due_date+1) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
2419 AND (NVL(P_amount_remaining -
2420 least(nvl(P_discount_taken, 0), P_discount_available), 0) *
2421 power(1 + (annual_interest_rate / (12 * 100)),
2422 trunc((least(P_check_date, add_months(due_date, 12))
2423 -due_date) / 30)) *
2424 (1 + ((annual_interest_rate / (360 * 100)) *
2425 mod((least(P_check_date, add_months(due_date, 12))
2426 -due_date), 30)))) -
2427 nvl(P_amount_remaining - least(nvl(P_discount_taken, 0),
2428 P_discount_available), 0)
2429 >= C_interest_tolerance_amount;
2430 /* Added the exception block for bug#12835170 Start */
2431 EXCEPTION
2432 WHEN NO_DATA_FOUND THEN
2433 P_interest_amount := 0;
2434 P_due_date := C_due_date;
2435 RETURN;
2436 END;
2437 /* Added the exception block for bug#12835170 End */
2438 ELSE
2439
2440 -- custom interest package returned an interest amount
2441 -- so we skip ap's interest calculation.
2442
2443 P_interest_amount := C_interest_amount;
2444 P_due_date := C_due_date;
2445 RETURN;
2446 END IF;
2447
2448 --------------------------
2449 -- Round P_interest_amount
2450 --------------------------
2451
2452 debug_info := 'Round the interest invoice_amount';
2453
2454 P_interest_amount := ap_utilities_pkg.ap_round_currency(
2455 C_interest_amount, P_currency_code);
2456
2457 P_due_date := C_due_date;
2458
2459
2460 EXCEPTION
2461 WHEN NO_DATA_FOUND THEN
2462 /* Commented for bug#12835170 Start
2463 IF (debug_info = 'Get the interest invoice_amount') OR
2464 (debug_info = 'Get invoice_due_date') then
2465 P_interest_amount := 0;
2466 P_due_date := C_due_date; / *Bug 5010005* /
2467 RETURN;
2468 ELSE
2469 Commented for bug#12835170 End */
2470 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2471 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2472 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2473 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
2474 ||', Payment_num = '||TO_CHAR(P_payment_num)
2475 ||', Check_date = '||TO_CHAR(P_check_date)
2476 ||', sys_auto_calc_int_flag = '||P_sys_auto_calc_int_flag
2477 ||', auto_calculate_interest_flag = '||P_auto_calculate_interest_flag
2478 ||', Amount_remaining = '||TO_CHAR(P_amount_remaining)
2479 ||', Discount_taken = '||TO_CHAR(P_discount_taken)
2480 ||', Discount_available = '||TO_CHAR(P_discount_available)
2481 ||', Currency_code = '||P_currency_code);
2482 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2483 APP_EXCEPTION.RAISE_EXCEPTION;
2484 /* END IF; Commented for bug#12835170 */
2485
2486 WHEN OTHERS THEN
2487 IF (SQLCODE <> -20001 ) THEN
2488 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2489 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2490 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2491 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
2492 ||', Payment_num = '||TO_CHAR(P_payment_num)
2493 ||', Check_date = '||TO_CHAR(P_check_date)
2494 ||', sys_auto_calc_int_flag = '||P_sys_auto_calc_int_flag
2495 ||', auto_calculate_interest_flag = '||
2496 P_auto_calculate_interest_flag
2497 ||', Amount_remaining = '||TO_CHAR(P_amount_remaining)
2498 ||', Discount_taken = '||TO_CHAR(P_discount_taken)
2499 ||', Discount_available = '||TO_CHAR(P_discount_available)
2500 ||', Currency_code = '||P_currency_code);
2501 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2502 END IF;
2503
2504 APP_EXCEPTION.RAISE_EXCEPTION;
2505
2506 END ap_calculate_interest;
2507
2508 PROCEDURE ap_pay_insert_invoice_payments(
2509 P_reference_invoice_id IN NUMBER,
2510 P_reference_invoice_num IN VARCHAR2,
2511 P_reference_nls_int IN VARCHAR2,
2512 P_checkrun_name IN VARCHAR2,
2513 P_vendor_id IN NUMBER,
2514 P_vendor_site_id IN NUMBER,
2515 P_vendor_num IN VARCHAR2,
2516 P_vendor_name IN VARCHAR2,
2517 P_vendor_site_code IN VARCHAR2,
2518 P_address_line1 IN VARCHAR2,
2519 P_address_line2 IN VARCHAR2,
2520 P_address_line3 IN VARCHAR2,
2521 P_city IN VARCHAR2,
2522 P_state IN VARCHAR2,
2523 P_zip IN VARCHAR2,
2524 P_voucher_num IN VARCHAR2,
2525 P_ap_ccid IN NUMBER,
2526 P_payment_priority IN NUMBER,
2527 P_province IN VARCHAR2,
2528 P_country IN VARCHAR2,
2529 P_withholding_status_lookup IN VARCHAR2,
2530 P_attention_ar_flag IN VARCHAR2,
2531 P_set_of_books_id IN NUMBER,
2532 P_invoice_exchange_rate IN NUMBER,
2533 P_payment_cross_rate IN NUMBER,
2534 P_customer_num IN VARCHAR2,
2535 P_payment_num IN NUMBER,
2536 P_last_update_date IN DATE,
2537 P_last_updated_by IN NUMBER,
2538 P_creation_date IN DATE,
2539 P_created_by IN NUMBER,
2540 P_invoice_date IN DATE,
2541 P_invoice_amount IN NUMBER,
2542 P_amount_remaining IN NUMBER,
2543 P_amount_paid IN NUMBER,
2544 P_discount_amount_taken IN NUMBER,
2545 P_due_date IN DATE,
2546 P_invoice_description IN VARCHAR2,
2547 P_discount_amount_remaining IN NUMBER,
2548 P_payment_amount IN NUMBER,
2549 P_proposed_payment_amount IN NUMBER,
2550 P_discount_amount IN NUMBER,
2551 P_ok_to_pay_flag IN VARCHAR2,
2552 P_always_take_discount_flag IN VARCHAR2,
2553 P_amount_modified_flag IN VARCHAR2,
2554 P_original_invoice_id IN VARCHAR2,
2555 P_bank_account_num IN VARCHAR2,
2556 P_bank_account_type IN VARCHAR2,
2557 P_bank_num IN VARCHAR2,
2558 P_original_payment_num IN NUMBER,
2559 P_sequence_num IN NUMBER,
2560 P_pay_selected_check_id IN NUMBER,
2561 P_calling_sequence IN VARCHAR2,
2562 P_org_id IN NUMBER DEFAULT NULL,
2563 P_last_update_login IN NUMBER DEFAULT NULL) IS
2564
2565 current_calling_sequence VARCHAR2(2000);
2566 debug_info VARCHAR2(100);
2567 C_int_num1 number:=0;
2568 C_int_num2 number:=0;
2569 C_invoice_num varchar2(50);
2570 C_invoice_id number;
2571 BEGIN
2572
2573 -- Interest Invoices project - Invoice Lines
2574 -- Add parameters to signature and INSERT Statement
2575 -- P_org_id
2576 -- P_last_update_login
2577 -- Remove SELECT org_id ..
2578 -- Remove SELECT NEXTVAL from Dual and SELECT NEXTVAL directly
2579 -- at INSERT time in the INSERT Statement.
2580
2581 current_calling_sequence :=
2582 'AP_INTEREST_INVOICE_PKG.ap_pay_interest_invoice_payments<-'
2583 || P_calling_sequence;
2584
2585 -- populating values later used to create interest invoice.
2586
2587 debug_info := 'Count from ap_selected_invoices for P_reference_invoice_id';
2588
2589 BEGIN
2590 SELECT COUNT(*)
2591 INTO C_int_num1
2592 FROM ap_selected_invoices
2593 -- CHANGES FOR BUG - 3293874 ** STARTS **
2594 --WHERE original_invoice_id = P_reference_invoice_id;
2595 WHERE original_invoice_id = to_char(P_reference_invoice_id);
2596 -- CHANGES FOR BUG - 3293874 ** ENDS **
2597
2598 EXCEPTION
2599 WHEN NO_DATA_FOUND then null;
2600 END;
2601
2602 debug_info :=
2603 'Count from ap_invoice_relationships for P_reference_invoice_id';
2604
2605 BEGIN
2606 SELECT count(*)
2607 INTO C_int_num2
2608 FROM ap_invoice_relationships
2609 WHERE original_invoice_id = P_reference_invoice_id;
2610
2611 EXCEPTION
2612 WHEN No_Data_Found THEN NULL;
2613 END;
2614
2615 -- Insert interest invoice here.
2616 -- calculate invoice num info
2617
2618 debug_info := 'Calculating invoice num ';
2619
2620 C_invoice_num := substrb(P_reference_invoice_num,
2621 1,(50 - LENGTHB('-' || P_reference_nls_int ||
2622 to_char(nvl(c_int_num1,0) +
2623 nvl(C_int_num2,0)+1)))) || '-' ||
2624 P_reference_nls_int || to_char(nvl(C_int_num1,0) +
2625 nvl(C_int_num2,0)+1);
2626
2627 debug_info := 'Insert ap_selected_invoices';
2628
2629 INSERT INTO AP_SELECTED_INVOICES (
2630 checkrun_name,
2631 invoice_id,
2632 vendor_id,
2633 vendor_site_id,
2634 vendor_num,
2635 vendor_name,
2636 vendor_site_code,
2637 address_line1,
2638 address_line2,
2639 address_line3,
2640 city,
2641 state,
2642 zip,
2643 invoice_num,
2644 voucher_num,
2645 ap_ccid,
2646 payment_priority,
2647 province,country,
2648 withholding_status_lookup_code,
2649 attention_ar_flag,
2650 set_of_books_id,
2651 invoice_exchange_rate,
2652 payment_cross_rate,
2653 customer_num,
2654 payment_num,
2655 last_update_date,
2656 last_updated_by,
2657 creation_date,
2658 created_by,
2659 invoice_date,
2660 invoice_amount,
2661 amount_remaining,
2662 amount_paid,
2663 discount_amount_taken,
2664 due_date,
2665 invoice_description,
2666 discount_amount_remaining,
2667 payment_amount,
2668 proposed_payment_amount,
2669 discount_amount,
2670 ok_to_pay_flag,
2671 always_take_discount_flag,
2672 amount_modified_flag,
2673 original_invoice_id,
2674 bank_account_num,
2675 bank_account_type,
2676 bank_num,
2677 original_payment_num,
2678 sequence_num,
2679 pay_selected_check_id,
2680 org_id,
2681 last_update_login)
2682 VALUES (
2683 P_checkrun_name,
2684 ap_invoices_s.NEXTVAL,
2685 P_vendor_id,
2686 P_vendor_site_id,
2687 P_vendor_num,
2688 P_vendor_name,
2689 P_vendor_site_code,
2690 P_address_line1,
2691 P_address_line2,
2692 P_address_line3,
2693 P_city,
2694 P_state,
2695 P_zip,
2696 C_invoice_num,
2697 P_voucher_num,
2698 P_ap_ccid,
2699 P_payment_priority,
2700 P_province,
2701 P_country,
2702 P_withholding_status_lookup,
2703 P_attention_ar_flag,
2704 P_set_of_books_id,
2705 P_invoice_exchange_rate,
2706 P_payment_cross_rate,
2707 P_customer_num,
2708 P_payment_num,
2709 P_last_update_date,
2710 P_last_updated_by,
2711 P_creation_date,
2712 P_created_by,
2713 P_invoice_date,
2714 P_invoice_amount,
2715 P_amount_remaining,
2716 P_amount_paid,
2717 P_discount_amount_taken,
2718 P_due_date,
2719 P_invoice_description,
2720 P_discount_amount_remaining,
2721 P_payment_amount,
2722 P_proposed_payment_amount,
2723 P_discount_amount,
2724 P_ok_to_pay_flag,
2725 P_always_take_discount_flag,
2726 P_amount_modified_flag,
2727 P_original_invoice_id,
2728 P_bank_account_num,
2729 P_bank_account_type,
2730 P_bank_num,
2731 P_original_payment_num,
2732 P_sequence_num,
2733 P_pay_selected_check_id,
2734 P_org_id,
2735 P_last_update_login);
2736 EXCEPTION
2737 WHEN OTHERS THEN
2738 IF (SQLCODE <> -20001 ) THEN
2739 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2740 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2741 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2742 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(C_invoice_id)
2743 ||', Checkrun_name = '||P_Checkrun_name
2744 ||', Vendor_id = '||TO_CHAR(P_Vendor_id)
2745 ||', Vendor_site_id = '||TO_CHAR(P_Vendor_site_id)
2746 ||', Vendor_num = '||P_Vendor_num
2747 ||', Vendor_name = '||P_Vendor_name
2748 ||', Vendor_site_code = '||P_Vendor_site_code
2749 ||', Payment_num = '||TO_CHAR(P_payment_num)
2750 ||', Invoice_num = '||C_Invoice_num
2751 ||', Voucher_num = '||P_Voucher_num
2752 ||', Customer_num = '||P_Customer_num
2753 ||', Invoice_description = '||P_Invoice_description
2754 ||', sequence_num = '||P_sequence_num);
2755 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2756 END IF;
2757
2758 APP_EXCEPTION.RAISE_EXCEPTION;
2759
2760 END ap_pay_insert_invoice_payments;
2761
2762 /* -----------------------------------------------------------------------
2763 This procedure is called from appbii.lpc (CONFIRM Program). This program
2764 is called to prorate Interest Invoice Distributions. Please see HLD/DLD
2765 for further details
2766 ----------------------------------------------------------------------- */
2767
2768 PROCEDURE ap_create_batch_interest_dists(
2769 P_checkrun_name IN VARCHAR2,
2770 P_base_currency_code IN VARCHAR2,
2771 P_interest_accts_pay_ccid IN NUMBER,
2772 P_last_updated_by IN NUMBER,
2773 P_period_name IN VARCHAR2,
2774 P_asset_account_flag IN VARCHAR2,
2775 P_calling_sequence IN VARCHAR2,
2776 p_checkrun_id in number,
2777 p_completed_pmts_group_id in number,
2778 p_org_id in number) IS
2779
2780 CURSOR c_select_interest_invoices is
2781 SELECT new.invoice_id P_int_invoice_id,
2782 new.due_date P_accounting_date,
2783 pv.vendor_id P_vendor_id,
2784 orig.invoice_num P_old_invoice_num,
2785 new.invoice_num P_int_invoice_num,
2786 new.payment_amount P_interest_amount,
2787 decode(orig.invoice_currency_code, P_base_currency_code, NULL,
2788 decode(base.minimum_accountable_unit, null,
2789 round(new.payment_amount / orig.payment_cross_rate *
2790 --bug 8899917 use new.payment_exchange_rate
2791 nvl(new.payment_exchange_rate,1), base.precision),
2792 round( ((new.payment_amount / orig.payment_cross_rate *
2793 nvl(new.payment_exchange_rate,1)) /
2794 base.minimum_accountable_unit) *
2795 base.minimum_accountable_unit ) ) )
2796 P_interest_base_amount,
2797 orig.set_of_books_id P_set_of_books_id,
2798 orig.payment_cross_rate P_payment_cross_rate,
2799 --bug 8899917 use new.payment_exchange_rate
2800 new.payment_exchange_rate P_exchange_rate,
2801 new.payment_exchange_rate_type P_exchange_rate_type,
2802 --bug 8899917 new.exchange_rate_date is not reliably
2803 --populated so use ibydocs.payment_date
2804 ibydocs.payment_date P_exchange_date,
2805 orig.invoice_id P_invoice_id,
2806 orig.invoice_currency_code P_invoice_currency_code,
2807 orig.org_id P_org_id
2808 FROM po_vendors pv,
2809 ap_invoices_all orig,
2810 ap_selected_invoices_all new,
2811 fnd_currencies base,
2812 fnd_currencies fcinv,
2813 iby_fd_docs_payable_v ibydocs
2814 WHERE new.original_invoice_id = orig.invoice_id --4346023, reverted 3293874
2815 AND new.vendor_id = pv.vendor_id
2816 AND new.checkrun_name = p_checkrun_name
2817 AND new.checkrun_id = p_checkrun_id
2818 AND base.currency_code = p_base_currency_code
2819 AND fcinv.currency_code = orig.invoice_currency_code
2820 and ibydocs.calling_app_doc_unique_ref1 = new.checkrun_id
2821 AND ibydocs.calling_app_doc_unique_ref2 = new.invoice_id
2822 AND ibydocs.calling_app_doc_unique_ref3 = new.payment_num
2823 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
2824 and ibydocs.org_id = p_org_id
2825 and new.org_id = p_org_id;
2826
2827
2828 rec_select_int_invoices c_select_interest_invoices%rowtype;
2829 debug_info VARCHAR2(100);
2830 l_login_id ap_invoice_distributions.last_update_login%TYPE;
2831
2832 BEGIN
2833
2834 -- Interest Invoices project - Invoice Lines
2835 -- Add l_login_id and pass it to ap_int_inv_insert_ap_inv_dist.
2836 -- Add P_org_id and pass it to ap_int_inv_insert_ap_inv_dist.
2837
2838 l_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
2839
2840 debug_info := 'Fetch from cursor c_select_interest_invoices';
2841
2842 OPEN c_select_interest_invoices;
2843
2844 LOOP
2845 FETCH c_select_interest_invoices into rec_select_int_invoices;
2846
2847 EXIT WHEN c_select_interest_invoices%NOTFOUND;
2848
2849 AP_INTEREST_INVOICE_PKG.ap_int_inv_insert_ap_inv_dist(
2850 rec_select_int_invoices.P_int_invoice_id,
2851 rec_select_int_invoices.P_accounting_date,
2852 rec_select_int_invoices.P_vendor_id,
2853 rec_select_int_invoices.P_old_invoice_num,
2854 rec_select_int_invoices.P_int_invoice_num,
2855 rec_select_int_invoices.P_interest_amount,
2856 rec_select_int_invoices.P_interest_base_amount,
2857 P_period_name,
2858 rec_select_int_invoices.P_set_of_books_id,
2859 P_last_updated_by,
2860 P_interest_accts_pay_ccid,
2861 P_asset_account_flag,
2862 rec_select_int_invoices.p_payment_cross_rate,
2863 rec_select_int_invoices.P_exchange_rate,
2864 rec_select_int_invoices.P_exchange_rate_type,
2865 rec_select_int_invoices.P_exchange_date,
2866 null,
2867 'PAYMENTBATCH',
2868 null,
2869 rec_select_int_invoices.P_invoice_id,
2870 P_calling_sequence,
2871 rec_select_int_invoices.P_invoice_currency_code,
2872 P_base_currency_code,
2873 NULL,
2874 NULL,
2875 rec_select_int_invoices.P_org_id,
2876 l_login_id);
2877
2878 END LOOP;
2879 CLOSE c_select_interest_invoices;
2880
2881 END ap_create_batch_interest_dists;
2882
2883 END AP_INTEREST_INVOICE_PKG;