Transaction Tables

General structure

When the Common Template is saved by STEPforward the first time ever, a table and two views are created in the database. The table name is SF_TXDetail, and the views are SF_TXVTransaction and SF_SYSVOpenItems.

Similar to Global and Subledger tables, SF_TXDetail table contains one column for every field of the Common Template that was created by dragging-and-dropping the "New field" object from Palette to the Workspace. SF_TXDetail table contains the following mandatory columns that correspond to the system-defined data fields placed in the Common Template by STEPforward:

Column Generalized data type Use
SF_IDG INT Internal GL account number
SF_IDS INT Internal subledger account number
SF_Amount MONEY Amount recorded for each transaction
SF_Description VARCHAR(N) Description of the transaction
SF_DocReferenceNo VARCHAR(M) Transaction reference number
SF_DocumentDate CHAR(8) Transaction date
SF_AccountingPeriod SMALLINT Internal number of accounting period

More columns can be added to SF_TXDetail table by bringing user-defined data fields into the Common Template.

In addition to the standard data fields, STEPforward adds the following system-defined columns to SF_TXDetail table created for the Common Template:

Column Generalized data type Use
SF_ID INT Unique number assigned to each row in the table
SF_TX INT Unique number assigned to each TX document
SF_Line SMALLINT Unique number assigned to each line in TX document
SF_Extra SMALLINT Id of Extra table if used with the transaction
SF_Status CHAR(1) Completion status of the transaction line
SF_RecordType CHAR(1) Type of transaction line

SF_TXDetail of minimal configuration (for Common Template that does not have any user-defined data fields) is created with the following SQL command:

create table SF_TXDetail (SF_ID int NULL,SF_TX int NULL,SF_Line smallint NULL,SF_Extra smallint NULL,SF_IDG int NULL,SF_IDS int NULL,SF_Status char(1) NULL, SF_RecordType char(1) NULL, SF_Amount money NULL,SF_Description varchar(40) NULL,SF_DocReferenceNo varchar(40) NULL,SF_DocumentDate char(8) NULL,SF_AccountingPeriod int NULL)

STEPforward defines Transaction Document as a collection of transaction records created from the same Source, that share the same transaction number. Transaction records are stored in SF_TXDetail table, with their SF_TX numbers tracked in the SF_SYSMasterLog table.

Individual transaction records can use additional data fields not included into the Common Template (and, by deduction, into SF_TXDetail table). These extra fields are used to store special pieces of information that are not generally used in accounting transactions, but may be required for certain GL accounts (such as the Quantity field for a GL account that tracks transactions with sale of inventory parts, for example). Allocating the Extra fields in the Common template would be counterproductive and uneconomical; for this reason, they are stored in a number of Extra tables created by STEPforward.

The following SQL command shows an example of the Extra table that tracks Quantity and Due_Date fields:

create table SF_ETAR (SF_ID int NULL,Due_Date char(8) NULL,Quantity int NULL)

Names of Extra tables are based on the names of the templates, with SF_ET prefix attached to them. SF_ID column carries the same integer value as SF_ID column in SF_TXDetail table, thus tying together the base transaction record and its extentions. In addition to SF_ID column that points to the row in the Extra table, SF_Extra column in SF_TXDetail table carries the id number of the Extra template that identifies the Extra table in which the row is stored. Together, SF_ID and SF_Extra columns let STEPforward find the extention data to any row in SF_TXDetail table if such an extention exists (if SF_Extra column contains 0, there is no extention).

Master Log

When STEPforward creates a new database, it creates a number of system-defined tables, among them   SF_SYSMasterLog table with the following structure:

Column Generalized data type Use
SF_TX INT Unique number assigned to each TX document
UserId VARCHAR(255) User-id of the user that last updated the TX document
SF_DateChanged CHAR(8) Date of the last update of the document
SF_TimeChanged CHAR(6) Time of the last update of the document
SF_TimeStamp SMALLINT Version of the row (incremented with each update)
Posted CHAR(1) Status code of the TX document
IdNumber SMALLINT Id number of Source Document used in transaction
SystemState SMALLINT Number of system state

For each Transaction Document a row is added to SF_SYSMasterLog table; this row identifies the document by its transaction number (SF_TX), and tracks the login account of the user that created or last updated the document. SF_DateChanged and SF_TimeChanged columns store the date and time of creation/last change of the document in CCYYMMDD and HHMMSS formats respectively. The SF_TimeStamp columns stores the version number of the document; this number is incremented by 1 every time the document is saved, and is used in resolving concurrency problems in the multi-user environment. "Posted" column contains the status code of the document which may be P (posted), C (completed but not posted) or S (suspended).

See documentation of SF_SYSSourceDocument and YYY tables for the description of IdNumber and SystemState columns.

Fiscal Periods

Every transaction record (line) entered through STEPforward is identified by the accounting (fiscal) period to which the transaction belongs. In SF_TXDetail table this accounting period is stored in SF_AccountingPeriod column; the detailed information about the accounting period is stored in SF_SYSFiscalPeriods table with the following structure:

Column Generalized data type Use
PeriodId SMALLINT Unique number assigned to each period
StartDate CHAR(8) Date of the first day of the period
EndDate CHAR(8) Date of the last day of the period
Period SMALLINT Period number within its fiscal year
PeriodString CHAR(6) Character representation of the period (YY/PPP)
SF_Open CHAR(1) Flag to track Open/Closed status of the period
Version SMALLINT Version of period (incremented with each reopening)
SystemState SMALLINT Number of system state
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The PeriodId column stores the number of the accounting period that is incremented for each period added to the table; the id number of the first period in the table is 0. The Period column, on the other hand, contains the number of the accounting period that is unique within its fiscal year, and starts with 1 (e.g. monthly periods within the year are numbered from 1 to 12). StartDate and EndDate are recorded in CCYYMMDD format; PeriodString combines the last two digits of the fiscal year with the number of the accounting period with leading zeros (e.g. period 5 of year 1998 is stored as the character string of '98/05'). SF_Open is set to O for open accounting periods, and to C for the closed ones. The Version column keeps track of the number of times the period has been closed and re-opened again. The SF_TimeStamp columns stores the version number of the row; this number is incremented by 1 every time the period is saved, and is used in resolving concurrency problems in the multi-user environment.

See documentation of XXX table for the description of the SystemState column.

In addition to the SF_SYSFiscalPeriods table, the following supplementary information is stored in the SF_SYSSetup table:

ParameterKey Use
FiscalPeriod Type of the fiscal year breakup into accounting periods; the valid types are:
QUARTERLY
MONTH
HALFMONTH
FOURWEEK
TWOWEEK
WEEK
DAY
OTHER
FiscalPeriodSize Size of period in days (if FiscalPeriod = OTHER)
FirstFiscalPeriod Starting date of the first accounting period
FiscalYearEnd Last date of the last accounting period for the first fiscal year in the table
FiscalPeriodsInYear Number of accounting periods in the fiscal year

Note: data in the SF_SYSSetup table are stored in the form of character-based key-value pairs, with the key stored in the ParameterKey column, and value in the Value column.

Source Documents

Source Documents are basic descriptions of types of transactions supported by STEPforward. They are stored in the SF_SYSSourceDocument table:

Column Generalized data type Use
DocName VARCHAR(20) Unique name of the Source Document
Description VARCHAR(255) Description of the Source Document
Prefix VARCHAR(255) Prefix of GL account used in the first record
GL VARCHAR(255) Number of GL account used in the first record
Sign CHAR(1) Sign of the Amount in the first record (+, - or *)
Post CHAR(1) Flag to control posting of completed document
Type CHAR(1) Type - actual (A) or budget (B)
Subtype CHAR(1) Subtype -- R (restatement document) or blank
Style CHAR(1) Document style -- journal entry or specific source
DoneFlag CHAR(1) How to save -- automatically or manually
IdNumber SMALLINT Document id number
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

The Post column can be set to S (Save only), P (Save & post) or H (Hold). The Type column contains A (Actual) or B (Budget) codes. Subtype can be R for Restatement, or blank. The Style column is set to J if both Prefix and GL column contain asterisks (*), otherwise it is set to blank (documents of J style are treated as journal entries, otherwise they are considerd to be of a specific Source). The DoneFlag contains Z if the Document is automatically saved when the running total becomes zero, or D if it is to be saved when the "Doc" button is pressed.

Transaction View

Every time STEPforward creates a table for the Common Template, it also creates a view that joins together SF_TXDetail, SF_SYSMasterLog, SF_SYSFiscalPeriods, SF_SYSSourceDocument, SF_SYSVGLAccount and SF_SYSTemplates tables into one coherent virtual table as per following example:

