The Lessons We Learned from Upgrading Web Service 8.2 to 8.5 in eStore

Elliott Web Service 8.5 changed ORDER_NO, INV_NO, and other keys from numeric to alphanumeric to accommodate a bigger range. This results in changes in the Web Service interface. This article documents our experience when we upgraded the Web Service from 8.2 to 8.5 in Elliott eStore.

Elliott eStore is written in csharp and all codes are strongly typed. The process of the upgrade is relatively straightforward. The main steps are:
  1. Install Elliott Service 8.5.
  2. Configure all service references to point to the 8.5 Elliott Service and update all service references.
  3. Compile -> which will result in many compilation errors due to date type changes in the service references.
  4. Fix each error. The fixes are simply changing data type from int to string.
  5. Test.

For the upgrade, the actual code change is trivial. It's simply updating the data type from int to string. The most difficult part of the upgrade is to identify all the areas that need to be updated. If your code base is written in a strongly typed language where the compiler will point you to all the areas, the upgrade process is straightforward. However, if you don't have an easy way to identify all the areas that need to be updated easily in your application, the following side notes will be helpful to understand what problems you might run into if you miss an area. 

In the initial eStore upgrade, we only updated EliorderService and tested order creation. The order was created successfully but it wasn't showing in the eStore order list page. The reason: the order list page uses OrderInquiry service, which was also updated. The OrderInquiry service has many filters, one of which is the ORDER_NO. The order list page does not use the ORDER_NO filter. Prior to the upgrade, it specified 0 (the default value for int), which is "nothing" on the Web Service side, and the filter would not be applied. After the upgrade, the ORDER_NO is a string and specifying 0 would no longer be "nothing" on the Web Service side. As a result, the Web Service was trying to match an order with order no = "0." This was easily resolved once we identified what was happening and updated the order list page.

There are many Elliott Web Service methods that  return a dataset in the response. Dataset is not strongly typed. Even in a strongly typed language code base, the compiler would not give us any warnings if we accessed a data column that had been updated to string. These codes would fail at run time. To identify this type of potential problem, we searched for "ORDER_NO" and "INV_NO" (e.g., DataRow["ORDER_NO"] which is a common way to access data in DataSet). We reviewed the result and made any necessary changes.

Another area to consider is any code that uses the QueryTurnaround method. Since document number are now string instead of int type fields, queries may require single quotes around the document number. In our testing we have seen that a query like this will work correctly against a converted 8.5 database:
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO = 677506;    

However, a query like this will fail:
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO > 677500;

Adding the single quotes around the order number field will resolve the issue.
select * from "CPBOXSHP" WHERE CPBOXSHP_ORD_NO > '677500';  

This 8.2 QueryTurnaround code example will fail with an 8.5 database:

Dim Service As New webServicesQueryTurnaround.QueryTurnaround()
Dim Result As New webServicesQueryTurnaround.ExecuteQueryResult
Dim query As String = “ORDER_NO, ORDER-DATE, ORDER-TYPE, ORDER-SHIP-TO-NO FROM CPORDHDR WHERE ORDER_NO > 112487 AND ORDER_NO < 115877”
Dim numberOfRecords As String = “999”
Result = Service.ExecuteQuery(“”, “”, query, numberOfRecords)

If Result.ReturnCode = 0 Then
  If Result.Data.Tables(0).Rows.Count > 0 Then
    lblResult.Text = “First Matching Order No. is” & _
      Result.Data.Tables(0).Rows(0).Item("ORDER_NO")
  Else
    lblResult.Text = “No Matching Order.”
  End If
Else
  lblResult.Text = “Error”
  lblErrorCode.Text = Result.ReturnCode.ToString
End If

To resolve the issue, the query should contain single quotes around the order numbers:
Dim query As String = “ORDER_NO, ORDER-DATE, ORDER-TYPE, ORDER-SHIP-TO-NO FROM CPORDHDR WHERE ORDER_NO > '112487' AND ORDER_NO < '115877'”

Also, you will receive an error if running an 8.5 web service and the ElliottService configuration file is configured to use a ServerDSN that is not using version 8.5 DDFs. For example, if using the QueryTurnaround service with the following query:

  Dim query As String = “ORDER_NO FROM CPORDHDR WHERE ORDER_NO = 'AA0011'”  

You will receive the following error:

Pervasive.Data.SqlClient.PsqlException (0x80004005): Pervasive.Data.SqlClient.Lna.k: [LNA][PSQL][SQL Engine]Error in predicate: ORDER_NO = 'AA0011' 

