Procedure to Cancel AP Invoice

CREATE OR REPLACE PROCEDURE cvrd_cancel_ap_invoices (

   p_org_id          IN NUMBER,

   p_inv_date_from      DATE,

   p_inv_date_to        DATE

)

AS

   l_user_id                      NUMBER := apps.fnd_global.user_id;

   l_org_id                       NUMBER := apps.fnd_global.org_id;

   l_message_name                 VARCHAR2 (1000);

   l_invoice_amount               NUMBER;

   l_base_amount                  NUMBER;

   l_tax_amount                   NUMBER;

   l_temp_cancelled_amount        NUMBER;

   l_cancelled_by                 VARCHAR2 (200);

   l_cancelled_amount             NUMBER;

   l_cancelled_date               DATE;

   l_last_update_date             DATE;

   l_original_prepayment_amount   NUMBER;

   l_pay_curr_invoice_amount      NUMBER;

   l_boolean                      BOOLEAN;

   err_msg                        VARCHAR2 (2000);

   CURSOR invoice_cur

   IS

      SELECT   aia.invoice_id,

               aia.last_updated_by,

               aia.last_update_login,

               aia.set_of_books_id,

               aia.gl_date,

               aia.invoice_num

        FROM   apps.cvrd_ap_tax_inv_stg cat, apps.ap_invoices_all aia

       WHERE       cat.invoice_num = aia.invoice_num

               AND cat.org_id = aia.org_id

               AND cat.org_id = NVL (p_org_id, l_org_id)

               AND cat.status = 'N'

               AND cat.invoice_id = aia.invoice_id

               AND aia.payment_status_flag = 'N'

               AND aia.invoice_date BETWEEN p_inv_date_from AND p_inv_date_to;

BEGIN

   apps.fnd_file.put_line (

      apps.fnd_file.LOG,

      'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice'

   );

   FOR l_inv_rec IN invoice_cur

   LOOP

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              ' Invoice Number:' || l_inv_rec.invoice_num);

      DBMS_OUTPUT.put_line (' Invoice Number:' || l_inv_rec.invoice_num);

      l_boolean :=

         ap_cancel_pkg.ap_cancel_single_invoice (

            p_invoice_id                   => l_inv_rec.invoice_id,

            p_last_updated_by              => l_inv_rec.last_updated_by,

            p_last_update_login            => l_inv_rec.last_update_login,

            p_set_of_books_id              => l_inv_rec.set_of_books_id,

            p_accounting_date              => l_inv_rec.gl_date,

            p_period_name                  => NULL,

            p_message_name                 => l_message_name,

            p_invoice_amount               => l_invoice_amount,

            p_base_amount                  => l_base_amount,

            p_tax_amount                   => l_tax_amount,

            p_temp_cancelled_amount        => l_temp_cancelled_amount,

            p_cancelled_by                 => l_cancelled_by,

            p_cancelled_amount             => l_cancelled_amount,

            p_cancelled_date               => l_cancelled_date,

            p_last_update_date             => l_last_update_date,

            p_original_prepayment_amount   => l_original_prepayment_amount,

            p_check_id                     => NULL,

            p_pay_curr_invoice_amount      => l_pay_curr_invoice_amount,

            p_calling_sequence             => NULL

         );

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              ' l_message_name => ' || l_message_name);

      DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              ' l_invoice_amount => ' || l_invoice_amount);

      DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              'l_cancelled_by => ' || l_cancelled_by);

      DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              'l_cancelled_amount => ' || l_cancelled_amount);

      DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);

      apps.fnd_file.put_line (apps.fnd_file.LOG,

                              'l_cancelled_date => ' || l_cancelled_date);

      DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);

      ---To update Staging table with appropriate message

      BEGIN

         IF l_boolean

         THEN

            apps.fnd_file.put_line (

               apps.fnd_file.LOG,

               'Successfully Cancelled the Invoice => '

               || l_inv_rec.invoice_num

            );

            DBMS_OUTPUT.put_line('Successfully Cancelled the Invoice => '

                                 || l_inv_rec.invoice_num);

            UPDATE   bolinf.cvrd_ap_tax_inv_stg cat

               SET   cat.status = 'C'

             WHERE   cat.invoice_id = l_inv_rec.invoice_id

                     AND cat.invoice_num = l_inv_rec.invoice_num;

         ELSE

            apps.fnd_file.put_line (

               apps.fnd_file.LOG,

               'Failed to Cancel the Invoice => ' || l_inv_rec.invoice_num

            );

            DBMS_OUTPUT.put_line (

               'Failed to Cancel the Invoice => ' || l_inv_rec.invoice_num

            );

            UPDATE   bolinf.cvrd_ap_tax_inv_stg cat

               SET   cat.status = 'E', cat.MESSAGE = l_message_name

             WHERE   cat.invoice_id = l_inv_rec.invoice_id

                     AND cat.invoice_num = l_inv_rec.invoice_num;

         END IF;

         COMMIT;

      EXCEPTION

         WHEN OTHERS

         THEN

            err_msg := 'Failed to update the staging table status' || SQLERRM;

            apps.fnd_file.put_line (apps.fnd_file.LOG, err_msg);

            DBMS_OUTPUT.put_line (err_msg);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      err_msg := 'Error in Procedure block' || SQLERRM;

      apps.fnd_file.put_line (apps.fnd_file.LOG, err_msg);

      DBMS_OUTPUT.put_line (err_msg);

END cvrd_cancel_ap_invoices;