Order Header Table Integration (CPORDHDR)

• 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)

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.  

The primary key of the note table consists of 

• Note_File_Name
  
• Note_File_Ref_No 
  
• Note_Folder
  
• Note_Create_Date
  
• Note_Create_Time (HH+MM+SS). 
  

Their combined value can not be duplicated.  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.  If you choose not to populate a unique value in Note_Topic (see below for detail), then you should increase the second field by two for each note record so Elliott V8 (and above) Note user interface is not going to consolidate the consecute note records (seperate by one second that has the same subject line) and present it as if they are one signle big note record.

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 = 'CPORDHDR' AND NOTE_FILE_REF_NO = '001234' AND NOTE_TOPIC = '123456789-002'

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 (The subject field of the Notes) 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.

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.

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

Example – FedEx Tracking Note

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_Estimated_Time: This is for 8.x & up DDF. Hard-Code this with zeros.

Note_Remind_Date:  This is for 8.x & up DDF. Hard-Code this with zeros.

Note_Remind_Time:  This is for 8.x & up DDF. Hard-Code this with zeros.

Note_Recur_Type:  This is for 8.x & up DDF. Populate with spaces.

Note_Recur_Freq:  This is for 8.x & up DDF. Hard-Code this with zeros.

Note_Rem_Ahead_Type:  This is for 8.x & up DDF. Populate with spaces.

Note_Rem_Ahead_Val:  This is for 8.x & up DDF. Hard-Code this with zeros.

Note_Content_CR1:  This is for 8.x & up DDF. Populate with spaces.

Note_Content_CR2:  This is for 8.x & up DDF. Populate with spaces.

Note_Content_CR3:   This is for 8.x & up DDF. Populate with spaces.

Note_Content_CR4:   This is for 8.x & up DDF. Populate with spaces.

Note_Content_CR5: This is for 8.x & up DDF. Populate with spaces. 

Note_Content_CR6: This is for 8.x & up DDF. Populate with spaces. 

Note_Content_CR7: This is for 8.x & up DDF. Populate with spaces. 

Note_Content_CR8: This is for 8.x & up DDF. Populate with spaces. 

Note_Content_CR9: This is for 8.x & up DDF. Populate with spaces. 

Note_Content_CR10: This is for 8.x & up DDF. Populate with spaces. 

Note_Filler_001 or Notes_Filler_001: Populate with spaces.  With Elliott 7.x DDF, use Filler_001.  With Elliott 8.x DDF, use Notes_Filler_001.

• 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. Never insert NULL value into Elliott database. 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 8.x DDF.  If you are using newer or older version DDF, you will need to 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 could be either numeric or alphabetic, but NOTE_FILE_REF_NO is always 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.