Wednesday, July 18, 2007

Query: eBiz Setup of Books/Chart of Accounts keyflex segments

Thought I'd post a few of the common queries I run to save some retyping... here's the first! When I want a quick overview of the structure of clients chart of accounts, the 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. Looks big, but quite a bit of repetition here to get the segment attributes.

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;
And just for good measure here's the sample output from 11.5.10.2 Vision Demo instance.
SOB_NAME                           SOB_ID     COA_ID STRUCT_NAME                    SEGMENT_NAME                   COLUMN_NAME                    B C N I S G FLEX_VALUE_SET_NAME                                          FLEX_VALUE_SET_ID
------------------------------ ---------- ---------- ------------------------------ ------------------------------ ------------------------------ - - - - - - ------------------------------------------------------------ -----------------
Vision Operations (USA)                 1        101 Operations Accounting Flex     Company                        SEGMENT1                       Y N N Y N Y Operations Company                                                     1002470
Vision Operations (USA)                 1        101 Operations Accounting Flex     Department                     SEGMENT2                       N Y N N N Y Operations Department                                                  1002471
Vision Operations (USA)                 1        101 Operations Accounting Flex     Account                        SEGMENT3                       N N Y N N Y Operations Account                                                     1002472
Vision Operations (USA)                 1        101 Operations Accounting Flex     Sub-Account                    SEGMENT4                       N N N N N Y Operations Sub-Account                                                 1002473
Vision Operations (USA)                 1        101 Operations Accounting Flex     Product                        SEGMENT5                       N N N N N Y Operations Product                                                     1002474
Nice.

4 comments:

Nicki said...

Fantastic. Thanks so much for the information on the attachments. Not sure anyone will let me do it but of great use.

Nicki - South Africa

DJADM said...

Please, help me.
HOW TO COUNT:
1. Number of invoices for wchich the try of matching them with purchase order was undertaken
2. Numeber of matched invoices with success and quantity of undertaken tries of matching . I forgot adding,that subject concerns Oracle Financial AP

Gareth Roberts said...

Hi djadm,
If my answer here is not enough, please post your question on Oracle Forums - thats a better method to get an answer. As a pointer, you'll some form of auditing to track "count of try of matching them with Purchase Order" o may be a forms personalization/modification. Either an invoice is matched to a PO or its not - you can get that count easily: ap_invoice_distributions.po_distribution_id = po_distributions.po_distribution_id

Gareth

whimper said...

www.manyaklar.org