Friday, 20 May 2016

Oracle Apps: How to set Profile Option Value from backend

In this post, I will just post a sample code to set a profile option value from the backend.

This is the sample screen shot of the Profile Option Definition.
Navigation :- Application Developer --> Profile --> System


To set the profile option value, use the below code.
 declare  
   lb_return boolean;  
 begin  
   -- To set the Profile value at site level  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'S', 'SITE');
  
   --To set the Profile value at user Level -- UserName:-348019  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'U', 'USER',146356);
  
   --To set the Profile value at user Level -- Application Name :-General Ledger  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'A', 'APPL',101);
  
   --To set the Profile value at user Level -- Responsibility Name :-General Ledger  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'R', 'RESP', 20434, 101);
  
   --Commit the changes  
   commit;
  
 end;  
After executing the above code:

SAVE procedure:

   /*  
   ** save  
   **  Saves the value of a profile option permanently to the database, at any  
   **  level using the FND_PROFILE_OPTION_VALUES_PKG. This routine can be used  
   **  at runtime or during patching. This routine will not actually commit  
   **  the changes; the caller must commit.  
   **  
   **  ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').  
   **  
   **  Examples of use:  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVER', 25);  
   **    FND_PROFILE.SAVE('P_NAME', 'ORG', 204);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, 25);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, -1);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', -1, -1, 25);  
   **  
   **  Arguments/Parameters:  
   **    profileName    - Profile name you are setting  
   **    profileValue   - Profile value you are setting  
   **    levelName     - Level that you're setting at:  
   **              'SITE','APPL','RESP','USER', etc.  
   **    levelValue    - context value used for setting profile option  
   **              value, e.g. user_id for 'USER' level. This does  
   **              not apply to 'SITE' level.  
   **    levelValueAppId  - applies to 'RESP' and 'SERVRESP' levels, i.e.  
   **              the Resp Application_Id.  
   **    levelValue2    - 2nd context value used for setting profile  
   **              option value. This applies to the 'SERVRESP'  
   **              hierarchy.  
   **  
   ** RETURNS: TRUE if successful, FALSE if failure.  
   */  
   FUNCTION SAVE(x_name IN VARCHAR2,  
          /* Profile name you are setting */  
          x_value IN VARCHAR2,  
          /* Profile value you are setting */  
          x_level_name IN VARCHAR2,  
          /* Level that you're setting at: 'SITE','APPL','RESP','USER',  
                   etc. */  
          x_level_value IN VARCHAR2 DEFAULT NULL,  
          /* Level value that you are setting at, e.g. user id for 'USER'  
                   level. X_LEVEL_VALUE is not used at site level. */  
          x_level_value_app_id IN VARCHAR2 DEFAULT NULL,  
          /* Used for 'RESP' and 'SERVRESP' level; Resp Application_Id. */  
          x_level_value2 IN VARCHAR2 DEFAULT NULL  
          /* 2nd Level value that you are setting at. This is for the  
                   'SERVRESP' hierarchy. */)  


Related links : Oracle Apps: Query to get the profile option values from backend



Feel free to point out if anything is missing/wrong in this blog.


3 comments:

  1. Above script is not helping.

    We should use below script in order to pass level_value and level_name .

    SET SERVEROUTPUT ON;
    DECLARE
    stat boolean;
    BEGIN
    dbms_output.disable;
    dbms_output.enable(100000);
    --stat := FND_PROFILE.SAVE('ORG_ID', valid_organization_id, 'SITE');
    stat:=FND_PROFILE.SAVE(
    X_NAME=>'XLA_DISABLE_GLLEZL',
    X_VALUE=>'N',
    X_LEVEL_NAME=>'SITE',
    X_LEVEL_VALUE=> 'SITE');
    IF stat THEN
    dbms_output.put_line( 'Stat = TRUE - profile updated' );
    ELSE
    dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
    END IF;
    end;

    ReplyDelete
    Replies
    1. Your script doesn't look right.. What is the value you are trying to set and at what level ?

      Delete