Acronyms

Introduction

CATS is a Commodity and Allocation Tracking System designed to help the DRMFSS add transparency to the food management operations. CATS is a multi phased, application development project which focued on core hub funcationalites in the food supply chain. In the first phase of the project, CATS project started by developing the appropriate interfaces to allow hubs to record Receipts and Issue Transactions.
This document is a brief overview of the transaction system that is serving as a core of the CATS hub transaction management module. This documentation was prepared as part of the phase 1 review process at the beginning of phase two.

Assumptions

The development effort in the first phase of the project has taken quite a number of key assumptions. At the time that this document was composed, a detailed analysis was not performed if the phase one assumptions were still sticking or not. However it is our recommendation that these assumptions get reviewed.
Here is a list of key assumptions that were taken on the development of the first phase of the project.

• The system to be developed needs to work exactly as the DRMFSS's current process. **** It shall not impose process changes.
• Due to different challenges observed at the stores, system should only function at the office level (not at the stores where the actual receive and dispatch takes place) which makes the system data entry clerk driven, rather than a live transactional system that would be a part of day to day activity.
• The key documents that the system would be capturing from are "GRNs" and "GINs" (Goods Receiving Notes and Goods Issuing Notes
• The system is to be designed to only be used by the hubs. (Given the central piece of the development effort is to be undertaken in the next phases of the development, Only hubs have access to the system with the exception of gift certificates)
• There is a possibility that the system could be used by different hub operators like EFSRA
• The system needs to be able to track SI numbers and or any equivalent organization level codes that will allow to trace commodities from receipt to distribution. (Add traceability)

Database Design

Transaction related Tables

The transactions system has various dependencies on lookup tables as well as tables that represent different entities.
In this section, we will be discussing the lookup tables that have a relationship with transactions.

1 Commodities

As a commodity Tracking system, the list of commodities and commodity types come to the center of the whole application. DRMFSS is interested in tracking both food and non food commodities. These commodity types are stored in the "CommodityType" table.
There are two commodity types that are currently supported in the system.

At the very core of it's service, DRMFSS tracks 4 types of food commodities. Non Food commodities could have many different types and the very nature of the non food commodities is not clearly understood as that is not the core of the DRMFSS business. The following list is the core list of food commodities that would be tracked in the CATS.

Commodities are represented in a self referencing, Commodities table. Under each type of food commodities, the system also supports to have a sub type. for example Cereals would have the following sub commodity types.

Note: Though the self referencing design of the commodity table support multiple level commodity classificaiton, CATS only works with 2 levels. Parent Commodity and Sub Commodity. Further commodity classification is not supported.

Relevant ER Diagram

Key Challenge

 
DRMFSS receives commodities by their sub commodity types, however when dispatch is performed, from planning to execution the only documented identifier for commodities is parent commodity and not the sub commodity type. This makes it difficult to record the exact commodities that were dispatched.

Here is an example to illustrate this challenge:

Hub Name Kombolcha

• A 10,000 MT of wheat is sent to Kombocha Hub (A sub type of cereal),
• A 10,000 MT of Rice is sent to Kombocha Hub (A sub type of cereal),
• A Goods Receiving Note is issued to the driver 1 with 10,000MT of Wheat
• A Goods Receiving Note is issued to the driver 2 with 10,000MT of Rice
• A Dispatch order is sent from Addis for 15,000 MT of Cereal (No sub type specified)
• A Goods Issue Note is issued with 15,000 MT of Cereal

Question: Since the recording of the system is based on the goods receiving note and goods issue note, What is the type of the commodity remaining? is it 5000 MT of Wheat? or 5000 MT of Rice?

2 Unit of Measurement

The requirement of the system is that it should be able to support tracking commodities in two units of measure. As the system is mainly designed to track food commodities the first and foremost priority is to support the measurement of commodities based on their weight. The second unit of measure is the count of baggages or packs.

The following list is a list of Units that were identified on the project.

Note that, there are no weight measurement units represented in the Unit table. The reason for this is because weight is primary tracked in a different column than the count of units.

Relevant ER Diagram

Notable Exceptions

1. There is no relationship is being maintained between commodities and Units

Example: One cannot assume that Oil will come in bags, and hence would expect that the interface to not show bags as choices for Oil.
This implementation was deemed out of scope as the relationship between units and commodities were not clearly understood, especially with the non food commodities.

1. There are no conversion Factors have been implemented

Example: In most cases, wheat comes in bags that contain 50 KG. This would mean that if a store has received 20 Bags, it is an equivalent of receiving one metric tonne. However, this assumption is wrong in the sense that there could be shipments of the same commodity, wheat in bags of 48KGs. This had been early identified as a risk to represent the conversion factors and hence made out of scope from the previous implementation.

Key Challenges

DRMFSS, depending on the functioning of the weighing machine, could receive using weight measurement (without counting the Units).
In this instances,

1. the cargo will be weighed on it's way into the compound,
2. it will be passed to the unloading destination (the store) to be unloaded,
3. the store man will unload the bags without counting the number of bags.
4. the cargo is again weighed without the commodities
5. the difference between the loaded truck and the unloaded truck is calculated to find the Weight of the commodities that have been unloaded.
6. An assumption based conversion is made between the # of units and the weight.

No standard bags: In this specific case, what would be recorded as the count of the units is by pure assumption, if it is wheat for example, it would be (# of MT x 20). This assumes that each bag contains 50 Killo gram of wheat. However there are cases when each bag contains only 48 Kg of which. In which case the number of bags that.
Assume for this example 40 MT of Wheat was loaded on the truck with a trailer.

• If 50KG per bag is assumed, it will be 800 Bags in total * This is how much is recorded.

if 48KG per bag is assumed, it will be 830 Bags in total (which is a different number of bags than the one that is recorded.
The challenge is when the commodities are dispatched, the commodities would be dispatched using count of Units. There is no clear policy by which the store men are asked to dispatch commodities that they have received by weight, using the weight.
The system would quickly go out of sync, with the actual count of the units as well as the weight left on ground.
This is the factor that led to a decision that, for Food commodities, the primary unit of Measurement should be weight. And the primary Unit of measurement for non food commodities is the unit count.
Weight, when recorded in the system should be recorded in MT. On Display, However, it could be converted based on user's preference to Quintals.

3 Commodity Source and Commodity Grade

Commodity Sources are indicators of how the commodity came to DRMFSS's possession this is a lookup of all the sources of the commodities.

List of Commodity Sources 

Commodity Grade applies for some commodity types. For example in the case of Wheat, the Shipment is examined on receipt for level of moisture, Once this is measured it could be greaded as Grade 1, Grade 2 etc. 

List of Commodity Grades

Relevant ER Diagram

Key Challenges

The commodity grades are presented as an optional field on the Goods Receiving Note.
Even if the commodities are tracked as different grades when received, there is no way to track which grade of commodity has been issued. The result of this would be unability to report which grade of commodities are remaining in the store.

4 Hubs and Physical Stores

DRMFSS has three distinct hubs to coordinate all it's national relief and safety net programs. Hubs would on the other hand will have different stores within their premises. 

DRMFSS also works with different organizations that also operate hubs. In particular DRMFSS works very closely with WFP and EFSRA. Both WFP and DRMFSS operate Hubs in same cities as the DRMFSS hubs. The relationship of these hubs between the different organization is not fully modeled. However, It appears that they are involved with different scenarios of loans and swaps
In the case of EFSRA, the commodity Tracking System was found to have a potential to be used by the EFSRA counterparts.
Below is a map that shows where the Hubs of DRMFSS are located around the country. 

Please see below where EFSRA Hubs are located below. The green circles represent places where DRMFSS operates Hubs, and the blue circles represent EFSRA operated hubs.

It is fair to assume that all of the EFSRA hubs and stores under their hubs could serve as a temporary DRMFSS store.

{Crazy Note} Even if the EFSRA hubs are far from the DRMFSS counterparts, any operation could utilize those EFSRA hubs as if they are operated by DRMFSS.




Before we discuss about the various challenges that were observed in the first phase of the project, It is important to also take a closer graphic look at how EFSRA and DRMFSS hubs are physically located on one of the locations (Adama)

Note that Both EFSRA and DRMFSS hubs are operated off the same compound in this instance. This could underline how these two agencies operate very closely.


The better way to look at Hubs is to see them as the offices that would process the paperwork for the physical stores with in their premises or within their proximity.

Relevant ER Diagram

Note: Each store will have a number of stacks for the commodities, Only the number of stacks is tracked on the store level, there is no "Stack" table in the database.

List of Hub Owners

List of DRMFSS Hubs

List of Stores under Kombocha DRMFSS Hub. 

Key Challenges

1. Even though there is interest to track the stacks, and the history related with the specific stacks, including the commodities that have come in to that stack, commodities that are currently stored in that stack and it's fumigation history, The key documents of transaction (GIN and GRN) do not have field for it. This means no complete history of the stack could be tracked.
2. Rented out (Temporary Stores do not have a proper name) which means, it is difficult to track temporary stores.
3. There is no clear process to start and end a temporary store, which means there is potential to have an out of sync store data.

Key Commodity Identifiers

SI and Project Codes

[Hail Mary please describe project codes for me].

Transactions

Key Decisions

1. For performance reasons, Keep the transactions Table compact. A key guideline taken in this case is to store only numeric values in the table as much as possible. (Avoid string values)
2. Use double entry accounting to represent the stock movements
3. Use the accounting patterns to only represent actual physical movement of stock, instead of using it to represent both the plan aspect and the actual receipt and issue information.
4. Design the application with the assumption that lack of one part of the workflow does not lock the next person. Example, If the person who does the receipt distribution among the hubs doesn't do the data entry, design the application to allow the person at the hubs to enter his receipts any ways.
5. Design with a partitioned deployment in mind.

• Given the hubs are found at remote locations which do not have reliable internet connection, design the transactional system with the notion that it could be deployed with appropriate partitions. Hence it should be able to support consolidation of the transactions between the hubs.
  Note: Partitions are not equivalent to Hubs. A hub could have 0 or more partitions.

1. Normalize for sanity but denormalize the transactions structure to make reporting easier. (Some data elements could be found redundant)
2. Keep header information out of the transactions structure and create an appropriate linkage between the transactions entries. Everything that describes the physical movement of the commodities should be put in header tables. Example: The Driver Name, The transporter name, Who the receiving Store Man was etc should be placed in the Receipt Header Table, Only the quantity aspect and the key Identifiers should be represented in the Transactions table.

ER Diagram of the core transaction table.

The following diagram shows a quick overview of the transactions table and all the associations that it has with the lookup tables. Follow up sections will illustrate different concepts that were incorporated in the transactions table design.

Note: Because of lack of paper information that supports the Stack and Commodity Grade, those two fields are optional. The sub commodity however is not optional. Even though the field was not optional, in cases where that information is not filled, it contains the ID of the ParentCommodity ID

Double Entry and the "Control Table"

The implementation of the double entry system requires that any transaction have a positive side and a negative side. At the end of the transaction the sum of the positive and the negative part should result 0.
This could be implemented in two different ways on a database table.
1: maintain two different columns for the positive columns and negative columns
2: maintain two different rows for the different accounting entries and link those by using a control number.
We have chosen to use the second option, which was to have a control number to link two rows in the transactions table. The TransactionGroup table is for recording the control number.

Compact Transactions Table

One key decision that was takens during the design of the transactions table is to keep the transactions table compact. To acheve this, we had taken the route of making most of the fields numeric data types, Hence avoiding text data elements. This helps avoid text comparison operations out of the transactions table.
There are two optimization steps that were taken to make sure that only numberic values were stored in the database.
1. Transaction Double Time Stamping: The requirment of the system was such that two particular time stamps tracked. The time where the transaction was recorded, as well as the effective date of the transaction. In most business reporting requirements, the date that would be used is the effective transaction date. Hence the Transactions table will only be storing the Effective Transaction date. However, the Header tables would be able to track the transaction recording date.
2. Text identifiers SI and Project code were placed in separate tables and references were added to the transactions table. See the related ER diagram below.

Header vs Transaction

Some of the data that is recorded on the key documents(GIN and GRN) describes "How"and the commodity is moving, on the other hand, you would also be able to find the key identifiers of the commodity that is moving on the same documents. A key distinction is made between the Who and the How.
All data elements that describe the commodity moved should be tracked on the header table for receive, and on the other hand, all data points that track the what and the key identifiers for the commodity should be stored in the transactions table.
[Elaborate More]

Receive Event

A receipt event happens when a truck shows up at the DRMFSS store. Upon unloading it's load, the store manager will issue a goods receiving note to the driver. The goods receiving note is then sent to the Hub office the next morning. The hub data operator will then record it to the CATS. Note that there could be an additional time delay in the mix if the store is physically far away from the hub. this delay could be as far as 6 months down the road.

Dispatch Event

A dispatch event happens when commodities are released out of the hub. commodities could be dispatched to either FDPs or Other Hubs. When commodities are dispatched from store, a truck loads the commodities based on a loading order. Up on loading the trucks, the store manager will write out a Goods Issue Note and asks the driver to sign it. The store manager then would compile and send all the GINs the next morning. The Hub data operator will then Key the GINs to the CATS. This one day delay could be up to 6 months depending on the distance of the physical store from the hub office.

Adjustment Event

An adjustment event happens when an error is found in the data entry or whenever a physical count confirms that there is an error in the balance reported by the system.

Swaps, Loans and Repayments as sources of commodities

Unlearning what we knew about loans:
Google definition of a "Loan": A thing that is borrowed, esp. a sum of money that is expected to be paid back with interest.
Definition of Loan in the DRMFSS context is something taken from WFP or EFSRA (mostly EFSRA) but the is never repaid. If the loan is repaid, the payee is going to be either the WFP or MOFED (In the case of PSNP Program)
The challenge with this is that the repayment pieces that happens between the guarantee(WFP or MoFED) does not trigger any process in the DRMFSS hubs. Hence the repayments are virtually invisible to the DRMFSS hubs.
Internally, the accounts that would be used to identify the commodities received via loan would keep accumilating rendering the reporting useless in a couple of rounds of loans and swaps.
The Loans and Swaps therefore were treated as "sources of commodities" in the receiving process. CATS would only record the physical movement of the commodities in these specific cases as :"Receipts and Issues"

Desired deployment option

Due to connectivity limits, or unreliable connectivity between the hubs and center, the fail safe deployment option is to deploy the system with appropriate database partitioning. Hence, the transactions could be synchronized with the centeral server whenever there is connectivity.
To Support this. the following design decisions have been taken.
[Add a visualization here]

GUID on Key Transaction Table Unique Keys

GUID is microsoft implementation of Globally Unique Identifiers. Two different databases servers configured to generate GUIDs are highly unlikely to generate similar GUIDs. Using GUIDs therefore minimizes the chance of collisions.

Partitioning & Transaction tables

Both header and transaction tables contain the partition ID, the partition ID in transaction these tables will identify which partition inserted that record.

Accounts for Different Entities

On any given transaction, a reference to one of the followin entities is required

Transaction Name: Entity Type
Receive - > Donors
Dispatch -> Donors
Dispatch -> FDP
Loan -> Source Hub Owner
Re-Payment (Dispatch) Destination Hub Owner

If we are to put different columns for each of these Entities, we would end up with many null columns for each transaction, an alternative design approach is taken to accommodate different entities. An Accounts Table. The accounts table would contain a globally unique identifier for each of the entries in the entities table of interest.

The following diagram will show Sample data from the accounts table.

Key challenges

This approach does not fully take into account the fact that these accounts could be created in different partitions. It's capability to support the partitioning scheme needs to be further investigated and studied.

Recording "Planning Events" in the Transaction Tables (or Not)

One of the major decision points in the design of the CATS

Key Considerations

The initial design of the transaction system considered recording the net effects of the planning activities around the commodity movements. An example of this design was illustrated in the list of chart of accounts and the sample transactions presented below. A further investigation into how much of the planning process could be captured and considered reliable was in question. This led to the discussion and key decision that the planning processes should be captured and stored independently of the transactions sub system.
A sample list of these planning processes could start from the receipt of Gift Certificates.
WFP, up on shipping commodities to Ethiopia, would issue a gift certificate to DRMFSS. The reason that this gift certificate is issued to DRMFSS is for DRMFSS to issue letter of support for the ministry of finance so that the commodities would be imported without Tax. The challenge with capturing this is that the gift certificate is an artifact that would only be found when WFP is involved in the donation. Not all donors would send gift certificates, and even if they send gift certificates, not all data elements in the WFP gift certificate could be found on the other donors gift certificates.
In WFP/DRMFSS, [The person we couldn't spot in 4 months] does receipt allocation based on the gift certificate. The receipt allocation is a division of the shipment to be delivered to the different hubs and warehouses. This allocation is communicated with the DRMFSS Center (Addis Ababa).
Notice that these planning stages will be passed only when the donor is WFP and even in the case of the WFP, it is not clear how much of this is communicated with the Hubs. It is this factor that led to the decision that the design should consider plans differently from the transactions table. Instead of using Leger accounts for the plans, consider using a different table structure for plans.
The following diagrams show the database structure that is in place so far to track the different levels of receipt planning and

Receipt Planning Events

Ideally, there are two receipt planning event. The first one is when a gift certificate has been sent from WFP to DRMFSS. The gift certificate would contain the details of the commodities that would be expected to arrive on port. The second planning event is when a receipt allocation is produced, which details which hubs would receive how much.
The following diagram shows how the two receipt planning events are represented and how the planning records are related with the actual receipt.

Dispatch Planning Events

In practice there are a lot of dispatch planning events that happen at the central DRMFSS office here in Addis Ababa. These events include the

1. Recording of RRD (Relief Requirement Document) - Done in Addis
2. Allocation of RRDs to specific hubs, - Done in Addis
3. Start of Transport Tendering - Done in Addis
4. Transport Contracting ( End of Transport Tendering ) - Done in Addis
5. Passing the Winners List to the Hubs - Done in Addis

The focus of the first phase of the CATS project was to only have screens to support the hubs. Therefore the planning events identified from 1 to 4 were automatically out of the first phases scope. The dispatch planning in the current implementation therefore started from event # 5
Please see below how the dispatch plans were represented in the current database design.
Note that dispatch planes could be for dispatching to FDPs or the plan could be to Dispatching for other parties like giving loan to WFP, Transferring to other hubs etc. In the later cases, there is a separate structure to track dispatch plan.


Dispatch planning events for FDP


Dispatch plan for non FDPs

Error Corrections

It is inevitable that data entry errors will occur, when using the accounting pattern, there are three alternatives as to how to record the event through which a data entry error has been discovered.

1. 1. Events are immutable (cannot be changed) if the error is detected, that should appear as a new event and have it's own entries. (only the adjustment piece should be inserted in the database)
   2. Reverse the previous events and go for the new (reverse the entry and insert a new correct entry)
   3. Edit the entries (which was a no discouraged)

On the current implementation of the system, the only supported error correction mechanism is Adjustment. This is a close match to the option A, however, it had gotten little acceptance from the users. The balance would be correct, however users do not like to see eronus entry at any point. The users would like to be able to erase the errors just like editing a row of data on Excel.

Key Accounts / Ledgers

The very first activity we had undertaken to implement the accounting pattern in this project was identifying the list of Accounts that were to be used in the DRMFSS context. A good point of reference for us was to start to visualize the accounts in the sense of Accounting Terminologies.
Consider the following basic Accounting equation, Asset = Liability + Owner's Equity. When identifying the key accounts that would be used in the DRMFSS context, we have tried to classify the accounts identified as key Assets, Liabilities or Owner's equity.
In the system, we have named the category of accounts, ledger types and hence stored in the "LedgerType" table. The actual accounts that would be used in the system are represented as Ledgers and hence found in the "Ledger" table.

Note: The following chart of accounts lists all of the core ledger accounts in the system, which includes plans as well as actual stock movements. In previous section, you have seen that there is a specific discussion on tracking the plans in a different structure than having to track them using the accounting pattern.

Though the design has tried to take into consideration possible accounts to track planning events, the system is currently using the accounts that are in Bold. Mainly, these accounts are non planning events.

Scenarios & Their Corresponding Entries

Recording of the scenarios in the database is as follows.
[To Complete Here]



Personal Notes

After reading the "Accounting Patterns" paper from Martin Fowler,
{+}http://martinfowler.com/apsupp/accounting.pdf+
Using the right terminologies in the system matters. Terminologies shape how we think and I think there were some missing terminologies which I found changed my perspective of how I see the accounting pattern.
Modeling the system around Events. when designing the system, "I" have never used the term "Event" to identify what we are trying to represent. The understanding was from a different perspective. I would recommend modeling the system around Events that are supported and events that are not supported.
Posting Rules: there was no identification of posting rules in the current system, let alone tracking the effective dates of posting rules.