FSG Best Practice

Document Sample
FSG Best Practice Powered By Docstoc
					Document Name: FSG Report Writing Guidelines

Date: 10/06/2006

Author: Daniel North, ORAFINAPPS Limited

Version: 3
                                       Table of Contents

1      Purpose                                                  5

2      High Level Development Plan - Roles & Responsibilities   5
2.1     Report Development Procedure                             5
2.2     Your Application Structure & Existing FSG Reports        5

3      FSG Report Naming Conventions                            6
3.1     Row Order Naming Conventions                             6
3.2     Column Set Naming Conventions                            7
3.3     Content Set Naming Conventions                           7
3.4     Row Set Naming Conventions                               8
3.5     Report Naming Conventions                                8
3.6     Naming Convention Conclusions                            8

4      Report Coding ‘Best Practices’                           9
4.1     Best Practice: Row Sets                                  9
4.2     Best Practice: Column Sets                              11
4.3     Best Practice: Content Sets                             11
4.4     Best Practice: Row Orders                               12
4.5     Best Practice: Reports                                  13

5      FSG Row/Column Overrides                                 14

6      Coding in Reconciliation Controls                        15

Report Coding Tools                                             16
6.1     ADI is best for:                                        16
6.2     Oracle Core Application Screens in GL are best for:     16

7      Using Control Values for Currency & Budgets              17

8      International Reporting With FSG                         18
8.1     Language                                                18
8.2     Rigid Code Values and Descriptions                      18
8.3     Horizontal/Vertical Report Formats.                     18
8.4     Continental Style Balance Sheet and P&L                 18

9      FSG Transfer to Production                               21
9.1     Suggested Procedure                                     21
9.2     Oracle Functionality                                    22
9.3     FSG Transfer Issues                                     23

10 FSG Support & Maintenance Procedures                         24
10.1       Maintenance & Support Structure                      24
10.2       Maintaining Budgets                                  24
10.3       Statistical Balances                                 25
10.4       Chart of Account Changes                             25
10.5   Other Considerations               25
10.6   Using FSG ‘Standard’ Reports       26
10.7   FSG SQL Scripts                    27

11 Leveraging ADI Tools & Functionality   28
11.1   Report Scheduling                  28
11.2   FSG Drilldown                      28
11.3   Hierarchy Editor.                  28
1.0        June 1999   First draft – D North
2.0        2000-2006   Updated for various client sites
3.0        July 2007   Updated for VWUK

COMPANY & ROLE         NAME                           ACTION

COMPANY & ROLE         NAME                        DATE        SIGNATURE
1 Purpose
The purpose of this document is to present a high level strategy and practical approach for the
implementation and maintenance of a suite of FSG reports to support the financial reporting
requirements of a group of companies running Oracle General Ledger.

2 High Level Development Plan - Roles & Responsibilities
Before any widespread FSG report development is undertaken it is necessary to define the roles
and responsibilities within the business and project team. This is to ensure that consistent report
coding standards are used and also to ensure that there is a single person who has overall
responsibility for FSG reports and the global consistency of key reports.

This is required so that there is there is a report gate-keeper to perform quality and reasonableness
tests to reports before they are released to production.

2.1       Report Development Procedure

Within the production environment the ability to update FSG reports and their components should
be very tightly controlled. The reason for this is firstly to prevent many untested reports being
entered into production, and secondly to prevent existing reports and their components being
updated without the knowledge of all users that rely upon them.
The procedure for adding or updating FSG reports is for the change requestor or a regional report
administrator to write them in a development environment or enlist the assistance from a member
of the Oracle project or support team. Most users should have a level of access in development
that allows them to create FSG reports.
Even if the users are coding the report themselves, an FSG template should accompany all FSG
requests. This means that if at some stage the writer has to seek assistance, they are able to
review the scope and objectives of the report and a better placed to assist. The driver for any
changes to existing reports needs to be the report owner, who will also need to sign of any changes
before they are moved into production. This is also true of any newly developed reports, which will
need to be signed-off by the report owner even if they are not the report writer.

Once it has been determined FSG is the most suitable tool for providing the solution then the report
requestor ( with the assistance if required ) can complete an FSG request template via the
helpdesk or Oracle support team. This can then be used to help track the development of the
report through to production.

2.2       Your Application Structure & Existing FSG Reports

If you are not sure about the details of your set of books and chart of accounts structure then ask
your Oracle system administrator to run the following scripts for you.

          Set Of Books Configuration Overview

          Chart Of Accounts Structure

These will detail how many sets of books you have, which books share chart of accounts, and also
the details of the chart of accounts such as which value sets are shared.

3 FSG Report Naming Conventions
If you are running a single production instance then some of the FSG components are visible
across all countries. For this reason, very tight control should be kept over report naming
conventions. This means that if a report or report object is for the use of a specific country then it
should be clearly indicated as such with consistent naming conventions. To reduce the sheer
number of reports on the system, every opportunity to share components between countries should
be taken.
The proposals for managing the naming conventions are as follows.
Firstly if the component is global ( such as non-CoA specific Column Sets ) or if it is a standard
component added to each chart of accounts then it should be prefixed with the company identifier,
in this case ‘VS’ for Vision Industries.
For country specific reports or components a two letter county identifier follows the company
identifier and precedes the name of any component. In most cases this can be the three character
set of books code. For example the components used by the Spanish tax book may be preceded
by the letters ‘ES’.

For example:
          VS is a global component that can be seen across all chart of accounts
          VSES, VSUS, VSGB represent standard global components in the Spanish, American and
           British chart of accounts respectively.
          ES, US & GB are local components that only relate to a specific country and do not need to
           be maintained globally.

After the country identifier you may want to then give an indication as to whether the component is
specific to the tax books or the management books. For example
          ‘ESTX P&L’ : Is specific to the Spanish statutory books
          ‘VSESMT P&L’ Is the standard global management P&L in the Spanish CoA

The benefit of using this method of identification is that although the reports and components are
not visible across different sets of books, when being submitted, modified or just reviewed on paper
they are still clearly indicated as belonging to a specific country and type (either management or
tax), This will also aid review and maintenance in the future when you are reviewing report
components using SQL or tools such as Oracle Discoverer that look across many sets of books.

One of the aims of a co-ordinated implementation of FSG report should also be to wherever
possible reduce the number of reports on the system. The scope for doing this varies greatly
between components, therefore a brief discussion on the opportunities available to each
component is provided in the following ‘Best Practices’ section.
When adding generic components to the system as much information about that component as
possibly should be provided within the name.

How this can be done varies between components, but some suggestions are given below.
3.1       Row Order Naming Conventions

If each set of books in your environment has a different chart of accounts, then Row Orders are
only visible in the set of books in which they have been coded. However as Row Orders interact
closely with the Column Sets it is important the Row Orders are standardised as much as possible
so that they can be used with the generic Column Sets.
In order to standardise components such as Row Orders across multiple sets of books you can
either use the FSG transfer program to transfer between books across different environments, or
you can use an unsupported update script to rename the components in a development
environment prior to transfer. The merits of each approach are discussed in the maintenance
section of this document.
When naming a generic Row Order there are three things that need to be conveyed.

              The segments the Row Order alters
           The segment value / description / both
           The width of the description

