Feature – Import Shopify Orders to Elliott through ESS and SPS

Feature – Import Shopify Orders to Elliott through ESS and SPS

Release Date: 07/08/2023
Version: 8.5 & Up

New Features to Support Shopify Interface

Shopify is a popular turnkey solution for creating eCommerce websites. Our EDI partner has created an interface with Shopify so the eCommerce order can be imported into Elliott as an EDI order.  Elliott has recently added the necessary features to support this through the ESS (Elliott SPS Interface). In this release, we have included the following features:

(1) Elliott now supports importing payment records with Sales Order Import. This was not necessary before since the typical EDI order does not come with payment information.  Since eCommerce orders are typically prepaid by credit card, it is now necessary to import the sales order to indicate that payment has been received.  See the following KB article for more details: https://support.netcellent.com/portal/en/kb/articles/feature-support-payment-record-in-sales-order-import

(2) The ESS (Elliott SPS Interface) has been enhanced to support the following features:
  • Map Customer Number by State: In most cases, the eCommerce site needs to charge sales taxes for customers in their home state.  If the sales volume to other states exceeds nexus, it may be necessary to collect sales tax for those states as well.  A common technique is to set up one or more customers with the appropriate sales tax percentage for each in customer setup, along with a generic customer for all other states that do not require sales tax collection. Therefore, depending on the ship-to state of the eCommerce order, the imported orders need to be mapped to the corresponding customer in Elliott. Note that Elliott 8.6 also supports Avalara. If you choose to use the Elliott 8.6 Avalara interface, then you only need one generic customer in Elliott for Shopify orders.
  • Map Bill-To Address: Traditionally, the Bill-To State would come from the customer's address, so there was no need to map the Bill-To address. However, in the eCommerce situation, we are using a generic miscellaneous customer number where the customer's address is not necessarily the billing address. Hence, it is now required to map the Bill-To address.
  • Map Customer Order Number to PO: The XML PO from SPS will have two order number types, Purchase Order Number and Customer Order Number. Traditionally, ESS would map the Purchase Order Number in the XML file to Elliott's Sales Order Customer Purchase Order Number. However, you may want to map the Customer Order Number instead.
  • Map Tax to Misc. Charges: Shopify can optionally interface with Avalara to calculate sales tax. Sales tax can be collected on the Shopify side. The issue is whether the sales tax should be reported from the Shopify side or the Elliott side. If the sales tax should be reported from the Avalara side, then the sales tax should be mapped to Elliott as a Misc. Charge. If the sales tax should be reported from the Elliott side, then there should be no sales tax imported so Elliott's sales tax logic can handle the calculation.
  • Map Discounted Unit Price: In Shopify, a line item may be discounted due to a promotion. The SPS interface will send the list price as the Purchase Price field. If there is a promotion, then the Discounted Unit Price should be used.
  • Suppress Attribute Truncation Warning: When a segment that will be mapped to Elliott's attribute exceeds 30 characters, ESS will send a warning email. This may happen frequently with certain Shopify implementations and is therefore meaningless.
  • Suppress Multiple Payment Terms Warning: The SPS interface with Shopify will result in two <PaymentTerms> segments, causing a warning with ESS. Since this occurs consistently with Shopify implementations, the warning becomes meaningless.
  • Map Payment Record: This feature maps the payment amount of a Shopify Order to the Elliott Sales Order as a payment record.

This new feature opens up opportunities for Elliott users to utilize Shopify eCommerce websites to accept online orders and, if desired, interface these eCommerce orders into Elliott as sales orders for processing shipments. The Elliott tracking number can be retrieved from existing Elliott reports and uploaded back to Shopify to notify consumers of the shipment.

An Example of Shopify Interface Implementation

In the following example, the Elliott users would like the mapping of the Shopify Order into the Elliott Sales order as follows:

Header Screen


We implemented the following flags in ESS to support mapping of order header fields:


<add key="EliCustomerNo" value="*55555" />
<add key="EliCustomerNoByState" value="CA|*CA555" />

The setting above means the default customer is *55555. But if the Customer Ship-To State is in “CA,” then use customer number *CA555 instead. Multiple states are supported by a comma “,” separator.


<add key="E850MapBillToAddress" value="Y" />

The flag above means ESS will map the <Address> segment that <AddressTypeCode> = “BT” to the Elliott sales order bill-to address. Without this flag, Elliott by default does not map the  Bill To Address, hence the use of  the customer’s address.

           <add key="E850MapCustomerOrderNumberToPO" value="Y" />

