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