API to cancel AP Invoice
AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE:
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)
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);