The above flag means to map the segment <CustomerOrderNumber> to the Elliott Sales Order Customer PO Number, instead of the default <PurchaseOrderNumber>> segment.
Note the since the Elliott ship to address is a maximum of 30 characters and the city is maximum 15 characters, there exists a possibility that the consumer’s address and city may exceed the maximum limit. In that case, ESS should send an email to warn users about this so users can take action to abbreviate the address or city. The following two flags are introduced to suppress irrelevant warning messages so the only warning emails users will receive will be the address-size-limit-exceed issue:


<add key="E850MapSuppressAttributeTruncationWarning" value="Y" />
<add key="E850MapSuppressMultiplePaymentTermsWarning" value="Y" />

Also, please be aware that ESS does not like non-ASCII characters. The Carrier description should not contain characters like “,” or “|” since those characters have special meaning. You should configure your Shopify carrier description to avoid using those characters. The following is a common mistake of using non-ASCII characters as registration trademark:
  • FedEx International Ground®
  • FedEx®
The following is a sample flag to EliShipViaCodeMapping:

<add key="EliShipViaCodeMapping" value="
    ~FedEx Ground (4-10 Days)~|FH,
    ~FedEx 2 Day Air (1-2 Day Lead-Time)~|F4,
    ~FedEx Next Day Air (1-2 Day Lead-Time)~|FP,
    ~FedEx Freight Economy~|TC,
    ~FedEx Freight Economy (L) with lift gate delivery~|TC,
    ~FedEx Freight Economy (R L) with residential and lift gate delivery~|TC,
    ~Local Pickup in Chino CA 5 Days (SoCal Only)~|WC,
   ~FedEx International Economy~|FI,
    ~FedEx International Ground~|FI,
    ~FedEx International Priority~|FI" />

Line Item Screen

We added the following flag to support the import of discounted unit price:

                <add key="E850MapDiscountUnitPrice" value="Y" />

It will use discounted unit price if it exists, otherwise it will use the purchase price which is the default.

Billing Screen

The following flags are added to support the billing screen:


<add key="E850MapTaxToMiscCharge" value="Y" />
<add key="E850MapTaxToMiscChargeExemptState" value="CA" />

The first flag will map the <TaxAmount> segment to the Elliott Misc. Charges field. The second flag indicates that if the ship-to state is “CA,” then the <TaxAmount> will be mapped to Misc. Charges.


<add key="E850MapPaymentRecord" value="paypal|CS,cyber_source|CC,|CC" />

The above flag means if the <PaymentMethodID> = “paypal,” then the order total amount should be mapped to payment type “CS” (Cash). If the <PaymentMethodID> = “cyber_source”, then the order total amount should be mapped to payment type “CC” (Credit Card). If the <PaymentMethodID> segment is preset in the XML, but no match is found, then the “CC” payment type should be used.

                <add key="E850MapFreightAmountCode" value="G830" />

The above flags means if <ChargesAllowances> segment is present and <AllowChrgeCode> = “G830” and the <ChargesAllowanceIndicator> = "C" (i.e. it is a "Charge"), then the <AllowChrgeAmt> will be mapped to the Order Freight Amount field. This flag is also related to the following changes we made in Elliott sales order import to support <ChargesAllowances> segment in 850:

Uploading Tracking Number to Shopify

To notify the eCommerce customers that the orders have been shipped with the associated tracking number, it is necessary to upload an Excel file with four columns: 
  1. Order Number: This is the Elliott Sales Order Customer Purchase Order Number & Carrier Tracking Number.  As indicated previously, we use the flag <add key="E850MapCustomerOrderNumberToPO" value="Y" /> so the Elliott Sales Order Customer Purchase Order Number.is actually the <CustomerOrderNumber> in the XML file.
  2. Tracking Number: You can get this information from Elliott Order Ship Status Audit Report.
  3. Tracking Company: This can be a static value. For example, if you always use FedEx for shipping, you can just put FedEx in this column.
  4. Tracking URL: This can be a static value of the URL for FedEx.
See sample template below:

You can go to Elliott, Customer Order Processing, Processing, Print Pick/pack Ticket & Lbls, Shipping, and Order Ship Status Audit Report.  Enter the proper parameters to get the result and make sure you choose to export to CSV.  The CSV contains a lot more columns than are needed for uploading to Shopify.  Eliminate the unnecessary columns and save the first two columns that are required for Shopify.  Then append the third and fourth columns as static values and save it as an Excel file.

To upload tracking numbers to Shopify, log into Shopify and locate the App section of the grey menu as shown in the screen below:

