Oracle Financial Statement Generator Report Basics

Financial Statement Generator (FSG) is a powerful report building tool for Oracle General Ledger.

1. Generate financial reports, such as income statements and balance sheets, based upon data in your general ledger.
2. Define segment value security rules to restrict financial information contained in FSG report output generated by specific users and responsibilities.

FSG Report uses the fundamental row/column concept to build the financial reports,a dn follow the below guidelines while defining a new report:
1. Decide which rows and columns will make up your report.
2. Define the rows and columns, and then tell FSG what attributes those rows and columns have.
3. Build a report using those rows and columns.

More than one row can be grouped to a Rowset and similarly more than once column can be grouped to a Column Set, and can be re-used in another reports.

Row sets and column sets are the two primary building blocks of FSG reports.

Example to create an FSG Report:

Create a Sample Rowset as below,

GL -> Reports -> Define -> Row Set

Click on Define Rows

Click on Account Assignments and define the ccounts for which you want to generate the Row details for,

 

 Activity can contain Net Dr, Cr values

Create a Column set as below,

GL -> Reports -> Define -> Column Set

Click on Define Columns

You can as well define Account Assignments at the Column level as well to control the data and values

Define Column Set with Column Headings as below,

Define a Financial Report as below,

GL -> Reports -> Define -> Report

Run the Report(Run Report from Report Definition screen), and it executes the following Query:

SELECT nvl(bal.PERIOD_TYPE, ''),
    nvl(bal.PERIOD_YEAR, -1),
    bal.PERIOD_NAME,
    nvl(bal.PERIOD_NUM, -1),
    nvl(bal.PERIOD_NUM, -1),
    bal.ACTUAL_FLAG,
    decode(cc.TEMPLATE_ID, NULL, 'N', 'Y'),
    -1,
    nvl(bal.BUDGET_VERSION_ID, -1),
    -1,
    nvl(bal.ENCUMBRANCE_TYPE_ID, -1),
    bal.CURRENCY_CODE,
    bal.SET_OF_BOOKS_ID,
    nvl(bal.TRANSLATED_FLAG, ''),
    nvl(bal.PERIOD_NET_DR, 0) - nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.PERIOD_NET_DR, 0),
    nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR,0)-nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR, 0),
    nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0) - nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0),
    nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0) - nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0),
    nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(SEGMENT1,''),
    nvl(SEGMENT2,''),
    nvl(SEGMENT3,''),
    nvl(SEGMENT4,''),
    nvl(SEGMENT5,''),
    nvl(SEGMENT6,'')
FROM GL_BALANCES bal,
    GL_CODE_COMBINATIONS cc
WHERE bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID
AND   cc.CHART_OF_ACCOUNTS_ID = 50286
AND   bal.SET_OF_BOOKS_ID  = 1
AND   nvl(bal.TRANSLATED_FLAG, 'x')in ('Y','N','x')
AND   cc.TEMPLATE_ID is NULL
--This is due to the Account assignments defined at row level
AND ( (nvl(SEGMENT2,'') >= '23000' AND  nvl(SEGMENT2,'') <= '23001')
)
--This is due to PTD and YTD Columns defined for the Column Set level
AND    (
(bal.period_name in ('Adj-14','Nov-13')
AND     (    (nvl(bal.period_name,'') = 'Adj-14'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
)
 OR
 (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))) ))) OR        (nvl(bal.period_name,'') = 'Nov-13'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))))))
) )

Print This Post