The nature of this project has to do with the Amazon Routing Number (ARN). The design is generic so it can work with other trading partners if they require the routing number in the EDI ASN (Advanced Shipping Notice or 856).  In this article we use Amazon Routing Number (ARN) and Routing Number interchangeably. 

Before shipping to Amazon, customers need to go to Amazon’s portal and indicate the PO they want to ship.  Amazon will then assign the ARN for the PO and the carrier will be specified.  ARN is a unique shipment number assigned by Amazon to track the shipment.  In the past, Amazon only needed the ARN for truck shipments and the Confirmation Number in the Elliott VICS Bill Of Lading can be used for that.  The Confirmation Number is exported in Shipped Order Data Export and Shipped History Data Export as part of the bill of lading data.

Amazon is now requiring parcel shipments (UPS or FedEx) to use an ARN.  Since the parcel shipment does not go through the VICS BOL, Elliott does not have a place to store the ARN in the order header.  Amazon requires the ARN to be unique, so backorder shipments are assigned a new number from the original shipment.

The solution is to let Elliott control the ARN.  Shipped Data Export will require the ARN when Amazon requires an ARN or the data will not export.  The ARN number be entered before exporting the ASN again.  The current procedure does not enforce the requirement of the ARN when exporting the Amazon ASN.

To resolve this issue, support for a new order/invoice Attribute, ^ROUTINGNUMBER, has been added.  The Attribute can be manually entered for each order or the routing number can be imported to create this Attribute.

^ROUTINGNUMBER Attribute Setup

System Utilities, System File Setup, Maintenance, Attribute File         

 

Create the ^ROUTINGNUMBER Attribute:

1.      Reference 1 – Routing Number
2.      Reference 2 – Carrier SCAC

 

Customer EDI Profile (Screen7)

The following fields have been added to screen 7 of Customer EDI Profile Maintenance:

3.      Require Order ^ROUTINGNUMBER Attribute for ASN? – Does the customer require a routing number attribute be entered for every order?  You could answer "N" if you are in a "mixed" mode operation.  In that case, Shipped Data Export (ASN/856) will always attempt to use the ^ROUNTINGNUMBER Attribute if it exists for the order.  But it will not stop users if that Attribute does not exist.
4.      Routing Number CSV Filename and Path – Default path to the Routing CSV File.
5.      Routing Number CSV PO Number Column – CSV column for the purchase order number.
6.      Routing Number CSV Routing Number Column – CSV column for the routing number.
7.      Routing Number CSV Carrier SCAC Column – CSV column for the Carrier SCAC. Enter 0 if there's no SCAC column.
8.      Routing Number CSV Has Header Row – Does the CSV file have a header row?
 

Import Routing Number CSV

Under COP, Processing, Print Pick/Pack Ticket & Lbls, and Shipping, a new entry, Import Routing Number CSV, has been added under Shipment Data Update and before Pick Order Processing.

To access the new import application the ^ROUTINGNUMBER Attribute must be configured with at least Ref1 and Ref2 fields enabled, but you can have more fields enabled.  If the Attribute setup does not meet our minimum requirements, an error message is displayed and the user is advised about the expected Attribute layout.

When the Attribute is properly configured, the menu allows for Pre-Interface and Interface.  There is no Layout option since different trading partners can have a different layouts. The layout can be defined in the EDI Trading Partner Profile. 

The Pre-interface and Interface will prompt for the following:

1.      Customer Number - Enter the customer number to be used when processing the CSV file.
2.      Routing Number CSV Filename And Path: Enter the path where the CSV file resides.
3.      Print CSV Raw Data: Should the CSV raw data be printed on the report?

After the customer number is entered, the application will look up the Routing Number CSV Filename And Path in the EDI Trading Partner Profile and display them as the default for field 2.  If the EDI Profile fields 5 through 7 are not valid (should have a value between 1 – 20), then the message “Customer is Not Setup to Use Routing Number CSV Import. Please Change Trading Partner Profile.” will appear.

The actual CSV import will use the information in fields 5 through 8 to determine how to process the CSV file.  The ARN (Routing Number) will be written to order ^ROUTINGNUMBER Attribute Reference 1.  The Carrier SCAC will be written to Reference 2.

