GL Account Tables

Component tables

When Account Prototypes utility saves the account chart structure, it creates one table for each level of Prefix, and one table for the GL numbers. Names of the tables are formatted by adding the standard SF_GL prefix to the name of the Prefix or GL Number level (embedded blanks in the user-supplied names are replaced with the underscores). For example, for the account structure that includes one-level Prefix named "Operating Unit" with the code size of 2, and the GL Number named "GL Account" (code size 3) STEPforward creates the following two tables:

SF_GLOperating_Unit table:

Column Generalized data type Use
Description VARCHAR(60) Free-formatted description of component
Code CHAR(2) GL Prefix component (2 numeric chars)
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

SF_GLGL_Account table:

Column Generalized data type Use
Description VARCHAR(60) Free-formatted description of component
Code CHAR(3) GL Number component (3 numeric chars)
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The component tables are filled with the account component codes that can be used in constructing the full GL account numbers. The following example shows some rows from the above tables:

SF_GLOperating_Unit table:

Description                                                  Code SF_TimeStamp
------------------------------------------------------------ ---- ------------
Administration                                               10   0
Laundry                                                      20   0
Grand Rapids                                                 30   0
Traverse City                                                31   0
Detroit                                                      33   0
Kalamazoo                                                    34   0

SF_GLGL_Account table:

Description                                                  Code SF_TimeStamp
------------------------------------------------------------ ---- ------------
Cash On Hand                                                 105  0
Bank Account                                                 106  0
Savings Account                                              107  0
Liquidity Account                                            108  0
Central States Stock                                         109  0
Accounts Receivable                                          113  0
Allowance For Bad Debts                                      114  0
Resale Flat Goods                                            130  0
Resale Garments                                              131  0
Resale Rugs/Misc                                             132  0
Resale Paper Products                                        133  0
Rental Flat Goods                                            140  0
Rental Garments                                              141  0
Rental Rugs/Misc                                             142  0
Rental Terry Goods                                           143  0
Rental Products - Unit/Turn Amortization                     147  0
Rental Products - Restored                                   148  0
Amortization and Restoration Expensed                        149  0

Account Chart table

In addition to the Component tables that are created for each level of the GL Prefix and GL Number, STEPforward creates one unified table (SF_SYSGLAccount) that includes one column for each Component table, plus a number of system-defined columns as shown below:

Column Generalized data type Use
SF_IDG INT Unique number assigned to each row in the table
Operating_Unit CHAR(2) Prefix code for the "Operating Unit"
GL_Account CHAR(3) GL code for the "GL Account"
SF_Locked CHAR(1) Account lock flag
SF_Allocation CHAR(1) Flag for automatic allocation of transactions
SF_MatchFieldId SMALLINT Id number of TX field used as a match field in OI
SF_MatchStrategy CHAR(1) Code for the matching strategy in Open Items
SF_PrimaryOPISign CHAR(1) Sign of the primary Open Item's amount (+ or -)
SF_ChequeSourceDoc VARCHAR(255) Name of the Source Document for cheque TX
SF_NameFieldName VARCHAR(255) Name of the field with payee name
SF_DueDateFieldName VARCHAR(255) Name of the field with due date
SF_BankAccount VARCHAR(255) String with GL account for Bank TX
SF_DiscountAccount VARCHAR(255) String with GL account for Discount TX
SF_Subledger VARCHAR(8) Name of the attached Subledger Template
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The table SF_SYSGLAccount holds one row for each GL account in the Account Chart. The SF_Locked column stores Y if the account is locked, otherwise it contains NULL (or blank, if the flag was once set to Y and then turned off). The SF_Allocation column stores P or F if the account is open for allocation of transactions into the past and future, or into the future only, otherwise it contains NULL (or blank, if the flag was once set to P/F and then turned off). The SF_Subledger column is set to the name of the Subledger Template attached to the GL account (making it a controlling GL account), or NULL if no Subledger is attached. The SF_TimeStamp columns stores the version number of the row; this number is incremented by 1 every time the row is saved, and is used in resolving concurrency problems in the multi-user environment.

