1 PACKAGE BODY AP_CANCEL_PKG AS
2 /* $Header: apicancb.pls 120.58 2011/02/24 05:07:06 mayyalas ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_CANCEL_PKG.';
12
13 -- Forward declaration of Payment Request Cancellation Subscription event
14 -- procedure. This will be only called if Payment Request is being cancelled
15 -- From Oracle Payables.
16 PROCEDURE Subscribe_To_Cancel_Event
17 (P_Event_Type IN VARCHAR2,
18 P_Invoice_ID IN NUMBER,
19 P_Application_ID IN NUMBER,
20 P_Return_Status OUT NOCOPY VARCHAR2,
21 P_Msg_Count OUT NOCOPY NUMBER,
22 P_Msg_Data OUT NOCOPY VARCHAR2,
23 P_Calling_Sequence IN VARCHAR2);
24
25 /*=============================================================================
26 | PROCEDURE Is_Invoice_Cancellable
27 |
28 | Check if the line is cancellable
29 |
30 | PROGRAM FLOW
31 | 0. If invoice contains distribution that does not have open GL period
32 | return FALSE
33 | 1. If invoice has an effective payment, return FALSE
34 | 2. If invoice is selected for payment, return FALSE
35 | 3. If invoice is already cancelled, return FALSE
36 | 4. If invoice is credited invoice, return FALSE
37 | 5. If invoices have been applied against this invoice, return FALSE
38 | 6. If invoice is matched to Finally Closed PO's, return FALSE
39 | 7. If project related invoices have pending adjustments, return FALSE
40 | 8. If cancelling will cause qty_billed or amount_billed to less
41 | than 0, return FALSE
42 | 9. If none of above, invoice is cancellable return Ture
43 | NOTES
44 |
45 | MODIFICATION HISTORY
46 | Date Author Description of Change
47 |
48 *============================================================================*/
49 Function Is_Invoice_Cancellable(
50 P_invoice_id IN NUMBER,
51 P_error_code OUT NOCOPY VARCHAR2, /* Bug 5300712 */
52 P_debug_info IN OUT NOCOPY VARCHAR2,
53 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
54 IS
55
56 CURSOR verify_no_pay_batch IS
57 SELECT checkrun_id
58 FROM ap_payment_schedules
59 WHERE invoice_id = P_invoice_id
60 FOR UPDATE NOWAIT;
61
62 -- Bug5497058
63 CURSOR qty_per_dist_negtive_count_cur IS
64 SELECT count(*)
65 FROM ap_invoice_distributions AID,
66 po_distributions_ap_v POD,
67 po_line_locations PLL,
68 po_lines PL,
69 ap_invoices AIV
70 WHERE POD.po_distribution_id = AID.po_distribution_id
71 AND POD.line_location_id = PLL.line_location_id
72 AND PLL.po_line_id = PL.po_line_id
73 AND AIV.invoice_id=AID.invoice_id
74 AND NVL(AID.reversal_flag, 'N') <> 'Y'
75 AND AID.invoice_id = P_invoice_id
76 -- Bug 5590826. For amount related decode
77 AND AID.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'IPV')
78 HAVING (DECODE(AIV.invoice_type_lookup_code,'PREPAYMENT',
79 SUM(NVL(POD.quantity_financed, 0)),SUM(NVL(POD.quantity_billed, 0))
80 ) -
81 SUM(round(decode(AID.dist_match_type,
82 'PRICE_CORRECTION', 0,
83 'AMOUNT_CORRECTION', 0,
84 'ITEM_TO_SERVICE_PO', 0,
85 'ITEM_TO_SERVICE_RECEIPT', 0,
86 nvl( AID.quantity_invoiced, 0 ) +
87 nvl( AID.corrected_quantity,0 )
88 ) *
89 po_uom_s.po_uom_convert(AID.matched_uom_lookup_code, --bug5844328
90 nvl(PLL.unit_meas_lookup_code,
91 PL.unit_meas_lookup_code),
92 PL.item_id), 15)
93 ) < 0
94 OR DECODE(AIV.invoice_type_lookup_code,'PREPAYMENT',
95 SUM(NVL(POD.amount_financed, 0)),SUM(NVL(POD.amount_billed, 0))) -
96 SUM(NVL(AID.amount, 0)) < 0 )
97 GROUP BY AIV.invoice_type_lookup_code,AID.po_distribution_id;
98
99
100 CURSOR dist_gl_date_cur IS
101 SELECT accounting_date
102 FROM ap_invoice_distributions AID
103 WHERE AID.invoice_id = P_invoice_id
104 AND NVL(AID.reversal_flag, 'N') <> 'Y';
105
106 TYPE date_tab is TABLE OF DATE INDEX BY BINARY_INTEGER;
107 l_gl_date_list date_tab;
108 i BINARY_INTEGER := 1;
109 l_open_gl_date DATE :='';
110 l_open_period gl_period_statuses.period_name%TYPE := '';
111
112 l_curr_calling_sequence VARCHAR2(2000);
113 l_debug_info VARCHAR2(100):= 'Is_Invoice_Cancellable';
114
115 l_checkrun_id NUMBER;
116 l_cancel_count NUMBER := 0;
117 l_project_related_count NUMBER := 0;
118 l_payment_count NUMBER := 0;
119 l_final_close_count NUMBER := 0;
120 l_prepay_applied_flag VARCHAR2(1);
121 l_po_dist_count NUMBER := 0;
122 l_credited_inv_flag BOOLEAN := FALSE;
123 l_pa_message_name VARCHAR2(50);
124 l_org_id NUMBER;
125 l_final_closed_shipment_count NUMBER;
126 l_allow_cancel VARCHAR2(1) := 'Y';
127 l_return_code VARCHAR2(30);
128 l_enc_enabled VARCHAR2(1); --Bug6009101
129 l_po_not_approved VARCHAR2(1); --Bug6009101
130
131
132 BEGIN
133 l_curr_calling_sequence := 'AP_INVOICE_PKG.IS_INVOICE_CANCELLABLE<-' ||
134 P_calling_sequence;
135
136 /*-----------------------------------------------------------------+
137 | Step 0 - If invoice contain distribtuion which does not have |
138 | OPEN gl period name, return FALSE |
139 +-----------------------------------------------------------------*/
140 /* bug 4942638. Move the next select here */
141 l_debug_info := 'Get the org_id for the invoice';
142
143 SELECT org_id
144 INTO l_org_id
145 FROM ap_invoices_all
146 WHERE invoice_id = p_invoice_id;
147
148 l_debug_info := 'Check if inv distribution has open period';
149
150 OPEN dist_gl_date_Cur;
151 FETCH dist_gl_date_Cur
152 BULK COLLECT INTO l_gl_date_list;
153 CLOSE dist_gl_date_Cur;
154
155 /* Bug 5354259. Added the following IF condition as for
156 For unvalidated invoice case most of the cases there wil be no distributions */
157 IF l_gl_date_list.count > 0 THEN
158 FOR i in l_gl_date_list.FIRST..l_gl_date_list.LAST
159 LOOP
160 /* bug 4942638. Added l_org_id in the next two function call */
161 l_open_period := ap_utilities_pkg.get_current_gl_date(l_gl_date_list(i), l_org_id);
162 IF ( l_open_period IS NULL ) THEN
163 ap_utilities_pkg.get_open_gl_date(
164 l_gl_date_list(i),
165 l_open_period,
166 l_open_gl_date,
167 l_org_id);
168 IF ( l_open_period IS NULL ) THEN
169 p_error_code := 'AP_DISTS_NO_OPEN_FUT_PERIOD';
170 p_debug_info := l_debug_info;
171 RETURN FALSE;
172 END IF;
173 END IF;
174 END LOOP;
175 END IF;
176
177 /*-----------------------------------------------------------------+
178 | Step 1 - If invoice has an effective payment, return FALSE |
179 | This include the check of if invoice itself is a |
180 | PREPAYMENT type invoice - Actively referenced |
181 | prepayment type invoice has to be fully paid when it |
182 | is applied. |
183 +-----------------------------------------------------------------*/
184
185 l_debug_info := 'Check if invoice has an effective payment';
186 --Bug10034598: Commected below query
187 /* SELECT count(*)
188 INTO l_payment_count
189 FROM ap_invoice_payments P,ap_payment_schedules PS
190 WHERE P.invoice_id=PS.invoice_id
191 AND P.invoice_id = P_invoice_id
192 AND PS.payment_status_flag <> 'N'
193 AND nvl(P.reversal_flag,'N') <> 'Y'
194 AND P.amount is not NULL
195 AND exists ( select 'non void check'
196 from ap_checks A
197 where A.check_id = P.check_id
198 and void_date is null);*/--Bug 6135172
199 --Bug10034598 : Removed join with ap_payment_schedules
200 SELECT COUNT(*)
201 INTO l_payment_count
202 FROM ap_invoice_payments P
203 WHERE P.invoice_id = P_invoice_id
204 AND NVL(P.reversal_flag,'N') <> 'Y'
205 AND P.amount IS NOT NULL
206 AND EXISTS
207 (SELECT 'non void check'
208 FROM ap_checks A
209 WHERE A.check_id = P.check_id
210 AND void_date IS NULL
211 );
212
213 IF ( l_payment_count <> 0 ) THEN
214 P_error_code := 'AP_INV_CANCEL_EFF_PAYMENT';
215 P_debug_info := l_debug_info;
216 RETURN FALSE;
217 END IF;
218
219 /*-----------------------------------------------------------------+
220 | Step 2. If invoice is selected for payment, return FALSE |
221 +-----------------------------------------------------------------*/
222
223 l_debug_info := 'Check if invoice is selected for payment';
224
225 BEGIN
226 OPEN verify_no_pay_batch;
227 LOOP
228 FETCH verify_no_pay_batch
229 INTO l_checkrun_id;
230 EXIT WHEN verify_no_pay_batch%NOTFOUND;
231 IF l_checkrun_id IS NOT NULL THEN
232 P_error_code := 'AP_INV_CANCEL_SEL_PAYMENT';
233 P_debug_info := l_debug_info || 'with no check run id';
234 COMMIT;
235 RETURN FALSE;
236 END IF;
237 END LOOP;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF ( verify_no_pay_batch%ISOPEN ) THEN
242 CLOSE verify_no_pay_batch;
243 END IF;
244 P_error_code := 'AP_INV_CANCEL_PS_LOCKED';
245 P_debug_info := l_debug_info || 'With exceptions';
246 COMMIT;
247 RETURN FALSE;
248 END;
249
250 /*-----------------------------------------------------------------+
251 | Step 3. If invoice is already cancelled, return FALSE |
252 +-----------------------------------------------------------------*/
253
254 l_debug_info := 'Check if invoice is already cancelled';
255
256 SELECT count(*)
257 INTO l_cancel_count
258 FROM ap_invoices
259 WHERE invoice_id = P_invoice_id
260 AND cancelled_date IS NOT NULL;
261
262 IF (l_cancel_count > 0) THEN
263 P_error_code := 'AP_INV_CANCEL_ALREADY_CNCL';
264 P_debug_info := l_debug_info;
265 RETURN FALSE;
266 END IF;
267
268 /*-----------------------------------------------------------------+
269 | Step 4. If invoice is a credited invoice return FALSE |
270 +-----------------------------------------------------------------*/
271 l_debug_info := 'Check if invoice is a credited invoice';
272
273 l_credited_inv_flag := AP_INVOICES_UTILITY_PKG.Is_Inv_Credit_Referenced(
274 P_invoice_id);
275
276 IF (l_credited_inv_flag <> FALSE ) THEN
277 P_error_code := 'AP_INV_IS_CREDITED_INV';
278 P_debug_info := l_debug_info;
279 RETURN FALSE;
280 END IF;
281
282 /*-----------------------------------------------------------------+
283 | Step 5. If invoices have been applied against this invoice |
284 | return FALSE |
285 +-----------------------------------------------------------------*/
286
287 l_debug_info := 'Check if invoices have been applied against this invoice';
288
289 l_prepay_applied_flag :=
290 AP_INVOICES_UTILITY_PKG.get_prepayments_applied_flag(P_invoice_id);
291
292 IF (nvl(l_prepay_applied_flag,'N') = 'Y') THEN
293 P_error_code := 'AP_INV_PP_NO_CANCEL';
294 P_debug_info := l_debug_info;
295 RETURN FALSE;
296 END IF;
297
298 /*-----------------------------------------------------------------+
299 | Step 6. If invoice is matched to a Finally Closed PO, return |
300 | FALSE |
301 +-----------------------------------------------------------------*/
302
303 l_debug_info := 'Check if invoice is matched to a finally closed PO';
304
305 -- Bug fix:3488316
306 -- Following code in this step has been added for JFMIP related work.
307 -- This code has been modified only for federal customers, before modifying
308 -- this code please get the code verified with the developer/manager
309 -- who added this code.
310 /* bug 4942638. Move the next select for l_org_id at the begining */
311
312 IF (FV_INSTALL.ENABLED (l_org_id)) THEN
313
314 BEGIN
315
316 SELECT 'N'
317 INTO l_allow_cancel
318 FROM ap_invoice_distributions AID,
319 po_distributions PD,
320 po_line_locations pll
321 WHERE aid.invoice_id = p_invoice_id
322 --AND aid.final_match_flag in ('N','Y') For Bug 3489536
323 AND aid.po_distribution_id = pd.po_distribution_id
324 AND pll.line_location_id = pd.line_location_id
325 AND decode(pll.final_match_flag, 'Y', 'D', aid.final_match_flag) in ('N','Y') --Bug 3489536
326 AND pll.closed_code = 'FINALLY CLOSED'
327 AND rownum = 1;
328
329 IF (l_allow_cancel = 'N') THEN
330 P_error_code := 'AP_INV_CANNOT_OPEN_SHIPMENT';
331 P_debug_info := l_debug_info;
332 RETURN(FALSE);
333 END IF;
334
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337
338 SELECT count(distinct pll.line_location_id)
339 INTO l_final_closed_shipment_count
340 FROM ap_invoice_distributions aid,
341 po_line_locations pll,
342 po_distributions pd
343 WHERE aid.invoice_id = p_invoice_id
344 AND aid.po_distribution_id = pd.po_distribution_id
345 AND pd.line_location_id = pll.line_location_id
346 --AND aid.final_match_flag = 'D' For bug 3489536
347 AND decode(pll.final_match_flag, 'Y', 'D', aid.final_match_flag) = 'D' --Bug 3489536
348 AND pll.closed_code = 'FINALLY CLOSED';
349
350 END ;
351
352 IF (l_final_closed_shipment_count > 1) THEN
353
354 P_error_code := 'AP_INV_MUL_SHIP_FINALLY_CLOSED' ;
355 P_debug_info := l_debug_info;
356 RETURN(FALSE);
357
358 END IF;
359
360 IF (l_final_closed_shipment_count = 1) THEN
361
362 l_debug_info := 'Open the Finally Closed PO Shipment ';
363 IF(NOT(FV_AP_CANCEL_PKG.OPEN_PO_SHIPMENT(p_invoice_id,
364 l_return_code))) THEN
365
366 P_error_code := 'AP_INV_CANNOT_OPEN_SHIPMENT';
367 P_debug_info := l_debug_info;
368 RETURN(FALSE);
369
370 END IF;
371
372 END IF;
373
374 ELSE
375
376
377 SELECT count(*)
378 INTO l_final_close_count
379 FROM ap_invoice_lines AIL,
380 po_line_locations_ALL PL
381 WHERE AIL.invoice_id = P_invoice_id
382 AND AIL.po_line_location_id = PL.line_location_id
383 AND AIL.org_id = PL.org_id
384 AND PL.closed_code = 'FINALLY CLOSED';
385
386 IF (l_final_close_count > 0) THEN
387 P_error_code := 'AP_INV_PO_FINALLY_CLOSED';
388 P_debug_info := l_debug_info;
389 RETURN FALSE;
390 END IF;
391 END IF;
392 /*-----------------------------------------------------------------+
393 | Step 7. If projects have pending adjustments then return FALSE |
394 +-----------------------------------------------------------------*/
395 --
396 -- Bug 5349193
397 -- As suggested in the bug, this validation is commented in R12.
398 --
399
400 /* SELECT count(*)
401 INTO l_project_related_count
402 FROM ap_invoices AI
403 WHERE AI.invoice_id = P_invoice_id
404 AND (AI.project_id is not null OR
405 exists (select 'X'
406 from ap_invoice_distributions AIL
407 where AIL.invoice_id = AI.invoice_id
408 and project_id is not null) OR
409 exists (select 'X'
410 from ap_invoice_distributions AID
411 where AID.invoice_id = AI.invoice_id
412 and project_id is not null));
413
414 IF (l_project_related_count <> 0) THEN
415 l_pa_message_name := pa_integration.pending_vi_adjustments_exists(
416 P_invoice_id);
417 IF (l_pa_message_name <> 'N') THEN
418 P_error_code := l_pa_message_name;
419 P_debug_info := l_debug_info;
420 RETURN FALSE;
421 END IF;
422 END IF; */
423
424 /*-----------------------------------------------------------------+
425 | Step 8. if the quantity billed and amount on PO would be |
426 | reduced to less than zero then return FALSE |
427 | Always allow Reversal distributions to be cancelled |
428 +-----------------------------------------------------------------*/
429
430 BEGIN
431
432 OPEN qty_per_dist_negtive_count_cur;
433 FETCH qty_per_dist_negtive_count_cur
434 INTO l_po_dist_count;
435 CLOSE qty_per_dist_negtive_count_cur;
436
437 END;
438
439 IF ( l_po_dist_count > 0 ) THEN
440 P_error_code := 'AP_INV_PO_CANT_CANCEL';
441 P_debug_info := l_debug_info;
442 RETURN FALSE;
443 END IF;
444
445 /*-----------------------------------------------------------------+
446 | Step 9. if the invoice is matched to an unapproved PO, if the
447 | encumbrance is on, then do not allow the invoice
448 | cancellation (bug6009101)
449 *-----------------------------------------------------------------*/
450
451 l_debug_info := 'Check if the PO is unapproved';
452
453 SELECT NVL(purch_encumbrance_flag,'N')
454 INTO l_enc_enabled
455 FROM financials_system_params_all
456 WHERE NVL(org_id, -99) = NVL(l_org_id, -99);
457
458 if l_enc_enabled = 'Y' then
459
460 begin
461
462 select 'Y'
463 into l_po_not_approved
464 from po_headers POH,
465 po_distributions POD,
466 ap_invoice_distributions AID,
467 ap_invoices AI
468 where AI.invoice_id = AID.invoice_id
469 and AI.invoice_id = P_invoice_id
470 and AID.po_distribution_id = POD.po_distribution_id
471 and POD.po_header_id = POH.po_header_id
472 and POH.approved_flag <> 'Y'
473 and rownum = 1;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 NULL;
478
479 end;
480
481 if l_po_not_approved = 'Y' then
482 p_error_code := 'AP_PO_UNRES_CANT_CANCEL';
483 p_debug_info := l_debug_info;
484 return FALSE;
485 end if;
486 end if;
487
488
489 p_error_code := null;
490 P_debug_info := l_debug_info;
491 RETURN TRUE;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 IF (SQLCODE <> -20001) THEN
496 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
497 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
498 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
499 FND_MESSAGE.SET_TOKEN('PARAMETERS',
500 ' P_invoice_id = ' || P_invoice_id );
501 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
502 END IF;
503
504 IF (qty_per_dist_negtive_count_cur%ISOPEN ) THEN
505 CLOSE qty_per_dist_negtive_count_cur;
506 END IF;
507
508 IF ( dist_gl_date_cur%ISOPEN ) THEN
509 CLOSE dist_gl_date_cur;
510 END IF;
511
512 P_debug_info := l_debug_info || 'With exceptions';
513 RETURN FALSE;
514
515 END Is_Invoice_Cancellable;
516
517
518 /*=============================================================================
519 | Function Ap_Cancel_Single_Invoice
520 |
521 | cancels one invoice by executing the following sequence of steps,
522 | returning TRUE if successful and FALSE otherwise.
523 |
524 | PROGRAM FLOW
525 |
526 | 1. check if invoice cancellable, if yes, proceed otherwise return false
527 | 3.(If invoice has had tax withheld, undo withholding) - commented
528 | 4. Clear out payment schedules
529 | 5. Cancel all the non-discard lines
530 | a. reverse matching
531 | b. fetch the maximum distribution line number
532 | c. Set encumbered flags to 'N'
533 | d. Accounting event generation
534 | e. reverse the distributions
535 | f. update Line level Cancelled information
536 | 6. Zero out the Invoice
537 | 7. Run AutoApproval for this invoice
538 | 8. check posting holds remain on this canncelled invoice
539 | a. if NOT exist - complete the cancellation by updating header
540 | level information set return value to TRUE
541 | b. if exist - no update, set the return valuse to FALSE, NO
542 | DATA rollback.
543 | 9. Commit Data
544 | 10. Populate the out parameters.
545 |
546 | NOTES
547 | 1. bug2328225 case of Matching a special charge only invoice to
548 | receipt so we check if the quantity invoiced is not null too
549 | 2. Events Project
550 | We no longer need to prevent the cancellation of an invoice
551 | just because the accounting of related payments has not been
552 | created. Therefore, bug fixes 902110 and 2237152 are removed.
553 |
554 | MODIFICATION HISTORY
555 | Date Author Description of Change
556 |
557 *============================================================================*/
558
559 FUNCTION Ap_Cancel_Single_Invoice(
560 P_invoice_id IN NUMBER,
561 P_last_updated_by IN NUMBER,
562 P_last_update_login IN NUMBER,
563 P_accounting_date IN DATE,
564 P_message_name OUT NOCOPY VARCHAR2,
565 P_invoice_amount OUT NOCOPY NUMBER,
566 P_base_amount OUT NOCOPY NUMBER,
567 P_temp_cancelled_amount OUT NOCOPY NUMBER,
568 P_cancelled_by OUT NOCOPY NUMBER,
569 P_cancelled_amount OUT NOCOPY NUMBER,
570 P_cancelled_date OUT NOCOPY DATE,
571 P_last_update_date OUT NOCOPY DATE,
572 P_original_prepayment_amount OUT NOCOPY NUMBER,
573 P_pay_curr_invoice_amount OUT NOCOPY NUMBER,
574 P_Token OUT NOCOPY VARCHAR2,
575 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
576 IS
577
578 CURSOR Invoice_Lines_cur IS
579 SELECT *
580 FROM ap_invoice_lines
581 WHERE invoice_id = P_invoice_id
582 AND (NVL(discarded_flag, 'N' ) <> 'Y'
583 AND NVL(cancelled_flag, 'N') <> 'Y') -- Bug 6669048
584 ORDER BY line_type_lookup_code;
585
586 CURSOR Tax_Holds_Cur IS
587 SELECT AH.hold_lookup_code
588 FROM AP_HOLDS AH,
589 AP_HOLD_CODES AHC
590 WHERE AH.invoice_id = P_invoice_id
591 AND AH.hold_lookup_code = AHC.hold_lookup_code
592 AND AH.release_lookup_code IS NULL
593 AND AH.hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE');
594
595 -- Cursor added for Payment Request Cancellation from Payables and
596 -- notifying the calling product.
597
598 CURSOR c_reg_products IS
599 SELECT Reg_Application_ID
600 FROM AP_Invoices_All AI,
601 AP_Product_Registrations APR
602 WHERE AI.Invoice_ID = P_Invoice_ID
603 AND AI.Application_ID = APR.Reg_Application_ID
604 AND APR.Registration_Event_Type = 'INVOICE_CANCELLED';
605
606 cursor dist_debug_cur is
607 Select *
608 FROM ap_invoice_distributions_all aid
609 WHERE aid.invoice_id = p_invoice_id;
610
611 l_inv_line_rec_list Inv_Line_Tab_Type;
612 l_ok_to_cancel BOOLEAN := FALSE;
613 l_discard_line_ok BOOLEAN := FALSE;
614 l_count NUMBER;
615 l_holds_count NUMBER := 0;
616 l_success BOOLEAN;
617 l_approval_status VARCHAR2(25);
618 l_result_string VARCHAR2(240);
619 l_error_code VARCHAR2(4000);
620 l_approval_return_message VARCHAR2(2000);
621 l_debug_info VARCHAR2(240);
622 l_curr_calling_sequence VARCHAR2(2000);
623 l_tax_already_calculated BOOLEAN;
624 i BINARY_INTEGER := 1;
625 l_tax_holds_count NUMBER := 0;
626 l_hold_code_tab AP_ETAX_SERVICES_PKG.REL_HOLD_CODES_TYPE;
627 l_token VARCHAR2(4000);
628 l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
629 l_invoice_validation_status VARCHAR2(100);
630 l_payment_status_flag AP_INVOICES_ALL.PAYMENT_STATUS_FLAG%TYPE;
631 l_invoice_type_lookup_code AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
632 Tax_Exception EXCEPTION;
633 --Bug 4539462 DBI logging
634 l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
635
636 -- Bug 6669048
637 l_tax_lines_count NUMBER := 0;
638 l_self_assess_tax_count NUMBER := 0 ; -- Bug 6694536
639 l_tax_dist_count NUMBER := 0; -- Bug 6815172
640
641 -- Bug 4748638
642 l_Accounting_event_ID AP_INVOICE_DISTRIBUTIONS_ALL.accounting_event_id%TYPE;
643 l_cancel_dist_exists NUMBER := 0;
644 l_open_gl_date AP_INVOICES_ALL.gl_date%type;
645 l_funds_return_code VARCHAR2(30); -- 4276409 (3462325)
646
647 -- Payment request Cancellation Subscription
648 l_return_status varchar2(1);
649 l_msg_count number;
650 l_msg_data varchar2(2000);
651 l_application_id number;
652
653 l_chk_encum NUMBER := 0; ---7264524
654 l_check_encumbrance NUMBER := 0; ---7428195
655 p_holds AP_APPROVAL_PKG.HOLDSARRAY; --7264524
656 p_hold_count AP_APPROVAL_PKG.COUNTARRAY; --7264524
657 p_release_count AP_APPROVAL_PKG.COUNTARRAY; --7264524
658 l_procedure_name CONSTANT VARCHAR2(50) := 'cancel_single_invoice';
659 prob_dist_list varchar2(1000):=NULL; --9100425
660 prob_dist_count number :=0; --9100425
661 l_cancel_proactive_flag varchar2(1); --9100425
662
663 -- Bug 9497953 : Start
664 l_max_inv_dist_acc_date DATE ;
665 l_max_pmt_acc_date DATE ;
666 l_org_id NUMBER ;
667 l_open_period gl_period_statuses.period_name%TYPE ;
668 l_accounting_date DATE ;
669 -- Bug 9497953 : End
670
671 l_unsuccessful_cancel NUMBER := 0 ; -- Bug 9362054
672 l_inv_num AP_INVOICES.invoice_num%TYPE ; -- Bug 9362054
673 BEGIN
674 l_curr_calling_sequence := 'AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE<-' ||
675 P_calling_sequence;
676
677 /*-----------------------------------------------------------------+
678 | Step 1. Check if invoice is cancellable |
679 +-----------------------------------------------------------------*/
680
681 l_debug_info := 'Check if invoice is cancellable';
682
683 l_ok_to_cancel := Is_Invoice_Cancellable(
684 P_invoice_id => p_invoice_id,
685 P_error_code => p_message_name,
686 P_debug_info => l_debug_info,
687 P_calling_sequence => l_curr_calling_sequence);
688
689 IF ( l_ok_to_cancel = FALSE ) THEN
690 RETURN FALSE;
691 END IF;
692
693 -- Bug 9497953 : Start
694 SELECT TRUNC( NVL( MAX( AID.accounting_date ) , P_accounting_date ) )
695 INTO l_max_inv_dist_acc_date
696 FROM ap_invoice_distributions AID
697 WHERE AID.invoice_id = P_invoice_id
698 AND AID.awt_invoice_payment_id IS NULL ;
699
700 SELECT TRUNC( NVL( MAX( AIP.accounting_date ) , P_accounting_date ) )
701 INTO l_max_pmt_acc_date
702 FROM ap_invoice_payments AIP
703 WHERE AIP.invoice_id = P_invoice_id ;
704
705 SELECT org_id
706 INTO l_org_id
707 FROM ap_invoices
708 WHERE invoice_id = P_invoice_id ;
709
710 IF l_max_pmt_acc_date > l_max_inv_dist_acc_date THEN
711 l_accounting_date := l_max_pmt_acc_date ;
712 ELSE
713 l_accounting_date := l_max_inv_dist_acc_date ;
714 END IF ;
715
716 IF TRUNC( P_accounting_date ) > l_accounting_date THEN
717 l_accounting_date := TRUNC( P_accounting_date );
718 END IF ;
719
720 IF l_accounting_date IS NOT NULL THEN
721 l_open_period := AP_UTILITIES_PKG.Get_Current_GL_Date_No_Cache( P_Date => l_accounting_date,
722 P_Org_Id => l_org_id );
723
724 IF (l_open_period is null) THEN
725 AP_UTILITIES_PKG.get_open_gl_date_no_cache( l_accounting_date, l_open_period, l_open_gl_date );
726 if (l_open_gl_date is null) then
727 p_message_name := 'AP_CANCEL_NO_OPEN_FUT_PERIOD' ;
728 RETURN FALSE ;
729 else
730 l_accounting_date := l_open_gl_date;
731 end if;
732 END IF;
733 END IF ;
734 -- Bug 9497953 : End
735
736 SAVEPOINT CANCEL_CHECK_1; --9100425
737 /*-----------------------------------------------------------------+
738 | Step 2. If invoice has had tax withheld, undo withholding |
739 | |
740 +-----------------------------------------------------------------*/
741
742 l_debug_info := 'Check if AWT has been performed by AutoApproval';
743
744 SELECT count(*)
745 INTO l_count
746 FROM ap_invoice_distributions
747 WHERE invoice_id = P_invoice_id
748 AND NVL(awt_flag,'N') = 'A' --bug 9702328
749 AND NVL(reversal_flag,'N') <> 'Y';
750
751 IF (l_count > 0) THEN
752
753 AP_WITHHOLDING_PKG.AP_UNDO_WITHHOLDING(
754 P_invoice_id,
755 'CANCEL INVOICE',
756 l_accounting_date, -- Bug 9497953 P_accounting_date,
757 NULL,
758 P_Last_Updated_By,
759 P_Last_Update_Login,
760 NULL,
761 NULL,
762 NULL,
763 l_result_string);
764 END IF;
765
766 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
767 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
768 END IF;
769
770 /*-----------------------------------------------------------------+
771 | Step 3. Clear out payment schedules |
772 | 1. Set hold_flag to 'N' to make cancelled invoice |
773 | does not show up on 'Invoice on Hold report'. |
774 +-----------------------------------------------------------------*/
775
776 l_debug_info := 'Clear out payment schedules';
777
778 UPDATE ap_payment_schedules
779 SET gross_amount = 0
780 ,amount_remaining = 0
781 ,payment_status_flag = 'N'
782 ,hold_flag = 'N'
783 ,last_updated_by = P_last_updated_by
784 ,last_update_date = sysdate
785 ,inv_curr_gross_amount =0 --Bug5446999
786 WHERE invoice_id = P_invoice_id;
787
788 --Bug 4539462 DBI logging
789 AP_DBI_PKG.Maintain_DBI_Summary
790 (p_table_name => 'AP_PAYMENT_SCHEDULES',
791 p_operation => 'U',
792 p_key_value1 => P_invoice_id,
793 p_calling_sequence => l_curr_calling_sequence);
794
795 /*-----------------------------------------------------------------+
796 | Step 4a. Delete all unprocessed bc events and update the |
797 | encumbered flag to 'R' |
798 +-----------------------------------------------------------------*/
799
800 --Start of bug 8733916
801
802 AP_FUNDS_CONTROL_PKG.Encum_Unprocessed_Events_Del
803 (p_invoice_id => p_invoice_id,
804 p_calling_sequence => l_curr_calling_sequence);
805
806 UPDATE ap_invoice_distributions aid
807 SET aid.encumbered_flag = 'R'
808 WHERE aid.invoice_id = p_invoice_id
809 AND nvl(aid.match_status_flag,'N') <> 'A'
810 AND nvl(aid.encumbered_flag,'N') <> 'Y'
811 AND aid.parent_reversal_id is null
812 AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
813 AND nvl(aid.reversal_flag,'N')<>'Y'
814 AND EXISTS (SELECT 1
815 FROM financials_system_params_all fsp
816 WHERE fsp.org_id = aid.org_id
817 AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
818
819 --End of bug 8733916
820
821 /*-----------------------------------------------------------------+
822 | Step 4b. Discard Line in Cancel Mode for each line of invoice |
823 +-----------------------------------------------------------------*/
824
825 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
826 l_debug_info := 'step4 - Now call Discard Lines for the invoice';
827 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
828 END IF;
829
830
831 BEGIN
832 OPEN Invoice_Lines_Cur;
833 FETCH Invoice_Lines_Cur
834 BULK COLLECT INTO l_inv_line_rec_list;
835 CLOSE Invoice_Lines_Cur;
836 END;
837
838 SELECT invoice_type_lookup_code,
839 payment_status_flag,
840 invoice_amount
841 INTO l_invoice_type_lookup_code,
842 l_payment_status_flag,
843 l_invoice_amount
844 FROM ap_invoices
845 WHERE invoice_id =p_invoice_id;
846
847 l_invoice_validation_status := ap_invoices_pkg.get_approval_status(
848 l_invoice_id => p_invoice_id,
849 l_invoice_amount => l_invoice_amount,
850 l_payment_status_flag => l_payment_status_flag,
851 l_invoice_type_lookup_code => l_invoice_type_lookup_code );
852
853 l_debug_info := 'Dicard Lines for the invoice: Calling etax for Validated Invoice';
854
855 IF (NVL(l_invoice_validation_status,'NEVER APPROVED') IN
856 ('APPROVED','AVAILABLE','UNPAID','FULL')) THEN
857
858 l_success := ap_etax_pkg.calling_etax(
859 P_Invoice_id => p_invoice_id,
860 P_Calling_Mode => 'UNFREEZE INVOICE',
861 P_All_Error_Messages => 'N',
862 P_error_code => l_error_code,
863 P_Calling_Sequence => l_curr_calling_sequence);
864
865 IF (not l_success) THEN
866 p_message_name := 'AP_ETX_CANCEL_UNFRZ_INV_FAILED';
867 p_token := l_error_code;
868 RETURN(FALSE);
869 END IF;
870
871 END IF;
872
873
874 l_debug_info := 'Dicard Lines for the invoice: Discarding Individual line';
875 --Bug 5585992
876 IF l_inv_line_rec_list.count > 0 THEN
877
878 FOR i in l_inv_line_rec_list.FIRST..l_inv_line_rec_list.LAST
879 LOOP
880 -- Bug 5585992
881 IF l_inv_line_rec_list.exists(i) THEN
882
883 /* Added nvl condition to p_accounting_date for bug #6627060 */
884 -- Bug 5584997
885 l_inv_line_rec_list(i).accounting_date :=
886 nvl(l_accounting_date,l_inv_line_rec_list(i).accounting_date); -- Bug 9497953
887
888 l_discard_line_ok := AP_INVOICE_LINES_PKG.Discard_Inv_Line(
889 P_line_rec => l_inv_line_rec_list(i),
890 P_calling_mode => 'CANCEL',
891 P_inv_cancellable => 'Y',
892 P_last_updated_by => p_last_updated_by,
893 P_last_update_login => p_last_update_login,
894 P_error_code => l_error_code,
895 P_Token => l_token,
896 P_calling_sequence => l_curr_calling_sequence);
897
898 IF ( l_discard_line_ok = FALSE ) THEN
899 P_Message_Name := l_error_code;
900 P_Token := l_token;
901 RETURN FALSE;
902 END IF;
903
904 END IF;
905
906 END LOOP;
907
908 l_inv_line_rec_list.DELETE;
909
910 ELSE
911
912 l_discard_line_ok := TRUE;
913
914 END IF;
915
916 --ETAX:
917 l_tax_already_calculated := AP_ETAX_UTILITY_PKG.Is_Tax_Already_Calc_Inv(
918 P_Invoice_Id => p_invoice_id,
919 P_Calling_Sequence => l_curr_calling_sequence);
920
921 IF (l_tax_already_calculated) THEN
922
923 --Commented below 3 SELECT and checking all types of tax lines count
924 --together in single SELECT, which needs to cancel. Bug#9244765
925
926 /*
927 -- Bug 6669048. Get the count of tax lines that have not been cancelled.
928 SELECT count(*)
929 INTO l_tax_lines_count
930 FROM ap_invoice_lines
931 WHERE invoice_id = p_invoice_id
932 AND line_type_lookup_code = 'TAX'
933 AND NVL(cancelled_flag,'N') <> 'Y'
934 AND rownum =1;
935
936 -- Bug 6694536. Need to call the etax api to reverse the self assessed tax lines.
937
938 SELECT count(*)
939 INTO l_self_assess_tax_count
940 FROM ap_self_assessed_tax_dist_all asat,
941 zx_rec_nrec_dist zx_dist
942 WHERE invoice_id = p_invoice_id
943 AND asat.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
944 AND zx_dist.self_assessed_flag = 'Y'
945 AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
946 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
947 AND rownum =1;
948
949 -- Bug 6815172. Get the count of tax distribution lines that
950 -- have not been cancelled in case of inclusive tax.
951
952 SELECT count(*)
953 INTO l_tax_dist_count
954 FROM zx_rec_nrec_dist zx_dist,
955 ap_invoice_distributions ap_dist
956 WHERE ap_dist.invoice_id = p_invoice_id
957 AND ap_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
958 AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
959 AND nvl(zx_dist.inclusive_flag, 'N') = 'Y'
960 AND ap_dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
961 AND rownum =1; */
962
963
964 SELECT count(*)
965 INTO l_tax_lines_count
966 FROM zx_lines_summary zls
967 WHERE zls.trx_id = p_invoice_id
968 and zls.application_id = 200
969 and zls.entity_code = 'AP_INVOICES'
970 and zls.event_class_code In ('STANDARD INVOICES',
971 'PREPAYMENT INVOICES',
972 'EXPENSE REPORTS')
973 and NVL(zls.reporting_only_flag, 'N') = 'N'
974 and nvl(zls.cancel_flag, 'N') <> 'Y'
975 and rownum =1;
976
977 --End of 9244765
978
979
980 -- Bug 7553603
981 -- cancelled_flag needs to be set prior to calling the tax api as
982 -- the line_level_action needs to be sent as DISCARD. This was
983 -- a regression from bug 6669048.
984
985 UPDATE ap_invoice_lines
986 SET cancelled_flag = 'Y'
987 WHERE invoice_id = P_invoice_id
988 AND NVL(discarded_flag, 'N' ) <> 'Y';
989
990
991 --Bug# 9244765. Commented all counts check and now checking with
992 --only correct count
993
994 /*
995 -- Bug 6669048. We should not be calling the etax API if all the tax
996 -- lines have already been cancelled.
997 IF l_tax_lines_count > 0 OR l_self_assess_tax_count > 0
998 OR l_tax_dist_count > 0 -- Bug 6815172 THEN */
999
1000 IF l_tax_lines_count > 0 THEN
1001
1002 --End of 9244765
1003
1004
1005 l_success := ap_etax_pkg.calling_etax(p_invoice_id => p_invoice_id,
1006 p_calling_mode => 'CANCEL INVOICE',
1007 p_all_error_Messages => 'N',
1008 p_error_code => l_error_code,
1009 p_calling_sequence => l_curr_calling_sequence);
1010
1011 IF NOT(l_success) THEN
1012 Raise Tax_Exception;
1013 END IF;
1014
1015 END IF;
1016
1017 END IF;
1018
1019
1020 /*-----------------------------------------------------------------+
1021 | Step 4.5 Proactive Cancellation Check ->fires Based on profile |
1022 +-----------------------------------------------------------------*/
1023 -- 9100425
1024 BEGIN
1025 fnd_profile.get('AP_ENHANCED_DEBUGGING', l_cancel_proactive_flag);
1026
1027 IF nvl(l_cancel_proactive_flag,'N') ='C' then
1028
1029 For I in(select invoice_distribution_id
1030 from ap_invoice_distributions aid1
1031 where aid1.invoice_id=P_invoice_id
1032 and aid1.parent_reversal_id is null --original dist
1033 --for original dists there is no reversal dist created
1034 and ( not exists (select 1 from ap_invoice_distributions aid2
1035 where aid1.invoice_id=aid2.invoice_id
1036 and aid2.invoice_id=P_invoice_id
1037 and aid2.invoice_line_number=aid1.invoice_line_number
1038 and aid2.parent_reversal_id =aid1.invoice_distribution_id)
1039 --the reversal dist does not reverse the amount correctly
1040 or exists (select 1 from ap_invoice_distributions aid2
1041 where aid1.invoice_id=aid2.invoice_id
1042 and aid2.invoice_id=P_invoice_id
1043 and aid2.invoice_line_number=aid1.invoice_line_number
1044 and aid2.parent_reversal_id =aid1.invoice_distribution_id
1045 and -1 * aid2.amount <> aid1.amount)))
1046
1047 LOOP
1048 prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
1049 prob_dist_count:=prob_dist_count+1;
1050
1051 end loop;
1052
1053 IF prob_dist_count > 0 then
1054 P_message_name := 'AP_INV_DIS_CAN_FAIL';
1055 P_Token := prob_dist_list;
1056 ROLLBACK TO SAVEPOINT CANCEL_CHECK_1;
1057
1058 RETURN (FALSE);
1059 end if;
1060
1061 END IF;
1062
1063 EXCEPTION WHEN OTHERS THEN
1064 NULL;
1065 END;
1066 --9100425
1067
1068
1069 /*-----------------------------------------------------------------+
1070 | Step 5. Zero out the invoice for main invoice table |
1071 +-----------------------------------------------------------------*/
1072
1073 IF ( l_discard_line_ok ) and prob_dist_count =0 THEN --9100425
1074 l_debug_info := 'Zero out the invoice';
1075
1076 UPDATE ap_invoices
1077 SET invoice_amount = 0
1078 ,base_amount = 0
1079 ,temp_cancelled_amount = DECODE(temp_cancelled_amount, NULL,
1080 invoice_amount,
1081 DECODE(invoice_amount, 0,
1082 temp_cancelled_amount,
1083 invoice_amount))
1084 ,pay_curr_invoice_amount = 0
1085 ,last_updated_by = P_last_updated_by
1086 ,last_update_date = sysdate
1087 WHERE invoice_id = P_invoice_id;
1088
1089 -- Bug 6669048. Update the cancelled_flag for all the lines to 'Y' since
1090 -- at this point all the invoice lines have been discarded including the
1091 -- tax lines and AWT lines
1092 UPDATE ap_invoice_lines
1093 SET cancelled_flag = 'Y'
1094 WHERE invoice_id = P_invoice_id
1095 AND NVL(discarded_flag, 'N' ) <> 'Y';
1096
1097 --Bug 4539462 DBI logginG
1098 AP_DBI_PKG.Maintain_DBI_Summary
1099 (p_table_name => 'AP_INVOICES',
1100 p_operation => 'U',
1101 p_key_value1 => P_Invoice_Id,
1102 p_calling_sequence => l_curr_calling_sequence);
1103
1104 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1105 l_debug_info := 'Before call approval pkg again ';
1106 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
1107
1108 FOR l_inv_dist_rec IN dist_debug_cur
1109 loop
1110 l_debug_info :='invoice distribution looks like'||
1111 'l_dist_type = ' || l_inv_dist_rec.line_type_lookup_code||
1112 'l_amount=' || l_inv_dist_rec.amount ||
1113 'l_base_amount =' || l_inv_dist_rec.base_amount ||
1114 'l_match_status_flag=' ||l_inv_dist_rec.match_status_flag ;
1115 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
1116 end loop;
1117
1118 END IF;
1119
1120 /*-----------------------------------------------------------------+
1121 | Step 7. Run Approval again for this cancelled invoice |
1122 | Ignore message returned from APPROVE process |
1123 +-----------------------------------------------------------------*/
1124
1125 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1126 l_debug_info := 'Run Approval for this invoice again';
1127 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
1128 END IF;
1129
1130 --SMYADAM: Approve procedure calls 4 etax apis (calculate, distribute, validate, freeze).
1131 --Do we need to call these apis when the invoice is being cancelled also (looks unnecessary)
1132 --, if we decide not to call these apis for cancellation case,
1133 --what if the invoice cancellation doesn't go thru due to postable holds which
1134 --we will know only after the validation has been done, and then since we did not
1135 --call the etax apis during the approve call and the invoice is not cancelled , won't
1136 --the tax data on this invoice be stale. ??
1137
1138 AP_APPROVAL_PKG.APPROVE(
1139 P_RUN_OPTION => '',
1140 P_INVOICE_BATCH_ID => '',
1141 P_BEGIN_INVOICE_DATE => '',
1142 P_END_INVOICE_DATE => '',
1143 P_VENDOR_ID => '',
1144 P_PAY_GROUP => '',
1145 P_INVOICE_ID => P_invoice_id,
1146 P_ENTERED_BY => '',
1147 P_SET_OF_BOOKS_ID => '',
1148 P_TRACE_OPTION => '',
1149 P_CONC_FLAG => 'N',
1150 P_HOLDS_COUNT => l_holds_count,
1151 P_APPROVAL_STATUS => l_approval_status,
1152 P_FUNDS_RETURN_CODE => l_funds_return_code, -- 4276409 (3462325)
1153 P_CALLING_MODE => 'CANCEL',
1154 P_CALLING_SEQUENCE => l_curr_calling_sequence,
1155 P_COMMIT => 'N');
1156
1157 l_approval_return_message := FND_MESSAGE.GET;
1158
1159 -- Bug Fixes for 7264524, 7428195 and 9465105 have been relocated
1160 -- to apaprvlb.pls for the bug9553683
1161 --
1162
1163
1164 /*-----------------------------------------------------------------+
1165 | Step 8. Check if there are posting hold exist |
1166 +-----------------------------------------------------------------*/
1167
1168 l_debug_info := 'Get the number of posting holds for invoice';
1169
1170 BEGIN
1171 SELECT count(*)
1172 INTO l_holds_count
1173 FROM ap_holds AH
1174 ,ap_hold_codes AHC
1175 WHERE AH.invoice_id = P_invoice_id
1176 AND AH.hold_lookup_code = AHC.hold_lookup_code
1177 AND AH.release_lookup_code IS NULL
1178 AND AHC.postable_flag = 'N';
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 l_holds_count := 0;
1182 END;
1183
1184
1185 IF (l_holds_count = 0) THEN
1186
1187 /*-----------------------------------------------------------------+
1188 | Step 9A. Complete Cancel process if no postable hold |
1189 | 1. Check if any tax holds,if so call etax to |
1190 | release tax holds. |
1191 | 2. Update the invoice header information |
1192 | 3. Release all the holds |
1193 | 4. set return value to TRUE - indicate success |
1194 +-----------------------------------------------------------------*/
1195 l_debug_info := 'Check if invoice has any tax holds';
1196
1197 SELECT count(*)
1198 INTO l_tax_holds_count
1199 FROM ap_holds AH
1200 ,ap_hold_codes AHC
1201 WHERE AH.invoice_id = P_invoice_id
1202 AND AH.hold_lookup_code = AHC.hold_lookup_code
1203 AND AH.release_lookup_code IS NULL
1204 AND AH.hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE');
1205
1206 IF (l_tax_holds_count <> 0) THEN
1207
1208 OPEN Tax_Holds_Cur;
1209 FETCH Tax_Holds_Cur bulk collect into l_hold_code_tab;
1210 CLOSE Tax_Holds_Cur;
1211
1212 l_debug_info := 'Call Etax to release tax holds';
1213
1214 l_success:= ap_etax_services_pkg.release_tax_holds(
1215 p_invoice_id => p_invoice_id,
1216 p_calling_mode => 'RELEASE TAX HOLDS',
1217 p_tax_hold_code => l_hold_code_tab,
1218 p_all_error_messages => 'N',
1219 p_error_code => l_error_code,
1220 p_calling_sequence => l_curr_calling_sequence);
1221
1222 IF NOT(l_success) THEN
1223 Raise Tax_Exception;
1224 END IF;
1225
1226 END IF;
1227
1228 /*-----------------------------------------------------------------+
1229 | Step 9A. Complete Cancel process if no postable hold |
1230 | Create single invoice cancelled accounting event |
1231 | This is a code fix combined with the following bugs |
1232 | 3574680, 2993905 while fixing 4748638 |
1233 +-----------------------------------------------------------------*/
1234
1235 -- Start Bug fix 4748638 (4881719)
1236 --Bug#3574680, determine if cancelled distributions exists
1237 SELECT COUNT(invoice_distribution_id)
1238 INTO l_cancel_dist_exists
1239 FROM ap_invoice_distributions
1240 WHERE invoice_id=p_invoice_id
1241 AND cancellation_flag='Y'
1242 AND rownum=1;
1243
1244 IF NVL(l_cancel_dist_exists,0) <> 0 THEN
1245
1246 AP_ACCOUNTING_EVENTS_PKG.Create_Events (
1247 'INVOICE CANCELLATION'
1248 ,NULL -- p_doc_type
1249 ,p_invoice_id
1250 ,l_accounting_date -- Bug 9497953 p_accounting_date
1251 ,l_Accounting_event_ID
1252 ,NULL -- checkrun_name
1253 ,P_calling_sequence);
1254 END IF;
1255
1256 -- BUG fix 4748638 END
1257
1258
1259 l_debug_info := 'Cancelling the invoice if no postable holds';
1260
1261 UPDATE ap_invoices
1262 SET cancelled_by = P_last_updated_by
1263 ,cancelled_amount = temp_cancelled_amount
1264 ,cancelled_date = sysdate
1265 ,last_updated_by = P_last_updated_by
1266 ,last_update_date = sysdate
1267 WHERE invoice_id = P_invoice_id;
1268
1269 -- Bug 9362054 : Begin
1270 SELECT COUNT(1)
1271 INTO l_unsuccessful_cancel
1272 FROM ap_invoice_distributions AID,
1273 financials_system_params_all FSP
1274 WHERE AID.invoice_id = P_invoice_id
1275 AND FSP.org_id = AID.org_id
1276 AND FSP.set_of_books_id = AID.set_of_books_id
1277 AND ( ( NVL( FSP.purch_encumbrance_flag, 'N' ) = 'Y'
1278 AND NVL( AID.match_status_flag, 'N' ) <> 'A'
1279 )
1280 OR ( NVL( FSP.purch_encumbrance_flag, 'N' ) = 'N'
1281 AND NVL( AID.match_status_flag, 'N' ) NOT IN ( 'A', 'T' )
1282 )
1283 )
1284 AND ROWNUM = 1 ;
1285
1286 IF l_unsuccessful_cancel <> 0 THEN
1287 SELECT invoice_num
1288 INTO P_Token
1289 FROM ap_invoices
1290 WHERE invoice_id = p_invoice_id ;
1291
1292 ROLLBACK TO SAVEPOINT CANCEL_CHECK_1 ;
1293 P_message_name := 'AP_UNSUCCESSFUL_CANCELLATION' ;
1294 RETURN FALSE ;
1295 END IF ;
1296 -- Bug 9362054 : End
1297
1298 -- bug9322013
1299 AP_ACCOUNTING_EVENTS_PKG.Set_Prepay_Event_Noaction
1300 (p_invoice_id,
1301 l_curr_calling_sequence);
1302
1303 -- Bug 9691312 : Begin
1304 FOR c_wf_status IN ( SELECT hold_id
1305 FROM ap_holds
1306 WHERE invoice_id = p_invoice_id
1307 AND release_lookup_code IS NULL
1308 AND wf_status IN ( 'STARTED', 'NEGOTIATE' )
1309 )
1310 LOOP
1311 AP_WORKFLOW_PKG.abort_holds_workflow( c_wf_status.hold_id ) ;
1312 END LOOP ;
1313 -- Bug 9691312 : End
1314
1315 UPDATE ap_holds
1316 SET release_lookup_code = 'APPROVED'
1317 ,release_reason = ( SELECT description
1318 FROM ap_hold_codes
1319 WHERE hold_lookup_code = 'APPROVED')
1320 ,last_update_date = SYSDATE
1321 ,last_updated_by = P_last_updated_by
1322 ,last_update_login = P_last_update_login
1323 WHERE invoice_id = P_invoice_id
1324 AND release_lookup_code IS NULL ;
1325
1326 l_success := TRUE;
1327
1328 --Bug 4539462 DBI logging
1329 AP_DBI_PKG.Maintain_DBI_Summary
1330 (p_table_name => 'AP_INVOICES',
1331 p_operation => 'U',
1332 p_key_value1 => P_Invoice_id,
1333 p_calling_sequence => l_curr_calling_sequence);
1334
1335 AP_DBI_PKG.Maintain_DBI_Summary
1336 (p_table_name => 'AP_HOLDS',
1337 p_operation => 'U',
1338 p_key_value1 => P_Invoice_id,
1339 p_calling_sequence => l_curr_calling_sequence);
1340
1341 COMMIT;
1342
1343 ELSE
1344
1345 /*-----------------------------------------------------------------+
1346 | Step 9B. Special treatment for an invoice with POSTABLE HOLD |
1347 | During Cancellation. |
1348 | 1. populate returned error message |
1349 | 2. set return value to FALSE |
1350 | Invoice Header Level data will not be marked as |
1351 | CANCELLED to allow user to release any hold when they|
1352 | review. We also need to commit data because the |
1353 | postable holds if any will be depending on the |
1354 | reversed data |
1355 +-----------------------------------------------------------------*/
1356
1357 l_debug_info := 'Special treatment for an invoice with POSTABLE HOLDS';
1358
1359 P_message_name := 'AP_INV_CANCEL_POSTING_HOLDS';
1360 l_success := FALSE;
1361
1362 END IF; -- end of check l_hold_count of postable hold
1363
1364 /*-----------------------------------------------------------------+
1365 | Step 10. Commit Data for success cancelled invoice and invoice |
1366 | with postable hold |
1367 +-----------------------------------------------------------------*/
1368
1369 l_debug_info := 'Committing changes to database';
1370
1371 /*----------------------------------------------------------------+
1372 | Step 10.5. Calling API for Payment Request Cancellation |
1373 | Subscribe Event |
1374 +-----------------------------------------------------------------*/
1375
1376 l_debug_info := 'Calling Payment Request Cancellation Subscription API';
1377 -- Payment request Cancellation from Payables
1378 IF p_calling_sequence not in ('ar_refund_pvt.cancel_refund','IGS_FI_PRC_APINT.CANCEL_INVOICE') THEN
1379 /* added 'IGS_FI_PRC_APINT.CANCEL_INVOICE' for bug 5948586 as we need not call
1380 the Subscribe_To_Cancel_Event if ap_cancel_single invoice is called from SF module.
1381 plz see the bug for details. */
1382
1383 OPEN c_reg_products;
1384 LOOP
1385 FETCH c_reg_products INTO l_application_id;
1386 EXIT WHEN c_reg_products%NOTFOUND;
1387
1388 AP_CANCEL_PKG.Subscribe_To_Cancel_Event(
1389 P_Event_Type => 'INVOICE_CANCELLED',
1390 P_Invoice_ID => P_invoice_id,
1391 P_Application_ID => l_application_id,
1392 P_Return_Status => l_return_status,
1393 P_Msg_Count => l_msg_count,
1394 P_Msg_Data => l_msg_data,
1395 P_Calling_Sequence => l_curr_calling_sequence);
1396
1397 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1398 FND_MSG_PUB.Count_And_Get
1399 (p_encoded => FND_API.G_FALSE,
1400 p_count => l_msg_count,
1401 p_data => l_msg_data);
1402 l_debug_info := l_msg_data;
1403 RETURN FALSE;
1404 END IF;
1405
1406 END LOOP;
1407 END IF;
1408
1409 /*-----------------------------------------------------------------+
1410 | Step 11. Populate the Out parameter |
1411 +-----------------------------------------------------------------*/
1412
1413 l_debug_info := 'Populate OUT parameters';
1414
1415 SELECT invoice_amount
1416 ,base_amount
1417 ,temp_cancelled_amount
1418 ,cancelled_by
1419 ,cancelled_amount
1420 ,cancelled_date
1421 ,last_update_date
1422 ,original_prepayment_amount
1423 ,pay_curr_invoice_amount
1424 INTO P_invoice_amount
1425 ,P_base_amount
1426 ,P_temp_cancelled_amount
1427 ,P_cancelled_by
1428 ,P_cancelled_amount
1429 ,P_cancelled_date
1430 ,P_last_update_date
1431 ,P_original_prepayment_amount
1432 ,P_pay_curr_invoice_amount
1433 FROM ap_invoices
1434 WHERE invoice_id = P_invoice_id;
1435
1436 RETURN l_success;
1437
1438 ELSE
1439 l_debug_info := 'Discard line(s) is not successful';
1440 RETURN FALSE;
1441 END IF; -- end of check l_discard_line_ok flag
1442
1443 EXCEPTION
1444 WHEN TAX_EXCEPTION THEN
1445 IF ( Invoice_Lines_Cur%ISOPEN ) THEN
1446 CLOSE Invoice_Lines_Cur;
1447 END IF;
1448 IF (Tax_Holds_Cur%ISOPEN) THEN
1449 CLOSE Tax_Holds_Cur;
1450 END IF;
1451 APP_EXCEPTION.RAISE_EXCEPTION;
1452 RETURN FALSE;
1453 WHEN OTHERS THEN
1454 IF (SQLCODE <> -20001) THEN
1455 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1456 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1457 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1458 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1459 ' P_invoice_id = ' || P_invoice_id
1460 ||' P_last_updated_by = ' || P_last_updated_by
1461 ||' P_last_update_login = ' || P_last_update_login
1462 ||' P_accounting_date = ' || P_accounting_date);
1463 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1464 END IF;
1465
1466 IF ( Invoice_Lines_Cur%ISOPEN ) THEN
1467 CLOSE Invoice_Lines_Cur;
1468 END IF;
1469 IF (Tax_Holds_Cur%ISOPEN) THEN
1470 CLOSE Tax_Holds_Cur;
1471 END IF;
1472 APP_EXCEPTION.RAISE_EXCEPTION;
1473 RETURN FALSE;
1474
1475 END Ap_Cancel_Single_Invoice;
1476
1477 /*=============================================================================
1478 | PROCEDURE Ap_Cancel_Invoices
1479 |
1480 | Cancels all invoices associated with the payment given by P_check_id
1481 |
1482 | PROGRAM FLOW
1483 |
1484 | Invoices that are not eligible for cancellation:
1485 | 1. invoices associated with an effective payment,
1486 | 2. invoices that are selected for payment,
1487 | 3. invoices that are already cancelled
1488 | 4. invoices (prepayments) that have been used by other invoices
1489 | 5. invoices that are matched to Finally Closed PO's)
1490 | 6. invoices which were paid originally by check but whose payment
1491 | was removed prior to the voiding of the check i.e. through an
1492 | invoice adjustment are left unaffected.
1493 |
1494 | NOTES
1495 | 1. AutoApproval is run for each invoice. If the invoice has posting
1496 | holds, it is zeroed out by reversing all invoice distributions and
1497 | PO matching, but the invoice is not cancelled.
1498 |
1499 | MODIFICATION HISTORY
1500 | Date Author Description of Change
1501 |
1502 *============================================================================*/
1503
1504 PROCEDURE Ap_Cancel_Invoices(
1505 P_check_id IN NUMBER,
1506 P_last_updated_by IN NUMBER,
1507 P_last_update_login IN NUMBER,
1508 P_accounting_date IN DATE,
1509 P_num_cancelled OUT NOCOPY NUMBER,
1510 P_num_not_cancelled OUT NOCOPY NUMBER,
1511 P_calling_sequence IN VARCHAR2)
1512 IS
1513 l_num_cancelled NUMBER := 0;
1514 l_num_not_cancelled NUMBER := 0;
1515 l_success BOOLEAN;
1516 l_message_name VARCHAR2(30);
1517 l_invoice_amount NUMBER;
1518 -- bug 6883407
1519 l_invoice_gl_date DATE;
1520
1521 l_base_amount NUMBER;
1522 l_temp_cancelled_amount NUMBER;
1523 l_cancelled_by NUMBER;
1524 l_cancelled_amount NUMBER;
1525 l_pay_curr_invoice_amount NUMBER;
1526 l_cancelled_date DATE;
1527 l_last_update_date DATE;
1528 l_debug_info VARCHAR2(240);
1529 l_original_prepayment_amount NUMBER;
1530 l_curr_calling_sequence VARCHAR2(2000);
1531 l_token VARCHAR2(4000);
1532
1533 /*-----------------------------------------------------------------+
1534 | Declare cursor to select all invoices associated with the |
1535 | payment given by P_check_id and ensuring that the invoice was |
1536 | effectively being paid by the check i.e. the invoice payment |
1537 | wasn't already reversed. |
1538 +-----------------------------------------------------------------*/
1539
1540 --bug 5182311 Modified the cursor to ignore already cancelled invoices
1541 -- Bug 8257752. Commented out the reversal_flag condition.
1542
1543 CURSOR invoices_cursor IS
1544 SELECT DISTINCT aip.invoice_id
1545 FROM ap_invoice_payments aip,ap_invoices ai
1546 WHERE aip.check_id = P_check_id
1547 --AND nvl(aip.reversal_flag, 'N') <> 'Y'
1548 AND ai.invoice_id=aip.invoice_id
1549 AND ai.cancelled_date is null;
1550
1551 TYPE inv_ib_tab_Type IS TABLE OF ap_invoices.invoice_id%TYPE;
1552
1553 l_invoice_id_list inv_ib_tab_Type;
1554 i BINARY_INTEGER :=1;
1555
1556 BEGIN
1557 l_curr_calling_sequence := 'AP_CANCEL_PKG.AP_CANCEL_INVOICES<-' ||
1558 P_calling_sequence;
1559
1560 l_debug_info := 'Open invoices_cursor and do bulk fetch';
1561
1562 OPEN invoices_cursor;
1563 FETCH invoices_cursor
1564 BULK COLLECT INTO l_invoice_id_list;
1565 CLOSE invoices_cursor;
1566
1567 IF l_invoice_id_list.COUNT > 0 THEN --bug 11781836
1568 FOR i IN l_invoice_id_list.FIRST..l_invoice_id_list.LAST
1569 LOOP
1570
1571 /* Bug 9497953
1572 -- bug 6883407
1573 l_debug_info := 'Fetch Invoice GL Date.';
1574 SELECT ai.gl_date
1575 INTO l_invoice_gl_date
1576 FROM ap_invoices ai
1577 WHERE ai.invoice_id = l_invoice_id_list(i); */
1578
1579
1580 l_success := AP_Cancel_Single_Invoice(
1581 l_invoice_id_list(i),
1582 P_last_updated_by,
1583 P_last_update_login,
1584 P_accounting_date, -- Bug 9497953 l_invoice_gl_date, --P_accounting_date,-- bug 6883407
1585 l_message_name,
1586 l_invoice_amount,
1587 l_base_amount,
1588 l_temp_cancelled_amount,
1589 l_cancelled_by,
1590 l_cancelled_amount,
1591 l_cancelled_date,
1592 l_last_update_date,
1593 l_original_prepayment_amount,
1594 l_pay_curr_invoice_amount,
1595 l_token,
1596 l_curr_calling_sequence);
1597
1598 IF (l_success) THEN
1599 l_num_cancelled := l_num_cancelled + 1;
1600 ELSE
1601 l_num_not_cancelled := l_num_not_cancelled + 1;
1602 END IF;
1603
1604 END LOOP;
1605 END IF; --bug 11781836
1606 l_invoice_id_list.DELETE;
1607
1608 P_num_cancelled := l_num_cancelled;
1609 P_num_not_cancelled := l_num_not_cancelled;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 IF (SQLCODE <> -20001) THEN
1614 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1615 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1616 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1617 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1618 ' P_check_id = ' || P_check_id
1619 ||' P_last_updated_by = ' || P_last_updated_by
1620 ||' P_last_update_login = ' || P_last_update_login
1621 ||' P_accounting_date = ' || P_accounting_date);
1622 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1623 END IF;
1624
1625 IF ( invoices_cursor%ISOPEN ) THEN
1626 CLOSE invoices_cursor;
1627 END IF;
1628 APP_EXCEPTION.RAISE_EXCEPTION;
1629
1630 END Ap_Cancel_Invoices;
1631
1632 -- Added for Payment Request Cancellation from Payables.
1633 -- Procedure to subscribe to the invoice cancellation event by other products
1634 -- This procedure checks the product registry table for all the product that have
1635 -- subscribed to cancellation event and calls the product API.
1636 ---------------------------------------------------------------------------------
1637 PROCEDURE Subscribe_To_Cancel_Event
1638 (P_Event_Type IN VARCHAR2,
1639 P_Invoice_ID IN NUMBER,
1640 P_Application_ID IN NUMBER,
1641 P_Return_Status OUT NOCOPY VARCHAR2,
1642 P_Msg_Count OUT NOCOPY NUMBER,
1643 P_Msg_Data OUT NOCOPY VARCHAR2,
1644 P_Calling_Sequence IN VARCHAR2) IS
1645
1646
1647 current_calling_sequence VARCHAR2(2000);
1648 debug_info VARCHAR2(100);
1649
1650 l_stmt VARCHAR2(1000);
1651 l_return_status VARCHAR2(1);
1652 l_msg_count NUMBER;
1653 l_msg_data VARCHAR2(2000);
1654
1655
1656 CURSOR c_products_registered IS
1657 SELECT Reg_Application_ID,
1658 Registration_API
1659 FROM AP_Product_Registrations
1660 WHERE Reg_Application_ID = P_Application_ID
1661 AND Registration_Event_Type = P_Event_Type;
1662
1663 BEGIN
1664
1665 -- Update the calling sequence
1666 --
1667 current_calling_sequence := 'AP_Cancel_PKG.Subscribe_To_Cancel_Event<-' ||
1668 P_calling_sequence;
1669
1670 debug_info := 'Calling the subscribe payment event API';
1671
1672 FOR c_product_rec IN c_products_registered
1673 LOOP
1674
1675 l_stmt := 'Begin ' ||
1676 c_product_rec.Registration_API ||
1677 '(:P_Event_Type,' ||
1678 ':P_Invoice_ID,' ||
1679 ':l_return_Status,' ||
1680 ':l_msg_count,' ||
1681 ':l_msg_data);' ||
1682 'End;';
1683
1684 EXECUTE IMMEDIATE l_stmt
1685 USING IN P_Event_Type,
1686 IN P_Invoice_ID,
1687 OUT l_return_status,
1688 OUT l_msg_count,
1689 OUT l_msg_data;
1690
1691 P_Return_Status := l_return_status;
1692 P_Msg_Count := l_msg_count;
1693 P_Msg_Data := l_msg_data;
1694
1695
1696 END LOOP;
1697
1698 EXCEPTION
1699
1700 WHEN OTHERS THEN
1701 IF (SQLCODE <> -20001) THEN
1702 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1703 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1704 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1705 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = ' || to_char(P_Invoice_ID));
1706 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1707 END IF;
1708 APP_EXCEPTION.RAISE_EXCEPTION;
1709
1710 END Subscribe_To_Cancel_Event;
1711
1712 END AP_CANCEL_PKG;
[Home] [Help]