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