Oracle APPS Who's Columns Deep Drive and FND_PROFILE

Record History is one of the excellent feature implemented by Oracle to track the data changes made by the users. Today in this article I will write how Oracle tracks the data changes made on the application.

Record history information is stored in the backend tables  in the form of WHO columns.

Following are the WHO columns generally observed in almost all tables of Oracle Applications

    created_by                -  Keeps track of which user created the record(row)
    creation_date           -  Stores the date on which a row was created
    last_update_by        -  Keeps track of who last updated the row
    last_update_date    -  Stores the date on which the row was last updated
    last_update_login   -  Login Session ID of the user

Column Name    How data is populated?
created_by    TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’))
creation_date    SYSDATE
last_updated_by    TO_NUMBER(FND_PROFILE.VALUE(‘USER_ID’))
last_update_date    SYSDATE
last_update_login    TO_NUMBER(FND_PROFILE.VALUE(‘LOGIN_ID’))

Some more information on last_update_login:
Let’s assume you have a user called JOHN (say User Id = 1234).

When user JOHN  logs on to Oracle Applications a record is created in the FND_LOGINS table (say Login Id = 4321). While logged in, a change is made to a Sales Order Lines record (OE_OREDER_LINES_ALL table) – the Last Updated By will be set to 1234 and the Last Update Login will be set to 4321.

If JOHN logs off and then logs on again, another record is created in the FND_LOGINS table (say Login Id = 5432). While logged in this time, another change is made to the Sales Order Line record – the Last Updated By will still be 1234 and the Last Update Login will be updated to 5432.

The data in FND_LOGINS which will probably be purged in regular intervals and so you will only be able to find only the details of the most recent logins of the application.
How is this implemented in the application?

FND_STANDARD.SET_WHO Procedure is used to update the WHO columns in a Table when ever  DML operations are performed from the application.

You represent each of the WHO columns as hidden fields in each block of your form (corresponding to the WHO columns in each underlying table).  A call  to FND_STANDARD.SET_WHO in PRE-UPDATE and PRE-INSERT to populate these fields.



Deep Drive into the FND_PROFILE:



In oracle user Profile functionality is provided in the 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 this API'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 has been 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 profile option

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 retrieve the 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 option without caching it.

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

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

Usage

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

8.INITIALIZE :This is used by internal Applications Object Library 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 Library to set multiple pairs of profile options and values