if you are looking for a quick answer to fetch the Charts Of Accounts Structure, query is given below. The query here will fetch you following list of columns:
1. Business Unit Name
2. Ledger Name
3. Charts Of Accounts Name
4. Segment Name
5. Value Set Code
6. Value Set Description
But in case you have some time in your hands and want to know a bit more in detail to get the idea of enterprise structure and where does Chart Of Accounts fit in the whole picture, I would suggest you go pass the query and read the “Brief Details”. It will be worth it, I promise.
Charts Of Accounts Structure Query
select hou.name BU_NAME,
led.NAME Ledger_Name,
Str.STRUCTURE_CODE CHART_OF_ACCOUNTS_Name,
SegInSt.SEGMENT_CODE COA_SEGMENT_NAME,
vs.value_set_code,
vs.description value_set_desc
from hr_operating_units hou,
xla_gl_ledgers led ,
fnd_kf_structures_b Str,
fnd_kf_str_instances_b StrInSt,
fnd_kf_segment_instances SegInSt,
fnd_vs_value_sets vs
where 1=1
and led.ledger_id = hou.set_of_books_id
AND led.CHART_OF_ACCOUNTS_ID = Str.Structure_id
AND Str.KEY_FLEXFIELD_CODE = ‘GL#’
AND Str.Structure_id = StrInSt.Structure_id
and SegInSt.Structure_inStance_id = StrInSt.Structure_inStance_id and vs.VALUE_SET_ID = SegInSt.VALUE_SET_ID
If you look at the query above, you will see that every Business Unit has something called “Set of Books”. This Set Of Books is nothing but the parent of Charts Of Accounts. Other children of Set of books are Currency & Calendar. Now further, this Chart Of accounts is nothing but segmented structure in which various accounts are stored & used for reporting etc. Generally companies in US use 7 or 8 segment structure but of course, it is not the eternal truth and can change company to company.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Brief Details: Every Business Unit has a ledger and ledger has a Chart Of Account. Chart of account is nothing but the structure of the accounts like 7 segment or 8 segment. Each segment has a value set attached to validate the values. Now depending on the Charts Of Accounts Structure of your enterprise, each BU can have same or different Ledger ( set of books). This query will give you details of the Chart of account of each BU.
Related Posts:
Business Unit – Legal Entity – Ledger/ set of Books Relationship Query
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
