Account List Tables

Component tables

When Account List editor saves the account list, it stores it in two tables one of which carries the names and id numbers of the lists, and another one contains the account ranges that the lists are made of.

The first table is SF_SYSAccountRangeNames, and has the following structure:

Column Generalized data type Use
NameId SMALLINT Unique id number of the account list
Name VARCHAR(255) Name of the account list
Description VARCHAR(255) Description of the account list
SF_UserId VARCHAR(255) User-id of the user that last saved the list
SF_DateChanged CHAR(8) Date of the last update of the row (CCYYMMDD)
SF_TimeChanged CHAR(6) Time of the last update of the row (HHMMSS)
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The following partial example shows some rows from table SF_SYSAccountRangeNames:

NameId Name       Description
------ ---------- ----------------------------------------
6      CSV        Cash Surrender Value - Life Insurance
8      FA         Fixed Assets
9      Land       Land only
14     CL         Current Liabilities
16     AP         Accounts Payable
18     CPLTD      Current Portion Long Term Debt
19     LTD        Long Term Debt
20     StockP     Stock Purchases
23     EQ         Equity
24     EQCS       Equity, Capital Stock
26     EQPL       Equity, Current Profit & Loss
27     AR         Accounts Receivable
31     FAC        Fixed Assets, Cost
32     FAD        Fixed Assets, Depreciation
33     TA         Total Assets
30     INV        Inventory, Merchandise and Supplies
28     InvM       Merchandise
35     FAM        Machinery & Equipment
36     FAB        Buildings
37     FAA        Automobiles & Trucks

The second table is SF_SYSAccountRanges, and has the following structure:

Column Generalized data type Use
NameId SMALLINT Unique id number of the account list
FromAccount VARCHAR(255) First account number in the range
ToAccount VARCHAR(255) Last account number in the range
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The following partial example shows some rows from table SF_SYSAccountRanges:

NameId FromAccount ToAccount
------ ----------- ----------
1      100         200
2      105         110
30     130         150
31     260         260
31     261         261
31     263         263
32     262         262
32     264         264
32     268         268
32     272         272
33     100         299
35     263         264
36     261         262
37     267         268
38     271         272
39     190         190
41     380         380
42     300         305

Account List composite view

The above tables are joined together at their NameId column in a view that is used by STEPforward whenever it loads data for the account lists:

create view SF_SYSVAccountRanges (Name, Description, SF_UserId, FromAccount, ToAccount, SF_TimeStamp) as select SF_Alias2.Name, SF_Alias2.Description, SF_Alias2.SF_UserId, SF_Alias1.FromAccount, SF_Alias1.ToAccount, SF_Alias2.SF_TimeStamp from SF_SYSAccountRanges SF_Alias1,SF_SYSAccountRangeNames SF_Alias2 where SF_Alias1.NameId = SF_Alias2.NameId

The virtual table created by the view is seen by STEPforward as containing the following sample data:

Name       Description                              FromAccount ToAccount
---------- ---------------------------------------- ----------- ----------
ADMIN      Administration & Selling                 800         802
ADMIN      Administration & Selling                 815         815
ADMIN      Administration & Selling                 900         900
ADMIN      Administration & Selling                 906         992
AP         Accounts Payable                         330         360
AR         Accounts Receivable                      113         114
BL         Bank Indebtedness                        300         305
BondClose  Bond Closing Expenses Amortized          200         200
CA         Current Assets                           100         200
CL         Current Liabilities                      300         399
CLLTD      Capital Loans, LongTerm Debt             430         434
CPLTD      Current Portion Long Term Debt           390         390
CPLTDY     Current Portion Long Term Debt due withi 439         439
CSV        Cash Surrender Value - Life Insurance    180         182
Cash       Cash and Equivalent                      105         110
Chart      Account Chart                            100         999
CostOfSale Cost of sales                            700         720
CostOfSale Cost of sales                            905         905
EQ         Equity                                   500         599
EQCS       Equity, Capital Stock                    510         510
EQPL       Equity, Current Profit & Loss            570         570
EQRT       Equity, Retained Earnings                560         570
EQSR       Capital Stock Redemption                 520         522