Integrate with Third-Party Shipping Manifest System

Q - Can I integrate a third party shipping manifest system with Elliott Business Software?

A - Yes, you can. Our recommended solution is Starship by V-Technologies. It has built-in integration with Elliott Business Software out of the box.  The base package supports UPS.  With add-on options, it can also support FedEx, US Mail and others.

Over the years, we have heard users express the desire to use UPS's Worldship or FedEx's Ship Manager, which they wanted to integrate with Elliott. By going with the proprietary shipping manifest system, you lose the advantage of freight shopping, which you can do with Starship.  In addition, you are not tied down to a specific carrier in case there's a strike. On the other hand, we understand that you may get a special pricing discount from a carrier if you dedicate all your shipments to them. There are certain shipping manifest systems that are a better fit for handling international shipments and produce the required import/export declaration forms.  The key is whether the third-party manifest solution will support ODBC or ADO.NET integration.

If you choose to use UPS Worldship, FedEx Ship Manager, or another third-party manifest solution, the following procedure will provide necessary information for UPS and FedEx's technicians to integrate their shipping manifest system with Elliott.

Order Header Table Integration (CPORDHDR)

The first integration should take place with Elliott's COP Order Header table (CPORDHDR). This table contains all the header information for sales orders that are ready to ship. Elliott is based on the PSQL database, which supports ODBC and ADO.NET connections.  Per Elliott's convention, the Elliott data source name will be either ElliottData (V7) or EliData (V8).  This is the same data source name you use with Crystal Report Writer or other third-party tools to access Elliott's data.  Your UPS or FedEx rep will need this information to connect to Elliott's database.  If you have set up a password for the database, you will need to provide that too.  If the data source is not created, please call Netcellent's support team.

When you go to Pervasive's Control Center, you can locate Elliott's COP Order Header table as CPORDHDR.  Right click on this table and choose "Properties." See sample screen below:



In the Properties page, go to the "Columns" tab. You will see a list of columns for CPORDHDR table like the following:


The primary key of CPORDHDR is ORDER_NO.  The Elliott order number is printed on the pick ticket and can be optionally bar coded.  Therefore, the likely way to interface with a third-party manifest system is to use Elliott's order number.  When the manifest prompts for Elliott's order number, you can simply scan the order number bar code on the pick ticket.  For a manifest system that integrates through ODBC or ADO.Net, it can simply issue a SQL statement like the following:
    SELECT * FROM CPORDHDR WHERE ORDER_NO = 123456
where 123456 is the order number input or scanned by the user in the manifest system.

