Skip to content

MEP 6 - ERP

  • Author: Rodda John
  • Status: Adopted
  • Adopted: 2024-03-27

Overview

An ERP (enterprise resource planning) is a piece of software that tracks the flow of goods, services, and money through a company.

Modern ERPs operate on the principle of double-entry accounting whereby every credit has an associated debit.

A credit or a debit could result in either an increase or a decrease, that follows the following rules:

Account types:

  • Asset accounts
    • Debit: increase
    • Credit: decrease
  • Expense accounts
    • Debit: increase
    • Credit: decrease
  • Liability accounts
    • Debit: decrease
    • Credit: increase
  • Equity accounts
    • Debit: decrease
    • Credit: increase
  • Revenue / Income accounts
    • Debit: decrease
    • Credit: increase

Modeling

Account

  • Name
  • Active (Y/N)
  • Classification (one of the account types above)
  • Type (see this)
  • Sub Type (see this)
  • Account number (alphanumeric, no colons)
  • Parent account (nullable, applicable if this is a sub account)

Journal

  • account_id (which account is the transaction associated with)
  • reference number
  • credit / debit
  • amount
  • Nullable fields
    • location-id
    • customer-id
    • vendor-id
    • order-id
    • asset-id

Journals should follow the following constraint:

SELECT SUM(journals.amount) = 0 FROM journals GROUP BY
journals.reference_number;

A balance check is trivial to run:

SELECT SUM(journals.amount) FROM journals GROUP BY
journals.account_id;

To retrieve more specific information:

  • e.g. sum all journal entries related to asset XYZ in a fixed asset account - accumulate depreciation account

Generating a balance sheet is trivial, simply get a sum of all accounts, display taxonomy

Getting a full report on a customer, vendor, item, order, is trivial as you just filter journals by that and then group by

Editing a journal amounts is normally possible if we're in an "open period" – we don't really have that concept, they're always editable as long as they zero out.

Editing a customer, vendor, order etc, is equally thus very possible whenever.

Integrations

This is already built.

Store an AccountingAccess model that understands how to authenticate and connect to external vendors. Store a RemoteObject model that stores objects in the foreign ERP locally so as to allow FKs. There is a task that syncs this

A customer, vendor, account, asset, order, etc can be linked to a remote object

A journal entry has a sync status column, all journals with the same reference number are synced at the same time.

Unsynced journals are polled and a sync task is persisted to postgres, this allows for rendering errors etc.

Third party limitations:

  • Quickbooks requires a customer for AR postings
  • Quickbooks requires a vendor for AP postings

Syncing

Terminology:

  • "Remote state" the actual remote state
  • "Remote model" the DB tables that track external state
  • "Local model" the DB tables that track Moab's objects

"Mappings"

  • Accounts
  • Customers
  • Vendors

For mappings, on every pull of remote objects:

  • For new objects in remote state not in remote models, create remote models
  • For updates to remote models, update them
  • For remote models without local models, create local models, and link via FK to remote model
  • There are no deletions, they're just marked as inactive
    • If any are marked as inactive, mark the remote model as inactive
    • For remote models marked as inactive, mark the local model as inactive

For mappings, when a new local model is created:

  • Create a remote state, create a remote model, link (important so as to not doubly create)

For mappings, when a local model is updated:

  • Create a queue object to sync state of a local model to the remote state
  • On sync, update the remote state, and update the remote model

"Syncs" Syncing refers to the process of syncing GLs to external ERPs.

For every GL Moab wants to write, create a local model journal

  • Thus, the Moab GL is up to date

There is a batch sync table that displays unsynced GLs in a few different statuses:

  • Missing mapping
    • There is an object this entry depends on (customer, vendor, GL), that doesn't exist on the remote side – this shouldn't really every happen

  • Waiting

    • When the accountant marks this as "ready", it gets picked up in a batch and is synced over

  • If you edit a journal entry, you can edit anything about it, but it must still 0 balance out.