Hold on Account Payable Invoice in Oracle Apps R12


Package: AP_HOLDS_PKG.INSERT_SINGLE_HOLD

Effect Table: AP_HOLDS_ALL

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 languagecontext */
      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;

No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...