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