The Database
The MARS database consists of three components. In order to explain these components, this chapter covers the following topics:
The discussions in this chapter revolve around the three components defined below. In addition, reconciliation between tables and ledgers, as well as management responsibilities toward MARS are also addressed.
Master tables are randomly accessible tables of information. They provide MARS with data for validating documents for a specific installation; for example, the installation's chart of accounts and system options.
Two types of master tables exist in MARS ADVANTAGE:
As you might expect, user-maintained tables are maintained by users and are referenced by MARS ADVANTAGE. For example, you add information to the Chart of Accounts and system control tables and MARS references your selections. Any changes to these tables must be made by a user.
System-maintained tables, on the other hand, are updated automatically by MARS as a result of processing documents. These tables contain data such as account balances and open items, and are not updated manually by users.
As described in the following paragraphs, master tables provide MARS with a central source of information that can be used during processing and reporting.
- Validation during processing. MARS compares all transaction codes against master tables. Any code used, for example, a Fund code, must be defined in Fund (FUN2). If a code is not listed in the appropriate window, MARS rejects the transaction and generates an error message stating that the code is invalid; for example, "Invalid Fund Code."
- Inferring default codes during processing. MARS uses master tables to obtain information not entered on the document window but needed for processing. For example, the Activity code defaults from its associated Organization code if Activity is not entered on the document.
- Processing options and controls. During processing, certain points exist where MARS has a choice of two or more ways to process a document. When this occurs, the system refers to master tables containing the processing options selected by the installation. For example, a table specifies whether an Organization is entered on accounting transactions for a given fund/agency.
- Name inference for reports. MARS uses master tables to look up code names or descriptions for use on reports. This makes reports easier to read and more meaningful than if they consisted only of a series of codes.
- Reporting hierarchies. MARS looks up code hierarchies from tables and uses this information to group data on reports. For example, Class and Category fields are not entered on transaction windows, but on reports any code belonging to a class or category is printed under the appropriate class or category heading.
- System-maintained data. MARS automatically updates account balances, open items, and other information in appropriate tables.
Additionally, master tables contain valuable information for users. Various levels of users need to access master table information because:
- Individuals need to know valid codes in the account code structure when coding transaction documents.
- Open items information may be required by billing and accounts payable staff.
- Various offices and managers use the data stored in the budget tables.
- Many tables contain miscellaneous information solely for user reference purposes, such as 1099 and minority vendor indicators in Vendor (VEN2).
In master tables, information is stored in lines, with all related information on one line. Information within a line is divided into fields. Special fields, called key fields, identify a line and distinguish lines from each other. A table can have more than one key field, but all the key field values for a line are combined to get a unique identification for that line. Therefore, two lines in a table cannot have identical values in all their key fields. For example, a vendor is uniquely identified in the vendor table by its Vendor code. To retrieve a specific record, the user must know its key; for example, to look up a vendor's name and address you must know the vendor's code. All information in a table line that is not a key is referred to as result data.
The Agency (AGC2) and Activity (ACT2) windows illustrate two different master tables. Agency (AGC2), shown in Figure 3, lists valid Agency codes for each fiscal year and the associated agency and manager names. Agency (AGC2) must include one line per fiscal year for each agency at the site. MARS uses Agency (AGC2) to check the Agency codes entered in documents and to infer the agency names on reports.
Activity (ACT2), shown in Figure 4, includes columns used to look up hierarchies for reports. Supplied only with an Activity code, MARS can place that activity under the proper activity class, category, group, and type when it prints reports.
Open Purchase Order Header Inquiry (OPOH), shown in Figure 5, is an example of a system-maintained table. It contains a list of currently open (unliquidated) purchase orders and information about each purchase order. This table is updated automatically each time a new purchase order transaction is accepted into the system and each time an existing purchase order is referenced. Later, MARS uses this table to ensure that the purchase order numbers referenced on payment vouchers are valid.
Open Purchase Order Header Inquiry (OPOH)
Master table maintenance includes adding, changing, or deleting a line in a master table. When new codes are added to the chart of accounts, new lines are needed (for example, a new Fund code, Vendor code, Object code). A new code cannot be used in transactions until it is added to the appropriate master table. Obsolete or erroneous codes should be deleted as necessary. Changing a line means changing the non-key fields (for example, changing a vendor address). To change a key field, the user must actually delete the old line and add the new line. A separate maintenance window exists for each master table.
Many of the codes in MARS are optional; if, for example, your installation does not use the activity group, this table will be empty.
In MARS ADVANTAGE, ledgers store data entered into the system by transactions. The data stored on ledgers includes the account code structure and other informational type data, for example, descriptions, as well as the amounts with the appropriate Debit/Credit code. The information stored in ledgers is used to generate most reports. Any reports, standard or custom, that show data by accounting period use the ledgers as input.
Many ledgers exist in MARS ADVANTAGE, each one serving a different purpose and collecting data from various sources. Some ledgers are updated daily, as new transactions are accepted by MARS ADVANTAGE. These detailed ledgers serve as the detailed audit trail of the system. These ledgers are high-volume and grow quickly in size; therefore, they should be summarized periodically (at period closing) into more manageable levels (summary ledgers). Summary ledgers contain all codes in the account code structure, so no data is lost for management reporting or accounting purposes. Individual transaction detail is not distinguishable on summary ledgers.
When a transaction is summarized into a summary ledger, it is removed from the detail ledger and placed in the closed ledger. Thus, summary ledgers contain data for an entire fiscal year, while detail ledgers contain data only for accounting periods. Detail level reports, for example, line-by-line transaction listings are easily obtainable for open accounting periods. However, it is almost impossible to get detailed reports for closed accounting periods. This historical data is usually stored on offline storage media (tape); therefore, advanced notice is required to the department before a detailed report on a closed accounting period can be expected.
The following sections describe MARS ledgers. A detailed discussion of the type of information stored in each ledger is located in the System Administration Guide .
The detail ledgers contain detail transaction data for all open accounting periods. Monthly closing deletes records from the detail ledgers and writes them to the appropriate detail history ledger. The detail ledgers and their purposes are listed in the table below.
Summary ledgers contain one record per accounting distribution, reflecting all activity for closed periods. Monthly closing summarizes the data on the detail ledgers and updates these summary ledgers accordingly.
Open item ledgers contain all open items (regardless of age) and those items closed between the current and one preceding accounting period. (Open items stay in ledgers for one accounting period following the period when they were closed.)
The open item ledgers contain the following types of records:
- Original open item transactions
- Succeeding documents that reference the open item (the Open Purchase Order Ledger contains payment voucher documents that reference purchase orders)
- Modifications made to the above transactions
The match key field on these ledger records is the Document Number of the open item document. (The match key of the succeeding documents is the Document Number of the referenced open item.) Reporting programs and other programs can use this match key field to make a set of transactions that are associated with each other. Summarizations can be made from the set, or a sorted list of detailed transactions can also be produced.
Open item detail is stored on ledgers so that open item and aging reports can be generated. The information in the open item master tables is a summarization of the information in the ledgers. The same data is kept in master tables so users can access it online. The open item ledgers and their purposes are listed in the table below.
The open item ledgers are purged by the Monthly Ledgers Clearing (AFINMCCL) program. The open item tables are purged by Monthly Table Clearing (AFINMCCT) program.
Transaction information relevant to grants and projects is also maintained in separate ledgers. The ledgers and their purposes are listed in the table which follows.
The history ledgers are usually stored on offline storage media (tapes or cartridges). They are the detail ledger files for closed accounting periods, closed open items, and closed years. The following history files constitute the historical audit trail required by accepted accounting procedures.
MARS provides the following online ledgers.
Document Listing (SUSF) Window
The Document Listing (SUSF) window is a "holding file" for transactions. The Document Listing (SUSF) provides the following features:
- It stores batches and documents from the time they are entered until they are processed.
- It stores rejected batches and documents without affecting master tables or ledgers until the errors are corrected.
- It stores batches and documents requiring approvals.
- Rejected batches and documents are accessed from the suspense file, corrected or approved online, and resubmitted for processing.
- Accepted documents are accessed from the Document Listing (SUSF), for a limited number of days after acceptance, for browsing purposes. The specific number of days that accepted documents remain on the Document Listing (SUSF) is controlled by a system parameter that can be different for each installation.
The Document Listing (SUSF) acts as a temporary storage facility, allowing users to view the batches and documents stored in the suspense file prior to running the Archive (AFINARCH) program. Once the Archive program is run, all batches and documents labelled as accepted ("ACCPT") or deleted ("DELET") are moved from the Document Listing (SUSF) to an offline archive tape and are no longer accessible online.
The Archive program is run by the computer operations personnel, on a regularly scheduled basis.
A parameter provided in the Archive program specifies the number of days that accepted batches and documents are allowed to remain on the Document Listing (SUSF). The program is delivered with this parameter set to 00005 and this designates that accepted batches and documents remain on the Document Listing (SUSF) for five days. On the 6th day, they will be deleted (if the Archive program is run). Archive also generates a list of all data that was archived.
Reconciliation between Tables and Ledgers
Summary balances for revenue, expenditure, and balance sheet accounts are maintained in the following MARS tables:
These summary balances are supported by detail in the ledger files. An audit trail of account activity is determined from the following ledgers:
- Current Detail General Ledger
- Current Detail Budget Ledger
- Year-to-Date General Ledger
- Year-to-Date Budget Ledger
MARS is designed to achieve complete integrity between ledgers and tables, so that when the detail in the ledgers is summarized, the resulting account balances equal the account balances stored in the master tables.
System crashes can occur on any computer hardware. Occasionally, operational errors in the restore and recovery procedures following a system crash cause balance discrepancies. Discrepancies may occur, for example, if the backups used were not synchronized.
The problem may be further complicated because the tables and the ledgers are updated at different times. Problems are likely to occur in the first months of live operation, when operators are unfamiliar with MARS backup, recovery, and restore procedures.
In response to the occurrences mentioned above, MARS contains Systems Assurance Process programs designed to help maintain data integrity on a daily, monthly, and annual basis. The Systems Assurance Process verifies that:
- Debits and credits are equal for each fund.
- All processed transactions have been recorded in the General Ledger, Budget Ledger, and the appropriate online tables.
- Organizational structure is accurately specified for each organization.
- Base table records are in sync with their associated alternative views.
In addition, problems occurring due to system crashes or operational errors will be detected and reported by the System Assurance Process. Systems Assurance (SA1 only to balance debits and credits in ledgers) can also be run against monthly and year-end functions, allowing each site flexibility to establish its own systems assurance procedures.
The following jobs are related to the Systems Assurance Process. A brief description for each is provided below.:
More information is provided about selected programs in the sections that follow.
Detailed General (Budget) Ledger Internal Consistency Report (AFINSA1) runs the SA1 program to examine the Daily General Ledger and the Daily Budget Ledger and to report any out of balance condition. If all daily transactions have posted correctly to the ledger, ledger debits and credit will be equal.
The SA1 program looks for balancing entries within accounting period, fund, and document ID. If a site is using summary ledgers rather than detailed ledgers (and therefore is running AFINBSPT rather than the AFINSPLT to update ledgers), the SA1 program will balance within fund. Also note that if a site is posting summarized entries out of automated disbursements, AD documents will be summarized by fund, rather than by document ID.
Out-of-Sync Listing (AFINSA3) runs the SA3 program to compare account balances, as stored in your Financial database, against the account balances as stored in the Daily General Ledger and the Daily Budget Ledger. Specifically, the following balances are verified:
- Current modified appropriation amount, pre-encumbered amount, encumbered amount, and expended amount on the appropriation table.
- Balance Sheet Account Balance on the balance sheet account balance table.
- Current modified budgeted amount, pre-encumbered amount, encumbered amount, and expended amount on the expense budget table.
- Current modified budgeted amount and recognized amount on the revenue budget table.
The SA3 program produces a report showing only the out-of-sync items. Every entry on each of the four tables is checked regardless of whether transactions were posted to a particular table entry that day.
Updating Begin Day Amounts (AFINSAJ) executes the SAJ program to update begin day amounts with current amounts. This job is run after ledger vs. table imbalances are successfully reconciled. Specifically, the following amounts are used in updating:
- Account balance is used to update begin day account balance on the balance sheet account balance table.
- Recognized amount is used to update begin day recognized amount, and current mod budgeted amount is used to update begin day current and budgeted amount on the revenue budget table.
- Current mod budgeted amount, pre-encumbered amount, pre-encumbered amount, encumbered amount, and expended amount are used to update their corresponding begin day amounts on the expense budget table.
- Current mod appropriation amount, pre-encumbered amount, encumbered amount, and expended amount are used to update their corresponding begin day amounts on the appropriation table.
After SAJ runs, all columns described above should have begin day amounts equaling current amounts.
This section outlines the areas that an installation's financial management should become involved with to ensure that MARS effectively meets the accounting, budgeting, and reporting needs of the user. These responsibilities include the following suggestions:
- Define control options and coding requirements. MARS offers many control options and other features that involve management level decisions on desired accounting procedures, controls to be imposed, and transaction coding requirements. These choices must be made before can function. See System Controls and Options for a discussion of these choices.
- Establish guidelines (online installations) for the security and approval functions; for example, establish guidelines describing which offices or individuals are allowed to perform various online functions.
- Decide whether the online version of the General Ledger should be maintained at your installation. Also, decide when the Online General Ledger should be summarized.
- Decide whether the Expense Budget Detail (Extended) (EEXD) should be maintained at your installation.
- Decide whether your operation's personnel should run the BSSPLIT program or the RUNSPLIT program during daily nightly cycle processing. RUNSPLIT writes all records generated by the document processors to the General Ledger. BSSPLIT summarizes some of the offset records and reduces the size of the General Ledger. Your decision should be based on the level of accounting detail desired for reporting purposes and the amount of disk space available for the Current Detail General Ledger (GENLED). Once a decision is made, the same program should be run consistently each day.
BSSPLIT summarizes only offset records containing the following accounts:
These records are summarized to the following level:
- Transaction Code
- Date of Record
- System Acceptance Date
- Accounting Period
- Budget Fiscal Year
- Fund
- Agency
- Balance Sheet Account
- Account Type
- Decide whether the open item ledgers will be maintained at your installation. These ledgers contain the detail transactions supporting the information in the open item master tables. Your decision about whether to maintain open item ledgers should be based on your reporting requirements. MARS standard open item reports, such as the Purchase Order Activity Detail Listing by PO Report (A640) and the Aged Open Purchase Order by Vendor and PO Report (A641), require the open item ledgers.
- Decide whether to summarize automated disbursements. (See System Controls and Options.)
To ensure that MARS is operating effectively, the following procedures should be implemented:
- Appoint offices or individuals to be responsible for keeping the chart of accounts, stored in master tables, current and complete. Encourage the use of meaningful name fields in master tables.
- Appoint an individual to be responsible for deciding when to request period closing. Encourage clearing and closing procedures shortly after the end of an accounting period.
- If the budgetary module is used, decide when to set the Budget Approved Indicator on Fiscal Year (FSYR) to Y .
- Ensure that procedures for taking timely backups of all the data files are established by a System Administrator. Also ensure that procedures exist to catalog history ledger files (usually on offline storage) so they can be found if needed for auditing purposes.
- Recommend communication procedures between data entry personnel and the individuals who code documents for the purpose of resolving errors in rejected transactions.
The following activities are suggested:
- Initiate annual closing and new year initialization.
- Analyze control options and coding requirements. All control options, optional features, and coding requirements may be changed at the start of a new fiscal year. Because of newly evolved requirements, management may want to review the previous year's choices before making changes.
- Request that action be taken on outstanding purchase orders.
Proper file backups on a daily basis are absolutely essential for uninterrupted and accurate operations. Backup recommendations are included in the System Administration Guide , but each installation must establish detailed procedures that conform to its operating environment. These procedures should be established and approved during the installation phase, and be ready for implementation on the first day of live system operation.