Some simple examples of how this can be achieved are as follows.
VSGBMT 1V2V3VD(80)           Standard global component on the UK chart of accounts in the
                             management books showing the segment 1 value, segment 2 value and
                             segment 3 value and description all within 80 characters.
VSUSMT 1V2V3VD(80)           As above but for the US chart of accounts.
FRTX 1VD2VD3VD5V(130)        A component specific to the French chart of accounts in the tax books ( As
                             opposed to a global standard ) showing the segment 1 value, segment 2
                             value & description, segment 3 value and description and segment 5 value
                             all within 130 characters.

3.2     Column Set Naming Conventions

Column Sets are often the only component that can be used across multiple charts of accounts
because as long as no account assignments are included then they are not linked to any particular
Firstly the names should follow the prefixes mentioned above, so they would start as either VS,
VSES or ES for a global component, standard local or a local component.
The name then needs to convey the some or all of the following information to the users
           Period type
           Amount type
           Intended use
           Calculations

As Column Sets are a much more flexible component than Row Orders then the naming
conventions cannot be so strict. But some suggestions are given below.

COLUMN SET NAME                      MEANING
VS PTD12 Rolling Monthly+YTD (80)    From this it is possible to determine that the Column Set is global
                                     and can be seen across all charts of accounts and gives 12 period
                                     to date columns with rolling monthly offsets, has a year to date
                                     total column at the end and has an offset of 80 Characters for the
                                     description , so that it can be used with Row Orders that fit into
                                     the 80 Characters, as per the examples above.
VS PTD&YTD to LY Var(80)             The description of this Column Set shows that the column has a
                                     comparison of current period to date with the same period last
                                     year and an actual variance between the two. It also has a
                                     comparison of current period year to date balance with the same
                                     period last year and a variance between to the two. Finally that it
                                     has the space for a 80 character description on the left hand side
                                     of the report.
USTX YTD Act by Entity(80)           This Column Set is for the US chart of accounts only and has the
                                     entity value ( Balancing Segment ) hard coded by column.

3.3     Content Set Naming Conventions

Generally Content Sets cannot be shared across chart of accounts. The prefix for content sent
names follows that of the components above ( VS, VSES, ES …) and then in addition to that the
following information needs to be provided in a Content Set name.
           The account segments referenced
           The display options selected
Some examples of generic Content Set names are provided below

CONTENT SET NAME                    MEANING
VSUSMT 1RE2RE3RE                    This is a global Content Set in the US Management books. It
                                    expands each row in a report by segments 1, 2 & 3.
VSGBMT 1PE                          This is a global Content Set in the GB Management books. It
                                    would provide a separate report for each segment1 value.
USMT 2PE Regional Summary           This is a Content Set specific to the US management books and
                                    gives a page ( spreadsheet tab in ADI ) by segment 2 values, but
                                    the additional description shows that it is summarised by region at
                                    the parent level.

3.4     Row Set Naming Conventions

The naming of Row Sets if likely to be very specific as they all have very clear user defined
purposes. Some examples are provided below.

ROW SET NAME                        MEANING

ESTX Statutory P&L                  Spanish Tax book statutory P&L Row Set
VSGBMT Corporate P&L                Global standard corporate P&L in the GB chart of accounts
VSITMT Balance Sheet                Global standard Balance Sheet in the Italian chart of accounts
USMT Technology Spend Analysis      US Management books technology spend analysis.

3.5     Report Naming Conventions

As a guideline a Row Set name can usually be applied to the report that it is used because the Row
Set is the main basis of a report. You can then add additional information or name of other
components used.
It is also recommended that you have a standard layout for reports so that with a basic name such
as ‘VSGB Corporate P&L’ the users can assume that it has a standard layout such as a PTD &
YTD Column Set, then you only have to add additional descriptions when other layouts are used.
Some examples of different versions of the same basic report are provided below.

REPORT NAME                         REPORT DESCRIPTION

VSGB Corporate P&L                  Global standard corporate P&L in the GB chart of accounts with
                                    PTD & YTD columns
VSGB Corporate P&L Budget           Global standard corporate P&L in the GB chart of accounts with
                                    actual, budget and variance for PTD & YTD
VSGB Corporate P&L Department       Global standard corporate P&L in the GB chart of accounts by
Summary                             department parents.
VSGB Corporate P&L PTD Monthly      Global standard corporate P&L in the GB chart of accounts, rolling
                                    month by period

3.6     Naming Convention Conclusions

If these naming conventions are observed then a great deal of clarity will be added to the FSG
report components. This is especially useful once the users start to create their own ad-hoc reports
by selecting specific components on submission rather than just selecting pre-defined reports.
Generally in the component descriptions you can copy the name, but where needed you can add
other information such as an indication of why the component is not suitable for other countries. For
example ‘Suitable only for French Tax books as the Set of Books is hard coded’
All of the above are just suggestions, so you should select those that work and are applicable to
your business and system structure.
For example if you don’t have Management & Tax books in Oracle then you don’t need to
distinguish between TX & MT in the naming conventions. If you only have one global chart of
accounts then you can also simplify the conventions, as you don’t need the global prefixes.

4 Report Coding ‘Best Practices’
The following sub sections provide guidelines that should be used to code consistent FSG report
components. When coding more complicated FSG reports such a consolidation control, cross
currency or inter-company then it may be necessary to work against some of these
recommendations to achieve a solution. Where possible, examples of this type of scenario have
been given.

This should not be looked upon as a user guide or training manual, but is aimed at people already
trained on report writing. When used in conjunction with a well defined report specification from the
user, this section should provide the prompts and points of advice that enable the writer to develop
a report quickly that is still in line with the global report writing conventions. It should also provide
enough information for the experienced report writer to avoid having to refer back to ‘FSG Report
Writing Basics’ presentation.

As a high level reminder a list of tips is provided below. These should be considered whenever an
FSG component is added to the system of modified.
             Do not start until the user provides a completed report specification.
             If no legacy report or example exists then plan you report on paper or excel first.
             Check if there is an existing component that can be used as a basis and copied.
             Use the appropriate tool for the job. ADI / Oracle screens?
             Always think of the least complicated and most transparent method of writing a report,
              even if it takes a little longer. It will save maintenance in the long run.
             Document any assumptions made or changes to specification during the writing of a
             Code in controls to a report so that any discrepancies are quickly visible ( Row Sets &
              Column Sets ).
4.1     Best Practice: Row Sets

The coding of Row Sets has the potential to become very complicated as there is no limit on how
long they can be. When coding the account assignments it is always beneficial to leverage the set-
up of the application, its functionality and different tools available.
For example, don’t code many child rows separately when you can set up a parent account and just
expand the display to show all the children.

SET-UP              ACTION
Name            &   Follow the naming conventions outlined in the previous section.
Line & Line Item    The line number should always be coded in increments of 10,20,30 etc. This is so that
                    if new lines need to be added later then it can be done without having to renumber the
                    entire report. The ‘Line Item’ is the row name that is actually seen on the report. It is
                    important that this is named with the advice of the users especially with tax and
                    statutory reports as precise naming of the report lines is often a statutory requirement.
                    It is also recommended that you split major sections of the report using line numbers.
                    For example on a P&L the income can be lines 10,20,30
                    Then the expenses 510,520,530 and so on, and then the project calculations and
                    margin 1010,1020,1030.
                    This will give plenty of room to add more detail to each section later.
                    Generally it is quicker to code all the lines in the applications rather than in ADI.
Format Options      Generally leave these options until last. Once the report is running and you have a
                    print out, then go through with the users and decide where to place all the format
                    options. The only points to note is that the underline character is user defined so you
                    can use ‘-----‘ for subtotals and ‘=====’ for grand totals. Also, page breaks are not
                    necessary unless specifically request by the user in a certain place as the application
                    will insert a page break to fit the page size.

                  Once all the rows have been creating by using the up and down arrows to move
                  between rows. Generally it is quicker to code the format options in the applications
                  rather than in ADI.
Advanced          The row name is used to reference a given row when entering the calculations. It is not
Options           seen in the outputted report. However to avoid confusion it is best to copy the name
                  directly from ‘line item’, though you may need to abbreviate this as only a limited
                  number of characters are allowed.
                   The percent of row field only needs to be filled in if specifically required for a
                  percentage column in the report, if required then the sequence number ( line number )
                  of the ‘total’ row should be entered in every other row that makes up that total.
                  Leave the over-ride row calculations box unticked unless specifically required.
                  Generally it is quicker to code the format options in the applications rather than in ADI.
Balance Control   For most FSG reports where the accounts are described on the row, and the columns
                  determine the period and amount type, all of these options can be left blank in the Row
Display Options   As with the balance control options these are generally contained at the Column Set
                  level, and should be left blank unless specifically required. The only exceptions to this
                  are the two tick boxes for ‘Display Row’ and ‘Display Zero’ . The display row box
                  should always be selected unless it is a calculation that you specifically wanted hidden.
                  The display zero box should generally be unticked to limit report size, but can be ticked
                  to provide a consistent size to the report and act as a control so that the users know
                  that the report is looking at those accounts even if they have no balance.
Account           When entering the account assignments it is usual to leave most fields blank ( Which
Assignments       will then pick up all ranges ) and enter assignments only for those specific CoA
                  segments that are of interest.
                  When entering account assignments, try wherever possible to use the parent account
                  structure rather than ranges of child accounts, it will reduce both report complexity and
                  maintenance requirements.
                  The display options are entirely user defined, but remember to use a Row Order to
                  manage descriptions if you are going to expand the rows. Also, the use of ‘B’ for both
                  does not look good on the finished report, more control of the formatting is available if
                  you expand a row and then add a second row with the total. This will enable you to add
                  formatting such as ‘-------‘ before the line.
                  The summary tick box should generally be left blank. It is possible to run FSG reports
                  directly on summary accounts, which is useful if you are having database performance
                  issues. However these can cause erroneous results if you do not carefully match you
                  account assignments to specific summary accounts on the Row Sets and Content Sets.
                  Unless specifically required to report across multiple books, the set of books fields
                  should be left blank ( SoB. is determined by you responsibility ) so that the Row Set can
                  be used by any other set of books that shares the same chart of accounts.
                  The activity is generally set to ‘Net’ unless you specifically want either Dr or Cr.
                  It can be beneficial to define the initial structure of the rows in the core applications, and
                  then open the Row Set in ADI to define all the account assignments, particularly if you
                  have been given all the ranges in a spreadsheet as you can map then to the ADI layout
                  and load them automatically.
                  Even if you have defined all the account ranges from within the core applications it is
                  worthwhile opening the Row Set definition in ADI to review and sign off with the users
                  as you can see all the account ranges in a spreadsheet format.
Calculations      These are entirely user defined, and generally unique to a specific Row Set. Operators
                  available include the following (+, -, *, /, %, Average, Enter, Median, StdDev, Abs ).
                  Using these operators it is possible to build quite complex multi-line calculations, and
                  example of which can be found in the section below ‘Continental Style Balance Sheet’.
                  It is also worth while working through complex calculations on a spreadsheet first ( the
                  operators above are also available in excel ) to confirm the results you are expecting as
                  it is quicker and easier to validate a formula in Excel than via FSG. Finally as a general
                  rule the first value line should be ‘Enter’ otherwise it can cause erroneous results with
                  complex calculations
                  Calculations are best done within the core applications rather than ADI.

4.2      Best Practice: Column Sets

Column Sets are a very flexible report component. They provide the structure and format of a report
( PTD, YTD, Actual, Budget ) whilst the Row Set provides the account assignments. However there
are many features in a Column Set that enable it to interact with the Row Set it is run with to create
a completely different report.

Set-up Option           Action
Name & Description      Enter the Column Set name in accordance naming conventions section above.
Column Attributes       The position in the columns relates to number of characters from the left of the
                        page that the column should start. If you are going to run the reports from the
                        core applications ( instead of or as well as ADI ) then it is important to
                        synchronise the number of characters in that the first column starts on as this
                        determines the width available for the row descriptions used in the Row Orders..
                        The best method is to have two or three standard widths, so that these can be
                        used with a range of standard Row Orders with the same width settings. For
                        example 40, 80 & 130 Characters and then have Row Orders defined to match
                        each of this.
                        This will prevent any unforeseen formatting where the system tries to squeeze
                        descriptions, or use up available space in an uncontrolled manner.
                        The sequence numbers should follow the same rules as the Row Sets, and the
                        format mask and factor used are dependent upon the reporting conventions of
                        the company and the width available to each column.
                        For example reporting in units with a format mask of ‘999,999,999,999.99’
                        would not be possible with a column width of 15 characters, so it would be
                        necessary to report in thousands, for example ‘,999,999,999’ which would fit
                        within the space available.
                        It is advised that you agree a corporate standard and stick with it as the default.
Balance Control         An amount type such as PTD or YTD always needs to be defined in a Column
                        Set, but unless specifically required all other options can be left blank.
Advanced Options        The column name is used as a reference in calculations, but it is advisable to
                        label the same as the column heading to avoid confusion ( To a limit of 30
                        characters ). Column description is as column name.
Display Options         As per Row Sets
                        Note: Level of detail should be left blank unless it has been decided to use this
                        functionality for the whole report.
Calculations            As per Row Sets
                        If you are using calculations in both the row and Column Set then refer to the
                        section called ‘FSG Row/Column Overrides’ for more information on how they
                        will interact together.
Account Assignments     As per Row Sets, though refer to the advice below with regards to using
                        account assignments in both rows and columns.
Column Set Builder      You should use this feature. It is the best way to visualise how the columns will
                        look on a report and the only way to ensure the correct headings.
                        Enter your own labels where necessary and make use of the relative ‘&’
                        headings that automatically pick up the accounting period as &POI or the
                        budget as &BUDGET.
                        This is also used to build or manually define the column headings that will
                        appear on the report., use the create default button initially and then update
                        manually with any changes you want.

4.3      Best Practice: Content Sets

Content Sets will be most frequently used component in management reporting to add a new
dimension or level of detail to an expense or revenue report. For statutory reporting the use of
Content Sets is likely to be limited ss most statutory reports only look at a single country, and are
generally not interested in a cost centre breakdown.
Some examples of where Content Sets may be useful are as follows:
A P&L report gives details of expenses by account and the figures for travel and accommodation
are very high. To highlight the source of these extra expenses a Content Set could be applied to
the report that breaks out each of the cost centre/activity/project segment values on the chart of
accounts, so that each project can either have its own report, or each expense is listed by the cost
centre/activity/project within a single report page ( spreadsheet tab in ADI ).

Set-up Option            Action
Name, Desc. & Type       Name and describe the Content Set as per the naming above, and select the
                         type of sequential ( refer to the section on ‘Maximising database performance’
                         for more details on this ).
Display Options.         When selecting the display options remember that a Content Set will ALWAYS
                         over-ride the settings in a Row Set.
Account Assignments      As per above, the account assignments in a Content Set will always over-ride
                         those in either a row or Column Set. If possible, when coding a Content Set try
                         and make the account ranges as generic as possible within the constraints of
                         the specific requirement.     Therefore only reference segments that you
                         specifically need to, and make the ranges as large as possible. This is
                         especially true if you use security rules to limit access to things such as
                         department codes as the security rule with take care of limiting the output to the
                         departments specific to each company.
Account     Assignment   Display :The default is ‘N’ which means no override
(Display Settings)
                                 CT : Total by Column.
                                 PE : Expand by page ( or spreadsheet TAB in ADI).
                                 PT : Total by page ( or spreadsheet TAB in ADI).
                                 RB : Both expand and total by row.
                                 RE : Expand by row.
                                 RT : Total by row.
                         The most commonly used options are RE, PE & PT.
                         RE is used to expand rows in more detail by specific segments .RB & RT are
                         not so common Row Sets tend to be written in summary anyway.
                         When a report is run in ADI PE & PT will expand it by spreadsheet tabs so for
                         example you could define a content set that had two lines. The first was a PT
                         on the parent for all Cost Centres, to give a summary for the company on the
                         first tab, then the second line could be a PE on the same parent cost centre so
                         that all subsequent tabs where a report by cost centre.
Summary Settings         Generally set to ‘NO’ unless you need to summarise the ranges referenced in
                         the Content Set. This setting can be quite difficult to predict because it interacts
                         with the settings in the Row Set, the use of parent structure and the display
                         settings in the row and Content Set.

4.4      Best Practice: Row Orders

You should aim to define a suite of standard Row Orders (and Content Sets ) that are consistent
across all charts of accounts, where the CoA structure is similar. You can then select a Row Order
from this standard suite can to be applied to any reports created. This would greatly reduce
ongoing maintenance and support as you should only occasionally have to create new Row Orders
or Content Sets if it is a requirement particular to one set of books or office.
The use of a Row Order should be considered after the report has been created, the display
options for the Row Set and Column Set established and the Column Set has determined the width
available for descriptions (40, 80 130). For most requirements a standard generic Row Order
should already be in existence on the system and can either be used, or copied and modified.

Set-up Option            Action
Name,Desc & Type         Name and describe the Row Order as per the naming conventions above.
Rank by Column           This refers to the report column that you want the descriptions to be sorted and
                         formatted by. This option is only really relevant if you wish to order the rows by
                         the balances on a particular column. If left blank then the report will order on
                         the first column, which is suitable in most situations.

Sequence & Segment        Enter a sequence for all segments of the chart of accounts, even if they are not
                          wanted. This means that you can enter a width of ‘0’ for those unwanted
                          segments and avoid erroneous results if the total Row Order width does not
                          match the Column Set width.
Order By                  This is entirely user defined. It affects the display order of each of the expanded
                          segments in turn.
Display                   Again entirely user defined. Select Value, Description or Value and Description.
                          Just remember to make enough room for the descriptions in the widths below.
Width                     Width in number of characters is dependant upon the segments selected for
                          display, the width of each segment and which ones have descriptions. For a
                          value only the width should the same number of characters as that segment +1,
                          so for a segment of 2 characters use a minimum width of 3. For descriptions
                          allow around 15-25 character.
                          If you do not want to see a particular segment then select a width of ‘0’.
                          The total of all segments should equal one of the standard widths of 40, 80 or
                          130 that you plan to match to the Column Sets so adjust the segment widths
                          accordingly to match one of these totals.

4.5      Best Practice: Reports

There is not a great deal of definition required to create a new report as they are basically the
linking of existing components. Some general rules are that if a particular combination of
components is being run several times as week as an ad-hoc report then it is worth defining as a
new report so that it is easier for the users to run consistently.

Set-up Option         Action
Name & Description    Follow the naming conventions outlined in the previous section, but also add in a
                      description and a report title. The latter is very important as this is the title that
                      will appear on the output report and may in some cases be a statutory
                      requirement to provide the report with the correct name
Report Components     Every Report must have a Row Set and Column Set, but other components are
                      optional and you have the flexibility to all be added when a report is submitted so
                      you do not necessarily need to hard code Content Sets and Row Orders with
                      each report.
                      An example might be you corporate P&L report. You could define two versions.
                            P&L Summary : This is just the Column Set and Row Set and does not show
                             account level detail.
                            P&L Detail : This is the same as above but with a Content Set using ‘RE’ and
                             a Row Order to expand the account and cost centre information in detail on
                             each row.
                      The users would then have the option of running the summary version and
                      selecting other Content Sets and Row Orders if they wanted different layouts,
                      such as the account expanded by row but a separate report for each cost centre.
Other Components      Unless this is a scheduled report to be run frequently, such as each night or
                      week, you can generally leave these options blank as they are all available when
                      the report is run. For example :
                      Segment Override : can be selected when the report is run to review a specific
                      cost centre, or to run for all cost centres at once use a Content Set. You do not
                      need to define separate reports for each cost c
                      Currency : This is usually left blank to pick up the functional currency of the set
                      of books, but you can also enter a specific currency , but only if translated
                      balances exist for that currency.
                      Rounding Options : It is sometimes statutory requirement to perform this in a
                      certain order. ‘Round then Calculate’ or ‘Calculate then Round’, otherwise leave
                      as default.
                      Level of Detail : This works with the level of detail on the row set definition to
                      have different reports from the same definition. This can be left blank and the
                      default will be ‘Financial Analyst’
                      Output Option : The default is text and this will work with publishing the reports

                            as text direct from the apps and with publishing to spreadsheets via ADI. The
                            other options are to change the format of reports published from the apps, but are
                            redundant if ADI is being used.

