GL Budget Utilities
**wss 1/31/2023
Set Inflation Defaults
Regroup Line Items
Set Calc Using Distribution Flag to
Assign Distribution
Calculate Allocations
Calculate Distributions
Cleanup GL Budget Prepare (Line Items)
Create Personnel profile Earned Hours
Load Actuals to Line Item User Columns
Delete GL Line Items
Force GL Prep Inflation Recalc
Set Allocation Master
Transfer GL Line Items to GL Budget
Empty Budget (Start Over)
Clone Source to Target Version
Base Load Budget From Prior Version
Merge Budget From Prior Version
Load From Staging Data Load – Merge to Line Item 1 in Target Version
Clear Target Field Values (Reset to 0)
Reset Secondary GL Budget Flag
Refresh GL Distribution Actuals
Refresh GL Distribution Budgets
Delete Unused GL Master Accounts
GL Distributions From Actuals
Load Depreciation Detail to Budget Prepare
#OCDM Table Population
Refresh Materialized OCDM Source Service Recipients Table
Copy Partial Budget Prepare Version
Move Partial Budget Prepare Version
Update GL Master Mapping
Load Trial Balance
Load Remote Budget
Utilities By Version
Set Inflation Defaults
Parameters
- Budget Version – Required
Documentation
What It Does
Sets the inflation percent for accounts defined in the inflation dictionaries.
When to Run
As required
Requirements
Inflation dictionaries and Budget version
Regroup Line Items
Parameters
- Budget Version – Required
Documentation
What It Does
Takes line item accounts that are the same except for the line number (excluded notes)
And groups them into a single line. The intent of this process is that aligns all the data into a single line for calculations, typically in budget control.
When to Run
As required
Requirements
Budget Version
Notes
Note that the process cannot be reversed and detailed notes would not be retained in the grouped row.
Set Calc Using Distribution Flag to …..
Parameters
- Budget Version – Required
- Checked = Calculate Using Distribution – Required
Documentation
What It Does
When checked, the amount column is the sum of GL Period 1 to 12 (GL Distribution).
When unchecked, the amount column is the sum of
Base amount,
Amounts 1 to 6
Inflation (Base * Inflation Percent),
Material management (Unit price * number of units)
In a similar fashion,
Checked: GL hours distribution
Unchecked: Base Hours
When to Run
As required
Requirements
Budget Version
Notes
Running this feature never deletes any data, however, if you then turn around and distribute that data and your source columns for amounts was the distribution, you could end up deleting something you may want to retain. Consider that as part of the budget control process to store certain values in user defined columns. This will not affect the Base Amount
Parameters
- Budget Version – Required
Documentation
What It Does
Assigns GL Distribution codes based on the assignment code tables
When to Run
As required
Requirements
GL Budget Version
Parameters
- Budget Version – Required
Documentation
What It Does
Processes the allocation calculation based on current allocation code settings
Note: Calculating allocations also calculate distributions
When to Run
As required
Requirements
GL Budget Version
Parameters
- Budget Version – Required
- Cost Centre
Documentation
What It Does
Processes the distributions based on current allocation code settings. Records coming from the salary module are ignored.
When to Run
As required
Requirements
GL Budget Version, or for a single cost centre (option)
Cleanup GL Budget Prepare (Line Items)
Parameters
- Budget Version – Required
Documentation
What It Does
Deletes line items for the specified version that meet all of the following criteria:
– Have no budget documents
– Description is blank
– Amount, Hours, Base Hours, Calc Base Amount, Base Hours are all 0
– Calc 01 to Calc 06 fields are 0
– User Amounts 01 to User Amount 30 fields are 0
Any line items that meet the above criteria and are going to be delete also remove the following linked records:
– Pay Period Distribution Records
– Allocation Records
When to Run
As Needed
Requirements
GL Budget Version
Create Personnel profile Earned Hours
What It Does
Draws salary budget data and auto-generates the Personnel Profile Earned hours based on MIS coding configured in the system tables (Production Type, Employee Class, Employee Type and Earning Benefit Types (for Earnings) See Target_MIS value code in each of these tables
When to Run
Typically this routine will be automated as part of the daily budget process job.
Requirements
General Ledger budget Version
Notes
Load Actuals to Line Item User Columns
What It Does
Pulls data from ESM General Ledger actuals for the specified years.
Can include GL or Pay Budget (can process both at once)
When to Run
On demand as required.
Requirements
Specify at least one source type (GL and/or Pay Budget)
Specify at least one source fiscal year
Specific at least one target budget prepare user column number (1-30)
Notes
Delete GL Line Items
What It Does
Permanently deletes GL line items from budget version
When to Run
To cleanup unwanted lines.
Requirements
Budget Version and source code type(s)
Typically will also include selective Cost Centres and/or Secondary Accounts
Notes
<<Caution>> Cannot be reversed so caution needs to be used on range to be deleted.
Force GL Prep Inflation Recalc
What It Does
Forces table trigger to recalculate any non-salary line with inflation percent or amount
to be recalculated.
When to Run
If user configuration changes from option 0 to 8 (On the GL Budget table table),
this utility needs to run to align the existing records to the latest configuration.
Requirements
Budget Version
Notes
Xref: Inflation approach calculation settings
0 – Base,
1-6 Represents the Adjustment Column,
7 Means Base + All Adjustments,
8 Base + Adj 01 only
Set Allocation Master
Set Allocation Master
Parameters
- Budget Version – Required
- Cost Centre – Required
- Allocation Master – Required
Documentation
What It Does
Sets the allocation table code to the detailed records based on selected Cost Centre
When to Run
As required
Requirements
GL Budget Version, Cost Centre
Transfer GL Line Items to GL Budget
Parameters
- Budget Version – Required
- Target Fiscal Year – Required
- Target Distribution – Required
Documentation
What It Does
Transfers the ungrouped GL budget prepare lines items data (by version) to a single fiscal year target in ESM budget.
If the parameter @gl_adj_to_salary_account is configured with a table translation, then this secondary account translation is applied
When to Run
When ready to publish GL budget version to ESM reporting. A single target exists for Budget, Target or Forecast by fiscal year.
Requirements
Verify that the distribution is in balance for the version (GL Budget/Forecast Overview)
Notes
This process will overwrite the target based on fiscal year and the distribution type selected (Budget, Target, Forecast)
Parameters
- Budget Version – Required
- Source Code Type
- Reason Code
- What To Delete
- ALL
- Salary
- Non-Salary
Documentation
What It Does
Delete the entire version, or a selected portion of it based on either salary or non-salary.
Non-Salary is defined by the secondary account table flag where the salary flag is off (GL flag is on)
When to Run
As required. A site may want to replace the base budget loaded as the source has changed so to ensure there is a clean load, the GL base budget line items would be deleted and replaced with a new transfer or import.
Requirements
GL Budget Version
Clone Source to Target Version
Parameters
- Budget Version – Required
- Target Budget Version – Required
- What To Include
- ALL
- Salary
- Non-Salary
Documentation
What It Does
Copies the content of the source to the target. If the source GL budget version contains records that were sourced from the salary module, these records are NOT copied. Transfers from salary would be accomplished from the salary module from desired salary budget version, either through a recalculation or transfer utility.
When to Run
As required.
Requirements
GL Budget Version
Load GL Budget Total to Base of Another Version
Base Load Budget From Prior Version
Parameters
- Source Budget Version – Required
- Target Budget Version – Required
- Target Source Type – Required
- Is Salary Budget? – Required
- Is GL Budget? – Required
Documentation
What It Does
Deletes target before loading budget amount from source into base budget of the target version. Salary or pay budget sourced post an offsetting entry.
When to Run
As required
Requirements
Source and target versions, Target type, source types to be included
Note: This utility is used for base load only
Merge Budget From Prior Version
Parameters
- Source Budget Version – Required
- Target Budget Version – Required
- Target Source Type – Required
- Target Field
- Is Salary Budget? – Required
- Is GL Budget? – Required
Documentation
What It Does
Merges budget amount from source version into specified target field of the target version. This is a merge which means it will replace the amount column when found, or add the account when the detailed line item doesn’t exist.
Note: If Target field is base, then Salary or pay budget sourced post an offsetting entry.
When to Run
As required
Requirements
Source and target versions, Target type, source types to be included
Note: Basic difference is this second option allows loading data to field other than base such as target or forecast. Second option only merges, does NOT delete
Load From Staging Data Load – Merge to Line Item 1 in Target Version
Parameters
- Source Fiscal Year – Required
- Target Budget Version – Required
- Target Source Type – Required
- Target Field – Required
Documentation
What It Does
Ensures GL master accounts exists before importing
Then Load the data from the staging data load into line 1 or 0 of that specific account. This is a merge which means it will replace the amount column when found, or add the account when the detailed line item doesn’t exist.
PAYBUDGET will post to line 0 while other source code types will post to line 1. PAYBUDGET is intended to be opening balances for salary accounts so they need their own line not to conflict with SALBUDGET transfers.
When to Run
As required given special nature.
Requirements
Source and target version, Target source type and specific column to load it to.
Clear Target Field Values (Reset to 0)
Parameters
- Target Budget Version – Required
- Target Source Type – Required
- Target Field – Required
Documentation
What It Does
Sets the target field amount (along with accompanying offset column if it applies) to zero.
When to Run
If there was a load error from either a base load or staging data load, this allows a reset (clear to 0) without deleting the physical records.
Requirements
Source and target version, Target source type and specific column to clear. If clearing PAYBUDGET, the offset colulmn is also cleared.
Reset Secondary GL Budget Flag
Parameters
- What To Set – Required
- Mixed GL/Salary (Default)
- All GL
Documentation
What It Does
Sets the default for secondary based on MIS standard / exceptions to either ALL GL accounts (sites that budget salary in GL module) or mixed model of GL / Salary accounts.
When to Run
As required.
Requirements
Select either mixed for all GL accounts in dialogue
Refresh Materialized Tables
Refresh GL Distribution Actuals
Parameters
- No Parameters
Documentation
What It Does
Updates the materialized table to current ESM GL actuals (all years)
When to Run
After changing anything in ESM GL Actuals (after import or posting adjustments), update the materialized table before reporting.
Requirements
No parameters are required
Refresh GL Distribution Budgets
Parameters
- No Parameters
Documentation
What It Does
Updates the materialized table to current ESM GL Budget (all years)
When to Run
After changing anything in ESM GL Budget (after transferring or posting adjustments), update the materialized table before reporting.
Requirements
No parameters are required
Refresh GL Distribution Budgets
What It Does
Updates the materialized table to current ESM GL Budget (all years)
When to Run
After changing anything in ESM GL Budget (after transferring or posting adjustments), update the materialized table before reporting.
Requirements
No parameters are required
No Parameter Utils
Delete Unused GL Master Accounts
Parameters
- No Parameters
Documentation
What It Does
Clean up of GL master accounts that have no children.
When to Run
As required to ensure keep only valid accounts. Typically run it after an import and you discover that the import contained account combination that will never be used.
Requirements
Automated Distributions
GL Distributions From Actuals
Parameters
- Source Fiscal Year – Required
- Target Fiscal Year – Required
Documentation
What It Does
Pulls GL actuals for matching accounts in staging data load. Balances the distribution and calculates percentages.
Creates distribution records for target fiscal year based on Cost Centre & Secondary and automatically creates assignment records.
When to Run
As required for selected accounts
Requirements
GL account information to be considered must be imported into staging data load.
(Cost Centre & Secondary combinations)
Staging Fiscal year is ignored
Source fy
Example: Fiscal year (prior fiscal) being pulled for selected GL accounts to generate automatic distribution records.
Target FY
Distribution is being created usually for the next fiscal year based on prior year actuals.
Load Depreciation Detail to Budget Prepare
Parameters
- Fiscal Year – Required
- Budget Version – Required
- Source Type – Required
Documentation
What It Does
Post asset specific level depreciation to GL budget version as part of detailed budgeting process
When to Run
When Required
Requirements
– Fiscal Year (Normally Next Year)
– Budget Version
– Source Type (Normally GLBUDGET)
Parameters
- OCDM Series – Required
Documentation
What It Does
Runs procedure to populate all OCDM related tables
When to Run
As required, typically after change or table change
Requirements
Series
Refresh Materialized OCDM Source Service Recipients Table
Refresh Materialized OCDM Source Service Recipients Table
Parameters
- No Parameters
Documentation
What It Does
Refreshes the materialized table for the OCDM Source Service Recipients
When to Run
As required, usually after changing any of the OCDM pouplated tables
Requirements
None. Updates all years
Copy/Move Partial Budget Prepare Version
Copy Partial Budget Prepare Version
What It Does
Deleted target version filters first, then
copies source version portion (By Cost Centre and other filters).
Source and target version must be different!
When to Run
Intent is to combined desired portions of budget into one target version
Requirements
Source/Target budget versions and filters, minimum of a cost centre source.
Source type is optional. All sources (Except SALBUDGET) will then be included in the delete and copy for noted filters.
Notes
Target reason type option exists
Move Partial Budget Prepare Version
What It Does
Detaches source detail records and re-attaches to the target account.
When to Run
As needed, when detailed records need to be attached to a different header account.
Requirements
Minimum of a source and target cost centre. Can be within the same version but version source and target must be specified.
Notes
Reason code target can be included if want to tag or change current coding.
Source and target versions can be the same or different
Set Account Mappings
Update GL Master Mapping
What It Does
Using the referenced GL master/detail mapping, sets the target
Job Cost and Project.
Target reason code will only apply to ESM budget areas
When to Run
Typically this utility will be used the Job runner. Run to test a change to the
GL Master/Detail mapping if want to check results immediately in either GL Actual or Budget
Requirements
Fiscal Year, Actual (or Budget) and GL master mapping header ID as source
Notes
Can set either General Ledger Actual or Budget.
Currently used in concert with complex reporting mix reports. (Covid, Envelope funding)
Load GL Trial Balance From ESM GL Budget
Load Trial Balance
What It Does
Takes an image of what is currently is ESM GL Budget and posts
detailed (grouped accounts) to that version of GL Trial Balance.
When to Run
On demand
Requirements
Source Fiscal Year,
Source Code Types,
Target Trial Balance Version
Notes
Please note if you target the same version and it already exists, it will erease target before
load fresh data.
Load Remote Budget
That one is special. The prc_load_remote_budget isn’t compiled on most clients.
If it isn’t compiled than it will be suppressed on the utility pages it’s referenced on.
