VMI Data Collection - POS Data

Point of Sale (POS) Data

• represents each register sale at a customers location
• typically received daily via EDI from each trading partner, sometimes downloaded from a trading partner's system
• typical fields are: sender/receiver ID (unique identifier of the trading partner), store/location ID, UPC/item ID, transaction date, quantity.  It is also helpful to collect a unique control/subcontrol number and a received/imported date.

POS Data Considerations

Variation in Data
There is a lot of variation in what each trading partner will send as their store/location ID and UPC/item ID.  Sometimes it will be the UPC, sometimes your internal number, sometime a GTIN.  There is often variation in whether leading zeros are sent, and whether check digits are sent.  To handle this, you need the ability to map external values to internal values for each trading partner.

Missing or Duplicate Data
Because you are dependent on many other IT systems to feed you the POS data, inevitably there will be periodic hiccups where data doesn't come through, or even comes through multiple times.  The sooner you can identify this problem and alert your trading partner the better.  Your back-end EDI system (Gentran, Data Masons, etc) might be useful in this, but watching for data anomalies inside of AX VMI ends up begin more effective.

Data Anomalies
It's important to be flexible in handling POS data that 'doesn't make sense'. Below are three common situations which should be planned for:

• Receiving sales (or returns) for an item that was never shipped to the customer location or that was discontinued years ago.
• Receiving a high quantity of unit sales of the same item from the same store on the same day.  In researching these with the trading partners, we believe these are legitimate large purchases where the cashier scans a single item and keys in a quantity rather than scanning each separate item.  
• If you sell product to a trading partner under different terms - some pay from scan (PFS)/ scan based trade (SBT), some Net 30, we've seen situations where outright sales come through EDI.  Similarly, this issue can arise when you ship an order to a customer based on a special request from one of their customers. 

AX VMI Architecture

Below is the architecture of the VMI module surrounding POS Data collection.

Tables
POSTransactionExternal - SQL table, not an AX table to avoid RecId requirements.  Records are inserted into this table from sources outside of AX. AX batch process moves records from this table table into POSTransactionStaging.
POSTransactionStaging - AX table containing raw POS data.  Users may review import errors, manually adjust data, and import/unimport data as needed. External values are mapped to internal values. An AX batch process automatically maps and imports data unless an error is detected.  This data is purged once there is no longer a need to unimport POS data - usually after a year.
POSTransaction - AX table containing validated POS transactions with all fields in terms of 'internal' values. (i.e. UPC has been translated to ItemId)

Screens
POS Import Journal - this screen allows users to view/edit/delete the data in the staging table, review/fix errors (typically where mapping has not been set up), and post/unpost (a.k.a import/unimport) data.

The status filter is used to highlight records that require attention.

Below is a successfully mapped/posted record.  The error description field contains details about why a record could not be mapped or posted.  Until a record has been posted, users receive reminder emails to address this issue.  In some cases, we use the 'Ignore and Never Post' option and add a note to the error description describing why this record is an exception.

The user can use the following buttons to:

1) manually import POS data from an Excel/text file.

2) manually Post/Unpost selected POS records

POS Data collection is just a small portion of the overall VMI structure, but it can be a problem area if it's not flexible to handle typical POS data anomalies.