create view SF_TXVTransaction (SF_ID, SF_TX, SF_Line, SF_Extra, SF_IDG, SF_IDS, SF_Status,
SF_Amount, SF_Description, SF_DocReferenceNo, SF_DocumentDate, SF_AccountingPeriod,
SF_ExtraTable, SF_UserId, SF_DateChanged, SF_TimeChanged, SF_Posted, SF_TimeStamp,
SF_IdNumber, SF_SystemState, SF_Period, SF_DocName, SF_DocType, SF_Restatement, SF_GLPrefix,
SF_GLNumber, SF_Subledger, SF_RecordType) as select SF_TXDetail.SF_ID, SF_TXDetail.SF_TX,
SF_TXDetail.SF_Line, SF_TXDetail.SF_Extra, SF_TXDetail.SF_IDG, SF_TXDetail.SF_IDS,
SF_TXDetail.SF_Status, SF_TXDetail.SF_Amount, SF_TXDetail.SF_Description,
SF_TXDetail.SF_DocReferenceNo, SF_TXDetail.SF_DocumentDate, SF_TXDetail.SF_AccountingPeriod,
SF_ExtraTable = SF_SYSTemplates.TemplateName,SF_UserId = SF_SYSMasterLog.UserId,
SF_DateChanged = SF_SYSMasterLog.SF_DateChanged, SF_TimeChanged =
SF_SYSMasterLog.SF_TimeChanged, SF_Posted = SF_SYSMasterLog.Posted, SF_TimeStamp =
SF_SYSMasterLog.SF_TimeStamp, SF_IdNumber = SF_SYSMasterLog.IdNumber, SF_SystemState =
SF_SYSMasterLog.SystemState, SF_Period = SF_SYSFiscalPeriods.PeriodString, SF_DocName =
SF_SYSSourceDocument.DocName, SF_DocType = SF_SYSSourceDocument.Type, SF_Restatement =
SF_SYSSourceDocument.Subtype, SF_SYSVGLAccount.SF_GLPrefix, SF_SYSVGLAccount.SF_GLNumber,
SF_SYSVGLAccount.SF_Subledger, SF_RecordType = SF_TXDetail.SF_RecordType from
SF_TXDetail, SF_SYSMasterLog, SF_SYSFiscalPeriods, SF_SYSSourceDocument, SF_SYSVGLAccount,
SF_SYSTemplates where SF_TXDetail.SF_TX = SF_SYSMasterLog.SF_TX and
SF_TXDetail.SF_AccountingPeriod = SF_SYSFiscalPeriods.PeriodId and SF_SYSMasterLog.IdNumber =
SF_SYSSourceDocument.IdNumber and SF_TXDetail.SF_IDG = SF_SYSVGLAccount.SF_IDG and
SF_TXDetail.SF_Extra = SF_SYSTemplates.IdNumber and SF_SYSTemplates.Scope = 'T'

This view, in addition to preserving the original columns of SF_TXDetail table, translates some of its more obscure data into the readable form that can be used in queries:

SF_Extra column (id number of the Extra Template used in the transaction) is supplemented by SF_ExtraTable column that provides the name of the Template;

SF_IDG column (internal number of the GL account recorded in SF_SYSVGLAccount table) is supplemented by SF_GLPrefix and SF_GLNumber columns that provide the character-based GL Prefix and GL Number of the account;

SF_AccountingPeriod column (internal number of the accounting period in SF_SYSFiscalPeriods table) is supplemented by SF_Period column that provides the character representation of the period in YY/PPP format;

SF_DocReferenceNo column (id number of the Source Document used in the transaction)  is supplemented by SF_DocName column that provide the name of the Source Document.

Open Items table

When the Common Template is saved by STEPforward the first time ever, an Open Items table is created in the database. The table name is SF_SYSOpenItems, and its structure is as shown below:

Column Generalized data type Use
SF_ID INT Pointer to the row in SF_TXDetail table
MatchValue VARCHAR(255) Match key assigned to the Open Item
Amount MONEY Amount recorded for the Open Item
SF_Status CHAR(1) Status of the Open Item (O or C)
Hidden CHAR(1) Reserved
SF_Primary CHAR(1) Flag to track the Primary status of OI (P or blank)
SF_TimeStamp SMALLINT Internal number of the accounting period

Open Items are essentially extentions of transaction records stored in SF_TXDetail table. Any record in SF_TXDetail table that contains an Open Items GL account can be split into several Open Items, each tracked by a row in SF_SYSOpenItems table. The sum total of the Amount field in the OI records equals the SF_Amount of the anchor record in SF_TXDetail table; the SF_ID field of the OI records has the same value as the SF_ID field of the anchor record. The SF_Status column tracks the open/closed status of the Open Item and contains O or C flags. The SF_Primary column is set to P for the primary Open Items, and to blank for non-primary Items.

