Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

The examples set up the session for SYSADMIN user, System Administrator responsibility.

e.g. SQL*Plus   exec fnd_global.apps_initialize(0,20420,1);

e.g. for PL/SQL, TOAD, SQLDeveloper, SQL Navigator etc.:

begin fnd_global.apps_initialize(0,20420,1); end;

The parameters used here are:

1.        User_ID

2.        Responsibility_ID

3.        Responsibility_Application_ID

To get these you have a couple of choices

a) SQL – Replace SYSADMIN and System Administrator with your user and responsibility:

select 'begin fnd_global.apps_initialize(' ||

       fu.user_id || ',' ||

       fr.responsibility_id || ',' ||

       fr.application_id || '); end;' || chr(10) || '/'

     from   fnd_user fu

,      fnd_responsibility_tl fr

where  fu.user_name = 'SYSADMIN'

and    fr.responsibility_name = 'System Administrator';

b) In your Oracle Applications forms session. Login as your user and navigate to the required responsibility. Open a function that uses Oracle forms Go to Help > Diagnostics > Examine In the Block enter $PROFILES$ In the field enter the appropriate field name for the parameter:

1.        User_ID = USER_ID

2.        Responsibility_ID = RESP_ID

3.        Responsibility_Application_ID = RESP_APPL_ID

Oracle 11i / R12 – How To : How To Disable The Personal Profiles Form

Disable The Personal Profiles Form


1.As System Administrator Navigate to  Security->Responsibility->Define.

2. Query up the responsibility assigned to the user .

3. Navigate to the Menu Exclusions tab.

4. In the type field choose "Function" and in the name choose "Profile User Values" FND_FNDPOMSV.

5. Save the changes.
6. Exit and enter the application again as the responsibility just modified.

7. Now you will see the Edit -> Preferences -> Profiles dimmed.

Distinction between configuration, personalization, and extensibility.

Distinction between configuration, personalization, and extensibility.

Configuration provides setup and administrative choices using the native features of the product. Some configuration examples include:

  • Profile Options
  • User-defined fields (Flexfield)
  • Function Security Setup
  • Data Security Setup
  • and a lot more….

Personalization enables you to declaratively tailor the UI look-and-feel, layout or visibility of page content to suit a business need or a user preference. Some personalization examples include:

  • Tailor the order in which table columns are displayed.
  • Tailor a query result.
  • Tailor the color scheme of the UI.
  • Folder Forms
  • Forms Personalization
  • Oracle Application Framework (OAF)

Extensibility is about extending the functionality of an application beyond what can be done through personalization. Some extensibility examples include:

  • Add new functional flows
  • Extend or override existing business logic
  • Using Oracle Forms Developer, Oracle JDeveloper and Oracle Workflow

Oracle Applications – Commonly Useful SQL Queries

Hello friend, here some of the commonly used sql, i tried to put sql query but don’t know how to put in proper format in html, so if u like to use these sql, u need to remove formatting before to use. let give ur feedback if these are useful for you…. thanks

Quick overview of the structure of chart of accounts

Following query comes in handy. Shows chart of accounts, set of books, accounting flexfield segments in use and their attributes, value sets for the key flexfield segments.

Select sob_name

,      sob.set_of_books_id sob_id

,      sob.chart_of_accounts_id coa_id

,      fifst.id_flex_structure_name struct_name

,      ifs.segment_name

,      ifs.application_column_name column_name

,      sav1.attribute_value  BALANCING

,      sav2.attribute_value COST_CENTER

,      sav3.attribute_value NATURAL_ACCOUNT

,      sav4.attribute_value  INTERCOMPANY

,      sav5.attribute_value SECONDARY_TRACKING

,      sav6.attribute_value GLOBAL

,      ffvs.flex_value_set_name

,      ffvs.flex_value_set_id

from   fnd_id_flex_structures fifs

,      fnd_id_flex_structures_tl fifst

,      fnd_segment_attribute_values  sav1

,      fnd_segment_attribute_values sav2

,      fnd_segment_attribute_values sav3

,      fnd_segment_attribute_values  sav4

,      fnd_segment_attribute_values sav5

,      fnd_segment_attribute_values sav6

,      fnd_id_flex_segments ifs

,      fnd_flex_value_sets ffvs

,      gl_sets_of_books sob

where  1=1

and    fifs.id_flex_code = ‘GL#’

and    fifs.application_id =  fifst.application_id

and    fifs.id_flex_code = fifst.id_flex_code

and    fifs.id_flex_num = fifst.id_flex_num

and    fifs.application_id =  ifs.application_id

and    fifs.id_flex_code = ifs.id_flex_code

and    fifs.id_flex_num = ifs.id_flex_num

and    sav1.application_id =  ifs.application_id

and    sav1.id_flex_code = ifs.id_flex_code

and    sav1.id_flex_num = ifs.id_flex_num

and    sav1.application_column_name =  ifs.application_column_name

and    sav2.application_id =  ifs.application_id

and    sav2.id_flex_code = ifs.id_flex_code

and    sav2.id_flex_num = ifs.id_flex_num

and    sav2.application_column_name =  ifs.application_column_name

