API to cancel AP Invoice

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, precede otherwise return false.

2)     If invoice has tax withheld, undo withholding.

3)     Clear out all payment schedules.

4)     Cancel all the non-discard lines.

o    Reverse matching

o    Fetch the maximum distribution line number

o    Set encumbered flags to ‘N’

o    Accounting event generation

o    Reverse the distributions

o    Update Line level Cancelled information

5)     Zero out the Invoice.

6)     Run Auto Approval for this invoice.

7)     Check posting holds remain on this cancelled invoice.

o    If NOT exist – complete the cancellation by updating header level information set return value to TRUE.

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