Sales and Inventory Jobs

Sales Data Sources

• Fishbowl
  • Wholesale/Manual orders
  • Marketplace orders (eBay, Amazon, etc.)
  • Channel Advisor
• Mercado Libre
• Kentucky (out of scope for this)

Basic Data Flow

1. Import Channel Advisor data file to temporary table in ShopFloor
2. Import Fishbowl sales via direct database connection to new sales tables
3. Import Mercado Libre sales via API to new sales tables
4. Update sales channel for Fishbowl sales
5. Update WorkTracking inventory and sales data for serialized items

Channel Advisor Data Flow

The only reason this exists is to determine which marketplace a sale came from. It does not change anything else for a sale. The temporary table SalesImport is used for nothing else.

1. Data is pulled from Channel Advisor by Mohammad G and saved in a CSV file in the sales bucket on Azure
2. An Azure function ctldatafilesync-prod runs every 45 minutes to parse that file and import it to the SalesImport table in the ShopFloor database. This might not be working 100% at the moment because the job takes too long to complete. GitHub

This can be expanded later to include sales from the Kentucky printer system. Adding a new mapping configuration for the data file is needed, and then a new job in ShopFloor to map these entries to the new sales data tables if required (for a more complete sales report).

Fishbowl Data Flow

This is the primary source of sales data. Most everything comes from Fishbowl, for both Dallas and Mexicali.

Note: Transfers of inventory to a third party (Mercado Libre or Amazon FBA) are included in Fishbowl. The current process is Dallas will enter those items transferred as a sale at $0. This incorrectly removes the item from ShopFloor inventory and marks it as sold. Mexicali uses the Set as Sold station (I think) to remove the items from inventory. Also wrong.

1. Data is pulled daily at 7:30AM UTC for all the previous day’s sales (wrong in the current production version, but updated in the Mercado Libre branch on UAT). Job name in ShopFloor is FishbowlSalesImportJob.
2. All sales that were created in the previous day are downloaded and translated to the new sales tables: ProductSalesOrder, ProductSalesOrderLineItem, and ProductSalesOrderLineItemDetail.
3. This job also checks the SalesImport table to see if the order exists and updates the ChannelName column if available.

Mercado Libre Data Flow

Not yet on production. This flow gets all sales data from Mercado Libre’s API and translates it to the new sales data format.

Note: Mohammad G has experienced several inconsistencies with the Mercado Libre API. For example, if a user deactivates their account, their orders do not show up in the API anymore. This should only impact historical sales data, unless a user orders and deactivates in less than 24 hours.

1. Data is pulled daily at 8:00AM UTC for the previous day’s sales. Job name in ShopFloor is MercadoLibreSalesImportJob.
2. All sales data is translated to the new sales data format.

Update Sales Channels

This job will try to match up any Fishbowl sales with the Channel Advisor data import by order number and set the correct channel.

All Fishbowl sales will show “Fishbowl” as the channel, until a matching order is found in the data import. This is in the UpdateSalesChannelJob in ShopFloor.

Inventory Update

This job is responsible for updating the WorkTracking records in ShopFloor for sold items. This replaces the SyncItemsShippedOrSold method in SyncSalesAndInventoryWithWorkTrackingJob. This is in the SalesInventoryUpdateJob in ShopFloor.

1. For any Mercado Libre orders where the status is shipped or delivered OR
2. For any non-Mercado Libre orders where the status is Fulfilled
   1. Determine which tenant in ShopFloor to look in (MX and CA locations are Mexicali, assume all others are Dallas).
   2. If the ProductSalesOrderLineItemDetail record does not have an associated WorkTrackingID
      1. If there is a known serial number from the sales data, find that WorkTracking record:
         1. Update WorkTrackingID and BOMHeaderID in the sales tables
         2. Update the UnitSalesPrice, UnitSoldDate in WorkTracking with the sales data
      2. If there is NO serial number from the sales data NOT YET DEVELOPED
         1. Find the FIFO item from WorkTracking for that third party warehouse (if sold from one) or the closest match
            1. Update WorkTrackingID and BOMHeaderID in the sales tables
            2. Update the UnitSalesPrice, UnitSoldDate in WorkTracking with the sales data

The purpose of this job is to update the WorkTracking table exactly like it did before, but the source of the data is more comprehensive.

---

Database Schema

Most columns in the tables are self-explanatory. The ones documented are ones that might be unclear.

ProductSalesOrder

• Status - this is a copy from the data source and not mapped to any internal status
• ChannelName - this is the source of the data. “Fishbowl” is used as the default for Fishbowl, but updated later from the UpdateSalesChannelJob.
• ChannelOrderID - for Fishbowl sales, this is the order # from the marketplace (eBay, Amazon, etc.). For Mercado Libre, this is the order #
• ExternalReferenceNumber - for Fishbowl sales, this is the database id column from the so table. For Mercado Libre, this is the pack #

ProductSalesOrderLineItem

• BOMHeaderID - not populated during the sales download, but parsed later during the SalesInventoryUpdateJob if a match is found. Primarily used for easier lookups later.
• ProductSKU / ProductDescription - these are the values from the sales channel, not the internal ShopFloor values.

ProductSalesOrderLineItemDetail

A record is created in this table for each unit on the sale. If 10 of a SKU is sold, there will be 10 records for that line item in this table. This is used to keep track of serialized line items. Sometimes we will have the serial number sold, sometimes we will not, but each one will eventually map back to a related record in WorkTracking.

• WorkTrackingID - the related work tracking item for this sold item. This is populated in the SalesInventoryUpdateJob.