Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions


Limiting Financial Data Depending on Applications Profile

597043 Member Posts: 115
edited Aug 27, 2008 8:43PM in Discoverer
Hi all,

Oracle Discoverer Administrator
Oracle End User Layer
Oracle Database
Oracle EBS

I have an Applications EUL and have created several custom Business Areas around the financial modules of EBS.

I am experiencing a problem when I try and limit the Item Class (LOV) values on a folder based on Database table GL_CODE_COMBINATIONS. There are values appearing from other Chart of Account Structures even though I have joined this to a Folder based on GL_SETS_OF_BOOKS, which has a Mandatory Condition on it to only return values for System Profile GL_SET_OF_BKS_ID.

I have registered PL/SQL Function FND_GLOBAL.VALUE as VALUE and am using it in the Mandatory Condition on the folder based on GL_SETS_OF_BOOKS.


Then I tried creating a Custom Folder with the following SQL which I proved returned the correct values in an apps initialised TOAD environment:
select gcc.*
from   gl_sets_of_books      glb
,      gl_code_combinations  gcc
where  glb.set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID')
and    glb.chart_of_accounts_id = gcc.chart_of_accounts_id
Again, when I login into Desktop or Plus with my Apps user and choose my Responsibility (set as GL_SET_OF_BKS_ID = 41) and run my Worksheet I am prompted with ALL values in my LOV not the restricted set I expected.

Your help is appreciated.

Have a great day :-)

Kind Regards,


  • Tamir Lazover
    Tamir Lazover Member Posts: 1,288
    The LOV is not being regenerate whenever you use them so you will not get what you expect.
    You will get all the values according to the security on the user you used while creating the LOV.
    What you can do is create a hierarchy of LOV and then limit the values you get based on another parameter (such as SOB or ORG).
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy

    The fnd_profile package does lots of cacheing that can cause problems. You should try using
    glb.set_of_books_id = to_number(fnd_profile.value_specific('GL_SET_OF_BKS_ID', fnd_global.USER_ID, fnd_global.RESP_ID, fnd_global.RESP_APPL_ID))
    glb.set_of_books_id = gl_security_pkg.LOGIN_SOB_ID
    Rod West
  • 597043
    597043 Member Posts: 115
    Hi all,

    Thanks for your valuable responses Tamir and Rod :-)

    I'm still not sure why this didn't work on the first occasion but I deleted all the Segment value Item Clasess I had created and recreated them. I ensured I connected to the Discoverer Administrator as a Responsibility with a System Profile GL Set of Books (GL_SET_OF_BKS_ID) equal to the value that will limit the data as required.

    Then I ammended my Custom Folder AR GL Code Combinations with the following SQL, which I again proved returned the correct values in an apps initialised TOAD environment:
    SELECT gcc.*
    FROM   gl_sets_of_books      glb
    ,      gl_code_combinations  gcc
    WHERE  glb.set_of_books_id = GL_SECURITY_PKG.LOGIN_SOB_ID
    and    glb.chart_of_accounts_id = gcc.chart_of_accounts_id
    This time however when it came to creating the Item Class I only chose the AR GL Code Combinations.Segmentx (previously I had also included the Complex folder Items that I wanted to attatch the Item Class to).
    So the next step was to remove all the Segment values from the Complex folder and replace them with the Segment values from the Custom Folder AR GL Code Combinations.
    I don't know if that all makes sense to you, but the bottom line is that my LOVs are now limiting as expected :-)
    Thanks again for your input.
    Kind Regards,
This discussion has been closed.