The remaining columns are used only with those GL accounts that support Open Items:

SF_MatchFieldId -- id number of the transaction field in the Common Template that provides the match key that holds together a group of the Open Item transactions. If the account does not support Open Items, this column contains NULL (or 0, if the account was once set to support Open Items and then turned off).

SF_MatchStrategy -- a flag that is set to A for automatically matched Open Items, or S for the hand-picked selection. If the account does not support Open Items, this column contains NULL (or blank, if the account was once set to support Open Items and then turned off).

SF_PrimaryOPISign -- a flag that is set to + (plus) for primary Open Items with positive Amounts (debits), or - (minus) for primary Open Items with negative Amounts (credits). If the account does not support Open Items, this column contains NULL (or blank, if the account was once set to support Open Items and then turned off).

The following columns are used only with Open Item accounts that support writing of cheques:

SF_ChequeSourceDoc -- name of the Source Document that the generated accounting transaction will use.

SF_NameFieldName -- user name of the transaction field in the Common or Extra Template that provides the name of payee (i.e. the contents of "Pay To" line on the printed cheque).

SF_DueDateFieldName -- user name of the transaction field in the Common or Extra Template that provides the due date for payment.

SF_BankAccount -- GL account for the Bank transaction (stored as character string with the GL Prefix and GL Number separated by a blank space).

SF_DiscountAccount -- GL account for the Discount transaction (stored as character string with the GL Prefix and GL Number separated by a blank space).

If the account does not support writing of cheques, the above columns are set to NULL.

Account Chart composite view

The SF_SYSGLAccount table contains setup-dependent columns for the GL Prefix and GL Number components that vary from one database configuration to another. To simplify access to the account chart table, STEPforward creates a view that combines all GL Prefix components into one virtual column by concatenating fields for all prefix levels, and it assigns standard system names to the GL Prefix and GL Number columns (SF_GLPrefix and SF_GLNumber respectively). For the SF_SYSGLAccount table described in the previous section the view is created via the following SQL command:

create view SF_SYSVGLAccount (SF_IDG, SF_GLPrefix, SF_GLNumber, SF_Locked, SF_Allocation,
SF_Subledger, SF_MatchFieldId, SF_MatchStrategy ,SF_PrimaryOPISign, SF_ChequeSourceDoc,
SF_NameFieldName, SF_DueDateFieldName, SF_BankAccount, SF_DiscountAccount) as select SF_Alias.SF_IDG, SF_Alias.Operating_Unit, SF_Alias.GL_Account,SF_Alias.SF_Locked,
SF_Alias.SF_Allocation, SF_Alias.SF_Subledger, SF_Alias.SF_MatchFieldId, SF_Alias.SF_MatchStrategy, SF_Alias.SF_PrimaryOPISign, SF_Alias.SF_ChequeSourceDoc,
SF_Alias.SF_NameFieldName, SF_Alias.SF_DueDateFieldName, SF_Alias.SF_BankAccount,
SF_Alias.SF_DiscountAccount from SF_SYSGLAccount SF_Alias where SF_IDG != NULL

Indexing

When a new SF_SYSGLAccount table is created by STEPforward, two indices are created on its fields: one on the SF_IDG field, and another one on the compound field that combines all columns for the GL Prefix and the column for GL Number. The exact syntax of the CREATE INDEX commands varies from one RDBMS to another; the following example shows the commands formatted for Sybase:

create index IndexID on SF_SYSGLAccount (SF_IDG) on SF_IndexSegment
create index IndexID on SF_SYSGLAccount (Operating_Unit, GL_Account) on SF_IndexSegment

GL Account number

Externally, account numbers are shown as character-based strings, taken from SF_GLPrefix and SF_GLNumber columns of SF_SYSVGLAccount view. However, when the account number is used in accounting transactions or in the subledger records, the records store the internal representation of the account number from SF_IDG column of the SF_SYSVGLAccount view.