Wednesday, January 11, 2012

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

1 comment: