Gentran:Server - SQL Volume Report

Here is a basic Gentran Server volume report we recently put together. Just a sample of the custom Gentran Server work we do here at EDI Associates. Feel free to contact at support@ediassociates.com if you have a special requests.

 select distinct b.PartnerName,   
 a.TransactionSetID,   
 count(a.TransactionSetID) myCount,   
 direction = CASE a.Direction   
   WHEN 0 THEN 'IN'  
   WHEN 1 THEN 'OUT'  
 end   
 from dbo.Document_tb a, dbo.Partner_tb b   
 where a.PartnerKEY = b.PartnerKEY   
 group by b.PartnerName, a.PartnerKEY, a.TransactionSetID, a.Direction   
 Order by myCount DESC  

Output

GENTRAN:Server send an attachment through the E-Mail gateway

Recently I was asked if there was a way to send a transaction as an attachment through the E-Mail gateway. Just a bit of history: E-Mail gateway by default will send transactions as the message text of an email.

The answer to our question is YES; however, it is one of those wonderfully undocumented features that is not a straight forward setup.

Follow these steps:
Open your GENTRAN:Server Mailbox Manager > Right click on the E-Mail Mailbox your trying to send data with. > Select Properties > then the Gateway tab > and then Configure.

Next Select Additional Mime Headers and add the following: "Content-Type: Application/X-EDI;"

Your all done, just keep hitting OK until your out of the mailbox properties.

Please Note that a couple readers of this Blog have pointed out that there is another Mime Header you can try if for some reason you do not get the desired result using the above:
"Content-Disposition: attachment;"

Happy Gentraning.

GENTRAN:Server Notifications on stalled events

Do you have an event that occasionally stalls. There are many reasons an event might stall: network errors, unexpected data values and poor code just to name a few. My goal here is not to give you or anyone a hard time about that one program you wrote like 5 years ago; however, I'm sure it would be fun. My goal is to show you how to setup a notification so that the next time it does get stalled your not stuck with 3 or 4 days in unsent transactions and a pissed off boss.

First take note of the Session and Command that is stalled:

Next, you will have to stop the event that runs the session. If the event is currently stalled you will have to stop and restart the "GENTRAN:Server Executive" service. This can either be done from Services under the Control Panel, or from the command prompt using the following commands.

NET STOP "GENTRAN:Server Executive"
NET START "GENTRAN:Server Executive"

Once you are able to suspend the event under the Events tab you will be able to edit the session under the Sessions tab. Set the Notification flag and them set the number of minutes the process should be allowed to run before it sends you the notification. If you are uncertain, run the event and see how long it normally takes to complete.

Now it's time to setup the notification. The Audit Number is 1-3-1591, you will need this to setup the notification and it normally looks like the following in the Audit Log.

Open Gentran Server for Windows Configuration and select the "Audit/Notification" tab. Click on the "Notifications" button, the Gentran Notifications windows will open. Create a new Notification.

Select an operator to receive the notification.

Select OK and your all done. Note if you do not have operators or notifications setup they will have to be setup first. Do to the number of steps that will have to be a topic for a future post.

XML Common Root Tag

Have you ever cursed your software vendor for their seemingly endless lack of foresight when it comes to their choice of XML Root Tags? Boy I have... And they always want to charge you big bucks when you desire a necessary correction. As a professional integrator I love reusable code libraries and if you have ever worked with multiple XML documents in a single project you probably have guessed why I say this. Here are a few personal reasons why I like them.

• Reusing code (and Root Tags) saves programming time, which reduces costs.
• Sharing code  (and Root Tags) can help prevent bugs, basically the more code a system contains (to process multiple Root Tags)  the more bugs it's likely to have. 
• Separating code into common libraries lets programmers specialize in their particular strengths. 

Ok, time for me to get off the high horse. Just because our latest ERP project had some poor design work done on their EDI gateway, doesn't mean I need to complain about it. Besides this is a site for posting solutions, so here is my latest. The following script does one thing and only one thing very well. It will take an XML document and give it a new Root Tag, both start and finish.

The script is written in vbScript and it will add a common Root Tag to every XML file it finds in the input directory. Command Line is simple and is easy to script.

Enjoy and feel free to comment.

Stephen

 option explicit  
 Private strRootDir  
 Private strLoadDir  
 Private strOutputDir  
 Private strArchiveDir  
 Private objFSO  
 Private myStr  
 call Main()  
 sub Main  
      if (LoadParams) then  
           set objFSO = CreateObject("Scripting.FileSystemObject")  
           if (not objFSO.FolderExists(strLoadDir)) then objFSO.CreateFolder(strLoadDir)  
           if (not objFSO.FolderExists(strArchiveDir)) then objFSO.CreateFolder(strArchiveDir)  
           call ProcessFiles  
      else  
           DisplayUsage  
      end if  
 end sub  
 private sub DisplayUsage  
  WScript.Echo "Usage: cscript.exe Add_Root.vbs <RootDir> <OutputDir>"  
  WScript.Echo "Example: cscript.exe Add_Root.vbs C:\Users\Stephen\test\Load C:\Users\Stephen\test\Archive"  
  WScript.Echo "Example: cscript.exe Add_Root.vbs C:\Users\Stephen\HDTestInvoices\input C:\Users\Stephen\HDTestInvoices\output"  
 End sub  
 private function LoadParams  
      dim blnResult  
      if (WScript.Arguments.Count = 2) then  
           strRootDir = WScript.Arguments(0)  
           strOutputDir = WScript.Arguments(1)  
           if (right(strRootDir, 1) <> "\") then strRootDir = strRootDir & "\"  
           if (right(strOutputDir, 1) <> "\") then strOutputDir = strOutputDir & "\"  
           strLoadDir = strRootDir  
           strArchiveDir = strOutputDir  
           blnResult = true  
      else  
           blnResult = false  
      end if  
      LoadParams = blnResult  
 end function  
 private sub ProcessFiles  
      dim objSource  
      dim strExt  
      for each objSource in objFSO.GetFolder(strLoadDir).Files  
           strExt = ucase(mid(objSource.Name, InStr(objSource.Name, ".") + 1))  
           if (strExt = "XML") then  
                call ProcessFile(objSource)  
                objSource.Move strArchiveDir  
           end if  
      next  
 end Sub  
 Private sub ProcessFile(objSource)  
      dim objFile  
      dim strLine  
      dim arrFields  
      Dim intIndex  
      Dim blnIsHeader  
      Dim strTranID, strToMail  
      Dim detTranID  
      Dim s1  
      Dim strWorking   
  strWorking = strLoadDir & objSource.Name  
      Set objFile = objFSO.OpenTextFile(strWorking,1)  
      while (not objFile.AtEndOfStream)  
           strLine = objFile.ReadLine  
 '**************************************************************************************************  
           If ((Len(strLine) > 1) and (strLine = "<?xml version=" & """1.0""" & " ?>")) Then  
             strToMail = strLine & "<EDIDOC>"  
             WScript.Echo strLine  
           else  
     strToMail = strToMail & strLine  
     WScript.Echo strLine   
           End If  
 '*****************************************************************************************************  
      Wend  
      strLine = "</EDIDOC>"  
      WScript.Echo strLine  
  strToMail = strToMail & strLine  
  objFile.Close  
      Set objFile = objFSO.OpenTextFile(strWorking,2)  
      objFile.Write(strToMail)  
      objFile.Close  
 end Sub  

Windows 8/Office 2013/Gentran Server

Figured I had better post this as a heads up, as most of my Gentran developer brethren will more thank likely be purchasing new Windows 8 computers soon.

Two weeks ago I broke the monitor on my Asus laptop and had to go out and purchase a new one. So I got a Sony Vaio with Windows 8. After getting Windows setup and a new office 2013 install I went ahead and installed SQL Express and Gentran Server and all was wonderful in the land; however, the next morning I went to check my email using MS Outlook and I received an error that simply said "Some went wrong", nothing worked, not Word, Excel, anything. After checking msdn the noted solution was to re-install office. However, when I went to run the Office installation utility it told me to close the Gentran Services and it listed all of them. After closing out of the install utility and all the gentran services all of my office products started working fine and I was immediately able to restart Gentran and get to work.

Note: depending on your installation you may have to start your Gentran services manually after you have opened just 1 (any one) of the office products. Funny thing is after following this startup procedure I can enter and exit my office products all day. This appears to only apply upon a cold boot when everything is starting up for the first time.

Happy Mapping,

Stephen

Cisco VPN Client and Windows 8

Ok, so this one is not Gentran related. Like most people today I was forced to purchase a Windows 8 laptop, not because I wanted it but because that was all that BestBuy had in an i5 configuration.

The issue was getting the Cisco VPN Client to work on Windows 8 and it fails with the error: "Reason 442: Failed to Enable Virtual Adapter". I found 2 official answer from the Cisco website:

1. Cisco 64bit VPN Client does not support Windows 8.
2. Contact Cisco support.

Neither of which are answers that are acceptable to me, so I did a lot more rooting around on google before I found a work around that a guy calling himself "Youbrown" posted on learningnetwork.cisco.com. 

Here is the post. All I know is it worked like a champ for me.

Just to update, the legacy Cisco VPN client (5.0.07.0440 for x64, 5.0.07.0410 for x86) is working for some people. You need to apply a small workaround as explained below –

  

• Open Registry editor by typing regedit in Run prompt
• Browse to the Registry Key HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\CVirtA
• Select the DisplayName to modify, and remove the leading characters from the value data up to "%;" i.e.
• For x86, change the value data from something like "@oem8.inf,%CVirtA_Desc%;Cisco Systems VPN Adapter” to "Cisco Systems VPN Adapter”
• For x64, change the value data from something like "@oem8.inf,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows” to "Cisco Systems VPN Adapter for 64-bit Windows”
• Try connecting again

GSW vs GIS/SI

GSW and GIS: If your using GSW your easiest upgrade (specially if you have a lot of maps) by far is Sterling Integrator (aka GIS). As you know GSW is an EDI specific application that is designed to take any data format and convert it to EDI and vice versa. And because it is very specific to EDI and it is a much older, time tested product, you will find GSW to be considerably more mature at what it does (EDI). SI (aka GIS) is what they call an EAI or "Enterprise application integration". An EAI (according to Wikipedia) is "An integration framework composed of a collection of technologies and services which form a middleware to enable integration of systems and applications across the enterprise.", loosely translated you can use an EAI to tie together your accounting to your inventory management just as easily as you could receive an 850 purchase order, translate it and move it into your order management system (said Tongue-in-cheek). The upside is that all your GSW maps should port right over unless you have some extensive user exits.

Personal Note: I generally liken the comparison between GSW and SI as that between a good pickup and a Mach Truck. I can buy a pickup today and start hauling stuff from Home Depot almost instantly, which is pretty much the same with GSW; however, not so simple with the Mach, I still need to outfit it with an appropriate trailer and equipment. That said a well appointed Mach can out haul a pickup almost 10 to 1, this is also true of a well appointed SI system. Also a normal pickup is about 1/3 the size of a setup Mach, which is an ironic number because 1/3 just about says everything else you need to know when comparing the two.

GSW has about 1/3 the features of SI.

GSW is about 1/3 the price of SI .

GSW has about 1/3 the setup time of SI.

GSW takes 1/3 the training time and cost of SI.

Lastly projects under GSW generally take 1/3 the implementation time of SI.

Final note, if your shopping for a translator and all your planning to do with the application is receiving a data file from a trading partner and port it over to your accounting system; do yourself and other employees a favor and stick to GSW, there are a lot of thing you can do to cleanup and streamline your processes. However, if your an IT manager looking to standardize all those little applications that run your entire organization onto one standard development language and framework, to include all your EDI processes; then SI is definitely your baby.