[Home] [Help]
PACKAGE BODY: APPS.AP_VOID_PKG
Source
1 PACKAGE BODY AP_VOID_PKG AS
2 /* $Header: apvoidpb.pls 120.36.12010000.4 2009/02/20 06:51:58 ppodhiya ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_VOID_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_VOID_PKG.';
21
22 /* bug 5169128 */
23 TYPE r_hold_info IS RECORD
24 (invoice_id AP_HOLDS_ALL.invoice_id%TYPE,
25 org_id AP_HOLDS_ALL.org_id%TYPE,
26 hold_id AP_HOLDS_ALL.hold_id%TYPE);
27
28 TYPE hold_tab_type IS TABLE OF r_hold_info INDEX BY BINARY_INTEGER;
29
30 PROCEDURE Ap_Reverse_Check(
31 P_Check_Id IN NUMBER,
32 P_Replace_Flag IN VARCHAR2,
33 P_Reversal_Date IN DATE,
34 P_Reversal_Period_Name IN VARCHAR2,
35 P_Checkrun_Name IN VARCHAR2,
36 P_Invoice_Action IN VARCHAR2,
37 P_Hold_Code IN VARCHAR2,
38 P_Hold_Reason IN VARCHAR2,
39 P_Sys_Auto_Calc_Int_Flag IN VARCHAR2,
40 P_Vendor_Auto_Calc_Int_Flag IN VARCHAR2,
41 P_Last_Updated_By IN NUMBER,
42 P_Last_Update_Login IN NUMBER,
43 P_Num_Cancelled OUT NOCOPY NUMBER,
44 P_Num_Not_Cancelled OUT NOCOPY NUMBER,
45 P_Calling_Module IN VARCHAR2 Default 'SQLAP',
46 P_Calling_Sequence IN VARCHAR2,
47 X_return_status OUT NOCOPY VARCHAR2,
48 X_msg_count OUT NOCOPY NUMBER,
49 X_msg_data OUT NOCOPY VARCHAR2)
50 IS
51 -- Cursor to insert reversing invoice payments. We swap gain and
52 -- loss ccids. This tricks posting into making the reversal to
53 -- the gain/loss account used for the original payment.
54
55 CURSOR c_new_payments IS
56 SELECT AIP.invoice_payment_id invoice_payment_id,
57 ap_invoice_payments_s.nextval new_invoice_payment_id,
58 AIP.invoice_id invoice_id,
59 AIP.payment_num payment_num,
60 AIP.check_id check_id,
61 0-NVL(AIP.amount,0) amount,
62 AIP.set_of_books_id set_of_books_id,
63 DECODE(AIP.discount_taken
64 ,'','',
65 0-NVL(AIP.discount_taken,0)) discount_taken,
66 DECODE(AIP.discount_lost
67 ,'','',
68 0-NVL(AIP.discount_lost,0)) discount_lost,
69 AIP.exchange_rate_type exchange_rate_type,
70 AIP.exchange_rate exchange_rate,
71 AIP.exchange_date exchange_date,
72 DECODE(AIP.invoice_base_amount
73 ,'','',0-NVL(AIP.invoice_base_amount,0))
74 invoice_base_amount,
75 DECODE(AIP.payment_base_amount
76 ,'','',
77 0-NVL(AIP.payment_base_amount,0))
78 payment_base_amount,
79 AIP.gain_code_combination_id gain_code_combination_id,
80 AIP.loss_code_combination_id loss_code_combination_id,
81 AIP.accts_pay_code_combination_id accts_pay_code_combination_id,
82 AIP.future_pay_code_combination_id future_pay_code_combination_id,
83 AI.vendor_id vendor_id,
84 AIP.assets_addition_flag assets_addition_flag,
85 AIP.attribute1,
86 AIP.attribute2,
87 AIP.attribute3,
88 AIP.attribute4,
89 AIP.attribute5,
90 AIP.attribute6,
91 AIP.attribute7,
92 AIP.attribute8,
93 AIP.attribute9,
94 AIP.attribute10,
95 AIP.attribute11,
96 AIP.attribute12,
97 AIP.attribute13,
98 AIP.attribute14,
99 AIP.attribute15,
100 AIP.attribute_category,
101 AIP.global_attribute1,
102 AIP.global_attribute2,
103 AIP.global_attribute3,
104 AIP.global_attribute4,
105 AIP.global_attribute5,
106 AIP.global_attribute6,
107 AIP.global_attribute7,
108 AIP.global_attribute8,
109 AIP.global_attribute9,
110 AIP.global_attribute10,
111 AIP.global_attribute11,
112 AIP.global_attribute12,
113 AIP.global_attribute13,
114 AIP.global_attribute14,
115 AIP.global_attribute15,
116 AIP.global_attribute16,
117 AIP.global_attribute17,
118 AIP.global_attribute18,
119 AIP.global_attribute19,
120 AIP.global_attribute20,
121 AIP.global_attribute_category,
122 AIP.org_id /* Bug 4759178, added org_id */
123 FROM ap_invoice_payments AIP,
124 ap_invoices AI
125 WHERE AIP.check_id = P_Check_Id
126 AND AIP.invoice_id = AI.invoice_id
127 AND nvl(AIP.reversal_flag, 'N') <> 'Y';
128
129 -------------------------------------------------------------------
130 -- Cursor finds all invoices paid by P_Check_Id
131 -------------------------------------------------------------------
132
133 CURSOR c_invoices IS
134 SELECT invoice_id
135 FROM ap_invoice_payments
136 WHERE check_id = P_Check_Id
137 AND nvl(reversal_flag, 'N') <> 'Y'
138 GROUP BY invoice_id;
139
140 -------------------------------------------------------------------
141 -- Cursor finds all payment schedules paid by P_Check_Id
142 -------------------------------------------------------------------
143
144 CURSOR c_payment_schedules IS
145 SELECT invoice_id,
146 payment_num
147 FROM ap_invoice_payments
148 WHERE check_id = P_Check_Id
149 AND nvl(reversal_flag, 'N') <> 'Y'
150 GROUP BY invoice_id, payment_num;
151
152
153 CURSOR C_Interest_Inv_Cur IS
154 SELECT aid.invoice_id invoice_id,
155 aid.dist_code_combination_id dist_code_combination_id,
156 ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
157 aid.invoice_line_number invoice_line_number, /* bug 5169128 */
158 aid.invoice_distribution_id parent_reversal_id, -- 2806074
159 aid.set_of_books_id set_of_books_id,
160 aid.amount * -1 amount,
161 aid.line_type_lookup_code line_type_lookup_code,
162 aid.base_amount * -1 base_amount,
163 alc.displayed_field || ' '|| aid.description description,
164 DECODE(gl.account_type, 'A', 'Y', 'N') assets_tracking_flag,
165 aid.accts_pay_code_combination_id accts_pay_code_combination_id,
166 -- Bug 4277744 - Removed references to USSGL
167 -- aid.ussgl_transaction_code ussgl_transaction_code,
168 aid.org_id org_id,
169 aid.type_1099 type_1099,
170 aid.income_tax_region income_tax_region
171 FROM ap_invoice_distributions aid,
172 gl_code_combinations gl,
173 ap_invoice_payments aip,
174 ap_invoice_relationships air,
175 ap_lookup_codes alc
176 WHERE air.related_invoice_id = aid.invoice_id
177 AND gl.code_combination_id = aid.dist_code_combination_id
178 AND aid.invoice_id = aip.invoice_id
179 AND aip.check_id = P_Check_Id
180 AND aip.amount > 0
181 AND alc.lookup_type = 'NLS TRANSLATION'
182 AND alc.lookup_code = 'VOID'
183 AND NVL(aip.reversal_flag, 'N') <> 'Y';
184
185 Interest_Inv_Cur C_Interest_Inv_Cur%ROWTYPE;
186
187 /* bug 5169128 */
188 Cursor C_Hold_Cur IS
189 SELECT DISTINCT AIP.invoice_id
190 , AIP.org_id /* Bug 3700128. MOAC PRoject */
191 FROM ap_invoice_payments AIP
192 WHERE AIP.check_id = P_check_id
193 AND nvl(AIP.reversal_flag, 'N') <> 'Y'
194 AND NOT EXISTS
195 (SELECT 'Invoice already has this hold'
196 FROM ap_holds AH
197 WHERE AH.invoice_id = AIP.invoice_id
198 AND AH.hold_lookup_code = P_Hold_Code
199 AND AH.release_lookup_code IS NULL)
200 AND NOT EXISTS (SELECT 'Invoice is an Interest Invoice' -- 3240962
201 FROM ap_invoices AI
202 WHERE AI.invoice_id = AIP.invoice_id
203 AND AI.invoice_type_lookup_code = 'INTEREST');
204
205 l_hold_tab hold_tab_type;
206 l_invoice_id_hold NUMBER;
207 l_org_id_hold NUMBER;
208 i NUMBER;
209 l_user_releaseable_flag VARCHAR2(1);
210 l_initiate_workflow_flag VARCHAR2(1);
211 /* bug 5169128 End */
212
213 l_max_dist_line_num NUMBER;
214
215 l_set_of_books_id NUMBER;
216 l_invoice_id NUMBER;
217 l_payment_num NUMBER;
218 l_success VARCHAR2(240);
219 INTERRUPT_VOID EXCEPTION;
220 l_debug_info VARCHAR2(240);
221 l_curr_calling_sequence VARCHAR2(2000);
222 rec_new_payments C_new_payments%ROWTYPE;
223 l_invoice_distribution_id NUMBER;
224
225 l_key_value_list1 gl_ca_utility_pkg.r_key_value_arr;
226 l_key_value_list2 gl_ca_utility_pkg.r_key_value_arr;
227
228 l_accounting_event_id NUMBER(38);
229 l_unaccounted_row_count NUMBER;
230 l_old_accounting_event_id NUMBER(38);
231 l_postable_flag VARCHAR2(1);
232
233 l_payment_type_flag ap_checks.payment_type_flag%TYPE; -- Bug3343314
234 l_amount ap_checks.amount%TYPE; -- Bug3343314
235 l_currency_code ap_checks.currency_code%TYPE; -- Bug3343314
236 l_exchange_rate_type ap_checks.exchange_rate_type%TYPE; -- Bug3343314
237 l_exchange_date ap_checks.exchange_date%TYPE; -- Bug3343314
238 l_exchange_rate ap_checks.exchange_rate%TYPE; -- Bug3343314
239 l_base_amount ap_checks.base_amount%TYPE; -- Bug3343314
240
241 --Bug 2840203 DBI logging
242 l_dbi_key_value_list1 ap_dbi_pkg.r_dbi_key_value_arr;
243 l_dbi_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
244 l_dbi_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
245
246 l_payment_id NUMBER;
247 l_return_status VARCHAR2(10);
248 l_msg_count NUMBER;
249 l_msg_data VARCHAR2(2000);
250 l_api_name CONSTANT VARCHAR2(30) := 'Ap_Reversal_Check';
251 l_error_count NUMBER;
252 l_error_msg VARCHAR2(2000);
253
254 l_org_id NUMBER;
255
256 l_netting_type VARCHAR2(30);
257 l_rev_pmt_hist_id NUMBER; -- Bug 5015973
258 l_transaction_type AP_PAYMENT_HISTORY_ALL.transaction_type%TYPE;
259
260 BEGIN
261
262 l_curr_calling_sequence := 'AP_VOID_PKG.AP_REVERSE_CHECK<-'||
263 P_Calling_Sequence;
264
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266
267 l_debug_info := 'Get accounting method system options';
268
269 l_debug_info := 'Get set of books id';
270
271 SELECT set_of_books_id
272 INTO l_set_of_books_id
273 FROM ap_invoice_payments
274 WHERE check_id = P_check_id
275 AND ROWNUM < 2;
276
277 l_debug_info := 'Get Payment Type information';
278
279 SELECT payment_type_flag
280 INTO l_netting_type
281 FROM ap_checks
282 WHERE check_id = p_check_id;
283
284 ---------------------------------------------------------------------
285 -- Fix for bug 893626:
286 -- Problem: After voiding a payment, the form field inv_curr_amount_paid
287 -- was incorrectly showing the invoice amount instead of the amount paid
288 -- for that invoice.
289 -- Cause: The payment_status_flag in ap_invoices table was not being
290 -- set to 'N' after voiding the payment for an invoice.
291 -- Fix: By executing the ap_pay_update_payment_schedule procedure call
292 -- before the ap_pay_update_ap_invoices, the payment_status_flag that was
293 -- set in ap_payment_schedule is populated in ap_invoices.
294 ---------------------------------------------------------------------
295
296 l_debug_info := 'Open c_payment_schedules cursor';
297
298 OPEN c_payment_schedules;
299
300 LOOP
301
302 l_debug_info := 'Fetch from c_payment_schedules cursor';
303
304 FETCH c_payment_schedules INTO l_invoice_id, l_payment_num;
305 EXIT WHEN c_payment_schedules%NOTFOUND;
306
307 -----------------------------------------------------------------
308 -- Update AP_PAYMENT_SCHEDULES paid by P_Check_Id
309 -----------------------------------------------------------------
310
311 AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_PAYMENT_SCHEDULE(
312 l_invoice_id,
313 l_payment_num,
314 P_Check_Id,
315 NULL,
316 NULL,
317 'Y',
318 'REV',
319 P_Replace_Flag,
320 P_Last_Updated_By,
321 SYSDATE,
322 l_curr_calling_sequence);
323 END LOOP;
324
325 l_debug_info := 'Close c_payment_schedules cursor';
326
327 CLOSE c_payment_schedules;
328
329 l_debug_info := 'Open c_invoices cursor';
330
331 OPEN c_invoices;
332
333 LOOP
334
335 l_debug_info := 'Fetch from c_invoices cursor';
336
337 FETCH c_invoices INTO l_invoice_id;
338 EXIT WHEN c_invoices%NOTFOUND;
339
340 -----------------------------------------------------------------
341 -- Update AP_INVOICES paid by P_Check_Id
342 -----------------------------------------------------------------
343
344 AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_AP_INVOICES (
345 l_invoice_id,
346 P_Check_Id,
347 NULL,
348 NULL,
349 'Y',
350 'REV',
351 P_Replace_Flag,
352 SYSDATE,
353 P_Last_Updated_By,
354 l_curr_calling_sequence);
355
356 END LOOP;
357
358 l_debug_info := 'Close c_invoices cursor';
359
360 CLOSE c_invoices;
361
362 -------------------------------------------------------------------
363 -- Reverse the interest invoice for the selected invoice
364 -- We should always reverse the interest invoices
365 -- related to original invoice if we are not replacing
366 -- the check i.e. we are voiding the check.
367 -- Also we need to update the payment schedules for the interest invoice
368 -------------------------------------------------------------------
369
370 BEGIN
371
372 IF (P_replace_flag = 'N') AND (l_netting_type <> 'N') THEN
373
374 l_debug_info := 'Update ap_payment_schedules';
375
376 UPDATE ap_payment_schedules_all aps
377 SET aps.last_updated_by = P_Last_Updated_By,
378 aps.gross_amount = 0,
379 aps.last_update_date = SYSDATE,
380 aps.amount_remaining = 0
381 WHERE aps.invoice_id IN (SELECT related_invoice_id
382 FROM ap_invoice_relationships air,
383 ap_invoice_payments_all aip
384 WHERE aip.check_id = P_Check_Id
385 AND air.related_invoice_id = aip.invoice_id
386 AND nvl(aip.reversal_flag, 'N') <> 'Y')
387 RETURNING aps.invoice_id
388 BULK COLLECT INTO l_dbi_key_value_list2;
389
390 IF (SQL%NOTFOUND) THEN
391 RAISE INTERRUPT_VOID;
392 END IF;
393
394 --Bug 4539462 DBI logging
395 AP_DBI_PKG.Maintain_DBI_Summary
396 (p_table_name => 'AP_PAYMENT_SCHEDULES',
397 p_operation => 'U',
398 p_key_value_list => l_dbi_key_value_list2,
399 p_calling_sequence => l_curr_calling_sequence);
400
401 l_debug_info := 'Update ap_invoices for Interest invoice';
402
403 UPDATE ap_invoices_all AI
404 SET AI.description = 'VOID '||AI.description,
405 AI.invoice_amount = 0,
406 AI.amount_paid = 0,
407 AI.invoice_distribution_total = 0,
408 AI.cancelled_date = sysdate, --bug5631957
409 AI.pay_curr_invoice_amount = 0 --bug5631957
410 WHERE AI.invoice_id IN
411 (SELECT AIR.related_invoice_id
412 FROM ap_invoice_relationships AIR,
413 ap_invoice_payments_all AIP
414 WHERE AIP.invoice_id = AIR.related_invoice_id
415 AND AIP.check_id = P_Check_Id
416 AND NVL(aip.reversal_flag, 'N') <> 'Y')
417 RETURNING invoice_id
418 BULK COLLECT INTO l_dbi_key_value_list1;
419
420 IF (SQL%NOTFOUND) THEN
421 RAISE INTERRUPT_VOID;
422 END IF;
423
424 --Bug 4539462 DBI logging
425 AP_DBI_PKG.Maintain_DBI_Summary
426 (p_table_name => 'AP_INVOICES',
427 p_operation => 'U',
428 p_key_value_list => l_dbi_key_value_list1,
429 p_calling_sequence => l_curr_calling_sequence);
430
431 l_debug_info := 'Update ap_invoice_lines for Interest invoice';
432
433 UPDATE ap_invoice_lines_all AIL
434 SET AIL.description = 'VOID '||AIL.description,
435 AIL.amount = 0,
436 AIL.base_amount = 0
437 WHERE AIL.invoice_id IN
438 (SELECT AIR.related_invoice_id
439 FROM ap_invoice_relationships AIR,
440 ap_invoice_payments_all AIP
441 WHERE AIP.invoice_id = AIR.related_invoice_id
442 AND AIP.check_id = P_Check_Id
443 AND NVL(aip.reversal_flag, 'N') <> 'Y');
444
445 l_debug_info := 'INSERT ap_invoice_distributions for Interest Invoice';
446
447 SELECT MAX(aid.distribution_line_number)
448 INTO l_max_dist_line_num
449 FROM ap_invoice_distributions aid,
450 gl_code_combinations gl,
451 ap_invoice_payments aip,
452 ap_invoice_relationships air,
453 ap_lookup_codes alc
454 WHERE air.related_invoice_id = aid.invoice_id
455 AND gl.code_combination_id = aid.dist_code_combination_id
456 AND aid.invoice_id = aip.invoice_id
457 AND aip.check_id = P_Check_Id
458 AND aip.amount > 0
459 AND alc.lookup_type = 'NLS TRANSLATION'
460 AND alc.lookup_code = 'VOID'
461 AND NVL(aip.reversal_flag, 'N') <> 'Y';
462
463 OPEN C_Interest_Inv_Cur;
464
465 LOOP
466 FETCH C_Interest_Inv_Cur INTO Interest_Inv_Cur;
467
468 EXIT WHEN C_Interest_Inv_Cur%NOTFOUND;
469
470 l_max_dist_line_num := l_max_dist_line_num + 1;
471
472
473 INSERT INTO ap_invoice_distributions_all
474 (INVOICE_ID,
475 DIST_CODE_COMBINATION_ID,
476 INVOICE_DISTRIBUTION_ID,
477 INVOICE_LINE_NUMBER, /* bug 5169128 */
478 LAST_UPDATED_BY,
479 ASSETS_ADDITION_FLAG,
480 ACCOUNTING_DATE,
481 PERIOD_NAME,
482 SET_OF_BOOKS_ID,
483 AMOUNT,
484 POSTED_FLAG,
485 CASH_POSTED_FLAG,
486 ACCRUAL_POSTED_FLAG,
487 MATCH_STATUS_FLAG,
488 DISTRIBUTION_LINE_NUMBER,
489 LINE_TYPE_LOOKUP_CODE,
490 BASE_AMOUNT,
491 LAST_UPDATE_DATE,
492 DESCRIPTION,
493 PA_ADDITION_FLAG,
494 CREATED_BY,
495 CREATION_DATE,
496 ASSETS_TRACKING_FLAG,
497 ACCTS_PAY_CODE_COMBINATION_ID,
498 -- USSGL_TRANSACTION_CODE, - Bug 4277744
499 ORG_ID,
500 DIST_MATCH_TYPE,
501 DISTRIBUTION_CLASS,
502 AMOUNT_TO_POST,
503 BASE_AMOUNT_TO_POST,
504 POSTED_AMOUNT,
505 POSTED_BASE_AMOUNT,
506 UPGRADE_POSTED_AMT,
507 UPGRADE_BASE_POSTED_AMT,
508 ROUNDING_AMT,
509 ACCOUNTING_EVENT_ID,
510 ENCUMBERED_FLAG,
511 PACKET_ID,
512 -- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
513 REVERSAL_FLAG,
514 PARENT_REVERSAL_ID,
515 CANCELLATION_FLAG,
516 ASSET_BOOK_TYPE_CODE,
517 ASSET_CATEGORY_ID,
518 LAST_UPDATE_LOGIN,
519 --Freight and Special Charges
520 RCV_CHARGE_ADDITION_FLAG,
521 TYPE_1099,
522 INCOME_TAX_REGION)
523 VALUES
524 (Interest_Inv_Cur.invoice_id,
525 Interest_Inv_Cur.dist_code_combination_id,
526 Interest_Inv_Cur.invoice_distribution_id,
527 Interest_Inv_Cur.invoice_line_number, /* bug 5169128 */
528 P_Last_Updated_By,
529 'U',
530 P_reversal_Date,
531 P_reversal_Period_Name,
532 Interest_Inv_Cur.set_of_books_id,
533 Interest_Inv_Cur.amount,
534 'N',
535 'N',
536 'N',
537 'A',
538 l_max_dist_line_num,
539 Interest_Inv_Cur.line_type_lookup_code,
540 Interest_Inv_Cur.base_amount,
541 SYSDATE,
542 Interest_Inv_Cur.description,
543 'E',
544 P_Last_Updated_By,
545 SYSDATE,
546 Interest_Inv_Cur.assets_tracking_flag,
547 Interest_Inv_Cur.accts_pay_code_combination_id,
548 -- Interest_Inv_Cur.ussgl_transaction_code, - Bug 4277744
549 Interest_Inv_Cur.org_id,
550 'MATCH_STATUS',
551 'PERMANENT',
552 NULL,
553 NULL,
554 NULL,
555 NULL,
556 NULL,
557 NULL,
558 NULL,
559 NULL,
560 'N',
561 NULL,
562 -- NULL, - Bug 4277744
563 NULL,
564 Interest_Inv_Cur.parent_reversal_id, --2806074
565 NULL,
566 NULL,
567 NULL,
568 P_last_update_login,
569 'N',
570 Interest_Inv_Cur.type_1099,
571 Interest_Inv_Cur.income_tax_region);
572
573 --Bug 4539462 DBI logging
574 AP_DBI_PKG.Maintain_DBI_Summary
575 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
576 p_operation => 'I',
577 p_key_value1 => Interest_Inv_Cur.invoice_id,
578 p_key_value2 => Interest_Inv_Cur.invoice_distribution_id,
579 p_calling_sequence => l_curr_calling_sequence);
580
581
582 END LOOP;
583
584 END IF;
585
586 EXCEPTION
587 WHEN INTERRUPT_VOID THEN
588 l_debug_info := 'INTERRUPT_VOID';
589
590 END;
591
592 -- -----------------------------------------------------------------
593 -- Events Project - 2 ----------------------------------------------
594 -- Added select to help determine whether event should be created
595 -- -----------------------------------------------------------------
596
597 -- Bug3343314
598 SELECT
599 payment_type_flag,
600 amount,
601 currency_code,
602 exchange_rate_type,
603 exchange_date,
604 exchange_rate,
605 base_amount,
606 org_id
607 INTO
608 l_payment_type_flag,
609 l_amount,
610 l_currency_code,
611 l_exchange_rate_type,
612 l_exchange_date,
613 l_exchange_rate,
614 l_base_amount,
615 l_org_id
616 FROM
617 ap_checks
618 WHERE
619 check_id = p_check_id;
620
621 --------------------------------------------------------------------
622 l_debug_info := 'Unclear the payment if the payment type is netting';
623
624 if l_netting_type = 'N' then --4945922
625
626 AP_RECONCILIATION_PKG.Recon_Payment_History
627 (NULL,
628 P_Check_Id,
629 P_Reversal_Date,
630 P_Reversal_Date,
631 l_amount,
632 'PAYMENT UNCLEARING',
633 NULL,
634 NULL,
635 l_currency_code,
636 l_exchange_rate_type,
637 l_exchange_date,
638 l_exchange_rate,
639 'N',
640 NULL,
641 SYSDATE,
642 P_Last_Updated_By,
643 P_Last_Update_Login,
644 P_Last_Updated_By,
645 SYSDATE,
646 NULL,
647 NULL,
648 NULL,
649 NULL,
650 l_curr_calling_sequence);
651 end if;
652
653 -- Events Project - 4 -----------------------------------------------
654 -- For the case where we reissue an unaccounted check that has a
655 -- Payment Event, we do not want to create a Payment Cancellation Event.
656 -- Instead, we will stamp the accounting_event_id of the Payment Event
657 -- on the rows in AP_INVOICE_PAYMENTS pertaining to the Payment void.
658 -- This will happen after the new rows are inserted into
659 -- AP_INVOICE_PAYMENTS below.
660 -- -----------------------------------------------------------
661
662 BEGIN
663
664 SELECT max(accounting_event_id)
665 INTO l_old_accounting_event_id
666 FROM AP_INVOICE_PAYMENTS AIP
667 WHERE check_id = P_check_id
668 AND posted_flag = 'N';
669
670 EXCEPTION when no_data_found then
671 l_old_accounting_event_id := NULL;
672 END;
673 -- Commenting for bug 8236138
674 /*
675 If ( P_Replace_flag <> 'Y') OR
676 ( l_old_accounting_event_id IS NULL ) then*/
677 -- Bug 4759178, event is PAYMENT CANCELLATION
678 AP_ACCOUNTING_EVENTS_PKG.Create_Events ('PAYMENT CANCELLATION'
679 ,l_payment_type_flag -- Bug3343314
680 ,P_check_id
681 ,P_Reversal_date
682 ,l_accounting_event_id
683 ,P_checkrun_name
684 ,l_curr_calling_sequence);
685
686 IF ( l_payment_type_flag = 'R' ) THEN
687 l_transaction_type := 'REFUND CANCELLED';
688 ELSE
689 l_transaction_type := 'PAYMENT CANCELLED';
690 END IF;
691
692 -- Bug 5015973. Getting the reversal payment history id
693 -- Commented for Bug 6953346
694 /*
695 SELECT MAX(Payment_History_ID)
696 INTO l_rev_pmt_hist_id
697 FROM AP_Payment_History APH
698 WHERE APH.Check_ID = P_Check_ID
699 AND APH.Transaction_Type = 'PAYMENT CREATED';
700 */
701
702 -- Added for Bug 6953346
703
704 IF(l_transaction_type = 'PAYMENT CANCELLED') THEN
705 SELECT MAX(Payment_History_ID)
706 INTO l_rev_pmt_hist_id
707 FROM AP_Payment_History APH
708 WHERE APH.Check_ID = P_Check_ID
709 AND APH.Transaction_Type = 'PAYMENT CREATED';
710
711 ELSE
712 SELECT MAX(Payment_History_ID)
713 INTO l_rev_pmt_hist_id
714 FROM AP_Payment_History APH
715 WHERE APH.Check_ID = P_Check_ID
716 AND APH.Transaction_Type = 'REFUND RECORDED';
717
718 END IF;
719
720 -- End of Bug 6953346
721
722 -- Bug3343314
723 AP_RECONCILIATION_PKG.insert_payment_history
724 (
725 x_check_id => p_check_id,
726 x_transaction_type => l_transaction_type,
727 x_accounting_date => p_reversal_date,
728 x_trx_bank_amount => NULL,
729 x_errors_bank_amount => NULL,
730 x_charges_bank_amount => NULL,
731 x_bank_currency_code => NULL,
732 x_bank_to_base_xrate_type => NULL,
733 x_bank_to_base_xrate_date => NULL,
734 x_bank_to_base_xrate => NULL,
735 x_trx_pmt_amount => l_amount,
736 x_errors_pmt_amount => NULL,
737 x_charges_pmt_amount => NULL,
738 x_pmt_currency_code => l_currency_code,
739 x_pmt_to_base_xrate_type => l_exchange_rate_type,
740 x_pmt_to_base_xrate_date => l_exchange_date,
741 x_pmt_to_base_xrate => l_exchange_rate,
742 x_trx_base_amount => l_base_amount,
743 x_errors_base_amount => NULL,
744 x_charges_base_amount => NULL,
745 x_matched_flag => NULL,
746 x_rev_pmt_hist_id => l_rev_pmt_hist_id,
747 x_org_id => l_org_id, -- 4578865
748 x_creation_date => SYSDATE,
749 x_created_by => p_last_updated_by,
750 x_last_update_date => SYSDATE,
751 x_last_updated_by => p_last_updated_by,
752 x_last_update_login => p_last_update_login,
753 x_program_update_date => NULL,
754 x_program_application_id => NULL,
755 x_program_id => NULL,
756 x_request_id => NULL,
757 x_calling_sequence => l_curr_calling_sequence,
758 x_accounting_event_id => l_accounting_event_id
759 );
760 -- Commenting for bug 8236138
761 /*
762 Else
763
764 l_accounting_event_id := l_old_accounting_event_id;
765 End If; */
766
767 -- Events Project - end -------------------------------------------
768
769 -------------------------------------------------------------------
770 -- Hold invoices if necessary
771 --
772 IF (P_Invoice_Action = 'HOLD') THEN
773 l_debug_info := 'Hold invoices';
774
775 --Bug 4539462 collecting invoice_ids first
776 SELECT DISTINCT AIP.invoice_id
777 BULK COLLECT INTO l_dbi_key_value_list3
778 FROM ap_invoice_payments AIP
779 WHERE AIP.check_id = P_check_id
780 AND nvl(AIP.reversal_flag, 'N') <> 'Y'
781 AND NOT EXISTS
782 (SELECT 'Invoice already has this hold'
783 FROM ap_holds AH
784 WHERE AH.invoice_id = AIP.invoice_id
785 AND AH.hold_lookup_code = P_Hold_Code
786 AND AH.release_lookup_code IS NULL)
787 AND NOT EXISTS (SELECT 'Invoice is an Interest Invoice'
788 FROM ap_invoices AI
789 WHERE AI.invoice_id = AIP.invoice_id
790 AND AI.invoice_type_lookup_code = 'INTEREST');
791
792 /* Bug 5169128 */
793 OPEN C_Hold_Cur;
794 LOOP
795
796 FETCH C_hold_Cur INTO l_invoice_id_hold,
797 l_org_id_hold;
798
799 EXIT WHEN C_Hold_Cur%NOTFOUND;
800
801 l_hold_tab(l_invoice_id_hold).invoice_id := l_invoice_id_hold;
802 l_hold_tab(l_invoice_id_hold).org_id := l_org_id_hold;
803 Select AP_HOLDS_S.nextval
804 INTO l_hold_tab(l_invoice_id_hold).hold_id
805 From DUAL;
806
807 END LOOP;
808 CLOSE C_Hold_Cur;
809
810 FOR i in nvl(l_hold_tab.FIRST, 0) .. nvl(l_hold_tab.LAST, 0) LOOP
811
812 IF (l_hold_tab.exists(i)) THEN
813
814 INSERT INTO ap_holds_all
815 (invoice_id
816 ,hold_lookup_code
817 ,last_update_date
818 ,last_updated_by
819 ,held_by
820 ,hold_date
821 ,hold_reason
822 ,created_by
823 ,creation_date
824 ,org_id /* Bug 3700128. MOAC Project */
825 ,hold_id)
826 Values
827 (l_hold_tab(i).invoice_id
828 ,P_Hold_Code
829 ,sysdate
830 ,P_Last_Updated_By
831 ,P_Last_Updated_By
832 ,sysdate
833 ,P_Hold_Reason
834 ,P_Last_Updated_By
835 ,sysdate
836 ,l_hold_tab(i).org_id /* Bug 3700128. MOAC PRoject */
837 ,l_hold_tab(i).hold_id);
838
839 END IF;
840
841 END LOOP;
842
843 /* bug 5169128 */
844 --Bug 4539462 DBI logging
845 AP_DBI_PKG.Maintain_DBI_Summary
846 (p_table_name => 'AP_HOLDS',
847 p_operation => 'I',
848 p_key_value_list => l_dbi_key_value_list3,
849 p_calling_sequence => l_curr_calling_sequence);
850
851
852 -- Events Project - 5 -------------------------------------------------
853 -- Added call to AP_ACCOUNTING_EVENTS_PKG.Update_Events_Status
854 -- so that if a posting_hold is placed on an invoice during payment
855 -- void, and the invoice has not been accounted, the status of the
856 -- invoice related event will change from 'CREATED' to 'INCOMPLETE'.
857 -- --------------------------------------------------------------------
858
859 SELECT postable_flag,
860 user_releaseable_flag, /* bug 5143826 */
861 initiate_workflow_flag
862 INTO l_postable_flag,
863 l_user_releaseable_flag,
864 l_initiate_workflow_flag
865 FROM AP_HOLD_CODES AHC
866 WHERE AHC.hold_lookup_code = P_Hold_code;
867
868 IF (nvl(l_postable_flag , 'N') = 'N') THEN
869
870 AP_ACCOUNTING_EVENTS_PKG.UPDATE_PAYMENT_EVENTS_STATUS -- Bug3343314
871 (
872 p_check_id => p_check_id,
873 p_calling_sequence => l_curr_calling_sequence -- Bug3343314
874 );
875 End if;
876
877 /* bug 5143826 */
878 IF (NVL(l_user_releaseable_flag, 'N') = 'Y' AND
879 NVL(l_initiate_workflow_flag, 'N') = 'Y') THEN
880
881 FOR i in nvl(l_hold_tab.FIRST, 0) .. nvl(l_hold_tab.LAST, 0) LOOP
882
883 IF (l_hold_tab.exists(i)) THEN
884
885 AP_WORKFLOW_PKG.create_hold_wf_process(l_hold_tab(i).hold_id);
886
887 END IF;
888
889 END LOOP;
890
891 END IF;
892
893
894 -------------------------------------------------------------------
895 -- Or cancel invoices
896 --
897 -------------------------------------------------------------------
898 -- Bug 8257752.
899 -- Cancel invoice is now called after undo withholding.
900 -------------------------------------------------------------------
901 /*
902 ELSIF (P_Invoice_Action = 'CANCEL') THEN
903
904 -----------------------------------------------------------------
905 l_debug_info := 'Commit changes before cancelling invoices';
906 -- 1828366, commenting out NOCOPY the commit because if the form fails
907 -- the record will still get commited. Removing the commit
908 -- below was not part of 1372660 (1828366 is a forward port of 1372660)
909 -- COMMIT;
910
911 AP_CANCEL_PKG.AP_CANCEL_INVOICES(P_Check_Id,
912 P_Last_Updated_By,
913 P_Last_Update_Login,
914 -- Base Line ARU
915 -- l_set_of_books_id,
916 P_Reversal_Date,
917 -- Base Line ARU
918 -- P_Reversal_Period_Name,
919 P_Num_Cancelled,
920 P_Num_Not_Cancelled,
921 l_curr_calling_sequence);
922 */
923 END IF;
924
925
926 l_debug_info := 'Open c_new_payments cursor';
927
928 -- -----------------------------------------------------------
929
930 OPEN c_new_payments;
931
932 LOOP
933 -----------------------------------------------------------------
934 l_debug_info := 'Fetch from c_new_payments cursor';
935
936 FETCH c_new_payments INTO rec_new_payments;
937 EXIT WHEN c_new_payments%NOTFOUND;
938
939 -----------------------------------------------------------------
940 -- Create reversing invoice payment
941 --
942 AP_PAY_INVOICE_PKG.AP_PAY_INVOICE
943 (rec_new_payments.invoice_id
944 ,P_Check_Id
945 ,rec_new_payments.payment_num
946 ,rec_new_payments.new_invoice_payment_id
947 ,rec_new_payments.invoice_payment_id
948 ,P_Reversal_Period_Name
949 ,NULL
950 ,P_Reversal_Date
951 ,rec_new_payments.amount
952 ,rec_new_payments.discount_taken
953 ,rec_new_payments.discount_lost
954 ,rec_new_payments.invoice_base_amount
955 ,rec_new_payments.payment_base_amount
956 ,'N'
957 ,'N'
958 ,'N'
959 ,rec_new_payments.set_of_books_id
960 ,P_Last_Updated_By
961 ,P_Last_Update_Login
962 ,NULL
963 ,NULL
964 ,rec_new_payments.exchange_rate
965 ,rec_new_payments.exchange_rate_type
966 ,rec_new_payments.exchange_date
967 ,NULL
968 ,NULL
969 ,NULL
970 ,NULL
971 ,'N'
972 ,NULL
973 ,rec_new_payments.accts_pay_code_combination_id
974 ,rec_new_payments.gain_code_combination_id
975 ,rec_new_payments.loss_code_combination_id
976 ,rec_new_payments.future_pay_code_combination_id
977 ,NULL
978 ,'Y'
979 ,'REV'
980 ,P_Replace_Flag
981 ,rec_new_payments.attribute1
982 ,rec_new_payments.attribute2
983 ,rec_new_payments.attribute3
984 ,rec_new_payments.attribute4
985 ,rec_new_payments.attribute5
986 ,rec_new_payments.attribute6
987 ,rec_new_payments.attribute7
988 ,rec_new_payments.attribute8
989 ,rec_new_payments.attribute9
990 ,rec_new_payments.attribute10
991 ,rec_new_payments.attribute11
992 ,rec_new_payments.attribute12
993 ,rec_new_payments.attribute13
994 ,rec_new_payments.attribute14
995 ,rec_new_payments.attribute15
996 ,rec_new_payments.attribute_category
997 ,rec_new_payments.global_attribute1
998 ,rec_new_payments.global_attribute2
999 ,rec_new_payments.global_attribute3
1000 ,rec_new_payments.global_attribute4
1001 ,rec_new_payments.global_attribute5
1002 ,rec_new_payments.global_attribute6
1003 ,rec_new_payments.global_attribute7
1004 ,rec_new_payments.global_attribute8
1005 ,rec_new_payments.global_attribute9
1006 ,rec_new_payments.global_attribute10
1007 ,rec_new_payments.global_attribute11
1008 ,rec_new_payments.global_attribute12
1009 ,rec_new_payments.global_attribute13
1010 ,rec_new_payments.global_attribute14
1011 ,rec_new_payments.global_attribute15
1012 ,rec_new_payments.global_attribute16
1013 ,rec_new_payments.global_attribute17
1014 ,rec_new_payments.global_attribute18
1015 ,rec_new_payments.global_attribute19
1016 ,rec_new_payments.global_attribute20
1017 ,rec_new_payments.global_attribute_category
1018 ,l_curr_calling_sequence
1019 ,l_accounting_event_id -- Events Project - 6
1020 ,rec_new_payments.org_id /* Bug 4759178, passed org_id */
1021 );
1022
1023 --Bug695340: Update the assets flag based on the old assets
1024 -- flag value.
1025 -- If the old was: U, N, or Y then set the new to U.
1026 -- Otherwise keep it as NULL.
1027 if rec_new_payments.assets_addition_flag is not null then
1028 UPDATE ap_invoice_payments
1029 SET assets_addition_flag = 'U'
1030 WHERE invoice_payment_id =
1031 rec_new_payments.new_invoice_payment_id;
1032 end if;
1033
1034 -----------------------------------------------------------------
1035 -- Undo any withholding taken at payment time
1036 --
1037
1038 IF l_netting_type <> 'N' THEN
1039 AP_WITHHOLDING_PKG.Ap_Undo_Withholding
1040 (rec_new_payments.invoice_payment_id
1041 ,'VOID PAYMENT'
1042 ,P_Reversal_Date
1043 ,rec_new_payments.new_invoice_payment_id
1044 ,P_Last_Updated_By
1045 ,P_Last_Update_Login
1046 ,NULL
1047 ,NULL
1048 ,NULL
1049 ,l_success
1050 );
1051 END IF;
1052
1053 END LOOP;
1054
1055 -------------------------------------------------------------------
1056 -- Bug 8257752.
1057 -- Cancel invoice is now called after undo withholding.
1058 -------------------------------------------------------------------
1059
1060 IF (P_Invoice_Action = 'CANCEL') THEN
1061
1062 -----------------------------------------------------------------
1063 l_debug_info := 'Commit changes before cancelling invoices';
1064 -- 1828366, commenting out NOCOPY the commit because if the form fails
1065 -- the record will still get commited. Removing the commit
1066 -- below was not part of 1372660 (1828366 is a forward port of 1372660)
1067 -- COMMIT;
1068
1069 AP_CANCEL_PKG.AP_CANCEL_INVOICES(P_Check_Id,
1070 P_Last_Updated_By,
1071 P_Last_Update_Login,
1072 /* Base Line ARU */
1073 -- l_set_of_books_id,
1074 P_Reversal_Date,
1075 /* Base Line ARU */
1076 -- P_Reversal_Period_Name,
1077 P_Num_Cancelled,
1078 P_Num_Not_Cancelled,
1079 l_curr_calling_sequence);
1080 END IF;
1081
1082 -- Events Project - 7 ---------------------------------------------
1083 -- Now that interest invoices and AWT distributions have been
1084 -- created, we want to stamp the accounting_event_id of the Payment
1085 -- event on the AWT and interest invoice distributions.
1086 -- ----------------------------------------------------------------
1087
1088 IF l_netting_type <> 'N' THEN
1089 AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
1090 (
1091 p_event_type => 'PAYMENT CANCELLED',
1092 p_check_id => p_check_id,
1093 p_event_id => l_accounting_event_id,
1094 p_calling_sequence => l_curr_calling_sequence
1095 );
1096 END IF;
1097
1098 ---------------------------------------------------------------------
1099
1100 l_debug_info := 'Close c_new_payments cursor';
1101
1102 CLOSE c_new_payments;
1103
1104 -------------------------------------------------------------------
1105 -- Delete any temporary records in AP_SELECTED_INVOICES
1106 -- if this is a Quickcheck as the
1107 -- format program could have bombed and the user just decided
1108 -- to void it all
1109 --
1110 l_debug_info := 'Delete from ap_selected_invoices';
1111
1112 IF l_netting_type <> 'N' THEN
1113
1114 BEGIN
1115
1116 DELETE FROM ap_selected_invoices
1117 WHERE checkrun_name = P_Checkrun_Name;
1118
1119 EXCEPTION
1120 WHEN NO_DATA_FOUND THEN
1121 NULL;
1122 END;
1123 END IF;
1124
1125 -----------------------------------------------------------------------
1126 -- In case the procedure has been called to Reverse a Netting Request
1127 -- then the check status should also be updated to 'VOIDED', and the
1128 -- Void Date should be populated as the sysdate
1129 -- This is not Required in the case of Quick Checks or Manual Checks as
1130 -- because the form takes care of populating these fields.
1131 -- bug6634891
1132
1133 IF l_netting_type = 'N' THEN
1134
1135 UPDATE ap_checks_all
1136 SET status_lookup_code = 'VOIDED',
1137 void_date = P_reversal_date
1138 WHERE check_id = p_check_id;
1139
1140 END IF;
1141
1142
1143 IF (p_calling_module <> 'IBY') AND (l_payment_type_flag NOT IN ('R','N'))
1144 THEN
1145
1146 l_debug_info := 'Selecting the IBY payment id from ap_checks_all';
1147
1148 BEGIN
1149 SELECT payment_id
1150 INTO l_payment_id
1151 FROM AP_CHECKS_ALL
1152 WHERE check_id = p_check_id;
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 null;
1156 END ;
1157
1158 IF l_payment_id IS NOT NULL THEN
1159
1160 l_debug_info := 'Calling IBY API to synchronize IBY Data';
1161
1162 IBY_DISBURSE_UI_API_PUB_PKG.Void_Payment
1163 (p_api_version => 1.0,
1164 p_init_msg_list => FND_API.G_FALSE,
1165 p_pmt_id => l_payment_id,
1166 p_voided_by => p_last_updated_by,
1167 p_void_date => p_reversal_date,
1168 p_void_reason => 'Oracle Payables',
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data);
1172
1173 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1174 FOR I IN 1..l_msg_count
1175 LOOP
1176 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I
1177 ,p_encoded => 'T');
1178 FND_MESSAGE.SET_ENCODED(l_error_msg);
1179 END LOOP;
1180 APP_EXCEPTION.RAISE_EXCEPTION;
1181 END IF;
1182
1183 END IF;
1184
1185 END IF;
1186 -------------------------------------------------------------------
1187 --1372660/1828366 removing the commit because if the form fails, the
1188 --record will still get commited possibly.
1189 --l_debug_info := 'Commit changes to database';
1190 --COMMIT;
1191
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 IF (SQLCODE <> -20001) THEN
1195 IF p_calling_module <> 'IBY' THEN
1196 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1197 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1198 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1199 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1200 ', CHECK_ID = ' || TO_CHAR(P_Check_Id)
1201 ||', REPLACE_FLAG = ' || P_Replace_Flag
1202 ||', REVERSAL_DATE = ' || TO_CHAR(P_Reversal_Date)
1203 ||', PERIOD_NAME = ' || P_Reversal_Period_Name
1204 ||', CHECKRUN_NAME = ' || P_Checkrun_Name
1205 ||', INVOICE_ACTION = ' || P_Invoice_Action
1206 ||', HOLD_CODE = ' || P_Hold_Code
1207 ||', HOLD_REASON = ' || P_Hold_Reason
1208 ||', LAST_UPDATED_BY = ' || TO_CHAR(P_Last_Updated_By)
1209 ||', LAST_UPDATED_LOGIN = '|| TO_CHAR(P_Last_Update_Login));
1210 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_Debug_Info);
1211 ELSE
1212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1213 FND_MSG_PUB.Count_And_Get
1214 (p_count => x_msg_count,
1215 p_data => x_msg_data
1216 );
1217 END IF;
1218 END IF;
1219 APP_EXCEPTION.RAISE_EXCEPTION;
1220
1221 END Ap_Reverse_Check;
1222
1223 /* New procedure to be used by Oracle Payments
1224 during voiding of payments from their UI */
1225
1226 PROCEDURE Iby_Void_Check
1227 (p_api_version IN NUMBER,
1228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1229 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1230 p_payment_id IN NUMBER,
1231 p_void_date IN DATE,
1232 x_return_status OUT NOCOPY VARCHAR2,
1233 x_msg_count OUT NOCOPY VARCHAR2,
1234 x_msg_data OUT NOCOPY VARCHAR2)
1235 IS
1236 l_api_name CONSTANT VARCHAR2(30) := 'Iby_Void_Check';
1237 l_api_version CONSTANT NUMBER := 1.0;
1238
1239 l_return_status VARCHAR2(10);
1240 l_msg_count NUMBER;
1241 l_msg_data VARCHAR2(2000);
1242 l_user_id NUMBER;
1243 l_login_id NUMBER;
1244 l_reversal_date DATE;
1245 l_reversal_period_name VARCHAR2(240);
1246 l_num_cancelled NUMBER;
1247 l_num_not_cancelled NUMBER;
1248 l_gl_date DATE;
1249 l_check_id NUMBER;
1250 -- bug# 6643035 l_checkrun_name is changed to database column type
1251 l_checkrun_name ap_checks_all.checkrun_name%type;
1252 l_org_id NUMBER;
1253 l_debug_info VARCHAR2(2000);
1254
1255 BEGIN
1256
1257 l_debug_info := 'Checking API Compatibility';
1258 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1259 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1260 END IF;
1261
1262 -- Standard call to check for call compatibility.
1263 IF NOT FND_API.Compatible_API_Call (l_api_version,
1264 p_api_version,
1265 l_api_name,
1266 G_PKG_NAME )
1267 THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270
1271 FND_MSG_PUB.initialize;
1272
1273 l_debug_info := 'Payment_id from IBY API: '||p_payment_id;
1274 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1275 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1276 END IF;
1277
1278
1279 l_user_id := FND_GLOBAL.USER_ID;
1280 l_login_id := FND_GLOBAL.LOGIN_ID;
1281 -- Initialize API return status to success
1282 x_return_status := FND_API.G_RET_STS_SUCCESS;
1283
1284 l_debug_info := 'Deriving check_id, org_id';
1285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1286 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1287 END IF;
1288
1289 BEGIN
1290 SELECT check_id,
1291 checkrun_id,
1292 org_id
1293 INTO l_check_id,
1294 l_checkrun_name,
1295 l_org_id
1296 FROM AP_CHECKS_ALL
1297 WHERE payment_id = p_payment_id;
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 l_check_id := NULL;
1301 l_checkrun_name := NULL;
1302 l_org_id := NULL;
1303 END;
1304
1305 l_debug_info := 'Derived Check_Id: '||l_check_id;
1306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1308 END IF;
1309
1310 IF l_org_id IS NOT NULL THEN
1311 AP_UTILITIES_PKG.Get_Only_Open_Gl_Date
1312 (p_date => p_void_date,
1313 p_period_name => l_reversal_period_name,
1314 p_gl_date => l_gl_date,
1315 p_org_id => l_org_id);
1316 END IF;
1317
1318 l_debug_info := 'l_reversal_period_name: '||l_reversal_period_name;
1319 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1320 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1321 END IF;
1322
1323 IF l_reversal_period_name IS NULL THEN
1324 x_return_status := FND_API.G_RET_STS_ERROR ;
1325 FND_MESSAGE.Set_Name('SQLAP','AP_NO_OPEN_PERIOD');
1326 FND_MSG_PUB.Count_And_Get(
1327 p_count => x_msg_count,
1328 p_data => x_msg_data
1329 );
1330 ELSE
1331 IF l_gl_date > p_void_date THEN
1332 l_reversal_date := l_gl_date;
1333 ELSE
1334 l_reversal_date := p_void_date;
1335 END IF;
1336
1337 l_debug_info := 'Calling Ap_Reverse_Check';
1338 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1339 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1340 END IF;
1341
1342 Ap_Reverse_Check
1343 (p_check_id => l_check_id,
1344 p_replace_flag => 'N',
1345 p_reversal_date => l_reversal_date,
1346 p_reversal_period_name => l_reversal_period_name,
1347 p_checkrun_name => l_checkrun_name,
1348 p_invoice_action => NULL,
1349 p_hold_code => NULL,
1350 p_hold_reason => NULL,
1351 P_Sys_Auto_Calc_Int_Flag => NULL,
1352 P_Vendor_Auto_Calc_Int_Flag => NULL,
1353 P_Last_Updated_By => l_user_id,
1354 P_Last_Update_Login => l_login_id,
1355 P_Num_Cancelled => l_num_cancelled,
1356 P_Num_Not_Cancelled => l_num_not_cancelled,
1357 P_Calling_Module => 'IBY',
1358 P_Calling_Sequence => 'AP_VOID_PKG.Iby_Void_Check',
1359 x_return_status => x_return_status,
1360 X_msg_count => X_msg_count,
1361 X_msg_data => X_msg_data);
1362
1363 l_debug_info := 'Return Status from Ap_Reverse_Check: '||x_return_status;
1364 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1366 END IF;
1367
1368 /* Bug 5407058 */
1369 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1370 UPDATE AP_CHECKS_ALL
1371 SET status_lookup_code = 'VOIDED'
1372 ,void_date = l_reversal_date
1373 WHERE check_id = l_check_id;
1374 END IF;
1375
1376 END IF;
1377
1378 EXCEPTION
1379
1380 WHEN FND_API.G_EXC_ERROR THEN
1381 x_return_status := FND_API.G_RET_STS_ERROR ;
1382 FND_MSG_PUB.Count_And_Get
1383 ( p_count => x_msg_count,
1384 p_data => x_msg_data
1385 );
1386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1388 FND_MSG_PUB.Count_And_Get
1389 ( p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392 WHEN OTHERS THEN
1393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1394 IF FND_MSG_PUB.Check_Msg_Level
1395 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1396 FND_MSG_PUB.Add_Exc_Msg
1397 ( G_PKG_NAME,
1398 l_api_name
1399 );
1400 END IF;
1401 FND_MSG_PUB.Count_And_Get
1402 ( p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405
1406 END Iby_Void_Check;
1407
1408 END AP_VOID_PKG;