[Home] [Help]
PACKAGE BODY: APPS.AP_PMT_CALLOUT_PKG
Source
1 PACKAGE BODY AP_PMT_CALLOUT_PKG AS
2 /*$Header: apcnfrmb.pls 120.74.12020000.8 2012/12/01 20:46:00 lnilacan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PMT_CALLOUT_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(30) := 'AP.PLSQL.AP_PMT_CALLOUT_PKG.';
21
22 FUNCTION get_user_rate (p_base_currency_code in varchar2,
23 p_payment_currency_code in varchar2,
24 p_checkrun_id in number) return number is
25 l_rate number;
26 l_debug_info varchar2(1000);
27 l_api_name varchar2(200) := 'Get_user_rate';
28
29 begin
30
31 /* Added decode for bug 11740095 */
32 /* select exchange_rate */
33 select decode( nvl(fnd_profile.value('DISPLAY_INVERSE_RATE'),'N'),'N',exchange_rate,
34 'Y', 1/exchange_rate)
35 into l_rate
36 from ap_user_exchange_rates
37 where checkrun_id = p_checkrun_id
38 and ledger_currency_code = p_base_currency_code
39 and payment_currency_code = p_payment_currency_code;
40
41 l_debug_info := 'User exchange rate returned : '||l_rate;
42 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
43 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
44 END IF;
45
46 return l_rate;
47
48 end;
49
50
51
52 FUNCTION get_base_amount
53 ( p_base_currency_code in varchar2,
54 p_payment_currency_code in varchar2,
55 p_checkrun_id in number,
56 p_exchange_rate_type in varchar2,
57 p_base_currency_mac in number,
58 p_payment_amount in number,
59 p_base_currency_precision in number,
60 p_exchange_date in date) return number is
61
62 l_rate number;
63 l_base_amount number;
64
65 begin
66
67 if p_exchange_rate_type = 'User' then
68 l_rate := ap_pmt_callout_pkg.get_user_rate(p_base_currency_code,
69 p_payment_currency_code,
70 p_checkrun_id);
71
72
73 if p_base_currency_mac is null then
74 return round(p_payment_amount * l_rate, p_base_currency_precision);
75 else
76 return round(((p_payment_amount * l_rate)/p_base_currency_mac)*p_base_currency_mac);
77 end if;
78
79 else --exchange rate is not a user rate
80
81
82 l_base_amount := gl_currency_api.convert_amount_sql(
83 p_payment_currency_code,
84 p_base_currency_code,
85 p_exchange_date,
86 p_exchange_rate_type,
87 p_payment_amount);
88
89 if l_base_amount not in (-1,-2) then
90 return l_base_amount;
91 else
92 return null;
93 end if;
94
95
96 end if;
97
98
99
100 end;
101
102
103
104
105 PROCEDURE assign_vouchers (p_completed_pmts_group_id IN number,
106 p_checkrun_id in number,
107 p_first_voucher_number in number,
108 p_current_org_id in number) IS
109
110 l_next_voucher_number number;
111 l_check_id number;
112 /* Start - BUG 14364091 - Logging */
113 l_api_name varchar2(30);
114 l_debug_info varchar2(2000);
115 /* End - BUG 14364091 - Logging */
116
117 cursor checks_to_assign_vouchers is
118 select check_id
119 from ap_checks_all
120 where completed_pmts_group_id = p_completed_pmts_group_id
121 and org_id = p_current_org_id;
122
123
124 begin
125 /* Start - BUG 14364091 - Logging */
126 l_api_name := 'assign_vouchers';
127 /* End - BUG 14364091 - Logging */
128
129 select next_voucher_number
130 into l_next_voucher_number
131 from ap_inv_selection_criteria_all
132 where checkrun_id = p_checkrun_id
133 for update;
134
135 if l_next_voucher_number is null then
136 l_next_voucher_number := p_first_voucher_number;
137 end if;
138
139 /* Start - BUG 14364091 - Logging */
140 l_debug_info := 'opening checks_to_assign_vouchers cursor ';
141 /* End - BUG 14364091 - Logging */
142 open checks_to_assign_vouchers;
143 loop
144 fetch checks_to_assign_vouchers into l_check_id;
145 exit when checks_to_assign_vouchers%notfound;
146
147 update ap_checks_all
148 set check_voucher_num = l_next_voucher_number
149 where check_id = l_check_id;
150
151 l_next_voucher_number := l_next_voucher_number + 1;
152
153
154
155 end loop;
156 /* Start - BUG 14364091 - Logging */
157 l_debug_info := 'updating ap_inv_selection_criteria_all with voucher number ';
158 /* End - BUG 14364091 - Logging */
159 update ap_inv_selection_criteria_all
160 set next_voucher_number = l_next_voucher_number
161 where checkrun_id = p_checkrun_id;
162
163 /* Start - BUG 14364091 - Logging */
164 EXCEPTION
165 WHEN OTHERS THEN
166 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
167 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
168 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
169 APP_EXCEPTION.RAISE_EXCEPTION;
170 /* End - BUG 14364091 - Logging */
171 end;
172
173
174
175 PROCEDURE assign_int_inv_sequence(p_check_id in number,
176 p_check_date in date,
177 p_seq_num_profile in varchar2,
178 p_set_of_books_id in number,
179 x_return_status IN OUT NOCOPY VARCHAR2,
180 x_msg_count IN OUT NOCOPY NUMBER,
181 x_msg_data IN OUT NOCOPY VARCHAR2) IS
182
183
184
185
186
187
188 CURSOR C_INTEREST_INVOICES IS
189 SELECT i.invoice_id
190 FROM ap_invoice_payments_all aip,
191 ap_checks_all c,
192 ap_invoices_all i,
193 ap_invoice_relationships ir
194 WHERE c.check_id = p_check_id
195 AND c.check_id = aip.check_id
196 AND aip.invoice_id = ir.related_invoice_id
197 AND aip.invoice_id = i.invoice_id
198 AND i.invoice_type_lookup_code = 'INTEREST';
199
200 l_invoice_id number;
201 l_return_code number;
202 l_int_seqval number;
203 l_int_dbseqid number;
204
205 /* Start - BUG 14364091 - Logging */
206 l_api_name varchar2(30);
207 l_debug_info varchar2(2000);
208 /* End - BUG 14364091 - Logging */
209
210 BEGIN
211
212 /* Start - BUG 14364091 - Logging */
213 l_api_name := 'assign_int_inv_sequence';
214 /* End - BUG 14364091 - Logging */
215
216 /* Initialize return status */
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 if p_seq_num_profile = 'P' and
220 x_msg_data is not null then
221 return; --return without raising an error
222 end if;
223
224 /* Start - BUG 14364091 - Logging */
225 l_debug_info := 'opening c_interest_invoices cursor ';
226 /* End - BUG 14364091 - Logging */
227 open c_interest_invoices;
228 loop
229 fetch c_interest_invoices INTO l_invoice_id;
230 exit when c_interest_invoices%notfound;
231
232 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
233 200,
234 'INT INV',
235 p_set_of_books_id,
236 'A',
237 p_check_date,
238 l_int_seqval,
239 l_int_dbseqid,
240 'N',
241 'N');
242
243 if (l_return_code <> 0 or l_int_seqval is null) and
244 p_seq_num_profile = 'A' then
245
246 x_return_status:= FND_API.G_RET_STS_ERROR;
247 x_msg_count:= 1;
248 x_msg_data := 'Invalid Interest Invoice Sequence';
249 return;
250
251 elsif l_return_code = 0 then
252
253 /* Start - BUG 14364091 - Logging */
254 l_debug_info := 'updating ap_invoices_all ';
255 /* End - BUG 14364091 - Logging */
256 update ap_invoices_all
257 set doc_sequence_id = l_int_dbseqid,
258 doc_sequence_value = l_int_seqval,
259 doc_category_code = 'INT INV'
260 where invoice_id = l_invoice_id;
261
262 end if;
263
264 END LOOP;
265 /* Start - BUG 14364091 - Logging */
266 EXCEPTION
267 WHEN OTHERS THEN
268 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
270 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
271 APP_EXCEPTION.RAISE_EXCEPTION;
272 /* End - BUG 14364091 - Logging */
273
274 END assign_int_inv_sequence;
275
276
277
278
279 PROCEDURE assign_sequences(p_checkrun_id IN number,
280 p_completed_pmts_group_id IN number,
281 p_set_of_books_id IN number,
282 p_seq_num_profile IN varchar,
283 x_return_status IN OUT nocopy VARCHAR2,
284 x_msg_count IN OUT nocopy NUMBER,
285 x_msg_data IN OUT nocopy VARCHAR2,
286 p_auto_calculate_interest_flag in varchar2,
287 p_interest_invoice_count in number,
288 p_check_date date,
289 p_current_org_id in number) IS
290
291
292
293
294 cursor check_sequences is
295 select ac.check_id,
296 ac.payment_document_id,
297 ac.payment_method_code,
298 ac.ce_bank_acct_use_id
299 from ap_checks_all ac,
300 iby_payment_profiles ipp
301 where completed_pmts_group_id = p_completed_pmts_group_id
302 and ac.checkrun_id = p_checkrun_id --bug15924350
303 and ipp.payment_profile_id = ac.payment_profile_id
304 and ac.org_id = p_current_org_id;
305
306
307 l_check_id number;
308 l_payment_document_id number;
309 l_payment_method_code varchar2(30);
310 l_bank_acct_use_id number;
311 l_doc_category_code varchar2(30);
312 l_seqval number;
313 l_dbseqid number;
314 l_return_code number;
315 l_docseq_id number;
316 l_docseq_type char(1); -- Bug 5555642
317 l_docseq_name varchar2(30);
318 l_db_seq_name varchar2(30);
319 l_seq_ass_id number;
320 l_prd_tab_name varchar2(30);
321 l_aud_tab_name varchar2(30);
322 l_msg_flag varchar(1);
323 l_api_name VARCHAR2(100);
324 l_debug_info varchar2(2000);
325
326 BEGIN
327
328
329 /* Initialize return status */
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331
332 l_api_name := 'assign_sequences';
333
334 -- Bug 5512197.Adding Fnd_Logging for this procedure
335 l_debug_info := 'opening check_sequences cursor ';
336 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
338 END IF;
339
340 open check_sequences;
341 loop
342 fetch check_sequences into l_check_id, l_payment_document_id, l_payment_method_code, l_bank_acct_use_id;
343
344 exit when check_sequences%notfound;
345
346 l_debug_info := 'calling CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat ';
347 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
348 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
349 END IF;
350
351 CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat(l_payment_document_id,
352 l_payment_method_code,
353 l_bank_acct_use_id,
354 l_doc_category_code); --out
355
356
357
358 l_debug_info := 'Value of doc category code from CE API: '|| l_doc_category_code;
359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
360 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
361 END IF;
362
363
364 -- Bug 5555642. Added fnd-logging and messages for sequence numbering
365 --if p_seq_num_profile = 'A' and l_doc_category_code = '-1' then
366 -- Bug 5555642
367 if l_doc_category_code = '-1' then
368 -- x_return_status:= FND_API.G_RET_STS_ERROR; --Bug6258525
369 x_msg_count:= 1;
370 if p_seq_num_profile = 'P' then
371 x_msg_data := 'No document category found and sequential number is set to partially used';
372 l_debug_info := 'No document category found from CE API and sequential number is set to '||
373 'partially used';
374 elsif p_seq_num_profile = 'A' then
375 x_msg_data := 'No document category found and sequential number is set to always used';
376 l_debug_info := 'No document category found from CE API and sequential number is set to '||
377 'always used';
378 x_return_status:= FND_API.G_RET_STS_ERROR; --Bug6258525
379 end if;
380 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
382 END IF;
383
384 return;
385
386 end if;
387
388
389
390 if l_doc_category_code <> '-1' then
391
392 l_debug_info := 'calling fnd_seqnum.get_seq_info ';
393 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
394 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
395 END IF;
396
397 l_debug_info := 'set_of_books_id: '||p_set_of_books_id||' check_date: '||
398 to_char(p_check_date, 'DD-MON-YYYY');
399 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
400 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
401 END IF;
402
403
404 l_return_code := fnd_seqnum.get_seq_info(
405 app_id => 200,
406 cat_code => l_doc_category_code,
407 sob_id => p_set_of_books_id,
408 met_code => 'A',
409 trx_date => p_check_date,
410 docseq_id => l_docseq_id,
411 docseq_type => l_docseq_type,
412 docseq_name => l_docseq_name,
413 db_seq_name => l_db_seq_name,
414 seq_ass_id => l_seq_ass_id,
415 prd_tab_name => l_prd_tab_name,
416 aud_tab_name => l_aud_tab_name,
417 msg_flag => l_msg_flag);
418
419 if l_return_code = 0 then
420 if l_docseq_type = 'M' then
421 x_msg_data := 'Manual Sequence assigned to Automatic Payments';
422 end if;
423
424 elsif l_return_code = -1 then
425 x_msg_data := 'An oracle error occurred';
426 elsif l_return_code = -2 then
427 x_msg_data := 'No sequence assignment exists';
428 elsif l_return_code = -3 then
429 x_msg_data := 'The assigned sequence is inactive';
430 elsif l_return_code = -8 then
431 x_msg_data := 'Sequential Numbering is always used and there is no assignment';
432 else
433 x_msg_data := 'Invalid document sequence setup';
434 end if;
435
436 --Bug 6736077
437 IF l_doc_category_code <> '-1' AND p_seq_num_profile = 'P' THEN
438 IF l_return_code = -2 THEN
439 RETURN;
440 END IF;
441 END IF;
442
443 l_debug_info := 'Return Code from fnd_seqnum.get_seq_info: '||l_return_code;
444 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
445 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
446 END IF;
447
448 l_debug_info := 'docseq_id: '||l_docseq_id||', docseq_type: '||l_docseq_type
449 ||' ,docseq_name: '||l_docseq_name;
450 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
451 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
452 END IF;
453
454 l_debug_info := 'db_seq_name: '||l_db_seq_name||', seq_ass_id: '||l_seq_ass_id;
455 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
457 END IF;
458
459
460 l_debug_info := 'prd_tab_name: '||l_prd_tab_name||', aud_tab_name: '||l_aud_tab_name;
461 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
462 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
463 END IF;
464
465
466 if x_msg_data is not null then
467 x_return_status:= FND_API.G_RET_STS_ERROR;
468 x_msg_count:= 1;
469 l_debug_info := x_msg_data;
470 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
472 END IF;
473 return;
474 else
475 l_debug_info := 'FND_SEQNUM.Get_Seq_Info returned succesfully';
476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
478 END IF;
479
480 end if;
481
482
483
484 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
485 200,
486 l_doc_category_code,
487 p_set_of_books_id,
488 'A',
489 p_check_date,
490 l_seqval,
491 l_dbseqid,
492 'N',
493 'N');
494
495 l_debug_info := 'Return Code from fnd_seqnum.get_seq_val: '||l_return_code;
496 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
498 END IF;
499
500 l_debug_info := 'seq_val: '||l_seqval||', docseq_d: '||l_dbseqid;
501 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
502 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
503 END IF;
504
505 if l_return_code <> 0 or l_seqval is null then
506
507 x_return_status:= FND_API.G_RET_STS_ERROR;
508 x_msg_count:= 1;
509 x_msg_data := 'Invalid Sequence';
510 return;
511
512 elsif l_return_code = 0 then
513
514 update ap_checks_all
515 set doc_sequence_id = l_dbseqid,
516 doc_sequence_value = l_seqval,
517 doc_category_code = l_doc_category_code
518 where check_id = l_check_id;
519
520
521 end if;
522
523 end if;
524
525
526
527
528
529 IF p_auto_calculate_interest_flag = 'Y' and p_interest_invoice_count > 0 then
530
531 l_return_code := fnd_seqnum.get_seq_info(
532 app_id => 200,
533 cat_code => 'INT INV',
534 sob_id => p_set_of_books_id,
535 met_code => 'A',
536 trx_date => p_check_date,
537 docseq_id => l_docseq_id,
538 docseq_type => l_docseq_type,
539 docseq_name => l_docseq_name,
540 db_seq_name => l_db_seq_name,
541 seq_ass_id => l_seq_ass_id,
542 prd_tab_name => l_prd_tab_name,
543 aud_tab_name => l_aud_tab_name,
544 msg_flag => l_msg_flag);
545
546 if l_return_code = 0 then
547 if l_docseq_type = 'M' then
548 x_msg_data := 'Manual Sequence assigned to interest invoices';
549 end if;
550
551 elsif l_return_code = -1 then
552 x_msg_data := 'An oracle error occurred';
553 elsif l_return_code = -2 then
554 x_msg_data := 'No sequence assignment exists for interest invoices';
555 elsif l_return_code = -3 then
556 x_msg_data := 'The assigned interest invoice sequence is inactive';
557 elsif l_return_code = -8 then
558 x_msg_data := 'Sequential Numbering is always used and there is no assignment for interest invoices';
559 else
560 x_msg_data := 'Invalid document sequence setup for interest invoices';
561 end if;
562
563 if x_msg_data is not null and p_seq_num_profile = 'A' then
564 x_return_status:= FND_API.G_RET_STS_ERROR;
565 x_msg_count:= 1;
566 return;
567 end if;
568
569
570 assign_int_inv_sequence ( l_check_id,
571 p_check_date,
572 p_seq_num_profile,
573 p_set_of_books_id,
574 x_return_status,
575 x_msg_count,
576 x_msg_data);
577
578 if x_return_status = FND_API.G_RET_STS_ERROR then
579 return;
580 end if;
581
582
583
584 END if;
585
586 end loop;
587
588 /* Start - BUG 14364091 - Logging */
589 EXCEPTION
590 WHEN OTHERS THEN
591 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
592 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
593 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
594 APP_EXCEPTION.RAISE_EXCEPTION;
595 /* End - BUG 14364091 - Logging */
596 END assign_sequences;
597
598
599 PROCEDURE documents_payable_rejected
600 ( p_api_version IN NUMBER,
601 p_init_msg_list IN VARCHAR2,
602 p_commit IN VARCHAR2,
603 x_return_status OUT nocopy VARCHAR2,
604 x_msg_count OUT nocopy NUMBER,
605 x_msg_data OUT nocopy VARCHAR2,
606 p_rejected_docs_group_id IN NUMBER) IS
607
608
609 l_checkrun_id number;
610 l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;
611 l_check_date date;
612 l_debug_info varchar2(200);
613 /* Start - BUG 14364091 - Logging */
614 l_api_name varchar2(30);
615 /* End - BUG 14364091 - Logging */
616
617 /* A single Payment Instruction can carry payments from multiple PPRs, hence a cursor to hold Check Run IDs Bug 14091572*/
618 CURSOR c_checkrun_id IS
619 select distinct calling_app_doc_unique_ref1
620 from iby_fd_docs_payable_v
621 where rejected_docs_group_id = p_rejected_docs_group_id;
622
623 rec_checkrun_id c_checkrun_id%ROWTYPE;
624 /*Bug 5020577*/
625 /*Introduced the join for org id with IBY tables*/
626 CURSOR c_sel_invs IS
627 SELECT invoice_id,
628 vendor_id,
629 payment_num
630 FROM ap_SELECTed_invoices_all ASI,
631 ap_system_parameters_all asp,
632 iby_fd_docs_payable_v ibydocs
633 WHERE checkrun_name = l_checkrun_name
634 AND original_invoice_id IS NULL
635 /* Bug 6950891. Added TO_CHAR */
636 /* Bug 12730662. Removed TO_CHAR */
637 AND ibydocs.calling_app_doc_unique_ref1 = ASI.checkrun_id
638 AND ibydocs.calling_app_doc_unique_ref2 = ASI.invoice_id
639 AND ibydocs.calling_app_doc_unique_ref3 = ASI.payment_num
640 AND ibydocs.rejected_docs_group_id = p_rejected_docs_group_id
641 AND asp.org_id = asi.org_id
642 and ibydocs.org_id=asp.org_id
643 and ibydocs.calling_app_id = 200
644 and checkrun_id = l_checkrun_id
645 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
646 decode(ASP.create_awt_dists_type,'BOTH','Y','PAYMENT',
647 'Y', decode(ASP.create_awt_invoices_type,'BOTH','Y','PAYMENT',
648 'Y', 'N'),
649 'N'),--Bug6660355
650 'N') = 'Y';
651
652 rec_sel_invs c_sel_invs%ROWTYPE;
653
654
655
656 BEGIN
657 OPEN c_checkrun_id;
658
659 LOOP
660 l_debug_info := 'Fetch CURSOR for all PPRs in a payment Instruction'; --Bug 14091572
661 FETCH c_checkrun_id INTO rec_checkrun_id;
662 EXIT WHEN c_checkrun_id%NOTFOUND;
663
664 /* Start - BUG 14364091 - Logging */
665 l_api_name := 'documents_payable_rejected';
666 /* End - BUG 14364091 - Logging */
667
668 l_checkrun_id := rec_checkrun_id.calling_app_doc_unique_ref1;
669
670
671 BEGIN
672
673 select checkrun_name, check_date
674 into l_checkrun_name, l_check_date
675 from ap_inv_selection_criteria_all
676 where checkrun_id = l_checkrun_id;
677
678 OPEN c_sel_invs;
679
680 LOOP
681 l_debug_info := 'Fetch CURSOR for all SELECTed invoices';
682 FETCH c_sel_invs INTO rec_sel_invs;
683 EXIT WHEN c_sel_invs%NOTFOUND;
684
685 DECLARE
686 undo_output VARCHAR2(2000);
687 BEGIN
688 AP_WITHHOLDING_PKG.Ap_Undo_Temp_Withholding
689 (P_Invoice_Id => rec_sel_invs.invoice_id
690 ,P_VENDor_Id => rec_sel_invs.vendor_id
691 ,P_Payment_Num => rec_sel_invs.payment_num
692 ,P_Checkrun_Name => l_Checkrun_Name
693 ,P_Undo_Awt_Date => SYSDATE
694 ,P_Calling_Module => 'CANCEL'
695 ,P_Last_Updated_By => to_number(FND_PROFILE.VALUE('USER_ID'))
696 ,P_Last_Update_Login => to_number(FND_PROFILE.VALUE('LOGIN_ID'))
697 ,P_Program_Application_Id => to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID'))
698 ,P_Program_Id => to_number(FND_PROFILE.VALUE('PROGRAM_ID'))
699 ,P_Request_Id => to_number(FND_PROFILE.VALUE('REQUEST_ID'))
700 ,P_Awt_Success => undo_output
701 ,P_checkrun_id => l_checkrun_id );
702 END;
703 END LOOP;
704
705 l_debug_info := 'CLOSE CURSOR for all SELECTed invoices';
706 CLOSE c_sel_invs;
707
708
709 --4693463
710
711 delete from ap_unselected_invoices_all
712 where checkrun_id = l_checkrun_id;
713
714 delete from ap_selected_invoices_all
715 where checkrun_id = l_checkrun_id
716 /* Bug 6950891. Added TO_CHAR */
717 /* Bug 12679356. Removed to_char */
718 and (invoice_id, payment_num) in
719 (select calling_app_doc_unique_ref2,
720 calling_app_doc_unique_ref3
721 from iby_fd_docs_payable_v
722 where rejected_docs_group_id = p_rejected_docs_group_id);
723
724 update ap_payment_schedules_all
725 set checkrun_id = null
726 where checkrun_id = l_checkrun_id
727 /* Bug 6950891. Added TO_CHAR */
728 /* Bug 12679356. Removed to_char */
729 and (invoice_id, payment_num) in
730 (select calling_app_doc_unique_ref2,
731 calling_app_doc_unique_ref3
732 from iby_fd_docs_payable_v
733 where rejected_docs_group_id = p_rejected_docs_group_id);
734
735
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 END;
739 END LOOP;
740
741 l_debug_info := 'Close CURSOR for all PPRs in a payment Instruction'; --Bug 14091572
742 CLOSE c_checkrun_id;
743
744 EXCEPTION --Bug 14472363
745 WHEN OTHERS THEN
746 IF c_sel_invs%ISOPEN THEN
747 CLOSE c_sel_invs;
748 END IF;
749
750 IF c_checkrun_id%ISOPEN THEN
751 CLOSE c_checkrun_id;
752 END IF;
753 /* Start - BUG 14364091 - Logging */
754 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
755 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
756 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
757 APP_EXCEPTION.RAISE_EXCEPTION;
758
759 /* End - BUG 14364091 - Logging */
760
761
762 END documents_payable_rejected;
763
764
765
766
767
768
769 PROCEDURE payments_completed
770 (p_api_version IN NUMBER,
771 p_init_msg_list IN VARCHAR2,
772 p_commit IN VARCHAR2,
773 x_return_status OUT nocopy VARCHAR2,
774 x_msg_count OUT nocopy NUMBER,
775 x_msg_data OUT nocopy VARCHAR2,
776 p_completed_pmts_group_id IN NUMBER) IS
777
778
779 l_batch_control_flag varchar2(1);
780 l_interest_terms_id number;
781 l_nls_int_inv_desc varchar2(80);
782 l_perform_awt_flag varchar2(1);
783 l_set_of_books_id number;
784 l_interest_invoice_count number;
785 l_checkrun_id number;
786 l_checkrun_name ap_checks_all.checkrun_name%type;
787 l_exchange_rate_type varchar2(30);
788 l_transfer_priority varchar2(25);
789 l_check_date date;
790 l_current_org_id number;
791 l_when_to_account_payment varchar2(20);
792 l_company_name varchar2(30);
793 l_int_asset_tracking_flag varchar2(1);
794 l_report_date varchar2(30); --Bug 11858238 Change size from 15 to 30
795 l_interest_code_combination_id number;
796 l_base_currency_code varchar2(10);
797 l_auto_calculate_interest_flag varchar2(1);
798 l_interest_accts_pay_ccid number;
799 l_int_batch_id number;
800 l_prorate_int_inv_across_dists varchar2(1);
801 l_accounting_event_id number;
802 l_period_name varchar2(15);
803 l_last_updated_by number;
804
805 l_seq_num_profile varchar2(80);
806 l_first_voucher_number number;
807 l_base_currency_mac number;
808 l_base_currency_precision number;
809
810 l_current_calling_sequence varchar2(2000);
811 l_debug_info varchar2(2000);
812
813 l_check_id number;
814 l_application_id number;
815 l_return_status varchar2(1);
816 l_msg_count number;
817 l_msg_data varchar2(2000);
818
819 subscribe_exception exception;
820
821 --Bug 5115310 Start
822 TYPE checkrun_id_tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
823 l_checkrun_id_list checkrun_id_tab;
824 l_check_count NUMBER;
825 l_total_check_count NUMBER;
826 l_docs_count NUMBER;
827 l_total_docs_count NUMBER;
828 l_iby_check_count NUMBER;
829 l_iby_docs_count NUMBER;
830 l_api_name VARCHAR2(100);
831
832
833 CURSOR c_relevant_checkrun_ids IS
834 SELECT distinct calling_app_doc_unique_ref1
835 FROM iby_fd_payments_v pmts,
836 iby_fd_docs_payable_v docs
837 WHERE pmts.payment_id = docs.payment_id
838 AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
839 AND pmts.org_type = 'OPERATING_UNIT';
840
841
842 CURSOR c_relevant_orgs(c_checkrun_id IN NUMBER) is
843 SELECT distinct pmts.org_id
844 FROM iby_fd_payments_v pmts,
845 iby_fd_docs_payable_v docs
846 WHERE pmts.payment_id = docs.payment_id
847 AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
848 AND pmts.org_type = 'OPERATING_UNIT'
849 AND docs.calling_app_doc_unique_ref1 = c_checkrun_id;
850 --Bug 5115310 End
851
852
853 -- Added for Payment Request
854 CURSOR c_subscribed_payments IS
855 SELECT Distinct AC.Check_ID,
856 APR.Reg_Application_ID
857 FROM AP_Checks_All AC,
858 AP_Invoice_Payments_All AIP,
859 AP_Invoices_All AI,
860 AP_Product_Registrations APR
861 WHERE AC.Checkrun_Name = l_checkrun_name
862 AND AC.Completed_Pmts_Group_ID = p_completed_pmts_group_id
863 AND AC.Org_ID = l_current_org_id
864 AND AC.Check_ID = AIP.Check_ID
865 AND AIP.Invoice_ID = AI.Invoice_ID
866 AND AI.Application_ID = APR.Reg_Application_ID
867 AND APR.Registration_Event_Type = 'PAYMENT_CREATED';
868
869 -- Bug 5658623. Cursor proceesing for Performance reason
870 CURSOR c_invoice_amounts(p_last_updated_by Number,
871 p_completed_group_id Number,
872 p_current_org_id Number,
873 p_checkrun_name Varchar2) IS
874 SELECT sysdate,
875 p_last_updated_by,
876 iby_amount_paid,
877 iby_discount_amount_taken,
878 AP_INVOICES_UTILITY_PKG.get_payment_status(inv.invoice_id),
879 inv.invoice_id
880 FROM ap_invoices_all inv,
881 ap_selected_invoices_all si,
882 (SELECT sum(ibydocs.payment_amount) iby_amount_paid,
883 nvl(sum(ibydocs.payment_curr_discount_taken),0) iby_discount_amount_taken,
884 ibydocs.calling_app_doc_unique_ref1 ref1,
885 ibydocs.calling_app_doc_unique_ref2 ref2,
886 ibydocs.calling_app_doc_unique_ref3 ref3
887 FROM iby_fd_docs_payable_v ibydocs,
888 iby_fd_payments_v ibypmts
889 WHERE ibypmts.org_type = 'OPERATING_UNIT'
890 AND ibypmts.payment_id = ibydocs.payment_id
891 AND ibypmts.completed_pmts_group_id = p_completed_group_id
892 AND ibypmts.org_id = p_current_org_id
893 GROUP BY ibydocs.calling_app_doc_unique_ref1,
894 ibydocs.calling_app_doc_unique_ref2,
895 ibydocs.calling_app_doc_unique_ref3) ibydpm
896 WHERE inv.invoice_id = si.invoice_id
897 AND si.checkrun_name = p_checkrun_name
898 AND inv.invoice_type_lookup_code <> 'INTEREST'
899 AND ibydpm.ref2 = to_char(inv.invoice_id)
900 AND ibydpm.ref1 = to_char(si.checkrun_id)
901 AND ibydpm.ref2 = to_char(si.invoice_id)
902 AND ibydpm.ref3 = to_char(si.payment_num);
903
904
905 -- Bug 5658623. Cursor proceesing for Performance reason
906 CURSOR c_schedule_amounts(p_last_updated_by Number,
907 p_completed_group_id Number,
908 p_current_org_id Number,
909 p_checkrun_name Varchar2) IS
910 SELECT sysdate,
911 p_last_updated_by,
912 (si.amount_remaining - ibydocs.payment_amount -
913 nvl(ibydocs.payment_curr_discount_taken,0)),
914 0,
915 decode(si.amount_remaining - ibydocs.payment_amount -
916 nvl(ibydocs.payment_curr_discount_taken,0), 0,
917 'Y', 'P'),
918 /* commented by zrehman for Bug#6836199 on 24-Jun-2008
919 (si.amount_remaining - si.proposed_payment_amount -
920 nvl(ibydocs.payment_curr_discount_taken,0)),
921 0,
922 decode(si.amount_remaining - si.proposed_payment_amount -
923 nvl(ibydocs.payment_curr_discount_taken,0), 0,
924 'Y', 'P'),*/
925 -- Added by epajaril to capture the AWT
926 -- Bug8477014: Undoing changes done for bug6836199
927 -- Bug8752557: Undoing changes done here for bug8477014
928 si.withholding_amount,
929 Null,
930 ps.invoice_id,
931 ps.payment_num
932 FROM ap_payment_schedules_all ps,
933 ap_invoices_all inv,
934 ap_selected_invoices_all si,
935 /*iby_fd_payments_v ibypmts, Commented for Bug#9459810 */
936 iby_fd_docs_payable_v ibydocs
937 WHERE si.checkrun_name = p_checkrun_name
938 AND si.payment_num = ps.payment_num
939 AND si.invoice_id = ps.invoice_id
940 AND ibydocs.calling_app_doc_unique_ref1 = to_char(si.checkrun_id)
941 AND ibydocs.calling_app_doc_unique_ref2 = to_char(si.invoice_id)
942 AND ibydocs.calling_app_doc_unique_ref3 = to_char(si.payment_num)
943 /*AND ibypmts.payment_id = ibydocs.payment_id
944 AND ibypmts.completed_pmts_group_id = p_completed_group_id
945 AND ibypmts.org_id = p_current_org_id
946 AND ibypmts.org_type = 'OPERATING_UNIT' Commented for bug#9459810*/
947 /* Added for bug#9459810 Start */
948 AND ibydocs.completed_pmts_group_id = p_completed_group_id
949 AND ibydocs.org_id = p_current_org_id
950 AND ibydocs.org_type = 'OPERATING_UNIT'
951 /* Added for bug#9459810 End */
952 AND inv.invoice_id = si.invoice_id
953 AND inv.invoice_id = ps.invoice_id
954 AND inv.invoice_type_lookup_code <> 'INTEREST';
955
956 -- Bug 5658623. Forall Processing for updating invoices and schedules
957 TYPE t_date_tab IS TABLE OF date INDEX BY binary_integer;
958 TYPE t_number_tab IS TABLE OF number INDEX BY binary_integer;
959 TYPE t_char_tab IS TABLE OF varchar2(10) INDEX BY binary_integer;
960
961 last_update_date_inv_l t_date_tab;
962 last_updated_by_inv_l t_number_tab;
963 amount_paid_inv_l t_number_tab;
964 discount_taken_inv_l t_number_tab;
965 payment_status_inv_l t_char_tab;
966 invoice_id_inv_l t_number_tab;
967
968 last_update_date_ps_l t_date_tab;
969 last_updated_by_ps_l t_number_tab;
970 amount_remaining_ps_l t_number_tab;
971 discount_remaining_ps_l t_number_tab;
972 payment_status_ps_l t_char_tab;
973 checkrun_id_ps_l t_number_tab;
974 invoice_id_ps_l t_number_tab;
975 payment_num_ps_l t_number_tab;
976 awt_num_ps_l t_number_tab;
977 l_wf_event_exists NUMBER; /* Added for bug#9459810 */
978
979 --Bug 9074840 start
980 TYPE prob_pay_rec IS RECORD
981 ( payment_id iby_payments_all.payment_id%TYPE
982 , payment_reference_number iby_payments_all.payment_reference_number%TYPE
983 , payment_instruction_id iby_payments_all.payment_instruction_id%TYPE
984 , invoice_id iby_docs_payable_all.calling_app_doc_unique_ref2%TYPE
985 , payment_num iby_docs_payable_all.calling_app_doc_unique_ref3%TYPE
986 , prob_type varchar2(200));
987
988 TYPE prob_pmt_tab IS TABLE OF prob_pay_rec INDEX BY BINARY_INTEGER;
989 l_prob_pmt_list prob_pmt_tab;
990
991
992 CURSOR c_prob_iby_payments IS
993 select payment_id, payment_reference_number, payment_instruction_id
994 , null, null, 'IBY_PMT'
995 FROM iby_fd_payments_v ifp
996 WHERE ifp.completed_pmts_group_id = p_completed_pmts_group_id
997 and not exists
998 (select 1
999 from ap_checks_all c
1000 where c.completed_pmts_group_id = ifp.completed_pmts_group_id
1001 and c.payment_id = ifp.payment_id);
1002
1003 CURSOR c_prob_iby_docs IS
1004 select ifp.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
1005 , ifd.calling_app_doc_unique_ref2, ifd.calling_app_doc_unique_ref3
1006 , 'IBY_DOC'
1007 from iby_fd_docs_payable_v ifd
1008 , iby_fd_payments_v ifp
1009 where ifd.payment_id = ifp.payment_id (+)
1010 and ifd.completed_pmts_group_id = p_completed_pmts_group_id
1011 and not exists
1012 (select 1
1013 from ap_invoice_payments_all ip
1014 , ap_checks_all c
1015 where c.payment_id = ifd.payment_id
1016 and c.completed_pmts_group_id = ifd.completed_pmts_group_id
1017 and c.check_id = ip.check_id
1018 and ifd.calling_app_doc_unique_ref2 = TO_CHAR(ip.invoice_id)
1019 and ifd.calling_app_doc_unique_ref3 = TO_CHAR(ip.payment_num));
1020
1021 CURSOR c_prob_checks IS
1022 select c.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
1023 , null, null, 'AP_CHECK'
1024 from ap_checks_all c
1025 , iby_fd_payments_v ifp
1026 where c.completed_pmts_group_id = ifp.completed_pmts_group_id
1027 and c.payment_id = ifp.payment_id
1028 and c.completed_pmts_group_id = p_completed_pmts_group_id
1029 group by c.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
1030 having count(c.check_id) > 1;
1031
1032 CURSOR c_prob_inv_pmts IS
1033 select ifp.payment_id, ifp.payment_reference_number
1034 , ifp.payment_instruction_id, TO_CHAR(ip.invoice_id), TO_CHAR(ip.payment_num)
1035 , 'AP_INV_PAY'
1036 from ap_invoice_payments_all ip
1037 , ap_checks_all c
1038 , iby_fd_payments_v ifp
1039 where ip.check_id = c.check_id
1040 and ifp.payment_id = c.payment_id
1041 and ifp.completed_pmts_group_id = c.completed_pmts_group_id
1042 and c.completed_pmts_group_id = p_completed_pmts_group_id
1043 group by ifp.payment_id, ifp.payment_reference_number
1044 , ifp.payment_instruction_id, ip.invoice_id, ip.payment_num
1045 having count(ip.invoice_payment_id) > 1;
1046
1047 PROCEDURE print_prob_pmt_detail (p_prob_pmt_list prob_pmt_tab
1048 , p_message_name varchar2) IS
1049 BEGIN
1050 if p_prob_pmt_list.exists(1) then
1051 fnd_message.set_name('SQLAP', p_message_name);
1052 l_debug_info := fnd_message.get;
1053 FND_FILE.PUT_LINE(FND_FILE.LOG, l_debug_info);
1054
1055 for i in l_prob_pmt_list.FIRST .. l_prob_pmt_list.LAST
1056 loop
1057 fnd_message.set_name('SQLAP', 'AP_IBY_PMT_MISMATCH_DETAIL');
1058 fnd_message.set_token('PAYMENT_REFERENCE_NUMBER', l_prob_pmt_list(i).payment_reference_number);
1059 fnd_message.set_token('PAYMENT_ID', l_prob_pmt_list(i).payment_id);
1060 fnd_message.set_token('PAYMENT_INSTRUCTION_ID', l_prob_pmt_list(i).payment_instruction_id);
1061 fnd_message.set_token('INVOICE_ID', l_prob_pmt_list(i).invoice_id);
1062 fnd_message.set_token('PAYMENT_NUM', l_prob_pmt_list(i).payment_num);
1063 l_debug_info := fnd_message.get;
1064 FND_FILE.PUT_LINE(FND_FILE.LOG, l_debug_info);
1065 end loop;
1066 end if;
1067 END print_prob_pmt_detail;
1068
1069 --Bug 9074840 end
1070
1071 BEGIN
1072 l_api_name := 'payments_completed';
1073 l_current_calling_sequence := 'AP_PMT_CALLOUT_PKG.payments_completed';
1074
1075 l_check_count :=0;
1076 l_total_check_count :=0;
1077 l_docs_count :=0;
1078 l_total_docs_count :=0;
1079 l_iby_check_count :=0;
1080 l_iby_docs_count :=0;
1081
1082
1083
1084 l_debug_info := 'get displayed Field';
1085 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1086 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1088 END IF;
1089
1090 SELECT displayed_field
1091 INTO l_nls_int_inv_desc
1092 FROM ap_lookup_codes
1093 WHERE lookup_type = 'NLS TRANSLATION'
1094 AND lookup_code = 'INTEREST OVERDUE INVOICE';
1095
1096
1097
1098 /* --Bug 5115310
1099 SELECT calling_app_doc_unique_ref1
1100 INTO l_checkrun_id
1101 FROM iby_fd_payments_v pmts,
1102 iby_fd_docs_payable_v docs
1103 WHERE pmts.payment_id = docs.payment_id
1104 AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
1105 AND pmts.org_type = 'OPERATING_UNIT'
1106 AND rownum=1;
1107 */
1108
1109
1110 --Bug 5115310
1111
1112 /* Initialize message list if p_init_msg_list is set to TRUE */
1113 /* IF FND_API.to_boolean(p_init_msg_list) THEN
1114 FND_MSG_PUB.initialize;
1115 END IF;
1116 */
1117 /* Initialize return status */
1118 x_return_status := FND_API.G_RET_STS_SUCCESS;
1119
1120 l_debug_info := 'get_relevant_checkrun_ids';
1121 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1122 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1123 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1124 END IF;
1125
1126 OPEN c_relevant_checkrun_ids;
1127 FETCH c_relevant_checkrun_ids
1128 BULK COLLECT INTO l_checkrun_id_list;
1129 CLOSE c_relevant_checkrun_ids;
1130
1131
1132 FOR i IN 1..l_checkrun_id_list.COUNT
1133 LOOP
1134
1135 l_checkrun_id := l_checkrun_id_list(i);
1136
1137 l_debug_info := 'l_checkrun_id: '||to_char(l_checkrun_id);
1138 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1139 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1140 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1141 END IF;
1142
1143 l_debug_info := 'get_checkrun_info';
1144 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1146 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1147 END IF;
1148
1149 SELECT exchange_rate_type,
1150 transfer_priority,
1151 check_date,
1152 checkrun_name,
1153 first_voucher_number
1154 INTO l_exchange_rate_type,
1155 l_transfer_priority,
1156 l_check_date, --use this for the exchange date also, PM confirmed
1157 l_checkrun_name,
1158 l_first_voucher_number
1159 FROM ap_inv_selection_criteria_all
1160 WHERE checkrun_id = l_checkrun_id;
1161
1162 l_debug_info := 'get_terms';
1163 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1164 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1165 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1166 END IF;
1167
1168 BEGIN
1169 --these are not multi-org table so should be fine
1170 SELECT apt.term_id
1171 INTO l_interest_terms_id
1172 FROM ap_terms apt, ap_terms_lines atl
1173 WHERE apt.term_id = atl.term_id
1174 AND atl.due_days=0
1175 AND nvl(end_date_active,sysdate+1) >= sysdate
1176 AND rownum < 2;
1177 EXCEPTION
1178 WHEN no_data_found THEN null;
1179 END;
1180
1181 l_last_updated_by := to_number(FND_GLOBAL.USER_ID);
1182
1183 l_debug_info := 'get_profiles';
1184 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1185 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1186 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1187 END IF;
1188 fnd_profile.get('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
1189 fnd_profile.get('AP_USE_INV_BATCH_CONTROLS', l_batch_control_flag);
1190
1191 OPEN c_relevant_orgs(l_checkrun_id);
1192
1193 l_debug_info := 'c_relevant_orgs';
1194 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1195 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1196 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1197 END IF;
1198
1199 LOOP
1200 FETCH c_relevant_orgs INTO l_current_org_id;
1201 EXIT WHEN c_relevant_orgs%NOTFOUND;
1202
1203 l_debug_info := 'l_current_org_id: '||to_char(l_current_org_id);
1204 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1205 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1207 END IF;
1208 l_debug_info := 'get_org_info';
1209 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1212 END IF;
1213
1214 /* Bug 5124784. dist_code_combination_id, proration across distribution will
1215 decided by parent invoice */
1216 SELECT nvl(when_to_account_pmt,'ALWAYS'),
1217 --nvl(prorate_int_inv_across_dists,'N'),
1218 interest_code_combination_id,
1219 base_currency_code,
1220 nvl(auto_calculate_interest_flag, 'N'),
1221 interest_accts_pay_ccid,
1222 DECODE(account_type, 'A','Y','N'),
1223 to_char(sysdate, 'DD-MON-RR HH24:MI'),
1224 gsob.name,
1225 decode(l_batch_control_flag, 'Y', AP_BATCHES_S.nextval, null),
1226 /* Added BOTH for performing AWT -- Bug 9697441 */
1227 decode(nvl(ASP.allow_awt_flag, 'N'),
1228 'Y', decode(ASP.create_awt_dists_type,
1229 'PAYMENT', 'Y', 'BOTH', 'Y',
1230 decode(ASP.create_awt_invoices_type,
1231 'PAYMENT', 'Y', 'BOTH', 'Y',
1232 'N')),
1233 'N'),
1234 asp.set_of_books_id,
1235 ap_utilities_pkg.get_gl_period_name(l_check_date,l_current_org_id)
1236 INTO l_when_to_account_payment,
1237 --l_prorate_int_inv_across_dists,
1238 l_interest_code_combination_id,
1239 l_base_currency_code,
1240 l_auto_calculate_interest_flag,
1241 l_interest_accts_pay_ccid,
1242 l_int_asset_tracking_flag,
1243 l_report_date,
1244 l_company_name,
1245 l_int_batch_id,
1246 l_perform_awt_flag,
1247 l_set_of_books_id,
1248 l_period_name
1249 FROM ap_system_parameters_all asp,
1250 gl_code_combinations gc,
1251 gl_sets_of_books gsob
1252 WHERE gc.code_combination_id(+) = asp.interest_code_combination_id
1253 AND gsob.set_of_books_id = asp.set_of_books_id
1254 AND asp.org_id = l_current_org_id;
1255
1256
1257 select minimum_accountable_unit, precision
1258 into l_base_currency_mac, l_base_currency_precision
1259 from fnd_currencies
1260 where currency_code = l_base_currency_code;
1261
1262
1263 IF l_auto_calculate_interest_flag = 'Y' THEN
1264
1265 l_debug_info := 'do interest invoice insertions';
1266 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1267 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1268 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1269 END IF;
1270
1271 INSERT INTO ap_invoices_all(
1272 invoice_id,
1273 last_update_date,
1274 last_updated_by,
1275 vendor_id,
1276 invoice_num,
1277 invoice_amount,
1278 vendor_site_id,
1279 amount_paid,
1280 discount_amount_taken,
1281 invoice_date,
1282 invoice_type_lookup_code,
1283 description,
1284 batch_id,
1285 amount_applicable_to_discount,
1286 tax_amount,
1287 terms_id,
1288 terms_date,
1289 voucher_num,
1290 pay_group_lookup_code,
1291 set_of_books_id,
1292 accts_pay_code_combination_id,
1293 invoice_currency_code,
1294 payment_currency_code,
1295 payment_status_flag,
1296 posting_status,
1297 creation_date,
1298 created_by,
1299 payment_cross_rate,
1300 exchange_rate,
1301 exchange_rate_type,
1302 exchange_date,
1303 base_amount,
1304 source,
1305 payment_method_code,
1306 pay_curr_invoice_amount,
1307 payment_cross_rate_date,
1308 payment_cross_rate_type,
1309 gl_date,
1310 exclusive_payment_flag,
1311 approval_ready_flag,
1312 wfapproval_status,
1313 legal_entity_id,
1314 org_id,
1315 party_id,
1316 party_site_id,
1317 -- added below columns for 7673570
1318 remit_to_supplier_name,
1319 remit_to_supplier_id,
1320 remit_to_supplier_site,
1321 remit_to_supplier_site_id,
1322 relationship_id)
1323 SELECT
1324 new.invoice_id,
1325 SYSDATE,
1326 l_last_updated_by,
1327 new.vendor_id,
1328 new.invoice_num,
1329 decode(fcinv.minimum_accountable_unit, null,
1330 round((new.payment_amount/orig.payment_cross_rate),
1331 fcinv.precision),
1332 round((new.payment_amount/orig.payment_cross_rate)
1333 /fcinv.minimum_accountable_unit)
1334 * fcinv.minimum_accountable_unit),
1335 new.vendor_site_id,
1336 ibydocs.payment_amount,
1337 0,
1338 new.due_date,
1339 'INTEREST',
1340 new.invoice_description||orig.invoice_num,
1341 l_int_batch_id,
1342 null,
1343 null,
1344 orig.terms_id, /* bug 5124784. Terms will be the parent Invoice term. */
1345 orig.terms_date,
1346 orig.voucher_num,
1347 orig.pay_group_lookup_code,
1348 orig.set_of_books_id,
1349 l_interest_accts_pay_ccid,
1350 orig.invoice_currency_code,
1351 orig.payment_currency_code,
1352 'Y',
1353 null,
1354 SYSDATE,
1355 l_last_updated_by,
1356 orig.payment_cross_rate,
1357 -- Start bug 8899917 use new instead of orig.
1358 new.payment_exchange_rate,
1359 new.payment_exchange_rate_type,
1360 l_check_date, -- exchange_date
1361 decode(orig.invoice_currency_code, l_base_currency_code,
1362 NULL,
1363 decode(l_base_currency_mac, null,
1364 round(new.payment_amount / orig.payment_cross_rate *
1365 nvl(new.payment_exchange_rate,1), l_base_currency_precision),
1366 round( (new.payment_amount / orig.payment_cross_rate *
1367 nvl(new.payment_exchange_rate,1)) /
1368 l_base_currency_mac) *
1369 l_base_currency_mac ) ),
1370 --end bug 8899917
1371 'Confirm PaymentBatch',
1372 orig.payment_method_code,
1373 new.payment_amount,
1374 orig.payment_cross_rate_date,
1375 orig.payment_cross_rate_type,
1376 new.due_date,
1377 new.exclusive_payment_flag,
1378 'Y',
1379 'NOT REQUIRED',
1380 ibypmts.legal_entity_id,
1381 ibypmts.org_id,
1382 orig.party_id,
1383 orig.party_site_id,
1384 -- added below columns for 7673570
1385 ibypmts.PAYEE_NAME,
1386 NVL(aps.vendor_id,-222), -- Modified for bug 8405513
1387 aps.vendor_site_code,
1388 NVL(ibypmts.supplier_site_id,-222), --modifed for bug 8405513
1389 ibypmts.relationship_id
1390 FROM ap_invoices_all orig,
1391 ap_supplier_sites_all aps, -- bug 7673570
1392 iby_fd_payments_v ibypmts,
1393 ap_selected_invoices_all new,
1394 iby_fd_docs_payable_v ibydocs,
1395 fnd_currencies fcinv
1396 /* Bug 6950891 . Added TO_CHAR */
1397 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
1398 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
1399 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
1400 AND new.original_invoice_id = orig.invoice_id
1401 and ibypmts.org_type = 'OPERATING_UNIT'
1402 AND ibypmts.payment_id = ibydocs.payment_id
1403 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1404 AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
1405 AND fcinv.currency_code = orig.invoice_currency_code
1406 AND ibypmts.org_id = l_current_org_id;
1407
1408
1409 l_interest_invoice_count := sql%rowcount;
1410
1411 l_debug_info := 'do interest invoice line insertions';
1412 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1413 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1414 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1415 END IF;
1416
1417
1418 INSERT INTO ap_invoice_lines_all(
1419 INVOICE_ID,
1420 LINE_NUMBER,
1421 LINE_TYPE_LOOKUP_CODE,
1422 LAST_UPDATE_DATE,
1423 LAST_UPDATED_BY,
1424 CREATION_DATE,
1425 CREATED_BY,
1426 ACCOUNTING_DATE,
1427 PERIOD_NAME,
1428 AMOUNT,
1429 BASE_AMOUNT,
1430 DESCRIPTION,
1431 TYPE_1099,
1432 SET_OF_BOOKS_ID,
1433 ASSETS_TRACKING_FLAG,
1434 -- ASSET_BOOK_TYPE_CODE, ??
1435 -- ASSET_CATEGORY_ID, ??
1436 LINE_SOURCE,
1437 GENERATE_DISTS,
1438 WFAPPROVAL_STATUS,
1439 org_id)
1440 SELECT new.invoice_id,
1441 1,
1442 'ITEM',
1443 SYSDATE,
1444 l_last_updated_by,
1445 sysdate,
1446 l_last_updated_by,
1447 new.due_date,
1448 l_period_name,
1449 decode(fcinv.minimum_accountable_unit, null,
1450 round((ibydocs.payment_amount/orig.payment_cross_rate),
1451 fcinv.precision),
1452 round((ibydocs.payment_amount/orig.payment_cross_rate)
1453 /fcinv.minimum_accountable_unit)
1454 * fcinv.minimum_accountable_unit),
1455 decode(orig.invoice_currency_code, l_base_currency_code,
1456 NULL,
1457 decode(l_base_currency_mac, null,
1458 round(new.payment_amount / orig.payment_cross_rate *
1459 --bug 8899917 take exchange rate from selected inv
1460 nvl(new.payment_exchange_rate,1), l_base_currency_precision),
1461 round( (new.payment_amount / orig.payment_cross_rate *
1462 nvl(new.payment_exchange_rate,1)) /
1463 l_base_currency_mac) *
1464 l_base_currency_mac ) ),
1465 new.invoice_description||orig.invoice_num,
1466 pv.type_1099,
1467 l_set_of_books_id,
1468 l_int_asset_tracking_flag,
1469 'AUTO INVOICE CREATION',
1470 'N',
1471 'NOT REQUIRED',
1472 l_current_org_id
1473 FROM
1474 po_vendors pv,
1475 ap_invoices_all orig,
1476 iby_fd_payments_v ibypmts,
1477 iby_fd_docs_payable_v ibydocs,
1478 ap_selected_invoices_all new, -- Modified for bug 8744658
1479 fnd_currencies fcinv
1480 /* Bug 6950891 Added TO_CHAR */
1481 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
1482 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
1483 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
1484 AND ibypmts.payment_id = ibydocs.payment_id
1485 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1486 AND ibypmts.org_id = l_current_org_id
1487 and ibypmts.org_type = 'OPERATING_UNIT'
1488 AND new.original_invoice_id = orig.invoice_id
1489 AND new.vendor_id = pv.vendor_id
1490 AND new.checkrun_name = l_checkrun_name
1491 AND fcinv.currency_code = orig.invoice_currency_code;
1492
1493
1494 l_debug_info := 'do ap_create_batch_interest_dists';
1495 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1496 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1497 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1498 END IF;
1499
1500 /* Bug 5124784. Distribution for interest invoice will be created via
1501 interest invoice package. */
1502
1503 AP_INTEREST_INVOICE_PKG.ap_create_batch_interest_dists(
1504 P_checkrun_name => l_checkrun_name,
1505 P_base_currency_code => l_base_currency_code,
1506 P_interest_accts_pay_ccid => l_interest_accts_pay_ccid,
1507 P_last_updated_by => l_last_updated_by,
1508 P_period_name => l_period_name,
1509 P_asset_account_flag => l_int_asset_tracking_flag,
1510 P_calling_sequence => l_current_calling_sequence,
1511 p_checkrun_id => l_checkrun_id,
1512 p_completed_pmts_group_id => p_completed_pmts_group_id,
1513 p_org_id => l_current_org_id);
1514
1515
1516
1517 l_debug_info := 'INSERT INTO ap_payment_schedules_all';
1518 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1519 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1520 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1521 END IF;
1522
1523 INSERT INTO ap_payment_schedules_all(
1524 invoice_id,
1525 payment_num,
1526 last_update_date,
1527 last_updated_by,
1528 due_date,
1529 gross_amount,
1530 discount_amount_available,
1531 amount_remaining,
1532 discount_amount_remaining,
1533 payment_priority,
1534 payment_status_flag,
1535 batch_id,
1536 payment_cross_rate,
1537 creation_date,
1538 created_by,
1539 payment_method_code,
1540 inv_curr_gross_amount,
1541 org_id,
1542 -- added below columns for 7673570
1543 remit_to_supplier_name,
1544 remit_to_supplier_id,
1545 remit_to_supplier_site,
1546 remit_to_supplier_site_id,
1547 relationship_id)
1548 SELECT
1549 new.invoice_id,
1550 1,
1551 SYSDATE,
1552 l_last_updated_by,
1553 new.due_date,
1554 ibydocs.payment_amount,
1555 0,
1556 0,
1557 0,
1558 new.payment_priority,
1559 'Y',
1560 l_int_batch_id,
1561 orig.payment_cross_rate,
1562 SYSDATE,
1563 l_last_updated_by,
1564 orig.payment_method_code,
1565 decode(fcinv.minimum_accountable_unit, null,
1566 round((new.payment_amount/orig.payment_cross_rate),
1567 fcinv.precision),
1568 round((new.payment_amount/orig.payment_cross_rate)
1569 /fcinv.minimum_accountable_unit)
1570 * fcinv.minimum_accountable_unit),
1571 l_current_org_id,
1572 -- added below columns for 7673570
1573 ibypmts.PAYEE_NAME,
1574 NVL(aps.vendor_id, -222), --Modified for bug 8405513
1575 aps.vendor_site_code,
1576 NVL(ibypmts.supplier_site_id, -222), --modifed for bug 8405513
1577 ibypmts.relationship_id
1578 FROM ap_invoices_all orig,
1579 ap_selected_invoices_all new,
1580 ap_supplier_sites_all aps, -- bug 7673570
1581 fnd_currencies fcinv,
1582 iby_fd_payments_v ibypmts,
1583 iby_fd_docs_payable_v ibydocs
1584 /* Bug 6950891 Added TO_CHAR */
1585 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
1586 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
1587 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
1588 AND ibypmts.payment_id = ibydocs.payment_id
1589 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1590 and ibypmts.org_type = 'OPERATING_UNIT'
1591 AND ibypmts.org_id = l_current_org_id
1592 AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
1593 AND new.original_invoice_id = orig.invoice_id
1594 AND new.checkrun_name = l_checkrun_name
1595 AND fcinv.currency_code = orig.invoice_currency_code;
1596
1597
1598
1599 l_debug_info := 'INSERT INTO ap_invoice_relationships';
1600 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1601 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1602 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1603 END IF;
1604
1605 INSERT INTO ap_invoice_relationships(
1606 original_invoice_id,
1607 related_invoice_id,
1608 created_by,
1609 creation_date,
1610 original_payment_num,
1611 last_updated_by,
1612 last_update_date,
1613 checkrun_name)
1614 SELECT orig.invoice_id,
1615 new.invoice_id,
1616 l_last_updated_by,
1617 SYSDATE,
1618 new.original_payment_num,
1619 l_last_updated_by,
1620 SYSDATE,
1621 l_checkrun_name
1622 FROM ap_invoices_all orig,
1623 ap_selected_invoices_all new,
1624 iby_fd_payments_v ibypmts,
1625 iby_fd_docs_payable_v ibydocs
1626 /* Bug 6950891 Added TO_CHAR */
1627 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
1628 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
1629 and ibypmts.org_type = 'OPERATING_UNIT'
1630 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
1631 AND ibypmts.payment_id = ibydocs.payment_id
1632 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1633 AND ibypmts.org_id = l_current_org_id
1634 AND new.original_invoice_id = orig.invoice_id
1635 AND new.checkrun_name = l_checkrun_name;
1636
1637
1638
1639 IF l_batch_control_flag = 'Y' THEN
1640
1641 l_debug_info := 'INSERT INTO ap_batches_all';
1642 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1643 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1644 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1645 END IF;
1646
1647 INSERT INTO ap_batches_all(
1648 batch_id,
1649 batch_name,
1650 batch_date,
1651 last_update_date,
1652 last_updated_by,
1653 control_invoice_count,
1654 actual_invoice_count,
1655 creation_date,
1656 created_by,
1657 org_id) --4945922
1658 SELECT l_int_batch_id,
1659 substrb(LC.displayed_field||l_checkrun_name, 1,50),
1660 SYSDATE,
1661 SYSDATE,
1662 l_last_updated_by,
1663 count(*),
1664 count(*),
1665 SYSDATE,
1666 l_last_updated_by,
1667 i.org_id
1668 FROM ap_invoices_all I,
1669 ap_lookup_codes LC
1670 WHERE I.batch_id= l_int_batch_id
1671 AND LC.lookup_type = 'NLS TRANSLATION' /* bug 9868737 */
1672 AND LC.lookup_code = 'INTEREST ON PAYMENTBATCH' /* bug 9868737 */
1673 GROUP BY l_int_batch_id, l_checkrun_name,
1674 LC.displayed_field, SYSDATE, l_last_updated_by, i.org_id;
1675
1676 END if;
1677
1678
1679
1680 END if; --if auto_calculate_interest_flag = 'Y'
1681
1682
1683 /*bug8224330, transported the insert into ap_checks_all here, after the insertion of interest invoices related data
1684 into ap tables*/
1685
1686 l_debug_info := 'insert into ap_checks_all';
1687 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1688 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1689 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1690 END IF;
1691
1692 INSERT INTO ap_checks_all
1693 (CHECK_ID,
1694 -- Bug 6845440 commented below field
1695 -- BANK_ACCOUNT_ID,
1696 CE_BANK_ACCT_USE_ID,
1697 BANK_ACCOUNT_NAME,
1698 AMOUNT,
1699 CHECK_NUMBER,
1700 CHECK_DATE,
1701 CURRENCY_CODE,
1702 LAST_UPDATE_DATE,
1703 LAST_UPDATED_BY,
1704 VENDOR_ID,
1705 VENDOR_NAME,
1706 VENDOR_SITE_ID,
1707 STATUS_LOOKUP_CODE,
1708 CHECKRUN_ID,
1709 CHECKRUN_NAME,
1710 ADDRESS_LINE1,
1711 ADDRESS_LINE2,
1712 ADDRESS_LINE3,
1713 ADDRESS_LINE4,
1714 CITY,
1715 STATE,
1716 ZIP,
1717 PROVINCE,
1718 COUNTRY,
1719 -- VENDOR_SITE_CODE,
1720 BANK_ACCOUNT_NUM,
1721 -- IBAN_NUMBER,
1722 BANK_NUM,
1723 BANK_ACCOUNT_TYPE,
1724 EXTERNAL_BANK_ACCOUNT_ID,
1725 TRANSFER_PRIORITY,
1726 PAYMENT_TYPE_FLAG,
1727 CREATION_DATE,
1728 CREATED_BY,
1729 PAYMENT_METHOD_code,
1730 EXCHANGE_RATE,
1731 EXCHANGE_RATE_TYPE,
1732 EXCHANGE_DATE,
1733 BASE_AMOUNT,
1734 FUTURE_PAY_DUE_DATE,
1735 MATURITY_EXCHANGE_DATE,
1736 MATURITY_EXCHANGE_RATE_TYPE,
1737 MATURITY_EXCHANGE_RATE,
1738 ANTICIPATED_VALUE_DATE,
1739 LEGAL_ENTITY_ID,
1740 ORG_ID,
1741 PAYMENT_ID,
1742 COMPLETED_PMTS_GROUP_ID,
1743 PAYMENT_PROFILE_ID, --SO WE CAN REFER BACK TO IBY
1744 PARTY_ID,
1745 PARTY_SITE_ID,
1746 PAYMENT_DOCUMENT_ID, --4752808
1747 PAYMENT_INSTRUCTION_ID, --4884849
1748 -- added below columns for 7673570
1749 REMIT_TO_SUPPLIER_NAME,
1750 REMIT_TO_SUPPLIER_ID,
1751 REMIT_TO_SUPPLIER_SITE,
1752 REMIT_TO_SUPPLIER_SITE_ID,
1753 RELATIONSHIP_ID)
1754 SELECT
1755 ap_checks_s.nextval,
1756 -- Bug 6845440 commented below field
1757 -- ce.bank_account_id,
1758 ce.bank_acct_use_id,
1759 substr(ceb.bank_account_name,1,80),
1760 iby.payment_amount,
1761 nvl(iby.paper_document_number, iby.payment_reference_number),
1762 iby.payment_date,
1763 iby.payment_currency_code,
1764 SYSDATE,
1765 l_last_updated_by,
1766 pv.vendor_id,
1767 nvl(pv.vendor_name, iby.payee_name), /* Added the nvl for bug#9976033 */
1768 iby.inv_supplier_site_id, -- 7673570
1769 decode(iby.maturity_date,null,'NEGOTIABLE','ISSUED'),
1770 l_checkrun_id,
1771 l_checkrun_name,
1772 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address1), /* Bug10061011 */
1773 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address2), /* Bug10061011 */
1774 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address3), /* Bug10061011 */
1775 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address4), /* Bug10061011 */
1776 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_City), /* Bug10061011 */
1777 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_State), /* Bug10061011 */
1778 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Postal_Code),/* Bug10061011 */
1779 Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Province),/* Bug10061011 */
1780 DECODE(pv.vendor_type_lookup_code,'EMPLOYEE',null,iby.payee_country),/* Bug10061011 */
1781 -- sc.vendor_site_code,
1782 iby.ext_bank_account_number,
1783 -- SC.iban_number, --need FROM iby
1784 -- iby.ext_bank_account_name, -- Bug 5090441
1785 iby.EXT_BANK_NUMBER,
1786 iby.ext_bank_account_type,
1787 iby.external_bank_account_id,
1788 l_transfer_priority,
1789 'A',
1790 sysdate,
1791 l_last_updated_by,
1792 iby.payment_method_code,
1793 decode(iby.payment_currency_code, l_base_currency_code,
1794 null,
1795 decode(l_exchange_rate_type, 'User',
1796 ap_pmt_callout_pkg.get_user_rate(
1797 l_base_currency_code,
1798 iby.payment_currency_code,
1799 l_checkrun_id),
1800 ap_utilities_pkg.get_exchange_rate(
1801 iby.payment_currency_code,
1802 l_base_currency_code,
1803 l_exchange_rate_type,
1804 l_check_date,
1805 'CONFIRM'))),
1806 decode (iby.payment_currency_code, l_base_currency_code,null,l_exchange_rate_type), /* Added Decode for bug13560190 */
1807 l_check_date, --exchange rate date
1808 decode(iby.payment_currency_code, l_base_currency_code,
1809 null,
1810 ap_pmt_callout_pkg.get_base_amount(l_base_currency_code,
1811 iby.payment_currency_code,
1812 l_checkrun_id,
1813 l_exchange_rate_type,
1814 l_base_currency_mac,
1815 iby.payment_amount,
1816 l_base_currency_precision,
1817 l_check_date)),
1818 iby.maturity_date,
1819 decode(iby.payment_currency_code, l_base_currency_code,
1820 null,
1821 decode(l_exchange_rate_type, 'User', l_check_date,
1822 iby.maturity_date)),
1823 decode (iby.payment_currency_code, l_base_currency_code,null,l_exchange_rate_type), /* Added Decode for bug13560190 */
1824 decode(iby.payment_currency_code, l_base_currency_code, NULL,
1825 decode(l_exchange_rate_type, 'User',
1826 ap_pmt_callout_pkg.get_user_rate(l_base_currency_code,
1827 iby.payment_currency_code,
1828 l_checkrun_id),
1829 ap_utilities_pkg.get_exchange_rate(
1830 iby.payment_currency_code,
1831 l_base_currency_code,
1832 l_exchange_rate_type,
1833 iby.maturity_date,
1834 'CONFIRM'))),
1835 iby.anticipated_value_date,
1836 iby.legal_entity_id,
1837 iby.org_id,
1838 iby.payment_id,
1839 iby.completed_pmts_group_id,
1840 iby.payment_profile_id,
1841 iby.inv_payee_party_id, -- 7673570 iby.payee_party_id,
1842 iby.inv_party_site_id, -- 7673570 iby.party_site_id,
1843 iby.payment_document_id,
1844 iby.payment_instruction_id,
1845 -- added below columns for 7673570
1846 iby.PAYEE_NAME,
1847 NVL(aps.vendor_id, -222), -- modifed for bug 8405513
1848 aps.vendor_site_code,
1849 NVL(iby.supplier_site_id, -222), --modifed for bug 8405513
1850 iby.relationship_id
1851 FROM iby_fd_payments_v iby,
1852 po_vendors pv,
1853 ce_bank_acct_uses_all ce,
1854 ce_bank_accounts ceb,
1855 ap_supplier_sites_all aps -- 7673570
1856 WHERE iby.inv_payee_party_id = pv.party_id(+) -- 7673570
1857 -- iby.payee_party_id = pv.party_id(+) -- 7673570
1858 AND aps.vendor_site_id(+) = iby.supplier_site_id -- 7673570 --modifed for bug 8405513 to handle Payment Request
1859 --AND pv.end_date_active IS NULL -- bug7166247
1860 -- commented the above condition and added the below condition for bug 8401306
1861 /* AND trunc(nvl(pv.end_date_active,sysdate)) >= trunc(sysdate) Commented for bug#8773583 */
1862 AND nvl(pv.vendor_id,-99) = (select CASE
1863 WHEN inv.invoice_type_lookup_code = 'PAYMENT REQUEST' AND SIGN(inv.vendor_id)= -1
1864 THEN nvl(pv.vendor_id,-99) --bug 8657535. Changed -99 to nvl(pv.vendor_id,-99)
1865 ELSE nvl(vendor_id,-99)
1866 END --Bug7493630 and Bug 8260736 (8348480)
1867 from ap_invoices_all inv,iby_docs_payable_all idp
1868 where inv.invoice_id=idp.calling_app_doc_unique_ref2
1869 and idp.payment_id=iby.payment_id
1870 and idp.calling_app_doc_unique_ref1=l_checkrun_id
1871 and rownum=1
1872 ) --7196023
1873 AND ce.bank_account_id = iby.internal_bank_account_id
1874 and ceb.bank_account_id = ce.bank_account_id
1875 and iby.org_type = 'OPERATING_UNIT'
1876 AND ce.org_id = l_current_org_id
1877 AND iby.org_id = l_current_org_id
1878 AND iby.completed_pmts_group_id = p_completed_pmts_group_id
1879 -- Bug 6752984
1880 AND iby.payment_service_request_id =
1881 (SELECT payment_service_request_id
1882 FROM IBY_PAY_SERVICE_REQUESTS
1883 WHERE call_app_pay_service_req_code = l_checkrun_name
1884 AND CALLING_APP_ID = 200);
1885
1886 --Bug 5115310
1887 l_check_count := SQL%ROWCOUNT;
1888 l_total_check_count := l_check_count + l_total_check_count;
1889
1890 l_debug_info := 'l_check_count: '||to_char(l_check_count);
1891 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1892
1893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1894 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1895 END IF;
1896
1897 l_debug_info := 'l_total_check_count: '||to_char(l_total_check_count);
1898 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1899
1900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1901 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1902 END IF;
1903
1904 l_debug_info := 'l_current_org_id: '||to_char(l_current_org_id);
1905 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1906 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1907 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1908 END IF;
1909
1910
1911 l_debug_info := 'create accounting';
1912 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1913 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1914 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1915 END IF;
1916
1917 AP_ACCOUNTING_EVENTS_PKG.create_payment_batch_events(
1918 p_checkrun_name => l_checkrun_name,
1919 p_completed_pmts_group_id => p_completed_pmts_group_id,
1920 p_accounting_date => l_check_date,
1921 p_org_id => l_current_org_id,
1922 p_set_of_books_id => l_set_of_books_id,
1923 p_calling_sequence => l_current_calling_sequence);
1924
1925
1926 IF l_when_to_account_payment = 'CLEARING ONLY' THEN
1927
1928 l_debug_info := 'AP_ACCOUNTING_EVENTS_PKG.update_pmt_batch_event_status';
1929 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1930 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1932 END IF;
1933
1934 AP_ACCOUNTING_EVENTS_PKG.update_pmt_batch_event_status(
1935 p_checkrun_name => l_checkrun_name,
1936 p_org_id => l_current_org_id,
1937 p_completed_pmts_group_id => p_completed_pmts_group_id,
1938 p_calling_sequence => l_current_calling_sequence);
1939
1940 END IF;
1941
1942 --bug8224330
1943
1944 l_debug_info := 'UPDATE ap_selected_invoices_all';
1945 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1946 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1947 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1948 END IF;
1949
1950 UPDATE ap_selected_invoices_all ASI
1951 SET ASI.invoice_payment_id = ap_invoice_payments_s.nextval
1952 WHERE ASI.checkrun_id = l_checkrun_id
1953 /* Bug 6950891. Added TO_CHAR */
1954 AND (TO_CHAR(ASI.invoice_id), TO_CHAR(ASI.payment_num)) in
1955 (select calling_app_doc_unique_ref2, calling_app_doc_unique_ref3
1956 FROM iby_fd_docs_payable_v ibydocs,
1957 iby_fd_payments_v ibypmts
1958 WHERE ibydocs.payment_id = ibypmts.payment_id
1959 and ibypmts.org_type = 'OPERATING_UNIT'
1960 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1961 AND ibypmts.org_id = l_current_org_id);
1962
1963
1964 IF l_perform_awt_flag = 'Y' THEN
1965
1966 l_debug_info := ' UPDATE ap_awt_temp_distributions_all';
1967 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1968 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1969 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1970 END IF;
1971
1972 UPDATE ap_awt_temp_distributions_all AATD
1973 SET AATD.invoice_payment_id =
1974 (SELECT ASI.invoice_payment_id
1975 FROM ap_selected_invoices_all ASI
1976 WHERE ASI.checkrun_id = AATD.checkrun_id
1977 AND ASI.invoice_id = AATD.invoice_id
1978 AND ASI.payment_num = AATD.payment_num
1979 AND asi.org_id = l_current_org_id)
1980 WHERE AATD.checkrun_id = l_checkrun_id
1981 AND aatd.org_id = l_current_org_id
1982 /* Bug 6950891. Added TO_CHAR */
1983 AND (TO_CHAR(AATD.invoice_id), TO_CHAR(AATD.payment_num)) in
1984 /* Bug 5383066, calling_app_doc_unique_ref3 should be used for payment_num*/
1985 (select calling_app_doc_unique_ref2, calling_app_doc_unique_ref3
1986 FROM iby_fd_docs_payable_v ibydocs,
1987 iby_fd_payments_v ibypmts
1988 WHERE ibydocs.payment_id = ibypmts.payment_id
1989 and ibypmts.org_type = 'OPERATING_UNIT'
1990 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
1991 AND ibypmts.org_id = l_current_org_id);
1992
1993
1994 l_debug_info := 'AP_WITHHOLDING_PKG.AP_WITHHOLD_CONFIRM';
1995 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
1996 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1997 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1998 END IF;
1999 AP_WITHHOLDING_PKG.AP_WITHHOLD_CONFIRM(l_checkrun_name,
2000 l_last_updated_by,
2001 --4863216
2002 to_number(FND_PROFILE.VALUE('LOGIN_ID')),
2003 to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID')),
2004 to_number(FND_PROFILE.VALUE('PROGRAM_ID')),
2005 to_number(FND_PROFILE.VALUE('REQUEST_ID')),
2006 l_checkrun_id,
2007 p_completed_pmts_group_id,
2008 l_current_org_id,
2009 l_check_date);
2010
2011 l_debug_info := 'DELETE FROM ap_awt_temp_distributions_all';
2012 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2013 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2014 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2015 END IF;
2016
2017 /* Bug 5383066. Foloowing Delete is not requeired. As call to
2018 AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL cleans up temporary withholding dists.
2019
2020 WHERE checkrun_name = l_checkrun_name
2021 AND org_id = l_current_org_id
2022 and checkrun_id = l_checkrun_id
2023 and (invoice_id, payment_num) in
2024 (select calling_app_doc_unique_ref2, calling_app_doc_unique_ref2
2025 FROM iby_fd_docs_payable_v ibydocs
2026 where calling_app_doc_unique_ref1 = l_checkrun_id
2027 and completed_pmts_group_id = p_completed_pmts_group_id
2028 and org_id = l_current_org_id);
2029 */
2030
2031 END IF;
2032
2033 OPEN c_schedule_amounts(l_last_updated_by --Bug5733731
2034 ,p_completed_pmts_group_id
2035 ,l_current_org_id
2036 ,l_checkrun_name);
2037
2038 LOOP
2039 FETCH c_schedule_amounts
2040 BULK COLLECT INTO
2041 last_update_date_ps_l,
2042 last_updated_by_ps_l,
2043 amount_remaining_ps_l,
2044 discount_remaining_ps_l,
2045 payment_status_ps_l,
2046 --awt_num_ps_l, --Bug8477014
2047 awt_num_ps_l, --Bug8752557
2048 checkrun_id_ps_l,
2049 invoice_id_ps_l,
2050 payment_num_ps_l
2051 LIMIT 1000;
2052
2053 l_debug_info := 'UPDATE ap_payment_schedules_all';
2054 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2055 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2056 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2057 END IF;
2058
2059
2060 FORALL i IN 1..invoice_id_ps_l.COUNT
2061
2062 UPDATE ap_payment_schedules_all
2063 SET last_update_date = last_update_date_ps_l(i)
2064 ,last_updated_by = last_updated_by_ps_l(i)
2065 -- Modified by epajaril, need to consider the AWT
2066 --Bug8477014: Undoing changes done for bug6836199
2067 --Bug8752557: Undoing changes done here for bug8477014
2068 --,amount_remaining = amount_remaining_ps_l(i)
2069 ,amount_remaining = (amount_remaining_ps_l(i) -
2070 nvl(awt_num_ps_l(i),0)) --bug:7523065 --Bug8752557
2071 ,discount_amount_remaining = discount_remaining_ps_l(i)
2072 --,payment_status_flag = payment_status_ps_l(i)
2073 , payment_status_flag = DECODE((amount_remaining_ps_l(i)-nvl(awt_num_ps_l(i),0)), 0,'Y', 'P')--Bug8759364
2074 ,checkrun_id = checkrun_id_ps_l(i)
2075 WHERE invoice_id = invoice_id_ps_l(i)
2076 AND payment_num = payment_num_ps_l(i);
2077
2078 EXIT WHEN c_schedule_amounts%NOTFOUND;
2079 END LOOP;
2080
2081 CLOSE c_schedule_amounts;
2082
2083 -- Bug 5658623. Cursor Processing for Performance
2084 l_debug_info := 'Opening Cursor for updating invoices via Forall';
2085 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2086 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2088 END IF;
2089
2090 OPEN c_invoice_amounts(l_last_updated_by
2091 ,p_completed_pmts_group_id
2092 ,l_current_org_id
2093 ,l_checkrun_name);
2094
2095
2096 LOOP
2097 FETCH c_invoice_amounts
2098 BULK COLLECT INTO
2099 last_update_date_inv_l,
2100 last_updated_by_inv_l,
2101 amount_paid_inv_l,
2102 discount_taken_inv_l,
2103 payment_status_inv_l,
2104 invoice_id_inv_l
2105 LIMIT 1000;
2106
2107 l_debug_info := 'UPDATE ap_invoices_all';
2108 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2109 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2110 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2111 END IF;
2112
2113 FORALL i IN 1..invoice_id_inv_l.COUNT
2114 UPDATE ap_invoices_all
2115 SET last_update_date = last_update_date_inv_l(i)
2116 ,last_updated_by = last_updated_by_inv_l(i)
2117 ,amount_paid = nvl(amount_paid,0) + amount_paid_inv_l(i)
2118 ,discount_amount_taken = nvl(discount_amount_taken,0) + discount_taken_inv_l(i)
2119 ,payment_status_flag = payment_status_inv_l(i)
2120 WHERE invoice_id = invoice_id_inv_l(i);
2121
2122 EXIT WHEN c_invoice_amounts%NOTFOUND;
2123 END LOOP;
2124
2125 CLOSE c_invoice_amounts; --Bug5733731
2126
2127
2128
2129 l_debug_info := 'insert into ap_invoice_payments_all';
2130 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2132 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2133 END IF;
2134
2135 INSERT INTO ap_invoice_payments_all(
2136 INVOICE_PAYMENT_ID,
2137 INVOICE_ID,
2138 PAYMENT_NUM,
2139 CHECK_ID,
2140 AMOUNT,
2141 LAST_UPDATE_DATE,
2142 LAST_UPDATED_BY,
2143 ELECTRONIC_TRANSFER_ID,
2144 SET_OF_BOOKS_ID,
2145 ACCTS_PAY_CODE_COMBINATION_ID,
2146 ACCOUNTING_DATE,
2147 PERIOD_NAME,
2148 POSTED_FLAG,
2149 ACCRUAL_POSTED_FLAG,
2150 CASH_POSTED_FLAG,
2151 DISCOUNT_TAKEN,
2152 DISCOUNT_LOST,
2153 EXCHANGE_RATE,
2154 EXCHANGE_RATE_TYPE,
2155 GAIN_CODE_COMBINATION_ID,
2156 LOSS_CODE_COMBINATION_ID,
2157 ASSET_CODE_COMBINATION_ID,
2158 INVOICE_BASE_AMOUNT,
2159 PAYMENT_BASE_AMOUNT,
2160 EXCHANGE_DATE,
2161 BANK_ACCOUNT_NUM,
2162 -- IBAN_NUMBER, --Bug 2633878
2163 BANK_NUM,
2164 BANK_ACCOUNT_TYPE,
2165 EXTERNAL_BANK_ACCOUNT_ID,
2166 ATTRIBUTE1,
2167 ATTRIBUTE2,
2168 ATTRIBUTE3,
2169 ATTRIBUTE4,
2170 ATTRIBUTE5,
2171 ATTRIBUTE6,
2172 ATTRIBUTE7,
2173 ATTRIBUTE8,
2174 ATTRIBUTE9,
2175 ATTRIBUTE10,
2176 ATTRIBUTE11,
2177 ATTRIBUTE12,
2178 ATTRIBUTE13,
2179 ATTRIBUTE14,
2180 ATTRIBUTE15,
2181 ATTRIBUTE_CATEGORY, -- Bug 4087878
2182 FUTURE_PAY_CODE_COMBINATION_ID,
2183 FUTURE_PAY_POSTED_FLAG,
2184 ACCOUNTING_EVENT_ID,
2185 CREATION_DATE,
2186 CREATED_BY,
2187 ORG_ID, --4945922
2188 INVOICING_PARTY_ID, /*4739343, added 3rd party columns*/
2189 INVOICING_PARTY_SITE_ID,
2190 INVOICING_VENDOR_SITE_ID, -- Bug 5658623
2191 -- added below columns for 7673570
2192 REMIT_TO_SUPPLIER_NAME,
2193 REMIT_TO_SUPPLIER_ID,
2194 REMIT_TO_SUPPLIER_SITE,
2195 REMIT_TO_SUPPLIER_SITE_ID,
2196 assets_addition_flag) -- bug 8741899: add
2197 SELECT /*hint added for bug14009966*/
2198 /*+ Leading( xeg AC IBYPMTS FORE CEGL IBYDOCS SI ) index( ac ap_checks_u1 ) use_nl(IBYDOCS SI ) index(SI AP_SELECTED_INVOICES_N5) */
2199 SI.invoice_payment_id,
2200 SI.invoice_id,
2201 SI.payment_num,
2202 ac.check_id,
2203 ibydocs.payment_amount,
2204 sysdate,
2205 l_last_updated_by,
2206 NULL,
2207 l_set_of_books_id,
2208 null,
2209 trunc(l_check_date), --bug6602676
2210 l_period_name,
2211 'N',
2212 'N',
2213 'N',
2214 DECODE(ibydocs.payment_curr_discount_taken,0,'',ibydocs.payment_curr_discount_taken),
2215 DECODE(ps.invoice_id, null, 0,
2216 DECODE(ps.gross_amount, 0, 0,
2217 (DECODE(FORE.minimum_accountable_unit,NULL,
2218 ROUND((((ibydocs.payment_amount+ibydocs.payment_curr_discount_taken)/
2219 DECODE(ps.gross_amount,0,1,ps.gross_amount)) *
2220 (greatest (nvl(PS.discount_amount_available,0),
2221 nvl(PS.second_disc_amt_available,0),
2222 nvl(PS.third_disc_amt_available,0)))),
2223 FORE.precision),
2224 ROUND((((ibydocs.payment_amount+ibydocs.payment_curr_discount_taken)/
2225 DECODE(ps.gross_amount,0,1,ps.gross_amount)) *
2226 (greatest (nvl(PS.discount_amount_available,0),
2227 nvl(PS.second_disc_amt_available,0),
2228 nvl(PS.third_disc_amt_available,0))))
2229 / FORE.minimum_accountable_unit)
2230 * FORE.minimum_accountable_unit)
2231 - ibydocs.payment_curr_discount_taken))),
2232 ac.exchange_rate,
2233 ac.exchange_rate_type,
2234 decode(ibydocs.payment_currency_code, l_base_currency_code,null,cegl.gain_code_combination_id),
2235 decode(ibydocs.payment_currency_code, l_base_currency_code,null,cegl.loss_code_combination_id),
2236 cegl.ap_asset_ccid,
2237 decode(AI.invoice_currency_code, l_base_currency_code,
2238 decode(ibydocs.payment_currency_code, l_base_currency_code,
2239 null,
2240 decode(gl_currency_api.convert_amount_sql(
2241 ibydocs.payment_currency_code,
2242 l_base_currency_code,
2243 AI.payment_cross_rate_date,
2244 AI.payment_cross_rate_type,
2245 abs(ibydocs.payment_amount)),
2246 -1, null, -2, null,
2247 -1, null, -2, null,
2248 gl_currency_api.convert_amount_sql(
2249 ibydocs.payment_currency_code,
2250 l_base_currency_code,
2251 AI.payment_cross_rate_date,
2252 AI.payment_cross_rate_type,
2253 ibydocs.payment_amount))),
2254 decode(SI.invoice_exchange_rate, null,
2255 null,
2256 decode(l_base_currency_mac,NULL,
2257 ROUND((ibydocs.payment_amount * SI.invoice_exchange_rate)
2258 / SI.payment_cross_rate, l_base_currency_precision),
2259 ROUND(((ibydocs.payment_amount * SI.invoice_exchange_rate)
2260 / SI.payment_cross_rate)
2261 / l_base_currency_mac)
2262 * l_base_currency_mac))), --invoice_base_amount
2263 decode(ibydocs.payment_currency_code, l_base_currency_code,
2264 decode(AI.invoice_currency_code, l_base_currency_code,
2265 null,
2266 ibydocs.payment_amount),
2267 --bug 8899917 take ex rate from check
2268 decode(ac.exchange_rate, NULL, NULL,
2269 decode(l_base_currency_mac, NULL,
2270 ROUND((ibydocs.payment_amount * ac.exchange_rate)
2271 ,l_base_currency_precision),
2272 ROUND((ibydocs.payment_amount * ac.exchange_rate)
2273 / l_base_currency_mac)
2274 * l_base_currency_mac))), -- payment_base_amount
2275 l_check_date,
2276 SI.bank_account_num,
2277 -- SI.iban_number,
2278 SI.bank_num,
2279 SI.bank_account_type,
2280 SI.external_bank_account_id,
2281 SI.attribute1,
2282 SI.attribute2,
2283 SI.attribute3,
2284 SI.attribute4,
2285 SI.attribute5,
2286 SI.attribute6,
2287 SI.attribute7,
2288 SI.attribute8,
2289 SI.attribute9,
2290 SI.attribute10,
2291 SI.attribute11,
2292 SI.attribute12,
2293 SI.attribute13,
2294 SI.attribute14,
2295 SI.attribute15,
2296 SI.attribute_category,
2297 cegl.future_dated_payment_ccid,
2298 'N',
2299 XEG.event_id,
2300 sysdate,
2301 l_last_updated_by,
2302 ai.org_id, --4945922
2303 ibydocs.beneficiary_party,
2304 decode(ibydocs.beneficiary_party, null, null, ai.party_site_id),
2305 decode(ibydocs.beneficiary_party, null, null, ai.vendor_site_id),
2306 -- added below columns for 7673570
2307 /* Bug 9074840 replaced following with ap_checks_all values
2308 ibypmts.PAYEE_NAME,
2309 NVL(aps.vendor_id, -222), --modifed for bug 8405513
2310 aps.vendor_site_code,
2311 NVL(ibypmts.supplier_site_id, -222), --modifed for bug 8405513
2312 */
2313 ac.remit_to_supplier_name,
2314 ac.remit_to_supplier_id,
2315 ac.remit_to_supplier_site,
2316 ac.remit_to_supplier_site_id,
2317 'U' -- bug 8741899: add
2318 FROM
2319 iby_fd_payments_v ibypmts,
2320 iby_fd_docs_payable_v ibydocs,
2321 ap_selected_invoices_all SI,
2322 fnd_currencies FORE,
2323 ap_payment_schedules_all PS,
2324 ap_invoices_all AI,
2325 ap_checks_all ac,
2326 --ap_supplier_sites_all aps, -- bug 7673570 --Removed by bug 9074840
2327 --ce_bank_acct_uses_all ceu, --Removed by bug 9074840
2328 ce_gl_accounts_ccid cegl,
2329 XLA_EVENTS_INT_GT xeg
2330 /* Bug 6950891. Added TO_CHAR */
2331 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(si.checkrun_id)
2332 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(si.invoice_id)
2333 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(si.payment_num)
2334 AND ibypmts.payment_id = ibydocs.payment_id
2335 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
2336 AND ibypmts.org_id = l_current_org_id
2337 and ibypmts.org_type = 'OPERATING_UNIT'
2338 /* bug 9074840
2339 AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
2340 */
2341 AND SI.checkrun_name = l_checkrun_name
2342 AND ac.payment_id = ibypmts.payment_id
2343 AND ac.completed_pmts_group_id = p_completed_pmts_group_id
2344 AND ibypmts.payment_currency_code = FORE.currency_code
2345 AND PS.invoice_id(+) = SI.invoice_id
2346 AND PS.payment_num(+) = SI.payment_num
2347 AND AI.invoice_id = SI.invoice_id
2348 /* bug 9074840
2349 AND ceu.bank_account_id = ibypmts.internal_bank_account_id
2350 AND ceu.org_id = l_current_org_id
2351 */
2352 AND ac.ce_bank_acct_use_id = cegl.bank_acct_use_id --bug 9074840
2353 AND xeg.application_id = 200
2354 AND XEG.ENTITY_CODE = 'AP_PAYMENTS'
2355 AND XEG.SOURCE_ID_INT_1 = ac.check_id ;
2356
2357 --Bug 5115310
2358 l_docs_count := SQL%ROWCOUNT;
2359
2360 l_total_docs_count := l_docs_count + l_total_docs_count;
2361
2362
2363 l_debug_info := 'Call product specific subscription API for Payment Event';
2364 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2365
2366 -- Added for Payment Request
2367 OPEN c_subscribed_payments;
2368 LOOP
2369
2370 FETCH c_subscribed_payments INTO l_check_id, l_application_id;
2371 EXIT WHEN c_subscribed_payments%NOTFOUND;
2372
2373 l_debug_info := 'AP_CHECKS_PKG.Subscribe_To_Payment_Event';
2374 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2375 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2376 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2377 END IF;
2378
2379 /* Bug 9868737 Wrapped call to subscribe_to_payment_event in
2380 a block and changed error handling so that errors in
2381 procedures registered in ap_product_registrations will
2382 not stop the payments completed procedure.
2383 */
2384 BEGIN
2385 AP_CHECKS_PKG.Subscribe_To_Payment_Event(
2386 P_Event_Type => 'PAYMENT_CREATED',
2387 P_Check_ID => l_check_id,
2388 P_Application_ID => l_application_id,
2389 P_Return_Status => l_return_status,
2390 P_Msg_Count => l_msg_count,
2391 P_Msg_Data => l_msg_data,
2392 P_Calling_Sequence => l_current_calling_sequence);
2393
2394
2395 IF L_Return_Status <> 'S' THEN
2396 l_debug_info := 'Status other than success during subscribe to payment event';
2397 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2398 END IF;
2399 EXCEPTION
2400 WHEN OTHERS THEN
2401 l_debug_info := 'Error returned during subscribe to payment event';
2402 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2403 fnd_file.put_line(FND_FILE.LOG,SQLERRM);
2404 END;
2405
2406 END LOOP;
2407 CLOSE c_subscribed_payments;
2408
2409
2410 l_debug_info := 'AP_DBI_PKG.Insert_Payment_Confirm_DBI';
2411 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2412 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2413 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2414 END IF;
2415 AP_DBI_PKG.Insert_Payment_Confirm_DBI(
2416 p_checkrun_name => l_checkrun_name,
2417 p_base_currency_code => l_base_currency_code,
2418 p_key_table => 'AP_INVOICE_PAYMENTS_ALL',
2419 p_calling_sequence => l_current_calling_sequence );
2420
2421 l_debug_info := 'AP_Accounting_Events_Pkg.Batch_Update_Payment_Info';
2422 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2423 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2424 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2425 END IF;
2426 AP_Accounting_Events_Pkg.Batch_Update_Payment_Info(
2427 p_checkrun_name => l_checkrun_name,
2428 p_completed_pmts_group_id => p_completed_pmts_group_id,
2429 p_org_id => l_current_org_id,
2430 p_calling_sequence=>l_current_calling_sequence);
2431
2432 -- Bug 5658623. Cursor Processing for Performance
2433 l_debug_info := 'Opening Cursor for updating payment schedules via Forall';
2434 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2435 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2436 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2437 END IF;
2438
2439 /* OPEN c_schedule_amounts(l_last_updated_by --Bug5733731
2440 ,p_completed_pmts_group_id
2441 ,l_current_org_id
2442 ,l_checkrun_name);
2443
2444 LOOP
2445 FETCH c_schedule_amounts
2446 BULK COLLECT INTO
2447 last_update_date_ps_l,
2448 last_updated_by_ps_l,
2449 amount_remaining_ps_l,
2450 discount_remaining_ps_l,
2451 payment_status_ps_l,
2452 checkrun_id_ps_l,
2453 invoice_id_ps_l,
2454 payment_num_ps_l
2455 LIMIT 1000;
2456
2457 l_debug_info := 'UPDATE ap_payment_schedules_all';
2458 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2459 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2460 END IF;
2461
2462 FORALL i IN 1..invoice_id_ps_l.COUNT
2463 UPDATE ap_payment_schedules_all
2464 SET last_update_date = last_update_date_ps_l(i)
2465 ,last_updated_by = last_updated_by_ps_l(i)
2466 ,amount_remaining = amount_remaining_ps_l(i)
2467 ,discount_amount_remaining = discount_remaining_ps_l(i)
2468 ,payment_status_flag = payment_status_ps_l(i)
2469 ,checkrun_id = checkrun_id_ps_l(i)
2470 WHERE invoice_id = invoice_id_ps_l(i)
2471 AND payment_num = payment_num_ps_l(i);
2472
2473 EXIT WHEN c_schedule_amounts%NOTFOUND;
2474 END LOOP;
2475
2476 CLOSE c_schedule_amounts;
2477
2478 -- Bug 5658623. Cursor Processing for Performance
2479 l_debug_info := 'Opening Cursor for updating invoices via Forall';
2480 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2481 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2482 END IF;
2483
2484 OPEN c_invoice_amounts(l_last_updated_by
2485 ,p_completed_pmts_group_id
2486 ,l_current_org_id
2487 ,l_checkrun_name);
2488
2489
2490 LOOP
2491 FETCH c_invoice_amounts
2492 BULK COLLECT INTO
2493 last_update_date_inv_l,
2494 last_updated_by_inv_l,
2495 amount_paid_inv_l,
2496 discount_taken_inv_l,
2497 payment_status_inv_l,
2498 invoice_id_inv_l
2499 LIMIT 1000;
2500
2501 l_debug_info := 'UPDATE ap_invoices_all';
2502 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2503 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2504 END IF;
2505
2506 FORALL i IN 1..invoice_id_inv_l.COUNT
2507 UPDATE ap_invoices_all
2508 SET last_update_date = last_update_date_inv_l(i)
2509 ,last_updated_by = last_updated_by_inv_l(i)
2510 ,amount_paid = nvl(amount_paid,0) + amount_paid_inv_l(i)
2511 ,discount_amount_taken = nvl(discount_amount_taken,0) + discount_taken_inv_l(i)
2512 ,payment_status_flag = payment_status_inv_l(i)
2513 WHERE invoice_id = invoice_id_inv_l(i);
2514
2515 EXIT WHEN c_invoice_amounts%NOTFOUND;
2516 END LOOP;
2517
2518 CLOSE c_invoice_amounts; Bug5733731 */
2519
2520 /* UPDATE ap_payment_schedules_all ps1
2521 SET (last_update_date,
2522 last_updated_by,
2523 amount_remaining,
2524 discount_amount_remaining,
2525 payment_status_flag,
2526 checkrun_id) =
2527 (SELECT sysdate,
2528 l_last_updated_by,
2529 SI1.amount_remaining - ibydocs.payment_amount - nvl(ibydocs.payment_curr_discount_taken,0),
2530 0,
2531 decode(SI1.amount_remaining - ibydocs.payment_amount - nvl(ibydocs.payment_curr_discount_taken,0), 0,
2532 'Y', 'P'),
2533 null --set checkrun_id to null
2534 FROM ap_selected_invoices_all SI1,
2535 iby_fd_docs_payable_v ibydocs
2536 WHERE checkrun_name = l_checkrun_name
2537 AND SI1.payment_num = ps1.payment_num
2538 AND SI1.invoice_id = ps1.invoice_id
2539 AND ibydocs.calling_app_doc_unique_ref1 = to_char(si1.checkrun_id)
2540 AND ibydocs.calling_app_doc_unique_ref2 = to_char(si1.invoice_id)
2541 AND ibydocs.calling_app_doc_unique_ref3 = to_char(si1.payment_num))
2542 WHERE (ps1.invoice_id, ps1.payment_num) in
2543 (SELECT SI3.invoice_id, SI3.payment_num
2544 FROM ap_selected_invoices_all SI3,
2545 iby_fd_payments_v ibypmts,
2546 iby_fd_docs_payable_v ibydocs,
2547 ap_invoices_all AI
2548 WHERE SI3.checkrun_name = l_checkrun_name
2549 AND ibydocs.calling_app_doc_unique_ref1 = to_char(si3.checkrun_id)
2550 AND ibydocs.calling_app_doc_unique_ref2 = to_char(si3.invoice_id)
2551 AND ibydocs.calling_app_doc_unique_ref3 = to_char(si3.payment_num)
2552 AND ibypmts.payment_id = ibydocs.payment_id
2553 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
2554 AND ibypmts.org_id = l_current_org_id
2555 and ibypmts.org_type = 'OPERATING_UNIT'
2556 AND AI.invoice_id = SI3.invoice_id
2557 AND AI.invoice_type_lookup_code <> 'INTEREST');
2558
2559
2560 l_debug_info := 'UPDATE ap_invoices_all';
2561 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2562 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2563 END IF;
2564
2565
2566 --bug5020577
2567 --Modified the sql to prevent MJC.
2568 UPDATE ap_invoices_all inv1
2569 SET (last_update_date,
2570 last_updated_by,
2571 amount_paid,
2572 discount_amount_taken,
2573 payment_status_flag)=
2574 (SELECT sysdate,
2575 l_last_updated_by,
2576 nvl(inv1.amount_paid,0) + sum(ibydocs.payment_amount),
2577 nvl(inv1.discount_amount_taken,0) + nvl(sum(ibydocs.payment_curr_discount_taken),0),
2578 AP_INVOICES_UTILITY_PKG.get_payment_status( inv1.invoice_id )
2579 FROM iby_fd_docs_payable_v ibydocs,
2580 iby_fd_payments_v ibypmts
2581 WHERE ibypmts.org_type = 'OPERATING_UNIT'
2582 AND ibypmts.payment_id = ibydocs.payment_id
2583 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
2584 AND ibypmts.org_id = l_current_org_id
2585 and
2586 (ibydocs.calling_app_doc_unique_ref1,ibydocs.calling_app_doc_unique_ref2,ibydocs.calling_app_doc_unique_ref3)
2587 in (select si.checkrun_id,si.invoice_id,si.payment_num from
2588 ap_selected_invoices_all si where si.invoice_id=inv1.invoice_id
2589 and checkrun_name = l_checkrun_name)
2590 )
2591 WHERE invoice_id IN
2592 (SELECT ibydocs.calling_app_doc_unique_ref2
2593 FROM iby_fd_docs_payable_v ibydocs,
2594 iby_fd_payments_v ibypmts
2595 WHERE ibypmts.payment_id = ibydocs.payment_id
2596 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
2597 and ibypmts.org_type = 'OPERATING_UNIT'
2598 AND ibypmts.org_id = l_current_org_id)
2599 AND invoice_type_lookup_code <> 'INTEREST';
2600 */
2601 -- Bug 5512197. Adding the following for fnd logging
2602 l_debug_info := 'Sequential Numbering Option: '||l_seq_num_profile;
2603 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2604 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2605 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2606 END IF;
2607
2608 IF l_seq_num_profile IN ('A','P') then
2609
2610 l_debug_info := 'assign_sequences';
2611 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2612 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2613 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2614 END IF;
2615
2616 --bug15924350 add checkrun_id to parameters in this call
2617 assign_sequences(l_checkrun_id,
2618 p_completed_pmts_group_id,
2619 l_set_of_books_id,
2620 l_seq_num_profile,
2621 x_return_status,
2622 x_msg_count,
2623 x_msg_data,
2624 l_auto_calculate_interest_flag,
2625 l_interest_invoice_count,
2626 l_check_date,
2627 l_current_org_id);
2628
2629 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2630 RETURN;
2631 END IF;
2632
2633 END IF;
2634
2635
2636 IF l_seq_num_profile NOT IN ('A','P') and l_first_voucher_number is not null then
2637 l_debug_info := 'assign_vouchers';
2638 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2639 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2640 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2641 END IF;
2642 assign_vouchers(p_completed_pmts_group_id,
2643 l_checkrun_id,
2644 l_first_voucher_number,
2645 l_current_org_id);
2646 END IF;
2647
2648
2649 if l_perform_awt_flag = 'Y' then
2650
2651 l_debug_info := 'AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL';
2652 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2653 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2654 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2655 END IF;
2656
2657 AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL(l_checkrun_name,
2658 l_last_updated_by,
2659 --4863216
2660 to_number(FND_GLOBAL.USER_ID),--Bug6489464
2661 --to_number(FND_PROFILE.VALUE('LOGIN_ID')),
2662 to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID')),
2663 to_number(FND_PROFILE.VALUE('PROGRAM_ID')),
2664 to_number(FND_PROFILE.VALUE('REQUEST_ID')),
2665 l_checkrun_id,
2666 p_completed_pmts_group_id,
2667 l_current_org_id);
2668
2669 END if;
2670
2671 l_debug_info := 'AP_PAYMENT_EVENT_PKG.raise_payment_batch_events';
2672 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2673 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2674 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2675 END IF;
2676
2677 /* Added for Bug#9459810 Start */
2678 l_wf_event_exists :=0;
2679 BEGIN
2680 SELECT 1
2681 INTO l_wf_event_exists
2682 FROM wf_events we
2683 WHERE owner_tag = 'SQLAP'
2684 AND name = 'oracle.apps.ap.payment'
2685 AND status = 'ENABLED';
2686 EXCEPTION
2687 WHEN others THEN
2688 l_wf_event_exists:=0;
2689 END;
2690
2691 IF l_wf_event_exists=1 THEN
2692
2693 AP_PAYMENT_EVENT_PKG.raise_payment_batch_events(
2694 p_checkrun_name => l_checkrun_name,
2695 p_checkrun_id => l_checkrun_id,
2696 p_completed_pmts_group_id => p_completed_pmts_group_id,
2697 p_org_id => l_current_org_id);
2698
2699 END IF;
2700 /* Added for Bug#9459810 End */
2701
2702
2703 l_debug_info := 'DELETE FROM ap_selected_invoices_all';
2704 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2705 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2706 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2707 END IF;
2708
2709 DELETE FROM ap_selected_invoices_all
2710 WHERE checkrun_id = l_checkrun_id
2711 /* Bug 6950891. Added TO_CHAR */
2712 and (TO_CHAR(invoice_id), TO_CHAR(payment_num)) in
2713 (select ibydocs.calling_app_doc_unique_ref2,
2714 ibydocs.calling_app_doc_unique_ref3
2715 from iby_fd_docs_payable_v ibydocs,
2716 iby_fd_payments_v ibypmts
2717 where ibypmts.payment_id = ibydocs.payment_id
2718 and ibypmts.org_type = 'OPERATING_UNIT'
2719 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
2720 AND ibypmts.org_id = l_current_org_id);
2721
2722
2723 END LOOP; --loop for org_id's
2724 close c_relevant_orgs;
2725
2726 l_debug_info := 'DELETE FROM ap_unselected_invoices_all';
2727 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2728 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2729 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2730 END IF;
2731
2732 DELETE FROM ap_unselected_invoices_all
2733 WHERE checkrun_id = l_checkrun_id;
2734
2735 END LOOP; -- loop for checkrun_id
2736
2737
2738 SELECT COUNT(*)
2739 INTO l_iby_check_count
2740 FROM iby_fd_payments_v
2741 WHERE completed_pmts_group_id = p_completed_pmts_group_id;
2742
2743 SELECT COUNT(*)
2744 INTO l_iby_docs_count
2745 FROM iby_fd_docs_payable_v
2746 WHERE completed_pmts_group_id = p_completed_pmts_group_id;
2747
2748 l_debug_info := '******AP-IBY COUNTS****************';
2749 fnd_file.put_line(FND_FILE.LOG,l_debug_info||' - '||systimestamp);
2750 fnd_file.put_line(FND_FILE.LOG,'IBY CHECK COUNT: '||TO_CHAR(l_iby_check_count)||' - '||systimestamp);
2751 fnd_file.put_line(FND_FILE.LOG,'IBY DOC COUNT: '||TO_CHAR(l_iby_docs_count)||' - '||systimestamp);
2752 fnd_file.put_line(FND_FILE.LOG,'AP CHECK COUNT: '||TO_CHAR(l_total_check_count)||' - '||systimestamp);
2753 fnd_file.put_line(FND_FILE.LOG,'AP DOCS COUNT: '||TO_CHAR(l_total_docs_count)||' - '||systimestamp);
2754
2755
2756 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2757 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2758 l_debug_info := 'IBY CHECK COUNT: '||TO_CHAR(l_iby_check_count);
2759 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2760 l_debug_info := 'IBY DOC COUNT: '||TO_CHAR(l_iby_docs_count);
2761 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2762 l_debug_info := 'AP CHECK COUNT: '||TO_CHAR(l_total_check_count);
2763 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2764 l_debug_info := 'AP DOCS COUNT: '||TO_CHAR(l_total_docs_count);
2765 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2766
2767 END IF;
2768
2769 --Bug 5115363
2770 IF (l_total_check_count <> l_iby_check_count) OR
2771 (l_total_docs_count <> l_iby_docs_count) THEN
2772
2773 x_return_status := FND_API.G_RET_STS_ERROR;
2774
2775 FND_MESSAGE.set_name('SQLAP', 'AP_IBY_PMT_MISMATCH');
2776
2777 --Bug 9074840 Add additional debug queries
2778
2779 --As this is an error condition it will be written to the concurrent log
2780 --regardless of the runtime fnd log level.
2781 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME);
2782 l_debug_info := fnd_message.get;
2783 FND_FILE.PUT_LINE(FND_FILE.LOG, l_debug_info);
2784
2785 OPEN c_prob_iby_payments;
2786 FETCH c_prob_iby_payments BULK COLLECT INTO l_prob_pmt_list;
2787 CLOSE c_prob_iby_payments;
2788 print_prob_pmt_detail(l_prob_pmt_list, 'AP_IBY_PMT_MISMATCH_PMT');
2789
2790 OPEN c_prob_iby_docs;
2791 FETCH c_prob_iby_docs BULK COLLECT INTO l_prob_pmt_list;
2792 CLOSE c_prob_iby_docs;
2793 print_prob_pmt_detail(l_prob_pmt_list, 'AP_IBY_PMT_MISMATCH_DOC');
2794
2795 OPEN c_prob_checks;
2796 FETCH c_prob_checks BULK COLLECT INTO l_prob_pmt_list;
2797 CLOSE c_prob_checks;
2798 print_prob_pmt_detail(l_prob_pmt_list, 'AP_IBY_PMT_MISMATCH_CHK');
2799
2800 OPEN c_prob_inv_pmts;
2801 FETCH c_prob_inv_pmts BULK COLLECT INTO l_prob_pmt_list;
2802 CLOSE c_prob_inv_pmts;
2803 print_prob_pmt_detail(l_prob_pmt_list, 'AP_IBY_PMT_MISMATCH_INV');
2804
2805 l_debug_info := '*****************************************************************************';
2806 FND_FILE.PUT_LINE(FND_FILE.LOG, l_debug_info);
2807 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME);
2808 /* no longer necessary as the messages are only written to the log
2809 FND_MSG_PUB.ADD;
2810 FND_MSG_PUB.COUNT_AND_GET(
2811 p_count => x_msg_count,
2812 p_data => x_msg_data
2813 );
2814 */
2815 --End Bug 9074840
2816
2817 ELSE
2818
2819 x_return_status := FND_API.G_RET_STS_SUCCESS;
2820
2821 END IF;
2822
2823
2824
2825 EXCEPTION
2826
2827 WHEN SUBSCRIBE_EXCEPTION THEN
2828
2829 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2830 FND_MESSAGE.SET_TOKEN('ERROR',l_msg_data);
2831 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_current_calling_sequence);
2832 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2833
2834 /* Start - BUG 14364091 - Logging */
2835 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
2836 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
2837 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
2838 /* End - BUG 14364091 - Logging */
2839
2840
2841 APP_EXCEPTION.RAISE_EXCEPTION;
2842
2843 WHEN OTHERS THEN
2844 IF (SQLCODE <> -20001) THEN
2845 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2846 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2847 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
2848 FND_MESSAGE.set_TOKEN('PARAMETERS',
2849 'Complted Payments Group id = ' || to_char(p_completed_pmts_group_id));
2850 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
2851 END IF;
2852
2853 /* Start - BUG 14364091 - Logging */
2854 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
2855 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
2856 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || l_debug_info ||' - '||systimestamp);
2857 /* End - BUG 14364091 - Logging */
2858
2859 APP_EXCEPTION.RAISE_EXCEPTION;
2860
2861
2862
2863 END payments_completed;
2864
2865
2866
2867
2868
2869 PROCEDURE payments_cleared
2870 ( p_api_version IN NUMBER,
2871 p_init_msg_list IN VARCHAR2,
2872 p_commit IN VARCHAR2,
2873 x_return_status OUT nocopy VARCHAR2,
2874 x_msg_count OUT nocopy NUMBER,
2875 x_msg_data OUT nocopy VARCHAR2,
2876 p_group IN NUMBER)IS
2877 BEGIN
2878 NULL;
2879 END;
2880
2881 PROCEDURE payments_uncleared
2882 ( p_api_version IN NUMBER,
2883 p_init_msg_list IN VARCHAR2,
2884 p_commit IN VARCHAR2,
2885 x_return_status OUT nocopy VARCHAR2,
2886 x_msg_count OUT nocopy NUMBER,
2887 x_msg_data OUT nocopy VARCHAR2,
2888 p_group IN NUMBER)IS
2889 BEGIN
2890 NULL;
2891 END;
2892
2893 PROCEDURE Payment_Voided
2894 ( p_api_version IN NUMBER,
2895 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2896 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2897 p_payment_id IN NUMBER,
2898 p_void_date IN DATE,
2899 -- p_accounting_date IN DATE, /* Bug 4775938 */
2900 x_return_status OUT nocopy VARCHAR2,
2901 x_msg_count OUT nocopy NUMBER,
2902 x_msg_data OUT nocopy VARCHAR2) IS
2903
2904 l_api_name CONSTANT VARCHAR2(30) := 'Payments_Voided';
2905 l_api_version CONSTANT NUMBER := 1.0;
2906 l_debug_info VARCHAR2(2000);
2907 BEGIN
2908
2909 l_debug_info := 'Creating Savepoint';
2910 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2911 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2912 END IF;
2913
2914 -- Standard Start of API savepoint
2915 SAVEPOINT Payments_Voided_PUB;
2916
2917 l_debug_info := 'Checking API Compatibility';
2918 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2919 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2920 END IF;
2921
2922 -- Standard call to check for call compatibility.
2923 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2924 p_api_version,
2925 l_api_name,
2926 G_PKG_NAME )
2927 THEN
2928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2929 END IF;
2930
2931 -- Initialize message list if p_init_msg_list is set to TRUE.
2932 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2933 FND_MSG_PUB.initialize;
2934 END IF;
2935
2936 l_debug_info := 'Calling AP Void Pkg.Iby_Void_Check';
2937 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2938 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2939 END IF;
2940
2941 -- Initialize API return status to success
2942 x_return_status := FND_API.G_RET_STS_SUCCESS;
2943
2944 Ap_Void_Pkg.Iby_Void_Check
2945 (p_api_version => 1.0,
2946 p_init_msg_list => p_init_msg_list,
2947 p_commit => p_commit,
2948 p_payment_id => p_payment_id,
2949 p_void_date => p_void_date,
2950 x_return_status => x_return_status,
2951 x_msg_count => x_msg_count,
2952 x_msg_data => x_msg_data);
2953
2954 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2955
2956 l_debug_info := 'AP Void Pkg.Iby_Void_Check returns error';
2957 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2958 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2959 END IF;
2960
2961 ROLLBACK TO Payments_Voided_PUB; --4945922
2962 ELSE
2963 l_debug_info := 'AP Void Pkg.Iby_Void_Check returns success';
2964 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2965 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2966 END IF;
2967
2968 --COMMIT WORK;
2969 END IF;
2970
2971
2972 EXCEPTION
2973 WHEN FND_API.G_EXC_ERROR THEN
2974 ROLLBACK TO Payments_Voided_PUB; --4945922
2975 x_return_status := FND_API.G_RET_STS_ERROR ;
2976 FND_MSG_PUB.Count_And_Get
2977 ( p_count => x_msg_count,
2978 p_data => x_msg_data
2979 );
2980 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2981 ROLLBACK TO Payments_Voided_PUB;
2982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2983 FND_MSG_PUB.Count_And_Get
2984 ( p_count => x_msg_count,
2985 p_data => x_msg_data
2986 );
2987 WHEN OTHERS THEN
2988 ROLLBACK TO Payments_Voided_PUB;
2989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2990 IF FND_MSG_PUB.Check_Msg_Level
2991 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2992 FND_MSG_PUB.Add_Exc_Msg
2993 ( G_PKG_NAME,
2994 l_api_name
2995 );
2996 END IF;
2997 FND_MSG_PUB.Count_And_Get
2998 ( p_count => x_msg_count,
2999 p_data => x_msg_data
3000 );
3001
3002 END Payment_Voided;
3003
3004 PROCEDURE ap_JapanBankChargeHook(
3005 p_api_version IN NUMBER,
3006 p_init_msg_list IN VARCHAR2,
3007 p_commit IN VARCHAR2,
3008 x_return_status OUT nocopy VARCHAR2,
3009 x_msg_count OUT nocopy NUMBER,
3010 x_msg_data OUT nocopy VARCHAR2)
3011 is
3012 l_api_version CONSTANT NUMBER := 1.0;
3013 l_debug_info VARCHAR2(2000);
3014 l_api_name CONSTANT VARCHAR2(100) := 'AP_JAPANBANKCHARGEHOOK';
3015 BEGIN
3016
3017 l_debug_info := 'Creating Savepoint';
3018 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3019 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3020 END IF;
3021
3022 l_debug_info := 'Checking API Compatibility';
3023 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3024 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3025 END IF;
3026
3027 -- Standard call to check for call compatibility.
3028 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3029 p_api_version,
3030 l_api_name,
3031 G_PKG_NAME )
3032 THEN
3033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3034 END IF;
3035
3036 -- Initialize message list if p_init_msg_list is set to TRUE.
3037 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3038 FND_MSG_PUB.initialize;
3039 END IF;
3040
3041 l_debug_info := 'Calling ap_bank_charge_pkg.ap_JapanBankChargeHook';
3042 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3043 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3044 END IF;
3045
3046 ap_bank_charge_pkg.ap_JapanBankChargeHook(
3047 p_api_version ,
3048 p_init_msg_list ,
3049 p_commit ,
3050 x_return_status ,
3051 x_msg_count ,
3052 x_msg_data );
3053
3054 EXCEPTION
3055 WHEN FND_API.G_EXC_ERROR THEN
3056 x_return_status := FND_API.G_RET_STS_ERROR ;
3057 FND_MSG_PUB.Count_And_Get
3058 ( p_count => x_msg_count,
3059 p_data => x_msg_data
3060 );
3061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3063 FND_MSG_PUB.Count_And_Get
3064 ( p_count => x_msg_count,
3065 p_data => x_msg_data
3066 );
3067 WHEN OTHERS THEN
3068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3069 IF FND_MSG_PUB.Check_Msg_Level
3070 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3071 FND_MSG_PUB.Add_Exc_Msg
3072 ( G_PKG_NAME,
3073 l_api_name
3074 );
3075 END IF;
3076 FND_MSG_PUB.Count_And_Get
3077 ( p_count => x_msg_count,
3078 p_data => x_msg_data
3079 );
3080
3081 end ap_JapanBankChargeHook;
3082
3083 /* Bug 6756063: Added the following procedures to sync up the status
3084 of the Payment in Payables and IBY when Stop has been initiated and released. */
3085
3086 PROCEDURE Payment_Stop_Initiated
3087 ( p_payment_id IN NUMBER,
3088 p_stopped_date IN DATE, -- Bug 6957071
3089 p_stopped_by IN NUMBER, -- Bug 6957071
3090 x_return_status OUT nocopy VARCHAR2,
3091 x_msg_count OUT nocopy NUMBER,
3092 x_msg_data OUT nocopy VARCHAR2) IS
3093
3094 l_api_name CONSTANT VARCHAR2(30) := 'Payment_Status_Updated';
3095 l_api_version CONSTANT NUMBER := 1.0;
3096 l_debug_info VARCHAR2(2000);
3097
3098 BEGIN
3099
3100 l_debug_info := 'In Payment_Stop_Initiated';
3101 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3102 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3103 END IF;
3104
3105 l_debug_info := 'Payment_id from IBY API: '||p_payment_id;
3106 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3107 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3108 END IF;
3109
3110 update ap_checks_all
3111 set status_lookup_code = 'STOP INITIATED',
3112 stopped_date= p_stopped_date, -- Bug 6957071
3113 stopped_by= p_stopped_by -- Bug 6957071
3114 where payment_id = p_payment_id;
3115
3116 x_return_status := FND_API.G_RET_STS_SUCCESS;
3117
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3121 x_msg_count := 1;
3122 x_msg_data := substr(SQLERRM,1,25);
3123
3124 END Payment_Stop_Initiated;
3125
3126
3127 PROCEDURE Payment_Stop_Released
3128 ( p_payment_id IN NUMBER,
3129 x_return_status OUT nocopy VARCHAR2,
3130 x_msg_count OUT nocopy NUMBER,
3131 x_msg_data OUT nocopy VARCHAR2) IS
3132
3133 l_api_name CONSTANT VARCHAR2(30) := 'Payment_Status_Updated';
3134 l_api_version CONSTANT NUMBER := 1.0;
3135 l_debug_info VARCHAR2(2000);
3136
3137 BEGIN
3138
3139 l_debug_info := 'In Payment_Stop_Released';
3140 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3141 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3142 END IF;
3143
3144 l_debug_info := 'Payment_id from IBY API: '||p_payment_id;
3145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3146 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3147 END IF;
3148
3149 update ap_checks_all
3150 set status_lookup_code = 'NEGOTIABLE',
3151 stopped_date=null, -- Bug 6957071
3152 stopped_by=null -- Bug 6957071
3153 where payment_id = p_payment_id;
3154
3155 x_return_status := FND_API.G_RET_STS_SUCCESS;
3156
3157 EXCEPTION
3158 WHEN OTHERS THEN
3159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3160 x_msg_count := 1;
3161 x_msg_data := substr(SQLERRM,1,25);
3162
3163 END Payment_Stop_Released;
3164
3165 /* End of fix for bug 6756063 */
3166
3167 PROCEDURE void_payment_allowed
3168 ( p_api_version IN NUMBER,
3169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3170 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3171 p_payment_id IN NUMBER,
3172 x_return_flag OUT nocopy VARCHAR2,
3173 x_return_status OUT nocopy VARCHAR2,
3174 x_msg_count OUT nocopy NUMBER,
3175 x_msg_data OUT nocopy VARCHAR2) IS
3176
3177 l_api_name CONSTANT VARCHAR2(30) := 'void_payment_allowed';
3178 l_api_version CONSTANT NUMBER := 1.0;
3179 l_debug_info VARCHAR2(2000);
3180 l_payment_status VARCHAR2(30);
3181 l_prepay_app_exists NUMBER;
3182 l_check_prepay_unapply VARCHAR2(1) := 'N';
3183
3184
3185 BEGIN
3186
3187 -- Standard call to check for call compatibility.
3188 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3189 p_api_version,
3190 l_api_name,
3191 G_PKG_NAME )
3192 THEN
3193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3194 END IF;
3195 -- Initialize message list if p_init_msg_list is set to TRUE.
3196 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3197 FND_MSG_PUB.initialize;
3198 END IF;
3199 -- Initialize API return status to success
3200
3201 x_return_status := FND_API.G_RET_STS_SUCCESS;
3202
3203 BEGIN
3204 SELECT status_lookup_code
3205 INTO l_payment_status
3206 FROM AP_CHECKS_ALL
3207 WHERE payment_id = p_payment_id;
3208
3209 EXCEPTION
3210 WHEN NO_DATA_FOUND THEN
3211 l_payment_status := null;
3212 END;
3213
3214 -- Initialize the return flag to N
3215 x_return_flag := 'N';
3216
3217 if(l_payment_status in ('ISSUED','NEGOTIABLE','STOP INITIATED')) then
3218
3219 SELECT COUNT(*) INTO l_prepay_app_exists
3220 FROM ap_checks_all ac,
3221 ap_invoice_payments_all aip,
3222 ap_invoice_distributions_all aid,
3223 ap_invoices_all ai
3224 WHERE ac.payment_id = p_payment_id
3225 AND ac.check_id = aip.check_id
3226 AND aip.invoice_id = ai.invoice_id
3227 AND ai.invoice_id = aid.invoice_id
3228 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
3229 AND nvl(aid.prepay_amount_remaining,aid.amount) <> aid.amount
3230 AND nvl(aid.reversal_flag, 'N') <> 'Y'
3231 AND rownum = 1;
3232
3233 if (l_prepay_app_exists = 0) then
3234
3235 -- bug9441420, we would not allow cancellation if there
3236 -- the payment has been made for a prepayment invoice
3237 -- which has been applied and unapplied, applied has been
3238 -- accounted, but unapplication is not accounted
3239 --
3240 BEGIN
3241
3242 SELECT 'Y'
3243 INTO l_check_prepay_unapply
3244 FROM dual
3245 WHERE EXISTS
3246 (SELECT 1
3247 FROM ap_invoice_distributions_all aid_prepay,
3248 ap_checks_all ac,
3249 ap_invoice_payments_all aip,
3250 ap_invoices_all ai_prepay,
3251 ap_invoice_distributions_all aid,
3252 ap_invoice_distributions_all aidp
3253 WHERE aip.check_id = ac.check_id
3254 AND ac.payment_id = p_payment_id
3255 AND aip.invoice_id = ai_prepay.invoice_id
3256 AND ai_prepay.invoice_type_lookup_code = 'PREPAYMENT'
3257 AND aid_prepay.invoice_id = ai_prepay.invoice_id
3258 AND aid_prepay.invoice_distribution_id = aid.prepay_distribution_id
3259 AND aid.prepay_distribution_id IS NOT NULL
3260 AND aid.parent_reversal_id IS NOT NULL
3261 AND aid.amount > 0
3262 AND nvl(aid.posted_flag, 'N') = 'N'
3263 AND aid.invoice_id = aidp.invoice_id
3264 AND aid.invoice_line_number = aidp.invoice_line_number
3265 AND aid.parent_reversal_id = aidp.invoice_distribution_id
3266 AND aid.prepay_distribution_id = aidp.prepay_distribution_id
3267 AND nvl(aidp.posted_flag, 'N') = 'Y');
3268
3269 EXCEPTION
3270 WHEN OTHERS THEN
3271 NULL;
3272 END;
3273
3274 if l_check_prepay_unapply = 'N' then
3275 x_return_flag := 'Y';
3276 end if;
3277
3278 end if ;
3279
3280 end if ;
3281
3282 EXCEPTION
3283 WHEN FND_API.G_EXC_ERROR THEN
3284 x_return_status := FND_API.G_RET_STS_ERROR ;
3285 FND_MSG_PUB.Count_And_Get
3286 ( p_count => x_msg_count,
3287 p_data => x_msg_data
3288 );
3289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3291 FND_MSG_PUB.Count_And_Get
3292 ( p_count => x_msg_count,
3293 p_data => x_msg_data
3294 );
3295 WHEN OTHERS THEN
3296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3297 IF FND_MSG_PUB.Check_Msg_Level
3298 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3299 FND_MSG_PUB.Add_Exc_Msg
3300 ( G_PKG_NAME,
3301 l_api_name
3302 );
3303 END IF;
3304 FND_MSG_PUB.Count_And_Get
3305 ( p_count => x_msg_count,
3306 p_data => x_msg_data
3307 );
3308 END void_payment_allowed;
3309
3310 END AP_PMT_CALLOUT_PKG;