EDI Database Mapping

This section describes how EDI documents are mapped to database tables in our Transportation Management System.

EDI 204 - Motor Carrier Load Tender

EDI 204 documents are parsed and stored in a relational database structure that preserves the hierarchical nature of the EDI format. Each segment of the EDI 204 has a corresponding table, with foreign key relationships maintaining the document hierarchy.

Database Schema

Note: For clarity and readability, the diagram above shows only selected columns for each table:

• Primary key fields (with PK suffix)
• Foreign key fields (with FK suffix) that establish relationships
• A selection of important business data columns for each table

The actual database tables contain additional fields not shown in this diagram.

Table Structure

The EDI 204 document is stored across the following tables:

1. EDI_204_ISA - Interchange Control Header

   • Contains sender/receiver information, control numbers, and dates
   • Represents the "envelope" of the EDI transmission

2. EDI_204_GS - Functional Group Header

   • Contains information about the functional group
   • Links to its parent ISA record

3. EDI_204_ST - Transaction Set Header

   • Marks the beginning of an individual transaction
   • Contains the transaction set identifier and control number

4. EDI_204_B2 - Shipment Information

   • Contains core shipment details like SCAC, shipment number, and payment method
   • Primary record for load tender information

5. EDI_204_B2A - Shipment Type Information

   • Contains information about the purpose of the shipment

6. EDI_204_G62 - Date/Time Reference

   • Contains date and time information for various events
   • Linked to different parent records based on context

7. EDI_204_L11 - Reference Numbers

   • Contains various reference identifiers for the shipment

8. EDI_204_N1_NAME_ADDRESS - Party Information

   • Contains name and address information for involved parties
   • Identifies shippers, consignees, bill-to parties, etc.

9. EDI_204_N7 - Equipment Details

   • Contains information about the equipment used for the shipment

10. EDI_204_NTE - Notes

    • Contains free-form text notes related to the shipment

11. EDI_204_S5 - Stop Information

    • Contains details about pickup and delivery stops
    • Includes sequence numbers and stop reasons

12. EDI_204_OID - Order Information

    • Contains details about the order within a stop
    • Includes reference numbers, quantities, weights, etc.

13. EDI_204_L5 - Item Description

    • Contains detailed information about items in the shipment
    • Includes commodity codes, descriptions, etc.

14. EDI_204_AT8 - Shipment Weight, Volume, and Quantity

    • Contains detailed weight and volume information for items

Database Views

To simplify data access and reporting, we've created several views that aggregate information from multiple tables:

vw_edi_204_transactions

This view provides a consolidated view of load tender transactions, combining information from multiple tables. It joins the ISA, GS, ST, B2, B2A, G62, and N7 tables to create a comprehensive record of each load tender transaction. The view includes:

• Transaction identification (ISA, GS, and ST control numbers)
• Customer information (tenant, company, and customer details)
• EDI partner identification (sender and receiver IDs)
• Shipment details (shipment number, payment method)
• Transaction purpose and type
• Date and time information for the shipment
• Equipment details used for the transportation

This view serves as the main entry point for accessing EDI 204 transaction data in the TMS.

vw_edi_204_stops

This view combines stop information with associated location details by joining the S5 table with N1_NAME_ADDRESS and G62 tables. Each record represents a stop on a shipment's route with:

• Stop identification and relationship to the shipment
• Stop sequence and reason (pickup, delivery, etc.)
• Physical characteristics (weight, volume, units)
• Location details (name, address, city, state, postal code)
• Appointment date and time information

The stop view is crucial for planning and execution of shipments, allowing dispatchers to see all pickup and delivery points in sequence.

vw_edi_204_orders

This view aggregates order information with item details by joining the OID, L5, and AT8 tables. It provides a comprehensive view of:

• Order identification and relationship to stops
• Reference numbers and purchase order information
• Packaging and quantity details
• Weight and volume measurements
• Item-level information (descriptions, commodity codes)
• Special handling requirements

This view is used for detailed shipment planning, ensuring all items are properly accounted for in the transportation process.

Data Flow

When an EDI 204 document is received, it follows this processing path:

The data is stored in a highly normalized structure to maintain the integrity and hierarchical nature of the EDI document. The views then present this data in a more denormalized format that's easier to use for business operations.

Key Relationships

• Each EDI 204 document has one ISA header
• Each ISA can contain multiple GS segments
• Each GS can contain multiple ST segments
• Each ST typically contains one B2 (shipment)
• Each B2 can have multiple S5 (stops)
• Each S5 can have multiple OID (orders)
• Each OID can have multiple L5 (items) and AT8 (weights)

EDI 990 - Response to Load Tender

The EDI 990 document is used to respond to a received EDI 204 Load Tender. Unlike the EDI 204 which has a complex hierarchical structure, the EDI 990 is simpler and is stored in a single primary table.

Database Schema

Table Structure

1. EDI_990_LOAD_TENDER_RESPONSES - Main table for storing responses to load tenders

   • Contains the response details like acceptance/rejection status
   • Links back to the original EDI 204 load tender
   • Includes carrier information and response metadata

2. EDI_OUT_LOG - Tracks the transmission of outbound EDI documents

   • Stores control numbers, timestamps, and file information
   • Used for all outgoing EDI documents, including 990 responses

Data Flow

When responding to an EDI 204 load tender with an EDI 990, the process follows this path:

EDI 214 - Transportation Carrier Shipment Status Message

The EDI 214 document provides status updates for shipments in transit. Like the EDI 990, it has a relatively simple structure in our database.

Database Schema

Table Structure

1. EDI_214_STATUS_MESSAGES - Main table for storing shipment status updates

   • Contains the status code, timestamp, and location information
   • Links back to the original EDI 204 load tender
   • May reference a specific stop in the shipment

2. EDI_OUT_LOG - Tracks the transmission of outbound EDI documents

   • Stores control numbers, timestamps, and file information
   • Used for all outgoing EDI documents, including 214 status updates

Data Flow

When sending an EDI 214 status update, the process follows this path:

Status Codes

The EDI 214 uses standard status codes to indicate the current state of a shipment. These values are maintained in the Types Master table under the 'SHIPMENT STATUS CODE' element:

Status Code	Description
D1	Completed Unloading at Delivery Location
X1	Arrived at Delivery Location
AF	Carrier Departed Pick-up Location with Shipment
X3	Arrived at Pick-up Location
SD	Shipment Delay / New ETA to Delivery Location
AB	Delivery Appointment
AG	Estimated Delivery
A7	Refused by Consignee
X4	Arrived at Terminal Location
P1	Departed Terminal Location

EDI 210 - Motor Carrier Freight Details and Invoice

The EDI 210 document is used to transmit freight invoice information between trading partners. This document allows carriers to submit detailed invoice information for transportation services rendered. In our system, EDI 210 data is stored in a normalized database structure.

Database Schema

Table Structure

1. EDI_INVOICES - Main table for storing invoice header information

   • Contains invoice identification, amounts, dates, and reference numbers
   • Links to the trading partner and company information
   • Stores summary information like totals and status

2. EDI_INVOICE_LINES - Line items for each invoice

   • Contains line number and item references
   • Links to the parent invoice record
   • Serves as a parent for line-level detail tables

3. EDI_LINE_DESCRIPTION_MARKS_NO - Detailed description for invoice line items

   • Contains commodity codes and descriptions
   • Provides textual information about the shipped items

4. EDI_LINE_ITEM_QTY_WEIGHT - Quantity and weight information for line items

   • Contains detailed weight and quantity measurements
   • Includes unit of measure information

5. EDI_LINE_ITEM_RATE_CHARGE - Rate and charge information for line items

   • Contains freight rates and charge amounts
   • Includes charge indicators for different types of charges

6. EDI_INVOICE_NAMES - Party information associated with the invoice

   • Contains names and addresses for involved parties
   • Identifies different roles using the identifier field (e.g., 'BT' for Bill-To, 'ST' for Ship-To)

7. EDI_INVOICE_EQUIPMENTS - Equipment used for the shipment

   • Contains equipment identification information
   • Links to the parent invoice

8. EDI_INVOICE_SPECIAL_HANDLING_INSTRUCTIONS - Special instructions for the shipment

   • Contains handling codes and descriptive text
   • Provides additional guidance for the shipment

9. EDI_OUT_LOG - Tracks the transmission of outbound EDI documents

   • Stores control numbers, timestamps, and file information
   • Used for all outgoing EDI documents, including 210 invoices

Database Views

To simplify data access and reporting, there are views that aggregate information from multiple tables:

vw_edi_invoices

This view provides a consolidated view of invoice information by joining the EDI_INVOICES table with related tables. It includes:

• Invoice identification and reference numbers
• Customer and company information
• Financial details (amounts, currency)
• Status information
• Bill-To and Ship-To party details

vw_edi_invoice_lines

This view provides detailed line item information by joining the EDI_INVOICE_LINES table with related detail tables. It includes:

• Line item identification
• Rate and charge information
• Reference back to invoice header information
• Customer and company details
• Financial summary information

Data Flow

The EDI 210 invoice processing follows this typical flow:

Integration with QuickBooks

The EDI 210 process includes a bi-directional integration with QuickBooks for financial processing. The primary data flow is from QuickBooks to our EDI system:

1. Invoices are created in QuickBooks first, storing invoice header and line detail information
2. The pr_load_edi_210_invoice stored procedure extracts data from QuickBooks tables
3. The procedure validates the invoice (checking for duplicates, amounts, line items)
4. Data is transformed and loaded into the EDI invoice tables with the proper structure
5. The procedure handles mapping of:
   • Invoice header information (dates, amounts, reference numbers)
   • Bill-To and Ship-To address information
   • Line items with their descriptions and charge amounts
   • Special handling instructions and equipment details
6. After successful mapping, the invoice is marked with status code '01' (Invoice Received)
7. The EDI 210 document is then generated and sent to the trading partner
8. Payment status updates may be synchronized back to QuickBooks

This approach ensures that financial data is maintained in a single system of record (QuickBooks) while still enabling EDI communication with trading partners.

Status Codes

The EDI 210 invoice uses the following status codes (stored in the Types Master table under 'EDI Invoice Status'):

Status Code	Description
01	Invoice Received
02	Invoice Sent
03	Missing or Invalid Fields

QuickBooks to EDI Mapping Process

The stored procedure pr_load_edi_210_invoice handles the extraction and transformation of data from QuickBooks tables to EDI format. Here's a summary of its operation:

This mapping process ensures that all relevant QuickBooks invoice data is properly translated into the EDI 210 format required by trading partners, while maintaining the financial integrity of the data.

For more detailed information on the QuickBooks integration and invoice processing, see the QuickBooks Integration documentation.