AddonSoftware Administration - Create Shipment Tracking View

 

Function

This task creates a database view for the given firm that can be used to interface AddonSoftware with 3rd party shipping software. The database view allows the 3rd party shipping software to pull needed data from the installation's AddonSoftware database. Refer to the Setup for Carrier Data Exchange Interface details below for information on configuring the interface.

 

=> To access this task...

Select Create Shipment Tracking View from the Utilities menu.

 

Create Shipment Tracking View

 

 

Setup for Carrier Data Exchange Interface

 

Purpose

 

As of version 18.10, the Carrier Data Exchange interface provides Addon with direct access to package shipment information, such as freight amount, from 3rd party shipping software, such as FedEx and UPS.

 

At a Glance

 

Three things are needed for the interface to 3rd party shipping software such as FedEx and UPS:

 

  1. A database view of the data the 3rd party shipping software will pull from Addon.

  2. A database table for the data the 3rd party shipping software will push to Addon.

  3. An ODBC connection the 3rd party shipping software can use to access the Addon database.

 

Data Pull

 

The Create Shipment Tracking View task on the AddonSoftware Administration - Utilities menu creates a OPV_SHIPTRACK_nn view in the Addon database for the data the 3rd party software pulls from Addon. Enter the Firm ID of a firm that will be interfacing with the 3rd party software, and it creates the necessary view. The view name will be OPV_SHIPTRACK_nn where nn is the entered Firm ID. The technician configuring the 3rd party software will need to know the name and structure of the view, and the Database it is in.


Field Name Type Description
FIRM_ID varchar Company ID
AR_TYPE varchar Always blank
CUSTOMER_ID varchar Customer Number
ORDER_NO. varchar Order Number
SHIP_SEQ_NO varchar Order shipment sequence number.  This is automatically incremented each time the order is backordered and is written back to the tracking history file to allow tying shipments to the order iteration.
SEARCH_FIELD varchar Field searched from the 3rd party shipping software
NAME varchar Company Name
ADDR_LINE_1 varchar Address Line 1
ADDR_LINE_2 varchar Address Line 2
ADDR_LINE_3 varchar Address Line 3
ADDR_LINE_4 varchar Address Line 4
CITY varchar City
STATE_CODE varchar State Code
ZIP_CODE varchar Postal Code
CONTACT_NAME varchar Contact Name
EMAIL_TO varchar Contact/Company Email Address
COUNTY varchar County of the address (long name)
CNTRY_ID varchar Country ID (short name)
AR_SHIP_VIA varchar Addon Ship Via designation
SHIPPING_ID varchar 3rd party shipping account ID
SCAC_CODE varchar Standard Carrier Alpha Code
CARRIER_CODE varchar Carrier service code. (Mapped to AR_SHIP_VIA in ARC_SHIPVIACODE table.)
SHIPTO_TYPE varchar Addon designation to show if this was shipped to the billing address, ship-to address, or manual address in the ERP application.
CUSTOMER_PO_NO varchar Customer's Purchase Order Number

Data Push

 

The OPT_SHIPTRACK table in the Addon database holds the data the 3rd party software pushes to Addon. The technician configuring the 3rd party software will need to know the name and structure of the table, and the Database it is in.

 

Table: OPT_SHIPTRACK
Description: This table is where the 3rd party shipping software writes to after the shipments are posted. Immediate update is preferred.

 

Field Name Source Type Description
FIRM_ID OPV_SHIPTRACK_nn varchar Company ID (set in view query). Data pass thru from view.
AR_TYPE OPV_SHIPTRACK_nn varchar Always blank. Data pass thru from view.

CUSTOMER_ID

OPV_SHIPTRACK_nn varchar Customer Number. Data pass thru from view.
ORDER_NO OPV_SHIPTRACK_nn varchar Order Number. Data pass thru from view.
SHIP_SEQ_NO OPV_SHIPTRACK_nn varchar Order shipment sequence number. Data pass thru from view.
TRACKING_NO Shipping Software varchar Package Tracking Number
VOID_FLAG Shipping Software varchar If tracking number is voided, a new line may be added to the table with void_flag = 'Y'
SCAC_CODE OPV_SHIPTRACK_nn varchar Standard Carrier Alpha Code. Data pass thru from view.
CARRIER_CODE Shipping Software varchar Carrier Service Code for how this package was actually shipped.
CREATE_DATE Shipping Software varchar Package Ship Date as YYYYMMDD varchar
WEIGHT Shipping Software decimal Package Weight
CUST_FREIGHT_AMT Shipping Software decimal Package Freight Cost to the receiver
ACT_FREIGHT_AMT Shipping Software decimal Package Freight Cost to the sender

 

ODBC Connection

 

In order for the 3rd party shipping software to access the Addon database, a BBj ODBC Driver must be configured for the database. Refer to the Creating System or User Data Sources with the ODBC Administrator document for instruction on how this is done. (http://documentation.basis.com/BASISHelp/WebHelp/b3odbc/create_sysr_user_ds_winodbc_admin.htm)

 

It is very important to provide adequate security when exposing a database via ODBC. The Remote Database Security document provides information for securing the database. (http://documentation.basis.com/BASISHelp/WebHelp/eclipse-em/db-remote_database_security.htm)

 

The technician configuring the 3rd party software will need to know the Server name and port, and the Database name.



______________________________________________________________________________________

Copyright BASIS International Ltd. AddonSoftware® and Barista® Application Framework are registered trademarks.