Some considerations:

A CSV record will not be processed if the order (PO) is not found.  This can happen because the sales order could have been purged.  This is not an error condition.  This is simply an informational message.

If the order is already posted, the CSV record will not be processed.

If the ASN has been exported, but order is not posted, the import will print a mismatch between the existing Attribute and the import ARN as a warning.

If the PO number is blank, the CSV record will not be processed.

If the PO number, ARN or SCAC column exceeds 30 digits, an error message will be printed.

If the Attribute already exists and the order is not posted but the ARN and SCAC are not the same, a warning message to indicate the ARN or SCAC does not match is printed.

Errors and warnings are tallied and totals are printed at the bottom of the report:

   ** Total No. of Warning          :                  2
   ** Total No. of Blank PO No.    :                  1
   ** Total No. of Posted Orders    :                  6
   ** Total No. of Actions          :                59
   ** Total No. of Error            :                10
   ** Total No. of Records          :                272

ASN Label to Support Routing Number

In the EDI Trading Partner Profile screen 5 and field 6, Custom Ship Label CSV Format (UCC-128), a "Y" value will pop up a window to allow fields to be selected for the shipment label.  A new selection, Routing Number, has been added after Bill of Lading No.  If this field is selected, then the Routing Number captured in the Order Attribute ^ROUTINGNUMBER will be used.  If the Attribute does not exist, the application will look for a corresponding value in the VICS Bill Of Lading.  If that value does not exist, then a blank will be exported.

 

Users are able to see this “Routing Number” as a selection item whether or not they have the ^ROUTINGNUMBER Attribute set.  But if they select the field and the ^ROUTINGNUMBER Attribute is not set, then we will display the proper message and indicate the Attribute is not set.

VICS BOL Creation

The EDI Trading Partner Profile Setup screen 4, field 10, VICS BOL Conf# Literal, is now populated with "ARN" if the ^ROUTINGNUMBER Attribute is found when the of VICS BOL is created. 

However, there could be exceptions:

(1) Multiple orders are included in creating the VICS BOL and their value in ^ROUTINGNUMBER is not consistent.  In this case, the application will give a warning message “Order routing numbers are inconsistent” and a default value is not assigned.

(2) Multiple orders are included in creating the VICS BOL and some of the them have ^ROUTINGNUMBER while some of them don’t. In this case the application will give a warning message “Not all orders have routing number assigned” and a default value is not assigned.

If none of the orders have a ^ROUNTINGNUMBER Attribute, you can manually enter field 18.

ASN Export

For trading partners that have flag 3, Require ^ROUTINGNUMBER Attribute for ASN, set to Y, the ASN export (both shipped data and shipped history data) must be able to find the routing number.  If the routing number exists in the Order Attribute or Invoice Attribute (for the shipped history export), then it will be exported.  If the Attribute does not exist and a bill of lading number is populated on the order, then the export will look up the routing number from the VICS BOL database.  If both fail, then the export will give an error message for the order and the export will fail.  If the flag 3 above is “N,” we will export it using the same procedure, but the application will not fail if the routing number is not found.

Invoice Posting

Post Invoices will transfer the Attribute on the order header to the invoice header.  The Attribute in the invoice header will be used for Shipped History Data Export.

Because there can be a backorder, the original ARN and the backorder shipment ARN will not be the same.  The post program will remove the original ^ROUTINGNUMBER Attribute on the order to avoid confusion.  On the other hand, a fully posted order will retain the Attribute.

Programs Changed: EDSHPLBL.WS, EDSHPLBL.PL, CP0200, CPSHPBAR, ARCUSEDI, NSCTLMNV, CP0305, CPBOLINS, CPSHPEDP, CPINHEDP

Programs Added: CPRTEIMS, CPRTEIMP, CPIMPER3.DS, CPIMPER3.FD, CPIMPER3.SL, CPIMPER3.WS, CPIMPRTE.DS, CPIMPRTE.FD, CPIMPRTE.SL, CPIMPRTE.WS, CPRTEASC.DS, CPRTEASC.FD, CPRTEASC.SL, CPRTEASC.WS

CLS