Note: When selecting a Row Order and Content Set for use in a report you should always match
the segments select each or them. For example:
‘VSIT 1RE2RE3RE’ can be used with Row Order ‘VSIT 1V2V3VD(80)’ because the segments
match, but not with ‘VSIT 1V2VD(80)’ because the Content Set is expanding segment 3, but the
Row Set is not telling the report how to display it.
Also you can use other Row Sets in different orders as long as they refer to the same three
segments, therefore it is ok to use ‘VSIT 1V3V3VD(80)’ & ‘VSIT 3V2V1VD(80)’ with that same
Content Set mentioned above.

5 FSG Row/Column Overrides
This section was originally from an Oracle metalink note
This section contains information on the effects of specifying different values for the same field at
Row Set, Column Set and/or Content Set level in a FSG report. These points are useful for all types
of FSG report, especially when coding complex intersecting FSG reports, or when looking for a
problem in an existing report. Below is a listing of fields that are common to both Row Set and
Column Set. If you assign DIFFERENT values for these fields, the following will result:

ROW overrides COLUMN for the following fields:
              Amount Type
              Period Offset
              Change Sign
              Change Sign on Variance
              Display Zero
              Factor
              Format (You can't use symbols in your row format such as $)
              Level of Detail
              Runtime Override (Ex. specifying different budgets for your row and column)

COLUMN overrides ROW for the following fields :
              Activity
              Override Calculation

Override Exceptions
              If you assign accounting flexfields in both your row and column, FSG takes the
             intersecting segment values to determine the balance to report.
             Assign the same summary option in your row and column. You will not get a
             meaningful result, otherwise
             Assign the same currency to your row or column or leave one of the fields blank.
             Otherwise, you'll get a zero amount.
             If you specify the same calculation precedence at both your row and column level, your
             column calculation takes precedence.
             There are also fields in the Row Set and Column Set that are common to the Content
             Set. Content Set will ALWAYS override the values you enter in your Row and Column

6 Coding in Reconciliation Controls
When coding a complicated report it is very easy to loose track of what accounts have been
included and which have been missed. This is usually checked manually with a list of the chart of
account segment values printed out on paper and then ticked off against the Row Set definition.
This is obvious a method that is time consuming and open to mistakes, therefore it is always
prudent to code in a couple of control totals through out the report.

This is done buy using specific ranges to test the logic and consistency of your parent structure and
the account assignments in the Row Set.

For example if you have the following parent account structure, you may have a report that
specifies detail accounts.
You could use a hidden calculation row on the parent or grand parent level to validate that the
balance of all your detail payroll rows in the report equals the parent account balance for ‘00100’

00100 – Payroll Costs   01000 – Salaries   10000 Basic Pay

00100 – Payroll Costs   01000 – Salaries   10005 Overtime
00100 – Payroll Costs   01000 – Salaries   10010 Other Overtime
00100 – Payroll Costs   01000 – Salaries   10011 Standby Payments
00100 – Payroll Costs   01000 – Salaries   10012 Call Out Payments
00100 – Payroll Costs   01000 – Salaries   10013 Shift Premium/Unsoc Hours Premium
00100 – Payroll Costs   01000 – Salaries   10015 Employers Nat Ins Contribs
00100 – Payroll Costs   01000 – Salaries   10016 Class NIC1A
00100 – Payroll Costs   01002 – Pensions   10020 Employers Pension Contribs
00100 – Payroll Costs   01002 – Pensions   10021 Pension Provision
00100 – Payroll Costs   01002 – Pensions   10022 Staff Efficiencies
00100 – Payroll Costs   01002 – Pensions   10023 Employers Superannuation GUPP
00100 – Payroll Costs   01002 – Pensions   10024 Employers Superannuation SCCPS
00100 – Payroll Costs   01002 – Pensions   10025 LA Pension Scheme Contribs
00100 – Payroll Costs   01002 – Pensions   10026 FRS 17 Pensions Actuarial Gain / Loss (STRGL)

By inserting two extra rows, firstly a calculated total of the detailed rows, and secondly a control
total that simple asks for the balance of accounts 10000-10999, or the balance of 00100 the coding
of the can be checked. If there is a difference then is it clear that either an account is being
included twice, or something is being missed out.
You can untick the display zero flag on this calculation row, and add a description that says ‘XXX
ERROR XXX’ so that on the report it only displays when there is a problem, and it is clear to
anyone reviewing the report that something needs investigating.
If these checks are carried through to production then they serve as a long term control, and will
even pick up problems if they only become apparent later. This is particularly useful for capturing
unexpected changes to the chart of accounts hierarchy that may find their way into production.

Ideally you should create a dedicated FSG report to validate the chart of accounts that could be run
as part of the month end process. The report could take the following format ( extended to all
values across the trial balance. Using this layout the balance at each level ( Child, Parent, Grand
Parent ) should match, and if it doesn’t then it is obvious that there is a problem.

DESCRIPTION             ACCOUNT            BALANCE
Grand Parent            00100              564,778,119.00
Parent                  01000-01009        564,778,119.00
Child                   10000-19999        564,778,119.00

Report Coding Tools
FSG reports can either be coded directly into the core Oracle applications, or using the ADI tool.
Whichever tool is used has no effect on the finished report, and reports coded in one method are
visible and 100% usable in the other.
Knowing when to use Oracle application screens and when to use the ADI tool is something that
comes from experience of writing FSG reports and from personal preference as to which tool is
most user friendly. However the points below provide assistance in selecting which tool to use.

6.1      ADI is best for:

            Visualising how the report will eventually look.
            Formatting of backgrounds/fonts/ colours etc using the ADI templates.
            Mass maintenance of account assignments, ( See example below )
            Final changes and sign-off with user – ADI is much more visual and so users will find it
             easier to relate to and understand issues.
            One of the most beneficial features of using ADI to write a report is that the account
             assignments used in a Row Set can be displayed in an Excel format.
            When using ADI to edit the Row Set account assignments the usual Excel edit features
             are available. This means that it is possible to perform a find replace, copy, paste and
             cut, on the entire reports account assignments. It is even possible to create an
             automated macro to copy account ranges from other non-ADI spreadsheet.

6.2      Oracle Core Application Screens in GL are best for:

            Initial coding of components and the attributes.
            Navigation in application is quicker in ADI, especially once familiar with the screens
            Maintenance of names, titles and formatting such as line and page breaks in many
             components at once as you can use the up and down arrows to move quickly between
            Copying components and reports

7 Using Control Values for Currency & Budgets
Control Values are used to add Budget, Encumbrance and Currency information to FSG reports.
They are referenced on the columns and/or rows as number and then in the report this numbers
are linked to budgets and currency.

The advantage of this method is that you can hard code the control values in the detailed
components ( Rows & Columns ) and then each year when the budget changes just update the
control values on the report once and link that value to a new budget.

Access the this screen by pressing the control values button on the report definition screen, though
it is only available when control values exist in one or more of the report components.
You can enter multiple control values if you want to use more than one currency or budget.

When using control values to define currency, you have to select a currency type of Entered or
Translated. This will only apply to the rows or columns with the matching control value. It is
different to selecting the currency on the report definition, which applies to the whole report and
only works with translated balances.

Some examples of how you would used control values on Column Sets are as follows.

            For actual and budget variance reports you would add a control value to the budget
            If you have several iterations of budget during the year, such as a Q1, Q2 & Q3 budget
             you would use different control values on each column to reference the different
            If you have to report in different translated currencies for local, regional and global
             reporting. For example a UK company running primary books in GBP may have to
             report to Europe region in EUR and head office in USD. You could use two control
                            nd       rd
             values on the 2 and 3 columns for the translated EUR and USD balances.

An example of how you would used control values on Row Sets is for an FSG report that reconciles
bank accounts, or intercompany control accounts you could use several rows, either with a different
control value for the main entered currencies to review the foreign currency entries passing through
the account.

8 International Reporting With FSG
The purpose of using separate charts of accounts and separate sets of books is to provide the
flexibility to meet the various and incompatible requirements of each country. The areas where
some of these requirements effect FSG reporting are discussed below.
8.1      Language

In some countries such as Belgium and Luxembourg the authorities are quite open to account
descriptions being in English as long as the statutory list of account values has been used.
However other countries such as Germany, Spain, Italy & France require that the descriptions
appearing in each report are in the native language of the country.

This requirement can be met in a number of ways.
            The local chart of account child ranges can be in a local language, and then the FSG
             report coded so that the rows are expanded to show these descriptions.
            If the children are still in English but the report must be in local language then a parent
             structure in local language can be created, and then the FSG coded to pick up the
             parent and not the child descriptions.
            If the parent structure is already in English then the FSG report can be coded with the
             desired description on each line. This is time consuming if done manually, but it is
             possible to load all the descriptions automatically using an excel template mapped to
             the report builder in ADI.
8.2      Rigid Code Values and Descriptions

This is a requirement in many European countries, where they have to use a national ‘Plan
Comptable’. This is normally dealt with by using a separate statutory set of books, but if this is not
viable for your project then it may be possible to deal with this using the report techniques
mentioned above, but this is stretching the requirement quite a lot and may not be accepted by the
local controller. Some sample ‘Plan Comptable’ chart of accounts are available on the website for France, Belgium and Spain.
8.3      Horizontal/Vertical Report Formats.

In some countries the users may present a legacy report that is in a horizontal (like a double page
of a book) format , instead of the typical vertical format. If this situation arises then the right hand
side of the report should be moved down as a block so that it creates a vertical style report. This
solution will meet the legal requirements as the data and descriptions of the report have not
changed, even the format of each report block remains unchanged, the difficulty my be in
persuading the report users, who have always had a reports such as a balance sheet in a
horizontal format.
8.4      Continental Style Balance Sheet and P&L

Another common requirement in Europe is for certain accounts such as clearing accounts, retained
earnings and provisions to appear as an asset or a liability( or Revenue/Expense) depending if the
balance is a credit or debit.
Unfortunately there is not an easy automatic way to achieve this, but it is possible by working with
the Row Set calculations on specific accounts.
An example of the formula required is provided below of a statutory Spanish P&L. In this example
WIP has to be shown as an income or expense depending if it is a Dr or Cr balance.

Lines 20 & 25 show how this appears as revenue.
Notice how line 20 is not displayed at all, but line 25 is always displayed as it will be zero or a debit.

Lines 1640 & 1645 are the same range of accounts showing on the expenses side of the P&L

The formula is as follows to only show a credit value (to only show a debit the replace the ‘-‘ with ‘+’
on line 20 )

Line     Operator         Row/Value
10       ABS              Row ‘x’
20       -                Row ‘x’
30       /                2

You have to take care that any calculations elsewhere in the report are looking at the ABS lines and
not the hidden lines.
Using the example above for WIP movements. This can be an increase or a decrease but not both.
Therefore if row 20 & row 1640 both have a WIP movement of 100 Dr they cannot both be included
in the total revenue and total expenses. Instead you should use rows 25 and 1645 so that only line
1645 shows 100 and line 25 shows zero.
The renaming is also important. For every active formula line the row name should be prefixed with
'ABS: '
This will allow quick checking of the report configuration later, and expedite updates of formula
such as in the example below.

Also the line item for every hidden row should be updated with the prefix of 'HIDE ' so that these
can be identified on the report and also to allow quick review of the configuration.

If you would like help in implementing this solution then can provide a fixed
price service to update your reports.

9 FSG Transfer to Production
9.1      Suggested Procedure

The procedure for a new FSG report or component to reach the production database should be
consistent with your policy for any other system change. First of all the change must be coded in
the development environment. Once the report has been completed in a DEV environment it can
then be migrated to a UAT environment with recent and realistic GL data where the user will be
expected to test the report and sign of its format and content. Once sign off is obtained the report
can then be migrated to production. This process is explained in the diagram below.

    FSG Request and
 Specification document is
 completed and signed off

                                                FSG Report is coded          The specification and
                                                into the development        coding of the report is
                                                     environment.           reviewed in light of the

   Report is migrated                          Report owner reviews
 from development to         Approved           the draft report and       Rejected
   UAT environment                               approves/rejects


                                               Report owner reviews
                                                the draft report and       Rejected

Migration to production must be
accompanied by :
-User Sign - off
- Specific Maintenance Procedures
- Notification to Oracle support
maintenance team

                                               Report is migrated to
                                            Production and available for

9.2      Oracle Functionality

The transfer of reports and components between environments is undertaken using the standard
Oracle program ‘FSG Transfer’. The program is run from the target environment ( Production ) and
pulls the report components from the previous environment (UAT ) according to the parameters
selected below.

9.2.1 Define Database Links

NAV : GL Super User > Setup > System > Database Links
Before the program can be run, check the setup in the target environment to make sure that the
database links exist to the environment you want to pull the reports from.
Using the examples above, in UAT you would want a link to DEV and in Production you would want
a link to UAT.

9.2.2 Transfer Reports

NAV : GL Super User > Reports > Request > Standard
Note : If the request doesn’t exist then contact you system administrator as it may have been removed.

This can only be run from the ‘target’ environment.
Once the request is selected, complete the following parameters.

Explanation of the parameters as follows.

Parameter                    Explanation
Component Type               Row Set, Column Set, Row Order, Content Set and Display Set can all
                             be transferred individually. If the component type selected is either a
                             Report or Report Set then this will transfer not only the report definition
                             but also ALL of the attached components that do not already exist in the
                             target environment.
                             Also note that you should be careful using the ‘All’ component as this
                             seems to mean everything and ignores anything you put in the
                             component name field. ( Occurs in R11.5.9 )
Component Name               The full name of the component being transferred can be typed or you
                             can use a wildcard ‘%’ with part names to transfer multiple components.
                             This because the field accepts wildcards and part names, so unless the
                             complete component name is entered there is always the possibility that
                             other components may be mistakenly picked up and transferred as well.
                             Some additional comments :
                              This field does not support either the pick list or find functions so the
                                 correct name needs to be typed in manually.
                              Next enter just a ‘%’ as it will try to transfer all components, so be as
                                 specific as possible when using ‘%’
                              Some versions of R11i have an intermittent error with the transfer of
                                 components with long names. The error usually occurs with names
                                 longer that 24 character, but has been not to occur with short

                            names where other characters such as an open bracket ‘(‘ without a
                            close ‘)’ have been used in the component name.
Source DB CoA           This is always the same as the target CoA and should be written in by
                        hand after the target DB CoA’ has been selected from the pick list.
Target DB CoA           This field contains a pick list.
Source Database         This is always the database that the report is being copied from.

9.3      FSG Transfer Issues

When transferring whole reports at once, the program will only import those components that don’t
already exist, therefore if the report being transferred contains a standard Column Set such as YTD
Actual, then the program will detect that this already exists in Prod and not import the Column Set,
the report will still work as it will now references the existing Column Set in the production
environment. This is a very useful safety measure as it prevents components getting accidentally
updated when running a transfer.

The danger of this feature is that if you are transferring a report that contains a custom Row &
Column Set and these already exist in production, then the components will not have been updated
when the transfer is run. This can be overcome by making sure that the components being
transferred have their namesakes deleted from production before the transfer program is run, then
always carefully review the FSG transfer log file to ensure that the transfer occurred as expected.

If you configuration up uses multiple sets of books and a different chart of accounts for each set of
set of books, then there are some differences in how visible certain components are across
different books. The basic rules are explained below

Any component that references and chart of accounts, is only visible to the sets of books using that
chart of accounts. This includes Row Sets, Row Orders and Content Sets. Where two books use
the same chart of accounts then these components are shared.
Column Sets are generally visible across all books and chart of accounts, until they have an
account assignment added to one of the columns, at which point they are stamped with a specific
If this is in an existing generic Column Set it will then become unusable for all other sets of books
and cause reports referencing that Column Set to end in error.

To work with these ‘features’ a number of basic procedures need to be followed. For example,
each component should be coded in the same set of books in the development environment that it
is intended for in the production environment, and naming conventions should be tightly followed.

10 FSG Support & Maintenance Procedures
10.1 Maintenance & Support Structure

This is usually the last stage of implementing the FSG reporting strategy to be considered. Rather
like training, it can be difficult to envisage a detailed report maintenance plan before the reports,
and staff are in place.
The maintenance plan is usually an actual set of procedures developed by the project team and
delivered to each country or company. These procedures are fairly standardised and in the case of
FSG reports, relate mainly to the Row Set objects. Topics include :
          Dealing with COA changes, additions, removals and changes in hierarchy.
          Year on year budget changes, or even first, second and third session budgets within a
          Procedures for modifying existing reports and adding new reports or components.
          Changes to the statistical balances like headcount or overheads that are used with

Maintenance procedures can be broken down into either routine or ad hoc changes depending
upon their nature.
A development environment should always be used to make changes in, and then the path to
Production discussed in the section above should be followed to ensure that the quality of the
reports is maintained.
10.2 Maintaining Budgets

Budgets are assigned to an FSG report by using a control value in the particular rows and columns
that use budgets and then assigning in each report the control value to a particular budget.
This has the advantage of allowing the reports to be flexible enough to be changed for each
budgeting period, by updating the link between the single control value and the budget at report
rather than row or column level.

The procedure of assigning a budget to a report is very quick, and all reports could be updated in a
few hours. Assuming three budgets each year and a total of 30 reports using budgets, then this is
no more than six hours of maintenance per year.
The above estimates are based on the following assumptions. Any deviation from these may mean
that increase maintenance is required.

These assumptions are as follows.
    Budgeting and budget uploads are done on a corporate and not cost centre level.
       Therefore a single budget Org is used and a single budget uploaded and assigned for each
    All report writers use a consistent control value for assigning this single budget.
    Current budgets are NOT compared to previous ones.
    A single report does not have more than one budget assigned.

The quickest method of updating the budget assignments to reports is as follows.

NAV : GL Super User > Reports > Define > Reports

        Place the cursor in the report name field and put in query mode.
        If you have used consistent naming conventions you should be able to find all budget
         reports with the ‘%Bud%’ entered in the report name or description.
        This will return all the matching report, then the up and down arrow keys can be used to
         move between reports.
        From the first report, press the ‘Control Values’ button, and this will open a second smaller
         screen where the report is linked to a budget. If the report does not need a budget then
         this screen will be greyed out.

        Return to the report name field in the first screen. (You should keep both screens open at
         once so that you can scroll down through the reports and still view the control values
        When you reach a report that does not have the control values greyed out, move to the
         Budget field of this screen, to the right of the control value. Use the list of values to select
         the correct budget.
        Use ‘Cntrl+C’ & ‘Cntrl+V’ to copy the budget name into all subsequent budget reports.
        Return to the report name field and then scroll to the next report requiring a budget.
        Move to the Control Value form and ‘Cntrl+V’ the name of the budget into the appropriate

10.3 Statistical Balances

FSG reports may be set up to use statistical balance, for example the use of a statistical balance
for headcount or number of hours to calculated the efficiency of a division.
Although the reports themselves will not require maintenance the statistical balances that they rely
upon will require maintenance at each year end, or possibly each month if the statistical data
changes monthly. The biggest risk with the use of statistical measures in FSG reports is that the
maintenance is forgotten and does not become apparent until the report is run for the first time in
the new accounting year.

In most examples it will be visible because if the maintenance is forgotten then the report will try
and produce calculations on NULL values. However care must be taken when using statistical
reports as it may not always be so clear if the maintenance has been carried out or not.

10.4 Chart of Account Changes

Any changes to the production chart of accounts, including the addition of new values, change of
meaning or deletion of existing values, will be prompted at the request of the business. The
process in place for making any changes should ensure that all the Oracle users will be made
aware of the change before they are migrated to the production environment. However, it is
possible that changes could be made without the knowledge of all report owners. To protect
against this possibility there are a number of standard reports ( FSG & CoA listing ) that can
provide the report owners with the information needed to track down suspected changes.

The table below provides a quick guide to the types of CoA changes that may occur.

Type Of COA Change          Effect on FSG reports                        Action required
Segment Value               The new description will be displayed on     None - Unless row name has
Description is altered      all reports using Row Orders to manage       been hard-coded by the
                            row descriptions.                            report writer
                            Hard-Coded row names will not change
Change of Segment           May either become relevant to a given        Understand implications of
value meaning               FSG, or may even need removing from          change and update report
                            an existing report.                          accordingly
New segment value is        May need adding to or excluding from         Understand implications of
added                       report.                                      change and update report
Child is added to Parent    Will automatically be included in reports    Consider effect on any fixed
value                       referencing the parent. May cause a          templates used with the
                            new row to be added to reports using         report.
                            expanded parents
Segment Value is            Will no longer appear on report run for      None
Deleted                     removal date onwards.

10.5 Other Considerations

When deciding upon the appropriate course of action required to make allowances for the chart of
accounts change, the following factors must also be considered
            Have account ranges been used, or specific accounts referenced?
            Have parent accounts been used. ?
            If the new account is a natural account then consider the dependent local account.

Fortunately there is a standard report that can be used to help with the maintenance of FSG
reports. The ‘FSG Where Used’ report can be run whenever a user suspects that a report may
need updating.
Based upon the segment values entered when this report is run the output will provide a list of all
the report components and the position in each component where the segment value is used.
The report user can then check if the segment value is being picked up at all, and if it is in the
correct place. An description of the ‘FSG - Were Used ‘report and others is provided in the next
10.6 Using FSG ‘Standard’ Reports

Oracle General Ledger comes seeded with a number of standard reports that are designed to
provide information about the FSG reports set up by users on the system. These reports can be
run from the menu path displayed below, and all are run with very simple parameters.

10.6.1 Summary FSG Component Listings

These provide a list of each individual entry of a given report component and provide the name,
description and an additional column of similarly high level information. The Report summary listing
is a list of every FSG report on the system and provides the name of each of the attached
Report Summary Listing            Review the report components and report options associated with each
                                  report defined in your current set of books.
Column Set Summary Listing        Review the names and descriptions of all Column Sets defined for your
                                  current set of books. General Ledger displays the chart of accounts
                                  structure associated with each Column Set.
Row Set Summary Listing           Review the names and descriptions of all Row Sets defined in your
                                  current set of books. General Ledger displays the chart of accounts
                                  structure associated with each Row Set.
Content Set Summary Listing       Review the names, descriptions, and processing types of all the
                                  Content Sets defined for your current set of books.
Report Set Summary Listing        Review the names and descriptions of the report sets you have defined.

10.6.2 Detailed FSG ‘Standard’ Reports

The detailed reports provide information on individual report components, for each of the detailed
reports a specific component must be selected.
Report Detail Listing             Review detailed information about a specific report, or about all reports
                                  defined in your current set of books. For each report, this listing prints
                                  the report components, report options and report details.
Column Set Detail Listing         Review detailed information about a specific Column Set, or about all
                                  Column Sets defined in your current set of books. General Ledger first
                                  prints your Column Set heading, then the details of each column
                                  definition. Display options for each column appear in a box. Finally,
                                  General Ledger prints your account assignments, and your calculation
                                  and exception definitions, if any.
Row Set Detail Listing            Review detailed information about a specific Row Set, or about all Row
                                  Sets defined in your current set of books. General Ledger prints the
                                  details of each row definition, with display and format options for each
                                  row appearing in a box. General Ledger also prints your account
                                  assignments and your calculation definitions, if any.
Row Order Detail Listing          Review detailed information about a specific Row Order, or about all
                                  Row Orders defined in your current set of books. For each Row Order,
                                  this listing prints the ranking and display options.
Content Set Detail Listing        Review detailed information about a specific Content Set, or about all
                                  Content Sets defined in your current set of books. For each Content
                                  Set, this listing provides the processing type and the account
                                  assignments. General Ledger also prints a concatenation of the display
                                  types for each segment value range and whether you chose to report
                                  on summary balances only.
Report Set Detail Listing         Review detailed information about a specific report set, or about every
                                  report set you have defined in your current set of books. This listing
                                  prints the report components and report options of each report assigned
                                  to your report set, including budget and encumbrance information.
10.6.3 Where Used Report

This is a very useful report to use when undertaking report maintenance.
For example, It can be used to compare the usage of an existing cost centre when compared
against that of a newly added cost centre. Once a comparison of usage has been made the report
components can be updated accordingly to include or exclude the new cost centre.
Where Used Report                 Determine where specific segment values are used in your Row Sets,
                                  Column Sets and Content Sets. This report prints each report
                                  component, sequence number, description and account range that
                                  includes the segment value you request when you run your report.

10.6.4 Chart of Account Listings

If you are using either an FSG report and suspect that some changes have been made to the chart
of accounts since the last time that you ran the report then you can run one of the following listings.
All of the Chart of Accounts listing provide live data from the production system at the time of
submission, so they will show any changes as soon as they have been made.
Again these can be run as standard reports from the Oracle application. A brief description of the
most relevant reports is provided below.
Chart of Accounts Listing         Review the chart of accounts for your current set of books, including
                                  detail and summary accounts. General Ledger first prints your enabled
                                  detail accounts, then your disabled detail accounts, and finally your
                                  summary accounts. Each of these three groups begins on a new page.
                                  Within each of the three groups, your accounts are sorted by their
                                  account segment values.
Rollup Detail Listing             Review all valid child segment values for each parent segment value for
                                  a specific account segment. This listing includes descriptions for both
                                  the parent and child segment values and the rollup group (if any) to
                                  which your parent segment value belongs. General Ledger sorts this
                                  listing in ascending order by account parent segment value. Within
                                  each parent segment value, General Ledger sorts the child segment
                                  values in ascending order.
Rollup Range Listing              Review a list of all parent segment values for an account segment. This
                                  listing includes information about each parent segment value, such as
                                  the rollup group to which each parent segment value belongs, whether
                                  each parent segment value is enabled and its range of child segment
                                  values. General Ledger sorts this listing in ascending order by parent
                                  segment value.

10.7 FSG SQL Scripts

In addition to the standard Oracle reports mentioned above you can also use the sql scripts in the knowledge base to review and audit your FSG report definitions at a detail

11 Leveraging ADI Tools & Functionality
11.1 Report Scheduling

Oracle Reporting tools all come with the functionality to schedule reports to run a pre-set times and
dates or even pre-set intervals. This should be leveraged as much as possible to remove the strain
on the server and network during normal office hours and take routine reporting to over-night
This can be achieved from the Oracle applications, ADI or the Discoverer tools. To integrate this
solution most effectively then the Web publishing features of ADI can be used to process and
distribute reports over the Intranet. This feature allows the reports to be published to password
protected intranet sites, and updated at pre-set times. Each published report can also be
accompanied by an Excel spreadsheet available for download should more in depth analysis of the
report be required.
Another advantage of this method is that the reports are published to a single web site, and the full
Excel sheet is only pulled across the network if the user specifically requests it. This eliminates the
wasted network time taken by just check the bottom line of figure for very large reports.
11.2 FSG Drilldown

It is possible to drill from the financial balances in an FSG report right down into the journal lines
from the originating sub-ledger. This feature should always be considered when the users are
asking for a report that gives transaction level detail. What are they really asking for ? Do they
genuinely need a custom transaction listing, or are they just asking for it to be able to reconcile
financial balances. If the latter is true then it is possible that the drill down feature of ADI is a
suitable solution.
Refer to the ADI documentation on for more information on this.
11.3 Hierarchy Editor.

With release 10.7 the Hierarchy Editor that used to be located in the GL application has been
moved to the ADI tool. This allows drag and drop editing of each segment in the chart of account
structure ( which is very dangerous in the wrong hands ), but more usefully it can be restricted to
view only mode via profile option and allows the users to visualise the chart of accounts hierarchy
graphically without the risk of unexpected updates being made.

This is particularly beneficial as users should not have access to the flexfield value screen in the
core applications.


Shared By: