Oracles Mass Allocation (aka “Stored Procedure”): A formula that allocates revenues or expenses across a group of profit centers, departments, divisions or companies. Whatever you call them, in Deltek Vision these are your Organizations (Orgs). The formula creates a Transaction Entry file for posting into your General Ledger.
In Oracle, a Mass allocation is a process through which one can distribute
and allocate expenses to various accounts. It is a very useful tool to properly distribute amounts between accounts and across organizations. For example, rent for the entire premises can be allocated to different orgs based on the area occupied by each department. The Mass allocation journals in Oracle are handled in a step by step manner. The following steps are involved in creating a Mass Allocation Journal Entry:
1. Define the Mass Allocation – The definition contains parameters as to how and what is to be distributed.
2. Validate the Mass Allocation – The second step is to validate the mass
allocation to ensure that the accounts, orgs and/or projects are correct and
3. Generate the Mass Allocation Journal – Finally, generate the mass
allocation journal so as to distribute or allocate the amounts based on the
4. Review and post the entries – Once the mass allocation journals are
generated, the last step in the process is to review the journals and post the
entries to the Oracle GL General Ledger.
In Deltek Vision, we can emulate the Mass Allocation formula by creating a SQL Stored Procedure. These can be used to automatically create journals, instead of manually calculating in a spreadsheet and then keying in or importing the journal for posting. A single Stored Procedure can perform multiple allocations. We can use these to automatically create Journal Entries, Labor Adjustments, AP Vouchers, Unit
Transactions or any other type of transaction entry.
And all are done via a SQL Stored Procedure, once written it is a simple click of a mouse followed by posting in Vision. These system shortcuts can simplify your life, automate closing entries and take you out of spreadsheet calculations improving your monthly close time and reporting process. The following steps are involved in creating a SQL Stored Procedure in Deltek Vision:
1. Define the SQL Stored Procedure – The definition contains parameters as to how and what is to be distributed. Chart out the formula in a spreadsheet to identify where and what information is required. Next identify the calculations that need to be made and upon what criteria. The spreadsheet becomes the basis for your SQL Stored Procedure and validation.
2. Create the SQL Query – Write the SQL Query in SQL Management Studio and
test the formula to ensure that the calculations, accounts, orgs and/or projects are correct and accurate.
3. Validate the SQL Query – The next step is to test and create the SQL Stored Procedure. Then to add the SQL Query from above and decide how it will be generated. Several options here, a scheduled Workflow, or directly from the SQL Server, a simple shortcut to “double click” or it can be scheduled.
4. Execute the SQL Stored Procedure – Finally, generate the transaction file
so as to distribute or allocate the amounts based on the defined formula.
5. Review and post the entries – Once you have executed the SQL Stored
Procedure the transaction files are generated in Deltek Vision. The last step in the process is to review the un-posted transaction files and then post the entries to the Deltek Vision GL General Ledger.
There are no limits for the uses for SQL Stored Procedures. Allocations of Corporate Overhead, Professional Liability Insurance, Fringe Benefit Costs, Rent, Other Expenses, WIP Adjustments and even Overtime Premium Costs not posted in timesheets just to name a few. Another common need is automated units that coincide with timesheet entry for Computer Time, Add-On’s tied to billed hours and many others.
Mass Allocations versus Recurring Journals