and    sav3.application_id =  ifs.application_id

and    sav3.id_flex_code = ifs.id_flex_code

and    sav3.id_flex_num = ifs.id_flex_num

and    sav3.application_column_name =  ifs.application_column_name

and    sav4.application_id =  ifs.application_id

and    sav4.id_flex_code = ifs.id_flex_code

and    sav4.id_flex_num = ifs.id_flex_num

and    sav4.application_column_name =  ifs.application_column_name

and    sav5.application_id =  ifs.application_id

and    sav5.id_flex_code = ifs.id_flex_code

and    sav5.id_flex_num = ifs.id_flex_num

and    sav5.application_column_name =  ifs.application_column_name

and    sav6.application_id =  ifs.application_id

and    sav6.id_flex_code = ifs.id_flex_code

and    sav6.id_flex_num = ifs.id_flex_num

and    sav6.application_column_name =  ifs.application_column_name

and    sav1.segment_attribute_type =  ‘GL_BALANCING’

and    sav2.segment_attribute_type = ‘FA_COST_CTR’

and    sav3.segment_attribute_type = ‘GL_ACCOUNT’

and    sav4.segment_attribute_type  = ‘GL_INTERCOMPANY’

and    sav5.segment_attribute_type =  ‘GL_SECONDARY_TRACKING’

and    sav6.segment_attribute_type =  ‘GL_GLOBAL’

and    ifs.id_flex_num = sob.chart_of_accounts_id

and    ifs.flex_value_set_id = ffvs.flex_value_set_id

— comment the next expression to show all books

— currently it show the info for the site level set profile option value

and    sob.set_of_books_id =


order by, sob.chart_of_accounts_id, ifs.application_column_name;


 Query: Find concurrent requests’ status

How about an all in one query? Note: this may not be complete, so any comments welcome! Also we limit to those requests submitted in the last hour.

select fcr.request_id

,      decode(fcr.phase_code






             ) phase

,      decode(fcr.phase_code


                        ,’Y’,’On Hold’

                        ,decode(sign(fcr.requested_start_date – sysdate)





             ) status

from   fnd_concurrent_requests fcr

,      fnd_lookups fl_p

,      fnd_lookups fl_s

where  1=1

and    fcr.phase_code = fl_p.lookup_code

and    fl_p.lookup_type = ‘CP_PHASE_CODE’

and    fcr.status_code = fl_s.lookup_code

and    fl_s.lookup_type = ‘CP_STATUS_CODE’

and    fcr.request_date > sysdate – 60/1440

order by fcr.request_id desc;


 Concurrent Programs by User and Responsibility

 The following query is a combination SQL behind the “Responsibilities” for a given user on the Users form, and the list of values for the Request Name (Concurrent Program) on the “Submit Requests” form, for version (11i.10.2). This can be useful to provide an “Audit” of what concurrent requests/programs a given Oracle eBusiness Suite user or Responsibility has access to.

 select distinct


,      r.responsibility_name

,      p.user_concurrent_program_name

,      a.application_name

,      a.application_short_name

,      p.concurrent_program_name

,      p.concurrent_program_id

,      p.application_id program_application_id

from fnd_concurrent_programs_vl p

,    fnd_application_vl a

,    fnd_request_group_units u

,    fnd_responsibility_vl r

,    fnd_user fu

,   (select user_id

     ,      responsibility_id

     ,      responsibility_application_id — ,start_date,end_date

     from   fnd_user_resp_groups_indirect

     where (responsibility_id,responsibility_application_id)

        in (select responsibility_id, application_id

            from   fnd_responsibility

            where (version = ‘4’ or

                   version = ‘W’ or

                   version = ‘M’ or

                   version = ‘H’)


     and nvl(start_date,sysdate-1) <= sysdate

     and nvl(end_date,sysdate+1) > sysdate


     select user_id

     ,      responsibility_id

     ,      responsibility_application_id

     from   fnd_user_resp_groups_direct

     where (responsibility_id,responsibility_application_id)

        in (select responsibility_id, application_id

            from   fnd_responsibility

            where (version = ‘4’ or

                   version = ‘W’ or

                   version = ‘M’ or

                   version = ‘H’))

     and nvl(start_date,sysdate-1) <= sysdate

     and nvl(end_date,sysdate+1) > sysdate

     ) user_resps

where p.srs_flag in (‘Y’, ‘Q’)

and   p.enabled_flag = ‘Y’

and   request_set_flag = ‘N’

and (

      (a.application_id = u.unit_application_id

       and u.application_id = r.group_application_id

      and u.request_group_id = r.request_group_id

       and u.request_unit_type = ‘A’)

   or (p.application_id = u.unit_application_id

       and p.concurrent_program_id = u.request_unit_id

       and u.application_id = r.group_application_id

       and u.request_group_id = r.request_group_id

       and u.request_unit_type = ‘P’))

and p.application_id = a.application_id

and user_resps.user_id = fu.user_id

and user_resps.responsibility_id = r.responsibility_id

and user_resps.responsibility_application_id = r.application_id

order by 1,2,4;


Thanks – Shivmohan Purohit