Edi, Syteline and PBTI


Introduction

Welcome to my post on transacting over EDI within Infor Syteline 8. I have written this post for 3 key reasons.

  1. In preparation for a presentation to the Syteline User Group in March 2014
  2. As an introduction to the edi system for a new member who is starting within the team and will have principle responsibility for bringing on new customers
  3. As an insight to other Syteline users who are interested in edi and what can be done with the system.

I have divided this post into 4 sections, so you can jump to the section you are interested in, these sections are :-

  1. Background information on PBTI
  2. The benefits PBTI have seen from using EDI
  3. The processes we have developed to cater for EDI processing
  4. Notifications, Alerts and Pitfals

Hopefully you will find it a relatively interesting read, and of use. Feedback is always greatfully received, and if you want any more details drop me a line an I will do my best to help.


Background information on PBTI

PBTI is an SME with 75 -100 staff, on a 5 hectare site, based in Wells. Somerset, shipping up to 30’000 products per month to countries throughout the World.

PBTI Aerial Photo

PBTI Aerial Photo

We have 3 main brands, PBTI, Xinia and SunAngel.

Xinia PBTI and SunAngel Logos

Xinia PBTI and SunAngel Logos

We specialise in printer consumables, mainly toner cartridges but also drums, opc’s, fuser kits, and specialist print media.

SunAngel Transfer MediaXinia Remanufactured C711 cartridgeWhite Printing

Our order processing intake fluctuates between 50 and 90 orders a day, the line count per order fluctuates  between 1 and 60 and the total order value fluctuates between £35 and £150’000. So as you can understand there is massive variation.

We :-

  • Only sell dealers, not end users.
  • Drop ship 90% of all orders to the dealers customer, and most end users are new meaning we have a total of 44’000 records in the custaddr table, 3285 belonging to a single customer who transacts over EDI with us.

 


The benefits PBTI have seen from using EDI

Processing Efficiencies

Pre Syteline 8, the average processing time per order use to equate to around 10 minutes. With the personalisations we incorporated with Syteline 8, the manual processing time has been reduced to around 5 minutes. EDI orders now take less than 30 seconds. so on a day we have 27 orders come in over EDI, that’s about 2 hours of manual processing removed from the organisation.

As we don’t want to be accused of spin doctoring when justifying the development expense, we monitor our EDI intake via an SSRS report which is emailed to the board on a weekly basis,  using three key performance indicators :-

  • Order Value
  • Order Count
  • Line Count
Edi Analysis 
PBTI’s current stats for EDI useage

Order Accuracy

The order entry accuracy has always been an issue as each of our customers has their own part codes for our items, and their own contract pricing, some of which are dependant on the notice period on the order (a 3 month order gets an order discount applied where as a same day order does not). Finally they often have their own agreed shipping rates.

As a result of these order variables, we use to observe a high level of issues caused by input error. At best this would cause our accounts tem to have to raise a price adjustment credit, at worst it would involve an RMA being raise, and a replacement part being sent to the end user. Whilst the Syteline EDI system does not facilitate for logic to be added to the inbound order, the systems we have put into place around them do.

Customer Requirements

Several of our big customers made the ability to transact over EDI a principle requirement during their tendering process as the reduction in administration at their end, for both their accounts staff and procurement team is considerable.

Order Processing Bottlenecks

Our customer services team have a lot to do in their day. As well as input orders they have to produce customer reports, deal with RMAs, upload price lists agreed by the sales team and generally build up the relationship with our customers.

As a result, order processing generally gets left to just before the lunch time order cut-off or the end of day order cut-off meaning an order placed at 0900, might not go onto the system until 12:00. This has 2 major impacts, firstly it does not give much notice to the factory to pick pack and send the items giving them a very “spikey” work pattern. Secondly it results in items which have been sold already still showing as available on the next customer enquiry, and worst still if the orders are not processed in sequence the customer who ordered first sometimes ends up without the product.

Because EDI automatically processes these orders within minutes of their arrival, this issue is alleviated,

Purchasing and PBTI 

At present, we do not transact with our vendors over EDI. This is mainly due to the way our procurement system works. It may be something we look into in the future, particularly inter-site.


The processes we have developed to cater for EDI processing

When we first considered using EDI we did not quite know what we were letting ourselves in for and were under the misconception we would be able to use it almost “out of the box”. The Infor Sytelines EDI system doesn’t appear to of changed much (if at all) between Syteline 6 which we installed in 2002 and version 8.03 which we currently use.

Inbound Data

The first and primary issue we had to overcome was the file formats. All of our customers use XML (and in particular a BOSS federation standard) and Syteline uses flat files. . Our customers are primarily smaller organisations with outsourced IT and relatively low IT experience, any changes to facilitate a vendor would either be costly to them, or simply not possible therefore it was our place to mould our system to meet their requirements …….  here’s a couple of examples of the file formats we were converting from and to :

