Script to get cancelled invoices in AP

Background:
Following script gives the list of cancelled AP Invoices in the data range.

SELECT *
  FROM ap_invoices_all
 WHERE cancelled_date IS NOT NULL
   AND invoice_date BETWEEN TO_DATE ('&From_Date', 'DD-MON-YYYY')
                        AND TO_DATE ('&TO_DATE', 'DD-MON-YYYY');

select * from ap_invoices_all where cancelled_date is not null


Background:
How to cancel Unpaid AP Invoice Batch?

Solution:
Navigation: Payables Manager > Invoices > Entry > Invoice Batches
1. Query your Invoice Batch
2. Click on Invoices
3. Select all Invoices (On the Menu Edit > Select All)
4. Click on 'Actions..' button
5. Check 'Cancel Invoices'
6. Finally click on 'OK'

Cancelled invoice API:

AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE

Here is a small procedure to check if an Invoice is cancellable or not.

create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER) is v_boolean               BOOLEAN; v_error_code            VARCHAR2(100); v_debug_info            VARCHAR2(1000); begin v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(                 P_invoice_id       => p_inv_id,                 P_error_code       => v_error_code,                 P_debug_info       => v_debug_info,                 P_calling_sequence => NULL); IF v_boolean=TRUE THEN DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is cancellable' ); ELSE DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is not cancellable :'|| v_error_code ); END IF; End XX_INV_CANCELLABLE;  -----------------------  *******************  ------------------------ Execute XX_INV_CANCELLABLE(12960); ---------------------------   **********************  --------------------------- AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE: AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise. 1] Check if the invoice is cancellable. if yes, proceed otherwise return false. 2] If invoice has tax withheld, undo withholding. 3] Clear out all payment schedules. 4] Cancel all the non-discard lines. a. reverse matching b. fetch the maximum distribution line number c. Set encumbered flags to ‘N’ d. Accounting event generation e. reverse the distributions f. update Line level Cancelled information 5] Zero out the Invoice. 6] Run AutoApproval for this invoice. 7] Check posting holds remain on this cancelled invoice. a. if NOT exist – complete the cancellation by updating header level information set return value to TRUE. b. if exist – no update, set the return values to FALSE, NO DATA rollback. 8] Commit the Data. 9] Populate the out parameters. Here is a small procedure to cancel a single invoice. create or replace procedure XX_INV_CANCEL(                             P_xx_invoice_id IN NUMBER,                             P_xx_last_updated_by IN  NUMBER,                             P_xx_last_update_login IN  NUMBER,                             P_xx_accounting_date IN  DATE) is v_boolean               BOOLEAN; v_message_name          VARCHAR2(1000); v_invoice_amount        NUMBER; v_base_amount           NUMBER; v_temp_cancelled_amount NUMBER; v_cancelled_by          VARCHAR2(1000); v_cancelled_amount      NUMBER; v_cancelled_date        DATE; v_last_update_date      DATE; v_orig_prepay_amt       NUMBER; v_pay_cur_inv_amt       NUMBER; v_token                 VARCHAR2(100); begin v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE             (p_invoice_id                 => P_xx_invoice_id,              p_last_updated_by            => P_xx_last_updated_by,              p_last_update_login          => P_xx_last_update_login,              p_accounting_date            => P_xx_accounting_date,              p_message_name               => v_message_name,              p_invoice_amount             => v_invoice_amount,              p_base_amount                => v_base_amount,              p_temp_cancelled_amount      => v_temp_cancelled_amount,              p_cancelled_by               => v_cancelled_by,              p_cancelled_amount           => v_cancelled_amount,              p_cancelled_date             => v_cancelled_date,              p_last_update_date           => v_last_update_date,              p_original_prepayment_amount => v_orig_prepay_amt,              p_pay_curr_invoice_amount    => v_pay_cur_inv_amt,              P_Token                      => v_token,              p_calling_sequence           => NULL              ); IF v_boolean THEN DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice' ); COMMIT; ELSE DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice' ); ROLLBACK; END IF; end XX_INV_CANCEL; Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);