Monday, November 7, 2016

Query to add any responsibility to a user

The following query adds a particular responsibility to a particular user. This query needs to be run by APPS.


The query will prompt for a "User Name" and "Reponsibility Name" that need to be added to that user. The query first finds the RESPONSIBILITY_KEY and APPLICATION_SHORT_NAME for that responsibility, and then adds it to the user using Oracle's FND_USER_PKG.ADDRESP function.

In the following example, I used 'AMOHSIN' as my username, and added "System Administrator" to my responsibilities. Change these two input parameters as per your requirement.


-------------------------------------------------------------------------------
-- Query to add a responsibility to a user, using FND_USER_PKG.ADDRESP
-------------------------------------------------------------------------------
DECLARE
   v_username         fnd_user.user_name%TYPE;
   v_resp_key         fnd_responsibility.responsibility_key%TYPE;
   v_apps_short_name  fnd_application.application_short_name%TYPE;
   v_resp_name        fnd_responsibility_tl.responsibility_name%TYPE;
       
BEGIN
   v_username   :=  '&USER_NAME';  -- eg. 'AMOHSIN'
   v_resp_name  :=  '&RESP_NAME';  -- eg. 'System Administrator'
   
   -------------------------------------------------------------
   -- find APPLICATION_SHORT_NAME and RESPONSIBILITY_KEY for
   -- the Responsibility that need to be added
   -------------------------------------------------------------
   SELECT fr.responsibility_key,
          fa.application_short_name
     INTO v_resp_key,
          v_apps_short_name
     FROM applsys.fnd_responsibility_tl      frt,
          applsys.fnd_responsibility         fr,
          applsys.fnd_application_tl         fat,
          applsys.fnd_application            fa
    WHERE fr.responsibility_id     =  frt.responsibility_id
      AND fa.application_id        =  fat.application_id
      AND fr.application_id        =  fat.application_id
      AND frt.language             =  USERENV('LANG')
      AND fat.language             =  USERENV('LANG')
      AND frt.responsibility_name  =  v_resp_name;
      
   -------------------------------------------------------------
   -- if found, then add it to the user; else jump into exception
   -------------------------------------------------------------
   FND_USER_PKG.ADDRESP(
      USERNAME        =>  UPPER(v_username), -- User Name: 'AMOHSIN'
      RESP_APP        =>  v_apps_short_name, -- Apps Short Name: 'SYSADMIN'
      RESP_KEY        =>  v_resp_key,        -- Resp Key: 'SYSTEM_ADMINISTRATOR'
      SECURITY_GROUP  =>  'STANDARD',
      DESCRIPTION     =>  NULL,
      START_DATE      =>  SYSDATE,
      END_DATE        =>  NULL);
  
   COMMIT;
  
   DBMS_OUTPUT.PUT_LINE(v_resp_name || ' responsibility added successfully for ' || v_username);
    
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(v_resp_name || ' responsibility not added for ' || v_username);
      DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SUBSTR(SQLERRM1100));
      ROLLBACK;
END;


No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...