EDI xml File Sample

EDI xml File Sample

 

EDI Flat File Sample

EDI Flat File Sample

When you come to map your customer file to the Syteline standards, always start with the help files. They are fairly conclusive once you get your head around them. The first page you will need is “Inbound Demand EDI Transactions (Flat-File Layout)” which shows something similar to the following. I also managed to get a couple of sample file off of some very helpful Infor staff which helped us make sense of the documentation :-

Syteline 8 EDI 100 Record Type Mapping

Syteline 8 EDI 100 Record Type Mapping

The IT Team at PBTI

At this stage, I think it is a good idea to mention the structure of my team, I have what I would call “soft” developers who have prominent communication, project management and logical skills but are not programmers. I often use these for light personalisation’s such as moving components around on forms, writing reports, staff training and similar tasks. I also have team members I would classify as coders, these people are responsible for larger coding projects, they often work alongside the “soft” developers and churn out thousands of lines of code for specific purposes.

Over the years, the team has changed as we have had a turn over of staff, but Credit for the successful integration and on-going upkeep of this project needs to go to Kris Hawkes, Matthew Neave, Gary Seviour & Gary Lansley.

File Format Conversion

Whilst we considered writing the file conversion in VB.NET, each customers requirement has proved to be slightly different, and I did not want the coders to be required every time we wanted to bring a new customer on toEDI, therefore we chose to use a third party application to do the mapping between the XML and Flat files which can be carried out by the ‘soft’ developers. The application we chose is http://www.altova.com/mapforce.html.

Altova MapForce

Altova MapForce

The application allows you to drag and drop elements from multiple sources to multiple destinations, and apply logic on route. As you can probably imagine, the files can get a little complex – here is part of one of our maps :-

Map Force Sample

Map Force Sample

The PBTI EDI App

We had to built an app to control each stage of our EDI process on a schedule. this has been written as a standalone app using C#. It is so business critical to us as customer s can order products in branded packaging up to 16:45 for same day shipment which leaves our site at 17:30.

It writes a debug log out to SQL which allows us to monitor it using workbenches. The user interface appears as follows, and we run 1 application per site on the database server :-

PBTI EDI App

PBTI EDI App

 

There is 6 key phases as follows :

Check FTP :

  1. For each customer configured for EDI, it logs on to their FTP “drop off” location, and looks for files noting if there are any files to process.

Phase 1 : Get Files – Per Customer

  1. Collects FTP files from customer location, and once successful deletes them from ftp site to stop double processing.
  2. Pre-processing any issues with the files (we have 1 customer who needs their files changed as they have no header information in it)
  3. Executes the first map-force phase 1 command for the customer which creates a customer ship to address file.
  4. Looks for the files map-force should of created, if it cannot find them, it throws an error, and does not continue for that customer.

Phase 2 : Set up ship-To etc Per Orders

  1. Compares the details in the ship-to file with our custaddr table to see if the customer ship-to exists and if it does, returns the ship to number
  2. If the ship-to does not exist it creates it, also adding customer document profiles
  3. It checks the ship-to has a trading partner record, if not it creates it
  4. Executes “Map-Force Phase 2 Customer”command which starts the conversion process of the XML to the flat file, using the trading partner code and ship to details.
  5. Checked the output files have been created, if not it throws an error, and does not continue.

Phase 3 : Merges all order details for all customers into 1 file & places into pickup directory

  1. Executed the “Global MapForce Phase 1” command which pulls all of the files for each customer that has been processed in phase 1 & phase 2 and puts them into a single file ready for Syteline to process
  2. Archives all files for analysis purposes
  3. Puts file into pickup directory

 

Phase 4 : Order Acknowlegments

  1. 855_imp.pb file generated by Syteline is looked for
  2. if found it executes “Global map force phase 2” command that splits the file into individual order acknowlgements for each customer & order
  3. Archives the 855_imp.pb file
  4. FTP’s the files back to the customers location

Phase 5 : Invoicing

  1. We have ignored Sytelines Invoice output due to too many issues
  2. We have a sql view for all non-ftp’d invoices for customers on EDI – including those orders received over the phone.
  3. Each invoice is then Ftp’d to the relevant customer

Post EDI Import

Once the order is received into Syteline, we use the application Event System to process the orders further. This includes :-

  • Sets the ship-via code dependant on the destination and weight, then calculating the shipping charge
  • Sets the paperwork types so as outbound forms are formatted correctly
  • Raising linked production jobs
  • Send an email notification to the sales team
  • Print the pick list in the factory

 


 

Notifications, Alerts & Pitfalls

We need to let the users know that an order has come in, else they would not get processed, so using event management we send out several different types of notifications.

