Introduction

CATS is a commodity tracking and reporting system designed to help NDRMC increase traceability and transparency of food commodity movement throughout its supply chain. One of the major design principles of CATS from the very beginning was to implement double-entry accounting pattern at the core of CATS for handling commodity movement transactions. During the initial phase of development, commodity movement at the hubs was the primary focus hence the accounting pattern focused on transactions occurring at the hubs – receiving, dispatch, loss/adjustment, and transfer. Observations and recommendation from the first phase of development are documented on CATS wiki found at https://ndrmcc.atlassian.net/wiki/display/CATS/Transaction+Related+Considerations.

Second phase of CATS development covered upstream processes occurring at federal and regional levels which were primarily planning activities by their nature. At the beginning of the development a review of the transaction pattern was conducted and a comprehensive design pattern to address issues faced during the first phase was drafted to be used as a guideline throughout the development process (https://ndrmcc.atlassian.net/wiki/display/CATS/Accounting+Patterns+in+CATS). After the completion of CATS development, it was rolled out for pilot testing for 18 months using real production data from NDRMC.

A review conducted to assess implementation of transaction pattern in CATS on July 2015 raised concerns on how transactions are implemented and recorded in the database. Incorrect implementation of transactions in CATS means:

• Transaction patterns are at the core of CATS and poor implementation means further development or changes to the software will be difficult; if not impossible; forcing future phases of software development to dictate rewriting of the current system.
• Gaps between the documented transaction design and implemented one means it will be too hard to understand the current codebase and introduce future changes
• Hard-to-trace errors may start to appear in reports since there are gaps between the documented designs and implemented one.

These concerns necessitate a review of existing pattern together with implementation and come up with a revised pattern to address identified issues. This document represents the revised accounting pattern for implementation in CATS v2.

Double-entry transaction pattern

Many computer systems utilizing a relational database contain some sort of accounting information. In the initial design and development stages there is often a debate about what type of accounting strategy should be used by the software to record this information. The decision is usually between a simple and cheap 'single entry' accounting system and a more complex 'double entry' accounting system.
A 'single entry' system is one where the numerical values being stored are only recorded once. In a 'double entry' system each value is stored twice, once as a credit (a positive value), once as a debit (a negative value). There are a number of rules that control these values.

Design Principles

CATS at its core includes an accounting information to trace commodity movement to allow reproduction and generation of reports at any particular point in time. The main rules are as follows:

• Every entry into the system must balance – i.e. the sum of any transaction must be zero.
• The sum of all the values in the system at any one time must be zero (the 'trial balance').
• No values can ever be amended or deleted. They must be negated with an opposing entry (a 'contra') and re-entered ('re-booked'). This provides a completely secure audit trail.

In addition CATS also imposes the following addition requirements for the design of accounting transaction patter.

1. The table will only record data elements that have:
   1. An identifiable Commodity Type (cereal, pulse, etc.)
   2. A corresponding weight value. In previous versions all weight values were expected to use a single measurement unit (mt) but in the current revision of the document it's recommended to use a uniform measurement unit but as long as corresponding measurement unit is specified for the amount then it should allow recording of values in other units too. For simplicity and to avoid unit conversion at the database level it's recommended to use a single measurement unit per category (weight, length or volume)
2. Only activities which trigger physical or virtual movement of commodities are recorded in the transaction table. This means that this version avoids recording planning activities hence they don't qualify as either a physical or virtual commodity movement.
3. Data elements contributing to the CATS KPIs must be included in the transaction table
4. Data elements that require historical-point-in-time reporting should be included
5. Conceptually similar events that result in different legal responsibilities or reporting requirements should be denominated into separate accounts
   1. Adjustments to stock due to losses in the DRMFSS stores and Adjustments to stock due to transporter negligence are conceptually similar: both are stock adjustments. 
   2. However, the legal responsibilities are different, and therefore should be registered in separate accounts
6. Retroactive adjustments to entries in the transaction table will use the difference adjustment accounting method
   1. The original transaction record will remain unchanged, and a new set of records will be inserted into the transaction table. 
   2. The sum of the original records and all related adjustment records will add to the final, revised amount. 
   3. Adjustments are registered in the same accounts the original, and are flagged by the letter 'A' in the sub-account field.

Transaction Activities

Different activities within CATS have a direct or indirect effect on account transactions. These activities are broadly classified as Physical and Planning events. Physical activities are those which trigger physical movement of commodities within the NDRMC supply chain such as receiving, dispatch, transfer, delivery and distribution while planning (virtual) events trigger account transactions with the intention of moving (in or out) commodities which is not reflected physically in the stores. A good example of planning (virtual) event is dispatch allocation which reserves stock within warehouses and affects stock status but do not trigger physical movement of commodities.

CATS identifies the following commodity movement activities within NDRMC.

Data Model

Considering the above principles, the following data model is used to represent double entry transactions in CATS.

Posting

Posting represents the actual double entry transactions. Keeping the figures in one table simplifies calculation of values significantly. Values in this table can never be updated or deleted instead a balancing counter transaction is recorded in the table. Records in this table should also have auto generated globally unique identifier as their primary key to insure no two records have similar ids.
In addition to basic information such as quantity, unit of measure, date; the posting table also keeps attributes such as operation (month/round), donor, warehouse, commodity, FDP, source document etc.

Journal

A journal entry is the data representation of any business transaction that will produce double entries – it represents a complete unit of work i.e. all Posting entries associated with a journal entry must be successfully completed or none must be completed. The numerical sum of all posting entries associated with a journal entry must also equal to zero. The only way the total value contained in the system can be amended is through statistics account (discussed below). Accordingly the following journals are identified for CATS:

Account

Account represents the different states the commodity is currently in. In traditional accounting model accounts represents owner of assets in a system but in CATS this is represented by states such as allocated, delivered, goods in transit, good on hand, distributed etc.

Based on the above model, the following database entity relational model is devised to represent it.

Event types

There are two types of events that trigger transaction entries in CATS namely virtual and physical.

1. Physical: events that trigger physical movement of commodities in NDRMC supply chain. Examples of this event are Receiving, Dispatch, Transfer, Delivery and Distribution.
2. Virtual (also called Planning in previous versions): is an intent to do something on commodities. Example of this event include Donation Plan, Dispatch Allocation, Beginning Inventory etc.

Posting Events

Beginning inventory/Starting balance