[Home] [Help]
PACKAGE BODY: APPS.AP_AUTOSELECT_PKG
Source
1 PACKAGE BODY AP_AUTOSELECT_PKG AS
2 /* $Header: appbselb.pls 120.53.12010000.10 2009/02/19 13:11:05 udhenuko ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_AUTOSELECT_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP.PLSQL.AP_AUTOSELECT_PKG';
21
22
23 --Bug6459578
24
25 PROCEDURE awt_special_rounding(p_checkrun_name in varchar2,p_calling_sequence in varchar2) IS
26
27 l_debug_info VARCHAR2(200);
28 l_current_calling_sequence VARCHAR2(2000);
29
30 BEGIN
31 -- Update the calling sequence
32
33 l_current_calling_sequence := p_calling_sequence || '<-awt_special_rounding';
34 l_debug_info := 'Call AP_CUSTOM_WITHHOLDING_PKG.AP_SPECIAL_ROUNDING';
35
36
37 AP_CUSTOM_WITHHOLDING_PKG.AP_SPECIAL_ROUNDING(p_checkrun_name);
38
39
40 EXCEPTION
41 WHEN OTHERS then
42
43 IF (SQLCODE <> -20001) THEN
44 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
45 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
46 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
47 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
48 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_name: '||
49 p_checkrun_name);
50
51 END IF;
52 APP_EXCEPTION.RAISE_EXCEPTION;
53
54 END;
55
56
57 PROCEDURE calculate_interest (p_checkrun_id in number,
58 p_checkrun_name in varchar2,
59 p_check_date in date,
60 p_calling_sequence in varchar2) is
61
62
63 CURSOR interest_cursor IS
64 SELECT ASI.invoice_id, ASI.payment_num, ASI.vendor_id,
65 ASI.vendor_site_id, ASI.vendor_num, ASI.vendor_name,
66 ASI.vendor_site_code, ASI.address_line1, ASI.address_line2,
67 ASI.address_line3, ASI.address_line4, ASI.city, ASI.state, ASI.zip,
68 ASI.invoice_num, ASI.voucher_num,
69 -- ASI.payment_priority, -- Bug 5139574
70 nvl(ASI.payment_priority, 99), ASI.province,
71 ASI.country, ASI.withholding_status_lookup_code,
72 ASI.attention_ar_flag, ASI.set_of_books_id,
73 ASI.invoice_exchange_rate, ASI.payment_cross_rate,
74 ASI.customer_num, asi.external_bank_account_id, ASI.ok_to_pay_flag,
75 round(LEAST(TRUNC(P_check_date),ADD_MONTHS(TRUNC(due_date),12))
76 - TRUNC(due_date)), /*Bug 5124784 */
77 annual_interest_rate,
78 AI.invoice_currency_code,
79 ASI.payment_currency_code,
80 /* bug 5233279. For Federal Installation Exclusive payment Flag is required */
81 decode(Ap_Payment_Util_Pkg.is_federal_installed(AI.org_id),
82 'Y', AI.exclusive_payment_flag, 'N'),
83 asp.interest_accts_pay_ccid,
84 ai.org_id
85 FROM ap_interest_periods,
86 ap_invoices AI, --Bug6040657. Changed from ap_invoices_all to ap_invoices
87 ap_selected_invoices_all ASI,
88 po_vendors pov,
89 ap_system_parameters_all asp
90 WHERE ASI.checkrun_id = P_checkrun_id
91 AND ASP.auto_calculate_interest_flag = 'Y'
92 AND ASP.org_id = asi.org_id
93 AND TRUNC(P_check_date) > TRUNC(due_date)
94 AND (trunc(due_date)+1) BETWEEN trunc(start_date) AND trunc(end_date)
95 AND (NVL(payment_amount,0) *
96 POWER(1 + (annual_interest_rate/(12 * 100)),
97 TRUNC((LEAST(P_check_date,
98 ADD_MONTHS(due_date,12))
99 - due_date) / 30))
100 *
101 (1 + ((annual_interest_rate/(360 * 100)) *
102 MOD((LEAST(P_check_date,
103 ADD_MONTHS(due_date,12))
104 - due_date)
105 , 30))))
106 - NVL(payment_amount,0) >= NVL(asp.interest_tolerance_amount,0)
107 AND ASI.vendor_id = pov.vendor_id
108 AND pov.auto_calculate_interest_flag = 'Y'
109 AND AI.invoice_id = ASI.invoice_id
110 AND AI.invoice_type_lookup_code <> 'PAYMENT REQUEST';
111
112
113 l_address_line1 po_vendor_sites_all.address_line1%TYPE;
114 l_address_line2 po_vendor_sites_all.address_line2%TYPE;
115 l_address_line3 po_vendor_sites_all.address_line3%TYPE;
116 l_address_line4 po_vendor_sites_all.address_line4%TYPE;
117 l_amount_remaining NUMBER;
118 l_attention_ar_flag VARCHAR2(1);
119 l_awt_status_lookup_code VARCHAR2(25);
120 l_city ap_selected_invoices_all.city%type; --6708281
121 l_country ap_selected_invoices_all.country%type; --6708281
122 -- l_city VARCHAR2(25);
123 -- l_country VARCHAR2(25);
124 l_current_calling_sequence varchar2(2000);
125 l_customer_num VARCHAR2(25);
126 l_debug_info varchar2(200);
127 l_discount_available NUMBER;
128 l_discount_taken NUMBER;
129 l_due_date date;
130 l_exclusive_payment_flag VARCHAR2(1);
131 l_existing_interest_count NUMBER;
132 l_external_bank_account_id number;
133 l_int_invoice_amt NUMBER;
134 l_int_invoice_days NUMBER;
135 l_int_invoice_no varchar2(50);
136 l_int_invoice_num VARCHAR2(50);
137 l_int_invoice_rate NUMBER;
138 l_int_vendor_name po_vendors.vendor_name%TYPE;
139 l_int_vendor_num VARCHAR2(30);
140 l_interest_ap_ccid number;
141 l_inv_curr_int_amt NUMBER;
142 l_invoice_currency_code VARCHAR2(15);
143 l_invoice_exchange_rate NUMBER;
144 l_invoice_id number;
145 l_nls_days ap_lookup_codes.displayed_field%TYPE;
146 l_nls_int ap_lookup_codes.displayed_field%TYPE;
147 l_nls_interest ap_lookup_codes.displayed_field%TYPE;
148 l_nls_percent ap_lookup_codes.displayed_field%TYPE;
149 l_ok_to_pay_flag VARCHAR2(1);
150 l_org_id number;
151 l_pay_currency_code VARCHAR2(15);
152 l_payment_amount NUMBER;
153 l_payment_cross_rate NUMBER;
154 l_payment_num number;
155 l_payment_priority NUMBER(2);
156 l_proposed_interest_count NUMBER;
157 l_province VARCHAR2(150);
158 l_set_of_books_id number;
159 l_site_code po_vendor_sites_all.vendor_site_code%TYPE;
160 l_site_id NUMBER(15);
161 l_state VARCHAR2(150);
162 l_vendor_id number;
163 l_voucher_num VARCHAR2(50);
164 -- l_zip VARCHAR2(20);
165 l_zip ap_selected_invoices_all.zip%type; --6708281
166
167
168 BEGIN
169
170 l_debug_info := 'Perform Interest calculations';
171 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
172 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
173 END IF;
174
175 l_current_calling_sequence := p_calling_sequence || '<-calculate_interest';
176
177
178 SELECT substrb(l1.displayed_field, 1, 25),
179 substrb(l2.displayed_field, 1, 10),
180 substrb(l3.displayed_field, 1, 5),
181 substrb(l4.displayed_field, 1, 25)
182 INTO l_nls_interest,
183 l_nls_days,
184 l_nls_percent,
185 l_nls_int
186 FROM ap_lookup_codes l1,
187 ap_lookup_codes l2,
188 ap_lookup_codes l3,
189 ap_lookup_codes l4
190 WHERE l1.lookup_type = 'NLS TRANSLATION'
191 AND l1.lookup_code = 'INTEREST'
192 AND l2.lookup_type = 'NLS TRANSLATION'
193 AND l2.lookup_code = 'DAYS'
194 AND l3.lookup_type = 'NLS TRANSLATION'
195 AND l3.lookup_code = 'PERCENT'
196 AND l4.lookup_type = 'NLS TRANSLATION'
197 AND l4.lookup_code = 'INT';
198
199
200
201
202
203 --interest calculations
204
205 OPEN interest_cursor;
206
207 LOOP
208
209 FETCH interest_cursor
210 INTO l_invoice_id, l_payment_num, l_vendor_id,
211 l_site_id, l_int_vendor_num, l_int_vendor_name,
212 l_site_code, l_address_line1, l_address_line2,
213 l_address_line3, l_address_line4, l_city, l_state, l_zip, l_int_invoice_num,
214 l_voucher_num, l_payment_priority, l_province,
215 l_country, l_awt_status_lookup_code,
216 l_attention_ar_flag,
217 l_set_of_books_id,
218 l_invoice_exchange_rate,
219 l_payment_cross_rate,
220 l_customer_num,
221 l_external_bank_account_id,
222 l_ok_to_pay_flag,
223 l_int_invoice_days,
224 l_int_invoice_rate,
225 l_invoice_currency_code,
226 l_pay_currency_code,
227 l_exclusive_payment_flag,
228 l_interest_ap_ccid,
229 l_org_id;
230
231 EXIT WHEN interest_cursor%NOTFOUND or interest_cursor%NOTFOUND is NULL;
232
233
234
235 SELECT nvl(payment_amount,0), nvl(amount_remaining,0),
236 nvl(discount_amount,0),nvl(discount_amount_remaining,0)
237 INTO l_payment_amount, l_amount_remaining,
238 l_discount_taken, l_discount_available
239 FROM ap_selected_invoices_all asi
240 WHERE asi.invoice_id = l_invoice_id
241 AND asi.payment_num = l_payment_num
242 and asi.checkrun_id = p_checkrun_id;
243
244
245
246 l_debug_info := 'Calling ap_interest_invoice_pkg';
247 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
248 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
249 END IF;
250
251
252
253 ap_interest_invoice_pkg.ap_calculate_interest(
254 P_INVOICE_ID =>l_invoice_id,
255 P_SYS_AUTO_CALC_INT_FLAG =>'Y', --should always be "Y" b/c of cursor where clause
256 P_AUTO_CALCULATE_INTEREST_FLAG =>'Y', --should always be "Y" b/c of cursor where clause
257 P_CHECK_DATE =>P_check_date,
258 P_PAYMENT_NUM =>l_payment_num,
259 P_AMOUNT_REMAINING =>l_amount_remaining,
260 P_DISCOUNT_TAKEN =>l_discount_taken,
261 P_DISCOUNT_AVAILABLE =>l_discount_available,
262 P_CURRENCY_CODE =>l_pay_currency_code,
263 P_INTEREST_AMOUNT =>l_int_invoice_amt,
264 P_DUE_DATE =>l_due_date,
265 P_INTEREST_INVOICE_NUM =>l_int_invoice_no,
266 P_PAYMENT_AMOUNT =>l_payment_amount,
267 P_CALLING_SEQUENCE =>l_current_calling_sequence);
268
269 l_debug_info := 'interest invoice amount = '||to_char(l_int_invoice_amt);
270 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
271 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
272 END IF;
273
274
275 IF (l_int_invoice_amt <> 0) THEN
276
277 SELECT count(*)
278 INTO l_existing_interest_count
279 FROM ap_invoice_relationships
280 WHERE original_invoice_id = l_invoice_id;
281
282
283 SELECT count(*)
284 INTO l_proposed_interest_count
285 FROM ap_selected_invoices
286 WHERE original_invoice_id = to_char(l_invoice_id); --4388916
287
288
289 l_inv_curr_int_amt := ap_utilities_pkg.ap_round_currency(
290 l_int_invoice_amt / l_payment_cross_rate,
291 l_invoice_currency_code);
292
293 INSERT INTO ap_selected_invoices_all
294 (checkrun_name,
295 invoice_id,
296 vendor_id,
297 vendor_site_id,
298 vendor_num,
299 vendor_name,
300 vendor_site_code,
301 address_line1,
302 address_line2,
303 address_line3,
304 address_line4,
305 city,
306 state,
307 zip,
308 invoice_num,
309 voucher_num,
310 ap_ccid,
311 payment_priority,
312 province,
313 country,
314 withholding_status_lookup_code,
315 attention_ar_flag,
316 set_of_books_id,
317 invoice_exchange_rate,
318 payment_cross_rate,
319 customer_num,
320 payment_num,
321 last_update_date,
322 last_updated_by,
323 invoice_date,
324 invoice_amount,
325 amount_remaining,
326 amount_paid,
327 discount_amount_taken,
328 due_date,
329 invoice_description,
330 discount_amount_remaining,
331 payment_amount,
332 proposed_payment_amount,
333 discount_amount,
334 ok_to_pay_flag,
335 always_take_discount_flag,
336 amount_modified_flag,
337 original_invoice_id,
338 original_payment_num,
339 creation_date,
340 created_by,
341 exclusive_payment_flag,
342 org_id,
343 external_bank_account_id,
344 checkrun_id,
345 payment_currency_code)
346 SELECT
347 P_checkrun_name,
348 ap_invoices_s.NEXTVAL,
349 l_vendor_id,
350 l_site_id,
351 l_int_vendor_num,
352 l_int_vendor_name,
353 l_site_code,
354 l_address_line1,
355 l_address_line2,
356 l_address_line3,
357 l_address_line4,
358 l_city,
359 l_state,
360 l_zip,
361 SUBSTRB(SUBSTRB(l_int_invoice_num,
362 1,(50 - LENGTHB('-' || l_nls_int ||
363 TO_CHAR(l_existing_interest_count +
364 l_proposed_interest_count + 1))))
365 || '-' || l_nls_int || TO_CHAR(l_existing_interest_count +
366 l_proposed_interest_count + 1),1,50),
367 l_voucher_num,
368 l_interest_ap_ccid,
369 l_payment_priority,
370 l_province,
371 l_country,
372 l_awt_status_lookup_code,
373 l_attention_ar_flag,
374 l_set_of_books_id,
375 l_invoice_exchange_rate,
376 l_payment_cross_rate,
377 l_customer_num,
378 1,
379 SYSDATE,
380 -- Bug 7383484 (Base bug 7296715)
381 -- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
382 -- '5',
383 FND_GLOBAL.USER_ID,
384 p_check_date,
385 l_inv_curr_int_amt,
386 l_int_invoice_amt,
387 0,
388 0,
389 p_check_date,
390 SUBSTRB(l_nls_interest|| ' ' || to_char(l_int_invoice_days)
391 || ' ' || l_nls_days || to_char(l_int_invoice_rate)
392 || l_nls_percent,1,50),
393 0,
394 l_int_invoice_amt,
395 l_int_invoice_amt,
396 0,
397 l_ok_to_pay_flag,
398 'N',
399 'N',
400 l_invoice_id,
401 l_payment_num,
402 SYSDATE,
403 -- Bug 7383484 (Base bug 7296715)
404 --'5',
405 FND_GLOBAL.USER_ID,
406 l_exclusive_payment_flag,
407 l_org_id,
408 l_external_bank_account_id,
409 p_checkrun_id,
410 l_pay_currency_code
411 FROM sys.dual;
412
413 END IF;
414
415 END LOOP;
416
417 CLOSE interest_cursor;
418
419 exception
420 WHEN OTHERS then
421
422 IF (SQLCODE <> -20001) THEN
423 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
424 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
425 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
426 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
427 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_id: '||
428 to_char(p_checkrun_id));
429
430 END IF;
431 APP_EXCEPTION.RAISE_EXCEPTION;
432
433 END;
434
435
436
437
438
439 PROCEDURE remove_all_invoices (p_checkrun_id in number,
440 p_calling_sequence in varchar2) IS
441 l_debug_info varchar2(2000);
442 l_current_calling_sequence varchar2(2000);
443
444 begin
445
446 l_current_calling_sequence := p_calling_sequence || '<- remove_all_invoices';
447 l_debug_info := 'delete unselected invoices';
448
449 delete from ap_unselected_invoices_all
450 where checkrun_id = p_checkrun_id;
451
452
453 l_debug_info := 'deleted selected invoices';
454
455 delete from ap_selected_invoices_all
456 where checkrun_id = p_checkrun_id;
457
458
459 l_debug_info := 'update payment schedules';
460
461 update ap_payment_schedules_all
462 set checkrun_id = null
463 where checkrun_id = p_checkrun_id;
464
465
466 exception
467 WHEN OTHERS then
468
469 IF (SQLCODE <> -20001) THEN
470 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
471 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
472 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
473 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
474 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_id: '||
475 to_char(p_checkrun_id));
476
477 END IF;
478 APP_EXCEPTION.RAISE_EXCEPTION;
479 end;
480
481
482
483
484
485
486
487 PROCEDURE remove_invoices (p_checkrun_id in number,
488 p_calling_sequence in varchar2) IS
489
490 cursor c_dont_pay_invoices (p_checkrun_id number) is
491 --make sure we seed dont pay reason codes
492 select invoice_id, payment_num, dont_pay_reason_code, org_id
493 from ap_selected_invoices_all
494 where checkrun_id = p_checkrun_id
495 and ok_to_pay_flag = 'N';
496
497 l_debug_info varchar2(2000);
498 l_current_calling_sequence varchar2(2000);
499 TYPE r_remove_invoices IS RECORD (invoice_id number(15),
500 payment_num number(15),
501 dont_pay_reason_code varchar2(25),
502 org_id number(15));
503
504 type t_remove_invoices is table of r_remove_invoices index by binary_integer;
505 l_remove_invoices t_remove_invoices;
506
507
508 begin
509
510
511
512 l_current_calling_sequence := p_calling_sequence || '<-remove invoices';
513
514
515 l_debug_info := 'Start of remove_invoices';
516 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
517 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
518 END IF;
519
520
521
522 OPEN c_dont_pay_invoices(p_checkrun_id);
523 FETCH c_dont_pay_invoices BULK COLLECT INTO l_remove_invoices;
524 CLOSE c_dont_pay_invoices;
525
526
527
528
529 if l_remove_invoices.count > 0 then
530 for i in l_remove_invoices.first .. l_remove_invoices.last loop
531
532 insert into ap_unselected_invoices_all(
533 checkrun_id,
534 invoice_id,
535 payment_num,
536 dont_pay_reason_code,
537 last_update_date,
538 last_updated_by,
539 created_by,
540 creation_date,
541 org_id)
542 values(
543 p_checkrun_id,
544 l_remove_invoices(i).invoice_id,
545 l_remove_invoices(i).payment_num ,
546 l_remove_invoices(i).dont_pay_reason_code,
547 sysdate,
548 5,
549 5,
550 sysdate,
551 l_remove_invoices(i).org_id);
552
553
554
555 update ap_payment_schedules_all
556 set checkrun_id = null
557 where invoice_id = l_remove_invoices(i).invoice_id
558 and payment_num = l_remove_invoices(i).payment_num
559 and checkrun_id = p_checkrun_id;
560
561 delete from ap_selected_invoices_all
562 where invoice_id = l_remove_invoices(i).invoice_id
563 and payment_num = l_remove_invoices(i).payment_num
564 and checkrun_id = p_checkrun_id;
565
566 end loop;
567 end if;
568 exception
569 WHEN OTHERS then
570
571 IF (SQLCODE <> -20001) THEN
572 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
573 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
574 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
575 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
576 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_id: '||
577 to_char(p_checkrun_id));
578
579 END IF;
580 APP_EXCEPTION.RAISE_EXCEPTION;
581 end;
582
583
584
585
586
587
588
589 PROCEDURE insert_unselected(p_payment_process_request_name in VARCHAR2,
590 p_hi_payment_priority in number,
591 p_low_payment_priority in number,
592 p_invoice_batch_id in number,
593 p_inv_vendor_id in number,
594 p_inv_exchange_rate_type in varchar2,
595 p_payment_method in varchar2,
596 p_supplier_type in varchar2,
597 p_le_group_option in varchar2,
598 p_ou_group_option in varchar2,
599 p_currency_group_option in varchar2,
600 p_pay_group_option in varchar2,
601 p_zero_invoices_allowed in varchar2,
602 p_check_date in date,
603 p_checkrun_id in number,
604 p_current_calling_sequence in varchar2,
605 p_party_id in number
606 ) IS
607
608 cursor unselected_invoices is
609 SELECT /*+NO_EXPAND */ inv.invoice_id,
610 ps.payment_num,
611 ps.hold_flag,
612 sites.hold_all_payments_flag,
613 ap_utilities_pkg.get_invoice_status(inv.invoice_id, null),
614 inv.wfapproval_status,
615 inv.org_id,
616 ps.due_date,
617 ps.discount_amount_available,
618 ps.discount_date
619 FROM ap_supplier_sites_all sites,
620 ap_invoices inv, --Bug6040657. Changed from ap_invoices_all to ap_invoices
621 ap_payment_schedules ps,
622 ap_suppliers suppliers,
623 hz_parties hz
624 where inv.invoice_id = ps.invoice_id
625 AND sites.vendor_site_id(+) = inv.vendor_site_id
626 AND suppliers.vendor_id(+) = inv.vendor_id
627 AND inv.party_id = hz.party_id
628 AND ps.payment_status_flag BETWEEN 'N' AND 'P'
629 AND inv.payment_status_flag BETWEEN 'N' AND 'P'
630 AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
631 AND p_low_payment_priority
632 AND inv.cancelled_date is null
633 -- Bug 5649608
634 --AND nvl(inv.batch_id,-99) = nvl(p_invoice_batch_id,-99)
635 AND (p_invoice_batch_id IS NULL
636 OR(p_invoice_batch_id IS NOT NULL AND
637 inv.batch_id = p_invoice_batch_id))
638 AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
639 AND inv.party_id = nvl(p_party_id, inv.party_id)
640 -- Bug 5507013 hkaniven start --
641 AND (( p_inv_exchange_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
642 OR (p_inv_exchange_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
643 OR (p_inv_exchange_rate_type IS NULL))
644 -- Bug 5507013 hkaniven end --
645 AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
646 AND nvl(suppliers.vendor_type_lookup_code,-99) =
647 nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
648 AND (inv.legal_entity_id in (select legal_entity_id
649 from ap_le_group
650 where checkrun_id = p_checkrun_id)
651 or p_le_group_option = 'ALL')
652 AND (inv.org_id in (select org_id
653 from AP_OU_GROUP
654 where checkrun_id = p_checkrun_id)
655 or p_ou_group_option = 'ALL')
656 AND (inv.payment_currency_code in (select currency_code
657 from AP_CURRENCY_GROUP
658 where checkrun_id = p_checkrun_id)
659 or p_currency_group_option = 'ALL')
660 AND (inv.pay_group_lookup_code in (select vendor_pay_group
661 from AP_PAY_GROUP
662 where checkrun_id = p_checkrun_id)
663 or p_pay_group_option = 'ALL')
664 AND ((p_zero_invoices_allowed = 'N' AND ps.amount_remaining <> 0) OR
665 p_zero_invoices_allowed = 'Y')
666 and ps.checkrun_id is null;
667
668
669
670
671
672
673
674
675 l_invoice_id number;
676 l_payment_num number;
677 l_invoice_status varchar2(50);
678 l_approval_status varchar2(50);
679 l_ps_hold_flag varchar2(1);
680 l_hold_all_payments_flag varchar2(1);
681 l_current_calling_sequence varchar2(2000);
682 l_debug_info varchar2(2000);
683 l_org_id number(15);
684 l_due_date date;
685 l_discount_amount_available number;
686 l_discount_date date;
687
688
689
690 BEGIN
691
692
693 l_current_calling_sequence := p_current_calling_sequence||'<- insert unselected';
694
695 l_debug_info := 'open unselected_invoices';
696 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
697 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
698 END IF;
699
700
701 open unselected_invoices;
702
703 loop
704 fetch unselected_invoices into l_invoice_id,
705 l_payment_num,
706 l_ps_hold_flag,
707 l_hold_all_payments_flag,
708 l_invoice_status,
709 l_approval_status,
710 l_org_id,
711 l_due_date,
712 l_discount_amount_available,
713 l_discount_date;
714
715
716 exit when unselected_invoices%notfound;
717
718
719 --Needs Invoice Validation
720 if l_invoice_status in ('NEVER APPROVED', 'UNAPPROVED') then
721
722 insert into ap_unselected_invoices_all(
723 checkrun_id,
724 invoice_id,
725 payment_num,
726 dont_pay_reason_code,
727 last_update_date,
728 last_updated_by,
729 created_by,
730 creation_date,
731 org_id)
732 values(
733 p_checkrun_id,
734 l_invoice_id,
735 l_payment_num ,
736 'NEEDS_INVOICE_VALIDATION',
737 sysdate,
738 5,
739 5,
740 sysdate,
741 l_org_id);
742
743 end if;
744
745
746 --Failed Invoice Validation
747 if l_invoice_status = 'NEEDS REAPPROVAL' then
748
749 insert into ap_unselected_invoices(
750 checkrun_id,
751 invoice_id,
752 payment_num,
753 dont_pay_reason_code,
754 last_update_date,
755 last_updated_by,
756 created_by,
757 creation_date,
758 org_id)
759 values(
760 p_checkrun_id,
761 l_invoice_id,
762 l_payment_num ,
763 'FAILED_INVOICE_VALIDATION',
764 sysdate,
765 5,
766 5,
767 sysdate,
768 l_org_id);
769
770 end if;
771
772
773 --Needs Approval
774 if l_approval_status in ('INITIATED','REQUIRED','NEEDS WFREAPPROVAL') then
775
776
777 insert into ap_unselected_invoices(
778 checkrun_id,
779 invoice_id,
780 payment_num,
781 dont_pay_reason_code,
782 last_update_date,
783 last_updated_by,
784 created_by,
785 creation_date,
786 org_id)
787 values(
788 p_checkrun_id,
789 l_invoice_id,
790 l_payment_num ,
791 'NEEDS_APPROVAL',
792 sysdate,
793 5,
794 5,
795 sysdate,
796 l_org_id);
797
798 end if;
799
800
801
802
803 --Approver Rejected
804 if l_approval_status = 'REJECTED' then
805
806 insert into ap_unselected_invoices(
807 checkrun_id,
808 invoice_id,
809 payment_num,
810 dont_pay_reason_code,
811 last_update_date,
812 last_updated_by,
813 created_by,
814 creation_date,
815 org_id)
816 values(
817 p_checkrun_id,
818 l_invoice_id,
819 l_payment_num ,
820 'APPROVER_REJECTED',
821 sysdate,
822 5,
823 5,
824 sysdate,
825 l_org_id);
826 end if;
827
828 --Scheduled Payment Hold
829 if l_ps_hold_flag = 'Y' then
830
831 insert into ap_unselected_invoices(
832 checkrun_id,
833 invoice_id,
834 payment_num,
835 dont_pay_reason_code,
836 last_update_date,
837 last_updated_by,
838 created_by,
839 creation_date,
840 org_id)
841 values(
842 p_checkrun_id,
843 l_invoice_id,
844 l_payment_num ,
845 'SCHEDULED_PAYMENT_HOLD',
846 sysdate,
847 5,
848 5,
849 sysdate,
850 l_org_id);
851 end if;
852
853
854
855 --Supplier Site Hold
856 if l_hold_all_payments_flag = 'Y' then
857
858 insert into ap_unselected_invoices(
859 checkrun_id,
860 invoice_id,
861 payment_num,
862 dont_pay_reason_code,
863 last_update_date,
864 last_updated_by,
865 created_by,
866 creation_date,
867 org_id)
868 values(
869 p_checkrun_id,
870 l_invoice_id,
871 l_payment_num ,
872 'SUPPLIER_SITE_HOLD',
873 sysdate,
874 5,
875 5,
876 sysdate,
877 l_org_id);
878 end if;
879
880
881 --Discount Rate Too Low
882 --4745133, can't call the ebd check in the cursor so doing it here.
883 insert into ap_unselected_invoices(
884 checkrun_id,
885 invoice_id,
886 payment_num,
887 dont_pay_reason_code,
888 last_update_date,
889 last_updated_by,
890 created_by,
891 creation_date,
892 org_id)
893 select
894 p_checkrun_id,
895 l_invoice_id,
896 l_payment_num ,
897 'DISCOUNT_RATE_TOO_LOW',
898 sysdate,
899 5,
900 5,
901 sysdate,
902 l_org_id
903 from dual
904 where fv_econ_benf_disc.ebd_check(p_payment_process_request_name, l_invoice_id,
905 p_check_date, l_due_date, l_discount_amount_available, l_discount_date) = 'N';
906
907
908 end loop;
909
910 close unselected_invoices;
911
912
913
914 EXCEPTION
915 WHEN OTHERS THEN
916
917 IF (SQLCODE <> -20001) THEN
918 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
919 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
920 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
921 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
922 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_payment_process_request_name: '||
923 p_payment_process_request_name);
924
925
926 END IF;
927 APP_EXCEPTION.RAISE_EXCEPTION;
928
929 END INSERT_UNSELECTED;
930
931
932
933 --this procedure groups invoices AP wants to ensure get paid together.
934 --it follows the grouping logic Oracle Payments currently has
935
936 PROCEDURE group_interest_credits (p_checkrun_id IN VARCHAR2,
937 p_current_calling_sequence IN VARCHAR2
938 ) IS
939
940
941
942 cursor c_documents (p_checkrun_id number) is
943 select nvl(asi.exclusive_payment_flag,'N')exclusive_payment_flag,
944 asi.org_id,
945 asi.payment_amount,
946 asi.vendor_site_id,
947 ai.party_id,
948 ai.party_site_id,
949 asi.payment_currency_code,
950 aps.payment_method_code,
951 nvl(aps.external_bank_account_id,-99) external_bank_account_id,
952 -- As per the discussion with Omar/Jayanta, we will only
953 -- have payables payment function and no more employee expenses
954 -- payment function.
955 nvl(ai.payment_function, 'PAYABLES_DISB') payment_function,
956 nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT',
957 'EMPLOYEE_EXP','PAYABLES_DOC')) pay_proc_trxn_type_code,
958 asi.invoice_id,
959 asi.payment_num,
960 asi.payment_grouping_number,
961 NVL(asi.ok_to_pay_flag,'Y') ok_to_pay_flag,
962 asi.proposed_payment_amount,
963 fv.beneficiary_party_id, --5017076
964 ipm.support_bills_payable_flag, -- Bug 5357689, 5479979
965 (trunc(aps.due_date) + nvl(ipm.maturity_date_offset_days,0)) due_date -- Bug 5357689
966 --Bug 543942 added NVL in the above scenario
967 from ap_selected_invoices_all asi,
968 ap_invoices ai, --Bug6040657. Changed from ap_invoices_all to ap_invoices
969 ap_inv_selection_criteria_all aisc,
970 ap_payment_schedules_all aps,
971 fv_tpp_assignments_v fv, --5017076
972 iby_payment_methods_vl ipm -- Bug 5357689
973 where asi.invoice_id = ai.invoice_id
974 and aps.invoice_id = asi.invoice_id
975 and aps.payment_num = asi.payment_num
976 and asi.checkrun_name = aisc.checkrun_name
977 and nvl(asi.ok_to_pay_flag,'Y') = 'Y'
978 and aisc.checkrun_id= p_checkrun_id
979 and asi.original_invoice_id is null
980 and fv.beneficiary_supplier_id(+) = ai.vendor_id
981 and fv.beneficiary_supplier_site_id(+) = ai.vendor_site_id
982 and ipm.payment_method_code = aps.payment_method_code -- Bug 5357689
983 order by asi.exclusive_payment_flag,
984 asi.org_id,
985 asi.vendor_site_id,
986 ai.party_id,
987 ai.party_site_id,
988 asi.payment_currency_code,
989 aps.payment_method_code,
990 aps.external_bank_account_id,
991 payment_function,
992 pay_proc_trxn_type_code,
993 fv.beneficiary_party_id,
994 SIGN(asi.invoice_amount) DESC, --this will make credit memos last per group
995 asi.due_date, -- Bug 5479979
996 /* DECODE(SIGN(asi.invoice_amount),
997 -1, TO_CHAR(asi.due_date,'YYYYMMDD'),
998 asi.invoice_num), */
999 asi.payment_num;
1000
1001
1002
1003 TYPE r_documents IS RECORD (exclusive_payment_flag varchar2(1),
1004 org_id number(15),
1005 payment_amount number,
1006 vendor_site_id number(15),
1007 party_id number(15),
1008 party_site_id number(15),
1009 payment_currency_code varchar2(15),
1010 payment_method varchar2(30),
1011 external_bank_account_id number(15),
1012 payment_function varchar2(30),
1013 pay_proc_trxn_type_code varchar2(30),
1014 invoice_id number(15),
1015 payment_num number(15),
1016 payment_grouping_number number(15),
1017 ok_to_pay_flag varchar2(1),
1018 proposed_payment_amount number,
1019 beneficiary_party_id number, --5017076
1020 support_bills_payable_flag varchar2(1), -- Bug 5357689
1021 due_date date); -- Bug 5357689
1022
1023 type t_documents is table of r_documents index by binary_integer;
1024 l_documents t_documents;
1025
1026
1027 l_prev_exclusive_payment_flag varchar2(1);
1028 l_prev_org_id number(15);
1029 l_prev_payment_amount number;
1030 l_prev_vendor_site_id number(15);
1031 l_prev_party_id number(15);
1032 l_prev_party_site_id number(15);
1033 l_prev_payment_currency_code varchar2(15);
1034 l_prev_payment_method varchar2(30);
1035 l_prev_ext_bank_acct_id number(15);
1036 l_prev_payment_function varchar2(30);
1037 l_prev_pay_proc_trxn_type_code varchar2(30);
1038 l_prev_grouping_number number;
1039 l_prev_beneficiary_party_id number;
1040 l_prev_bills_payable_flag varchar2(1); -- Bug 5357689
1041 l_prev_due_date date; -- Bug 5357689
1042
1043 l_grouping_number number;
1044 l_payment_sum number := 0;
1045 l_remove_cm_flag varchar2(1);
1046 l_current_calling_sequence varchar2(2000);
1047 l_debug_info varchar2(2000);
1048
1049 l_maximize_credits_flag varchar2(1); --5007819
1050
1051
1052 BEGIN
1053 l_current_calling_sequence := p_current_calling_sequence||'<-group interest credits';
1054 l_debug_info := 'open c_documents';
1055 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1056 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1057 END IF;
1058
1059 OPEN c_documents(p_checkrun_id);
1060 FETCH c_documents BULK COLLECT INTO l_documents;
1061 CLOSE c_documents;
1062
1063 if l_documents.count = 0 then
1064 return;
1065 end if;
1066
1067 l_grouping_number := 1;
1068 l_remove_cm_flag := 'N';
1069
1070 --5007819
1071 select nvl(zero_amounts_allowed,'N')
1072 into l_maximize_credits_flag
1073 from ap_inv_selection_criteria_all
1074 where checkrun_id = p_checkrun_id;
1075
1076 l_debug_info := 'group all documents';
1077 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1078 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1079 END IF;
1080
1081 --initialize the previous variables
1082
1083 l_prev_org_id := l_documents(1).org_id;
1084 l_prev_exclusive_payment_flag := l_documents(1).exclusive_payment_flag;
1085 l_prev_payment_amount := l_documents(1).payment_amount;
1086 l_prev_vendor_site_id := l_documents(1).vendor_site_id;
1087 l_prev_party_id := l_documents(1).party_id;
1088 l_prev_party_site_id := l_documents(1).party_site_id;
1089 l_prev_payment_currency_code := l_documents(1).payment_currency_code;
1090 l_prev_payment_method := l_documents(1).payment_method;
1091 l_prev_ext_bank_acct_id := l_documents(1).external_bank_account_id;
1092 l_prev_payment_function := l_documents(1).payment_function;
1093 l_prev_pay_proc_trxn_type_code := l_documents(1).pay_proc_trxn_type_code;
1094 l_prev_beneficiary_party_id := l_documents(1).beneficiary_party_id;
1095 l_prev_bills_payable_flag := l_documents(1).support_bills_payable_flag; -- Bug 5357689
1096 l_prev_due_date := l_documents(1).due_date; -- Bug 5357689
1097
1098 for i in l_documents.first .. l_documents.last loop
1099
1100 if l_documents(i).exclusive_payment_flag = 'Y' or
1101 l_prev_exclusive_payment_flag = 'Y' or
1102 l_prev_org_id <> l_documents(i).org_id or
1103 nvl(l_prev_vendor_site_id,-1) <> nvl(l_documents(i).vendor_site_id,-1) or
1104 l_prev_party_id <>l_documents(i).party_id or
1105 l_prev_party_site_id <> l_documents(i).party_site_id or
1106 l_prev_payment_currency_code <> l_documents(i).payment_currency_code or
1107 l_prev_payment_method <> l_documents(i).payment_method or
1108 nvl(l_prev_ext_bank_acct_id,-1) <> nvl(l_documents(i).external_bank_account_id,-1) or
1109 l_prev_payment_function <> l_documents(i).payment_function or
1110 --l_prev_pay_proc_trxn_type_code <> l_documents(i).pay_proc_trxn_type_code or
1111 /*Commented by zrehman for bug#7427845 on 20-Oct-2008*/
1112 nvl(l_prev_beneficiary_party_id,-1) <> nvl(l_documents(i).beneficiary_party_id,-1) or
1113 /* Bug 5357689 , 5479979 */
1114 ((l_documents(i).support_bills_payable_flag = 'Y') and
1115 (l_documents(i).payment_amount >= 0) and
1116 (trunc(l_prev_due_date) <> trunc(l_documents(i).due_date)))
1117 then
1118
1119 l_grouping_number := l_grouping_number + 1;
1120 l_payment_sum := 0;
1121 l_remove_cm_flag := 'N';
1122 end if;
1123
1124 l_documents(i).payment_grouping_number := l_grouping_number;
1125
1126 l_prev_org_id := l_documents(i).org_id;
1127 l_prev_exclusive_payment_flag := l_documents(i).exclusive_payment_flag;
1128 l_prev_payment_amount := l_documents(i).payment_amount;
1129 l_prev_vendor_site_id := l_documents(i).vendor_site_id;
1130 l_prev_party_id := l_documents(i).party_id;
1131 l_prev_party_site_id := l_documents(i).party_site_id;
1132 l_prev_payment_currency_code := l_documents(i).payment_currency_code;
1133 l_prev_payment_method := l_documents(i).payment_method;
1134 l_prev_ext_bank_acct_id := l_documents(i).external_bank_account_id;
1135 l_prev_payment_function := l_documents(i).payment_function;
1136 l_prev_pay_proc_trxn_type_code := l_documents(i).pay_proc_trxn_type_code;
1137 l_prev_beneficiary_party_id := l_documents(i).beneficiary_party_id;
1138 l_prev_bills_payable_flag := l_documents(i).support_bills_payable_flag; -- Bug 5357689
1139 l_prev_due_date := l_documents(i).due_date; -- Bug 5357689
1140
1141 end loop;
1142
1143
1144 --5007819
1145 if l_maximize_credits_flag = 'Y' then
1146
1147 l_debug_info := 'reduce credit memo amounts';
1148 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1149 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1150 END IF;
1151
1152 --now everything should be grouped, let's go through and determine which
1153 --credit memos need to be reduced or set to not pay
1154
1155 --initialize the previous grouping number
1156 l_prev_grouping_number := l_documents(1).payment_grouping_number;
1157
1158 for i in l_documents.first .. l_documents.last loop
1159 --Bug 7371792 --
1160 l_documents(i).payment_amount := l_documents(i).proposed_payment_amount;
1161
1162 if l_documents(i).payment_grouping_number = l_prev_grouping_number then
1163
1164 l_payment_sum := l_payment_sum + l_documents(i).payment_amount;
1165
1166 if l_payment_sum < 0 then
1167 if l_remove_cm_flag <> 'Y' then
1168 --if are here we have a credit memo that just took
1169 --the total payment amount below zero
1170 --the code below will set the proposed payment amount for the credit memo
1171 --so that the total for this grouping_number is zero
1172 l_documents(i).proposed_payment_amount := l_documents(i).payment_amount - l_payment_sum;
1173
1174
1175 --4688545 this will handle the case where the very first record
1176 --in the pl/sql table is is a credit memo
1177 if l_documents(i).proposed_payment_amount = 0 then
1178 l_documents(i).ok_to_pay_flag := 'N';
1179 end if;
1180
1181 l_remove_cm_flag := 'Y';
1182 l_payment_sum := 0;
1183
1184 else
1185 l_documents(i).ok_to_pay_flag := 'N';
1186 end if;
1187 end if;
1188
1189
1190 else
1191 --we are at a new payment, reset the sum
1192 --and if just a single credit memo then set it's ok to pay flag to 'N'
1193 l_payment_sum := l_documents(i).payment_amount; -- Bug 5479979
1194
1195 if l_documents(i).payment_amount < 0 then
1196 l_documents(i).ok_to_pay_flag := 'N';
1197 end if;
1198
1199 end if;
1200
1201 l_prev_grouping_number := l_documents(i).payment_grouping_number;
1202
1203 end loop;
1204
1205 end if; --5007819
1206
1207
1208 l_debug_info := 'update grouping numbers, ok to pay flags, and amounts';
1209 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1210 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1211 END IF;
1212
1213
1214
1215
1216 --IBY isn't using proposed_payment_amount so we need to set the payment
1217 --amount here
1218 -- 7371792 proposed_payment_amount contains original
1219 -- payment_amount. Later we reset the payment_amount
1220
1221 for i in l_documents.first .. l_documents.last loop
1222 update ap_selected_invoices_all
1223 set payment_grouping_number = l_documents(i).payment_grouping_number,
1224 ok_to_pay_flag = l_documents(i).ok_to_pay_flag,
1225 -- proposed_payment_amount = l_documents(i).proposed_payment_amount, 7371792
1226 payment_amount = l_documents(i).proposed_payment_amount,
1227 dont_pay_reason_code = decode(l_documents(i).ok_to_pay_flag,'N',
1228 'CREDIT TOO LOW',null),
1229 last_update_date = sysdate,
1230 -- Bug 7383484 (Base bug 7296715)
1231 -- last_updated_by = 5
1232 last_updated_by = FND_GLOBAL.USER_ID,
1233 due_date = l_documents(i).due_date -- Bug 5357689
1234 where invoice_id = l_documents(i).invoice_id
1235 and payment_num = l_documents(i).payment_num
1236 and checkrun_id= p_checkrun_id;
1237 end loop;
1238
1239
1240
1241 --handle interest invoices where federal is not installed
1242 --bug 5233279
1243 update ap_selected_invoices_all asi
1244 set payment_grouping_number =
1245 (select asi2.payment_grouping_number
1246 from ap_selected_invoices_all asi2
1247 where asi2.invoice_id = asi.original_invoice_id
1248 and asi2.payment_num = asi.original_payment_num
1249 and asi2.checkrun_id = p_checkrun_id)
1250 where asi.checkrun_id = p_checkrun_id
1251 and asi.original_invoice_id is not null
1252 and Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'N';
1253
1254
1255
1256 --5007819
1257 --Bug 5277604. Selected Invoices will
1258 --will be only unselected table if total payment
1259 --amount goes below zero.
1260 if l_maximize_credits_flag <> 'Y' then
1261 update ap_selected_invoices_all asi
1262 set ok_to_pay_flag = 'N',
1263 dont_pay_reason_code = 'CREDIT TOO LOW',
1264 last_update_date = sysdate,
1265 -- Bug 7383484 (Base bug 7296715)
1266 -- last_updated_by = 5
1267 last_updated_by = FND_GLOBAL.USER_ID
1268 where checkrun_id = p_checkrun_id
1269 and payment_grouping_number in
1270 (select asi2.payment_grouping_number
1271 from ap_selected_invoices_all asi2
1272 where asi2.checkrun_id = p_checkrun_id
1273 group by asi2.payment_grouping_number
1274 having sum(asi2.payment_amount) < 0);
1275 end if;
1276
1277
1278 --now remove the grouping numbers where no interest invoice is involved
1279 --or no credit memo is involved
1280 UPDATE Ap_Selected_Invoices_All ASI
1281 SET payment_grouping_number = null
1282 WHERE payment_grouping_number NOT IN (
1283 SELECT payment_grouping_number
1284 FROM Ap_Selected_Invoices_All ASI2
1285 WHERE (ASI2.original_invoice_id is not null or
1286 ASI2.payment_amount < 0)
1287 AND ASI2.ok_to_pay_flag = 'Y'
1288 AND ASI2.checkrun_id = p_checkrun_id
1289 AND ASI2.payment_grouping_number IS NOT NULL)
1290 AND ASI.checkrun_id = p_checkrun_id
1291 AND ASI.payment_grouping_number IS NOT NULL;
1292
1293 --Bug 5646890, Following Update is replaed by updated above as per performance team
1294 --now remove the grouping numbers where no interest invoice is involved
1295 --or no credit memo is involved
1296 /*update ap_selected_invoices_all asi
1297 set payment_grouping_number = null
1298 where payment_grouping_number not in (
1299 select payment_grouping_number
1300 from ap_selected_invoices_all asi2
1301 where (asi2.original_invoice_id is not null or
1302 asi2.payment_amount < 0)
1303 and asi2.ok_to_pay_flag = 'Y'
1304 and checkrun_id = p_checkrun_id)
1305 and checkrun_id = p_checkrun_id; */
1306
1307 --now remove the grouping numbers where interest invoice is involved
1308 --but federal is installed. Bug 5233279.
1309 -- Bug 5645890, Added extra condition payment_grouping_number is not null
1310
1311 update ap_selected_invoices_all asi
1312 set payment_grouping_number = null
1313 where asi.checkrun_id = p_checkrun_id
1314 and asi.payment_grouping_number is not null
1315 and Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'Y'
1316 and exists (
1317 select /*+NO_UNNEST */ NULL
1318 from ap_selected_invoices_all asi2
1319 where asi2.original_invoice_id is not null
1320 and asi2.original_invoice_id = asi.invoice_id
1321 and asi2.ok_to_pay_flag = 'Y'
1322 and asi2.checkrun_id = p_checkrun_id);
1323
1324
1325 --bug 5233279. Now set the pay alone flag to 'N' for all invoices
1326 --when federal is not installed and has related interest invoice
1327 -- Bug 5645890, Added the hint
1328 update ap_selected_invoices_all asi
1329 set exclusive_payment_flag = 'N'
1330 where asi.checkrun_id = p_checkrun_id
1331 and Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'N'
1332 and exists (
1333 select NULL
1334 from ap_selected_invoices_all asi2
1335 where asi2.original_invoice_id is not null
1336 and asi2.original_invoice_id = asi.invoice_id
1337 and asi2.ok_to_pay_flag = 'Y'
1338 and asi2.checkrun_id = p_checkrun_id);
1339
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343
1344 IF (SQLCODE <> -20001) THEN
1345 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1346 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1347 END IF;
1348 APP_EXCEPTION.RAISE_EXCEPTION;
1349
1350 END GROUP_INTEREST_CREDITS;
1351
1352
1353 Procedure create_checkrun (p_checkrun_id out nocopy number,
1354 p_template_id in number,
1355 p_payment_date in date,
1356 p_pay_thru_date in date,
1357 p_pay_from_date in date,
1358 p_current_calling_sequence in varchar2) is
1359
1360
1361
1362 l_current_calling_sequence varchar2(2000);
1363 l_debug_info varchar2(2000);
1364
1365 begin
1366
1367 l_current_calling_sequence := p_current_calling_sequence || '<-create_checkrun';
1368
1369 l_debug_info := 'select checkrun_id';
1370 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1371 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1372 END IF;
1373
1374
1375 select ap_inv_selection_criteria_s.nextval
1376 into p_checkrun_id
1377 from dual;
1378
1379
1380
1381 l_debug_info := 'insert into ap_inv_selection_criteria_all';
1382 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1383 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1384 END IF;
1385
1386 insert into ap_inv_selection_criteria_all(
1387 check_date,
1388 pay_thru_date,
1389 hi_payment_priority,
1390 low_payment_priority,
1391 pay_only_when_due_flag,
1392 status,
1393 -- zero_amounts_allowed, --deepak is unsure of this one in his dld
1394 zero_invoices_allowed,
1395 vendor_id,
1396 checkrun_id,
1397 pay_from_date,
1398 inv_exchange_rate_type,
1399 exchange_rate_type, --Bug6829191
1400 payment_method_code,
1401 vendor_type_lookup_code,
1402 CREATE_INSTRS_FLAG,
1403 PAYMENT_PROFILE_ID,
1404 bank_account_id,
1405 checkrun_name,
1406 ou_group_option,
1407 le_group_option,
1408 currency_group_option,
1409 pay_group_option,
1410 last_update_date,
1411 last_updated_by,
1412 last_update_login,
1413 creation_date,
1414 created_by,
1415 template_flag,
1416 template_id,
1417 payables_review_settings,
1418 payments_review_settings,
1419 document_rejection_level_code,
1420 payment_rejection_level_code,
1421 party_id,
1422 request_id, --4737467
1423 payment_document_id, --7315136
1424 transfer_priority --7315136
1425 )
1426 select nvl(p_payment_date,sysdate), --4681989
1427 nvl(p_pay_thru_date, sysdate + ADDL_PAY_THRU_DAYS),--4681989
1428 hi_payment_priority,
1429 low_payment_priority,
1430 pay_only_when_due_flag,
1431 'UNSTARTED',
1432 -- zero_amounts_allowed,
1433 ZERO_INV_ALLOWED_FLAG,
1434 vendor_id,
1435 p_checkrun_id,
1436 nvl(p_pay_from_date, sysdate - ADDL_PAY_FROM_DAYS), --4681989
1437 inv_exchange_rate_type,
1438 payment_exchange_rate_type, --Bug6829191
1439 payment_method_code,
1440 vendor_type_lookup_code,
1441 CREATE_INSTRS_FLAG,
1442 PAYMENT_PROFILE_ID,
1443 BANK_ACCOUNT_ID,
1444 template_name ||'-'||to_char(sysdate, 'DD-MON-RRRR HH24:MI:SS'),
1445 ou_group_option,
1446 le_group_option,
1447 currency_group_option,
1448 pay_group_option,
1449 sysdate,
1450 last_updated_by,
1451 last_update_login,
1452 sysdate,
1453 created_by,
1454 'Y',
1455 p_template_id,
1456 payables_review_settings,
1457 payments_review_settings,
1458 document_rejection_level_code,
1459 payment_rejection_level_code,
1460 party_id,
1461 fnd_global.conc_request_id, --4737467
1462 payment_document_id, --7315136
1463 transfer_priority --7315136
1464 from AP_PAYMENT_TEMPLATES
1465 where template_id = p_template_id;
1466
1467
1468 l_debug_info := 'insert into ap_le_group';
1469 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1470 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1471 END IF;
1472
1473 insert into ap_le_group (
1474 legal_entity_id,
1475 checkrun_id,
1476 LE_GROUP_ID,
1477 CREATION_DATE,
1478 CREATED_BY,
1479 LAST_UPDATE_DATE,
1480 LAST_UPDATED_BY)
1481 select legal_entity_id,
1482 p_checkrun_id,
1483 AP_LE_GROUP_S.nextval,
1484 sysdate,
1485 alg.created_by,
1486 sysdate,
1487 alg.last_updated_by
1488 from ap_le_group alg,
1489 ap_payment_templates appt
1490 where alg.template_id = p_template_id
1491 and alg.template_id = appt.template_id
1492 and appt.le_group_option = 'SPECIFY';
1493
1494
1495 l_debug_info := 'insert into AP_OU_GROUP';
1496 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1497 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1498 END IF;
1499
1500 insert into AP_OU_GROUP (
1501 org_id,
1502 checkrun_id,
1503 OU_GROUP_ID,
1504 CREATION_DATE,
1505 CREATED_BY,
1506 LAST_UPDATE_DATE,
1507 LAST_UPDATED_BY)
1508 select aog.org_id,
1509 p_checkrun_id,
1510 AP_OU_GROUP_S.nextval,
1511 sysdate,
1512 aog.created_by,
1513 sysdate,
1514 aog.last_updated_by
1515 from ap_ou_group aog,
1516 ap_payment_templates appt
1517 where aog.template_id = p_template_id
1518 and aog.template_id = appt.template_id
1519 and appt.ou_group_option = 'SPECIFY';
1520
1521 l_debug_info := 'insert into AP_CURRENCY_GROUP';
1522 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1523 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1524 END IF;
1525
1526
1527 insert into AP_CURRENCY_GROUP (
1528 currency_code,
1529 checkrun_id,
1530 CURRENCY_GROUP_ID,
1531 CREATION_DATE,
1532 CREATED_BY,
1533 LAST_UPDATE_DATE,
1534 LAST_UPDATED_BY)
1535 select currency_code,
1536 p_checkrun_id,
1537 AP_CURRENCY_GROUP_S.nextval,
1538 sysdate,
1539 acg.created_by,
1540 sysdate,
1541 acg.last_updated_by
1542 from AP_CURRENCY_GROUP acg,
1543 ap_payment_templates appt
1544 where acg.template_id = p_template_id
1545 and acg.template_id = appt.template_id
1546 and appt.currency_group_option = 'SPECIFY';--Bug6926344
1547
1548 l_debug_info := 'insert into AP_PAY_GROUP';
1549 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1550 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
1551 END IF;
1552
1553 insert into AP_PAY_GROUP (
1554 vendor_pay_group,
1555 checkrun_id,
1556 PAY_GROUP_ID,
1557 CREATION_DATE,
1558 CREATED_BY,
1559 LAST_UPDATE_DATE,
1560 LAST_UPDATED_BY)
1561 select vendor_pay_group,
1562 p_checkrun_id,
1563 AP_PAY_GROUP_S.nextval,
1564 sysdate,
1565 apg.created_by,
1566 sysdate,
1567 apg.last_updated_by
1568 from AP_PAY_GROUP apg,
1569 ap_payment_templates appt
1570 where apg.template_id = p_template_id
1571 and apg.template_id = appt.template_id
1572 and appt.pay_group_option = 'SPECIFY'; --Bug6926344
1573
1574
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578
1579 IF (SQLCODE <> -20001) THEN
1580 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1581 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1582 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
1583 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
1584 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_template_id= '||
1585 to_char(p_template_id));
1586
1587
1588 END IF;
1589 APP_EXCEPTION.RAISE_EXCEPTION;
1590
1591 end create_checkrun;
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601 PROCEDURE select_invoices (errbuf OUT NOCOPY VARCHAR2,
1602 retcode OUT NOCOPY NUMBER,
1603 p_checkrun_id in varchar2,
1604 P_template_id in varchar2,
1605 p_payment_date in varchar2,
1606 p_pay_thru_date in varchar2,
1607 p_pay_from_date in varchar2) IS
1608
1609 l_abort varchar2(1);
1610 l_api_name CONSTANT VARCHAR2(100) := 'SELECT_INVOICES';
1611 l_base_currency_code varchar2(15);
1612 l_batch_exchange_rate_type varchar2(30);
1613 l_batch_status varchar2(30);
1614 l_bank_account_id number; --4710933
1615 l_check_date date;
1616 l_checkrun_id number;
1617 l_count_inv_selected number;
1618 l_create_instrs_flag varchar2(1);
1619 l_currency_group_option varchar2(10);
1620 l_current_calling_sequence VARCHAR2(2000);
1621 l_debug_info VARCHAR2(2000);
1622 l_disc_pay_thru_date date;
1623 l_doc_rejection_level_code varchar2(30);
1624 l_encumbrance_flag number;
1625 l_hi_payment_priority number;
1626 l_inv_exchange_rate_type varchar2(30);
1627 l_inv_vendor_id number;
1628 l_invoice_batch_id number;
1629 l_le_group_option varchar2(10);
1630 l_log_module varchar2(240);
1631 l_low_payment_priority number;
1632 l_max_payment_amount number;
1633 l_min_check_amount number;
1634 l_missing_rates_count number;
1635 l_ou_group_option varchar2(10);
1636 l_party_id number(15);
1637 l_pay_from_date date;
1638 l_pay_group_option varchar2(10);
1639 l_pay_rejection_level_code varchar2(30);
1640 l_pay_review_settings_flag varchar2(1);
1641 l_pay_thru_date date;
1642 l_payables_review_settings varchar2(1);
1643 l_payment_date date;
1644 l_payment_document_id number(15); --4939405
1645 l_payment_method varchar2(30);
1646 l_payment_process_request_name varchar2(240);
1647 l_payment_profile_id number;
1648 l_req_id number;
1649 l_set_of_books_name varchar2(30);
1650 l_supplier_type varchar2(30);
1651 l_template_id number;
1652 l_zero_amounts_allowed varchar2(1);
1653 l_zero_invoices_allowed varchar2(1);
1654 SELECTION_FAILURE EXCEPTION;
1655 l_xml_output BOOLEAN;
1656 l_iso_language FND_LANGUAGES.iso_language%TYPE;
1657 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
1658 l_template_code Fnd_Concurrent_Programs.template_code%TYPE; -- Bug 6969710
1659 /*bug 7519277*/
1660 l_ATTRIBUTE_CATEGORY VARCHAR2(150);
1661 l_ATTRIBUTE1 VARCHAR2(150);
1662 l_ATTRIBUTE2 VARCHAR2(150);
1663 l_ATTRIBUTE3 VARCHAR2(150);
1664 l_ATTRIBUTE4 VARCHAR2(150);
1665 l_ATTRIBUTE5 VARCHAR2(150);
1666 l_ATTRIBUTE6 VARCHAR2(150);
1667 l_ATTRIBUTE7 VARCHAR2(150);
1668 l_ATTRIBUTE8 VARCHAR2(150);
1669 l_ATTRIBUTE9 VARCHAR2(150);
1670 l_ATTRIBUTE10 VARCHAR2(150);
1671 l_ATTRIBUTE11 VARCHAR2(150);
1672 l_ATTRIBUTE12 VARCHAR2(150);
1673 l_ATTRIBUTE13 VARCHAR2(150);
1674 l_ATTRIBUTE14 VARCHAR2(150);
1675 l_ATTRIBUTE15 VARCHAR2(150);
1676 /*bug 7519277*/
1677 l_icx_numeric_characters VARCHAR2(30); --for bug#7435751
1678 l_return_status boolean; --for bug#7435751
1679 l_inv_awt_exists_flag VARCHAR2(1); -- Bug 7492768
1680
1681 -- Bug 5646890. Performance changes
1682 TYPE checkrun_name_t IS TABLE OF AP_SELECTED_INVOICES_ALL.checkrun_name%TYPE INDEX BY BINARY_INTEGER;
1683 TYPE checkrun_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.checkrun_id%TYPE INDEX BY BINARY_INTEGER;
1684 TYPE invoice_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_id%TYPE INDEX BY BINARY_INTEGER;
1685 TYPE payment_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_num%TYPE INDEX BY BINARY_INTEGER;
1686 TYPE last_update_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_update_date%TYPE INDEX BY BINARY_INTEGER;
1687 TYPE last_updated_by_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
1688 TYPE creation_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.creation_date%TYPE INDEX BY BINARY_INTEGER;
1689 TYPE created_by_t IS TABLE OF AP_SELECTED_INVOICES_ALL.created_by%TYPE INDEX BY BINARY_INTEGER;
1690 TYPE last_update_login_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_update_login%TYPE INDEX BY BINARY_INTEGER;
1691 TYPE vendor_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_id%TYPE INDEX BY BINARY_INTEGER;
1692 TYPE vendor_site_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_site_id%TYPE INDEX BY BINARY_INTEGER;
1693 TYPE vendor_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_num%TYPE INDEX BY BINARY_INTEGER;
1694 TYPE vendor_name_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_name%TYPE INDEX BY BINARY_INTEGER;
1695 TYPE vendor_site_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_site_code%TYPE INDEX BY BINARY_INTEGER;
1696 TYPE address_line1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.address_line1%TYPE INDEX BY BINARY_INTEGER;
1697 TYPE city_t IS TABLE OF AP_SELECTED_INVOICES_ALL.city%TYPE INDEX BY BINARY_INTEGER;
1698 TYPE state_t IS TABLE OF AP_SELECTED_INVOICES_ALL.state%TYPE INDEX BY BINARY_INTEGER;
1699 TYPE zip_t IS TABLE OF AP_SELECTED_INVOICES_ALL.zip%TYPE INDEX BY BINARY_INTEGER;
1700 TYPE province_t IS TABLE OF AP_SELECTED_INVOICES_ALL.province%TYPE INDEX BY BINARY_INTEGER;
1701 TYPE country_t IS TABLE OF AP_SELECTED_INVOICES_ALL.country%TYPE INDEX BY BINARY_INTEGER;
1702 TYPE attention_ar_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attention_ar_flag%TYPE INDEX BY BINARY_INTEGER;
1703 TYPE withholding_status_lookup_t IS TABLE OF AP_SELECTED_INVOICES_ALL.withholding_status_lookup_code%TYPE INDEX BY BINARY_INTEGER;
1704 TYPE invoice_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_num%TYPE INDEX BY BINARY_INTEGER;
1705 TYPE invoice_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_date%TYPE INDEX BY BINARY_INTEGER;
1706 TYPE voucher_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.voucher_num%TYPE INDEX BY BINARY_INTEGER;
1707 TYPE ap_ccid_t IS TABLE OF AP_SELECTED_INVOICES_ALL.ap_ccid%TYPE INDEX BY BINARY_INTEGER;
1708 TYPE due_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.due_date%TYPE INDEX BY BINARY_INTEGER;
1709 TYPE discount_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_date%TYPE INDEX BY BINARY_INTEGER;
1710 TYPE invoice_description_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_description%TYPE INDEX BY BINARY_INTEGER;
1711 TYPE payment_priority_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_priority%TYPE INDEX BY BINARY_INTEGER;
1712 TYPE ok_to_pay_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.ok_to_pay_flag%TYPE INDEX BY BINARY_INTEGER;
1713 TYPE always_take_disc_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.always_take_discount_flag%TYPE INDEX BY BINARY_INTEGER;
1714 TYPE amount_modified_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_modified_flag%TYPE INDEX BY BINARY_INTEGER;
1715 TYPE invoice_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_amount%TYPE INDEX BY BINARY_INTEGER;
1716 TYPE payment_cross_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_cross_rate%TYPE INDEX BY BINARY_INTEGER;
1717 TYPE invoice_exchange_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1718 TYPE set_of_books_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.set_of_books_id%TYPE INDEX BY BINARY_INTEGER;
1719 TYPE customer_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.customer_num%TYPE INDEX BY BINARY_INTEGER;
1720 TYPE future_pay_due_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.future_pay_due_date%TYPE INDEX BY BINARY_INTEGER;
1721 TYPE exclusive_payment_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.exclusive_payment_flag%TYPE INDEX BY BINARY_INTEGER;
1722 TYPE attribute1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attribute1%TYPE INDEX BY BINARY_INTEGER;
1723 TYPE attribute_category_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attribute_category%TYPE INDEX BY BINARY_INTEGER;
1724 TYPE org_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.org_id%TYPE INDEX BY BINARY_INTEGER;
1725 TYPE payment_currency_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_currency_code%TYPE INDEX BY BINARY_INTEGER;
1726 TYPE external_bank_account_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.external_bank_account_id%TYPE INDEX BY BINARY_INTEGER;
1727 TYPE legal_entity_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.legal_entity_id%TYPE INDEX BY BINARY_INTEGER;
1728 TYPE global_attribute1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.global_attribute1%TYPE INDEX BY BINARY_INTEGER;
1729 TYPE global_attribute_category_t IS TABLE OF AP_SELECTED_INVOICES_ALL.global_attribute_category%TYPE INDEX BY BINARY_INTEGER;
1730 TYPE amount_paid_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_paid%TYPE INDEX BY BINARY_INTEGER;
1731 TYPE discount_amount_taken_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount_taken%TYPE INDEX BY BINARY_INTEGER;
1732 TYPE amount_remaining_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_remaining%TYPE INDEX BY BINARY_INTEGER;
1733 TYPE discount_amount_remaining_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount_remaining%TYPE INDEX BY BINARY_INTEGER;
1734 TYPE payment_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_amount%TYPE INDEX BY BINARY_INTEGER;
1735 TYPE discount_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount%TYPE INDEX BY BINARY_INTEGER;
1736 TYPE sequence_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.sequence_num%TYPE INDEX BY BINARY_INTEGER;
1737 TYPE dont_pay_reason_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.dont_pay_reason_code%TYPE INDEX BY BINARY_INTEGER;
1738 TYPE check_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.check_number%TYPE INDEX BY BINARY_INTEGER;
1739 TYPE bank_account_type_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_account_type%TYPE INDEX BY BINARY_INTEGER;
1740 TYPE original_invoice_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.original_invoice_id%TYPE INDEX BY BINARY_INTEGER;
1741 TYPE original_payment_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.original_payment_num%TYPE INDEX BY BINARY_INTEGER;
1742 TYPE bank_account_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_account_num%TYPE INDEX BY BINARY_INTEGER;
1743 TYPE bank_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_num%TYPE INDEX BY BINARY_INTEGER;
1744 TYPE proposed_payment_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.proposed_payment_amount%TYPE INDEX BY BINARY_INTEGER;
1745 TYPE pay_selected_check_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.pay_selected_check_id%TYPE INDEX BY BINARY_INTEGER;
1746 TYPE print_selected_check_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.print_selected_check_id%TYPE INDEX BY BINARY_INTEGER;
1747 TYPE withholding_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.withholding_amount%TYPE INDEX BY BINARY_INTEGER;
1748 TYPE invoice_payment_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_payment_id%TYPE INDEX BY BINARY_INTEGER;
1749 TYPE dont_pay_description_t IS TABLE OF AP_SELECTED_INVOICES_ALL.dont_pay_description%TYPE INDEX BY BINARY_INTEGER;
1750 TYPE transfer_priority_t IS TABLE OF AP_SELECTED_INVOICES_ALL.transfer_priority%TYPE INDEX BY BINARY_INTEGER;
1751 TYPE iban_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.iban_number%TYPE INDEX BY BINARY_INTEGER;
1752 TYPE payment_grouping_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_grouping_number%TYPE INDEX BY BINARY_INTEGER;
1753 TYPE payment_exchange_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1754 TYPE payment_exchange_rate_type_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_rate_type%TYPE INDEX BY BINARY_INTEGER;
1755 TYPE payment_exchange_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_date%TYPE INDEX BY BINARY_INTEGER;
1756
1757
1758
1759
1760 TYPE sel_inv_rec_type IS RECORD (
1761 checkrun_name_l checkrun_name_t
1762 ,checkrun_id_l checkrun_id_t
1763 ,invoice_id_l invoice_id_t
1764 ,payment_num_l payment_num_t
1765 ,last_update_date_l last_update_date_t
1766 ,last_updated_by_l last_updated_by_t
1767 ,creation_date_l creation_date_t
1768 ,created_by_l created_by_t
1769 ,last_update_login_l last_update_login_t
1770 ,vendor_id_l vendor_id_t
1771 ,vendor_site_id_l vendor_site_id_t
1772 ,vendor_num_l vendor_num_t
1773 ,vendor_name_l vendor_name_t
1774 ,vendor_site_code_l vendor_site_code_t
1775 ,address_line1_l address_line1_t
1776 ,address_line2_l address_line1_t
1777 ,address_line3_l address_line1_t
1778 ,address_line4_l address_line1_t
1779 ,city_l city_t
1780 ,state_l state_t
1781 ,zip_l zip_t
1782 ,province_l province_t
1783 ,country_l country_t
1784 ,attention_ar_flag_l attention_ar_flag_t
1785 ,withholding_status_lookup_l withholding_status_lookup_t
1786 ,invoice_num_l invoice_num_t
1787 ,invoice_date_l invoice_date_t
1788 ,voucher_num_l voucher_num_t
1789 ,ap_ccid_l ap_ccid_t
1790 ,due_date_l due_date_t
1791 ,discount_date_l discount_date_t
1792 ,invoice_description_l invoice_description_t
1793 ,payment_priority_l payment_priority_t
1794 ,ok_to_pay_flag_l ok_to_pay_flag_t
1795 ,always_take_disc_flag_l always_take_disc_flag_t
1796 ,amount_modified_flag_l amount_modified_flag_t
1797 ,invoice_amount_l invoice_amount_t
1798 ,payment_cross_rate_l payment_cross_rate_t
1799 ,invoice_exchange_rate_l invoice_exchange_rate_t
1800 ,set_of_books_id_l set_of_books_id_t
1801 ,customer_num_l customer_num_t
1802 ,future_pay_due_date_l future_pay_due_date_t
1803 ,exclusive_payment_flag_l exclusive_payment_flag_t
1804 ,attribute1_l attribute1_t
1805 ,attribute2_l attribute1_t
1806 ,attribute3_l attribute1_t
1807 ,attribute4_l attribute1_t
1808 ,attribute5_l attribute1_t
1809 ,attribute6_l attribute1_t
1810 ,attribute7_l attribute1_t
1811 ,attribute8_l attribute1_t
1812 ,attribute9_l attribute1_t
1813 ,attribute10_l attribute1_t
1814 ,attribute11_l attribute1_t
1815 ,attribute12_l attribute1_t
1816 ,attribute13_l attribute1_t
1817 ,attribute14_l attribute1_t
1818 ,attribute15_l attribute1_t
1819 ,attribute_category_l attribute_category_t
1820 ,org_id_l org_id_t
1821 ,payment_currency_code_l payment_currency_code_t
1822 ,external_bank_account_id_l external_bank_account_id_t
1823 ,legal_entity_id_l legal_entity_id_t
1824 ,global_attribute1_l global_attribute1_t
1825 ,global_attribute2_l global_attribute1_t
1826 ,global_attribute3_l global_attribute1_t
1827 ,global_attribute4_l global_attribute1_t
1828 ,global_attribute5_l global_attribute1_t
1829 ,global_attribute6_l global_attribute1_t
1830 ,global_attribute7_l global_attribute1_t
1831 ,global_attribute8_l global_attribute1_t
1832 ,global_attribute9_l global_attribute1_t
1833 ,global_attribute10_l global_attribute1_t
1834 ,global_attribute11_l global_attribute1_t
1835 ,global_attribute12_l global_attribute1_t
1836 ,global_attribute13_l global_attribute1_t
1837 ,global_attribute14_l global_attribute1_t
1838 ,global_attribute15_l global_attribute1_t
1839 ,global_attribute16_l global_attribute1_t
1840 ,global_attribute17_l global_attribute1_t
1841 ,global_attribute18_l global_attribute1_t
1842 ,global_attribute19_l global_attribute1_t
1843 ,global_attribute20_l global_attribute1_t
1844 ,global_attribute_category_l global_attribute_category_t
1845 ,amount_paid_l amount_paid_t
1846 ,discount_amount_taken_l discount_amount_taken_t
1847 ,amount_remaining_l amount_remaining_t
1848 ,discount_amount_remaining_l discount_amount_remaining_t
1849 ,payment_amount_l payment_amount_t
1850 ,discount_amount_l discount_amount_t
1851 ,sequence_num_l sequence_num_t
1852 ,dont_pay_reason_code_l dont_pay_reason_code_t
1853 ,check_number_l check_number_t
1854 ,bank_account_type_l bank_account_type_t
1855 ,original_invoice_id_l original_invoice_id_t
1856 ,original_payment_num_l original_payment_num_t
1857 ,bank_account_num_l bank_account_num_t
1858 ,bank_num_l bank_num_t
1859 ,proposed_payment_amount_l proposed_payment_amount_t
1860 ,pay_selected_check_id_l pay_selected_check_id_t
1861 ,print_selected_check_id_l print_selected_check_id_t
1862 ,withhloding_amount_l withholding_amount_t
1863 ,invoice_payment_id_l invoice_payment_id_t
1864 ,dont_pay_description_l dont_pay_description_t
1865 ,transfer_priority_l transfer_priority_t
1866 ,iban_number_l iban_number_t
1867 ,payment_grouping_number_l payment_grouping_number_t
1868 ,payment_exchange_rate_l payment_exchange_rate_t
1869 ,payment_exchange_rate_type_l payment_exchange_rate_type_t
1870 ,payment_exchange_date_l payment_exchange_date_t);
1871
1872 sel_inv_list sel_inv_rec_type;
1873
1874 CURSOR pay_sched_enc_cur (p_checkrun_name IN VARCHAR2,
1875 p_checkrun_id IN NUMBER,
1876 p_check_date IN DATE,
1877 p_pay_thru_date IN DATE,
1878 p_pay_from_date IN DATE,
1879 p_disc_pay_thru_date IN DATE,
1880 p_hi_payment_priority IN NUMBER,
1881 p_lo_payment_priority IN NUMBER,
1882 p_inv_batch_id IN NUMBER,
1883 p_inv_vendor_id IN NUMBER,
1884 p_party_id IN NUMBER,
1885 p_inv_exc_rate_type IN VARCHAR2,
1886 p_payment_method IN VARCHAR2,
1887 p_supplier_type IN VARCHAR2,
1888 p_le_group_option IN VARCHAR2,
1889 p_ou_group_option IN VARCHAR2,
1890 p_curr_group_option IN VARCHAR2,
1891 p_pay_group_option IN VARCHAR2,
1892 p_zero_inv_allowed IN VARCHAR2 ) IS
1893 SELECT /*+ NO_EXPAND */
1894 p_checkrun_name checkrun_name
1895 ,p_checkrun_id checkrun_id
1896 ,ps.invoice_id invoice_id
1897 ,payment_num payment_num
1898 ,SYSDATE last_update_date
1899 -- Bug 7383484 (Base bug 7296715)
1900 -- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
1901 -- ,5 last_updated_by
1902 ,FND_GLOBAL.USER_ID last_updated_by
1903 ,SYSDATE creation_date
1904 -- Bug 7383484 (Base bug 7296715)
1905 -- ,5 created_by
1906 ,FND_GLOBAL.USER_ID created_by
1907 ,NULL last_update_login
1908 ,inv.vendor_id vendor_id
1909 ,inv.vendor_site_id vendor_site_id
1910 ,suppliers.segment1 vendor_num
1911 /* Bug 5620285, Added the following decode */
1912 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1913 hzp.party_name, suppliers.vendor_name) vendor_name
1914 ,sites.vendor_site_code vendor_site_code
1915 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1916 hzl.address1, sites.address_line1) address_line1
1917 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1918 hzl.address2, sites.address_line2) address_line2
1919 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1920 hzl.address3, sites.address_line3) address_line3
1921 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1922 hzl.address4, sites.address_line4) address_line4
1923 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1924 hzl.city, sites.city) city
1925 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1926 hzl.state, sites.state) state
1927 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1928 hzl.postal_code, sites.zip) zip
1929 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1930 hzl.province, sites.province) province
1931 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
1932 hzl.country, sites.country) country
1933 ,sites.attention_ar_flag attention_ar_flag
1934 ,suppliers.withholding_status_lookup_code withholding_status_lookup_code
1935 ,inv.invoice_num invoice_num
1936 ,inv.invoice_date invoice_date
1937 ,DECODE(inv.doc_sequence_id,
1938 '', inv.voucher_num,
1939 inv.doc_sequence_value) voucher_num
1940 ,inv.accts_pay_code_combination_id ap_ccid
1941 ,TRUNC(ps.due_date) due_date
1942 ,DECODE(sites.always_take_disc_flag,
1943 'Y', TRUNC(ps.due_date),
1944 DECODE(SIGN(p_check_date
1945 - NVL(ps.discount_date,
1946 p_check_date+1)-1),
1947 -1, ps.discount_date,
1948 DECODE(SIGN(p_check_date
1949 -NVL(ps.second_discount_date,
1950 p_check_date+1)-1),
1951 -1, ps.second_discount_date,
1952 DECODE(SIGN(p_check_date
1953 -NVL(ps.third_discount_date,
1954 p_check_date+1)-1),
1955 -1, ps.third_discount_date,
1956 TRUNC(ps.due_date))))) discount_date
1957 ,SUBSTRB(inv.description,1,50) invoice_description
1958 ,nvl(ps.payment_priority, 99) payment_priority
1959 ,'Y' ok_to_pay_flag
1960 ,sites.always_take_disc_flag always_take_discount_flag
1961 ,'N' amount_modified_flag
1962 ,inv.invoice_amount invoice_amount
1963 ,inv.payment_cross_rate payment_cross_rate
1964 ,DECODE(inv.exchange_rate,
1965 NULL, DECODE(inv.invoice_currency_code,
1966 asp.base_currency_code, 1,
1967 NULL),
1968 inv.exchange_rate) invoice_exchange_rate
1969 ,inv.set_of_books_id set_of_books_id
1970 ,sites.customer_num customer_num
1971 ,ps.future_pay_due_date future_pay_due_date
1972 ,inv.exclusive_payment_flag exclusive_payment_flag
1973 ,ps.attribute1 attribute1
1974 ,ps.attribute2 attribute2
1975 ,ps.attribute3 attribute3
1976 ,ps.attribute4 attribute4
1977 ,ps.attribute5 attribute5
1978 ,ps.attribute6 attribute6
1979 ,ps.attribute7 attribute7
1980 ,ps.attribute8 attribute8
1981 ,ps.attribute9 attribute9
1982 ,ps.attribute10 attribute10
1983 ,ps.attribute11 attribute11
1984 ,ps.attribute12 attribute12
1985 ,ps.attribute13 attribure13
1986 ,ps.attribute14 attribute14
1987 ,ps.attribute15 attribute15
1988 ,ps.attribute_category attribute_category
1989 ,inv.org_id org_id
1990 ,inv.payment_currency_code payment_currency_code
1991 ,ps.external_bank_account_id external_bank_account_id
1992 ,inv.legal_entity_id legal_entity_id
1993 /* Bug 5192018 we will insert global attribute values from ap_invoices table */
1994 ,inv.global_attribute1 global_attribute1
1995 ,inv.global_attribute2 global_attribute2
1996 ,inv.global_attribute3 global_attribute3
1997 ,inv.global_attribute4 global_attribute4
1998 ,inv.global_attribute5 global_attribute5
1999 ,inv.global_attribute6 global_attribute6
2000 ,inv.global_attribute7 global_attribute7
2001 ,inv.global_attribute8 global_attribute8
2002 ,inv.global_attribute9 global_attribute9
2003 ,inv.global_attribute10 global_attribute10
2004 ,inv.global_attribute11 global_attribute11
2005 ,inv.global_attribute12 global_attribute12
2006 ,inv.global_attribute13 global_attribute13
2007 ,inv.global_attribute14 global_attribute14
2008 ,inv.global_attribute15 global_attribute15
2009 ,inv.global_attribute16 global_attribute16
2010 ,inv.global_attribute17 global_attribute17
2011 ,inv.global_attribute18 global_attribute18
2012 ,inv.global_attribute19 global_attribute19
2013 ,inv.global_attribute20 global_attribute20
2014 ,inv.global_attribute_category global_attribute_category -- end of bug 5192018
2015 ,Null amount_paid
2016 ,Null discount_amount_taken
2017 ,Null amount_remaining
2018 ,Null discount_amount_remaining
2019 ,Null payment_amount
2020 ,Null discount_amount
2021 ,Null sequence_num
2022 ,Null done_pay_reason_code
2023 ,Null check_number
2024 ,Null bank_account_type
2025 ,Null original_invoice_id
2026 ,Null original_payment_num
2027 ,Null bank_account_num
2028 ,Null bank_num
2029 ,Null proposed_payment_amount
2030 ,Null pay_selected_check_id
2031 ,Null print_selected_check_id
2032 ,Null withholding_amount
2033 ,Null invoice_payment_id
2034 ,Null dont_pay_description
2035 ,Null transfer_priority
2036 ,Null iban_number
2037 ,Null payment_grouping_number
2038 ,Null payment_exchange_rate
2039 ,Null payment_exchange_rate_type
2040 ,Null payment_exchange_date
2041 FROM ap_supplier_sites_all sites,
2042 ap_suppliers suppliers,
2043 ap_invoices inv, --Bug6040657. Changed from ap_invoices_all to ap_invoices
2044 ap_payment_schedules_all ps,
2045 ap_system_parameters_all asp,
2046 hz_parties hzp,
2047 hz_party_sites hzps, -- Bug 5620285
2048 hz_locations hzl -- Bug 5620285
2049 WHERE ps.checkrun_id is null -- Bug 5705276. Regression
2050 AND ((due_date <= p_pay_thru_date +0/24+1 and
2051 due_date >= nvl(p_pay_from_date + 0/24,due_date))
2052 OR
2053 DECODE(NVL(sites.pay_date_basis_lookup_code,'DISCOUNT'),
2054 'DISCOUNT',
2055 DECODE(sites.always_take_disc_flag,
2056 'Y', ps.discount_date,
2057 DECODE(SIGN(p_check_date
2058 -NVL(ps.discount_date,
2059 p_check_date+1)-1),
2060 -1, ps.discount_date,
2061 DECODE(SIGN(p_check_date
2062 -NVL(ps.second_discount_date,
2063 p_check_date+1)-1),
2064 -1, ps.second_discount_date,
2065 DECODE(SIGN(p_check_date
2066 -NVL(ps.third_discount_date,
2067 p_check_date+1)-1),
2068 -1, ps.third_discount_date,
2069 TRUNC(ps.due_date))))),
2070 TRUNC(due_date))
2071 BETWEEN DECODE(sites.always_take_disc_flag,'Y',
2072 nvl(p_pay_from_date, TO_DATE('1901','YYYY')),
2073 p_check_date)
2074 AND p_disc_pay_thru_date)
2075 AND ps.payment_status_flag BETWEEN 'N' AND 'P'
2076 AND inv.payment_status_flag BETWEEN 'N' AND 'P'
2077 AND nvl(inv.force_revalidation_flag, 'N') = 'N' --bug7244642
2078 AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
2079 AND p_lo_payment_priority
2080 AND inv.cancelled_date is null
2081 -- Bug 7167192 Added decode and outer join
2082 -- hzp and hzps data is required only for Payment Requests.
2083 AND hzp.party_id(+) = decode(inv.invoice_type_lookup_code,
2084 'PAYMENT REQUEST', inv.party_id
2085 , -99)
2086 AND NVL(ps.hold_flag, 'N') = 'N'
2087 AND NVL(sites.hold_all_payments_flag, 'N') = 'N'
2088 AND inv.invoice_id = ps.invoice_id
2089 AND sites.vendor_id(+) = inv.vendor_id
2090 AND sites.vendor_site_id(+) = inv.vendor_site_id
2091 AND suppliers.vendor_id(+) = inv.vendor_id
2092 AND asp.org_id = inv.org_id
2093 AND hzp.party_id = hzps.party_id (+) -- Bug 5620285
2094 --Bug 5929034: An employee does not have a hz_party_site, modifying query to reflect the same
2095 -- AND nvl(hzps.party_site_id,-99) = decode(suppliers.vendor_type_lookup_code,'EMPLOYEE',-99,nvl(inv.party_site_id, hzps.party_site_id)) -- Bug 5620285
2096 -- Bug 6662382
2097 -- Bug 7167192 - Query condition is now based on whether the Invoice
2098 -- is a Payment Request. Supplier type does not matter.
2099 --AND NVL(hzps.party_site_id,-99) = DECODE(suppliers.vendor_type_lookup_code,'EMPLOYEE', COALESCE(inv.party_site_id, hzps.party_site_id,-99),
2100 -- NVL(inv.party_site_id, hzps.party_site_id))
2101 AND NVL(hzps.party_site_id,-99) = NVL(decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST', INV.party_site_id, -99), NVL(hzps.party_site_id,-99))
2102 -- Bug 7167192
2103 AND nvl(hzps.location_id,-99) = hzl.location_id (+) -- Bug 5620285
2104 --End of 5929034
2105 AND fv_econ_benf_disc.ebd_check(p_checkrun_name, inv.invoice_id,
2106 p_check_date, due_date, ps.discount_amount_available, ps.discount_date) = 'Y'
2107 AND AP_INVOICES_PKG.get_wfapproval_status(inv.invoice_id, inv.org_id) in
2108 ('NOT REQUIRED','WFAPPROVED','MANUALLY APPROVED')
2109 AND (p_inv_batch_id IS NULL OR
2110 (p_inv_batch_id IS NOT NULL AND inv.batch_id = p_inv_batch_id))
2111 AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
2112 AND inv.party_id = nvl(p_party_id, inv.party_id)
2113 -- Bug 5507013 hkaniven start --
2114 AND (( p_inv_exc_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
2115 OR (p_inv_exc_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
2116 OR (p_inv_exc_rate_type IS NULL))
2117 -- Bug 5507013 hkaniven end --
2118 AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
2119 AND nvl(suppliers.vendor_type_lookup_code,-99) =
2120 nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
2121 AND (inv.legal_entity_id in (select legal_entity_id
2122 from ap_le_group
2123 where checkrun_id = p_checkrun_id)
2124 or p_le_group_option = 'ALL')
2125 AND (inv.org_id in (select org_id
2126 from AP_OU_GROUP
2127 where checkrun_id = p_checkrun_id)
2128 or p_ou_group_option = 'ALL')
2129 AND (inv.payment_currency_code in (select currency_code
2130 from AP_CURRENCY_GROUP
2131 where checkrun_id = p_checkrun_id)
2132 or p_curr_group_option = 'ALL')
2133 AND (inv.pay_group_lookup_code in (select vendor_pay_group
2134 from AP_PAY_GROUP
2135 where checkrun_id = p_checkrun_id)
2136 or p_pay_group_option = 'ALL')
2137 AND ((p_zero_inv_allowed = 'N' AND ps.amount_remaining <> 0) OR
2138 p_zero_inv_allowed = 'Y')
2139 --Bug 6342390 Added the clause below.
2140 --Commented the fix for the bug6342390, bug6365720
2141 /* AND NOT EXISTS (SELECT 'Invoice is not fully approved'
2142 FROM ap_invoice_distributions_all D2
2143 WHERE D2.invoice_id = inv.invoice_id
2144 AND NVL(D2.match_status_flag, 'N') in ('N', 'S'))*/
2145 --bug6365720
2146 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2147 FROM ap_holds H
2148 WHERE H.invoice_id = inv.invoice_id
2149 AND H.release_lookup_code is null)
2150 AND NOT EXISTS (SELECT 'Invoice is not fully approved'
2151 FROM ap_invoices_derived_v AIDV
2152 WHERE AIDV.invoice_id = inv.invoice_id
2153 AND AIDV.approval_status_lookup_code IN
2154 ('NEVER APPROVED', 'NEEDS REAPPROVAL', 'UNAPPROVED'))
2155 AND EXISTS (SELECT 'Distributions exist'
2156 FROM ap_invoice_distributions D4
2157 WHERE D4.invoice_id = inv.invoice_id)
2158 -- bug 6456537
2159 AND NOT EXISTS (SELECT 'CCR EXPIRED'
2160 FROM FV_TPP_ASSIGNMENTS_V TPP
2161 WHERE TPP.beneficiary_party_id = inv.party_id
2162 AND TPP.beneficiary_party_site_id = inv.party_site_id
2163 AND NVL(TPP.fv_tpp_pay_flag, 'Y') = 'N');
2164 -- 6456537 Checking the validity of CCR of the Third Party for
2165 -- supplier. If the CCR is Invalid then the invoice document
2166 -- is not consider for the Payment(Auto Select)
2167
2168 CURSOR pay_sched_cur (p_checkrun_name IN VARCHAR2,
2169 p_checkrun_id IN NUMBER,
2170 p_check_date IN DATE,
2171 p_pay_thru_date IN DATE,
2172 p_pay_from_date IN DATE,
2173 p_disc_pay_thru_date IN DATE,
2174 p_hi_payment_priority IN NUMBER,
2175 p_lo_payment_priority IN NUMBER,
2176 p_inv_batch_id IN NUMBER,
2177 p_inv_vendor_id IN NUMBER,
2178 p_party_id IN NUMBER,
2179 p_inv_exc_rate_type IN VARCHAR2,
2180 p_payment_method IN VARCHAR2,
2181 p_supplier_type IN VARCHAR2,
2182 p_le_group_option IN VARCHAR2,
2183 p_ou_group_option IN VARCHAR2,
2184 p_curr_group_option IN VARCHAR2,
2185 p_pay_group_option IN VARCHAR2,
2186 p_zero_inv_allowed IN VARCHAR2 ) IS
2187 SELECT /*+ NO_EXPAND */
2188 p_checkrun_name checkrun_name
2189 ,p_checkrun_id checkrun_id
2190 ,ps.invoice_id invoice_id
2191 ,ps.payment_num payment_num
2192 ,SYSDATE last_update_date
2193 -- Bug 7383484 (Base bug 7296715)
2194 -- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
2195 -- ,5 last_updated_by
2196 ,FND_GLOBAL.USER_ID last_updated_by
2197 ,SYSDATE creation_date
2198 -- Bug 7383484 (Base bug 7296715)
2199 -- ,5 created_by
2200 ,FND_GLOBAL.USER_ID created_by
2201 ,NULL last_update_login
2202 ,inv.vendor_id vendor_id
2203 ,inv.vendor_site_id vendor_site_id
2204 ,suppliers.segment1 vendor_num
2205 /* Bug 5620285, Added the following decode */
2206 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2207 hzp.party_name, suppliers.vendor_name) vendor_name
2208 ,sites.vendor_site_code vendor_site_code
2209 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2210 hzl.address1, sites.address_line1) address_line1
2211 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2212 hzl.address2, sites.address_line2) address_line2
2213 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2214 hzl.address3, sites.address_line3) address_line3
2215 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2216 hzl.address4, sites.address_line4) address_line4
2217 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2218 hzl.city, sites.city) city
2219 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2220 hzl.state, sites.state) state
2221 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2222 hzl.postal_code, sites.zip) zip
2223 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2224 hzl.province, sites.province) province
2225 ,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
2226 hzl.country, sites.country) country
2227 ,sites.attention_ar_flag attention_ar_flag
2228 ,suppliers.withholding_status_lookup_code withholding_status_lookup_code
2229 ,inv.invoice_num invoice_num
2230 ,inv.invoice_date invoice_date
2231 ,DECODE(inv.doc_sequence_id,
2232 '', inv.voucher_num,
2233 inv.doc_sequence_value) voucher_num
2234 ,inv.accts_pay_code_combination_id ap_ccid
2235 ,TRUNC(ps.due_date) due_date
2236 ,DECODE(sites.always_take_disc_flag,
2237 'Y', TRUNC(ps.due_date),
2238 DECODE(SIGN(p_check_date
2239 - NVL(ps.discount_date,
2240 p_check_date+1)-1),
2241 -1, ps.discount_date,
2242 DECODE(SIGN(p_check_date
2243 -NVL(ps.second_discount_date,
2244 p_check_date+1)-1),
2245 -1, ps.second_discount_date,
2246 DECODE(SIGN(p_check_date
2247 -NVL(ps.third_discount_date,
2248 p_check_date+1)-1),
2249 -1, ps.third_discount_date,
2250 TRUNC(ps.due_date))))) discount_date
2251 ,SUBSTRB(inv.description,1,50) invoice_description
2252 ,nvl(ps.payment_priority, 99) payment_priority
2253 ,'Y' ok_to_pay_flag
2254 ,sites.always_take_disc_flag always_take_discount_flag
2255 ,'N' amount_modified_flag
2256 ,inv.invoice_amount invoice_amount
2257 ,inv.payment_cross_rate payment_cross_rate
2258 ,DECODE(inv.exchange_rate,
2259 NULL, DECODE(inv.invoice_currency_code,
2260 asp.base_currency_code, 1,
2261 NULL),
2262 inv.exchange_rate) invoice_exchange_rate
2263 ,inv.set_of_books_id set_of_books_id
2264 ,sites.customer_num customer_num
2265 ,ps.future_pay_due_date future_pay_due_date
2266 ,inv.exclusive_payment_flag exclusive_payment_flag
2267 ,ps.attribute1 attribute1
2268 ,ps.attribute2 attribute2
2269 ,ps.attribute3 attribute3
2270 ,ps.attribute4 attribute4
2271 ,ps.attribute5 attribute5
2272 ,ps.attribute6 attribute6
2273 ,ps.attribute7 attribute7
2274 ,ps.attribute8 attribute8
2275 ,ps.attribute9 attribute9
2276 ,ps.attribute10 attribute10
2277 ,ps.attribute11 attribute11
2278 ,ps.attribute12 attribute12
2279 ,ps.attribute13 attribure13
2280 ,ps.attribute14 attribute14
2281 ,ps.attribute15 attribute15
2282 ,ps.attribute_category attribute_category
2283 ,inv.org_id org_id
2284 ,inv.payment_currency_code payment_currency_code
2285 ,ps.external_bank_account_id external_bank_account_id
2286 ,inv.legal_entity_id legal_entity_id
2287 /* Bug 5192018 we will insert global attribute values from ap_invoices table */
2288 ,inv.global_attribute1 global_attribute1
2289 ,inv.global_attribute2 global_attribute2
2290 ,inv.global_attribute3 global_attribute3
2291 ,inv.global_attribute4 global_attribute4
2292 ,inv.global_attribute5 global_attribute5
2293 ,inv.global_attribute6 global_attribute6
2294 ,inv.global_attribute7 global_attribute7
2295 ,inv.global_attribute8 global_attribute8
2296 ,inv.global_attribute9 global_attribute9
2297 ,inv.global_attribute10 global_attribute10
2298 ,inv.global_attribute11 global_attribute11
2299 ,inv.global_attribute12 global_attribute12
2300 ,inv.global_attribute13 global_attribute13
2301 ,inv.global_attribute14 global_attribute14
2302 ,inv.global_attribute15 global_attribute15
2303 ,inv.global_attribute16 global_attribute16
2304 ,inv.global_attribute17 global_attribute17
2305 ,inv.global_attribute18 global_attribute18
2306 ,inv.global_attribute19 global_attribute19
2307 ,inv.global_attribute20 global_attribute20
2308 ,inv.global_attribute_category global_attribute_category -- end of bug 5192018
2309 ,Null amount_paid
2310 ,Null discount_amount_taken
2311 ,Null amount_remaining
2312 ,Null discount_amount_remaining
2313 ,Null payment_amount
2314 ,Null discount_amount
2315 ,Null sequence_num
2316 ,Null done_pay_reason_code
2317 ,Null check_number
2318 ,Null bank_account_type
2319 ,Null original_invoice_id
2320 ,Null original_payment_num
2321 ,Null bank_account_num
2322 ,Null bank_num
2323 ,Null proposed_payment_amount
2324 ,Null pay_selected_check_id
2325 ,Null print_selected_check_id
2326 ,Null withholding_amount
2327 ,Null invoice_payment_id
2328 ,Null dont_pay_description
2329 ,Null transfer_priority
2330 ,Null iban_number
2331 ,Null payment_grouping_number
2332 ,Null payment_exchange_rate
2333 ,Null payment_exchange_rate_type
2334 ,Null payment_exchange_date
2335 FROM ap_supplier_sites_all sites,
2336 ap_suppliers suppliers,
2337 ap_invoices inv, --Bug6040657. Changed from ap_invoices_all to ap_invoices
2338 ap_payment_schedules_all ps,
2339 ap_system_parameters_all asp,
2340 hz_parties hzp,
2341 hz_party_sites hzps, -- Bug 5620285
2342 hz_locations hzl -- Bug 5620285
2343 WHERE ps.checkrun_id is null -- Bug 5705276. Regression
2344 AND ((due_date <= p_pay_thru_date +0/24+1 and
2345 due_date >= nvl(p_pay_from_date + 0/24,due_date))
2346 OR
2347 DECODE(NVL(sites.pay_date_basis_lookup_code,'DISCOUNT'),
2348 'DISCOUNT',
2349 DECODE(sites.always_take_disc_flag,
2350 'Y', ps.discount_date,
2351 DECODE(SIGN(p_check_date
2352 -NVL(ps.discount_date,
2353 p_check_date+1)-1),
2354 -1, ps.discount_date,
2355 DECODE(SIGN(p_check_date
2356 -NVL(ps.second_discount_date,
2357 p_check_date+1)-1),
2358 -1, ps.second_discount_date,
2359 DECODE(SIGN(p_check_date
2360 -NVL(ps.third_discount_date,
2361 p_check_date+1)-1),
2362 -1, ps.third_discount_date,
2363 TRUNC(ps.due_date))))),
2364 TRUNC(due_date))
2365 BETWEEN DECODE(sites.always_take_disc_flag,'Y',
2366 nvl(p_pay_from_date, TO_DATE('1901','YYYY')),
2367 p_check_date)
2368 AND p_disc_pay_thru_date)
2369 AND ps.payment_status_flag BETWEEN 'N' AND 'P'
2370 AND nvl(inv.force_revalidation_flag, 'N') = 'N' --bug7244642
2371 AND inv.payment_status_flag BETWEEN 'N' AND 'P'
2372 AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
2373 AND p_lo_payment_priority
2374 AND inv.cancelled_date is null
2375 -- Bug 7167192 Added decode
2376 -- hzp and hzps data is required only for Payment Requests.
2377 AND hzp.party_id(+) = decode(inv.invoice_type_lookup_code,
2378 'PAYMENT REQUEST', inv.party_id
2379 , -99)
2380 AND NVL(ps.hold_flag, 'N') = 'N'
2381 AND NVL(sites.hold_all_payments_flag, 'N') = 'N'
2382 AND inv.invoice_id = ps.invoice_id
2383 AND sites.vendor_id(+) = inv.vendor_id
2384 AND sites.vendor_site_id(+) = inv.vendor_site_id
2385 AND suppliers.vendor_id(+) = inv.vendor_id
2386 AND asp.org_id = inv.org_id
2387 AND hzp.party_id = hzps.party_id (+) -- Bug 5620285
2388 --Bug 5929034: An employee does not have a hz_party_site changing query to reflect the same
2389 -- AND nvl(hzps.party_site_id,-99) = decode(suppliers.vendor_type_lookup_code,'EMPLOYEE',-99,nvl(inv.party_site_id, hzps.party_site_id)) -- Bug 5620285
2390 -- Bug 6662382
2391 -- Bug 7167192 - Query condition is now based on whether the Invoice
2392 -- is a Payment Request. Supplier type does not matter.
2393 --AND NVL(hzps.party_site_id,-99) = DECODE(suppliers.vendor_type_lookup_code,'EMPLOYEE', COALESCE(inv.party_site_id, hzps.party_site_id,-99),
2394 -- NVL(inv.party_site_id, hzps.party_site_id))
2395 AND NVL(hzps.party_site_id,-99) = NVL(decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST', INV.party_site_id, -99), hzps.party_site_id)
2396 -- Bug 7167192
2397 AND nvl(hzps.location_id,-99) = hzl.location_id(+) -- Bug 5620285
2398 --End Bug 5929034
2399 AND fv_econ_benf_disc.ebd_check(p_checkrun_name, inv.invoice_id,
2400 p_check_date, due_date, ps.discount_amount_available, ps.discount_date) = 'Y'
2401 AND AP_INVOICES_PKG.get_wfapproval_status(inv.invoice_id, inv.org_id) in
2402 ('NOT REQUIRED','WFAPPROVED','MANUALLY APPROVED')
2403 AND (p_inv_batch_id IS NULL OR
2404 (p_inv_batch_id IS NOT NULL AND inv.batch_id = p_inv_batch_id))
2405 AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
2406 AND inv.party_id = nvl(p_party_id, inv.party_id)
2407 -- Bug 5507013 hkaniven start --
2408 AND (( p_inv_exc_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
2409 OR (p_inv_exc_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
2410 OR (p_inv_exc_rate_type IS NULL))
2411 -- Bug 5507013 hkaniven end --
2412 AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
2413 AND nvl(suppliers.vendor_type_lookup_code,-99) =
2414 nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
2415 AND (inv.legal_entity_id in (select legal_entity_id
2416 from ap_le_group
2417 where checkrun_id = p_checkrun_id)
2418 or p_le_group_option = 'ALL')
2419 AND (inv.org_id in (select org_id
2420 from AP_OU_GROUP
2421 where checkrun_id = p_checkrun_id)
2422 or p_ou_group_option = 'ALL')
2423 AND (inv.payment_currency_code in (select currency_code
2424 from AP_CURRENCY_GROUP
2425 where checkrun_id = p_checkrun_id)
2426 or p_curr_group_option = 'ALL')
2427 AND (inv.pay_group_lookup_code in (select vendor_pay_group
2428 from AP_PAY_GROUP
2429 where checkrun_id = p_checkrun_id)
2430 or p_pay_group_option = 'ALL')
2431 AND ((p_zero_inv_allowed = 'N' AND ps.amount_remaining <> 0) OR
2432 p_zero_inv_allowed = 'Y')
2433 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2434 FROM ap_holds_all H
2435 WHERE H.invoice_id = inv.invoice_id
2436 AND H.release_lookup_code is null)
2437 AND NOT EXISTS (SELECT 'Invoice is not fully approved'
2438 FROM ap_invoice_distributions_all D2
2439 WHERE D2.invoice_id = inv.invoice_id
2440 AND NVL(D2.match_status_flag, 'N') in ('N', 'S'))
2441 AND EXISTS (SELECT 'Distributions exist'
2442 FROM ap_invoice_distributions_all D4
2443 WHERE D4.invoice_id = inv.invoice_id)
2444 -- bug 6456537
2445 AND NOT EXISTS (SELECT 'CCR EXPIRED'
2446 FROM FV_TPP_ASSIGNMENTS_V TPP
2447 WHERE TPP.beneficiary_party_id = inv.party_id
2448 AND TPP.beneficiary_party_site_id = inv.party_site_id
2449 AND NVL(TPP.fv_tpp_pay_flag, 'Y') = 'N');
2450 -- 6456537 Checking the validity of CCR of the Third Party for
2451 -- supplier. If the CCR is Invalid then the invoice document
2452 -- is not consider for the Payment(Auto Select)
2453
2454
2455
2456 BEGIN
2457
2458
2459
2460 l_current_calling_sequence := 'select invoices';
2461
2462 l_debug_info := 'Check to see if creating checkrun from template';
2463 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2464 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2465 END IF;
2466
2467 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2469 END IF;
2470
2471
2472
2473 ---if this is a repeating request call a procedure to insert into ap_inv_selection_criteria_all
2474 --the hld says to validate the parameters, but this should probably be done
2475 --in the value sets or setup for the pay process request
2476
2477
2478 --l_checkrun_id will be assigned from create_checkrun if this is a
2479 --repeating request
2480 l_checkrun_id := to_number(p_checkrun_id);
2481
2482
2483 if p_template_id is not null then
2484
2485 l_template_id := to_number(p_template_id);
2486 l_payment_date := FND_DATE.CANONICAL_TO_DATE(P_payment_date);
2487 l_pay_thru_date := FND_DATE.CANONICAL_TO_DATE(P_pay_thru_date);
2488 l_pay_from_date := FND_DATE.CANONICAL_TO_DATE(P_pay_from_date);
2489
2490 create_checkrun(l_checkrun_id,
2491 l_template_id,
2492 L_payment_date,
2493 L_pay_thru_date,
2494 L_pay_from_date,
2495 l_current_calling_sequence);
2496
2497 end if;
2498
2499
2500
2501 --get data from ap_inv_selection_criteria
2502
2503
2504 l_debug_info := 'Select data from ap_invoice_selection_criteria';
2505 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2506 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2507 END IF;
2508
2509 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2510 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2511 END IF;
2512
2513
2514 BEGIN
2515 -- Bug 5460584. Added trun for check_date, pay_thru_date, pay_from_date
2516 SELECT
2517 trunc(check_date),
2518 trunc(pay_thru_date),
2519 NVL(hi_payment_priority,1),
2520 NVL(low_payment_priority,99),
2521 DECODE(pay_only_when_due_flag,'Y',
2522 to_date('01/01/80','MM/DD/RR'),
2523 trunc(pay_thru_date)),
2524 DECODE(status,'SELECTING','N','Y'),
2525 nvl(zero_amounts_allowed,'N'),
2526 nvl(zero_invoices_allowed,'N'),
2527 invoice_batch_id,
2528 vendor_id,
2529 checkrun_name,
2530 trunc(pay_from_date),
2531 inv_exchange_rate_type,
2532 payment_method_code,
2533 vendor_type_lookup_code,
2534 ou_group_option,
2535 le_group_option,
2536 currency_group_option,
2537 pay_group_option,
2538 exchange_rate_type,
2539 payables_review_settings,
2540 bank_account_id, --4710933
2541 payment_profile_id,
2542 max_payment_amount,
2543 min_check_amount,
2544 payments_review_settings,
2545 decode(payment_profile_id,null,'N',nvl(create_instrs_flag,'N')),
2546 party_id,
2547 payment_document_id,
2548 /*bug 7519277*/
2549 ATTRIBUTE_CATEGORY,
2550 ATTRIBUTE1,
2551 ATTRIBUTE2,
2552 ATTRIBUTE3,
2553 ATTRIBUTE4,
2554 ATTRIBUTE5,
2555 ATTRIBUTE6,
2556 ATTRIBUTE7,
2557 ATTRIBUTE8,
2558 ATTRIBUTE9,
2559 ATTRIBUTE10,
2560 ATTRIBUTE11,
2561 ATTRIBUTE12,
2562 ATTRIBUTE13,
2563 ATTRIBUTE14,
2564 ATTRIBUTE15
2565 /*bug 7519277*/
2566 INTO
2567 l_check_date,
2568 l_pay_thru_date,
2569 l_hi_payment_priority,
2570 l_low_payment_priority,
2571 l_disc_pay_thru_date,
2572 l_abort,
2573 l_zero_amounts_allowed,
2574 l_zero_invoices_allowed,
2575 l_invoice_batch_id,
2576 l_inv_vendor_id,
2577 l_payment_process_request_name,
2578 l_pay_from_date,
2579 l_inv_exchange_rate_type,
2580 l_payment_method,
2581 l_supplier_type,
2582 l_ou_group_option,
2583 l_le_group_option,
2584 l_currency_group_option,
2585 l_pay_group_option,
2586 l_batch_exchange_rate_type,
2587 l_payables_review_settings,
2588 l_bank_account_id ,
2589 l_payment_profile_id,
2590 l_max_payment_amount,
2591 l_min_check_amount,
2592 l_pay_review_settings_flag,
2593 l_create_instrs_flag,
2594 l_party_id,
2595 l_payment_document_id,
2596 /* bug 7519277*/
2597 l_ATTRIBUTE_CATEGORY,
2598 l_ATTRIBUTE1,
2599 l_ATTRIBUTE2,
2600 l_ATTRIBUTE3,
2601 l_ATTRIBUTE4,
2602 l_ATTRIBUTE5,
2603 l_ATTRIBUTE6,
2604 l_ATTRIBUTE7,
2605 l_ATTRIBUTE8,
2606 l_ATTRIBUTE9,
2607 l_ATTRIBUTE10,
2608 l_ATTRIBUTE11,
2609 l_ATTRIBUTE12,
2610 l_ATTRIBUTE13,
2611 l_ATTRIBUTE14,
2612 l_ATTRIBUTE15
2613 FROM ap_inv_selection_criteria_all
2614 WHERE checkrun_id = l_checkrun_id
2615 AND status = 'UNSTARTED';
2616
2617 EXCEPTION
2618 WHEN NO_DATA_FOUND then
2619 l_debug_info := 'Could not find the payment process';
2620 raise SELECTION_FAILURE;
2621 END;
2622
2623
2624 UPDATE ap_inv_selection_criteria_all
2625 set status = 'SELECTING',
2626 -- Bug 7492768 We need to reset the inv_awt_exists_flag which indicates if the
2627 -- check run contains invoice that has awt.
2628 inv_awt_exists_flag = 'N'
2629 where checkrun_id = l_checkrun_id;
2630
2631
2632
2633 l_debug_info := 'l_checkrun_id = '|| to_char(l_checkrun_id);
2634 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2635 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2636 END IF;
2637
2638 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2639 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2640 END IF;
2641
2642
2643 l_debug_info := 'See if encumbrances are turned on';
2644 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2645 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2646 END IF;
2647
2648 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2649 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2650 END IF;
2651
2652 SELECT count(*)
2653 INTO l_encumbrance_flag
2654 FROM financials_system_parameters
2655 WHERE nvl(purch_encumbrance_flag,'N') = 'Y'
2656 AND (org_id in (select org_id
2657 from AP_OU_GROUP
2658 where checkrun_id = l_checkrun_id)
2659 or l_ou_group_option = 'ALL')
2660 AND rownum=1;
2661
2662
2663
2664 -- Bug 5646890. Cursor processing with FORALL to make sure update and insert
2665 -- only do one one pass to the tables
2666 -- Based on encumbrances two different cursor will be opened and data will be
2667 -- processed.
2668
2669 if l_encumbrance_flag = 1 then
2670
2671 l_debug_info := 'Open payment schedules cursor - encumbrances are on';
2672 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2673 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2674 END IF;
2675
2676 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2677 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2678 END IF;
2679
2680 OPEN pay_sched_enc_cur (l_payment_process_request_name,
2681 l_checkrun_id,
2682 l_check_date,
2683 l_pay_thru_date,
2684 l_pay_from_date,
2685 l_disc_pay_thru_date,
2686 l_hi_payment_priority,
2687 l_low_payment_priority,
2688 l_invoice_batch_id,
2689 l_inv_vendor_id,
2690 l_party_id,
2691 l_inv_exchange_rate_type,
2692 l_payment_method,
2693 l_supplier_type,
2694 l_le_group_option,
2695 l_ou_group_option,
2696 l_currency_group_option,
2697 l_pay_group_option,
2698 l_zero_invoices_allowed);
2699 LOOP
2700 FETCH pay_sched_enc_cur
2701 BULK COLLECT INTO
2702 sel_inv_list.checkrun_name_l
2703 ,sel_inv_list.checkrun_id_l
2704 ,sel_inv_list.invoice_id_l
2705 ,sel_inv_list.payment_num_l
2706 ,sel_inv_list.last_update_date_l
2707 ,sel_inv_list.last_updated_by_l
2708 ,sel_inv_list.creation_date_l
2709 ,sel_inv_list.created_by_l
2710 ,sel_inv_list.last_update_login_l
2711 ,sel_inv_list.vendor_id_l
2712 ,sel_inv_list.vendor_site_id_l
2713 ,sel_inv_list.vendor_num_l
2714 ,sel_inv_list.vendor_name_l
2715 ,sel_inv_list.vendor_site_code_l
2716 ,sel_inv_list.address_line1_l
2717 ,sel_inv_list.address_line2_l
2718 ,sel_inv_list.address_line3_l
2719 ,sel_inv_list.address_line4_l
2720 ,sel_inv_list.city_l
2721 ,sel_inv_list.state_l
2722 ,sel_inv_list.zip_l
2723 ,sel_inv_list.province_l
2724 ,sel_inv_list.country_l
2725 ,sel_inv_list.attention_ar_flag_l
2726 ,sel_inv_list.withholding_status_lookup_l
2727 ,sel_inv_list.invoice_num_l
2728 ,sel_inv_list.invoice_date_l
2729 ,sel_inv_list.voucher_num_l
2730 ,sel_inv_list.ap_ccid_l
2731 ,sel_inv_list.due_date_l
2732 ,sel_inv_list.discount_date_l
2733 ,sel_inv_list.invoice_description_l
2734 ,sel_inv_list.payment_priority_l
2735 ,sel_inv_list.ok_to_pay_flag_l
2736 ,sel_inv_list.always_take_disc_flag_l
2737 ,sel_inv_list.amount_modified_flag_l
2738 ,sel_inv_list.invoice_amount_l
2739 ,sel_inv_list.payment_cross_rate_l
2740 ,sel_inv_list.invoice_exchange_rate_l
2741 ,sel_inv_list.set_of_books_id_l
2742 ,sel_inv_list.customer_num_l
2743 ,sel_inv_list.future_pay_due_date_l
2744 ,sel_inv_list.exclusive_payment_flag_l
2745 ,sel_inv_list.attribute1_l
2746 ,sel_inv_list.attribute2_l
2747 ,sel_inv_list.attribute3_l
2748 ,sel_inv_list.attribute4_l
2749 ,sel_inv_list.attribute5_l
2750 ,sel_inv_list.attribute6_l
2751 ,sel_inv_list.attribute7_l
2752 ,sel_inv_list.attribute8_l
2753 ,sel_inv_list.attribute9_l
2754 ,sel_inv_list.attribute10_l
2755 ,sel_inv_list.attribute11_l
2756 ,sel_inv_list.attribute12_l
2757 ,sel_inv_list.attribute13_l
2758 ,sel_inv_list.attribute14_l
2759 ,sel_inv_list.attribute15_l
2760 ,sel_inv_list.attribute_category_l
2761 ,sel_inv_list.org_id_l
2762 ,sel_inv_list.payment_currency_code_l
2763 ,sel_inv_list.external_bank_account_id_l
2764 ,sel_inv_list.legal_entity_id_l
2765 ,sel_inv_list.global_attribute1_l
2766 ,sel_inv_list.global_attribute2_l
2767 ,sel_inv_list.global_attribute3_l
2768 ,sel_inv_list.global_attribute4_l
2769 ,sel_inv_list.global_attribute5_l
2770 ,sel_inv_list.global_attribute6_l
2771 ,sel_inv_list.global_attribute7_l
2772 ,sel_inv_list.global_attribute8_l
2773 ,sel_inv_list.global_attribute9_l
2774 ,sel_inv_list.global_attribute10_l
2775 ,sel_inv_list.global_attribute11_l
2776 ,sel_inv_list.global_attribute12_l
2777 ,sel_inv_list.global_attribute13_l
2778 ,sel_inv_list.global_attribute14_l
2779 ,sel_inv_list.global_attribute15_l
2780 ,sel_inv_list.global_attribute16_l
2781 ,sel_inv_list.global_attribute17_l
2782 ,sel_inv_list.global_attribute18_l
2783 ,sel_inv_list.global_attribute19_l
2784 ,sel_inv_list.global_attribute20_l
2785 ,sel_inv_list.global_attribute_category_l
2786 ,sel_inv_list.amount_paid_l
2787 ,sel_inv_list.discount_amount_taken_l
2788 ,sel_inv_list.amount_remaining_l
2789 ,sel_inv_list.discount_amount_remaining_l
2790 ,sel_inv_list.payment_amount_l
2791 ,sel_inv_list.discount_amount_l
2792 ,sel_inv_list.sequence_num_l
2793 ,sel_inv_list.dont_pay_reason_code_l
2794 ,sel_inv_list.check_number_l
2795 ,sel_inv_list.bank_account_type_l
2796 ,sel_inv_list.original_invoice_id_l
2797 ,sel_inv_list.original_payment_num_l
2798 ,sel_inv_list.bank_account_num_l
2799 ,sel_inv_list.bank_num_l
2800 ,sel_inv_list.proposed_payment_amount_l
2801 ,sel_inv_list.pay_selected_check_id_l
2802 ,sel_inv_list.print_selected_check_id_l
2803 ,sel_inv_list.withhloding_amount_l
2804 ,sel_inv_list.invoice_payment_id_l
2805 ,sel_inv_list.dont_pay_description_l
2806 ,sel_inv_list.transfer_priority_l
2807 ,sel_inv_list.iban_number_l
2808 ,sel_inv_list.payment_grouping_number_l
2809 ,sel_inv_list.payment_exchange_rate_l
2810 ,sel_inv_list.payment_exchange_rate_type_l
2811 ,sel_inv_list.payment_exchange_date_l
2812 LIMIT 1000;
2813
2814
2815 l_debug_info := 'Update ap_payment_schedules_all: encumbrances are on';
2816 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2817 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2818 END IF;
2819
2820 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2821 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2822 END IF;
2823
2824
2825 FORALL i IN sel_inv_list.invoice_id_l.FIRST .. sel_inv_list.invoice_id_l.LAST
2826 UPDATE Ap_Payment_Schedules_All
2827 SET checkrun_id = sel_inv_list.checkrun_id_l(i)
2828 WHERE invoice_id = sel_inv_list.invoice_id_l(i)
2829 AND payment_num = sel_inv_list.payment_num_l(i)
2830 AND checkrun_id IS NULL --bug 6788730
2831 ;
2832
2833
2834 l_debug_info := 'Insert into ap_selected_invoices_all: encumbrances are on';
2835 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2836 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
2837 END IF;
2838
2839 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2840 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2841 END IF;
2842
2843
2844 FORALL i IN sel_inv_list.invoice_id_l.FIRST .. sel_inv_list.invoice_id_l.LAST
2845 INSERT INTO ap_selected_invoices_all
2846 (checkrun_name
2847 ,checkrun_id
2848 ,invoice_id
2849 ,payment_num
2850 ,last_update_date
2851 ,last_updated_by
2852 ,creation_date
2853 ,created_by
2854 ,vendor_id
2855 ,vendor_site_id
2856 ,vendor_num
2857 ,vendor_name
2858 ,vendor_site_code
2859 ,address_line1
2860 ,address_line2
2861 ,address_line3
2862 ,address_line4
2863 ,city
2864 ,state
2865 ,zip
2866 ,province
2867 ,country
2868 ,attention_ar_flag
2869 ,withholding_status_lookup_code
2870 ,invoice_num
2871 ,invoice_date
2872 ,voucher_num
2873 ,ap_ccid
2874 ,due_date
2875 ,discount_date
2876 ,invoice_description
2877 ,payment_priority
2878 ,ok_to_pay_flag
2879 ,always_take_discount_flag
2880 ,amount_modified_flag
2881 ,invoice_amount
2882 ,payment_cross_rate
2883 ,invoice_exchange_rate
2884 ,set_of_books_id
2885 ,customer_num
2886 ,future_pay_due_date
2887 ,exclusive_payment_flag
2888 ,attribute1
2889 ,attribute2
2890 ,attribute3
2891 ,attribute4
2892 ,attribute5
2893 ,attribute6
2894 ,attribute7
2895 ,attribute8
2896 ,attribute9
2897 ,attribute10
2898 ,attribute11
2899 ,attribute12
2900 ,attribute13
2901 ,attribute14
2902 ,attribute15
2903 ,attribute_category
2904 ,org_id
2905 ,payment_currency_code
2906 ,external_bank_account_id
2907 ,legal_entity_id
2908 ,global_attribute1
2909 ,global_attribute2
2910 ,global_attribute3
2911 ,global_attribute4
2912 ,global_attribute5
2913 ,global_attribute6
2914 ,global_attribute7
2915 ,global_attribute8
2916 ,global_attribute9
2917 ,global_attribute10
2918 ,global_attribute11
2919 ,global_attribute12
2920 ,global_attribute13
2921 ,global_attribute14
2922 ,global_attribute15
2923 ,global_attribute16
2924 ,global_attribute17
2925 ,global_attribute18
2926 ,global_attribute19
2927 ,global_attribute20
2928 ,global_attribute_category)
2929 --bug 6788730 Changed this to SELECT
2930 --VALUES
2931 (
2932 SELECT
2933 sel_inv_list.checkrun_name_l(i)
2934 ,sel_inv_list.checkrun_id_l(i)
2935 ,sel_inv_list.invoice_id_l(i)
2936 ,sel_inv_list.payment_num_l(i)
2937 ,sel_inv_list.last_update_date_l(i)
2938 ,sel_inv_list.last_updated_by_l(i)
2939 ,sel_inv_list.creation_date_l(i)
2940 ,sel_inv_list.created_by_l(i)
2941 ,sel_inv_list.vendor_id_l(i)
2942 ,sel_inv_list.vendor_site_id_l(i)
2943 ,sel_inv_list.vendor_num_l(i)
2944 ,sel_inv_list.vendor_name_l(i)
2945 ,sel_inv_list.vendor_site_code_l(i)
2946 ,sel_inv_list.address_line1_l(i)
2947 ,sel_inv_list.address_line2_l(i)
2948 ,sel_inv_list.address_line3_l(i)
2949 ,sel_inv_list.address_line4_l(i)
2950 ,sel_inv_list.city_l(i)
2951 ,sel_inv_list.state_l(i)
2952 ,sel_inv_list.zip_l(i)
2953 ,sel_inv_list.province_l(i)
2954 ,sel_inv_list.country_l(i)
2955 ,sel_inv_list.attention_ar_flag_l(i)
2956 ,sel_inv_list.withholding_status_lookup_l(i)
2957 ,sel_inv_list.invoice_num_l(i)
2958 ,sel_inv_list.invoice_date_l(i)
2959 ,sel_inv_list.voucher_num_l(i)
2960 ,sel_inv_list.ap_ccid_l(i)
2961 ,sel_inv_list.due_date_l(i)
2962 ,sel_inv_list.discount_date_l(i)
2963 ,sel_inv_list.invoice_description_l(i)
2964 ,sel_inv_list.payment_priority_l(i)
2965 ,sel_inv_list.ok_to_pay_flag_l(i)
2966 ,sel_inv_list.always_take_disc_flag_l(i)
2967 ,sel_inv_list.amount_modified_flag_l(i)
2968 ,sel_inv_list.invoice_amount_l(i)
2969 ,sel_inv_list.payment_cross_rate_l(i)
2970 ,sel_inv_list.invoice_exchange_rate_l(i)
2971 ,sel_inv_list.set_of_books_id_l(i)
2972 ,sel_inv_list.customer_num_l(i)
2973 ,sel_inv_list.future_pay_due_date_l(i)
2974 ,sel_inv_list.exclusive_payment_flag_l(i)
2975 ,sel_inv_list.attribute1_l(i)
2976 ,sel_inv_list.attribute2_l(i)
2977 ,sel_inv_list.attribute3_l(i)
2978 ,sel_inv_list.attribute4_l(i)
2979 ,sel_inv_list.attribute5_l(i)
2980 ,sel_inv_list.attribute6_l(i)
2981 ,sel_inv_list.attribute7_l(i)
2982 ,sel_inv_list.attribute8_l(i)
2983 ,sel_inv_list.attribute9_l(i)
2984 ,sel_inv_list.attribute10_l(i)
2985 ,sel_inv_list.attribute11_l(i)
2986 ,sel_inv_list.attribute12_l(i)
2987 ,sel_inv_list.attribute13_l(i)
2988 ,sel_inv_list.attribute14_l(i)
2989 ,sel_inv_list.attribute15_l(i)
2990 ,sel_inv_list.attribute_category_l(i)
2991 ,sel_inv_list.org_id_l(i)
2992 ,sel_inv_list.payment_currency_code_l(i)
2993 ,sel_inv_list.external_bank_account_id_l(i)
2994 ,sel_inv_list.legal_entity_id_l(i)
2995 ,sel_inv_list.global_attribute1_l(i)
2996 ,sel_inv_list.global_attribute2_l(i)
2997 ,sel_inv_list.global_attribute3_l(i)
2998 ,sel_inv_list.global_attribute4_l(i)
2999 ,sel_inv_list.global_attribute5_l(i)
3000 ,sel_inv_list.global_attribute6_l(i)
3001 ,sel_inv_list.global_attribute7_l(i)
3002 ,sel_inv_list.global_attribute8_l(i)
3003 ,sel_inv_list.global_attribute9_l(i)
3004 ,sel_inv_list.global_attribute10_l(i)
3005 ,sel_inv_list.global_attribute11_l(i)
3006 ,sel_inv_list.global_attribute12_l(i)
3007 ,sel_inv_list.global_attribute13_l(i)
3008 ,sel_inv_list.global_attribute14_l(i)
3009 ,sel_inv_list.global_attribute15_l(i)
3010 ,sel_inv_list.global_attribute16_l(i)
3011 ,sel_inv_list.global_attribute17_l(i)
3012 ,sel_inv_list.global_attribute18_l(i)
3013 ,sel_inv_list.global_attribute19_l(i)
3014 ,sel_inv_list.global_attribute20_l(i)
3015 ,sel_inv_list.global_attribute_category_l(i)
3016 FROM Ap_Payment_Schedules_All
3017 WHERE invoice_id = sel_inv_list.invoice_id_l(i)
3018 AND payment_num = sel_inv_list.payment_num_l(i)
3019 AND checkrun_id = sel_inv_list.checkrun_id_l(i)
3020 --bug 6788730
3021 );
3022
3023 EXIT WHEN pay_sched_enc_cur%NOTFOUND;
3024 END LOOP;
3025
3026 COMMIT;
3027
3028 CLOSE pay_sched_enc_cur;
3029
3030
3031 else --no encumbrances used
3032
3033 l_debug_info := 'Open payment schedules cursor- encumbrances are off';
3034 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3035 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3036 END IF;
3037
3038 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3039 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3040 END IF;
3041
3042
3043 OPEN pay_sched_cur (l_payment_process_request_name,
3044 l_checkrun_id,
3045 l_check_date,
3046 l_pay_thru_date,
3047 l_pay_from_date,
3048 l_disc_pay_thru_date,
3049 l_hi_payment_priority,
3050 l_low_payment_priority,
3051 l_invoice_batch_id,
3052 l_inv_vendor_id,
3053 l_party_id,
3054 l_inv_exchange_rate_type,
3055 l_payment_method,
3056 l_supplier_type,
3057 l_le_group_option,
3058 l_ou_group_option,
3059 l_currency_group_option,
3060 l_pay_group_option,
3061 l_zero_invoices_allowed);
3062
3063
3064 LOOP
3065 FETCH pay_sched_cur
3066 BULK COLLECT INTO
3067 sel_inv_list.checkrun_name_l
3068 ,sel_inv_list.checkrun_id_l
3069 ,sel_inv_list.invoice_id_l
3070 ,sel_inv_list.payment_num_l
3071 ,sel_inv_list.last_update_date_l
3072 ,sel_inv_list.last_updated_by_l
3073 ,sel_inv_list.creation_date_l
3074 ,sel_inv_list.created_by_l
3075 ,sel_inv_list.last_update_login_l
3076 ,sel_inv_list.vendor_id_l
3077 ,sel_inv_list.vendor_site_id_l
3078 ,sel_inv_list.vendor_num_l
3079 ,sel_inv_list.vendor_name_l
3080 ,sel_inv_list.vendor_site_code_l
3081 ,sel_inv_list.address_line1_l
3082 ,sel_inv_list.address_line2_l
3083 ,sel_inv_list.address_line3_l
3084 ,sel_inv_list.address_line4_l
3085 ,sel_inv_list.city_l
3086 ,sel_inv_list.state_l
3087 ,sel_inv_list.zip_l
3088 ,sel_inv_list.province_l
3089 ,sel_inv_list.country_l
3090 ,sel_inv_list.attention_ar_flag_l
3091 ,sel_inv_list.withholding_status_lookup_l
3092 ,sel_inv_list.invoice_num_l
3093 ,sel_inv_list.invoice_date_l
3094 ,sel_inv_list.voucher_num_l
3095 ,sel_inv_list.ap_ccid_l
3096 ,sel_inv_list.due_date_l
3097 ,sel_inv_list.discount_date_l
3098 ,sel_inv_list.invoice_description_l
3099 ,sel_inv_list.payment_priority_l
3100 ,sel_inv_list.ok_to_pay_flag_l
3101 ,sel_inv_list.always_take_disc_flag_l
3102 ,sel_inv_list.amount_modified_flag_l
3103 ,sel_inv_list.invoice_amount_l
3104 ,sel_inv_list.payment_cross_rate_l
3105 ,sel_inv_list.invoice_exchange_rate_l
3106 ,sel_inv_list.set_of_books_id_l
3107 ,sel_inv_list.customer_num_l
3108 ,sel_inv_list.future_pay_due_date_l
3109 ,sel_inv_list.exclusive_payment_flag_l
3110 ,sel_inv_list.attribute1_l
3111 ,sel_inv_list.attribute2_l
3112 ,sel_inv_list.attribute3_l
3113 ,sel_inv_list.attribute4_l
3114 ,sel_inv_list.attribute5_l
3115 ,sel_inv_list.attribute6_l
3116 ,sel_inv_list.attribute7_l
3117 ,sel_inv_list.attribute8_l
3118 ,sel_inv_list.attribute9_l
3119 ,sel_inv_list.attribute10_l
3120 ,sel_inv_list.attribute11_l
3121 ,sel_inv_list.attribute12_l
3122 ,sel_inv_list.attribute13_l
3123 ,sel_inv_list.attribute14_l
3124 ,sel_inv_list.attribute15_l
3125 ,sel_inv_list.attribute_category_l
3126 ,sel_inv_list.org_id_l
3127 ,sel_inv_list.payment_currency_code_l
3128 ,sel_inv_list.external_bank_account_id_l
3129 ,sel_inv_list.legal_entity_id_l
3130 ,sel_inv_list.global_attribute1_l
3131 ,sel_inv_list.global_attribute2_l
3132 ,sel_inv_list.global_attribute3_l
3133 ,sel_inv_list.global_attribute4_l
3134 ,sel_inv_list.global_attribute5_l
3135 ,sel_inv_list.global_attribute6_l
3136 ,sel_inv_list.global_attribute7_l
3137 ,sel_inv_list.global_attribute8_l
3138 ,sel_inv_list.global_attribute9_l
3139 ,sel_inv_list.global_attribute10_l
3140 ,sel_inv_list.global_attribute11_l
3141 ,sel_inv_list.global_attribute12_l
3142 ,sel_inv_list.global_attribute13_l
3143 ,sel_inv_list.global_attribute14_l
3144 ,sel_inv_list.global_attribute15_l
3145 ,sel_inv_list.global_attribute16_l
3146 ,sel_inv_list.global_attribute17_l
3147 ,sel_inv_list.global_attribute18_l
3148 ,sel_inv_list.global_attribute19_l
3149 ,sel_inv_list.global_attribute20_l
3150 ,sel_inv_list.global_attribute_category_l
3151 ,sel_inv_list.amount_paid_l
3152 ,sel_inv_list.discount_amount_taken_l
3153 ,sel_inv_list.amount_remaining_l
3154 ,sel_inv_list.discount_amount_remaining_l
3155 ,sel_inv_list.payment_amount_l
3156 ,sel_inv_list.discount_amount_l
3157 ,sel_inv_list.sequence_num_l
3158 ,sel_inv_list.dont_pay_reason_code_l
3159 ,sel_inv_list.check_number_l
3160 ,sel_inv_list.bank_account_type_l
3161 ,sel_inv_list.original_invoice_id_l
3162 ,sel_inv_list.original_payment_num_l
3163 ,sel_inv_list.bank_account_num_l
3164 ,sel_inv_list.bank_num_l
3165 ,sel_inv_list.proposed_payment_amount_l
3166 ,sel_inv_list.pay_selected_check_id_l
3167 ,sel_inv_list.print_selected_check_id_l
3168 ,sel_inv_list.withhloding_amount_l
3169 ,sel_inv_list.invoice_payment_id_l
3170 ,sel_inv_list.dont_pay_description_l
3171 ,sel_inv_list.transfer_priority_l
3172 ,sel_inv_list.iban_number_l
3173 ,sel_inv_list.payment_grouping_number_l
3174 ,sel_inv_list.payment_exchange_rate_l
3175 ,sel_inv_list.payment_exchange_rate_type_l
3176 ,sel_inv_list.payment_exchange_date_l
3177 LIMIT 1000;
3178
3179 l_debug_info := 'Update ap_payment_schedules_all: encumbrances are off';
3180 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3181 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3182 END IF;
3183
3184 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3185 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3186 END IF;
3187
3188
3189 FORALL i IN sel_inv_list.invoice_id_l.FIRST .. sel_inv_list.invoice_id_l.LAST
3190 UPDATE Ap_Payment_Schedules_All
3191 SET checkrun_id = sel_inv_list.checkrun_id_l(i)
3192 WHERE invoice_id = sel_inv_list.invoice_id_l(i)
3193 AND payment_num = sel_inv_list.payment_num_l(i)
3194 AND checkrun_id IS NULL --bug 6788730
3195 ;
3196
3197
3198 l_debug_info := 'Insert into ap_selected_invoices_all: encumbrances are off';
3199 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3200 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3201 END IF;
3202
3203 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3204 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3205 END IF;
3206
3207
3208 FORALL i IN sel_inv_list.invoice_id_l.FIRST .. sel_inv_list.invoice_id_l.LAST
3209 INSERT INTO ap_selected_invoices_all
3210 (checkrun_name
3211 ,checkrun_id
3212 ,invoice_id
3213 ,payment_num
3214 ,last_update_date
3215 ,last_updated_by
3216 ,creation_date
3217 ,created_by
3218 ,vendor_id
3219 ,vendor_site_id
3220 ,vendor_num
3221 ,vendor_name
3222 ,vendor_site_code
3223 ,address_line1
3224 ,address_line2
3225 ,address_line3
3226 ,address_line4
3227 ,city
3228 ,state
3229 ,zip
3230 ,province
3231 ,country
3232 ,attention_ar_flag
3233 ,withholding_status_lookup_code
3234 ,invoice_num
3235 ,invoice_date
3236 ,voucher_num
3237 ,ap_ccid
3238 ,due_date
3239 ,discount_date
3240 ,invoice_description
3241 ,payment_priority
3242 ,ok_to_pay_flag
3243 ,always_take_discount_flag
3244 ,amount_modified_flag
3245 ,invoice_amount
3246 ,payment_cross_rate
3247 ,invoice_exchange_rate
3248 ,set_of_books_id
3249 ,customer_num
3250 ,future_pay_due_date
3251 ,exclusive_payment_flag
3252 ,attribute1
3253 ,attribute2
3254 ,attribute3
3255 ,attribute4
3256 ,attribute5
3257 ,attribute6
3258 ,attribute7
3259 ,attribute8
3260 ,attribute9
3261 ,attribute10
3262 ,attribute11
3263 ,attribute12
3264 ,attribute13
3265 ,attribute14
3266 ,attribute15
3267 ,attribute_category
3268 ,org_id
3269 ,payment_currency_code
3270 ,external_bank_account_id
3271 ,legal_entity_id
3272 ,global_attribute1
3273 ,global_attribute2
3274 ,global_attribute3
3275 ,global_attribute4
3276 ,global_attribute5
3277 ,global_attribute6
3278 ,global_attribute7
3279 ,global_attribute8
3280 ,global_attribute9
3281 ,global_attribute10
3282 ,global_attribute11
3283 ,global_attribute12
3284 ,global_attribute13
3285 ,global_attribute14
3286 ,global_attribute15
3287 ,global_attribute16
3288 ,global_attribute17
3289 ,global_attribute18
3290 ,global_attribute19
3291 ,global_attribute20
3292 ,global_attribute_category)
3293 --bug 6788730 Changed this to SELECT
3294 --
3295 -- VALUES
3296 (
3297 SELECT
3298 sel_inv_list.checkrun_name_l(i)
3299 ,sel_inv_list.checkrun_id_l(i)
3300 ,sel_inv_list.invoice_id_l(i)
3301 ,sel_inv_list.payment_num_l(i)
3302 ,sel_inv_list.last_update_date_l(i)
3303 ,sel_inv_list.last_updated_by_l(i)
3304 ,sel_inv_list.creation_date_l(i)
3305 ,sel_inv_list.created_by_l(i)
3306 ,sel_inv_list.vendor_id_l(i)
3307 ,sel_inv_list.vendor_site_id_l(i)
3308 ,sel_inv_list.vendor_num_l(i)
3309 ,sel_inv_list.vendor_name_l(i)
3310 ,sel_inv_list.vendor_site_code_l(i)
3311 ,sel_inv_list.address_line1_l(i)
3312 ,sel_inv_list.address_line2_l(i)
3313 ,sel_inv_list.address_line3_l(i)
3314 ,sel_inv_list.address_line4_l(i)
3315 ,sel_inv_list.city_l(i)
3316 ,sel_inv_list.state_l(i)
3317 ,sel_inv_list.zip_l(i)
3318 ,sel_inv_list.province_l(i)
3319 ,sel_inv_list.country_l(i)
3320 ,sel_inv_list.attention_ar_flag_l(i)
3321 ,sel_inv_list.withholding_status_lookup_l(i)
3322 ,sel_inv_list.invoice_num_l(i)
3323 ,sel_inv_list.invoice_date_l(i)
3324 ,sel_inv_list.voucher_num_l(i)
3325 ,sel_inv_list.ap_ccid_l(i)
3326 ,sel_inv_list.due_date_l(i)
3327 ,sel_inv_list.discount_date_l(i)
3328 ,sel_inv_list.invoice_description_l(i)
3329 ,sel_inv_list.payment_priority_l(i)
3330 ,sel_inv_list.ok_to_pay_flag_l(i)
3331 ,sel_inv_list.always_take_disc_flag_l(i)
3332 ,sel_inv_list.amount_modified_flag_l(i)
3333 ,sel_inv_list.invoice_amount_l(i)
3334 ,sel_inv_list.payment_cross_rate_l(i)
3335 ,sel_inv_list.invoice_exchange_rate_l(i)
3336 ,sel_inv_list.set_of_books_id_l(i)
3337 ,sel_inv_list.customer_num_l(i)
3338 ,sel_inv_list.future_pay_due_date_l(i)
3339 ,sel_inv_list.exclusive_payment_flag_l(i)
3340 ,sel_inv_list.attribute1_l(i)
3341 ,sel_inv_list.attribute2_l(i)
3342 ,sel_inv_list.attribute3_l(i)
3343 ,sel_inv_list.attribute4_l(i)
3344 ,sel_inv_list.attribute5_l(i)
3345 ,sel_inv_list.attribute6_l(i)
3346 ,sel_inv_list.attribute7_l(i)
3347 ,sel_inv_list.attribute8_l(i)
3348 ,sel_inv_list.attribute9_l(i)
3349 ,sel_inv_list.attribute10_l(i)
3350 ,sel_inv_list.attribute11_l(i)
3351 ,sel_inv_list.attribute12_l(i)
3352 ,sel_inv_list.attribute13_l(i)
3353 ,sel_inv_list.attribute14_l(i)
3354 ,sel_inv_list.attribute15_l(i)
3355 ,sel_inv_list.attribute_category_l(i)
3356 ,sel_inv_list.org_id_l(i)
3357 ,sel_inv_list.payment_currency_code_l(i)
3358 ,sel_inv_list.external_bank_account_id_l(i)
3359 ,sel_inv_list.legal_entity_id_l(i)
3360 ,sel_inv_list.global_attribute1_l(i)
3361 ,sel_inv_list.global_attribute2_l(i)
3362 ,sel_inv_list.global_attribute3_l(i)
3363 ,sel_inv_list.global_attribute4_l(i)
3364 ,sel_inv_list.global_attribute5_l(i)
3365 ,sel_inv_list.global_attribute6_l(i)
3366 ,sel_inv_list.global_attribute7_l(i)
3367 ,sel_inv_list.global_attribute8_l(i)
3368 ,sel_inv_list.global_attribute9_l(i)
3369 ,sel_inv_list.global_attribute10_l(i)
3370 ,sel_inv_list.global_attribute11_l(i)
3371 ,sel_inv_list.global_attribute12_l(i)
3372 ,sel_inv_list.global_attribute13_l(i)
3373 ,sel_inv_list.global_attribute14_l(i)
3374 ,sel_inv_list.global_attribute15_l(i)
3375 ,sel_inv_list.global_attribute16_l(i)
3376 ,sel_inv_list.global_attribute17_l(i)
3377 ,sel_inv_list.global_attribute18_l(i)
3378 ,sel_inv_list.global_attribute19_l(i)
3379 ,sel_inv_list.global_attribute20_l(i)
3380 ,sel_inv_list.global_attribute_category_l(i)
3381 FROM Ap_Payment_Schedules_All
3382 WHERE invoice_id = sel_inv_list.invoice_id_l(i)
3383 AND payment_num = sel_inv_list.payment_num_l(i)
3384 AND checkrun_id = sel_inv_list.checkrun_id_l(i)
3385 --bug 6788730
3386 );
3387
3388
3389 EXIT WHEN pay_sched_cur%NOTFOUND;
3390 END LOOP;
3391
3392 COMMIT;
3393 CLOSE pay_sched_cur;
3394
3395
3396 end if;
3397
3398
3399 --COMMIT;
3400
3401
3402 l_debug_info := 'Done Inserting Into Ap_Selected_Invoices_AlL';
3403 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3404 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3405 END IF;
3406
3407 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3408 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3409 END IF;
3410
3411
3412 -- Bug 5646890. Added l_checkrun_id condition for performance reason
3413 UPDATE Ap_Payment_Schedules_All aps
3414 SET checkrun_id = null
3415 WHERE checkrun_id = l_checkrun_id
3416 AND NOT EXISTS (SELECT /*+HASH_AJ */ 'no row in asi'
3417 FROM ap_selected_invoices_all asi
3418 WHERE asi.invoice_id = aps.invoice_id
3419 AND asi.payment_num = aps.payment_num
3420 AND asi.checkrun_id = l_checkrun_id);
3421
3422 --for payment date, reject if itn's not in an open period
3423 --for payment date < system date and allow pre-date payables option is disabled
3424 -- Bug 5646890. Rewrite the following two update as per Performance team
3425
3426 UPDATE AP_SELECTED_INVOICES_ALL ASI
3427 SET OK_TO_PAY_FLAG = 'N',
3428 DONT_PAY_REASON_CODE = 'PERIOD CLOSED'
3429 WHERE CHECKRUN_ID = l_checkrun_id
3430 AND EXISTS
3431 (SELECT NULL
3432 FROM AP_SELECTED_INVOICES_ALL ASI2
3433 WHERE ASI.INVOICE_ID =ASI2.INVOICE_ID
3434 AND ASI.PAYMENT_NUM = ASI2.PAYMENT_NUM
3435 AND ASI2.CHECKRUN_ID = l_checkrun_id
3436 AND NOT EXISTS
3437 (SELECT NULL
3438 FROM GL_PERIOD_STATUSES GLPS
3439 WHERE TRUNC(l_check_date) BETWEEN GLPS.START_DATE AND GLPS.END_DATE
3440 AND GLPS.CLOSING_STATUS IN ('O', 'F')
3441 AND GLPS.APPLICATION_ID = 200
3442 AND GLPS.SET_OF_BOOKS_ID = ASI2.SET_OF_BOOKS_ID));
3443
3444
3445 UPDATE Ap_Selected_Invoices_All ASI
3446 SET ok_to_pay_flag = 'N',
3447 dont_pay_reason_code = 'PRE DATE NOT ALLOWED'
3448 WHERE checkrun_id = l_checkrun_id
3449 AND Exists (SELECT /*+NO_UNNEST */ NULL
3450 FROM Ap_Selected_Invoices_All ASI2,
3451 Ap_System_Parameters_All ASP
3452 WHERE ASI.invoice_id = ASI2.invoice_id
3453 AND ASI.payment_num = ASI2.payment_num
3454 AND ASI2.checkrun_id = l_checkrun_id
3455 AND ASI2.org_id = ASP.org_id
3456 AND ASI2.set_of_books_id = ASP.set_of_books_id
3457 AND NVL(ASP.post_dated_payments_flag, 'N') = 'N'
3458 AND trunc(l_check_date) < trunc(sysdate));
3459
3460
3461 l_debug_info := 'Calling Remove_Invoices';
3462 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3463 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3464 END IF;
3465
3466 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3467 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3468 END IF;
3469
3470 remove_invoices(l_checkrun_id,l_current_calling_sequence);
3471
3472 l_debug_info := 'Calling Insert_UnselectedL';
3473 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3474 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3475 END IF;
3476
3477 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3478 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3479 END IF;
3480
3481 insert_unselected( l_payment_process_request_name,
3482 l_hi_payment_priority,
3483 l_low_payment_priority,
3484 l_invoice_batch_id,
3485 l_inv_vendor_id,
3486 l_inv_exchange_rate_type,
3487 l_payment_method,
3488 l_supplier_type,
3489 l_le_group_option,
3490 l_ou_group_option,
3491 l_currency_group_option,
3492 l_pay_group_option,
3493 l_zero_invoices_allowed,
3494 l_check_date,
3495 l_checkrun_id,
3496 l_current_calling_sequence,
3497 l_party_id);
3498
3499
3500
3501 l_debug_info := 'Update amounts in ap_selected_invoices';
3502 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3503 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3504 END IF;
3505
3506 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3507 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3508 END IF;
3509
3510
3511 --PM has said payment request should not take discounts
3512
3513 UPDATE ap_selected_invoices_all asi
3514 SET (amount_remaining,
3515 discount_amount_remaining,
3516 payment_amount,
3517 proposed_payment_amount,
3518 discount_amount)
3519 =
3520 (SELECT
3521 PS.amount_remaining,
3522 0,
3523 decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
3524 ps.amount_remaining,
3525 PS.amount_remaining
3526 - (DECODE(PS.GROSS_AMOUNT,
3527 0, 0,
3528 DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
3529 'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
3530 GREATEST(DECODE(SIGN(l_check_date
3531 - NVL(PS.DISCOUNT_DATE,
3532 TO_DATE('01/01/1901',
3533 'MM/DD/YYYY'))),
3534 1, 0,
3535 NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
3536 DECODE(SIGN(l_check_date
3537 - NVL(PS.SECOND_DISCOUNT_DATE,
3538 TO_DATE('01/01/1901',
3539 'MM/DD/YYYY'))),
3540 1, 0,
3541 NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
3542 DECODE(SIGN(l_check_date
3543 - NVL(PS.THIRD_DISCOUNT_DATE,
3544 TO_DATE('01/01/1901',
3545 'MM/DD/YYYY'))),
3546 1, 0,
3547 NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
3548 0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
3549 * (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
3550 0, 1,
3551 PS.GROSS_AMOUNT))))),
3552 decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
3553 ps.amount_remaining,
3554 PS.amount_remaining
3555 - (DECODE(PS.GROSS_AMOUNT,
3556 0, 0,
3557 DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
3558 'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
3559 GREATEST(DECODE(SIGN(l_check_date
3560 - NVL(PS.DISCOUNT_DATE,
3561 TO_DATE('01/01/1901',
3562 'MM/DD/YYYY'))),
3563 1, 0,
3564 NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
3565 DECODE(SIGN(l_check_date
3566 - NVL(PS.SECOND_DISCOUNT_DATE,
3567 TO_DATE('01/01/1901',
3568 'MM/DD/YYYY'))),
3569 1, 0,
3570 NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
3571 DECODE(SIGN(l_check_date
3572 - NVL(PS.THIRD_DISCOUNT_DATE,
3573 TO_DATE('01/01/1901',
3574 'MM/DD/YYYY'))),
3575 1, 0,
3576 NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
3577 0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
3578 * (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
3579 0, 1,
3580 PS.GROSS_AMOUNT))))),
3581 decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
3582 0,
3583 DECODE(PS.GROSS_AMOUNT,
3584 0, 0,
3585 DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
3586 'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
3587 GREATEST(DECODE(SIGN(l_check_date
3588 - NVL(PS.DISCOUNT_DATE,
3589 TO_DATE('01/01/1901',
3590 'MM/DD/YYYY'))),
3591 1, 0,
3592 NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
3593 DECODE(SIGN(l_check_date
3594 - NVL(PS.SECOND_DISCOUNT_DATE,
3595 TO_DATE('01/01/1901',
3596 'MM/DD/YYYY'))),
3597 1, 0,
3598 NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
3599 DECODE(SIGN(l_check_date
3600 - NVL(PS.THIRD_DISCOUNT_DATE,
3601 TO_DATE('01/01/1901',
3602 'MM/DD/YYYY'))),
3603 1, 0,
3604 NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
3605 0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
3606 * (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
3607 0, 1,
3608 PS.GROSS_AMOUNT))))
3609 FROM ap_payment_schedules_all PS,
3610 ap_invoices ai --Bug6040657. Changed from ap_invoices_all to ap_invoices
3611 WHERE PS.invoice_id = asi.invoice_id
3612 AND PS.payment_num = asi.payment_num
3613 and ai.invoice_id = ps.invoice_id)
3614 WHERE checkrun_id = l_checkrun_id;
3615
3616
3617
3618 l_debug_info := 'Round amounts in ap_selected_invoices';
3619 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3620 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3621 END IF;
3622
3623 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3624 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3625 END IF;
3626
3627
3628 --round the values we just updated
3629 UPDATE ap_selected_invoices_all ASI
3630 SET payment_amount = ap_utilities_pkg.ap_round_currency
3631 (payment_amount,payment_currency_code),
3632 proposed_payment_amount = ap_utilities_pkg.ap_round_currency
3633 (proposed_payment_amount,payment_currency_code) ,
3634 discount_amount = ap_utilities_pkg.ap_round_currency
3635 (discount_amount,payment_currency_code)
3636 WHERE checkrun_id= l_checkrun_id;
3637
3638
3639 --get rid of $0 invoices if not allowed
3640 update ap_selected_invoices_all
3641 set ok_to_pay_flag = 'N',
3642 dont_pay_reason_code = 'ZERO INVOICE'
3643 WHERE checkrun_id = l_checkrun_id
3644 AND l_zero_invoices_allowed = 'N'
3645 AND amount_remaining = 0;
3646
3647 l_debug_info := 'Calling Calculate_Interest';
3648 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3649 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3650 END IF;
3651
3652 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3653 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3654 END IF;
3655
3656
3657 calculate_interest (l_checkrun_id,
3658 l_payment_process_request_name,
3659 l_check_date,
3660 l_current_calling_sequence);
3661
3662
3663
3664 l_debug_info := 'Calling ap_withholding_pkg';
3665 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3666 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3667 END IF;
3668
3669 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3670 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3671 END IF;
3672
3673
3674 AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(
3675 l_payment_process_request_name,
3676 FND_GLOBAL.USER_ID,
3677 FND_GLOBAL.LOGIN_ID,
3678 FND_GLOBAL.PROG_APPL_ID,
3679 FND_GLOBAL.CONC_PROGRAM_ID,
3680 FND_GLOBAL.CONC_REQUEST_ID,
3681 l_checkrun_id);
3682
3683 --Bug6459578
3684 l_debug_info := 'Calling ap_custom_withholding_pkg';
3685 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3686 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3687 END IF;
3688
3689 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3690 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3691 END IF;
3692
3693 Awt_special_rounding(l_checkrun_id,l_current_calling_sequence);
3694
3695 --Bug6459578
3696
3697
3698 remove_invoices (l_checkrun_id, l_current_calling_sequence);
3699
3700 --the rejection levels can be changed in our awt code, hence they need to be
3701 --retrieved after the call above
3702 -- Bug 7492768 Start
3703 -- We need to retrieve inv_awt_exists_flag that determines whether
3704 -- the PPR contains invoices that have AWT.
3705 -- If the flag is set then the invoice contains AWT and we must pass the
3706 -- rejection level code as REQUEST to IBY apis. Otherwise we can pass the same
3707 -- value defined by the user.
3708 select document_rejection_level_code,
3709 payment_rejection_level_code,
3710 inv_awt_exists_flag
3711 into l_doc_rejection_level_code,
3712 l_pay_rejection_level_code,
3713 l_inv_awt_exists_flag
3714 from ap_inv_selection_criteria_all
3715 where checkrun_id = l_checkrun_id;
3716
3717 IF NVL(l_inv_awt_exists_flag, 'N') = 'Y' THEN
3718 l_doc_rejection_level_code := 'REQUEST';
3719 l_pay_rejection_level_code := 'REQUEST';
3720 END IF;
3721 -- Bug 7492768 End
3722
3723
3724 l_debug_info := 'updating exchange rate info';
3725 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3726 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3727 END IF;
3728
3729 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3730 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3731 END IF;
3732
3733
3734 if l_batch_exchange_rate_type = 'User' then
3735
3736 update ap_selected_invoices_all asi
3737 set (payment_exchange_rate_type, payment_exchange_rate) =
3738 (select 'User', exchange_rate
3739 from ap_user_exchange_rates auer,
3740 ap_system_parameters_all asp
3741 where asp.org_id = asi.org_id
3742 and asi.payment_currency_code = auer.payment_currency_code
3743 and asp.base_currency_code = auer.ledger_currency_code
3744 and asp.base_currency_code <> asi.payment_currency_code
3745 and auer.checkrun_id = l_checkrun_id)
3746 where checkrun_id = l_checkrun_id
3747 and (invoice_id, payment_num) in
3748 (select invoice_id, payment_num
3749 from ap_selected_invoices_all asi2,
3750 ap_system_parameters_all asp2
3751 where asp2.org_id = asi2.org_id
3752 and asp2.base_currency_code <> asi2.payment_currency_code
3753 and asi2.checkrun_id = l_checkrun_id);
3754
3755 else
3756 --update for all other exchange rate types
3757
3758 update ap_selected_invoices asi
3759 set (payment_exchange_rate_type, payment_exchange_rate) =
3760 (select l_batch_exchange_rate_type,
3761 ap_utilities_pkg.get_exchange_rate(
3762 asi.payment_currency_code,
3763 asp.base_currency_code,
3764 l_batch_exchange_rate_type,
3765 l_check_date,
3766 'AUTOSELECT')
3767 from ap_system_parameters_all asp
3768 where asp.org_id = asi.org_id
3769 and asp.base_currency_code <> asi.payment_currency_code)
3770 where checkrun_id = l_checkrun_id
3771 and (invoice_id, payment_num) in
3772 (select invoice_id, payment_num
3773 from ap_selected_invoices_all asi2,
3774 ap_system_parameters_all asp2
3775 where asp2.org_id = asi2.org_id
3776 and asp2.base_currency_code <> asi2.payment_currency_code
3777 and asi2.checkrun_id = l_checkrun_id); --Bug 5123855
3778
3779
3780
3781 end if;
3782
3783 select count(*)
3784 into l_missing_rates_count
3785 from ap_selected_invoices_all asi,
3786 ap_system_parameters_all asp
3787 where asi.org_id = asp.org_id
3788 and asi.checkrun_id = l_checkrun_id
3789 and asi.payment_currency_code <> asp.base_currency_code
3790 and asi.payment_exchange_rate is null
3791 and ((l_batch_exchange_rate_type <> 'User'
3792 and asp.make_rate_mandatory_flag = 'Y') OR
3793 l_batch_exchange_rate_type = 'User')
3794 and rownum = 1;
3795
3796
3797 --need to pause the request if payables options requires exchange rates
3798 --and none were found or we are using 'user'exchange rate type
3799
3800 if l_missing_rates_count > 0 then
3801 update ap_inv_selection_criteria_all
3802 set status = 'MISSING RATES'
3803 where checkrun_id = l_checkrun_id;
3804
3805 if l_batch_exchange_rate_type = 'User' then
3806 insert into ap_user_exchange_rates auer
3807 (checkrun_id,
3808 payment_currency_code,
3809 ledger_currency_code,
3810 creation_date,
3811 created_by, --Bug 5123855
3812 last_update_date,
3813 last_updated_by,
3814 last_update_login)
3815 (select l_checkrun_id,
3816 asi.payment_currency_code,
3817 asp.base_currency_code,
3818 SYSDATE,
3819 FND_GLOBAL.user_id,
3820 SYSDATE,
3821 FND_GLOBAL.user_id,
3822 FND_GLOBAL.login_id
3823 from ap_selected_invoices_all asi,
3824 ap_system_parameters_all asp
3825 where asi.payment_exchange_rate is null
3826 and asp.org_id = asi.org_id
3827 and asp.base_currency_code <> asi.payment_currency_code
3828 and asi.checkrun_id = l_checkrun_id
3829 group by asi.payment_currency_code, /* bug 5447896 */
3830 asp.base_currency_code);
3831 end if;
3832 end if;
3833
3834
3835 l_debug_info := 'Grouping selected invoices';
3836 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3837 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3838 END IF;
3839
3840 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3841 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3842 END IF;
3843
3844
3845 group_interest_credits(l_checkrun_id, l_current_calling_sequence);
3846
3847
3848 remove_invoices (l_checkrun_id, l_current_calling_sequence );
3849
3850
3851 --4745133, moved the code below to this position
3852 --as we could have removed invoices in the ppr
3853 --after they have been inserted into asi
3854
3855 select count(*)
3856 into l_count_inv_selected
3857 from ap_selected_invoices_all
3858 where checkrun_id = l_checkrun_id
3859 and rownum = 1;
3860
3861
3862 if l_count_inv_selected = 0 then
3863
3864 update ap_inv_selection_criteria_all
3865 set status = 'CANCELLED NO PAYMENTS'
3866 where checkrun_id = l_checkrun_id;
3867
3868 commit;
3869
3870 fnd_file.put_line(FND_FILE.LOG, 'No scheduled payments matched the invoice selection criteria');
3871
3872 l_debug_info := 'No scheduled payments matched the invoice selection criteria';
3873 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3874 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3875 END IF;
3876
3877 -- Bug 5111495
3878 -- app_exception.raise_exception;
3879
3880 return;
3881 end if;
3882
3883
3884
3885
3886 --5007587
3887 declare
3888
3889 l_init_msg_list varchar2(2000);
3890 l_return_status varchar2(1);
3891 l_msg_count number;
3892 l_msg_data varchar2(2000);
3893 l_msg_index_out number;
3894
3895 begin
3896
3897 FV_FEDERAL_PAYMENT_FIELDS_PKG.SUBMIT_CASH_POS_REPORT(
3898 p_init_msg_list => l_init_msg_list,
3899 p_org_id => null,
3900 p_checkrun_id => l_checkrun_id,
3901 x_request_id => l_req_id,
3902 x_return_status => l_return_status,
3903 x_msg_count => l_msg_count,
3904 x_msg_data => l_msg_data);
3905
3906 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3907 IF l_msg_count > 0 THEN
3908 FOR i in 1..l_msg_count LOOP
3909 FND_MSG_PUB.Get( p_msg_index => i,
3910 p_encoded => 'F',
3911 p_data => l_msg_data,
3912 p_msg_index_out => l_msg_index_out);
3913 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
3914 End LOOP;
3915 end if;
3916 end if;
3917 end;
3918 --end 5007857
3919
3920
3921
3922
3923
3924 update ap_inv_selection_criteria_all
3925 set status = decode(l_payables_review_settings,'Y','REVIEW','SELECTED')
3926 where status = 'SELECTING'
3927 and checkrun_id = l_checkrun_id;
3928
3929 -- Bug 4681857
3930 SELECT lower(iso_language),iso_territory
3931 INTO l_iso_language,l_iso_territory
3932 FROM FND_LANGUAGES
3933 WHERE language_code = USERENV('LANG');
3934
3935 --Bug 6969710
3936 SELECT nvl(template_code, 'APINVSEL' )
3937 INTO l_template_code
3938 FROM Fnd_Concurrent_Programs
3939 WHERE concurrent_program_name = 'APINVSEL'; --Bug 6969710
3940
3941
3942 l_xml_output:= fnd_request.add_layout(
3943 template_appl_name => 'SQLAP',
3944 template_code => l_template_code , --Bug 6969710
3945 template_language => l_iso_language,
3946 template_territory => l_iso_territory,
3947 output_format => 'PDF'
3948 );
3949
3950 --below code added for bug#7435751 as we need to set the current nls character setting
3951 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
3952 l_return_status:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
3953
3954 --submit the selected payment schedules report
3955 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
3956 'SQLAP',
3957 'APINVSEL',
3958 '',
3959 '',
3960 FALSE,
3961 to_char(l_checkrun_id),
3962 chr(0));
3963
3964 select status
3965 into l_batch_status
3966 from ap_inv_selection_criteria_all
3967 where checkrun_id = l_checkrun_id;
3968
3969 if l_batch_status = 'SELECTED' and l_payables_review_settings <> 'Y' then
3970
3971 l_debug_info := 'Submitting Oracle Payments Build';
3972 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3973 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
3974 END IF;
3975
3976 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
3977 'IBY',
3978 'IBYBUILD',
3979 '',
3980 '',
3981 FALSE,
3982 '200',
3983 l_payment_process_request_name,
3984 to_char(l_bank_account_id),
3985 to_char(l_payment_profile_id),
3986 l_zero_invoices_allowed, -- Bug 6523501,
3987 to_char(l_max_payment_amount),
3988 to_char(l_min_check_amount),
3989 l_doc_rejection_level_code,
3990 l_pay_rejection_level_code,
3991 l_pay_review_settings_flag,
3992 l_create_instrs_flag,
3993 l_payment_document_id,
3994 /* bug 7519277*/
3995 l_ATTRIBUTE_CATEGORY,
3996 l_ATTRIBUTE1,
3997 l_ATTRIBUTE2,
3998 l_ATTRIBUTE3,
3999 l_ATTRIBUTE4,
4000 l_ATTRIBUTE5,
4001 l_ATTRIBUTE6,
4002 l_ATTRIBUTE7,
4003 l_ATTRIBUTE8,
4004 l_ATTRIBUTE9,
4005 l_ATTRIBUTE10,
4006 l_ATTRIBUTE11,
4007 l_ATTRIBUTE12,
4008 l_ATTRIBUTE13,
4009 l_ATTRIBUTE14,
4010 l_ATTRIBUTE15,
4011 /*bug 7519277*/
4012 chr(0));
4013
4014 l_debug_info := 'request_id ='||to_char(l_req_id);
4015 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4016 fnd_file.put_line(FND_FILE.LOG,l_debug_info);
4017 END IF;
4018
4019 end if;
4020
4021 COMMIT;
4022
4023 EXCEPTION
4024
4025 WHEN OTHERS then
4026
4027 IF (SQLCODE <> -20001) THEN
4028 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4029 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4030 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
4031 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
4032 FND_MESSAGE.SET_TOKEN('PARAMETERS','p_checkrun_id '||p_checkrun_id
4033 ||',P_template_id '||P_template_id
4034 ||',p_payment_date '||p_payment_date
4035 ||',p_pay_thru_date '||p_pay_thru_date
4036 ||',p_pay_from_date '||p_pay_from_date);
4037
4038
4039
4040 END IF;
4041 APP_EXCEPTION.RAISE_EXCEPTION;
4042
4043 END SELECT_INVOICES;
4044
4045
4046 PROCEDURE recalculate (errbuf OUT NOCOPY VARCHAR2,
4047 retcode OUT NOCOPY NUMBER,
4048 p_checkrun_id in varchar2,
4049 p_submit_to_iby in varchar2 default 'N') is
4050
4051 l_debug_info varchar2(2000);
4052 l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;-- bug# 6643035
4053 l_check_date date;
4054 l_bank_account_id number; --4710933
4055 l_payment_profile_id number;
4056 l_req_id number;
4057 l_zero_amounts_allowed varchar2(1);
4058 l_zero_invoices_allowed varchar2(1); -- Bug 6523501
4059 l_max_payment_amount number;
4060 l_min_check_amount number;
4061 l_doc_rejection_level_code varchar2(30);
4062 l_pay_rejection_level_code varchar2(30);
4063 l_pay_review_settings_flag varchar2(1);
4064 l_create_instrs_flag varchar2(1);
4065 l_missing_rates_count number;
4066 l_batch_exchange_rate_type varchar2(30);
4067 l_batch_status varchar2(30);
4068 l_payment_document_id number(15); --4939405
4069 l_xml_output BOOLEAN;
4070 l_iso_language FND_LANGUAGES.iso_language%TYPE;
4071 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
4072 l_template_code Fnd_Concurrent_Programs.template_code%TYPE; --Bug 6969710
4073 /*bug 7519277*/
4074 l_ATTRIBUTE_CATEGORY VARCHAR2(150);
4075 l_ATTRIBUTE1 VARCHAR2(150);
4076 l_ATTRIBUTE2 VARCHAR2(150);
4077 l_ATTRIBUTE3 VARCHAR2(150);
4078 l_ATTRIBUTE4 VARCHAR2(150);
4079 l_ATTRIBUTE5 VARCHAR2(150);
4080 l_ATTRIBUTE6 VARCHAR2(150);
4081 l_ATTRIBUTE7 VARCHAR2(150);
4082 l_ATTRIBUTE8 VARCHAR2(150);
4083 l_ATTRIBUTE9 VARCHAR2(150);
4084 l_ATTRIBUTE10 VARCHAR2(150);
4085 l_ATTRIBUTE11 VARCHAR2(150);
4086 l_ATTRIBUTE12 VARCHAR2(150);
4087 l_ATTRIBUTE13 VARCHAR2(150);
4088 l_ATTRIBUTE14 VARCHAR2(150);
4089 l_ATTRIBUTE15 VARCHAR2(150);
4090 /*bug 7519277*/
4091 l_icx_numeric_characters VARCHAR2(30); --for bug#7435751
4092 l_return_status boolean; --for bug#7435751
4093
4094
4095
4096 CURSOR c_all_sel_invs IS
4097 SELECT invoice_id
4098 , vendor_id
4099 , payment_num
4100 FROM ap_SELECTed_invoices_all ASI,
4101 ap_system_parameters_all asp
4102 WHERE checkrun_name = l_checkrun_name
4103 AND original_invoice_id IS NULL
4104 AND asp.org_id = asi.org_id
4105 and checkrun_id = p_checkrun_id
4106 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
4107 decode(ASP.create_awt_dists_type,'BOTH','Y','PAYMENT',
4108 'Y', decode(ASP.create_awt_invoices_type,'BOTH','Y','PAYMENT',
4109 'Y', 'N'),
4110 'N'),
4111 'N') = 'Y'; --Bug6660355
4112 rec_all_sel_invs c_all_sel_invs%ROWTYPE;
4113
4114 l_current_calling_sequence varchar2(2000);
4115
4116
4117
4118 BEGIN
4119
4120
4121 --in 11.5 we can do the undo and re-do of awt all at the same time
4122 --but if I am to handle interest here instead of the front end then
4123 --this cannot be done, we have to do interest calculations after awt
4124 --is undone
4125
4126 --4676790, since this code runs as a concurrent request the calling
4127 --code should update the status.
4128
4129
4130 l_current_calling_sequence := 'ap_autoselect_pkg.recalculate';
4131 l_debug_info:= 'delete interest invoices';
4132
4133
4134 delete from ap_selected_invoices_all
4135 where checkrun_id = p_checkrun_id
4136 and original_invoice_id is not null;
4137
4138 SELECT
4139 checkrun_name,
4140 check_date,
4141 nvl(zero_amounts_allowed,'N'),
4142 nvl(zero_invoices_allowed,'N'), -- Bug 6523501
4143 bank_account_id, --4710933
4144 payment_profile_id,
4145 max_payment_amount,
4146 min_check_amount,
4147 payments_review_settings,
4148 decode(payment_profile_id,null,'N',nvl(create_instrs_flag,'N')),
4149 document_rejection_level_code,
4150 payment_rejection_level_code,
4151 exchange_rate_type,
4152 payment_document_id,
4153 /*bug 7519277*/
4154 ATTRIBUTE_CATEGORY,
4155 ATTRIBUTE1,
4156 ATTRIBUTE2,
4157 ATTRIBUTE3,
4158 ATTRIBUTE4,
4159 ATTRIBUTE5,
4160 ATTRIBUTE6,
4161 ATTRIBUTE7,
4162 ATTRIBUTE8,
4163 ATTRIBUTE9,
4164 ATTRIBUTE10,
4165 ATTRIBUTE11,
4166 ATTRIBUTE12,
4167 ATTRIBUTE13,
4168 ATTRIBUTE14,
4169 ATTRIBUTE15
4170 /*bug 7519277*/
4171 INTO l_checkrun_name,
4172 l_check_date,
4173 l_zero_amounts_allowed,
4174 l_zero_invoices_allowed, -- Bug 6523501
4175 l_bank_account_id,
4176 l_payment_profile_id,
4177 l_max_payment_amount,
4178 l_min_check_amount,
4179 l_pay_review_settings_flag,
4180 l_create_instrs_flag,
4181 l_doc_rejection_level_code,
4182 l_pay_rejection_level_code,
4183 l_batch_exchange_rate_type,
4184 l_payment_document_id,
4185 /* bug 7519277*/
4186 l_ATTRIBUTE_CATEGORY,
4187 l_ATTRIBUTE1,
4188 l_ATTRIBUTE2,
4189 l_ATTRIBUTE3,
4190 l_ATTRIBUTE4,
4191 l_ATTRIBUTE5,
4192 l_ATTRIBUTE6,
4193 l_ATTRIBUTE7,
4194 l_ATTRIBUTE8,
4195 l_ATTRIBUTE9,
4196 l_ATTRIBUTE10,
4197 l_ATTRIBUTE11,
4198 l_ATTRIBUTE12,
4199 l_ATTRIBUTE13,
4200 l_ATTRIBUTE14,
4201 l_ATTRIBUTE15
4202 /*bug 7519277*/
4203 FROM ap_inv_selection_criteria_all
4204 WHERE checkrun_id = p_checkrun_id;
4205
4206
4207
4208
4209 --undo awt, this is essentially the same code as in ap_withholding_pkg
4210
4211 OPEN c_all_sel_invs;
4212
4213 LOOP
4214 l_debug_info := 'Fetch CURSOR for all SELECTed invoices';
4215 FETCH c_all_sel_invs INTO rec_all_sel_invs;
4216 EXIT WHEN c_all_sel_invs%NOTFOUND;
4217
4218 DECLARE
4219 undo_output VARCHAR2(2000);
4220 BEGIN
4221 AP_WITHHOLDING_PKG.Ap_Undo_Temp_Withholding
4222 (P_Invoice_Id => rec_all_sel_invs.invoice_id
4223 ,P_VENDor_Id => rec_all_sel_invs.vendor_id
4224 ,P_Payment_Num => rec_all_sel_invs.payment_num
4225 ,P_Checkrun_Name => l_Checkrun_Name
4226 ,P_Undo_Awt_Date => SYSDATE
4227 ,P_Calling_Module => 'AUTOSELECT'
4228 ,P_Last_Updated_By => FND_GLOBAL.USER_ID
4229 ,P_Last_Update_Login => FND_GLOBAL.LOGIN_ID
4230 ,P_Program_Application_Id => FND_GLOBAL.PROG_APPL_ID
4231 ,P_Program_Id => FND_GLOBAL.CONC_PROGRAM_ID
4232 ,P_Request_Id => FND_GLOBAL.CONC_REQUEST_ID
4233 ,P_Awt_Success => undo_output
4234 ,P_checkrun_id => p_checkrun_id );
4235
4236 END;
4237 END LOOP;
4238
4239 l_debug_info := 'CLOSE CURSOR for all SELECTed invoices';
4240 CLOSE c_all_sel_invs;
4241
4242
4243
4244 update ap_selected_invoices_all
4245 set payment_grouping_number = null
4246 where checkrun_id = p_checkrun_id;
4247
4248 -- Bug 7492768 We need to reset the inv_awt_exists_flag which indicates if the
4249 -- check run contains invoice that has awt.
4250 update ap_inv_selection_criteria_all
4251 set inv_awt_exists_flag = 'N'
4252 where checkrun_id = p_checkrun_id;
4253
4254
4255
4256
4257
4258 --redo interest
4259
4260 calculate_interest (p_checkrun_id,
4261 l_checkrun_name,
4262 l_check_date,
4263 l_current_calling_sequence);
4264
4265
4266
4267 --redo awt
4268 AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(
4269 l_checkrun_name,
4270 FND_GLOBAL.USER_ID,
4271 FND_GLOBAL.LOGIN_ID,
4272 FND_GLOBAL.PROG_APPL_ID,
4273 FND_GLOBAL.CONC_PROGRAM_ID,
4274 FND_GLOBAL.CONC_REQUEST_ID,
4275 p_checkrun_id);
4276
4277 -- Bug 7492768 start
4278 select decode(nvl(inv_awt_exists_flag, 'N'), 'Y', 'REQUEST', document_rejection_level_code) document_rejection_level_code,
4279 decode(nvl(inv_awt_exists_flag, 'N'), 'Y', 'REQUEST', payment_rejection_level_code) payment_rejection_level_code
4280 into l_doc_rejection_level_code,
4281 l_pay_rejection_level_code
4282 FROM ap_inv_selection_criteria_all
4283 WHERE checkrun_id = p_checkrun_id;
4284 -- Bug 7492768 End
4285
4286 --regroup
4287
4288 group_interest_credits(p_checkrun_id, l_current_calling_sequence);
4289 remove_invoices (p_checkrun_id, l_current_calling_sequence );
4290
4291
4292
4293
4294
4295
4296 if l_batch_exchange_rate_type = 'User' then
4297
4298 update ap_selected_invoices_all asi
4299 set (payment_exchange_rate_type, payment_exchange_rate) =
4300 (select 'User', exchange_rate
4301 from ap_user_exchange_rates auer,
4302 ap_system_parameters_all asp
4303 where asp.org_id = asi.org_id
4304 and asi.payment_currency_code = auer.payment_currency_code
4305 and asp.base_currency_code = auer.ledger_currency_code
4306 and asp.base_currency_code <> asi.payment_currency_code
4307 and auer.checkrun_id = p_checkrun_id) --Bug 5123855
4308 where checkrun_id = p_checkrun_id
4309 and (invoice_id, payment_num) in
4310 (select invoice_id, payment_num
4311 from ap_selected_invoices_all asi2,
4312 ap_system_parameters_all asp2
4313 where asp2.org_id = asi2.org_id
4314 and asp2.base_currency_code <> asi2.payment_currency_code
4315 and asi2.checkrun_id = p_checkrun_id);
4316
4317 else
4318 --update for all other exchange rate types
4319
4320 update ap_selected_invoices asi
4321 set (payment_exchange_rate_type, payment_exchange_rate) =
4322 (select l_batch_exchange_rate_type,
4323 ap_utilities_pkg.get_exchange_rate(
4324 asi.payment_currency_code,
4325 asp.base_currency_code,
4326 l_batch_exchange_rate_type,
4327 l_check_date,
4328 'AUTOSELECT')
4329 from ap_system_parameters_all asp
4330 where asp.org_id = asi.org_id
4331 and asp.base_currency_code <> asi.payment_currency_code)
4332 where checkrun_id = p_checkrun_id
4333 and (invoice_id, payment_num) in
4334 (select invoice_id, payment_num
4335 from ap_selected_invoices_all asi2,
4336 ap_system_parameters_all asp2
4337 where asp2.org_id = asi2.org_id
4338 and asp2.base_currency_code <> asi2.payment_currency_code
4339 and asi2.checkrun_id = p_checkrun_id);
4340
4341
4342
4343 end if;
4344
4345 select count(*)
4346 into l_missing_rates_count
4347 from ap_selected_invoices_all asi,
4348 ap_system_parameters_all asp
4349 where asi.org_id = asp.org_id
4350 and asi.checkrun_id = p_checkrun_id
4351 and asi.payment_currency_code <> asp.base_currency_code
4352 and asi.payment_exchange_rate is null
4353 and ((l_batch_exchange_rate_type <> 'User'
4354 and asp.make_rate_mandatory_flag = 'Y') OR
4355 l_batch_exchange_rate_type = 'User')
4356 and rownum = 1;
4357
4358
4359
4360 if l_missing_rates_count > 0 then
4361
4362 update ap_inv_selection_criteria_all
4363 set status = 'MISSING RATES'
4364 where checkrun_id = p_checkrun_id;
4365
4366 if l_batch_exchange_rate_type = 'User' then
4367 insert into ap_user_exchange_rates auer
4368 (checkrun_id,
4369 payment_currency_code,
4370 ledger_currency_code,
4371 creation_date, --Bug 5123855
4372 created_by,
4373 last_update_date,
4374 last_updated_by,
4375 last_update_login)
4376 (select p_checkrun_id,
4377 asi.payment_currency_code,
4378 asp.base_currency_code,
4379 SYSDATE,
4380 FND_GLOBAL.user_id,
4381 SYSDATE,
4382 FND_GLOBAL.user_id,
4383 FND_GLOBAL.login_id
4384 from ap_selected_invoices_all asi,
4385 ap_system_parameters_all asp
4386 where asi.payment_exchange_rate is null
4387 and asp.org_id = asi.org_id
4388 and asp.base_currency_code <> asi.payment_currency_code
4389 and asi.checkrun_id = p_checkrun_id
4390 and not exists (select 'row already in auer'
4391 from ap_user_exchange_rates auer2
4392 where auer2.checkrun_id = asi.checkrun_id
4393 and auer2.payment_currency_code = asi.payment_currency_code
4394 and auer2.ledger_currency_code = asp.base_currency_code));
4395
4396 end if;
4397 end if;
4398
4399
4400
4401
4402
4403 --5007587
4404 declare
4405
4406 l_init_msg_list varchar2(2000);
4407 l_return_status varchar2(1);
4408 l_msg_count number;
4409 l_msg_data varchar2(2000);
4410 l_msg_index_out number;
4411
4412 begin
4413
4414 FV_FEDERAL_PAYMENT_FIELDS_PKG.SUBMIT_CASH_POS_REPORT(
4415 p_init_msg_list => l_init_msg_list,
4416 p_org_id => null,
4417 p_checkrun_id => p_checkrun_id,
4418 x_request_id => l_req_id,
4419 x_return_status => l_return_status,
4420 x_msg_count => l_msg_count,
4421 x_msg_data => l_msg_data);
4422
4423 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4424 IF l_msg_count > 0 THEN
4425 FOR i in 1..l_msg_count LOOP
4426 FND_MSG_PUB.Get( p_msg_index => i,
4427 p_encoded => 'F',
4428 p_data => l_msg_data,
4429 p_msg_index_out => l_msg_index_out);
4430 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
4431 End LOOP;
4432 end if;
4433 end if;
4434 end;
4435 --end 5007857
4436
4437
4438
4439
4440
4441
4442 select status
4443 into l_batch_status
4444 from ap_inv_selection_criteria_all
4445 where checkrun_id = p_checkrun_id;
4446
4447
4448
4449
4450
4451 if p_submit_to_iby = 'Y' and l_batch_status = 'CALCULATING' then
4452
4453 update ap_inv_selection_criteria_all
4454 set status = 'SELECTED'
4455 where checkrun_id = p_checkrun_id;
4456
4457 SELECT lower(iso_language),iso_territory
4458 INTO l_iso_language,l_iso_territory
4459 FROM FND_LANGUAGES
4460 WHERE language_code = USERENV('LANG');
4461
4462 -- Bug 4681857
4463
4464 --Bug 6969710
4465 SELECT nvl(template_code, 'APINVSEL' )
4466 INTO l_template_code
4467 FROM Fnd_Concurrent_Programs
4468 WHERE concurrent_program_name = 'APINVSEL'; --Bug 6969710
4469
4470 l_xml_output:= fnd_request.add_layout(
4471 template_appl_name => 'SQLAP',
4472 template_code => l_template_code , --Bug 6969710
4473 template_language => l_iso_language,
4474 template_territory => l_iso_territory,
4475 output_format => 'PDF'
4476 );
4477
4478 --below code added for bug#7435751 as we need to set the current nls character setting
4479 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
4480 l_return_status:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
4481
4482 --submit the selected payment schedules report
4483 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
4484 'SQLAP',
4485 'APINVSEL',
4486 '',
4487 '',
4488 FALSE,
4489 to_char(p_checkrun_id),
4490 chr(0));
4491
4492 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
4493 'IBY',
4494 'IBYBUILD',
4495 '',
4496 '',
4497 FALSE,
4498 '200',
4499 l_checkrun_name,
4500 to_char(l_bank_account_id),
4501 to_char(l_payment_profile_id),
4502 l_zero_invoices_allowed, -- Bug 6523501,
4503 to_char(l_max_payment_amount),
4504 to_char(l_min_check_amount),
4505 l_doc_rejection_level_code,
4506 l_pay_rejection_level_code,
4507 l_pay_review_settings_flag,
4508 l_create_instrs_flag,
4509 l_payment_document_id,
4510 /*bug 7519277*/
4511 l_ATTRIBUTE_CATEGORY,
4512 l_ATTRIBUTE1,
4513 l_ATTRIBUTE2,
4514 l_ATTRIBUTE3,
4515 l_ATTRIBUTE4,
4516 l_ATTRIBUTE5,
4517 l_ATTRIBUTE6,
4518 l_ATTRIBUTE7,
4519 l_ATTRIBUTE8,
4520 l_ATTRIBUTE9,
4521 l_ATTRIBUTE10,
4522 l_ATTRIBUTE11,
4523 l_ATTRIBUTE12,
4524 l_ATTRIBUTE13,
4525 l_ATTRIBUTE14,
4526 l_ATTRIBUTE15,
4527 /*bug 7519277*/
4528 chr(0));
4529 --4676790
4530 elsif p_submit_to_iby = 'N' and l_batch_status = 'CALCULATING' then
4531
4532 update ap_inv_selection_criteria_all
4533 set status = 'REVIEW'
4534 where checkrun_id = p_checkrun_id;
4535
4536 end if;
4537
4538 commit;
4539
4540
4541 exception
4542 WHEN OTHERS then
4543
4544 IF (SQLCODE <> -20001) THEN
4545 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4546 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4547 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
4548 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
4549 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_id: '||
4550 to_char(p_checkrun_id));
4551
4552 END IF;
4553 APP_EXCEPTION.RAISE_EXCEPTION;
4554
4555
4556
4557 END recalculate;
4558
4559
4560
4561 PROCEDURE cancel_batch (errbuf OUT NOCOPY VARCHAR2,
4562 retcode OUT NOCOPY NUMBER,
4563 p_checkrun_id in varchar2) is
4564
4565 l_debug_info varchar2(2000);
4566 l_current_calling_sequence varchar2(2000);
4567 l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;-- bug# 6643035
4568 l_psr_id number;
4569 l_return_status varchar2(1);
4570
4571 begin
4572
4573 l_current_calling_sequence := 'ap_autoselect_pkg.cancel_batch';
4574
4575 select checkrun_name
4576 into l_checkrun_name
4577 from ap_inv_selection_criteria_all
4578 where checkrun_id = p_checkrun_id;
4579
4580
4581 begin
4582
4583 select PAYMENT_SERVICE_REQUEST_ID
4584 into l_psr_id
4585 from iby_pay_service_requests
4586 where calling_app_id = 200
4587 and CALL_APP_PAY_SERVICE_REQ_CODE = l_checkrun_name;
4588 exception
4589 when no_data_found then null;
4590 end;
4591
4592 if l_psr_id is not null then
4593
4594 IBY_DISBURSE_UI_API_PUB_PKG.terminate_pmt_request (
4595 l_psr_id,
4596 'TERMINATED',
4597 l_return_status);
4598
4599 else
4600
4601 update ap_inv_selection_criteria_all
4602 set status = 'CANCELING'
4603 where checkrun_id = p_checkrun_id;
4604
4605 commit;
4606
4607
4608 l_debug_info := 'delete unselected invoices';
4609
4610 delete from ap_unselected_invoices_all
4611 where checkrun_id = p_checkrun_id;
4612
4613 l_debug_info := 'undo awt';
4614
4615 AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL(l_checkrun_name,
4616 FND_GLOBAL.USER_ID,
4617 FND_GLOBAL.LOGIN_ID, -- Bug 5478602
4618 FND_GLOBAL.PROG_APPL_ID,
4619 FND_GLOBAL.CONC_PROGRAM_ID,
4620 FND_GLOBAL.CONC_REQUEST_ID,
4621 p_checkrun_id,
4622 null,
4623 null);
4624
4625
4626 l_debug_info := 'delete selected invoices';
4627
4628 delete from ap_selected_invoices_all
4629 where checkrun_id = p_checkrun_id;
4630
4631
4632 l_debug_info := 'update payment schedules';
4633
4634 update ap_payment_schedules_all
4635 set checkrun_id = null
4636 where checkrun_id = p_checkrun_id;
4637
4638 update ap_inv_selection_criteria_all
4639 set status = 'CANCELED' --seeded with one L
4640 where checkrun_id = p_checkrun_id;
4641
4642 end if;
4643
4644
4645 exception
4646 WHEN OTHERS then
4647
4648 IF (SQLCODE <> -20001) THEN
4649 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4650 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4651 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
4652 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
4653 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_checkrun_id: '||
4654 to_char(p_checkrun_id));
4655
4656 END IF;
4657 APP_EXCEPTION.RAISE_EXCEPTION;
4658 end cancel_batch;
4659
4660
4661 PROCEDURE selection_criteria_report(
4662 errbuf OUT NOCOPY VARCHAR2,
4663 retcode OUT NOCOPY NUMBER,
4664 p_checkrun_id in varchar2)is
4665
4666 l_qryCtx DBMS_XMLGEN.ctxHandle;
4667 l_result_clob CLOB;
4668 l_current_calling_sequence varchar2(2000);
4669 l_debug_info varchar2(200);
4670
4671 begin
4672
4673 l_current_calling_sequence := 'ap_autoselect_pkg.selection_criteria_report';
4674 l_debug_info:= 'select from ap_inv_selection_criteria_all';
4675
4676 fnd_file.put_line(fnd_file.output, '<SELECTION_CRITERIA_RPT>');
4677
4678 l_qryCtx := DBMS_XMLGEN.newContext(
4679 'select aisc.checkrun_name, aisc.pay_from_date, aisc.pay_thru_date,
4680 aisc.hi_payment_priority, aisc.low_payment_priority,
4681 aisc.pay_only_when_due_flag, aisc.zero_amounts_allowed,
4682 aisc.zero_invoices_allowed, ab.batch_name,
4683 vndr.meaning supplier_type, hz.party_name,
4684 iby.payment_method_name, rate.displayed_field document_exchange_rate_type,
4685 apt.template_name
4686 from ap_inv_selection_criteria_all aisc,
4687 ap_batches_all ab,
4688 iby_payment_methods_vl iby,
4689 fnd_lookups vndr,
4690 hz_parties hz,
4691 ap_lookup_codes rate,
4692 ap_payment_templates apt
4693 where checkrun_id ='|| p_checkrun_id ||'
4694 and apt.template_id(+) = aisc.template_id
4695 and aisc.invoice_batch_id = ab.batch_id(+)
4696 and aisc.payment_method_code = iby.payment_method_code(+)
4697 and aisc.vendor_type_lookup_code = vndr.lookup_code(+)
4698 and vndr.lookup_type(+) = ''VENDOR TYPE''
4699 and aisc.party_id = hz.party_id(+)
4700 and rate.lookup_type(+) = ''INVOICE_EXCHANGE_RATE_TYPE''
4701 and aisc.inv_exchange_rate_type = rate.lookup_code(+)');
4702
4703 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'CRITERIA');
4704 DBMS_XMLGEN.setRowTag(l_qryCtx, 'SELECTION_CRITERIA');
4705 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
4706 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
4707 DBMS_XMLGEN.closeContext(l_qryCtx);
4708 ap_utilities_pkg.clob_to_file(l_result_clob);
4709
4710
4711 l_debug_info := 'select pay group';
4712
4713
4714 l_qryCtx := DBMS_XMLGEN.newContext('SELECT vendor_pay_group '||
4715 'FROM ap_pay_group '||
4716 'WHERE checkrun_id = '||to_char(p_checkrun_id));
4717 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'PAY');
4718 DBMS_XMLGEN.setRowTag(l_qryCtx, 'PAY_GROUP');
4719 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
4720 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
4721 DBMS_XMLGEN.closeContext(l_qryCtx);
4722 ap_utilities_pkg.clob_to_file(l_result_clob);
4723
4724
4725 l_debug_info := 'select currency group';
4726
4727 l_qryCtx := DBMS_XMLGEN.newContext('SELECT currency_code '||
4728 'FROM AP_CURRENCY_GROUP '||
4729 'WHERE checkrun_id = '||to_char(p_checkrun_id));
4730 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'CURRENCY');
4731 DBMS_XMLGEN.setRowTag(l_qryCtx, 'CURRENCY_GROUP');
4732 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
4733 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
4734 DBMS_XMLGEN.closeContext(l_qryCtx);
4735 ap_utilities_pkg.clob_to_file(l_result_clob);
4736
4737
4738
4739 l_debug_info:= 'select le group';
4740
4741 l_qryCtx := DBMS_XMLGEN.newContext('SELECT name legal_entity_name '||
4742 'FROM ap_le_group aleg, xle_entity_profiles xle '||
4743 'WHERE aleg.legal_entity_id = xle.legal_entity_id '||
4744 'AND checkrun_id = '||to_char(p_checkrun_id));
4745 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'LEGAL_ENTITY');
4746 DBMS_XMLGEN.setRowTag(l_qryCtx, 'LE_GROUP');
4747 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
4748 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
4749 DBMS_XMLGEN.closeContext(l_qryCtx);
4750 ap_utilities_pkg.clob_to_file(l_result_clob);
4751
4752
4753 l_debug_info := 'select ou group';
4754
4755 l_qryCtx := DBMS_XMLGEN.newContext('SELECT name organization_name '||
4756 'FROM AP_OU_GROUP AOG, HR_OPERATING_UNITS HR '||
4757 'WHERE hr.organization_id = aog.org_id '||
4758 'AND checkrun_id = '||to_char(p_checkrun_id));
4759 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'ORGANIZATION');
4760 DBMS_XMLGEN.setRowTag(l_qryCtx, 'OU_GROUP');
4761 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
4762 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
4763 DBMS_XMLGEN.closeContext(l_qryCtx);
4764 ap_utilities_pkg.clob_to_file(l_result_clob);
4765
4766 fnd_file.put_line(fnd_file.output, '</SELECTION_CRITERIA_RPT>');
4767
4768 EXCEPTION
4769
4770 WHEN OTHERS then
4771
4772 IF (SQLCODE <> -20001) THEN
4773 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4774 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4775 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
4776 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
4777 FND_MESSAGE.SET_TOKEN('PARAMETERS','p_checkrun_id '||p_checkrun_id);
4778
4779
4780
4781 END IF;
4782 APP_EXCEPTION.RAISE_EXCEPTION;
4783
4784
4785 END selection_criteria_report;
4786
4787
4788
4789
4790
4791 END AP_AUTOSELECT_PKG;