FORM开发中Profiles的使用

用户配置文件相当于系统参数，可以在不同层级（Site层、应用模块层、责任层、用户层）设置不同的值；作用范围小的覆盖范围大的层，系统已经预设了很多user profile; 开发人员也可以定义

在EBS 中我们可能定义profile，用于存储特定的用户信息，以便在程序运行中进行调用。相当于全局变量.

1. Profile的定义

Path:application developer/profile

其中的hierarchy type 用来确定profile的作用范围。一般选security.

Profile的作用范围是底层优先的，也就是user 层的最优先，其次是organization….

比较常用的是根据responsibility 来提取profile的信息.

SQL Validation 是用来定义profile value 的value set的，当然不定义，然后手工输入也没问题，但为了安全，还是定义一个比较好，定义方法如下:

SQL="SELECT fv_region.flex_value "Log Mode", 
fv_region.flex_value 
into :visible_option_value, 
:profile_option_value 
FROM fnd_flex_value_sets fvs_region, 
fnd_flex_values fv_region 
WHERE fvs_region.flex_value_set_name = 'FAC_REORG_ASSET_REGION' 
AND fvs_region.flex_value_set_id = fv_region.flex_value_set_id 
AND fv_region.enabled_flag = 'Y' " 
COLUMN=""Log Mode"(10)"

2. Profile 的维护

Path: System administrator/profile/system

可根据需要，在site/application/responsibility/organization/user层对相应的profile进行赋值。

3.使用profile

几个相关函数的使用说明实例

1. FND_PROFILE.GET(‘Name of the Profile’,variable name);

[sql] view plain copy print ?

1. SELECT fnd_profile.value('PROFILEOPTION')

2.       ,fnd_profile.value('MFG_ORGANIZATION_ID')

3.       ,fnd_profile.value('ORG_ID')

4.       ,fnd_profile.value('LOGIN_ID')

5.       ,fnd_profile.value('USER_ID')

6.       ,fnd_profile.value('USERNAME')

7.       ,fnd_profile.value('CONCURRENT_REQUEST_ID')

8.       ,fnd_profile.value('GL_SET_OF_BKS_ID')

9.       ,fnd_profile.value('SO_ORGANIZATION_ID')

10.      ,fnd_profile.value('APPL_SHRT_NAME')

11.      ,fnd_profile.value('RESP_NAME')

12.      ,fnd_profile.value('RESP_ID')

13.  FROM DUAL;

SELECTfnd_profile.value('PROFILEOPTION')

      ,fnd_profile.value('MFG_ORGANIZATION_ID')

      ,fnd_profile.value('ORG_ID')

      ,fnd_profile.value('LOGIN_ID')

      ,fnd_profile.value('USER_ID')

      ,fnd_profile.value('USERNAME')

     ,fnd_profile.value('CONCURRENT_REQUEST_ID')

      ,fnd_profile.value('GL_SET_OF_BKS_ID')

      ,fnd_profile.value('SO_ORGANIZATION_ID')

      ,fnd_profile.value('APPL_SHRT_NAME')

      ,fnd_profile.value('RESP_NAME')

      ,fnd_profile.value('RESP_ID')

FROM DUAL;

2. variable name := FND_PROFILE.VALUE(‘Name ofthe profile’);

3. FND_PROFILE.PUT(‘Name of the profile’, valueof the profile);

[sql] view plain copy print ?

1. SET SERVEROUTPUT ON;

2. DECLARE

3.    v_conc_login_id      NUMBER;

4. BEGIN

5.    FND_PROFILE.put ('CONC_LOGIN_ID',1425);

6.    fnd_profile.get ('CONC_LOGIN_ID', v_conc_login_id);

7.    DBMS_OUTPUT.put_line (v_conc_login_id);

8. END;

9. Output:

10.1425

11.PL/SQL procedure successfully completed

SETSERVEROUTPUT ON;

DECLARE

   v_conc_login_id      NUMBER;

BEGIN

   FND_PROFILE.put ('CONC_LOGIN_ID',1425);

   fnd_profile.get ('CONC_LOGIN_ID',v_conc_login_id);

   DBMS_OUTPUT.put_line (v_conc_login_id);

END;

Output:

1425

PL/SQLprocedure successfully completed

FND_PROFILE.GET is the procedure and FND_PROFILE.VALUE is the function so,it return a value.

4.FND_PROFILE.SAVE used to set the profile values frombackend.