STEPforward also creates the following indices shown here in the Sybase format:

create index SF_LineId on SF_SYSOpenItems (SF_ID)
create index SF_Index1 on SF_SYSOpenItems (SF_Status)
create index SF_Index2 on SF_SYSOpenItems (MatchValue)

Open Items view

When STEPforward creates a table for the Common Template, it also creates a view that joins together SF_SYSOpenItems table with SF_TXDetail, SF_SYSMasterLog, SF_SYSFiscalPeriods, SF_SYSSourceDocument, SF_SYSVGLAccount and SF_SYSTemplates tables into one coherent virtual table as per following example:

create view SF_SYSVOpenItems (SF_ID, SF_TX, SF_Line, SF_Extra, SF_IDG, SF_IDS, SF_Status,
SF_Description, SF_DocReferenceNo, SF_DocumentDate, SF_AccountingPeriod, SF_ExtraTable,
SF_UserId, SF_DateChanged, SF_TimeChanged, SF_Posted, SF_TimeStamp, SF_IdNumber,
SF_SystemState, SF_Period, SF_DocName, SF_DocType, SF_Restatement, SF_GLPrefix, SF_GLNumber,
SF_Subledger, SF_RecordType, SF_MatchValue, SF_MatchStatus, SF_Hidden, SF_Amount, SF_Primary)
as select SF_TXDetail.SF_ID, SF_TXDetail.SF_TX, SF_TXDetail.SF_Line, SF_TXDetail.SF_Extra,
SF_TXDetail.SF_IDG, SF_TXDetail.SF_IDS, SF_TXDetail.SF_Status, SF_TXDetail.SF_Description,
SF_TXDetail.SF_DocReferenceNo, SF_TXDetail.SF_DocumentDate, SF_TXDetail.SF_AccountingPeriod,
SF_ExtraTable = SF_SYSTemplates.TemplateName, SF_UserId = SF_SYSMasterLog.UserId,
SF_DateChanged = SF_SYSMasterLog.SF_DateChanged, SF_TimeChanged =
SF_SYSMasterLog.SF_TimeChanged, SF_Posted = SF_SYSMasterLog.Posted, SF_TimeStamp =
SF_SYSOpenItems.SF_TimeStamp, SF_IdNumber = SF_SYSMasterLog.IdNumber, SF_SystemState =
SF_SYSMasterLog.SystemState, SF_Period = SF_SYSFiscalPeriods.PeriodString, SF_DocName =
SF_SYSSourceDocument.DocName, SF_DocType = SF_SYSSourceDocument.Type, SF_Restatement =
SF_SYSSourceDocument.Subtype, SF_SYSVGLAccount.SF_GLPrefix, SF_SYSVGLAccount.SF_GLNumber,
SF_SYSVGLAccount.SF_Subledger, SF_RecordType = SF_TXDetail.SF_RecordType, SF_MatchValue =
SF_SYSOpenItems.MatchValue, SF_MatchStatus = SF_SYSOpenItems.SF_Status, SF_Hidden =
SF_SYSOpenItems.Hidden, SF_Amount = SF_SYSOpenItems.Amount, SF_Primary =
SF_SYSOpenItems.SF_Primary from SF_TXDetail, SF_SYSMasterLog, SF_SYSFiscalPeriods,
SF_SYSSourceDocument, SF_SYSVGLAccount, SF_SYSTemplates, SF_SYSOpenItems where
SF_TXDetail.SF_TX = SF_SYSMasterLog.SF_TX and SF_TXDetail.SF_AccountingPeriod =
SF_SYSFiscalPeriods.PeriodId and SF_SYSMasterLog.IdNumber
= SF_SYSSourceDocument.IdNumber and SF_TXDetail.SF_IDG = SF_SYSVGLAccount.SF_IDG and
SF_TXDetail.SF_Extra = SF_SYSTemplates.IdNumber and SF_SYSTemplates.Scope = 'T' and
SF_TXDetail.SF_ID = SF_SYSOpenItems.SF_ID

This view, while being somewhat similar to the SF_TXVTransaction view described in the preceding section, differs from it in one important respect: whereas the SF_TXVTransaction view shows the original SF_TXDetail table rows with extended contents, the SF_SYSVOpenItems view shows the Open Items -- that is, splintered parts of the SF_TXDetail table rows (also with the extended contents, like the related anchor records). Thus, if the SF_TXVTransaction view contains three rows that are split into three Open Items each, the SF_SYSVOpenItems view will show 9 rows.