Click on Bulk Fulfill app and proceed to "Open."  Drag the Excel file into upload area:


Once the file is uploaded, you will see the following screen. If nothing is in red, that means your template is in the correct form.

Reference A - Sample Shopify XML File from SPS

<Orders xmlns="http://www.spscommerce.com/RSX"><Order>
    <Meta>
        <IsDropShip>true</IsDropShip>
    </Meta>
    <Header>
        <OrderHeader>
            <TradingPartnerId>ZM0ALLKINGSTONB</TradingPartnerId>
            <PurchaseOrderNumber>5744808394794</PurchaseOrderNumber>
            <PrimaryPOTypeCode>DS</PrimaryPOTypeCode>
            <PurchaseOrderDate>2023-03-14</PurchaseOrderDate>
            <SellersCurrency>USD</SellersCurrency>
            <Division>58094</Division>
            <CustomerOrderNumber>KB159094</CustomerOrderNumber>
        </OrderHeader>
        <PaymentTerms>
            <PaymentMethodID>cyber_source</PaymentMethodID>
        </PaymentTerms>
        <PaymentTerms>
            <PaymentMethodID>rIb0dMC5lQLVjL3MWl0nk6Av6</PaymentMethodID>
        </PaymentTerms>
        <Dates>
            <DateTimeQualifier>171</DateTimeQualifier>
            <Date>2023-03-14</Date>
        </Dates>
        <Dates>
            <DateTimeQualifier>ORS</DateTimeQualifier>
            <Date>2023-03-14</Date>
        </Dates>
        <Address>
            <AddressTypeCode>WH</AddressTypeCode>
            <AddressLocationNumber>62655889450</AddressLocationNumber>
            <Address1>12775 South Reservoir Street</Address1>
            <City>Chino</City>
            <PostalCode>91710</PostalCode>
            <Country>US</Country>
            <Contacts>
                <ContactTypeCode>IC</ContactTypeCode>
                <ContactName>12775 South Reservoir Street</ContactName>
                <PrimaryPhone>8772527277</PrimaryPhone>
            </Contacts>
        </Address>
        <Address>
            <AddressTypeCode>ST</AddressTypeCode>
            <AddressName>Patricia McNiel</AddressName>
            <Address1>10207 Hunt Dr</Address1>
            <City>Davison</City>
            <State>MI</State>
            <PostalCode>48423</PostalCode>
            <Country>US</Country>
            <Contacts>
                <ContactTypeCode>RE</ContactTypeCode>
                <ContactName>Patricia McNiel</ContactName>
                <PrimaryPhone>(810) 656-4743</PrimaryPhone>
            </Contacts>
        </Address>
        <Address>
            <AddressTypeCode>BT</AddressTypeCode>
            <AddressName>Patricia McNiel</AddressName>
            <Address1>10207 Hunt Dr</Address1>
            <City>Davison</City>
            <State>MI</State>
            <PostalCode>48423</PostalCode>
            <Country>US</Country>
            <Contacts>
                <ContactTypeCode>BI</ContactTypeCode>
                <ContactName>Patricia McNiel</ContactName>
                <PrimaryPhone>(810) 656-4743</PrimaryPhone>
                <PrimaryEmail>pmcniel4743@gmail.com</PrimaryEmail>
            </Contacts>
        </Address>
        <Address>
            <AddressTypeCode>BY</AddressTypeCode>
            <LocationCodeQualifier>92</LocationCodeQualifier>
            <AddressLocationNumber>7379349471274</AddressLocationNumber>
            <AddressName>Patricia McNiel</AddressName>
            <Address1>10207 Hunt Dr</Address1>
            <City>Davison</City>
            <State>MI</State>
            <PostalCode>48423</PostalCode>
            <Country>US</Country>
            <References>
                <ReferenceQual>YD</ReferenceQual>
                <ReferenceID>6005139669034</ReferenceID>
            </References>
            <Contacts>
                <ContactTypeCode>IC</ContactTypeCode>
                <ContactName>Patricia McNiel</ContactName>
                <PrimaryEmail>pmcniel4743@gmail.com</PrimaryEmail>
                <ContactReference>Verified Email: true</ContactReference>
            </Contacts>
        </Address>
        <Address>
            <AddressTypeCode>SF</AddressTypeCode>
            <AddressName>Intuitive Shipping</AddressName>
        </Address>
        <CarrierInformation>
            <CarrierAlphaCode>650f1a14fa979ec5c74d063e968411d4</CarrierAlphaCode>
            <CarrierRouting>FedEx Ground (4-10 Days)</CarrierRouting>
        </CarrierInformation>
        <References>
            <ReferenceQual>06</ReferenceQual>
            <ReferenceID>4725454635050</ReferenceID>
        </References>
        <References>
            <ReferenceQual>AN</ReferenceQual>
            <ReferenceID>5744808394794</ReferenceID>
        </References>
        <Notes>
            <NoteCode>SPE</NoteCode>
            <Note>FedEx® Small Box (1 lb):1x KBH3638PL Porcelain Lever Handle, Brushed Nickel</Note>
        </Notes>
        <Taxes>
            <TaxTypeCode>UNDF</TaxTypeCode>
            <TaxAmount>2.94</TaxAmount>
            <TaxPercent>0.06</TaxPercent>
            <Description>MI STATE TAX</Description>
        </Taxes>
        <Taxes>
            <TaxTypeCode>TX</TaxTypeCode>
            <TaxAmount>2.94</TaxAmount>
        </Taxes>
        <ChargesAllowances>
            <AllowChrgIndicator>C</AllowChrgIndicator>
            <AllowChrgCode>G830</AllowChrgCode>
            <AllowChrgAmt>14.07</AllowChrgAmt>
        </ChargesAllowances>
        <MonetaryAmounts>
            <MonetaryAmountCode>1</MonetaryAmountCode>
            <MonetaryAmount>34.95</MonetaryAmount>
        </MonetaryAmounts>
        <MonetaryAmounts>
            <MonetaryAmountCode>OJ</MonetaryAmountCode>
            <MonetaryAmount>34.95</MonetaryAmount>
        </MonetaryAmounts>
        <QuantityTotals>
            <QuantityTotalsQualifier>SQT</QuantityTotalsQualifier>
            <WeightQualifier>G</WeightQualifier>
            <Weight>158</Weight>
            <WeightUOM>GR</WeightUOM>
        </QuantityTotals>
    </Header>
    <LineItem>
        <OrderLine>
            <LineSequenceNumber>12013082705962</LineSequenceNumber>
            <BuyerPartNumber>KBH3638PL</BuyerPartNumber>
            <ConsumerPackageCode>663370233333</ConsumerPackageCode>
            <ProductID>
                <PartNumberQual>ZVP</PartNumberQual>
                <PartNumber>6841803243562</PartNumber>
            </ProductID>
            <ProductID>
                <PartNumberQual>VA</PartNumberQual>
                <PartNumber>40262643154986</PartNumber>
            </ProductID>
            <ProductID>
                <PartNumberQual>MN</PartNumberQual>
                <PartNumber>KBH3638PL Porcelain Lever Handle, Brushed Nickel</PartNumber>
            </ProductID>
            <OrderQty>1</OrderQty>
            <OrderQtyUOM>EA</OrderQtyUOM>
            <PurchasePrice>34.95</PurchasePrice>
            <ProductColorDescription>Default Title</ProductColorDescription>
        </OrderLine>
        <Measurements>
            <MeasurementQualifier>WT</MeasurementQualifier>
            <MeasurementValue>159</MeasurementValue>
            <CompositeUOM>GR</CompositeUOM>
        </Measurements>
        <ProductOrItemDescription>
            <ProductCharacteristicCode>08</ProductCharacteristicCode>
            <ProductDescription>KBH3638PL Porcelain Lever Handle, Brushed Nickel</ProductDescription>
        </ProductOrItemDescription>
        <Notes>
            <NoteCode>GFT</NoteCode>
            <Note>IsGiftCard: false</Note>
        </Notes>
        <Address>
            <AddressTypeCode>VN</AddressTypeCode>
            <AddressName>Kingston Brass</AddressName>
        </Address>
        <Address>
            <AddressTypeCode>WH</AddressTypeCode>
            <AddressName>manual</AddressName>
        </Address>
        <QuantitiesSchedulesLocations>
            <QuantityQualifier>33</QuantityQualifier>
            <TotalQty>1</TotalQty>
            <TotalQtyUOM>EA</TotalQtyUOM>
        </QuantitiesSchedulesLocations>
        <Taxes>
            <TaxTypeCode>UNDF</TaxTypeCode>
            <TaxAmount>2.1</TaxAmount>
            <TaxPercent>0.06</TaxPercent>
            <Description>MI STATE TAX</Description>
        </Taxes>
        <Taxes>
            <TaxTypeCode>TX</TaxTypeCode>
            <Description>IsTaxable: true</Description>
        </Taxes>
    </LineItem>
    <Summary>
        <TotalAmount>51.96</TotalAmount>
        <TotalLineItemNumber>1</TotalLineItemNumber>
    </Summary>