To resolve this, you must use a ServerDSN that is using version 8.5 DDFs.
  
The following table lists all of the services, methods and the structures that were changed for V8.5. 

Service

Method or Structure

Change

El2crcrdService

Structure: SalesTrxInput

INV_NO

Structure: SalesTrxResult

CreditDocumentNo

Structure: ForceInput

INV_NO

Structure: ForceResult

CreditDocumentNo

Structure: RefundInput

INV_NO

Structure: RefundResult

CreditDocumentNo

El2getfrService

GetFrtByOrder

OrderNo

EliaptrxService

Structure: CreateTrxHeaderInput

VoucherNo, CheckNo

Structure: CreateTrxHeaderResult

VoucherNo, InvoiceNo,CheckNo

Structure: CreateTrxDistInput

VoucherNo

Structure: CreateTrxDistResult

VoucherNo, InvoiceNo, CheckNo, ApplyToNo

PostTrx

VoucherNo

Structure: PostTrxInput

VoucherNo

Structure: PostTrxResult

VoucherNo, CheckNo

EliarachService

Structure: ViewOrderACHAcctInput

OrderNo

Structure: AssignOrderACHAcctInput

OrderNo

EliautdpService

Structure: AutoDepositInput

ApplyTo

Structure: AutoDepositResult

CheckNo

ElibmordService

AddWorkOrder

OrderNo

ChangeWorkOrder

OrderNo

DeleteWorkOrder

OrderNo

PrintWorkOrder

OrderNo

AddProductionTrx

OrderNo

ChangeProductionTrx

OrderNo

DeleteProductionTrx

OrderNo

PostProductionTrx

OrderNo

Structure: AddWorkOrderResult

OrderNo

ElicshtxService

DeleteCashTrx

CheckNo

Structure: CashTrxForAdd

CheckNo

Structure: CashReceiptApplyToForAdd

ApplyToNo

EliinvocService

ReadHeader

INV_NO

ReadInvItem

INV_NO

Structure: ReadHeaderResult

INV_NO, INV_APPLY_TO_NO, INV_CHECK-NO, INV_ORDER_NO

Structure: ReadInvItemResult

INV_ITM_INV_NO, INV_ITM_ORG_BK_ORDNO, INV_ITM_BM_ORDER_NO

EliorderService

DeleteOrder

ORDER_NO

FinishOrder

ORDER_NO

ChangeOrder

ORDER_NO

ReapplyCredit

ORDER_NO, ORDER_CHECK_NO

ViewOrder

ORDER_NO

ViewLineLs

ORDER_NO

RecreateOrder

OrderNo

CancelOrder

OrderNo

CloseOrder

OrderNo

CloseOrCancelOrder

OrderNo

Structure: CreateHeaderInput

ORDER_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_CHECK_NO, ORDER_INVOICE_NO

Structure: ChangeHeaderInput

ORDER_NO, ORDER_CHECK_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_INVOICE_NO

Structure: CreateLineItemInput

LINE_ITM_ORDER_NO, LINE_ITM_ORD_NO_ALT1, LINE_ITM_ORD_NO_ALT2, LINE_ITM_BM_ORDER_NO, LINE_ITM_BKT_ORD_NO

Structure: CreateHeaderResult

ORDER_NO

Structure: ViewOrderResult

ORDER_APPLY_TO_NO, ORDER_CHECK_NO, ORDER_INVOICE_NO, ORDER_NO

Structure: ViewLineLSResult

ORD_LINE_LS_ORD_NO

Structure: CreateOrderResult

OrderNo

Structure: RecreateOrderResult

OrderNo

Structure: CreateOrderInput

ORDER_NO, ORDER_APPLY_TO_NO, ORDER_NO_ALT, ORDER_CHECK_NO, ORDER_INVOICE_NO

Structure: LineItem

LINE_ITM_ORDER_NO, LINE_ITM_ORD_NO_ALT1, LINE_ITM_ORD_NO_ALT2, LINE_ITM_BM_ORDER_NO, LINE_ITM_BKT_ORD_NO

Structure: SendOrderConfirmationEmailInput

ORDER_NO

EliordiqService

Structure: OrderInquiryOrderNoInput

OrderNo

InvoiceInquiry

GetOneInvoiceDetail

invoiceNo

OrderInquiry

GetOrder

orderNo

GetOneOrderDetail

orderNo

VYC, CLS

