Response 9x Manifest ODBC Interface
Manifest ODBC Interface Setup – v7.x, 8x. 9.x
Overview:
This document only describes the setup procedure for enabling the ODBC manifest interface. Please refer to the separate documentation (Manifest_general.doc) for information on how to setup Response for Windows and use the Manifest Interface views.
In the past, data was transferred to the manifest through via intermediary files which were alternately written to and read in order to pass the data to the manifest software and vice versa. The advantage of ODBC is that the data is written directly between the application databases without any intermediate steps or physical files required. All that’s required is to “map” the fields appropriately from one database to the other.
An in-depth knowledge of configuring ODBC data sources may be required here. Regardless, this is not a task to be undertaken by inexperienced users. If you lack the necessary expertise in-house, please consider bringing in outside assistance. Alternatively, CoLinear can provide Technical Consulting along these lines. However, because ODBC connectivity is beyond the realm of routine Response support, such assistance shall be billable.
Manifest Options
Response offers a configurable manifest interface that can theoretically be used with any manifest program that supports ODBC. The most common manifest systems being used with Response include Starship, CPS, Worldship and Clippership. Each of these manifest programs have their own unique configuration requirements for which we’ve created a unique SQL view that you’ll use to ”export” your Response shipping data.
Please click the appropriate link below to see vendor-specific configuration instructions:
CLIPPERSHIP
CPS
STARSHIP
UPS WORLDSHIP
Sending Response data to Manifest for orders to be shipped:
The following list of tables with their fields are the most commonly used tables for sending data to the manifest software. . The most important is MEXPORT. Be sure your order/shipto is in MEXPORT before you gather any other data from the other tables. The other tables will hold the data that is related to (but not available in) MEXPORT. Of course you will need some (but not all!) data from the other related tables. We provide a “SQL View” for some interfaces like CPS and UPSWS. The SQL View makes getting all the data MUCH easier by letting you see it all in one place. The SQL View is explained a little more below.
TIP:
The following view in Response (addons > manifest > File Based > export shipments to manifest) shows you records in the MEXPORT table/file. ODBC users can look at this view for reference if needed.
If you are using ODBC, you must be able to see the records here before you can send them to your manifest software (though viewing them here first for ODBC is not required).
On with ODBC Setup…..
7/22/08 the following is for Windows XP. Other Windows versions may look different. Please discuss this with your Network Administrator if you have questions. CoLinear support cannot provide more than this example for setting up a DSN.
Use the ODBC Datasource Administrator provided by Windows. You must create a USER or SYSTEM DSN for you SQL Server. Use SQL Server authentication
configure a User DSN (System DSN should also work) on the computer that runs your Manifest.
Find and open ODBC Administrator. (try Control Panel > Performance and Maintenance > Administrative Tools)
Hit the “add” button under the User DSN tab.
Select “SQL Server” from the list of drivers and hit the “Finish” button.
Type the name of the Data Source, I used “UPSR4W”.
No description is needed.
The name of the server can be chosen by hitting the drop down button. You may also enter an IP address if the name is not there. This is not unusual if your server is at the other end of a WAN Link, but could be an indication of trouble if you are on the same local LAN as the server.
Make the next screen look like this:
You do not have to change anything in “Client Configuration” unless you are trying to penetrate a firewall or proxy server.
Enter your user and password for your SQL Server (same as Mertech login)
Choose Next
On the next screen you have to check the box for “Change the default database “ and choose “R4W_001” in the drop down box. If R4W_001 is not there you have failed to connect to the Server. Take the checks out of the boxes for Use ANSI…..
Here’s the last screen, choose FINISH
The next screen has a summary and a “Test Data Source” Button. Press Test if you want, but if you got this far it will be successful.
END ODBC DSN Setup 7/22/08
Note: UPS Worldship or CPS users should point to a SQL VIEW named UPSR4W (or CPSR4W view) instead of the several tables below In these “Views” you will find the equivalent fieldnames as described below. A SQL VIEW (as opposed to a SQL table) is a kind of mirror of the six tables below (mexport, sysoent, shipto, etc) combined into one. This “mirror” or SQL VIEW file is created behind the scenes by SQL server . Explaining how a SQL view is created or works is beyond the scope of this document. NOTE: at the end of this document is a sample of the Syntax in UPSR4W.sql which creates a SQL View. You could execute this SQL Query to create your SQL View. This may be helpful for users who are NOT using CPS or UPS, you’ll still want to create a SQL view and this one could be a starting point for you. If you want us to create a custom SQL view for you, this could be done at a billable project. Contact for more info.
The object is for the UPSR4W or CPSR4W or other SQL view you create for Manifest, to be used when accessing Response data with ODBC.
(tech note: the SQL VIEW pulls info from MEXPORT, but joins its record with M_SHIP to get the shipping type.)
The SQL View will be available to ODBC and therefore to your Manifest system via ODBC and may contain the following fields: (This view is a combination of fields from the 6 tables listed in detail following.)
shipto_id (from mexport)
first_name (from shipto)
last_name (from shipto)
company_name (is first+last if company blank)
attn_line (from sysoent)
address_line1 (from shipto)
address_line2
address_line3
address_line4
address_line5
city
state
zipcode
a1
phone_area (from shipto)
phone_exch
phone_ext
phone_exten
m_ship_desc (the manifest crossref description)
email1 (from sysoent or shipto)
status (mexport.status)
order_no
Division_ID = Mexport.Division_ID (multi-co only)
residential
Y (always Y, special request by others who needed a field with a Y value)
Manifest Interface Export – MEXPORT (#41):
The table MEXPORT is the primary table for determining which orders are available to be shipped. This is the only table you should write to during the reading process (when ODBC “reads” the data to send to manifest). This table drives what you see in the SQL view.
Name / Description / Type / Can be Linked toShipto_ID / Order Number or Invoice Number / Numeric / Shipto.shipto_id, Pickbatch.Batch
Export#_Today / Carton Number – Usually 1, but if the order/shipto prints more than once in a day this will indicate which printing this represents. / Numeric
Print_Batch_No / The print batch ID for the picking tickets / Numeric / Batches.Batch, Picbatch.Batch
Export_Type / If setup this will be the Ship Type defined in the Ship Method Cross Reference file for this order’s shipping method. / ASCII / M_Ship.M_Ship_Type
Export_Date / This is the date when this record was exported to your manifest software. You should write to this field when you have read in all the information for this order. / Date
Export_Time / This is the time when this record was exported to your manifest software. You should write to this field when you have read in all the information for this order. The format should be HH:MM. / ASCII
Shipmeth_Code / This is the Response shipping method code for this order. / ASCII / Shipmeth.Shipmeth_Code
Status / E – Entered/Ready to Export, H – On Hold, X – Exported. You should only read in record whose status is E. (ODBC will read the order information regardless of status, so it’s not necessary to change status to X after shipping) / ASCII
Division_ID / If you have a multi-company or multi-division setup, this field will indicate which division, this record is for. / ASCII
Order Shipto Info –SHIPTO (#13) and SYSOENT (#2):
These are the two main order tables. These tables contains the shipping address, shipping method and order totals. The following are a list of the most commonly used fields (from both tables) for manifest. This is not a complete list. Note in the Name column the use of the table.fieldname format because two tables are involved here. The information you need from these tables for Manifest will be in the SQL view.
Name / Description / Type / Can be Linked toShipto.Shipto_id / The unique Shipto ID for the order / Numeric / Mexport.shipto_id, Pickbatch.Batch
Sysoent.Order_Date / The date the order was taken. / Date
Sysoent.PO_Number / The purchase order number for the customer. / ASCII
Sysoent.Customer_ID / The customer number. / Numeric / Syscust.Customer_ID
Shipto.Shipmeth_Code / The Response shipping method code for this order. / ASCII / Shipmeth.Shipmeth_Code
Shipto.Company_Name / The company name for the shipping address. / ASCII
Shipto.Last_Name / The last name for the shipping address. / ASCII
Shipto.First_Name / The first name for the shipping address / ASCII
Shipto.MI / The middle initial for the shipping address / ASCII
Shipto.Salutation / The surname for the shipping address. Example: Dr., Mr., Ms. / ASCII
Shipto.Address_Line1 / The first address line for the shipping address. / ASCII
Shipto.Address_Line2 / The 2nd address line for the shipping address. / ASCII
Shipto.Address_Line3 / The 3rd address line for the shipping address. / ASCII
Shipto.Address_Line4 / The 4th address line for the shipping address. / ASCII
Shipto.Address_Line5 / The 5th address line for the shipping address. / ASCII
Shipto.City / The city of the shipping address. / ASCII
Shipto.State / The state of the shipping address. / ASCII / Sysstate.State_Abb
Shipto.ZIPCode / The ZIP Code of the shipping address. / ASCII
Shipto.Country / The country of the shipping address. Note: Some manifest systems may have a different country spelling or case that the ones stored in Response for Windows. If so, you may want to use the Manifest Country Crossreference file to lookup your manifest systems countries. / ASCII / M_Cntry.M_Country, Country.Country_Name
Shipto.Phone_Area / The area code of the shipping address. / ASCII
Shipto.Phone_Exch / The 3-digit exchange of the phone number of the shipping address. / ASCII
Shipto.Phone_Ext / The last 4 digits of the phone number of the shipping address. / ASCII
Shipto.Phone_Exten / The extension of the phone number of the shipping address. / ASCII
Shipto.Attn_Line / The attention line of the shipping address. / ASCII
Sysoent.Next_Ship / The requested shipping date of the order. / Date
Sysoent.Payment_Code / The Response payment method code for this order. / ASCII
Sysoent.Gross / The total of the item’s amounts ordered. / Numeric
Shipto.Ship_Charge / The shipping charge for this order. / Numeric
Sysoent.Misc_Charge / The miscellaneous change for this order. / Numeric
Sysoent.Discount_Amt / The discount amount for this order. / Numeric
Sysoent.Bal_Due / The total amount due for this order. / Numeric
Sysoent.Sales_Tax / The total tax charged on this order. / Numeric
Sysoent.Total_Num_Items / The total number of items on this order. / Numeric
Sysoent.Total_Wt / The total weight of this order. Note: This is based on the weight setup in inventory. The inventory weight may not include the shipping a packing materials. / Numeric
Line Items – LINEITEM (#23):
This table contains the line items ordered. The following are a list of the most commonly used fields for manifest. This is not a complete list. The information you typically need from this table for Manifest will be in the SQL view.
Name / Description / Type / Can be Linked toShipto_ID / Order Number or Invoice Number / Numeric / Mexport.shipto_id, Pickbatch.Batch
Line_No / The line item number. / Numeric
Order_Date / The date the order was taken. / Date
Item_ID / The inventory item ID of the item ordered / ASCII / Inventor.Item_ID
Item_Price / The sale price of this item on this order. / Numeric
Sugg_Ship_Qty / The number of items that will be shipped on this shipment. Note: If this is zero, you should not consider this to be part of the current shipment. / Numeric
Message / The line item message that prints out on picktick. / ASCII
Kit_Flag / Blank – Not associated with a kit. Y – this item is a kit master and the line items following will be components in it. K – this item is a kit component. / ASCII
Manifest Shipmeth Crossreference – M_SHIP (#46):
This table is a cross-reference table for linking Response for Windows shipping methods with your manifest system’s shipping methods. You only need this table if the shipping methods names are different in the two systems (they usually are).
Name / Description / Type / Can be Linked toM_Ship_Key / The shipping method code/ID used by your manifest software. / ASCII
M_Ship_Desc / The shipping method description used by your manifest software. / ASCII
Response_Ship / The Response shipping method. / ASCII / Shipmeth.Shipmeth_Code, Sysoent.Shipmeth_Code, Mexport.Shipmeth_Code
M_Country / Some manifest systems have different shipping methods for different countries. For example, UPS Ground to the USA might be different than UPS Ground to Canada. In Response for Windows, generally you will just setup on shipping method for all countries. This field allows Response for Windows to link a single shipping method for all countries to several different manifest software shipping methods for each country defined. / ASCII / M_Cntry.M_Country
M_Ship_Type / The shipping method type used by your manifest software. / ASCII
COD / Y – COD charge on this shipping method. N – no COD charge on this shipping method. / ASCII
COD_Terms_Key / The COD terms key used by your manifest software / ASCII
Call_Tag / Y – This method has a call tag. N – This method does not have a call tag. / ASCII
Manifest Country Crossreference – M_CNTRY: (#45)
This table is a cross-reference table for the country definitions in Response and your manifest software. This table is only necessary if Response for Windows country list and your manifest software’s country list differ.