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
Hi,
I need AR to GL Reconciliation sql query and standard report anything,,, some one can advise me…
Thanks
Suresh
LikeLike
Hi Shivmohan,
I have a problem with Budget at Summary account level. One particular account at summary level is not passing the funds check for which the summary level budget balance is available. I need your help if you can suggest me a way out as it is a serious problem for me in the PROD instance.
Regards
Kumar
LikeLike