API to cancel AP Invoice

• If invoice contains distribution that does not have open GL period return FALSE.
• If invoice has an effective payment, return FALSE.
• If invoice is selected for payment, return FALSE.
• If invoice is already cancelled, return FALSE.
• If invoice is credited invoice, return FALSE.
• If invoices have been applied against this invoice, return FALSE.
• If invoice is matched to Finally Closed PO’s, return FALSE.
• If project related invoices have pending adjustments, return FALSE.
• If cancelling will cause qty_billed or amount_billed to less than 0, return FALSE.
• If none of above, invoice is cancellable return True.

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.

5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.

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
             );

Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);