The table fnd_profile_options_tl, profileoptions names are kept. Now find the corresponding the Profileoption namefor which you need to update from backend. For thisexample I took my favorite“ORG_ID”

SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE user_profile_option_nameLIKE'MO%'

It returns more than one row but i can make out that "ORG_ID" is thePROFILE_OPTION_NAME for MO: Operating Unit. Now I need to know the Org_ID ofthe Org whose value is to be set in MO: Operating Unit. SO I use thesimpleselect as below

SELECT organization_id,NAME
FROMhr_all_organization_units;

From the organization name I find the one which will be the default OperatingUnit, and I note the ID. In my case the ID for my default Operating Unit is286. Now with the code below I set the profile option value usingfnd_profile.save.

DECLARE
stat BOOLEAN;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(100000);
stat := fnd_profile.SAVE('ORG_ID',286,'SITE');
   IF stat
THEN
DBMS_OUTPUT.put_line('Stat = TRUE - profileupdated');
ELSE
DBMS_OUTPUT.put_line('Stat = FALSE - profileNOT updated');
END IF;
   COMMIT;
END;

5.fnd_profile.defined
IF (fnd_profile.defined('TRANSACTION_DATE')) THEN
l_profile_value := TO_NUMBER(fnd_profile.value('TRANSACTION_DATE'));
....

In oracle user Profile functionality is provided inthe FND_PROFILE package and the FNDSQF library.

What is inside this API:

·        Retrieve user profile values for the current run-time environment

·        Set user profile values for the current run-time environment

There are various Objects that can be used with thisAPI's. These are discussed below:

1. Put :This can be used to put a value to the specified user profile option.

Usage:

·        FND_Profile.Put('PROFILE_NAME','New_Value')

·        FND_Profile.Put('USERNAME', Usr_Name)

·        FND_Profile.Put('RESP_ID', Resp_ID)

·        FND_Profile.Put('RESP_APPL_ID', Resp_App_ID)

·        FND_Profile.Put('USER_ID', User_ID)

2.DEFINED : this is function returns TRUE if a value hasbeen assigned to the specified profile option.

Usage:

·        SELECT fnd_profile.defined('ACCOUNT_GENERATOR:DEBUG_MODE')ACC_GEN_DEBUG_SESSION_MODE FROM DUAL;

3.GET :This is used to retrieve the current value of the specified user profileoption

Usage :

Different type of options can be retrieved like

·        FND_Profile.Get('PROFILENAME', Profile_name);

·        FND_Profile.Get('CONC_LOGIN_ID', Conc_login_id);

·        FND_Profile.Get('LOGIN_ID', loginid);

4.VALUE : This is function which returns a character string. Used to retrievethe current value of the specified user profile option.

Usage:

·        fnd_profile.value('PROFILEOPTION')

·        fnd_profile.value('MFG_ORGANIZATION_ID')

·        fnd_profile.value('login_ID')

·        fnd_profile.value('USER_ID')

·        fnd_profile.value('USERNAME')

·        fnd_profile.value('CONCURRENT_REQUEST_ID')

·        fnd_profile.value('GL_SET_OF_BKS_ID')

·        fnd_profile.value('ORG_ID')

·        fnd_profile.value('SO_ORGANIZATION_ID')

·        fnd_profile.value('APPL_SHRT_NAME')

·        fnd_profile.value('RESP_NAME')

·        fnd_profile.value('RESP_ID')

5.VALUE_WNPS: This is a function, returns a character string.This is Used to retrieve the current value of the specified user profile optionwithout caching it.

6.SAVE_USER :This is function used to save a value for a profileoption permanently to the database, for the current user level. It is necessaryto explicitly issue a commit when using this function. Returns TRUE if profile optionis successfully saved, otherwise FALSE.

7.SAVE :This is function used to save a value for a profile option permanently tothe database, for a specified level. It is necessary to explicitly issue acommit when using this function. Returns TRUE if profile option is successfullysaved, otherwise FALSE.

Usage

·        fnd_profile.save('GUEST_USER_PWD', 'GUEST/ORACLE', 'SITE');

8.INITIALIZE :This is used by internal Applications ObjectLibrary to initialize the internal profile information at the level context.
The cache is first cleared of all database options.

Usage:

·        fnd_profile.initialize(user_id);

9.PUTMULTIPLE :This is used by internal Applications Object Libraryto set multiple pairs of profile options and values.