BilTAY Teknoloji SCIENTA EDI System
EDI Processing
Overview
BilTAY Teknoloji SCIENTA includes six stored procedures to help process EDI Order(850), Shipment(856), and Invoice(810) data within the system. These stored procedures help validate and map data from external EDI Systems into the BilTAY Teknoloji SCIENTA system. You will still need to use a specialized mapping program to build the transactions, and a program to manage the connections to the VAN.
We include the following stored procedures - EDI_OrderProcesingInbound, EDI_OrderProcessingOutbound, EDI_ShipmentNoticeProcessingInbound, EDI_ShipmentNoticeProcessingOutbound, EDI_InvoiceProcessingInbound and EDI_InvoiceProcessingOutbound
Terminology
Orders are also known as 850's
Shipment Notices are also called either 856's or ASN's (Advanced Shipment Notices)
Invoices are also called 810's
VAN is a Value Added Network and is the medium by which EDI is exchanged.
Mapping and Translation is the act of moving data from a Relational Format to an EDI format for sending over a VAN. Mapping and translation is a specialized function and is handled by very specialized software.
EDI_OrderProcessingInbound
The EDI mapping and translation program should move the Order Information (850 Information) into the EDIIOrderHeader and EDIOrderDetail tables, which is where the Enterprise system is going to look for them. They would have an EDIOpen flag set to true, be EDI transaction type 850, and EDIDirection as I (For Inbound).
This procedure will process all orders from EDIOrderHeader in two major steps:
1. First all orders from EDIOrderHeader will be validated to see if:
A) PurchaseOrderNumber from EDIOrderHeader does not exist in OrderHeader table. This is to prevent the processing of duplicate EDI Orders
B) The ship to id in the EDIOrderHeader matches a valid ship to for that customer; To check this we do a lookup into the CustomerShipToLocations table
C) The cancel date from EDIOrderHeader is less then the date the order is processed.
D) The ItemID on the detail line matches an item within the system, If it doesn't, we do a lookup by customer id by item id in the CustomerItemCrossReference table and if it still doesn't match check if UPCCode in the EDIOrderDetail has a matching UPCCode in the InventoryItems table.
E) The ItemPrice on the detail line matches the item price in the system If it doesn't, we do a lookup by customer id by item id in the CustomerPriceCrossReference table
If any of these conditions fail, write a record to the EDI Exceptions Table with the error code and contimue processing.
2. If everything is ok, will insert into OrderHeader and OrderDetail the EDI Order information from the EDIOrderHeader and EDIOrderDetail and then will post the order. When the order is processed we change EDIOpen flag to NO (means that the order from EDIOrderHeader was processed and is therefore not OPEN).
EDI_OrderProcessingOutbound
The EDI mapping and translation program should look for the Order Information (850 Information) in the EDIPurchaseHeader and EDIPurchaseDetail tables, which is where the Enterprise system is going to put them. They would have an EDIOpen flag set to true, be EDI transaction type 850, and EDIDirection as O (For Outbound).
This is an Order sent from our system to one of our vendors. This works in the following manor:
The system will look at the VendorInformation table and determine which vendors accept EDI Orders, and will then cross reference that list with the open purchase orders in the PurchaseHeader & PurchaseDetail table to determine the list of purchase orders to be processed.
The system will then perform the following checks on those purchase orders:
1) Check to make sure that the purchase order is not already in the EDI PurchaseOrderHeader Table
2) Check to make sure that purchase order has detail line items, and that the line items have non-zero quantities
3) Check to be sure that the item prices are correct, The price has to match a price in the system, and if does not, the price will be checked in the VendorPriceCrossReference table.
Any exception should be written in the EDIException table
After all checks have been completed, the purchase order will be copied from the PurchaseHeader and PurchaseDetail tables into EDIPurchaseHeader and EDIPurchaseDetail tables and the EDIOpen flag will be set to true.
The external EDI Program can now go through the EDIPurchaseOrderHeader and EDIPurchaseOrderDetail tables and select the orders that need to be mapped and sent to the vendors on the VAN.
EDI_ShipmentNoticesProcessingInbound
The EDI mapping and translation program should move the Shipment Notices Information (856 Information) into the EDIPurchaseHeader and EDIPurchaseDetail tables, which is where the system is going to look for them. They would have an EDIOpen flag set to true, be EDI transaction type 856, and EDIDirection as I (For Inbound).
Here is how we handle the inbound 856's. First and most importantly, These inbound 856's actually do not get written to the main system tables, we are going to validate them, and then take some information from the 856's and update the proper status fields in the Purchase Order Header & Detail Tables.
Here is the process: First, Select an inbound 856 for processing,
Validate the following information:
1) That the Vendor ID is a valid vendor
2) That the Purchase Order Number for the Transaction matches a valid open purchase order in the system
3) The 856 is a valid 856 with a header and detail lines, and that the detail lines have quantities associated with them, and the quantities match from the Shipment Notice to the associated Purchase Order.
4) That the Ship Date, Ship Method, Tracking Number field are all populated with valid data
Once all this is validated, we can update the Purchase Order Header & Detail Tables with the information from the Shipment Notice as follows:
In the Purchase Order Header Table
EDIPurchaseHeader ShipDate writes to PurchaseOrderHeader ShipDate
EDIPurchaseHeader ShipMethod writes to PurchaseOrderHeader ShipMethod
EDIPurchaseHeader TrackingNumber writes to PurchaseOrderHeader TrackingNumber
Then, in the Purchase Order Detail table
Do a check to see which data to populate:
check the EDI Purchase Detail Tracking Number -
IF EDI Purchase Detail Tracking Number is Null, then:
EDI Purchase Header Tracking Number writes to Purchase Detail Tracking Number
Otherwise
EDI Purchase Detail Tracking Number -> Purchase Detail Tracking Number
After this update is done the EDIOpen flag on the transaction is changed to False.
EDI_ShipmentNoticesProcessingOutbound
The EDI mapping and translation program should look for the Shipment Notices Information (856 Information) in the EDIOrderHeader and EDIOrderDetail tables, which is where the Enterprise system is going to put them. They would have an EDIOpen flag set to true, be EDI transaction type 856, and EDIDirection as O (For Outbound).
This procedure would be called after a lot of orders are shipped for the day, or after a large shipment is made to a particular customer that requires EDI Shipment Notices.
This procedure will first go through the open orders that have shipped and have been shipped confirmed, and check to see if the customer that the order is for needs EDI Shipment Notices.
For each of these orders, the following actions are performed:
1) Check to make sure that the ShipmentNotice already does not appear in the EDI Order Header and Detail Tables.
2) Check to make sure that the ShipmentNotice is valid, that the header has detail lines, the detail lines have quantities, and amounts are non-zero, make sure that the Ship Date, and Tracking Number Fields have been populated.
3) Check the header and the detail Tracking Number fields. If the header tracking number is populated but not the detail tracking numbers, then use the header tracking number for all of the details, otherwise use the header tracking number for the header, and the detail tracking numbers for the detail. It is possible for the header and each of the detail lines to each have different tracking numbers.
Any errors will be written to the exceptions table
If everything is correct, then the Shipping Notice gets written FROM the Orders Header & Detail Table TO the EDI Order Header & Details table, and make sure the flags are set to EDIOpen = yes, EDI transaction type = 856, Edi Direction = outbound.
It is expected that a customized Pick & Pack routine would break the detail lines of the order into whatever pick and pack structure that the retailer requires, otherwise the structure is assumed to simply be either entire shipment into one carton, or each line item in its own carton depending upon where you put your tracking numbers. For example, for Pallet / Carton shipping, you can put the pallet number as the header tracking number, arrange each of the detail lines to be a carton, and have the detail tracking number be the carton tracking number, etc. There are lots of fields in the system where custom values can be defined and retained.
EDI_InvoiceProcessingInbound
The EDI mapping and translation program should move the Invoice Information (810 Information) into the EDIInvoiceHeader and EDIInvoiceDetail tables, which is where the Enterprise system is going to look for them. They would have an EDIOpen flag set to true, be EDI transaction type 810, and EDIDirection as I (For Inbound).
(First, the outside program would populate the EDI Payment Header & Detail Table with the transaction information).
Now,a Look in the EDI Payments Header & Detail table for open Inbound 810 transactions that are not processed. (This would be direction = inbound, transtype = 810, ediopen = true)
Once you have selected an Inbound invoice for processing, do the following checks:
1) Make sure the Transactions does not already exist in the system
2) Make sure the payment is for a valid customer
3) Make sure the payment detail references a valid document number in the system, for example, an inbound invoice would have a document number in the document number fields in the edi purchase detail table the would match a purchase order number in the system that has shipped.
Now write the information into FROM the EDI Payments Header & Detail tables INTO the Payment Header and Detail Tables.
When this is done, set the ediopen flag to false
Then call the process that posts the payment and get's it ready to be approved.
EDI_InvoiceProcessingOutbound
The EDI mapping and translation program should look for the Invoice Information (810 Information) in the EDIInvoiceHeader and EDIInvoiceDetail tables, which is where the Enterprise system is going to put them. They would have an EDIOpen flag set to true, be EDI transaction type 810, and EDIDirection as O (For Outbound).
This is an Invoice that we are sending TO a customer in response to and order that came FROM a customer, so we are going to be taking a transaction FROM the Invoice Header and Detail Table, and Writing it TO the EDI Invoice Header & Detail table.
So, when we do this process, we are going to find all of the invoices in the system that are open and are not sent to a customer yet, and cross reference that invoice by customer id to the customer information table to see if that customer received EDI Invoices.
For all of these EDI Invoices we will perform the following checks:
1) Make sure that we did not already send this invoice to the customer already
2) Check to make sure all of the proper fields are populated, and the invoice has a total, make sure the invoice has detail lines, check that all of these detail lines are non zero and have items on them.
3) Check and make sure that the item prices match the prices that are set up in the system, lookup the price in the item table, if it does not match do the lookup in the customer price cross reference.
Any exceptions to this process will be written to the EDI Exceptions table
If the invoice passes all of these validations, we then copy the invoice FROM the Invoice Header and Detail Tables TO the EDI Invoice Header & Detail Tables, and set the EDIOpen flag to true.
EDI Setup
This screen is used to enter the EDI Setup values for your company. This would be your received and sender id, and all of the documents that you process. The stored procedures in the system that process EDI can be set to look for these flags and process EDI Inbound and Outbound accordingly.
EDI Document Types
The EDI Document Types screen displays a list of all the types of EDI Documents in the BilTAY Teknoloji SCIENTA system. This table is used by the system to classify the types of EDI Documents that the company accepts.
The EDI Document Types screen can be accessed from the EDI Setup menu. The BilTAY Teknoloji SCIENTA system comes shipped with basic EDI Document Types that are suitable for most businesses. During the Setup and Installation process, and as one of the items on the Setup Checklist, it is recommended that this screen be reviewed to ensure that the EDI Document Types entered meets the way your company does business.
Additional EDI Document Types can be entered by clicking the New button and entering data in the following fields:
EDI Document Type ID: Enter the Type of the EDI Document. For example, if the EDI Document is an Invoice, then 810 would be entered for the EDI Document Type ID.
EDI Document Type Description: Enter the description of the EDI Document in this field. This description can be longer then the EDI Document Type ID but should not be too long.
When you are finished, click OK to close the screens.
EDI Document Directions
This form allows you to maintain the EDI Document Directions which are values that determine the direction of documents in the system.
EDI Exceptions
EDI Exception are EDI Documents that have come into the system but have errors of one form or another. EDI Documents with these errors can not be processed, so they are "Flagged" and a notation of this flag is put into the EDI Exceptions for follow-up.
EDI Exception Types
The EDI Exception Types screen displays a list of all the types of EDI Exceptions in the BilTAY Teknoloji SCIENTA system. This table is used by the system to classify the types of EDI Exceptions that the company may receive when processing EDI Transactions.
The EDI Exception Types screen can be accessed from the EDI Setup menu. The BilTAY Teknoloji SCIENTA system comes shipped with basic EDI Exception Types that are suitable for most businesses. During the Setup and Installation process, and as one of the items on the Setup Checklist, it is recommended that this screen be reviewed to ensure that the EDI Exception Types entered meets the way your company does business.
Additional EDI Exception Types can be entered by clicking the New button and entering data in the following fields:
EDI Exception Type ID: Enter the Type of the EDI Exception. For example, if the EDI Exception is an invalid price, then Price would be entered for the EDI Exception Type ID.
EDI Exception Type Description: Enter the description of the EDI Exception in this field. This description can be longer then the EDI Exception Type ID but should not be too long.
When you are finished, click OK to close the screens. |
