GL_Rollups

GL Account rollups are built based on the ranges specified in this table. The rollup structure enables you to create a hierarchical structure within the accounts dimension based on ranges.  Thus, rollups can be defined as ranges of accounts. For example, a range of accounts from 5000 to 5999 might go into a summary account called salaries.  The next level could be salaries and employee benefts, with the next level including total compensation expenses, operating expenses, total expenses, and net income.

Each GL Account number is compared against each row of the GL_Rollups table.  If the account number falls within the range of From_Acct_Code and To_Acct_Code, that account is included in the corresponding set of rollups.

The account hierarchies can be easily maintained by using this structure and the associated procedure. The hierarchy will be updated each time a new source is updated.

If there are non contiguous account ranges, each contiguous account range must be specified even if it only contains a single account.  There can be no overlap in account ranges.

Rollup_1 represents the top most level, then Rollup_2, so on and so forth.

Important:  Using this table for rollups will overwrite any rollups configured in the Account table.

To illustrate, let’s consider the following example using a few payroll tax accounts.  In the diagram below, there are a number of detail payroll taxes that are part of the overall Net Income hierarchy.  The numbers represent the corresponding rollup number, with rollup 1 being the top level of the hierarchy.

account.gif

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Idx Field Type Maximum Length Required
1 From_Acct_Code Alphanumeric 20 Yes
2 To_Acct_Code Alphanumeric 20 Yes
3 Rollup_1 Alphanumeric 100 No
4 Rollup_2 Alphanumeric 100 No
5 Rollup_3 Alphanumeric 100 No
6 Rollup_4 Alphanumeric 100 No
7 Rollup_5 Alphanumeric 100 No
8 Rollup_6 Alphanumeric 100 No
9 Rollup_7 Alphanumeric 100 No
10 Rollup_8 Alphanumeric 100 No
11 Rollup_9 Alphanumeric 100 No
12 Rollup_10 Alphanumeric 100 No

 

Summary

GL_Rollups

GL Account rollups are built based on the ranges specified in this table in <usp_UpdateGLRollups>.

Columns

 

From_Acct_Code

Starting range of account numbers used in comparison (inclusive). Required.

To_Acct_Code

Ending range of account numbers used in comparison (inclusive). Required.

Rollup_1

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_2

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_3

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_4

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_5

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_6

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_7

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_8

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_9

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Rollup_10

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

Documentation

 

Sample CSV Load File

This load file is a small sample of a GL Rollups load.

Sample CSV Load File

This load file is a small sample of a GL Rollups load using a few accounts based on the picture above.

Filename: GL_Rollups.csv

40000001,40000099,Net Income,Total Expenses,Total Operating Expenses,Total Compensation & Fringes,Total Employee Compensation,Compensation – Legal,Legal Salary,,,,
40100001,40100001,Net Income,Total Expenses,Total Operating Expenses,Total Compensation & Fringes,Total Employee Compensation,Compensation – Legal,Legal Bonus,,,,
45000001,45000099,Net Income,Total Expenses,Total Operating Expenses,Total Compensation & Fringes,Fringe Benefits & Other Emoloyee Costs,Payroll Taxes,,,,
 

Columns

From_Acct_Code

From_Acct_Code varchar(

20

) NOT NULL

Starting range of account numbers used in comparison (inclusive). Required.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

To_Acct_Code

To_Acct_Code varchar(

20

) NOT NULL

Ending range of account numbers used in comparison (inclusive). Required.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

Rollup_1

Rollup_1 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_2

Rollup_2 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_3

Rollup_3 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_4

Rollup_4 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_5

Rollup_5 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_6

Rollup_6 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_7

Rollup_7 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_8

Rollup_8 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_9

Rollup_9 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Rollup_10

Rollup_10 varchar(

100

) NULL

If the account falls within this range, the account is included in the rollup specified in this column. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk