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 |
SF_Description | VARCHAR(60) | Free-formatted description of component |
SF_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 |
SF_Description | VARCHAR(60) | Free-formatted description of component |
SF_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:
SF_Description SF_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:
SF_Description SF_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 | ![]() | Unique number assigned to each row in the table |
Operating_Unit | ![]() | Prefix code for the "Operating Unit" |
GL_Account | ![]() | GL code for the "GL Account" |
SF_Locked | ![]() | Account lock flag |
SF_Allocation | ![]() | Flag for automatic allocation of transactions |
SF_MatchFieldId | ![]() | Id number of TX field used as a match field in OI |
SF_MatchStrategy | ![]() | Code for the matching strategy in Open Items |
SF_PrimaryOPISign | ![]() | Sign of the primary Open Item's amount (+ or -) |
SF_ChequeSourceDoc | ![]() | Name of the Source Document for cheque TX |
SF_NameFieldName | ![]() | Name of the field with payee name |
SF_DueDateFieldName | ![]() | Name of the field with due date |
SF_BankAccount | ![]() | String with GL account for Bank TX |
SF_DiscountAccount | ![]() | String with GL account for Discount TX |
SF_Subledger | ![]() | Name of the attached Subledger Template |
SF_TimeStamp | ![]() | 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.