Summary Table
General structure
Transaction Summary table SF_TXSummary is used to summarize accounting information by GL accounts, fiscal periods and system states of posted transaction documents.
SF_TXSummary has the following structure:
Information on the account triggers is stored in table SF_SYSSpecialGL of the following structure:
Column | Generalized data type | Use |
SF_IDG | INT | Internal number of GL account |
SF_IDS | INT | Internal number of Subledger account |
SF_CreditAmount | MONEY | Amount of credit for the account |
SF_DebitAmount | MONEY | Amount of debit for the account |
SF_TotalSinceInception | MONEY | Grand total since beginning of times |
SF_AnnualTotal | MONEY | Total for the fiscal year |
SF_AccountingPeriod | INT | Internal number of the fiscal period |
SF_SystemState | SMALLINT | System state |
SF_DocType | CHAR(1) | Document type |
SF_Restatement | CHAR(1) | Document restatement flag |
SF_TimeStamp | SMALLINT | Version of the row |
Notes:
The table contains one row for each combination of GL/Subledger account, accounting period, system state, document type and restatement flag.
SF_AccountingPeriod is the internal id number of the fiscal period. It is taken from the PeriodId column of SF_SYSFiscalPeriods table.
SF_SystemState is the system state number that existed when the row was inserted in the Summary table. STEPforward will keep updating this row until the system state changes, at which point a new row will be inserted in the table with the next posting.
SF_DocType is the type of transaction document as in Type column of SF_SYSSourceDocument table (A fot actual, B for budget). STEPforward keeps separate summaries for these types of documents.
SF_Restatement is the subtype of transaction document as in Subtype column of SF_SYSSourceDocument table (R fot restatement, blank for all others). STEPforward keeps separate summaries for these types of documents.
The following is a partial sample of contents of SF_TXSummary table (note that in order to conserve space the original column names have been changed as follows: SF_CreditAmount = Credit, SF_DebitAmount = Debit, SF_TotalSinceInception = TSI, SF_AnnualTotal = Annual, SF_AccountingPeriod = Period, SF_SystemState = State):
SF_IDG SF_IDS Credit Debit TSI Annual Period State
---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
1 0 0.00 1500.00 1500.00 1500.00 1 1
2 0 0.00 20000.00 20000.00 20000.00 0 1
2 0 -10067.56 13166.00 23098.44 3098.44 1 1
3 0 0.00 200000.00 200000.00 200000.00 0 1
4 0 0.00 60000.00 60000.00 60000.00 0 1
5 0 0.00 99.99 99.99 99.99 0 3
5 0 -12300.00 21600.00 9300.00 9300.00 1 1
5 1 0.00 99.99 99.99 99.99 0 3
5 1 -3600.00 4500.00 900.00 900.00 1 1
5 2 -2700.00 3600.00 900.00 900.00 1 1
5 3 -2000.00 3600.00 1600.00 1600.00 1 1
5 4 -2000.00 2700.00 700.00 700.00 1 1
5 5 -2000.00 7200.00 5200.00 5200.00 1 1
7 0 0.00 8000.00 8000.00 8000.00 1 1
8 0 -15.91 1456.00 1440.09 1440.09 1 1
8 0 -115.37 0.00 1324.72 1324.72 2 1
8 0 -123.32 0.00 1201.40 1201.40 3 1
8 0 -119.34 0.00 1082.06 1082.06 4 1
8 0 -123.32 0.00 958.74 958.74 5 1
8 0 -119.34 0.00 839.40 839.40 6 1
8 0 -123.32 0.00 716.08 716.08 7 1
8 0 -123.32 0.00 592.76 592.76 8 1
8 0 -119.34 0.00 473.42 473.42 9 1
8 0 -123.32 0.00 350.10 350.10 10 1
8 0 -119.34 0.00 230.76 230.76 11 1
8 0 -123.32 0.00 107.44 107.44 12 1
8 0 -107.44 0.00 0.00 -107.44 13 1
10 0 0.00 750000.00 750000.00 750000.00 0 1
20 0 -14525.58 0.00 -14525.58 -14525.58 1 1
20 1 -79.50 0.00 -79.50 -79.50 1 1
20 4 -400.00 0.00 -400.00 -400.00 1 1
20 5 -2750.00 0.00 -2750.00 -2750.00 1 1
20 6 -569.50 0.00 -569.50 -569.50 1 1
20 7 -4500.00 0.00 -4500.00 -4500.00 1 1
20 8 -1456.00 0.00 -1456.00 -1456.00 1 1
20 9 -112.62 0.00 -112.62 -112.62 1 1
20 10 -3000.00 0.00 -3000.00 -3000.00 1 1
20 11 -1657.96 0.00 -1657.96 -1657.96 1 1
29 0 -692300.00 0.00 -692300.00 -692300.00 0 1
29 0 0.00 0.00 -692300.00 0.00 1 1
29 0 0.00 2750.00 2750.00 2750.00 1 2
Updating the Summary table
The process of adding new records or updating the existing ones in SF_TXSummary table is governed by the following rules (note that for simplicity both SF_CreditAmount and SF_DebitAmount columns are referred to as "Amount". Exactly which column is used in the updates depends on the arithmetic sign of the amount).
1. | Posting into existing slot (update) |
In this mode a new Amount value is added to the existing open Period/State row of the table. By definition, this can only be the latest System State of the Period, since all previous states are not updatable. The new values is:
a). | added to all values in SF_TotalSinceInception column for all Period/State combinations of the current account starting with the next period, system state 1. The process stops at the end of the table (or, more precisely, at the last available Period/State slot of the current account). | |
b). | added to all values in SF_AnnualTotal column for all Period/State combinations of the current account starting with the next period, system state 1. The process stops at the end of the fiscal year to which the updated Period belongs. |
2. | Posting into new slot (insert) |
In this mode a new Period/State slot is created in the table. There are two possible variations in this case:
A. | The slot is created for the new Period. System state in this case is automatically set to the current state as recorded in table SF_SYSSystemState. Amount column is set to the inserted value. Then the following steps are performed: |
a). | SF_TotalSinceInception is calculated as a sum of the current amount and the SF_TotalSinceInception from the latest System State of the Period immediately prior to the added one. | |
b). | If the Period is 1 (beginning of the fiscal year), SF_AnnualTotal column is set to the current amount. |
if the Period is not 1, SF_AnnualTotal column is calculated as a sum of the current amount and the SF_AnnualTotal from the latest System State of the Period immediately prior to the added one.
c). | The current amount is added to all values in SF_TotalSinceInception column for all Period/State combinations of the current account starting with the next period, system state 1. The process stops at the end of the table (or, more precisely, at the last available Period/State slot of the current account). | |
d). | The current amount is added to all values in SF_AnnualTotal column for all Period/State combinations of the current account starting with the next period, state 1. The process stops at the end of the fiscal year to which the updated Period belongs. |
B. | The slot is created for the existing Period, but new (next) System state. Amount column is set to the inserted value. Then the following steps are performed: |
a). | A difference between the current amount and the amount recorded in the most recent (highest) system state of the current Period is calculated. This difference, called amount increment, is used in all subsequent calculations. | |
b). | SF_TotalSinceInception is calculated as a sum of the amount increment and the SF_TotalSinceInception from the latest Syatem State of the current Period. | |
c). | If the Period is 1 (beginning of the fiscal year), SF_AnnualTotal column is set to the current amount. |
if the Period is not 1, SF_AnnualTotal column is calculated as a sum of the amount increment and the SF_AnnualTotal from the latest system state of the current Period.
d). | The amount increment is added to all values in SF_TotalSinceInception column for all Period/State combinations of the current account starting with the next period, state 1. The process stops at the end of the table (or, more precisely, at the last available Period/State slot of the current account). | |
e). | The amount increment is added to all values in SF_AnnualTotal column for all Period/State combinations of the current account starting with the next period, state 1. The process stops at the end of the fiscal year to which the updated Period belongs. |