Overview

Overview

The Jumpmind Commerce (JMC) application requires some core data tables to be populated in order for functionality within the POS to work properly. Integrations must be developed that can load these required tables, they can utilize any desired integration tool platform as long as it can communicate with the source systems and JMC central office database platform chosen.

Modules

JMC modules represent different areas within the application. Each area has a prefix that associates related tables to it (i.e. prm_, ctx_, sls_, etc.) The modules each contain many tables that make up the entire area but only a few are required to be integrated into. Some tables are also loaded via the configuration and are not loaded using a regular scheduled integration.

Context (ctx_)

Context from an integration perspective represents the data associated with a business unit. Tables like address, business unit, phone, country, state,etc.

Other tables in this area provide additional configuration settings like buttons, disposition codes, reason codes, prompts, general config… these are typically loaded via configuration files or are not required and not used.

Item (itm_)

The Item module contains details aboutthe merchandise, things that can be transacted in the POS, including hierarchy, images, product details, prices and rules. It also includes basic coupon details however not the rules associated.

Promotion (prm_)

Promotion specifies the discounts or deals that are set up associated with items or groups of items or coupons. Promotion start/stop dates, qualifying and reward items along with the rules to meet the criteria as well as the available reward to receive.

There are two required promotion tables that are set up typically through configuration that are related to the promotion types and rules for how promotions stack with one another; prm_promotion_type and prm_stacking_rule. These tables don’t change regularly thus are not typically part of a regular integration.

Tax (tax_)

In order for JMC to calculate tax in a transaction the tax tables must be populated. These tables have the authorities, jurisdictions, groupings, rates and rules that can be associated with merchandise that needs to be taxed.

JMC comes with an API for use with Vertex if that software package is being used. With regards to Vertex (and possibly other tax software packages), consideration needs to be made if all tax data is not provided via the tax package. For instance, in the case of using Vertex for US & Canada but not other countries where a different tax package might be in use.

Two things when loading this data since there will be the use of the API for Vertex and then manual integration to load the remaining data. First, the Vertex API performs a comparison of data between what is in the tables and what it knows about. Since all data is not known from a single source data will be removed which means the international tax data in this case will need to be re-loaded each time the Vertex API is called. Secondly, when adding the international records, caution must be taken when defining the group and authority id’s to be certain they do not use the same id values and overwrite the Vertex records.

Sales (sls_)

The core Sales module retail transaction tables only need to be integrated into if the client needs to allow returns of transactions that occur outside the JMC POS. I.e. in the case of Web orders. There are many tables in this module but only a few are required to complete the return process. The data that is loaded here typically will only exist in the Central Office JMC database, since sales data typically only trickles up from the registers to the central office vs the other direction. Returns usually are looked up in the central database instead of locally. There could be exceptions to this depending on the implementation.

Sales Orders (sls_order_)

The Sales module order tables only need to be integrated into if the client needs to allow fulfillment of transactions that occur outside the JMC POS. I.e. in the case of Web orders like BOPIS. The data typically only resides in the Central Office JMC database just like the regular sales tables

API Endpoints

JMC has several optional endpoints available for various actions. They are built on a microservice-based architecture and are extensible. There are also some pre-configured endpoints, for example, Vertex and Adyen. Some of the other available API’s include: Customer, Loyalty, Inventory, Address Verification, Serialized Coupon, etc.

Please refer to the API Reference Documentation for the comprehensive services.

Transaction Publisher

JMC transactions executed at a POS are all captured and can be exposed via a service bus. The unaudited records can be grouped by type; Sales, Orders, Administrative, Cash Management…, and bundled and published as compressed JSON payloads. This data can be published directly to queues that are configurable in JMC. Subscribers to the service bus or queue can consume this data to load in other backend systems like Sales Audit, Loyalty, et al.

When transactions are published to a queue or service bus from the central office database there are two tables that drive this. The sls_trans_publish which contains all transactions as they are received (keys) with the JSON payload and the sls_trans_publish_status which contains the transactions that have been published already. This means if a record exists only in the sls_trans_publish table, that transaction will be added to the queue or service bus and then all other records, when existing in both tables, nothing will happen and it is assumed these records had already been successfully published.

If for some reason a transaction needs to be re-published and the details are still in these two tables, remove the record from only the sls_trans_publish_status table and then the next time the JMC publish job runs this transaction will be re-published.

There is a purge process set up in the configuration to prevent these two tables from growing too large. By default the number of purge days is set to 2 weeks so any transaction older than that will be purged automatically. This purge configuration can be found in the application-purge.yml files (purgeSalesTransPublish*).

Implementation Considerations

When setting up an integration to load the JMC tables, awareness of how the tables are synchronized through SymmetricDS (SDS) needs to be considered. Looking at how the source data is updated and how often. For instance, is data available as a delta load vs full load, are changes able to be detected and only loaded. These can help determine what the potential impact may be to the synchronization.

Since SDS runs off table triggers, it is important to try to avoid unnecessary data changes if possible. Don’t do a full table reload if a delta is possible, because this will cause many deletes followed by insert triggers to fire which will or could cause slowness in the sync process.

When integrations are scheduled to run is just about as important as how they run. If an integration needs to delete critical data and then insert new records then care needs to be taken when this happens. For example, deleting item price or rule records then inserting new records could cause the POS to not find merchandise being scanned at the register if it happens between the time the delete happens and the new record is added back in.

Typically, the main batch integration jobs should be run off normal store business hours to minimize the impact at a POS.

Detail Table Requirements

The tables in Datamodel are the minimum required for JMC POS to function, except where special circumstances are noted. For each of the tables, the required columns are included along with valid values where applicable, some additional optional columns are also included for visibility but will be noted.

Some tables are ‘tagged’ meaning there are columns starting with ‘tag_’ like tag_country, tag_business_unit_id, etc. If a table is ‘tagged’ then all tag_ columns will exist and must have at a minimum ‘*’ as the value which means all.

The list of these columns is dependent on the JMC implementation, typically there will be tag_country, tag_state, tag_business_unit_id, tag_store_type and tag_device_type. The tags can be used by SDS for determining routing of data, so only pertinent data gets sent to the proper store(s) instead of all data everywhere.

Also, some tags, like tag_device_type, could be used by the POS when determining what data should be shown at a POS on render, i.e. sometimes buttons appear on the back office device that do not appear on a fixed register. Tables below which contain the tag columns will be noted.

All tables contain create_time, create_by, last_update_time and last_update_by columns that are used while creating and updating records. Integrations should populate these accordingly; on creation, both the create and last update columns should be filled in with the same values. Using a name in the xxx_by column that associates to the integration job could be useful during support to understand who last touched the data record.