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_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 =

        nvl(fnd_profile.value(‘GL_SET_OF_BKS_ID’),sob.set_of_books_id)

order by  sob.name, 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

             ,’P’,decode(fcr.hold_flag

                        ,’Y’,’Inactive’

                        ,fl_p.meaning

                        )

             ,fl_p.meaning

             ) phase

,      decode(fcr.phase_code

             ,’P’,decode(fcr.hold_flag

                        ,’Y’,’On Hold’

                        ,decode(sign(fcr.requested_start_date – sysdate)

                               ,1,’Scheduled’

                               ,fl_s.meaning)

                  )

             ,fl_s.meaning

             ) 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 11.5.10.2 (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

       fu.user_name

,      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

     union

     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