Elliott Version 8.5

  1. V8.5 Alpha Document Number Support
  2. Crystal Report Conversion for V8.5
  3. The Run Local or Hybrid Feature in Elliott 8.5
  4. How to Use EL850DB.EXE Utility to Create PSQL Databases
  5. Showing Document Date in Links After Elliott 8.5
  6. The Lessons We Learned from Upgrading Web Service 8.2 to 8.5 in eStore
  7. Feature - ASCII File Filter Condition in Mass Email and Export Processor
  8. Feature - List Users Acively Logged into Elliott and Optionally Send Email
  9. Feature - Allow Print One Invoice to Print Duplicate Invoices for Order Not Posted
  10. Feature - Print Range of Duplicate Unposted Orders
  11. Feature - Prevent Duplicate Invoice Numbers with Invoice Cross Reference File
  12. Feature - Last Year Net Income Layout Support
  13. Receive Message "Not Found" When Trying to Set Up Run Local
  14. Receive 'Invalid Column Name' Message After Starting to Use Alphabetic Document Numbers
  15. Feature - Invoice Log Report
  16. Feature - Add Sales or Usage to Where-Used inquiry
  17. Feature - Case Size Support for Quantity Per Box
  18. Feature - Exclude Obsolete Customers from Customer Generic Search
  19. Feature - Refresh VICS Bill of Lading
  20. Feature - Support Item Case Size in New Item CSV Import and Change Existing Item Import
  21. Feature - Copy Notes, Attributes, and Links When Copying Item Through the New Item CSV Import
  22. Feature - Enhanced Cash Receipts Processing Apply-To Range
  23. Feature - Add Links Import to System Files Setup
  24. Feature - Shipment Email Acknowledgement for Bill-To Customers
  25. Feature - Restrict Sending Tracking Number to Bill-To Customer in Shipment Email Acknowledgement
  26. Feature - Allow Source Range on Credit Card Log Report
  27. Feature - Indented Bill of Material CSV
  28. Feature - Stock Status Reports Add Include with Alloc or On Order Quantity Option
  29. Feature - Release Manually Held Orders
  30. Feature - Create Shipment Records When Complete Bill Of Lading
  31. Feature - Print Warning Msg for Cancelled PO Line Item
  32. Feature - Export 25-Character PO Number in Expanded Aging Report CSV File
  33. Feature - Show Pending Cash Receipts in Payment History Inquiry
  34. Feature - Show Customer Note Fields in Payment History Inquiry
  35. Receive "Error Getting List of Override Reports" When Printing Report
  36. Feature - Fields Added to User-Defined CSV to Support EDI 846
  37. Feature - Credit Memo Warning When Fully Paid
  38. Feature - Create Shipment Verification Box Records Through Billing CSV Interface
  39. Feature - Show Box ID on Order Inquiry Shipment Data Screen
  40. Feature - Print Error Message If Inventory Transaction Distributions Are Missing
  41. Feature - Customer CSV Import
  42. Feature - Alleviate Error 25 When Two Users Import Sales Orders Simultaneously
  43. Feature - Use Note Type for Special Instructions on VICS BOL
  44. Feature - VICS BOL to Enforce Shipment Verification Before Printing of BOL
  45. Feature - Order/Invoice Inquiry Box Serial Number Drill Down
  46. Feature - Delete Order Tracking Note When Void Completed VICS BOL
  47. Feature - Picking Ticket For Customer 2 Event
  48. Feature - Set All Options to Allow or Disable in User Global Security
  49. Feature - Stock Analysis Creation by Customer Range
  50. Feature - Print Work Order Planned Hours on Plus Work Order Traveler
  51. How to Ensure Local Elliott Folder Is Up to Date
  52. Feature - Global Setup Flag to Update Tracking Note from Box Data Window
  53. Feature - Packing List by Box One Box Per Page
  54. Feature - Move EDI Profile Maintenance Maximum # of Item Per Label
  55. How to Update Elliott 8.5 License Key Code
  56. Installing Elliott 8.5 and Received "PSQL v11, v12 or V13 Is Required" Message
  57. Feature - Warehouse Receiving Missing Item Attribute Event
  58. Feature - Added a Drill Down to Order by Item Inquiry in Newly Received Items Inquiry
  59. Feature - Customer Type Display in Pick Order Processing
  60. Elliott V8.5 Configuration Utility (EL850CF.EXE)
  61. Feature - Add Customer Type to Pick Ticket Selection
  62. Feature - Void Checks after Posting Warning for Multiple Checks
  63. Error 1315 Unable to Write to the Specified Folder When Installing Elliott 8.5

Feedback and Knowledge Base