A few important things to point out to integrate with CPORDHDR table:
  • Map to the Ship To address, not the Bill To address.  The Ship To address columns are: ORDER_SHIP_TO_NAME, ORDER_SHIP_TO_ADDR_1, ORDER_SHIP_TO_ADDR_2, ORDER_SHIP_TO_CITY, ORDER_SHIP_TO_ST, ORDER_SHIP_TO_ZIPCD and ORDER_SHIP_TO_COUNTRY.
  • You should consider printing Order Shipping Instructions on the labels. The columns are ORDER_SHIP_INSTRUC1 and ORDER_SHIP_INSTRUC2.  You may, for example, use ORDER_SHIP_INSTRUC1 as the "attention" or "contact" information. Some users use ORDER_PURCH_ORDER_NO as the "attention."  In any case, adopt a convention and stick to it.
  • In Elliott's Order Header screen, you will notice the purchase order number is 25 digits.  However, in the CPORDHDR table, the purchase order number is split into two parts: ORDER_PURCH_ORDER_NO is the first 10 digits and ORDER_EDI_PO_NO_CONT is the last 15 digits.  You should concatenate these two columns to get your entire 25-digit PO number.
  • Elliott's freight pay code determines whether the order freight is prepaid, collect or bill to a third party.  The column name is ORDER_FRGHT_PAY_CODE, and the value can be C=Consignee, P=Prepaid, T=Third party, R=Recpt(collect), S=Sender, H=Hold or blank, which means "Prepaid" as well.  Be aware that in Global Setup -> Order Header, if the flag "22. Does Frt Status 'P' Means Prepaid & No Charge?" is set to "Y," then it means "P=Prepaid & Free" and the blank value means "Prepaid & Billable."  This will affect whether or not the manifest system should write back the freight amount to Elliott.
  • When you use third-party billing, you will need to provide the third party's account to UPS or FedEx. Typically, we store that information in user-definable CUS_NOTE_1 to 5 columns.  To look up information from the customer table (ARCUSFIL), your technician will use the ORDER_CUSTOMER_NO (foreign key) to look up the ARCUSFIL table with its primary key CUS_NO in order to retrieve the CUS_NOTE_1 to 5.  It is quite possible that you need to store both UPS and FedEx third-party billing accounts depending on your customer's preference.  Therefore, you will need two columns from CUS_NOTE_1 to 5.  Do not store UPS account in FedEx column and vice versa.  Otherwise, you will get errors in your manifest system.  Make a decision on which column is for UPS and which column is for FedEX account, and stick to it.
  • Your manifest system may need to map a customer's phone number.  You may retrieve that from ARCUSFIL.CUS_PHONE_NO by using the same principle.  Another alternative is to configure the Global Setup so the system automatically copies the eContact's phone number to ORDER_COMMENT1 - 3.  Please refer to Global Setup -> System -> eContact Manager. Flag "11. Copy eContact Telephone to Order?"
  • The third-party manifest system should calculate insurance based on the ORDER_TOTAL_COST or ORDER_TOTAL_SALE_AMT.  However, these two fields' values are not guaranteed to be correct until the order is selected for billing.  The only way to guarantee the correct total amount is to calculate from Order Line Item table (CPORDLIN).
  • To calculate the COD amount, the manifest should consider the amount paid with this formula: ORDER_TOTAL_SALE_AMT + ORDER_MISC_CHRG_AMT + ORDER_FREIGHT_AMOUNT + ORD_SALES_TAX_AMT_1 + ORD_SALES_TAX_AMT_2 + ORD_SALES_TAX_AMT_3 - ORDER_PAYMENT_AMOUNT.
  • If you wish to bill your customer for the actual freight, it is possible for the manifest to write back the ORDER_FREIGHT_AMOUNT. There are always exceptional situations where the freight amount should not be written back.  That's why the Manifest should look at the ORDER_FRGHT_PAY_CODE column as explained above.  In addition, the manifest should never write back the ORDER_FREIGHT_AMOUNT in the following conditions: (1) If the Order is invoiced or posted; (2) If the Order Type is "O" and the order is selected for billing. The column ORDER_TYPE determines the type of order ("O" = Order, "I" = Invoice).  The column ORDER_SELECTION_CODE determines the order status ("C" = complete, "S" = Selected, "X" = Invoice Printed, "Z" = Posted).
  • You should provide a list of Ship Via Codes to your technician. For example "UG" may mean "UPS Ground," "U1" may mean "Next Day"...etc.  The Ship Via Code is in ORDER_SHIP_VIA_CODE.
  • You should provide the terms code that represents COD to your technician. The terms code is in ORDER_TERMS_CODE.

Notes Table Integration (NOTES)

The tracking number should be written back to the Elliott Notes database.  If it is written back to Elliott correctly, Elliott can utilize the information for many purposes, including online tracking shipment status, printing manifest report and sending shipping acknowledgment emails.

In PSQL, you may see both NOTES and NOTE tables.  They all point to the same physical data file NOTE.BTR.  However, since the word "NOTE" is a reserved word in SQL, we prefer that you use the NOTES table for interfacing.  The following is a list of columns in the NOTES table.



We suggest that each tracking number and its associated information like weight and freight amount is written back to Elliott as an order note.  That is to say, if you have three packages for an order, then write three records to the NOTES table.  Ask your technician to insert shipping data into this NOTES table by using the following rules:

Note_File_Name: This field is to be hard-coded with the value “CPORDHDR.

Note_File_Ref_No: It is important to know that this a string column in the NOTES table, but you need to store the Elliott order number, which is 6 digits numeric, in the text format and padded with leading zeros.  For example, Elliott Order# 1234 should be saved as “001234.” Do not leave any junk note records behind as you do the test. Those junk note records may cause problems for Elliott Business Software down the road. If you want know what kind of problem may happen when you leave junk notes records behind, see the following Knowledge Base article:

    http://support.elliott.com/knowledgebase/articles/630157-an-example-of-debugging-note-ord-view-psql-express

Note_Folder: This field should be left blank.

Note_Create_Date: This is the current date, which is an 8-digit number in the format of YYYYMMDD.  For example, 3/23/2005 will be stored as 20050323.

Note_Create_Time_HH: This is the hour portion of the current time in a 24-hour format.

Note_Create_Time_MM: This is the minute portion of the time.

