Meeting the Requirements of an OGA

Client Description:

The client is a worldwide logistics company providing this service for a large producer and distributor of cosmetic products.

 

Challenge:

To provide a solution that met numerous criteria.

  • To make use of data stored within existing order management and inventory control systems;
  • To augment the available data file with information stored in external databases;
  • To separate the handling of Section 321 shipments (typically under $200) from shipments subject to "formal entry";
  • To eliminate duplicate data entry;
  • To generate the paperwork required by both US Customs and the FDA;
  • To provide a computer readable file to the Broker;
  • To provide a computer readable file to the carrier to satisfy the requirements of the US Customs ACE program.

Ability to Provide Data Electronically:

Various order management and inventory control systems.  The systems generate CSV files containing the majority of the information required by Customs and OGA's.


Solution Implemented:

The EDI-Link - Web Data Import


Solution Details:

The technology challenge was to receive raw shipment information in the form of two different types of text files and transform it to:

  •  Separate shipments by Customs Rules for "Formal" and "Section 321" entries;
  • Incorporate data fields from external databases;
  • Ensure high levels of data extraction, processed in a timely way;
  • Transform all data to importable files for:
    • The Customs Brokerage ABI system;
    • An ACE Manifest e-file.
  • Reduce clerical costs for both companies.

The overall challenge was to do this in a single software process.

The process starts with the user generating two types of CSV files from their internal system (one format for shipments below $200 and a different format for shipments above $200).  Due to the large number of shipments involved, there are multiple data files.  The data files are themselves saved within two "zip" files.   

The process continues when the "zip" files are imported using the EDI-Link - Web Import page.  Both types of zip files can be imported at the same time.   The process automatically unzips the files, determines the type and uploads the data to the EC Dynamics WebServer.

Shipments of $200 and above are assigned both an entry number and an ACE trip number.   Shipments below $200 are assigned only the ACE trip number.

The data files being imported do not contain all the required information.  In this case, the information about the FDA Producer (name, address, registration number, etc.) is not contained in the data file.  Instead the file contains a code representing the FDA Producer.  The missing fields are stored in an external database.  As the data file is imported, the import process uses the Product code and the FDA Producer code to identify the proper file in the external "FDA Producer" database and "pull" the missing fields and embed them into the new shipment record.

The import process contains several edit checks to verify the existence and format of selected fields.  If any required field is missing, the process notifies the user and stops the import process.

When the import process is complete, the user has the ability to print the required forms.  In addition, the EC Dynamics WebServer automatically:

  • Generates a computer readable and importable file.  The file is in the format and delivered in the manner required by each individual recipient;
  • Distributes the data file to the Customs Broker for importing into their ABI system.  This eliminates the redundant data entry.  The Customs Broker reviews the information received.  Once the file is ready, the Broker submits the file to Customs.