Wednesday, June 15, 2011

API to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)

                                                                                                         

Below script will help you apply a hold on AP invoice in oracle apps R12

API: AP_HOLDS_PKG.INSERT_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_hold_type          VARCHAR2 (200);
   x_hold_reason        VARCHAR2 (200);
   x_held_by            NUMBER;
   x_calling_sequence   VARCHAR2 (200);
   v_check_flag         VARCHAR2 (1);
   v_context            VARCHAR2 (10);

   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', '&resp_name’, '&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_hold_type := 'LINE HOLD REASON';
   x_hold_reason := 'Total of Invoice Lines does not equal Invoice Amount';
   x_held_by := 5;
   x_calling_sequence := NULL;

   AP_HOLDS_PKG.INSERT_SINGLE_HOLD
            (x_invoice_id            => x_invoice_id,
             x_hold_lookup_code      => x_hold_lookup_code,
             x_hold_type             => x_hold_type,
             x_hold_reason           => x_hold_reason,
             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 hold_lookup_code = 'LINE VARIANCE';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_check_flag := 'N';
   END;

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


0 Responses to “API to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)”

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.