How to programmatically apply hold on AP Invoices in 11i,R12

Below procedure script will help you apply a hold on AP invoice in oracle apps 11i,R12.

This procedure can be registered as a Concurrent Executable and Concurrent Program.

API: AP_HOLDS_PKG.INSERT_SINGLE_HOLD

  /*+===============================================================*

   * PROCEDURE                                                                                                   *             *                                                                                                                    *
   *  assign_invhold_prc                                                                                         *
   *                                                                                                                    *
   * DESCRIPTION                                                                                                 *
   * ===========                                                                                               *
   *  The following procedure is called by the concurrent program is used to apply            *
   *  Invoice Hold                                                                                                  *
   *  PARAMETERS                                                                                                 *
   * ==========                                                                                                  *
   * NAME                TYPE     DESCRIPTION                                                               *
   * -----------------------------------------------------------------------------------*
   * retcode              OUT      Standard Conc Program parameter                                   *
   * errbuf               OUT      Standard Conc Program parameter                                     *
   * p_invoice_hold       IN       Invoice Hold Type                                                         *
   * p_inv_lookup         IN       Invoice Type(STANDARD,MIXED etc.)                                *
   * ------------------------------------------------------------------------------------*
   * CALLED BY                                                                                                       *
   * Concurrent Program                                                                                           *
   *+=================================================================*/

 PROCEDURE assign_invhold_prc (errbuf         OUT VARCHAR2,

                            retcode        OUT VARCHAR2,

                            p_invoice_hold IN VARCHAR2,

                            p_inv_lookup   IN VARCHAR2)

   IS

      --Declare local variables

   lv_hold_type          ap_hold_codes_v.hold_type%TYPE DEFAULT NULL;

   lv_lookup_type        ap_lookup_codes.lookup_type%TYPE := 'HOLD CODE';

   lv_hold_lookup_code   ap_hold_codes_v.hold_lookup_code%TYPE := p_invoice_hold;

   lv_hold_reason        ap_hold_codes_v.description%TYPE;

   lv_invoice_id         PLS_INTEGER;

   lv_invtype_lkpcode   ap_invoices_all.invoice_type_lookup_code%TYPE := p_inv_lookup;

   lv_fnd_user           fnd_user.user_id%TYPE := fnd_profile.VALUE ('USER_ID');

   lv_org_id             ap_invoices_all.org_id%TYPE := fnd_profile.value('ORG_ID');

   lv_sql_count          NUMBER :=0;

      -- Cursor to derive invoices to be placed on hold

      CURSOR cur_inv

      IS

         SELECT invoice_id, invoice_num

           FROM (SELECT ai.invoice_id invoice_id, ai.invoice_num invoice_num

                   FROM ap_invoices_all ai

                  WHERE 1 = 1

                    AND ai.org_id = lv_org_id

                      AND ai.invoice_type_lookup_code = lv_invtype_lkpcode

                    AND ai.payment_status_flag IN ('N','P')

             WHERE NOT EXISTS (

                   SELECT 1

                     FROM ap_holds_all

                    WHERE hold_lookup_code = lv_hold_lookup_code

                      AND org_id = lv_org_id

                      AND invoice_id = ai.invoice_id);

   BEGIN

      -- Derive Hold Type Code

      BEGIN

         SELECT ahc.hold_type, ahc.hold_lookup_code, ahc.description

           INTO lv_hold_type, lv_hold_lookup_code, lv_hold_reason

           FROM ap_lookup_codes alc, ap_hold_codes ahc

          WHERE alc.lookup_type = lv_lookup_type

            AND alc.lookup_code = lv_hold_lookup_code

            AND UPPER (ahc.hold_lookup_code) = UPPER (alc.lookup_code);

      EXCEPTION

         WHEN OTHERS

         THEN

            fnd_file.put_line (fnd_file.LOG,

                               'Error deriving hold lookup -' || SQLERRM

                              );

      END;

      fnd_file.put_line

               (fnd_file.output,

                'Following Invoices are put on ' || p_invoice_hold || ' Hold:'

               );

      fnd_file.put_line (fnd_file.output,

                         '                                  ');

      fnd_file.put_line (fnd_file.output,

                         'Invoice Number    |  Invoice ID   ');

      fnd_file.put_line (fnd_file.output,

                         '------------------ ---------------');

--Loop begins

      FOR inv_rec IN cur_inv

      LOOP

         lv_sql_count := lv_sql_count+1;

--API to apply hold

         ap_holds_pkg.insert_single_hold

                                  (x_invoice_id            => inv_rec.invoice_id,

                                   x_hold_lookup_code      => lv_hold_lookup_code,

                                   x_hold_type             => lv_hold_type,

                                   x_hold_reason           => lv_hold_reason,

                                   x_held_by               => lv_fnd_user,

                                   x_calling_sequence      => NULL

                                  );

         fnd_file.put_line (fnd_file.output,

                               RPAD (inv_rec.invoice_num, 18, ' ')

                            || '|  '

                            || RPAD (inv_rec.invoice_id, 15, ' ')

                           );

      END LOOP;

         IF lv_sql_count = 0 THEN

           fnd_file.put_line (fnd_file.output,'  *******No Data Found*******');

         END IF;

--Loop Ends

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         IF (SQLCODE <> -20001)

         THEN

            fnd_message.set_name ('SQLAP', 'AP_DEBUG');

            fnd_message.set_token ('ERROR', SQLERRM);

         END IF;

         fnd_file.put_line (fnd_file.LOG,

                            'Error in prc: assign_invhold_prc -' || SQLERRM

                           );

         app_exception.raise_exception;

   END assign_invhold_prc;

Note: Use ap_holds_pkg.release_single_hold to release hold by passing required parameters like invoice_id, hold_lookup_code