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. Clicking the Get Balance function on the GL Wand toolbar will open the Get Balance Wizard.
The following options are available in the Function Wizard:
- Select a value from the drop down list. These lists are populated with the available values for each parameter or with the configuration data read directly from Oracle.
- Click the
button to link the parameter to an Excel cell. - With an Excel range reference in a parameter, use the F4 key to toggle the absolute and relative addressing as you would in Excel.
- Use the
button to enter account combination criteria. - Use the
button to enter descriptive flexfield criteria.
| Parameter | Description |
|---|---|
| Period Name | 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, and will instead return an Error (Period) message. 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 uses 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, FYS, FYE, JED, JEDP or JEDU all in upper case.
|
| Currency | The currency code as configured in Oracle all in upper case e.g. “USD”. |
| Translated Flag | Enter E, T or C (or Total on Oracle R12). 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 Ledger (or Total on Oracle R12). 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. |
| Budget | If you entered B above, this field will become available and you will need to enter a budget name. |
| Encumbrance | If you entered E above, this field will become available and you will need to enter an encumbrance name. |
| 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. To include all values simply enter “%”. The segment value parameters can be entered as:
|
| Ledger | This parameter cannot be modified as the segments are driven based on the current Ledger. The Ledger defaults to the item selected in the dropdown list on the toolbar, or to the value in the function when editing an editing Get Balance. |
| Include the Ledger Id | Choose to include the Ledger Id in the formula inserted in to Excel. |
| Journal Source | Limit the results to a specific journal source. |
| Journal Category | Limit the results to a specific journal category. |
| Movement | Show only the debit or credit amounts. Leave this field blank to show both. |
| Multiply | Use this parameter to switch the sign of the balance by multiplying by -1. Also useful to show the number in a factor e.g. thousands by multiplying by 0.001. |
| Decimal Places | Round the balance to the specified number of decimal places. Useful to use in conjunction with the Multiply parameter to show the number in a factor e.g. thousands by multiplying by 0.001 and then round to zero decimals. See the Multiply parameter for an explanation of why it is recommended to use this parameter rather than Excel functionality to round the balance. |
See Also
Creating Get Balance functions using segment combination criteria.
Creating Get Balance functions using descriptive flexfield criteria.
Attachments:
e4awand_32_get_balance.png (image/png)
ref_edit.png (image/png)
account_combination.png (image/png)
dff_criteria.png (image/png)
get_balance.png (image/png)