Wednesday, June 15, 2011

API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)

                                                                                        

Below script will help you to release hold on AP invoice in oracle apps R12

API: AP_HOLDS_PKG.RELEASE_SINGLE_HOLD

Test Instance: R12.1.1

Tables Affected: AP_HOLDS_ALL

Script:

SET serveroutput on;

DECLARE
   x_invoice_id            NUMBER;
   x_hold_lookup_code      VARCHAR2 (200);
   x_release_lookup_code   VARCHAR2 (200);
   x_held_by               NUMBER;
   x_calling_sequence      VARCHAR2 (200);
   v_context               VARCHAR2 (10);
   v_check_flag            VARCHAR2 (1);

   FUNCTION set_context (
      i_user_name   IN   VARCHAR2,
      i_resp_name   IN   VARCHAR2,
      i_org_id      IN   NUMBER
   )
      RETURN VARCHAR2
   IS
      v_user_id        NUMBER;
      v_resp_id        NUMBER;
      v_resp_appl_id   NUMBER;
      v_lang           VARCHAR2 (100);
      v_session_lang   VARCHAR2 (100) := fnd_global.current_language;
      v_return         VARCHAR2 (10)  := 'T';
      v_nls_lang       VARCHAR2 (100);
      v_org_id         NUMBER         := i_org_id;

/* Cursor to get the user id information based on the input user name */
      CURSOR cur_user
      IS
         SELECT user_id
           FROM fnd_user
          WHERE user_name = i_user_name;

/* Cursor to get the responsibility information */
      CURSOR cur_resp
      IS
         SELECT responsibility_id, application_id, LANGUAGE
           FROM fnd_responsibility_tl
          WHERE responsibility_name = i_resp_name;

/* Cursor to get the nls language information for setting the language context */
      CURSOR cur_lang (p_lang_code VARCHAR2)
      IS
         SELECT nls_language
           FROM fnd_languages
          WHERE language_code = p_lang_code;
   BEGIN
      /* To get the user id details */
      OPEN cur_user;

      FETCH cur_user
       INTO v_user_id;

      IF cur_user%NOTFOUND
      THEN
         v_return := 'F';
      END IF;     --IF cur_user%NOTFOUND

      CLOSE cur_user;

      /* To get the responsibility and responsibility application id */
      OPEN cur_resp;

      FETCH cur_resp
       INTO v_resp_id, v_resp_appl_id, v_lang;

      IF cur_resp%NOTFOUND
      THEN
         v_return := 'F';
      END IF;    --IF cur_resp%NOTFOUND

      CLOSE cur_resp;

      /* Setting the oracle applications context for the particular session */
      fnd_global.apps_initialize (user_id           => v_user_id,
                                  resp_id           => v_resp_id,
                                  resp_appl_id      => v_resp_appl_id
                                 );
      /* Setting the org context for the particular session */
      mo_global.set_policy_context ('S', v_org_id);

      /* setting the nls context for the particular session */
      IF v_session_lang != v_lang
      THEN
         OPEN cur_lang (v_lang);

         FETCH cur_lang
          INTO v_nls_lang;

         CLOSE cur_lang;

         fnd_global.set_nls_context (v_nls_lang);
      END IF;    --IF v_session_lang != v_lang

      RETURN v_return;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 'F';
   END set_context;

BEGIN
--1. Set applications context if not already set.
   v_context := set_context ('&user', '&respname', '&org_id');

   IF v_context = 'F'
   THEN
      DBMS_OUTPUT.put_line ('Error while setting the context');
   END IF;

   mo_global.init ('SQLAP');

   x_invoice_id := 1425;
   x_hold_lookup_code := 'LINE VARIANCE';
   x_release_lookup_code := 'VARIANCE CORRECTED';
   x_held_by := 5;
   x_calling_sequence := NULL;

   ap_holds_pkg.release_single_hold
         (x_invoice_id               => x_invoice_id,
          x_hold_lookup_code         => x_hold_lookup_code,
          x_release_lookup_code      => x_release_lookup_code,
          x_held_by                  => x_held_by,
          x_calling_sequence         => x_calling_sequence
         );

   BEGIN
      SELECT 'Y'
        INTO v_check_flag
        FROM ap_holds_all
       WHERE invoice_id = x_invoice_id
         AND release_lookup_code = 'VARIANCE CORRECTED';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_check_flag := 'N';
   END;

   DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);
END;


2 Responses to “API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)”

Aravindan said...
June 27, 2011 at 9:48 PM

Hi,
Is this API ap_holds_pkg.release_single_hold a published API? How was it concluded that this must be the API that must be used (apart from the fact that this procedure updates the ap_holds_all table)? Would oracle support a update done via this API?


Unknown said...
June 30, 2014 at 8:17 PM

Good and Useful document for a newbie.

Many Thanks


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.