Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.
It follows the following steps and returns a Boolean value depending on the result.
- 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)
P_invoice_id => p_inv_id,
P_error_code => v_error_code,
P_debug_info => v_debug_info,
P_calling_sequence => NULL);
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is cancellable’ );
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is not cancellable :’|| v_error_code );
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)
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
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice’ );
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice’ );