The Get Balance function is probably the most important function in GL Wand. It returns a General Ledger balance individually or summed based on the parameters that you enter.
Example =Get_Balance("Jan-02","YTD","USD","E","A","BIS CORPORATE","01","T","4000-4160","0000","000")
Clicking the Get Balance function on the GL Wand toolbar will open the Get Balance Wizard.
This form has lists of values for items like period name, balance type, actual flag etc. Select the applicable values and enter a valid currency code and budget or encumbrance name if required.
Double clicking a segment value or clicking the list of values button next to the segment will open the segment list of values form for that segment. See the Segment List of Values section for details on how to use this form. When you click the “OK” button on the Segment List of Values form the selected segment parameters are entered against that segment in the Get Balance Wizard form.
If your Oracle configuration includes dependant segments as part of the accounting flexfield, this form will support selecting values based on the independent and dependant segment values. Simply select a value from the available list of values for the independent segment. Then open the list of values for the dependant segment. The list of available values will be limited to only those values that are related to the selected independent segment value.
When you have entered all the parameters click the “OK” button to insert the Get Balance function into the selected cell with the entered parameters. Clicking the “Edit” button will open the Get Balance function in the Excel formula wizard form which will allow you to link parameters to cells in the Excel sheet.
You can also open the Get Balance formula in the Excel formula wizard form using the fx button on the formula bar or by selecting the “Insert Function” menu item on the Excel menu bar.
| Parameter | Description |
| Period | The period name as configured in Oracle. This parameter must be entered in exactly the same format as the period names in Oracle. For example if your period names are configured as follows “Jan-07” in Oracle and you enter “Jan-2007” or “January-07” or “jan-07” for this parameter, GL Wand will not return any data.
When used in conjunction with the special Balance Type “CTD” (Custom to Date) you can specify a custom period selection. For example “Feb-07-Aug-07” will add all the movement for the entered period range. You may enter multiple lists or ranges of periods and can use wild cards as well e.g. “Jan-0%” or “%-07”. When used in conjunction with the special Balance Types “JED”, “JEDU” or “JEDP” (Journal Effective Date) the period parameter becomes a date parameter e.g. “01/01/2007-05/01/2007,10/01/2007”. The format that your date is in is important. The “-“ character is used in GL Wand to denote a range of values and therefore it cannot be used as a date delimiter e.g. 01-01-2007 would not be valid. So take care if your Windows regional setting use the “-“ as a date delimiter. For JED type calculations we recommend that you use the “/” as a date delimiter and that you always format the date as text in Excel otherwise Excel will convert the date based on your regional setting. |
| Balance Type | YTD, QTD, PTD, PJTD, CTD, JED, JEDP or JEDU all in upper case.
|
| Currency Code | The currency code as configured in Oracle all in upper case e.g. “USD”. |
| Translated Flag | Enter E, T or C. Use E to retrieve the entered amount and T for the translated amount. Use C to report on the converted amount i.e. the functional currency equivalent of a foreign currency amount.
Only E is valid if requesting a balance for the functional currency of the current Set of Books. You must ensure that you have run the currency translation process in Oracle for the selected currency and period before reporting on translated balances. If you enter T to request the translated balance and there is no current translated balance available in the system a zero is returned. Oracle Release 12 provides a new feature to allow reporting on the values entered in the functional currency only i.e. excludes any foreign currency transactions converted to the functional currency. Therefore, in Release 12, if you use the E (entered) flag in conjunction with the functional currency, any converted amounts will be excluded. To report on the total balance (including values entered in functional currency and values converted to the functional currency) use the new “Total” option. This option is only available if you log on to a release 12 Oracle instance. |
| Actual Flag | A – Actual, B – Budget, E – Encumbrance. |
| Bud / Encumb Name | If you enter B or E above enter the exact budget name or encumbrance type here. Otherwise simply enter “” for actual balances. |
| Segment1 through Segment20 | Enter the segment value here. You must enter it as a text value including any leading zeros e.g. enter segment value “10” as “0010” if this segment is configured as four characters with leading zeros in Oracle.
You may enter your segment values in a variety of flexible ways including parents, child values, ranges, wildcards, excludes etc. See the explanation below on valid values for this parameter. To include all values simply enter “%”. Please note as from GL Wand 3.85.0 you can no longer leave the segment blank, a value is required. |
| Book | Optionally enter a set of books id to bind the formula to a specific set of books. If this parameter is omitted, the set of books currently selected in the Set of Books dropdown list will be used. |
| Options | This parameter is used to modify the results returned by the Get Balance function from the standard behavior. Currently two options are supported.
Source – Limit the results to a specific journal source. Category – Limit the results to a specific journal category. The criteria needs to be entered as follows - “Source=Manual” or “Category=Adjustment”. To specify both a source and category use the “|” as a delimiter e.g. “Source=Manual|Category=Adjustment”. You can specify multiple values by separating them with “,” e.g. “Source=Payables,Receivables”. |
When creating Get Balance formulas always use “%” to denote all values as this allows more efficient SQL to be generated. Do not use a range like “000-ZZZ” as they will require unnecessary processing.
The segment value parameters can be entered as
a single value representing a child or parent account e.g. “1500” might represent a parent account.
a range of values e.g. entering “1500-1600” in one of the segment parameters will sum all the segment values between 1500 and 1600 for that segment.
a list of segment values e.g. entering “1500,1510,1590” in one of the segment parameters will sum the specific segment values entered. If any of the segment values are parents GL Wand will sum the hierarchy for that parent.
wild card entries e.g. 1%00 will sum 1100, 1200, 1300, 1400 etc. or entering the value 1% will sum all values that start with 1.
values to be included and then specific exclusions e.g. “1000-2000,~1500” will sum the values between 1000 and 2000 but will skip 1500.
a combination of any of the above e.g. "1000-2000,2371,2375,2560-2580,3000,~3200,4000,~4161-4169". In this way you can build up your own calculation definition in the segment parameter.
Please note – there are two additional methods for specifying segment criteria in the Get Balance function. The one allows you to specify account combinations and the other allows the use of segment descriptive flexfields. Please see the Special Get Balance Criteria section in order to use these options.