</Order>
</Orders>

Reference B - Sample Shopify Config File in ESS
The following is an example of Shopify.Config in Elliott SPS Service setup:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="notificationSettings" type="System.Configuration.AppSettingsSection" />
  </configSections>
  <appSettings>
    <add key="Name" value="Shopify" />
    <add key="SpsTradingPartnerId" value="ZM0ALLKINGSTONB" />
    <add key="EliCustomerNo" value="*55555" />
    <add key="EliCustomerNoByState" value="CA|*CA555" />
    <add key="Enable850" value="Y" />
    <add key="Enable810" value="N" />
    <add key="Enable846" value="N" />
    <add key="Enable856" value="N" />
    <add key="Enable855TurnAround" value="N" />
    <add key="UseUCC128Label" value="N" />
    <!-- Drop Ship Customers Only -->
    <!-- Bill to address is consumer bill-to address and must be mapped -->
    <add key="EliShipDateQualifier" value="171" />
    <add key="EliShipInstr1Date" value="171|Order Date: {0:MM/dd/yyyy}" />
    <add key="EliShipInstr2Date" value="" />
    <add key="DefaultTsetPurposeCode" value="00" />
    <add key="DefaultPrimaryPOTypeCode" value="DS" />
    <add key="E850MapBillToAddress" value="Y" />
    <add key="E850MapCustomerOrderNumberToPO" value="Y" />
    <add key="E850MapTaxToMiscCharge" value="Y" />
    <add key="E850MapTaxToMiscChargeExemptState" value="CA" />
    <add key="E850MapDiscountUnitPrice" value="Y" />
    <add key="E850MapFreightAmountCode" value="G830" />
    <add key="E850MapSuppressAttributeTruncationWarning" value="Y" />
    <add key="E850MapSuppressMultiplePaymentTermsWarning" value="Y" />
    <add key="E850MapPaymentRecord" value="paypal|CS,cyber_source|CC,|CC" />
    <add key="DefaultVendor" value="" />
    <add key="MapVendorPartNumberIn" value="Y" />
    <add key="EliShipViaCodeMapping" value="
    ~FedEx Ground (4-10 Days)~|FH,
    ~FedEx 2 Day Air (1-2 Day Lead-Time)~|F4,
    ~FedEx Next Day Air (1-2 Day Lead-Time)~|FP,
    ~FedEx Freight Economy~|TC,
    ~FedEx Freight Economy (L) with lift gate delivery~|TC,
    ~FedEx Freight Economy (R L) with residential and lift gate delivery~|TC,
    ~Local Pickup in Chino CA 5 Days (SoCal Only)~|WC,
    ~FedEx International Economy~|FI,
    ~FedEx International Ground~|FI,
    ~FedEx International Priority~|FI" />
  </appSettings>
