EPB : Unmapped Chart of Accounts

Categories: Other
- OGL EPB BALANCE RULE ERROR CHART OF ACCOUNTS HAVE NOT BEEN MAPPED PROPERLY : ZPB -   Ever encountered the unmapped accounts error during running of the GL balance rule? If you hit this error then the concurrent request output will display all the GL code combinations for which the run has failed. The common solution mentioned in metalink document is to re-submit all the dimension rules in order to catch any newly created dimension members. But knowing that the instance is a cloned instance from production and there is only one user currently working on this instance since the clone I was pretty sure that there were no new dimension members created. But when the Doc says, you have to religiously follow it before complaining further. So I did resubmit all the dimension rules and then re-ran the GL balance rule but the error persisted. I did further digging into the topic and found the following note 357959.1 which mentions that the issue could be related to the incompatible summary flag settings between the flex values of any of the code combination segments and the summary flag in the gl_code_combinations table. Let me clarify further, each GL chart of account is made of more than one segment (field). This segment has a list of values that is maintained in the fnd_flex_values table. This table contains a column known as summary_flag. So lets say the typical structure contains 5 segments Company, Account, Cost Center, product, Country then the values of these segments is maintained in the fnd_flex_values table. E.g case Company { 01, 02, ..99 }, Account { 1000, ,.... 9999} , Cost Center { 000 ... 999} , Country { US, UK ...IN etc}, And the combination of these values is stored in the gl_code_combinations table for eg. 01.1000.001.US , 01.2000.001.US , 03.1000.003.UK etc, and these combinations also have a summary_flag. So any of the above values either company -01 or Cost Center 000 or country UK could have the summary flag set differently than the summary flag in the code combinations table. To detect which segment values had the problem there are a bunch of scripts to be run as follows. You will need the following information: -- Chart of Account Id from #1 -- Code Combinations ID from #2 -- Segment and the flex_value_set_id of the specific segment from above select a.flex_value, b.code_combination_id, a.summary_flag as "Flex Summary Flag", b.summary_flag as "GL Summary Flag" from fnd_flex_values a, gl_code_combinations b where b. = a.flex_value and a.summary_flag <> b.summary_flag and b.chart_of_accounts_id = and b.code_combination_id in (value1, value2, value3, etc.) and a.flex_value_set_id = ; Example for the Chart of Account "Vision Operations" - SEGMENT3: select a.flex_value, b.code_combination_id, a.summary_flag as "Flex Summary Flag", b.summary_flag as "GL Summary Flag" from fnd_flex_values a, gl_code_combinations b where b.SEGMENT3 = a.flex_value and a.summary_flag <> b.summary_flag and b.chart_of_accounts_id = 101 and b.code_combination_id in (12831, 18068, 90094) and a.flex_value_set_id = 1002472; Repeat this query for all Segments.  You can find the list of segments and their value set id using the below script select segment_name, application_column_name, flex_value_set_id, enabled_flag from fnd_id_flex_segments where application_id = 101 and id_flex_code = 'GL#' and id_flex_num = ; But this seemed to be a lengthy and tedious process, so I used the trial version of InfoCaptor and quickly added one more qlet using the below query. select a.flex_value     "G_PARAM "      , b.code_combination_id, a.summary_flag as "Flex Summary Flag", b.summary_flag as "GL Summary Flag" from fnd_flex_values a, gl_code_combinations b where b.G_PARAM      /*application_column_name*/     = a.flex_value and a.summary_flag <> b.summary_flag and b.chart_of_accounts_id =     'G_PARAM  '    /*gsob.chart_of_accounts_id*/    and b.code_combination_id in     (     select code_combination_id     from gl_code_combinations    where segment4||'.'||segment1||'.'||segment3||'.'||segment2||'.'||segment5||'.'||segment6||'.'||segment7    in       ( '001.13105.000.290.000.000.00000', '001.28100.000.100.000.000.00000',  ......... ......... '001.19035.000.100.000.000.00000'       )     ) and a.flex_value_set_id =     'G_PARAM '    /*flex_value_set_id*/     The seeded dashboard has global parameters where you can pick the Set of books, Chart of accounts and the segment. Internally it can pass the segments flex_value_set_id and this is used inside the above dynamic query. So each time you run the above query the G_PARAM values are obtained at run time and results displayed on the grid. Using this I was able to determine that the country segment had 4 values that had the summary flag set to "Y" and the corresponding code combinations had summary flag set to "N". To fix, login to GL responsibility in order to modify the flex values. Query the country segment and their values. For the four defective settings "un-check" the parent checkbox and save it. (yes summary flag on the front end is displayed as parent checkbox) Then I went ahead and ran "undo" of GL Balance rule, then ran "undo" of country dimension, submitted all the dimension rules again and re-ran the GL Balance rule. This time the actuals load completed successfully :)   
« « What is Oracle Apps (ERP)?                        Ellison’s Buying Power? » »

Comments are closed.