Order Received Notification

For each EDI order received we notify the sales team with all of the order details which allows them to double check what the system has processed and correct any errors identified. On the example shown below, the event system has also created a job order for the line, and linked it as it is a non stocked, manufactured item. The event system also calculated the shipping cost based on the customer, the postcode, the weight and the total order value :-

Edi Successful Email

Edi Successful Email

Order “Stuck” Notification

When Syteline imports EDI orders, it puts them into a holding form called “EDI Customer Orders” and then validates them on post. There is no built in alert for orders failing to validate, so once again we build an email notification for this (note the hyper-link on the order number, clicking this takes the user straight to the form and record). Validation can fail for several reasons, but the most common we see are :-

  • Unrecognised Item Code
  • Invalid Item Price
  • Order already exists

EdiValidationFailed

Wall Board

We use a combination of Workbench Critical Numbers and SSRS reports to show all relevant KPI statistics within the sales team. It’s displayed on a 32″ wall mounted screen and has proved very useful as not only does it allow the sales team to quickly see how everything is going, we find all senior staff pop their heads around to look at it throughout the day. The only issue we have with this is that it consumes a full transactional Syteline user, which are not cheap. As a result we may be forced to develop something in-house, especially as we have 3 wall boards at present (Sales, Production and IT) and will probably got to 6 by the time we are finished (adding Warehouse, Packing and Purchasing).

Sales Wallboard Photo

Sales Wallboard Photo

The EDI notification is actually an SSRS report and appears as follows (I took this snapshot at 0830 am!)

EDI Wallboard Alert

EDI Wallboard Alert

Monitoring

The EDI system needs a lot of babysitting, if you are not careful it will all fall down in a heap and you will be none the wiser until a customer rings you chasing their order. Here is a couple of things we monitor for

Duplicate data in 850.pb file

 

This is a really interesting one, as you have no idea of an issue. The background task completes successfully and it is only when you look at the taks messages you discover there is an issue as shown here :-

 

EDI Error DuplicateData

EDI Error DuplicateData

The saving grace of this issue is that when the EDI import runs successfully it clears and deleted the 850.pb file. When it doesn’t work as above, the file is not deleted. As such, we can monitor the age of the file and display it on a critical number as shown here :-

EDI Stale 850 File

EDI Stale 850 File

As the form refreshes the critical number on a set interval, and the critical number is based on a stored procedure we can easily send an email if certain conditions are met – ie if the file is older than X minutes old. This is how our Stored Procedure works (excuse the way wordpress formats this)

DECLARE @myfiles TABLE (MyID INTIDENTITY(1,1)PRIMARYKEY, FullPath VARCHAR(2000))

declare @filepath asnvarchar(100)=‘\\fileserver\it\Syteline_8_EDI\Demand_Inbound_Data\850_EXP.PB’

declare @commandline asnvarchar(100)=‘dir “‘+  @filepath +‘” /T:c’

declare @minsold asint

INSERTINTO @MyFiles(FullPath)

exec xp_cmdshell  @commandline

DELETEFROM @MyFiles WHERE fullpath ISNULLOR fullpath=‘File Not Found’

DELETEFROM @MyFiles WHERE fullpath notlike‘%850_EXP.pb’

select @minsold =cast(DATEDIFF(MINUTE, CONVERT(datetime,replace(left(rtrim(ltrim(fullpath)),17),‘  ‘,‘ ‘),103),GETDATE())asdecimal(10,2))

from @MyFiles

set @Amount = @minsold

declare @msg asnvarchar(2000)=‘file ‘+ @filepath +‘ was created ‘+cast(@minsold asnvarchar(10))+‘ minues ago – you might want to check it out? <br/><br />Sales – Please ensure someone in IT is dealing with this – it is your responsibility and your customers who wont be happy! <br/><br /> This Message was sent by the Stored procedure pbti_ih_sp_cn_EDIImportFileAge ‘

declare @sub asnvarchar(2000)=‘EDI Import File Stuck ‘+ @filepath

if @Amount > 10.00 anddatepart(HOUR,GETDATE())between 7 and 20

EXEC msdb.dbo.sp_send_dbmail@profile_name=‘PBTI’,

@recipients=’employee.1@domain.com; employee.2@domain.com; employee.3@domain.com; employee.4@domain.com’,

@subject= @sub ,

@body= @msg,

@body_format=‘HTML’

GO

Here is a copy of the email the stored procedure sends ……

EDI Error Duplicate Data - Email Alert

EDI Error Duplicate Data – Email Alert

 EDI App not running

If the server reboots, or the app encounters an issue and stops we have set up a notification system, which again is triggered via event management. The email shown below is sent to both the sales and IT team.

EDI App Not RunningEDI App Not Running

EDI App Not Running

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.