Note_Create_Time_SS: This is the second portion of the time.  If you write multiple Note records for the same order due to multiple tracking numbers, you should avoid duplication by increasing the second field by one for each record.  The primary key of the note table consists of Note_File_Name, Note_File_Ref_No, Note_Folder, Note_Create_Date and Note_Create_Time (HH+MM+SS).  They can not be duplicated.

Note_Type: You can either leave it blank or populate it with a valid Elliott Note Type.  If wish to print this tracking Note on various Order documents, like the packing slip or the invoice, then a valid Note Type must be populated.  In addition, the Note Type Setup will indicate to print it on the appropriate COP document.

Note_Create_By_User: This must be hard-coded with “STARSHIP.”  This is because our first shipping manifest integration is with Starship. Elliott looks for notes created by “STARSHIP” as an indication that this is a shipment tracking note. Elliott shipment tracking functions will not work if another name is used.

Note_Task_Status: Hard-code this field with numeric “0.”

Note_FollowUp_By_Nam: Leave this field blank.

Note_FollowUp_Date: Populate with zero value.

Note_FollowUp_Time: Populate with zero value.

Note_File_Name_Alt: Hard-code the value as “CPORDHDR.

Note_File_Ref_No_Alt: Same as Note_File_Ref_No (i.e., “001234”).

Note_Topic: We suggest that you populate this field with the manifest system's internal transaction number plus the package ID.  This field is part of the NOTES alternate key and it is modifiable.  This makes it easier for the manifest system to update or delete Notes based on its internal transaction number.  For example, if a package is being deleted from the order through the manifest system, the manifest should delete the corresponding track notes from Elliott by using a SQL statement similar to the following:

  • DELETE FROM NOTES WHERE NOTE_FILE_NAME_ALT = 'CPORDHDR' AND NOTE_FILE_REF_NO_ALT = '001234' AND NOTE_TOPIC = '123456789-002'

Please note that these three fields represent the Notes table alternate key.  By utilizing the alternate key, the PSQL database engine can perform in an efficient manner.  The value '123456789-002' is an example of transaction ID '123456789' and '002' is the package ID. This is just an example. The actual value to populate NOTE_TOPIC can be determined by your technician. This column is for the ease of the third party manifest integration. So what value to populate (or not to populate at all) is at your own discretion.

It is not a good idea to delete or update tracking notes by using the primary key since it involves knowing the date/time stamp value, and the manifest system probably does not keep track of that.  You can't assume all notes for a particular order are for a tracking number.  Elliott stores other types of notes for the order, like credit hold reasons, shipping instructions...etc.

The minimum requirement for Note_Content fields 1 to 10 is for one of the fields to store the tracking number.  However, you must make sure the position of the literal and the value are in the correct columns.  Elliott recognizes three literals in the notes content 1 - 10 area: “Track #:”, “Weight”, and “Freight” must begin at column 3.  The values must begin with column 17.  Elliott references these literal values by their exact positions. These three literals must be matched exactly, including case.  They can be on any note content line.  Adding additional tracking information in the notes content area will not cause a problem for Elliott.

Note_Content_1:
Note_Content_2:
Note_Content_3:
Note_Content_4:
Note_Content_5:
Note_Content_6:
Note_Content_7:
Note_Content_8:
Note_Content_9:
Note_Content_10:

The following layout demonstrates the exact position of the literal and data in note content area

--------------------------------------------------
12345678901234567890123456789012345678901234567890
Ship Date:  99/99/9999 Pkg#: 1
  ShipVia:      UPS 2nd Day Air
  Track #:      1Z92752E0200007078
  Weight:       1
  Decl Value:   0
  Freight:      9.31
--------------------------------------------------

Example – FedEx Tracking Note



Example – UPS Tracking Note



It is crucial that the literal "Track #:" and the tracking number "1Z92752E0300010982" start at the exact position (3 and 17). If they do not, Elliott will not recognize the tracking number and as a result, many of Elliott's side integrations will not work.  A quick way to test if the position is correct is to bring up an order note with a tracking number and right click on the tracking number.  In the popup menu, if the top line shows the option to "OnLine with UPS" or FedEx, then it means the system recognizes the tracking number in the note and the position is correct.  Otherwise, it is wrong.

Note_Modify_Date: Make this the same as Note_Create_Date.

Note_Modify_HH: Make this the same as Note_Create_Time_HH.

Note_Modify_MM: Make this the same as Note_Create_Time_MM.

Note_Modify_SS: Make this the same as Note_Create_Time_SS.

