[Home] [Help]
PACKAGE BODY: APPS.AP_VOID_PKG
Source
1 PACKAGE BODY AP_VOID_PKG AS
2 /* $Header: apvoidpb.pls 120.43 2011/03/18 11:18:30 inanaiah 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 -- bug9441420
206 CURSOR Prepay_Appl IS
207 SELECT DISTINCT aid.invoice_id
208 FROM ap_invoice_payments_all aip,
209 ap_invoice_distributions_all aid_prepay,
210 ap_invoices_all ai_prepay,
211 ap_invoice_distributions_all aid
212 WHERE aip.check_id = P_Check_ID
213 AND aip.invoice_id = aid_prepay.invoice_id
214 AND aid_prepay.invoice_id = ai_prepay.invoice_id
215 AND ai_prepay.invoice_type_lookup_code = 'PREPAYMENT'
216 AND aid_prepay.invoice_distribution_id = aid.prepay_distribution_id;
217
218
219 l_hold_tab hold_tab_type;
220 l_invoice_id_hold NUMBER;
221 l_org_id_hold NUMBER;
222 i NUMBER;
223 l_user_releaseable_flag VARCHAR2(1);
224 l_initiate_workflow_flag VARCHAR2(1);
225 /* bug 5169128 End */
226
227 l_max_dist_line_num NUMBER;
228
229 l_set_of_books_id NUMBER;
230 l_invoice_id NUMBER;
231 l_payment_num NUMBER;
232 l_success VARCHAR2(240);
233 INTERRUPT_VOID EXCEPTION;
234 l_debug_info VARCHAR2(240);
235 l_curr_calling_sequence VARCHAR2(2000);
236 rec_new_payments C_new_payments%ROWTYPE;
237 l_invoice_distribution_id NUMBER;
238
239 l_key_value_list1 gl_ca_utility_pkg.r_key_value_arr;
240 l_key_value_list2 gl_ca_utility_pkg.r_key_value_arr;
241
242 l_accounting_event_id NUMBER(38);
243 l_unaccounted_row_count NUMBER;
244 l_old_accounting_event_id NUMBER(38);
245 l_postable_flag VARCHAR2(1);
246
247 l_payment_type_flag ap_checks.payment_type_flag%TYPE; -- Bug3343314
248 l_amount ap_checks.amount%TYPE; -- Bug3343314
249 l_currency_code ap_checks.currency_code%TYPE; -- Bug3343314
250 l_exchange_rate_type ap_checks.exchange_rate_type%TYPE; -- Bug3343314
251 l_exchange_date ap_checks.exchange_date%TYPE; -- Bug3343314
252 l_exchange_rate ap_checks.exchange_rate%TYPE; -- Bug3343314
253 l_base_amount ap_checks.base_amount%TYPE; -- Bug3343314
254
255 --Bug 2840203 DBI logging
256 l_dbi_key_value_list1 ap_dbi_pkg.r_dbi_key_value_arr;
257 l_dbi_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
258 l_dbi_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
259
260 l_payment_id NUMBER;
261 l_return_status VARCHAR2(10);
262 l_msg_count NUMBER;
263 l_msg_data VARCHAR2(2000);
264 l_api_name CONSTANT VARCHAR2(30) := 'Ap_Reversal_Check';
265 l_error_count NUMBER;
266 l_error_msg VARCHAR2(2000);
267
268 l_org_id NUMBER;
269
270 l_netting_type VARCHAR2(30);
271 l_rev_pmt_hist_id NUMBER; -- Bug 5015973
272 l_transaction_type AP_PAYMENT_HISTORY_ALL.transaction_type%TYPE;
273
274 /* Introduced following variables for IL BOE project - Bug 11661879 */
275 l_il_patch_level VARCHAR2(1);
276 l_ap_patch_level VARCHAR2(1);
277 l_boe_excp EXCEPTION;
278 l_il_ou_check BOOLEAN;
279 l_ou_id NUMBER;
280
281 BEGIN
282
283 l_curr_calling_sequence := 'AP_VOID_PKG.AP_REVERSE_CHECK<-'||
284 P_Calling_Sequence;
285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_curr_calling_sequence);
287 END IF;
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 l_debug_info := 'Get set of books id';
291 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
292 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
293 END IF;
294
295
296 SELECT set_of_books_id,org_id
297 INTO l_set_of_books_id,l_ou_id
298 FROM ap_invoice_payments
299 WHERE check_id = P_check_id
300 AND ROWNUM < 2;
301
302 l_debug_info := 'Get patch set level';
303 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
305 END IF;
306
307 /* As part of IL BOE Project - Bug 11661879, we need to stop voiding of
308 payment which belongs to invoices defined under BOE category.
309 This project is implemented in 12.2 and onwards, thus checking the patchset
310 level for further logic */
311
312 SELECT decode(substr(patch_level,1,9), 'R12.AP.A.', 'N', 'R12.AP.B.', 'N', 'Y')
313 INTO l_ap_patch_level
314 FROM fnd_product_installations
315 WHERE application_id = 200;
316
317 l_debug_info := 'Is AP patch set level above branchline :'||l_ap_patch_level;
318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
319 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
320 END IF;
321
322 l_debug_info := 'Get info whether IL is installed';
323 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
325 END IF;
326
327 BEGIN
328 SELECT decode(substr(patch_level,1,9), 'R12.JA.A.', 'N', 'R12.JA.B.', 'N', 'Y')
329 INTO l_il_patch_level
330 FROM fnd_product_installations
331 WHERE application_id = 7000;
332
333 IF (l_il_patch_level = 'Y') THEN
334
335 l_debug_info := 'Check IL installation at Org level';
336 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
338 END IF;
339
340 l_il_ou_check := JAI_CMN_UTILS_PKG.CHECK_JAI_EXISTS(p_calling_object => G_PKG_NAME||'.'||l_api_name
341 ,p_org_id => l_ou_id);
342 IF (l_il_ou_check) THEN
343 l_il_patch_level := 'Y';
344 ELSE
345 l_il_patch_level := 'N';
346 END IF;
347
348 l_debug_info := 'Is IL installed at Org level :'||l_il_patch_level;
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 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 l_il_patch_level := 'N';
357 END;
358
359 l_debug_info := 'Is IL patch set level above branchline :'||l_il_patch_level;
360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
362 END IF;
363
364 /* IL BOE Project - Bug 11661879 / 11886141
365 Before performing any function, we need to verify whether check_id
366 is eligible for voiding. For this, we need to verify whether associated
367 invoice belongs to BOE category or not. Checks paying BOE invoices will
368 not be allowed to VOID - as per this project */
369
370 IF (l_ap_patch_level = 'Y' AND l_il_patch_level = 'Y' ) THEN
371
372 JAI_BOE_GENERAL_PKG.ap_void_check ( pn_check_id => p_check_id,
373 xv_return_code => x_return_status,
374 xv_return_mesg => x_msg_data
375 );
376
377 l_debug_info := 'Return status from JAI is :'||x_return_status;
378 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
380 END IF;
381
382 l_debug_info := 'Return message from JAI is :'||x_msg_data;
383 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
385 END IF;
386
387 IF (nvl(x_return_status,'N') <> 'N') THEN
388 RAISE l_boe_excp;
389 END IF;
390
391 END IF;
392 /* Addition for IL BOE project - Bug 11661879 ends */
393
394
395 l_debug_info := 'Get Payment Type information';
396 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
398 END IF;
399
400 SELECT payment_type_flag
401 INTO l_netting_type
402 FROM ap_checks
403 WHERE check_id = p_check_id;
404
405 ---------------------------------------------------------------------
406 -- Fix for bug 893626:
407 -- Problem: After voiding a payment, the form field inv_curr_amount_paid
408 -- was incorrectly showing the invoice amount instead of the amount paid
409 -- for that invoice.
410 -- Cause: The payment_status_flag in ap_invoices table was not being
411 -- set to 'N' after voiding the payment for an invoice.
412 -- Fix: By executing the ap_pay_update_payment_schedule procedure call
413 -- before the ap_pay_update_ap_invoices, the payment_status_flag that was
414 -- set in ap_payment_schedule is populated in ap_invoices.
415 ---------------------------------------------------------------------
416
417 l_debug_info := 'Open c_payment_schedules cursor';
418 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
419 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
420 END IF;
421
422 OPEN c_payment_schedules;
423
424 LOOP
425
426 l_debug_info := 'Fetch from c_payment_schedules cursor';
427 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
428 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
429 END IF;
430
431 FETCH c_payment_schedules INTO l_invoice_id, l_payment_num;
432 EXIT WHEN c_payment_schedules%NOTFOUND;
433
434 -----------------------------------------------------------------
435 -- Update AP_PAYMENT_SCHEDULES paid by P_Check_Id
436 -----------------------------------------------------------------
437
438 AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_PAYMENT_SCHEDULE(
439 l_invoice_id,
440 l_payment_num,
441 P_Check_Id,
442 NULL,
443 NULL,
444 'Y',
445 'REV',
446 P_Replace_Flag,
447 P_Last_Updated_By,
448 SYSDATE,
449 l_curr_calling_sequence);
450 END LOOP;
451
452 l_debug_info := 'Close c_payment_schedules cursor';
453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
455 END IF;
456
457 CLOSE c_payment_schedules;
458
459 l_debug_info := 'Open c_invoices cursor';
460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
462 END IF;
463
464 OPEN c_invoices;
465
466 LOOP
467
468 l_debug_info := 'Fetch from c_invoices cursor';
469 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
470 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
471 END IF;
472
473 FETCH c_invoices INTO l_invoice_id;
474 EXIT WHEN c_invoices%NOTFOUND;
475
476 -----------------------------------------------------------------
477 -- Update AP_INVOICES paid by P_Check_Id
478 -----------------------------------------------------------------
479
480 AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_AP_INVOICES (
481 l_invoice_id,
482 P_Check_Id,
483 NULL,
484 NULL,
485 'Y',
486 'REV',
487 P_Replace_Flag,
488 SYSDATE,
489 P_Last_Updated_By,
490 l_curr_calling_sequence);
491
492 END LOOP;
493
494 l_debug_info := 'Close c_invoices cursor';
495 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
496 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
497 END IF;
498
499 CLOSE c_invoices;
500
501 -------------------------------------------------------------------
502 -- Reverse the interest invoice for the selected invoice
503 -- We should always reverse the interest invoices
504 -- related to original invoice if we are not replacing
505 -- the check i.e. we are voiding the check.
506 -- Also we need to update the payment schedules for the interest invoice
507 -------------------------------------------------------------------
508
509 BEGIN
510
511 IF (P_replace_flag = 'N') AND (l_netting_type <> 'N') THEN
512
513 l_debug_info := 'Update ap_payment_schedules';
514 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
515 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
516 END IF;
517
518 UPDATE ap_payment_schedules_all aps
519 SET aps.last_updated_by = P_Last_Updated_By,
520 aps.gross_amount = 0,
521 aps.last_update_date = SYSDATE,
522 aps.amount_remaining = 0
523 WHERE aps.invoice_id IN (SELECT related_invoice_id
524 FROM ap_invoice_relationships air,
525 ap_invoice_payments_all aip
526 WHERE aip.check_id = P_Check_Id
527 AND air.related_invoice_id = aip.invoice_id
528 AND nvl(aip.reversal_flag, 'N') <> 'Y')
529 RETURNING aps.invoice_id
530 BULK COLLECT INTO l_dbi_key_value_list2;
531
532 IF (SQL%NOTFOUND) THEN
533 RAISE INTERRUPT_VOID;
534 END IF;
535
536 --Bug 4539462 DBI logging
537 AP_DBI_PKG.Maintain_DBI_Summary
538 (p_table_name => 'AP_PAYMENT_SCHEDULES',
539 p_operation => 'U',
540 p_key_value_list => l_dbi_key_value_list2,
541 p_calling_sequence => l_curr_calling_sequence);
542
543 l_debug_info := 'Update ap_invoices for Interest invoice';
544 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
546 END IF;
547
548 UPDATE ap_invoices_all AI
549 SET AI.description = 'VOID '||AI.description,
550 AI.invoice_amount = 0,
551 AI.amount_paid = 0,
552 AI.invoice_distribution_total = 0,
553 AI.cancelled_date = sysdate, --bug5631957
554 AI.pay_curr_invoice_amount = 0 --bug5631957
555 WHERE AI.invoice_id IN
556 (SELECT AIR.related_invoice_id
557 FROM ap_invoice_relationships AIR,
558 ap_invoice_payments_all AIP
559 WHERE AIP.invoice_id = AIR.related_invoice_id
560 AND AIP.check_id = P_Check_Id
561 AND NVL(aip.reversal_flag, 'N') <> 'Y')
562 RETURNING invoice_id
563 BULK COLLECT INTO l_dbi_key_value_list1;
564
565 IF (SQL%NOTFOUND) THEN
566 RAISE INTERRUPT_VOID;
567 END IF;
568
569 --Bug 4539462 DBI logging
570 AP_DBI_PKG.Maintain_DBI_Summary
571 (p_table_name => 'AP_INVOICES',
572 p_operation => 'U',
573 p_key_value_list => l_dbi_key_value_list1,
574 p_calling_sequence => l_curr_calling_sequence);
575
576 l_debug_info := 'Update ap_invoice_lines for Interest invoice';
577 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
579 END IF;
580
581 UPDATE ap_invoice_lines_all AIL
582 SET AIL.description = 'VOID '||AIL.description,
583 AIL.amount = 0,
584 AIL.base_amount = 0
585 WHERE AIL.invoice_id IN
586 (SELECT AIR.related_invoice_id
587 FROM ap_invoice_relationships AIR,
588 ap_invoice_payments_all AIP
589 WHERE AIP.invoice_id = AIR.related_invoice_id
590 AND AIP.check_id = P_Check_Id
591 AND NVL(aip.reversal_flag, 'N') <> 'Y');
592
593 l_debug_info := 'INSERT ap_invoice_distributions for Interest Invoice';
594 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
595 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
596 END IF;
597
598 SELECT MAX(aid.distribution_line_number)
599 INTO l_max_dist_line_num
600 FROM ap_invoice_distributions aid,
601 gl_code_combinations gl,
602 ap_invoice_payments aip,
603 ap_invoice_relationships air,
604 ap_lookup_codes alc
605 WHERE air.related_invoice_id = aid.invoice_id
606 AND gl.code_combination_id = aid.dist_code_combination_id
607 AND aid.invoice_id = aip.invoice_id
608 AND aip.check_id = P_Check_Id
609 AND aip.amount > 0
610 AND alc.lookup_type = 'NLS TRANSLATION'
611 AND alc.lookup_code = 'VOID'
612 AND NVL(aip.reversal_flag, 'N') <> 'Y';
613
614 OPEN C_Interest_Inv_Cur;
615
616 LOOP
617 FETCH C_Interest_Inv_Cur INTO Interest_Inv_Cur;
618
619 EXIT WHEN C_Interest_Inv_Cur%NOTFOUND;
620
621 l_max_dist_line_num := l_max_dist_line_num + 1;
622
623
624 INSERT INTO ap_invoice_distributions_all
625 (INVOICE_ID,
626 DIST_CODE_COMBINATION_ID,
627 INVOICE_DISTRIBUTION_ID,
628 INVOICE_LINE_NUMBER, /* bug 5169128 */
629 LAST_UPDATED_BY,
630 ASSETS_ADDITION_FLAG,
631 ACCOUNTING_DATE,
632 PERIOD_NAME,
633 SET_OF_BOOKS_ID,
634 AMOUNT,
635 POSTED_FLAG,
636 CASH_POSTED_FLAG,
637 ACCRUAL_POSTED_FLAG,
638 MATCH_STATUS_FLAG,
639 DISTRIBUTION_LINE_NUMBER,
640 LINE_TYPE_LOOKUP_CODE,
641 BASE_AMOUNT,
642 LAST_UPDATE_DATE,
643 DESCRIPTION,
644 PA_ADDITION_FLAG,
645 CREATED_BY,
646 CREATION_DATE,
647 ASSETS_TRACKING_FLAG,
648 ACCTS_PAY_CODE_COMBINATION_ID,
649 -- USSGL_TRANSACTION_CODE, - Bug 4277744
650 ORG_ID,
651 DIST_MATCH_TYPE,
652 DISTRIBUTION_CLASS,
653 AMOUNT_TO_POST,
654 BASE_AMOUNT_TO_POST,
655 POSTED_AMOUNT,
656 POSTED_BASE_AMOUNT,
657 UPGRADE_POSTED_AMT,
658 UPGRADE_BASE_POSTED_AMT,
659 ROUNDING_AMT,
660 ACCOUNTING_EVENT_ID,
661 ENCUMBERED_FLAG,
662 PACKET_ID,
663 -- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
664 REVERSAL_FLAG,
665 PARENT_REVERSAL_ID,
666 CANCELLATION_FLAG,
667 ASSET_BOOK_TYPE_CODE,
668 ASSET_CATEGORY_ID,
669 LAST_UPDATE_LOGIN,
670 --Freight and Special Charges
671 RCV_CHARGE_ADDITION_FLAG,
672 TYPE_1099,
673 INCOME_TAX_REGION)
674 VALUES
675 (Interest_Inv_Cur.invoice_id,
676 Interest_Inv_Cur.dist_code_combination_id,
677 Interest_Inv_Cur.invoice_distribution_id,
678 Interest_Inv_Cur.invoice_line_number, /* bug 5169128 */
679 P_Last_Updated_By,
680 'U',
681 P_reversal_Date,
682 P_reversal_Period_Name,
683 Interest_Inv_Cur.set_of_books_id,
684 Interest_Inv_Cur.amount,
685 'N',
686 'N',
687 'N',
688 'A',
689 l_max_dist_line_num,
690 Interest_Inv_Cur.line_type_lookup_code,
691 Interest_Inv_Cur.base_amount,
692 SYSDATE,
693 Interest_Inv_Cur.description,
694 'E',
695 P_Last_Updated_By,
696 SYSDATE,
697 Interest_Inv_Cur.assets_tracking_flag,
698 Interest_Inv_Cur.accts_pay_code_combination_id,
699 -- Interest_Inv_Cur.ussgl_transaction_code, - Bug 4277744
700 Interest_Inv_Cur.org_id,
701 'MATCH_STATUS',
702 'PERMANENT',
703 NULL,
704 NULL,
705 NULL,
706 NULL,
707 NULL,
708 NULL,
709 NULL,
710 NULL,
711 'N',
712 NULL,
713 -- NULL, - Bug 4277744
714 NULL,
715 Interest_Inv_Cur.parent_reversal_id, --2806074
716 NULL,
717 NULL,
718 NULL,
719 P_last_update_login,
720 'N',
721 Interest_Inv_Cur.type_1099,
722 Interest_Inv_Cur.income_tax_region);
723
724 --Bug 4539462 DBI logging
725 AP_DBI_PKG.Maintain_DBI_Summary
726 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
727 p_operation => 'I',
728 p_key_value1 => Interest_Inv_Cur.invoice_id,
729 p_key_value2 => Interest_Inv_Cur.invoice_distribution_id,
730 p_calling_sequence => l_curr_calling_sequence);
731
732
733 END LOOP;
734
735 END IF;
736
737 EXCEPTION
738 WHEN INTERRUPT_VOID THEN
739 l_debug_info := 'INTERRUPT_VOID';
740
741 END;
742
743 -- -----------------------------------------------------------------
744 -- Events Project - 2 ----------------------------------------------
745 -- Added select to help determine whether event should be created
746 -- -----------------------------------------------------------------
747
748 -- Bug3343314
749 SELECT
750 payment_type_flag,
751 amount,
752 currency_code,
753 exchange_rate_type,
754 exchange_date,
755 exchange_rate,
756 base_amount,
757 org_id
758 INTO
759 l_payment_type_flag,
760 l_amount,
761 l_currency_code,
762 l_exchange_rate_type,
763 l_exchange_date,
764 l_exchange_rate,
765 l_base_amount,
766 l_org_id
767 FROM
768 ap_checks
769 WHERE
770 check_id = p_check_id;
771
772 --------------------------------------------------------------------
773 l_debug_info := 'Unclear the payment if the payment type is netting :'||l_netting_type;
774 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
775 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
776 END IF;
777
778 if l_netting_type = 'N' then --4945922
779
780 AP_RECONCILIATION_PKG.Recon_Payment_History
781 (NULL,
782 P_Check_Id,
783 P_Reversal_Date,
784 P_Reversal_Date,
785 l_amount,
786 'PAYMENT UNCLEARING',
787 NULL,
788 NULL,
789 l_currency_code,
790 l_exchange_rate_type,
791 l_exchange_date,
792 l_exchange_rate,
793 'N',
794 NULL,
795 SYSDATE,
796 P_Last_Updated_By,
797 P_Last_Update_Login,
798 P_Last_Updated_By,
799 SYSDATE,
800 NULL,
801 NULL,
802 NULL,
803 NULL,
804 l_curr_calling_sequence);
805 end if;
806
807 -- Events Project - 4 -----------------------------------------------
808 -- For the case where we reissue an unaccounted check that has a
809 -- Payment Event, we do not want to create a Payment Cancellation Event.
810 -- Instead, we will stamp the accounting_event_id of the Payment Event
811 -- on the rows in AP_INVOICE_PAYMENTS pertaining to the Payment void.
812 -- This will happen after the new rows are inserted into
813 -- AP_INVOICE_PAYMENTS below.
814 -- -----------------------------------------------------------
815
816 BEGIN
817
818 SELECT max(accounting_event_id)
819 INTO l_old_accounting_event_id
820 FROM AP_INVOICE_PAYMENTS AIP
821 WHERE check_id = P_check_id
822 AND posted_flag = 'N';
823
824 EXCEPTION when no_data_found then
825 l_old_accounting_event_id := NULL;
826 END;
827 -- Commenting for bug 8236138
828 /*
829 If ( P_Replace_flag <> 'Y') OR
830 ( l_old_accounting_event_id IS NULL ) then*/
831 -- Bug 4759178, event is PAYMENT CANCELLATION
832 AP_ACCOUNTING_EVENTS_PKG.Create_Events ('PAYMENT CANCELLATION'
833 ,l_payment_type_flag -- Bug3343314
834 ,P_check_id
835 ,P_Reversal_date
836 ,l_accounting_event_id
837 ,P_checkrun_name
838 ,l_curr_calling_sequence);
839
840 IF ( l_payment_type_flag = 'R' ) THEN
841 l_transaction_type := 'REFUND CANCELLED';
842 ELSE
843 l_transaction_type := 'PAYMENT CANCELLED';
844 END IF;
845
846 -- Bug 5015973. Getting the reversal payment history id
847 -- Commented for Bug 6953346
848 /*
849 SELECT MAX(Payment_History_ID)
850 INTO l_rev_pmt_hist_id
851 FROM AP_Payment_History APH
852 WHERE APH.Check_ID = P_Check_ID
853 AND APH.Transaction_Type = 'PAYMENT CREATED';
854 */
855
856 -- Added for Bug 6953346
857
858 IF(l_transaction_type = 'PAYMENT CANCELLED') THEN
859 SELECT MAX(Payment_History_ID)
860 INTO l_rev_pmt_hist_id
861 FROM AP_Payment_History APH
862 WHERE APH.Check_ID = P_Check_ID
863 AND APH.Transaction_Type = 'PAYMENT CREATED';
864
865 ELSE
866 SELECT MAX(Payment_History_ID)
867 INTO l_rev_pmt_hist_id
868 FROM AP_Payment_History APH
869 WHERE APH.Check_ID = P_Check_ID
870 AND APH.Transaction_Type = 'REFUND RECORDED';
871
872 END IF;
873
874 -- End of Bug 6953346
875
876 -- Bug3343314
877 AP_RECONCILIATION_PKG.insert_payment_history
878 (
879 x_check_id => p_check_id,
880 x_transaction_type => l_transaction_type,
881 x_accounting_date => p_reversal_date,
882 x_trx_bank_amount => NULL,
883 x_errors_bank_amount => NULL,
884 x_charges_bank_amount => NULL,
885 x_bank_currency_code => NULL,
886 x_bank_to_base_xrate_type => NULL,
887 x_bank_to_base_xrate_date => NULL,
888 x_bank_to_base_xrate => NULL,
889 x_trx_pmt_amount => l_amount,
890 x_errors_pmt_amount => NULL,
891 x_charges_pmt_amount => NULL,
892 x_pmt_currency_code => l_currency_code,
893 x_pmt_to_base_xrate_type => l_exchange_rate_type,
894 x_pmt_to_base_xrate_date => l_exchange_date,
895 x_pmt_to_base_xrate => l_exchange_rate,
896 x_trx_base_amount => l_base_amount,
897 x_errors_base_amount => NULL,
898 x_charges_base_amount => NULL,
899 x_matched_flag => NULL,
900 x_rev_pmt_hist_id => l_rev_pmt_hist_id,
901 x_org_id => l_org_id, -- 4578865
902 x_creation_date => SYSDATE,
903 x_created_by => p_last_updated_by,
904 x_last_update_date => SYSDATE,
905 x_last_updated_by => p_last_updated_by,
906 x_last_update_login => p_last_update_login,
907 x_program_update_date => NULL,
908 x_program_application_id => NULL,
909 x_program_id => NULL,
910 x_request_id => NULL,
911 x_calling_sequence => l_curr_calling_sequence,
912 x_accounting_event_id => l_accounting_event_id
913 );
914 -- Commenting for bug 8236138
915 /*
916 Else
917
918 l_accounting_event_id := l_old_accounting_event_id;
919 End If; */
920
921 -- Events Project - end -------------------------------------------
922
923 -------------------------------------------------------------------
924 -- Hold invoices if necessary
925 --
926 IF (P_Invoice_Action = 'HOLD') THEN
927 l_debug_info := 'Hold invoices';
928
929 --Bug 4539462 collecting invoice_ids first
930 SELECT DISTINCT AIP.invoice_id
931 BULK COLLECT INTO l_dbi_key_value_list3
932 FROM ap_invoice_payments AIP
933 WHERE AIP.check_id = P_check_id
934 AND nvl(AIP.reversal_flag, 'N') <> 'Y'
935 AND NOT EXISTS
936 (SELECT 'Invoice already has this hold'
937 FROM ap_holds AH
938 WHERE AH.invoice_id = AIP.invoice_id
939 AND AH.hold_lookup_code = P_Hold_Code
940 AND AH.release_lookup_code IS NULL)
941 AND NOT EXISTS (SELECT 'Invoice is an Interest Invoice'
942 FROM ap_invoices AI
943 WHERE AI.invoice_id = AIP.invoice_id
944 AND AI.invoice_type_lookup_code = 'INTEREST');
945
946 /* Bug 5169128 */
947 OPEN C_Hold_Cur;
948 LOOP
949
950 FETCH C_hold_Cur INTO l_invoice_id_hold,
951 l_org_id_hold;
952
953 EXIT WHEN C_Hold_Cur%NOTFOUND;
954
955 l_hold_tab(l_invoice_id_hold).invoice_id := l_invoice_id_hold;
956 l_hold_tab(l_invoice_id_hold).org_id := l_org_id_hold;
957 Select AP_HOLDS_S.nextval
958 INTO l_hold_tab(l_invoice_id_hold).hold_id
959 From DUAL;
960
961 END LOOP;
962 CLOSE C_Hold_Cur;
963
964 FOR i in nvl(l_hold_tab.FIRST, 0) .. nvl(l_hold_tab.LAST, 0) LOOP
965
966 IF (l_hold_tab.exists(i)) THEN
967
968 INSERT INTO ap_holds_all
969 (invoice_id
970 ,hold_lookup_code
971 ,last_update_date
972 ,last_updated_by
973 ,held_by
974 ,hold_date
975 ,hold_reason
976 ,created_by
977 ,creation_date
978 ,org_id /* Bug 3700128. MOAC Project */
979 ,hold_id)
980 Values
981 (l_hold_tab(i).invoice_id
982 ,P_Hold_Code
983 ,sysdate
984 ,P_Last_Updated_By
985 ,P_Last_Updated_By
986 ,sysdate
987 ,P_Hold_Reason
988 ,P_Last_Updated_By
989 ,sysdate
990 ,l_hold_tab(i).org_id /* Bug 3700128. MOAC PRoject */
991 ,l_hold_tab(i).hold_id);
992
993 END IF;
994
995 END LOOP;
996
997 /* bug 5169128 */
998 --Bug 4539462 DBI logging
999 AP_DBI_PKG.Maintain_DBI_Summary
1000 (p_table_name => 'AP_HOLDS',
1001 p_operation => 'I',
1002 p_key_value_list => l_dbi_key_value_list3,
1003 p_calling_sequence => l_curr_calling_sequence);
1004
1005
1006 -- Events Project - 5 -------------------------------------------------
1007 -- Added call to AP_ACCOUNTING_EVENTS_PKG.Update_Events_Status
1008 -- so that if a posting_hold is placed on an invoice during payment
1009 -- void, and the invoice has not been accounted, the status of the
1010 -- invoice related event will change from 'CREATED' to 'INCOMPLETE'.
1011 -- --------------------------------------------------------------------
1012
1013 SELECT postable_flag,
1014 user_releaseable_flag, /* bug 5143826 */
1015 initiate_workflow_flag
1016 INTO l_postable_flag,
1017 l_user_releaseable_flag,
1018 l_initiate_workflow_flag
1019 FROM AP_HOLD_CODES AHC
1020 WHERE AHC.hold_lookup_code = P_Hold_code;
1021
1022 IF (nvl(l_postable_flag , 'N') = 'N') THEN
1023
1024 AP_ACCOUNTING_EVENTS_PKG.UPDATE_PAYMENT_EVENTS_STATUS -- Bug3343314
1025 (
1026 p_check_id => p_check_id,
1027 p_calling_sequence => l_curr_calling_sequence -- Bug3343314
1028 );
1029 End if;
1030
1031 /* bug 5143826 */
1032 IF (NVL(l_user_releaseable_flag, 'N') = 'Y' AND
1033 NVL(l_initiate_workflow_flag, 'N') = 'Y') THEN
1034
1035 FOR i in nvl(l_hold_tab.FIRST, 0) .. nvl(l_hold_tab.LAST, 0) LOOP
1036
1037 IF (l_hold_tab.exists(i)) THEN
1038
1039 AP_WORKFLOW_PKG.create_hold_wf_process(l_hold_tab(i).hold_id);
1040
1041 END IF;
1042
1043 END LOOP;
1044
1045 END IF;
1046
1047
1048 -------------------------------------------------------------------
1049 -- Or cancel invoices
1050 --
1051 -------------------------------------------------------------------
1052 -- Bug 8257752.
1053 -- Cancel invoice is now called after undo withholding.
1054 -------------------------------------------------------------------
1055 /*
1056 ELSIF (P_Invoice_Action = 'CANCEL') THEN
1057
1058 -----------------------------------------------------------------
1059 l_debug_info := 'Commit changes before cancelling invoices';
1060 -- 1828366, commenting out NOCOPY the commit because if the form fails
1061 -- the record will still get commited. Removing the commit
1062 -- below was not part of 1372660 (1828366 is a forward port of 1372660)
1063 -- COMMIT;
1064
1065 AP_CANCEL_PKG.AP_CANCEL_INVOICES(P_Check_Id,
1066 P_Last_Updated_By,
1067 P_Last_Update_Login,
1068 -- Base Line ARU
1069 -- l_set_of_books_id,
1070 P_Reversal_Date,
1071 -- Base Line ARU
1072 -- P_Reversal_Period_Name,
1073 P_Num_Cancelled,
1074 P_Num_Not_Cancelled,
1075 l_curr_calling_sequence);
1076 */
1077 END IF;
1078
1079
1080 l_debug_info := 'Open c_new_payments cursor';
1081 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1082 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1083 END IF;
1084
1085 -- -----------------------------------------------------------
1086
1087 OPEN c_new_payments;
1088
1089 LOOP
1090 -----------------------------------------------------------------
1091 l_debug_info := 'Fetch from c_new_payments cursor';
1092 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1094 END IF;
1095
1096 FETCH c_new_payments INTO rec_new_payments;
1097 EXIT WHEN c_new_payments%NOTFOUND;
1098
1099 -----------------------------------------------------------------
1100 -- Create reversing invoice payment
1101 --
1102 AP_PAY_INVOICE_PKG.AP_PAY_INVOICE
1103 (rec_new_payments.invoice_id
1104 ,P_Check_Id
1105 ,rec_new_payments.payment_num
1106 ,rec_new_payments.new_invoice_payment_id
1107 ,rec_new_payments.invoice_payment_id
1108 ,P_Reversal_Period_Name
1109 ,NULL
1110 ,P_Reversal_Date
1111 ,rec_new_payments.amount
1112 ,rec_new_payments.discount_taken
1113 ,rec_new_payments.discount_lost
1114 ,rec_new_payments.invoice_base_amount
1115 ,rec_new_payments.payment_base_amount
1116 ,'N'
1117 ,'N'
1118 ,'N'
1119 ,rec_new_payments.set_of_books_id
1120 ,P_Last_Updated_By
1121 ,P_Last_Update_Login
1122 ,NULL
1123 ,NULL
1124 ,rec_new_payments.exchange_rate
1125 ,rec_new_payments.exchange_rate_type
1126 ,rec_new_payments.exchange_date
1127 ,NULL
1128 ,NULL
1129 ,NULL
1130 ,NULL
1131 ,'N'
1132 ,NULL
1133 ,rec_new_payments.accts_pay_code_combination_id
1134 ,rec_new_payments.gain_code_combination_id
1135 ,rec_new_payments.loss_code_combination_id
1136 ,rec_new_payments.future_pay_code_combination_id
1137 ,NULL
1138 ,'Y'
1139 ,'REV'
1140 ,P_Replace_Flag
1141 ,rec_new_payments.attribute1
1142 ,rec_new_payments.attribute2
1143 ,rec_new_payments.attribute3
1144 ,rec_new_payments.attribute4
1145 ,rec_new_payments.attribute5
1146 ,rec_new_payments.attribute6
1147 ,rec_new_payments.attribute7
1148 ,rec_new_payments.attribute8
1149 ,rec_new_payments.attribute9
1150 ,rec_new_payments.attribute10
1151 ,rec_new_payments.attribute11
1152 ,rec_new_payments.attribute12
1153 ,rec_new_payments.attribute13
1154 ,rec_new_payments.attribute14
1155 ,rec_new_payments.attribute15
1156 ,rec_new_payments.attribute_category
1157 ,rec_new_payments.global_attribute1
1158 ,rec_new_payments.global_attribute2
1159 ,rec_new_payments.global_attribute3
1160 ,rec_new_payments.global_attribute4
1161 ,rec_new_payments.global_attribute5
1162 ,rec_new_payments.global_attribute6
1163 ,rec_new_payments.global_attribute7
1164 ,rec_new_payments.global_attribute8
1165 ,rec_new_payments.global_attribute9
1166 ,rec_new_payments.global_attribute10
1167 ,rec_new_payments.global_attribute11
1168 ,rec_new_payments.global_attribute12
1169 ,rec_new_payments.global_attribute13
1170 ,rec_new_payments.global_attribute14
1171 ,rec_new_payments.global_attribute15
1172 ,rec_new_payments.global_attribute16
1173 ,rec_new_payments.global_attribute17
1174 ,rec_new_payments.global_attribute18
1175 ,rec_new_payments.global_attribute19
1176 ,rec_new_payments.global_attribute20
1177 ,rec_new_payments.global_attribute_category
1178 ,l_curr_calling_sequence
1179 ,l_accounting_event_id -- Events Project - 6
1180 ,rec_new_payments.org_id /* Bug 4759178, passed org_id */
1181 );
1182
1183 --Bug695340: Update the assets flag based on the old assets
1184 -- flag value.
1185 -- If the old was: U, N, or Y then set the new to U.
1186 -- Otherwise keep it as NULL.
1187 if rec_new_payments.assets_addition_flag is not null then
1188 UPDATE ap_invoice_payments
1189 SET assets_addition_flag = 'U'
1190 WHERE invoice_payment_id =
1191 rec_new_payments.new_invoice_payment_id;
1192 end if;
1193
1194 -----------------------------------------------------------------
1195 -- Undo any withholding taken at payment time
1196 --
1197
1198 IF l_netting_type <> 'N' THEN
1199 AP_WITHHOLDING_PKG.Ap_Undo_Withholding
1200 (rec_new_payments.invoice_payment_id
1201 ,'VOID PAYMENT'
1202 ,P_Reversal_Date
1203 ,rec_new_payments.new_invoice_payment_id
1204 ,P_Last_Updated_By
1205 ,P_Last_Update_Login
1206 ,NULL
1207 ,NULL
1208 ,NULL
1209 ,l_success
1210 );
1211 END IF;
1212
1213 END LOOP;
1214
1215 -- bug9441420, added the below code for marking
1216 -- any prepayment application and unapplication
1217 -- event reversal pair to N/U status
1218 --
1219
1220 l_debug_info := 'Beginning to mark the Prepayment Application/Unapplication '||
1221 'events to N/U status';
1222 OPEN prepay_appl;
1223 LOOP
1224 FETCH prepay_appl INTO l_invoice_id;
1225 EXIT WHEN prepay_appl%NOTFOUND;
1226
1227 l_debug_info := 'before calling Set_Prepay_Event_Noaction api for invoice '||
1228 ' id : '||l_invoice_id;
1229 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1231 END IF;
1232
1233 AP_ACCOUNTING_EVENTS_PKG.Set_Prepay_Event_Noaction
1234 (p_invoice_id => l_invoice_id,
1235 p_calling_sequence => l_curr_calling_sequence);
1236
1237 END LOOP;
1238
1239 -------------------------------------------------------------------
1240 -- Bug 8257752.
1241 -- Cancel invoice is now called after undo withholding.
1242 -------------------------------------------------------------------
1243
1244 IF (P_Invoice_Action = 'CANCEL') THEN
1245
1246 -----------------------------------------------------------------
1247 l_debug_info := 'Commit changes before cancelling invoices';
1248 -- 1828366, commenting out NOCOPY the commit because if the form fails
1249 -- the record will still get commited. Removing the commit
1250 -- below was not part of 1372660 (1828366 is a forward port of 1372660)
1251 -- COMMIT;
1252
1253 AP_CANCEL_PKG.AP_CANCEL_INVOICES(P_Check_Id,
1254 P_Last_Updated_By,
1255 P_Last_Update_Login,
1256 /* Base Line ARU */
1257 -- l_set_of_books_id,
1258 P_Reversal_Date,
1259 /* Base Line ARU */
1260 -- P_Reversal_Period_Name,
1261 P_Num_Cancelled,
1262 P_Num_Not_Cancelled,
1263 l_curr_calling_sequence);
1264 END IF;
1265
1266 -- Events Project - 7 ---------------------------------------------
1267 -- Now that interest invoices and AWT distributions have been
1268 -- created, we want to stamp the accounting_event_id of the Payment
1269 -- event on the AWT and interest invoice distributions.
1270 -- ----------------------------------------------------------------
1271
1272 IF l_netting_type <> 'N' THEN
1273 AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
1274 (
1275 p_event_type => 'PAYMENT CANCELLED',
1276 p_check_id => p_check_id,
1277 p_event_id => l_accounting_event_id,
1278 p_calling_sequence => l_curr_calling_sequence
1279 );
1280 END IF;
1281
1282 ---------------------------------------------------------------------
1283
1284 l_debug_info := 'Close c_new_payments cursor';
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 CLOSE c_new_payments;
1290
1291 -------------------------------------------------------------------
1292 -- Delete any temporary records in AP_SELECTED_INVOICES
1293 -- if this is a Quickcheck as the
1294 -- format program could have bombed and the user just decided
1295 -- to void it all
1296 --
1297 l_debug_info := 'Delete from ap_selected_invoices';
1298 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1299 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1300 END IF;
1301
1302
1303 IF l_netting_type <> 'N' THEN
1304
1305 BEGIN
1306
1307 DELETE FROM ap_selected_invoices
1308 WHERE checkrun_name = P_Checkrun_Name;
1309
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 NULL;
1313 END;
1314 END IF;
1315
1316 -----------------------------------------------------------------------
1317 -- In case the procedure has been called to Reverse a Netting Request
1318 -- then the check status should also be updated to 'VOIDED', and the
1319 -- Void Date should be populated as the sysdate
1320 -- This is not Required in the case of Quick Checks or Manual Checks as
1321 -- because the form takes care of populating these fields.
1322 -- bug6634891
1323
1324 IF l_netting_type = 'N' THEN
1325
1326 UPDATE ap_checks_all
1327 SET status_lookup_code = 'VOIDED',
1328 void_date = P_reversal_date
1329 WHERE check_id = p_check_id;
1330
1331 END IF;
1332
1333
1334 IF (p_calling_module <> 'IBY') AND (l_payment_type_flag NOT IN ('R','N'))
1335 THEN
1336
1337 l_debug_info := 'Selecting the IBY payment id from ap_checks_all';
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 BEGIN
1343 SELECT payment_id
1344 INTO l_payment_id
1345 FROM AP_CHECKS_ALL
1346 WHERE check_id = p_check_id;
1347 EXCEPTION
1348 WHEN NO_DATA_FOUND THEN
1349 null;
1350 END ;
1351
1352 IF l_payment_id IS NOT NULL THEN
1353
1354 l_debug_info := 'Calling IBY API to synchronize IBY Data';
1355 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1356 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1357 END IF;
1358
1359 IBY_DISBURSE_UI_API_PUB_PKG.Void_Payment
1360 (p_api_version => 1.0,
1361 p_init_msg_list => FND_API.G_FALSE,
1362 p_pmt_id => l_payment_id,
1363 p_voided_by => p_last_updated_by,
1364 p_void_date => p_reversal_date,
1365 p_void_reason => 'Oracle Payables',
1366 x_return_status => l_return_status,
1367 x_msg_count => l_msg_count,
1368 x_msg_data => l_msg_data);
1369
1370 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1371 FOR I IN 1..l_msg_count
1372 LOOP
1373 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I
1374 ,p_encoded => 'T');
1375 FND_MESSAGE.SET_ENCODED(l_error_msg);
1376 END LOOP;
1377 APP_EXCEPTION.RAISE_EXCEPTION;
1378 END IF;
1379
1380 END IF;
1381
1382 END IF;
1383 -------------------------------------------------------------------
1384 --1372660/1828366 removing the commit because if the form fails, the
1385 --record will still get commited possibly.
1386 --l_debug_info := 'Commit changes to database';
1387 --COMMIT;
1388
1389 EXCEPTION
1390 /* Added for IL BOE project - Bug 11661879 */
1391 WHEN l_boe_excp THEN
1392 x_return_status := 'E';
1393
1394 WHEN OTHERS THEN
1395 IF (SQLCODE <> -20001) THEN
1396 IF p_calling_module <> 'IBY' THEN
1397 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1398 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1399 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1400 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1401 ', CHECK_ID = ' || TO_CHAR(P_Check_Id)
1402 ||', REPLACE_FLAG = ' || P_Replace_Flag
1403 ||', REVERSAL_DATE = ' || TO_CHAR(P_Reversal_Date)
1404 ||', PERIOD_NAME = ' || P_Reversal_Period_Name
1405 ||', CHECKRUN_NAME = ' || P_Checkrun_Name
1406 ||', INVOICE_ACTION = ' || P_Invoice_Action
1407 ||', HOLD_CODE = ' || P_Hold_Code
1408 ||', HOLD_REASON = ' || P_Hold_Reason
1409 ||', LAST_UPDATED_BY = ' || TO_CHAR(P_Last_Updated_By)
1410 ||', LAST_UPDATED_LOGIN = '|| TO_CHAR(P_Last_Update_Login));
1411 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_Debug_Info);
1412 ELSE
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 FND_MSG_PUB.Count_And_Get
1415 (p_count => x_msg_count,
1416 p_data => x_msg_data
1417 );
1418 END IF;
1419 END IF;
1420 APP_EXCEPTION.RAISE_EXCEPTION;
1421
1422 END Ap_Reverse_Check;
1423
1424 /* New procedure to be used by Oracle Payments
1425 during voiding of payments from their UI */
1426
1427 PROCEDURE Iby_Void_Check
1428 (p_api_version IN NUMBER,
1429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1430 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1431 p_payment_id IN NUMBER,
1432 p_void_date IN DATE,
1433 x_return_status OUT NOCOPY VARCHAR2,
1434 x_msg_count OUT NOCOPY VARCHAR2,
1435 x_msg_data OUT NOCOPY VARCHAR2)
1436 IS
1437 l_api_name CONSTANT VARCHAR2(30) := 'Iby_Void_Check';
1438 l_api_version CONSTANT NUMBER := 1.0;
1439
1440 l_return_status VARCHAR2(10);
1441 l_msg_count NUMBER;
1442 l_msg_data VARCHAR2(2000);
1443 l_user_id NUMBER;
1444 l_login_id NUMBER;
1445 l_reversal_date DATE;
1446 l_reversal_period_name VARCHAR2(240);
1447 l_num_cancelled NUMBER;
1448 l_num_not_cancelled NUMBER;
1449 l_gl_date DATE;
1450 l_check_id NUMBER;
1451 -- bug# 6643035 l_checkrun_name is changed to database column type
1452 l_checkrun_name ap_checks_all.checkrun_name%type;
1453 l_org_id NUMBER;
1454 l_debug_info VARCHAR2(2000);
1455
1456 BEGIN
1457
1458 l_debug_info := 'Checking API Compatibility';
1459 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1460 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1461 END IF;
1462
1463 -- Standard call to check for call compatibility.
1464 IF NOT FND_API.Compatible_API_Call (l_api_version,
1465 p_api_version,
1466 l_api_name,
1467 G_PKG_NAME )
1468 THEN
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END IF;
1471
1472 FND_MSG_PUB.initialize;
1473
1474 l_debug_info := 'Payment_id from IBY API: '||p_payment_id;
1475 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1476 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1477 END IF;
1478
1479
1480 l_user_id := FND_GLOBAL.USER_ID;
1481 l_login_id := FND_GLOBAL.LOGIN_ID;
1482 -- Initialize API return status to success
1483 x_return_status := FND_API.G_RET_STS_SUCCESS;
1484
1485 l_debug_info := 'Deriving check_id, org_id';
1486 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1488 END IF;
1489
1490 BEGIN
1491 SELECT check_id,
1492 checkrun_id,
1493 org_id
1494 INTO l_check_id,
1495 l_checkrun_name,
1496 l_org_id
1497 FROM AP_CHECKS_ALL
1498 WHERE payment_id = p_payment_id;
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND THEN
1501 l_check_id := NULL;
1502 l_checkrun_name := NULL;
1503 l_org_id := NULL;
1504 END;
1505
1506 l_debug_info := 'Derived Check_Id: '||l_check_id;
1507 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1508 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1509 END IF;
1510
1511 IF l_org_id IS NOT NULL THEN
1512 AP_UTILITIES_PKG.Get_Only_Open_Gl_Date
1513 (p_date => p_void_date,
1514 p_period_name => l_reversal_period_name,
1515 p_gl_date => l_gl_date,
1516 p_org_id => l_org_id);
1517 END IF;
1518
1519 l_debug_info := 'l_reversal_period_name: '||l_reversal_period_name;
1520 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1522 END IF;
1523
1524 IF l_reversal_period_name IS NULL THEN
1525 x_return_status := FND_API.G_RET_STS_ERROR ;
1526 FND_MESSAGE.Set_Name('SQLAP','AP_NO_OPEN_PERIOD');
1527 FND_MSG_PUB.Count_And_Get(
1528 p_count => x_msg_count,
1529 p_data => x_msg_data
1530 );
1531 ELSE
1532 IF l_gl_date > p_void_date THEN
1533 l_reversal_date := l_gl_date;
1534 ELSE
1535 l_reversal_date := p_void_date;
1536 END IF;
1537
1538 l_debug_info := 'Calling Ap_Reverse_Check';
1539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1541 END IF;
1542
1543 Ap_Reverse_Check
1544 (p_check_id => l_check_id,
1545 p_replace_flag => 'N',
1546 p_reversal_date => l_reversal_date,
1547 p_reversal_period_name => l_reversal_period_name,
1548 p_checkrun_name => l_checkrun_name,
1549 p_invoice_action => NULL,
1550 p_hold_code => NULL,
1551 p_hold_reason => NULL,
1552 P_Sys_Auto_Calc_Int_Flag => NULL,
1553 P_Vendor_Auto_Calc_Int_Flag => NULL,
1554 P_Last_Updated_By => l_user_id,
1555 P_Last_Update_Login => l_login_id,
1556 P_Num_Cancelled => l_num_cancelled,
1557 P_Num_Not_Cancelled => l_num_not_cancelled,
1558 P_Calling_Module => 'IBY',
1559 P_Calling_Sequence => 'AP_VOID_PKG.Iby_Void_Check',
1560 x_return_status => x_return_status,
1561 X_msg_count => X_msg_count,
1562 X_msg_data => X_msg_data);
1563
1564 l_debug_info := 'Return Status from Ap_Reverse_Check: '||x_return_status;
1565 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1566 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1567 END IF;
1568
1569 /* Bug 5407058 */
1570 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1571 UPDATE AP_CHECKS_ALL
1572 SET status_lookup_code = 'VOIDED'
1573 ,void_date = l_reversal_date
1574 WHERE check_id = l_check_id;
1575 END IF;
1576
1577 END IF;
1578
1579 EXCEPTION
1580
1581 WHEN FND_API.G_EXC_ERROR THEN
1582 x_return_status := FND_API.G_RET_STS_ERROR ;
1583 FND_MSG_PUB.Count_And_Get
1584 ( p_count => x_msg_count,
1585 p_data => x_msg_data
1586 );
1587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1589 FND_MSG_PUB.Count_And_Get
1590 ( p_count => x_msg_count,
1591 p_data => x_msg_data
1592 );
1593 WHEN OTHERS THEN
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1595 IF FND_MSG_PUB.Check_Msg_Level
1596 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1597 FND_MSG_PUB.Add_Exc_Msg
1598 ( G_PKG_NAME,
1599 l_api_name
1600 );
1601 END IF;
1602 FND_MSG_PUB.Count_And_Get
1603 ( p_count => x_msg_count,
1604 p_data => x_msg_data
1605 );
1606
1607 END Iby_Void_Check;
1608
1609 END AP_VOID_PKG;