</configuration>



EMK


    • Related Articles

    • How Do You Export Tracking Numbers From Elliott Into Shopify?

      Release Date: 7/7/2023 Version: 8.5 & Up Q: How do you export tracking numbers from Elliott into Shopify? A: Shopify is a good tool to build a presence on the internet and allow customers to enter orders for your products. Once you have shipped the ...

    • How to Write Test Codes for C# ESS Projects

      Release Date: 11/20/2019 Version: All ESS (Elliott SPS Services) is a cloud hosting service that translates incoming EDI XML files (i.e., 850) from SPS Commerce to the Elliott Sales Order Import format (fixed length ASCII), and translates the ...

    • SPS Document Processing Error on AllowChrgAmt

      Release: 12/31/2019 Updated: 7/10/23 Version: All Q - I received an email from SPS Commerce showing this message: InvoiceNumber:708241 InvoiceNumber:708241 Missing element: expect AllowChrgAmt before ending ChargesAllowances in line 106,column 25 ...

    • Feature - Skip Orders With Duplicate PO Numbers in Sales Order Import

      Release Date: 5/21/2024 Revised Date: 08/27/2024 Version: 8.5 and Up Background Sales Order Import would flag orders with duplicate purchase order numbers in Elliott as a warning, but allow the orders to import and place them on hold. This behavior ...

    • Feature - Sales Order Import Places Orders With Duplicate Purchase Order Numbers On Hold

      Release date: 8/20/2021 Version: 8.5 and Up Sales Order Import will now place orders that meet the duplicate purchase order number criteria on hold and a hold note will be attached to the order header. Earlier versions of the program would give a ...