Note_Modify_By_User: Hard-code this with “STARSHIP.”

Note_Readable_Flag: Hard-code this with “Y.”

Note_Changeable_Flag: Hard-code this with “Y.”

Note_Deleteable_Flag: Hard-code this with “Y.”

Note_Orig_Foll_Up_Dt: Hard-code this with zeros.

Note_Time_Foll_Dt_CH: Hard-code this with zeros.

Note_Filler_001: Populate with spaces.

Sample SQL Statement to Add Shipment Tracking Notes Record to Elliott


The following is a sample SQL statement to add a shipment tracking note to Elliott notes table. A few things to be noted are:
  • It is important to name all columns of NOTES table in the INSERT statement even if some of the columns are going to have blank or zero value. If you miss any NOTES column from your INSERT statement, it will result in null value for that column.  Since Elliott does not handle null character in its database, this will cause unexpected error down the road. The sample SQL statement provided here is based on Elliott 7.5 DDF.  If you are using newer version DDF, you will see more columns before NOTE_FILLER_001 column.  Adjust your SQL statement accordingly.
  • In this example, we add a note record for Elliott sales order 322 which is save in the NOTE_FILE_REF_NO and NOTE_FILE_REF_NO_ALT columns. Elliott order number is numeric, but NOTE_FILE_REF_NO is alpha numeric. You will need to pad leading zeros to 6 digits if your Elliott order number is less than 6 digits. Therefore, order number 322 become '000322'.
  • The internal date format in Elliott is YYYYMMDD, so the value 20160128 represents 01/28/2016.
  • In this example, we populate the time as 13:48:12 (military time in HH:MM:SS format). You must provide system time as you insert the note record. Since the last segment of Elliott NOTES primary key is the time, it is important you provide the system time to avoid duplicate value error. If you should insert two notes record into the same order, and they have the same system time. To avoid duplicate value error, you need to increment the 2nd note record time by 1.
  • We previous talk about the spacing and alignment issue, In this sample SQL statement, we use “  Track #:       1Z2088866811111112”. This is important if you wish Elliott to parse and recognize that tracking number correctly. Same for weight and freight value.
  • We make the modify user, date/time same as create user, date/time. This is not necessary. But this is how STARSHIP writes to Elliott notes.

INSERT INTO NOTES
(NOTE_FILE_NAME
,NOTE_FILE_REF_NO
,NOTE_FOLDER
,NOTE_CREATE_DATE
,NOTE_CREATE_TIME_HH
,NOTE_CREATE_TIME_MM
,NOTE_CREATE_TIME_SS
,NOTE_TYPE
,NOTE_CREATE_BY_USER
,NOTE_TASK_STATUS
,NOTE_FOLLOWUP_BY_NAM
,NOTE_FOLLOWUP_DATE
,NOTE_FOLLOWUP_TIME
,NOTE_FILE_NAME_ALT
,NOTE_FILE_REF_NO_ALT
,NOTE_TOPIC
,NOTE_CONTENT_1
,NOTE_CONTENT_2
,NOTE_CONTENT_3
,NOTE_CONTENT_4
,NOTE_CONTENT_5
,NOTE_CONTENT_6
,NOTE_CONTENT_7
,NOTE_CONTENT_8
,NOTE_CONTENT_9
,NOTE_CONTENT_10
,NOTE_MODIFY_DATE
,NOTE_MODIFY_HH
,NOTE_MODIFY_MM
,NOTE_MODIFY_SS
,NOTE_MODIFY_BY_USER
,NOTE_READABLE_FLAG
,NOTE_CHANGEABLE_FLAG
,NOTE_DELETEABLE_FLAG
,NOTE_ORIG_FOLL_UP_DT
,NOTE_TIME_FOLL_DT_CH
,NOTES_FILLER_001)
VALUES
('CPORDHDR'
,'000322'
,''
,20160128
,13
,48
,12
,''
,'STARSHIP'
,'0'
,''
,0
,0
,'CPORDHDR'
,'000322'
,'123456789-002'
,'Ship Date: 02/28/2016 13:48:12'
,'Pkg#: 1'
,'  Ship Via:     UPS Ground'
,'  Track #:      1Z2088866811111112'
,'  Weight:       6.00'
,'  Freight:      18.97'
,''
,''
,''
,''
,20160128
,13
,48
,12
,'STARSHIP'
,'Y'
,'Y'
,'Y'
,0
,0
,'')


Feedback and Knowledge Base