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