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 1002474Nice.
4 comments:
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
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
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
www.manyaklar.org
Post a Comment