Walmart Get Orders

Summary of Changes: (The purpose of this table is to keep traceability and Product team to highlight the things that were changed into the scope, based on comments or discussions)

The purpose of this page is to define general requirements for Order management, flow and download setup and mapping from Walmart into Hemi

API Docs: https://developer.walmart.com/doc/us/mp/us-mp-orders/ & https://developer.walmart.com/api/us/mp/orders

API Call: GET https://developer.walmart.com/api/us/mp/orders#operation/getAllOrders

Parameters: We are to use lastModifiedStartDate & lastModifiedEndDate to ensure we are always getting the correctly updated orders. As in our general logic where we can use modified time we want to have an overlap of the time and we have to store a record in <v1.1> last_date_run </v1.1> only once we've successfully completed an order pull. If possible we also want to use shipNodeType as with this parameter we can specify seller fulfilled orders vs Walmart fulfilled orders - or WFO (similar to FBA). If we can ask for both seller and WFO at the same time it would be great, if not we should drop the WFO ones to start off and build it in a separate flow

Standard flow for seller fulfilled orders is we are to download them on a Created status and then Acknowledge them (WFO come directly as Acknowledged). In Walmart “acknowledging” an order means exactly that - marking to Walmart that the order is received. We are obliged to acknowledge orders within 4 hours of the order passing to Created state. Acknowledging an order is not blocking us from any potential alterations of the order (cancellations & refunds) but is allowing us to actually start processing and fulfilment. Unacknowledged orders can’t be shipped

Orders are put by Walmart on a Created status only once a grace period they have internally (30 minutes) passes. Meaning a Created order is a good order for us to start fulfilling

As we download orders for the first time we have to try and pull back for the past 180 days (this is the maximum allowed by Walmart). In a case we receive an error on limitations (due to the load of orders - max of 50 000) best would be to store an order_read record in 170 days and if we continue to receive this error keep reducing the time by 10 days (TBD if 10 days is ok and or if there is another proposition on how to track such a limit error to ensure we will be able to automatically track this - for example field in Account Walmart for first pull number of days to go back)

Below can be seen an example payload followed by Mapping and additional information on the workflow

{
  "list": {
    "meta": {
      "totalCount": 31,
      "limit": 10,
      "nextCursor": "?limit=10&hasMoreElements=true&soIndex=31&poIndex=10&partnerId=100009&sellerId=8&createdStartDate=2013-08-16&createdEndDate=2019-09-17T18:47:03.703Z"
    },
    "elements": {
      "order": [
        {
          "purchaseOrderId": "1796277083022",
          "customerOrderId": "5281956426648",
          "customerEmailId": "3A31739D8B0A45A1B23F7F8C81C8747F@relay.walmart.com",
          "orderType": "REPLACEMENT",
          "originalCustomerOrderID": "1234567891234",
          "orderDate": 1568466571000,
          "shippingInfo": {
            "phone": "3155598681",
            "estimatedDeliveryDate": 1569438000000,
            "estimatedShipDate": 1568700000000,
            "methodCode": "Value",
            "postalAddress": {
              "name": "Kathryn Cole",
              "address1": "3258BWarners rd",
              "address2": "Garage",
              "city": "Warners",
              "state": "NY",
              "postalCode": "13164",
              "country": "USA",
              "addressType": "RESIDENTIAL"
            }
          },
          "orderLines": {
            "orderLine": [
              {
                "lineNumber": "4",
                "item": {
                  "productName": "Beba Bean Pee-pee Teepee Airplane - Blue - Laundry Bag",
                  "sku": "test1"
                },
                "charges": {
                  "charge": [
                    {
                      "chargeType": "PRODUCT",
                      "chargeName": "ItemPrice",
                      "chargeAmount": {
                        "currency": "USD",
                        "amount": 10
                      },
                      "tax": {
                        "taxName": "Tax1",
                        "taxAmount": {
                          "currency": "USD",
                          "amount": 0.8
                        }
                      }
                    }
                  ]
                },
                "orderLineQuantity": {
                  "unitOfMeasurement": "EACH",
                  "amount": "1"
                },
                "statusDate": 1568466647000,
                "orderLineStatuses": {
                  "orderLineStatus": [
                    {
                      "status": "Created",
                      "statusQuantity": {
                        "unitOfMeasurement": "EACH",
                        "amount": "1"
                      },
                      "cancellationReason": null,
                          "trackingInfo":
                          "shipDateTime": 1566933169000,
                          "carrierName": {
                              "otherCarrier": "USPS Marketplace"
                      },
                      "methodCode": "Value",
                      "trackingNumber": "9400100000000000000000",
                      "trackingURL": "http://walmart.narvar.com/walmart/tracking/usps?&type=MP&seller_id=8&promise_date=09/05/2019&dzip=94133&tracking_numbers=9400100000000000000000",
                      "returnCenterAddress": null
                    }
                  ]
                },
                "refund": null,
                "fulfillment": {
                  "fulfillmentOption": "S2H",
                  "shipMethod": "VALUE",
                  "storeId": null,
                  "pickUpDateTime": 1568919600000,
                  "pickUpBy": null
                }
              }
            ]
          }
        },
        {
          "purchaseOrderId": "3796235970012",
          "customerOrderId": "5241952426446",
          "customerEmailId": "630083BD274E4FB2968D3166C8336151@relay.walmart.com",
          "orderType": "REGULAR",
          "orderDate": 1568145593000,
          "shippingInfo": {
            "phone": "4088592715",
            "estimatedDeliveryDate": 1568919600000,
            "estimatedShipDate": 1568181600000,
            "methodCode": "Value",
            "postalAddress": {
              "name": "Steffen Matt",
              "address1": "1830 Mason St",
              "address2": null,
              "city": "San Francisco",
              "state": "CA",
              "postalCode": "94133",
              "country": "USA",
              "addressType": "RESIDENTIAL"
            }
          },
          "orderLines": {
            "orderLine": [
              {
                "lineNumber": "1",
                "item": {
                  "productName": "Qwik Time QT5 Quartz Metronome",
                  "sku": "TEST-02"
                },
                "charges": {
                  "charge": [
                    {
                      "chargeType": "PRODUCT",
                      "chargeName": "ItemPrice",
                      "chargeAmount": {
                        "currency": "USD",
                        "amount": 0
                      },
                      "tax": {
                        "taxName": "Tax1",
                        "taxAmount": {
                          "currency": "USD",
                          "amount": 0
                        }
                      }
                    }
                  ]
                },
                "orderLineQuantity": {
                  "unitOfMeasurement": "EACH",
                  "amount": "1"
                },
                "statusDate": 1568527247000,
                "orderLineStatuses": {
                  "orderLineStatus": [
                    {
                      "status": "Cancelled",
                      "statusQuantity": {
                        "unitOfMeasurement": "EACH",
                        "amount": "1"
                      },
                      "cancellationReason": null,
                      "trackingInfo": null,
                      "returnCenterAddress": null
                    }
                  ]
                },
                "refund": null,
                "fulfillment": {
                  "fulfillmentOption": "S2H",
                  "shipMethod": "VALUE",
                  "storeId": null,
                  "pickUpDateTime": 1568401200000,
                  "pickUpBy": null
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Mapping

Additionally we need to decide if we want to store the original field in here and add additional for 2 char ISO or take the route of this field being always ISO 2 code and store this separately (I’d prefer method 1 and have a separate field for the original info and map both country name and code through our internal list and always keep these 2 clean and the same but we can discuss) | | | | | | | addressType | | | | | Orders > Shipping Address Type | new field - We have two options - original Orders table and have it as ease of display in the future (and export if needed) - or shove it in Orders Walmart as it is the only one that is using it at the moment and move it up to Orders if needed later | | | | | orderLines | | | | | | | N/A | | | | | | | orderLine | | | | | | N/A | | | | | | | | lineNumber | | | | | Products On Order > Item Order Line ID | | | | | | | | item | | | | | N/A | | | | | | | | | productName | | | | Products On Order > Item Title | | | | | | | | | sku | | | | Products On Order > SKU | | | | | | | | charges | | | | | N/A | | | | | | | | | charge | | | | N/A | | | | | | | | | | chargeType | | | N/A | | | | | | | | | | chargeName | | | N/A | Charge seem to be the way of Walmart to provide the price of the product but at the same time keep it versatile for other types of Charges - For item price we need to take only charges with “chargeName” = “ItemPrice“. For itemShippingCost we need to take charges with name “Shipping” Any other “charges” are to be considered as “additional” and to simply be stored as an error line. This is not to affect the overall error status of the order but is more as a Warning so it can be checked and a decision on any changes can be made. Example error line if we have a different charge - “There is an additional charge of X type with Y amount“ | | | | | | | | | chargeAmount | | | N/A | | | | | | | | | | | currency | | Orders > Currency | With an additional validation that all product lines are with the same Currency. If not an error should be stored on the Order | | | | | | | | | | amount | | Products On Order > Item Price | <v1.2> This is the total amount for the line, so we need to divide it by the orderLineQuantity > amount when storing in Hemi. So basically we are dividing the price by the quantity </v1.2> | | | | | | | | | tax | | | N/A | | | | | | | | | | | taxName | | Order Item Walmar > Product Taxes (Key) | All taxes for each product should be summed (if more than 1) and stored against that relevant product. Also for every tax we receive we should store in an IS type field in Order Item Walmart both the name and amount against it so it can be later used for refunds (as we need to pass those too) | | | | | | | | | | taxAmount | | N/A | | | | | | | | | | | | currency | N/A | | | | | | | | | | | | amount | Products On Order > Item Tax Price AND Order Item Walmart > Product Taxes (Value) | As it seems we are receiving values in the form of an option for multiple values we are storing currently the first tax we read in the Order Item > Item Tax Price. All taxes in this section should be summed and stored. This means if we have more taxes we should combine them all for the relevant record and store them in the appointed Order Item Walmart > Product Taxes field. If there is only one tax these two fields should be the same | | | | | | | orderLineQuantity | | | | | N/A | | | | | | | | | unitOfMeasurement | | | | N/A | | | | | | | | | amount | | | | Products On Order > Quantity | | | | | | | | statusDate | | | | | N/A | This should be the last updated status - I would assume this would be useful to track from the DB for any changes but I don’t think it is very useful to the user, hence why I haven’t added a mapping - it can stay in Order Walmart if decided to store it | | | | | | | orderLineStatuses | | | | | N/A | Generally these statuses should be stored on Order Item Line level and should be updated accordingly by actions from within the system (Acknowledge / Ship / Cancel / Refund) or by reading them from the MP. As seen below there can be multiple statuses per Walmart line meaning each ordered qty can have a different status on the order. For more information on exact statuses and their update and priority please see “Table of Statuses” below and “Additional Information” but the idea would be to keep track on Order Item Line level and extrapolate what is the actual status of the Order Item (status) and from there the Order itself (Marketplace status) | | | | | | | | orderLineStatus | | | | N/A | | | | | | | | | | status | | | Order Item Line > Status | | | | | | | | | | statusQuantity | | | N/A | | | | | | | | | | | unitOfMeasurement | | N/A | | | | | | | | | | | amount | | N/A | Quantity for each status is not to be mapped but according to it we need to build the logic to track how many of that item moved to the next status and which Order Item Line IDs in Hemi need to be updated | | | | | | | | | cancellationReason | | | Order Payment > Reason | This needs to stay in an Order Payment Row as for any “Cancelled” or “Refunded” orders that have this filled in we need to have a Refund in Hemi. It will either be created by us and we will already have this reason or it will be new that we are creating and need to input the reason as well (if present) | | | | | | | | | trackingInfo | | | | See table “trackingInfo” below | | | | | | | | | returnCenterAddress | | | | See table “returnCenterAddress“ below | | | | | | | refund | | | | | | See table “refund” below | | | | | | | fulfillment | | | | | N/A | I believe this section is generally not that useful to us at this point. We might need to add additional mappings from here in case a client asks for this information for Walmart fulfilment or 3PL fulfilment, etc. Besides this for now we should only store the fulfilmentOption which seems to be the indicator who should fulfil this | | | | | | | | fulfillmentOption | | | | Order Walmart > Fulfilment option AND Orders > Order Type | new field* - just to store the information as it is AND Map to existing options: IF we see “S2H” map to “Home Delivery”, otherwise leave empty | | | | | | | | shipMethod | | | | N/A | | | | | | | | | storeId | | | | N/A | | | | | | | | | pickUpDateTime | | | | N/A | | | | | | | | | pickUpBy | | | | N/A | |

Order Statuses

Note: Just to reiterate the above on different statuses in the same order - If we are downloading an order for the first time to store in Hemi the logic goes like this: IF there is at least one ACKNOWLEDGED = RFS, ELSE IF there is at least one SHIPPED/DISPATCHED = Shipped ELSE IF all lines are CANCELLED/REFUND = Cancelled. In any other combination OR statuses we don’t know what to do with we should map to the Hemi status Incomplete

Incomplete should also be used in case we have any combination between CREATED and anything else or if we are missing enough information to store any of the following in Hemi: MP Order ID, Total, buyer name, Shipping Address 1, Shipping Postcode, Shipping City, Shipping Country, Shipping State Province, Order Lines, Item Quantity or Item Price

ADDITIONAL INFORMATION

Cancellation - During the process of testing we’ve had multiple different experiences with Walmart. To mitigate for this we need to be prepared for 2 scenarios (below scenarios are concerning multiline orders. A single line order can be only cancelled in full after all)

1. Receiving the whole order - This is the default case we are expecting. This means that when a multiline order has been partially cancelled we will receive the whole order (non cancelled items included). In this scenario we want to track what is cancelled on the items, what we’ve marked as cancelled so fat and simple invoke the standard Refund processing ability to store the correct Payment record in our system besides updating the line status fields
2. Receiving only the partially cancelled item - This is the edge case we’ve seen for long enough to not feel comfortable just dropping - at this point we need to figure out what else we have on the order (based on what we have stored so far in our system) and decide how to proceed from there as a standard process

To achieve this I would imagine we have to start with a process of making sure that the order we’ve received is full or if not take all needed information from our record in Hemi and then proceed with the standard input of status updates (tool status included in case needed based on the supplied explanation in this doc above), payment record creation, etc.

trackingInfo - besides the example Mapping there are a few more fields to ensure everything is captured (hopefully), trackingInfo being one of them. See the mapping as from the XSD below.

<v1.1>

We have potential for more than one shipment (for different order lines) so we need to handle this in in the Order Shipment table. We need to validate and if we have different shipment info across different order lines for the same order, we need to store each shipment as separate row in the Order Shipment table with the respective shipping info. If we have the same shipping info (carrierName AND trackingNumber) we need to combine the shipped order lines and create a single row in Order Shipment.

Note: If the tracking and Carrier values are the same as those in Hemi (meaning the latest update is done via us) they don’t need to be updated, only Order Shipment > Shipment Time so we know when was processed the latest update we’ve provided.

If the tracking info is not present in Hemi (meaning we have not done the Shipping update) then we need to store the info as per the above table with a Completed status.</v1.1>

returnCenterAddress - This is the return address that the seller has specified in the Walmart Seller central. This info is not required for us but it is rather informative and is good to have it stored in Hemi anyways. Mapping below :

refund - this section will be present only where applicable. By the looks of it it structure wise it seems similar to what we have and we can combine from the order by the refund ID (see below). This should be our check for the refund processor as well to decide if this will be stored or already present. Multiple refunds can be present for a single product

Note:

• First thing is I’ve noticed in the response of a Refund order that there was no Refund ID… In which case we need to employ the additional checks when downloading a refund. I hope there will actually always be a refund ID but we need to be prepared
• Second thing is it seems we can be refunding a sum, whatever we want it to be (more in the refund page) - which means we should check how it looks like in a consecutive refund on the same product (if there are different IDs, again, it should be easy. But if not and it just starts adding to the same refund for the product it will be a problem for us and the way we handle it)

Additional Information

• It seems orders will be coming combined with multiple SKUs in them (Order Lines) and each with its own quantity and statuses. Everything in Walmart turns around the order line itself even if it can be a part of a multi product order

• Once an order is on a “Created” status we are not expecting anything to be changing anymore from the MP itself (no buyer cancellations, changes of addresses etc, addition/removal of products, etc.)

• Based on the example we have a few key sections not filled in (that can be found in the xsd). Those are:

  • paymentTypes - which should be mapped to our payment method in Orders
  • orderSummary - which has:
    • totalAmount - mapped to Orders > Total
    • orderSubTotals
      • subTotalType - N/A
      • totalAmount - mapped to Orders > Subtotal
  • pickupPersons - N/A (for now, if needed we can add it to Orders Walmart)

  • shipNode - N/A (for now)

    What needs to be taken as consideration here though is the fact that we don’t have those sections in the example. To be on the safe side we should prepare for the orderSummary section if it is not present to calculate via the orderLines. Everything that we’ve deemed acceptable for the product price (PRODUCT charge and its tax) should be multiplied by quantity, summed for each line in the order and used for subtotal for the time being. Adding the shipping charge from below should give us the Order Total

• Shipping Charge After some testing we managed to find that as part of the ordeLines we can receive also a separate charge for Shipping with type “Shipping”. This should be stored on the respective item against which we are reading it (the corresponding orderLine from Walmart to our order_item record) in the Order Item > Item Shipping Cost field. We should also store the tax in a separate field (please use Order Item > Item Shipping tax cost) for Item Shipping Cost Sales Tax. Additionally we should pick also all shipping costs for all lines and combine them in a general “Shipping Cost” for the order and do the same for the Shopping Cost Sales Tax from all items and store them in the general “Shipping Sales Tax” field

  {
      "chargeType":"SHIPPING",
      "chargeName":"Shipping",
      "chargeAmount":{
         "currency":"USD",
         "amount":6.95
      },
      "tax":{
         "taxName":"Tax1",
         "taxAmount":{
            "currency":"USD",
            "amount":0.57
         }
      }
   }

  <v1.2> All item costs with their respective taxes, divided by the ordered quantities for the respective items plus shipping costs and shipping cost sales taxes should form the final Order Total </v1.2>

• We shouldn’t be able to cancel something that is already shipped or vise versa, refund something that is already cancelled (refund should be used only post cancelations) - meaning the refund processor can have a clear distinction between something with a cancelled status and refund section filled in