This post explains in a brief detail how to create a simple BizTalk Server Integration to receive, transform, group and sort an EDI 856 Message (Advance Shipping Notice) to a simple Flat File representation. More specifically we are going to review how to:
Review/Analyze an EDI 856 Message structure from three different perspectives: HTML (Conceptual), Raw Data and Xml Encoded
Review how to map global data, groups of data using global keys, specific data using identifier keys
Review how to isolate nodes, groups and sort data using key values
This blog post was written assuming the target audience is familiar with Microsoft BizTalk Server, Microsoft Visual Studio and Extensible Stylesheet Language Transformation (XSLT) code. It also assumes the target audience understand the basic concepts about Enterprise Application Integration and Electronic Data Interchange.
The Business Scenario that will be covered with this blog post is pretty simple: A Trading Partner (Supplier) will transmit regularly Advance Shipping Notices (EDI 856 ASN) to a Manufacturing Center (Manufacturer). Every transmission can contain one or multiple Purchase Orders and every Purchase Order can contain one or several Rolls. All the Rolls will include the following measurements: Diameter, Width, Length and Gross Weight.
The Manufacturer’s internal platform requires to receive the data into a Delimited Flat File created with these rules:
The Purchase Orders will be populated in ascending order
The Rolls inside every Purchase Order will be extracted following the same order
A Unique List with the Dimension ‘Width’ per Rolls’ Group will be created
The Flat File will include a Header Record per PO+Width Unique Key and several Detail Records including the PO and Roll Data
Flat File Structure
Let’s start the data analysis understanding the EDI 856 Structure. Generally speaking, an EDI document is a simple text file consisting of three parts: Header, Detail and Footer. This blog post covers the Detail Segment, it contains all of the business information that gives meaning to the document.
It’s easy and recommendable to use three different EDI representations to analyze and determine the mapping conditions.
A human friendly presentation that is achieved through the use of templates.
- Raw Data
Raw data (also known as primary or source data) is the data that has not been processed for use. In EDI terms, text formatted using the EDI Standard.
- XML Encoded
XML is a flexible way to create and share common information formats. BizTalk Server uses XML internally to define the data and structure of business documents so we we will considerate this EDI representation as well.
In a nutshell, we confirm the business scenario is accurate, the EDI Messages we are dealing with contain Purchase Orders (HL Loops where HL03 = ‘O’) and every Purchase Order contains several items (HL Loops where HL03=’I’), we will cover it in detail below. Something to consider “The loop segments (PO,Items) are included sequentially and, from an XML stand point, are not hierarchically related”.
We will create a BizTalk Application using the Publish-Subscribe Architecture Model. A Receive Port will pick up the EDI Messages from an input folder. BizTalk Server will resolve the Party and validate the Messages at the Receive Pipeline, it will publish these messages to the MessageBox Database. A send port will be configured to subscribe these messages, a map will be used to implement the transformation from EDI to a Generic Flat File and, the final result will be delivered to an output folder.
All right, it’s time to have some fun 😀 . The following steps will provide a high level walk through of this build process.
- First at all, we have to create a Visual Studio Solution and an Empty BizTalk Server Project. We also need to create a Strong Name and, just for internal organization purposes, we will create three folders inside the project: Schemas, Pipelines and Maps.
- Second, we have to add the EDI 856 Schema (Version 4010) and Generate the Schema for the Delimited Flat File.
- Third, since the Solution is dealing with a Delimited Flat File, we need to create/include a Pipeline to serialize the XML document into delimited flat file format.
- Fourth, we have to create a new Map to transform from EDI 856 to the Delimited Flat File. Also, this blog post covers the use of a whole External XSLT style sheet to conduct the transformation, consequently, we have to create an XSL File as well.
Until this point, we have the base code required for the integration. The following section provides a step-by-step guideline to create the XSLT Map.
- Setting up Namespaces. A common practice is to declare all the namespaces within the stylesheet header.
- Global Variables. In some cases the Globals Variables constitute a good option to handle constants and/or global scope values. In this case, the Shipment Id is a good candidate, it will be handled using a global var.
- First XPath Query. Our first goal is to locate/iterate all the Purchase Orders. We know from our data analysis the POs are included into the HL Loops where HL03 = ‘O’.
Behind the Scenes: In a human-readable sentence, these XPath Expressions executes the following processes:
- The first one match=”ns0:X12_00401_856″ reads and points to the XML Root node, every occurrence generates a call to the Main Template
- Once the main template is called, the target XML Root node is created (GenericASN) using the corresponding Namespace (n1, see above)
- Inside the new Target Root Node, a second XPath Expression is created to locate and iterate all the HLLoop1 nodes where the sub-element HL03 equals to “O”. For every occurrence, the Template PO is called
- Local Variables. As I mentioned before, the PO Template is called for every Purchase Order into the Global EDI (XML Representation), it receives the pointer to the occurrence generated by the Main PO Iteration. The first step to achieve inside this template is to determine the Purchase Order Number and store it into a local variable POrder.
Behind the Scenes: Take note the XPath Expression includes the sub-elements’ internal paths (ns0:PRF…), it doesn’t include the whole path (ns0:HLLoop1/ns0:PRF…) since the pointer is located inside the HLoop1, the same logic applies for all the map.
- Current PO Nodes. Next, we have to isolate the nodes/elements that belong to the current Purchase Order. It can be achieved using a new XPath Expression that combines the Purchase Order as a key and the current PO pointer (position inside the Global XML). An array is generated as result.
Behind the Scenes: Once again, in a human-readable sentence, this XPath Expression selects all the nodes that accomplish with the following conditions:
- The sub-element HL03 value equals to “I” or “D” (parts and measurements)
- The preceding sibling HLoop1 where HL03 equals to “O” and PRF01 equals to the Purchase Order Number (Purchase Order Node)
- The Purchase Order sequence (position into the Global XML) equals to the current pointer only
- Physical Dimensions Width. All right, we have until now the Purchase Order and its nodes, we need to determine a Unique Dimensions List (Measurement Width) using the nodes array generated above. There are several options to achieve this goal, we will use a List Variable so we have to consider the following scenarios:
- The PO includes only one node
- The PO nodes include only one Width Measurement
- The PO nodes include several Width Measurements
Behind the Scenes: Some topics to review/take note into these XPath Expressions:
- The sentence copy-of is being used instead of value-of, the reason behind this decision is the iteration we need to apply later. The copy-of sentence gets the whole node while the value-of gets only the value (one node/measurement condition use cases)
- If the PO has only one node, the value is extracted directly
- If the PO has several nodes but only one measurement, the same logic applies, the value is extracted directly
- If the PO has several nodes and several measurements, a new XPath Expression is used to determine a Unique Value List. In a human-readable sentence, the expression reads the nodes one by one and extract only the measurements where there is not any occurrence equal on the following nodes
- Iterations. Finally we have all the data and keys that we need: The PO Number, the PO Nodes and the Measurements Unique List. It’s time to map all together and create the target file. The following section includes the logic and expressions to:
Behind the Scenes: Note the use of msxsl:node-set to convert a result tree fragment into a node set, it’s required to normalize the Unique List due the different scenarios described above.
- Iterate the Measurements’ Unique List
- Select the PO Rolls that accomplish with the Measurement Width in process
- Call a Template to create the Header Record per PO and Measurement
- Iterate the PO Nodes and call a Template to create a Detail Record per Roll
- Header and Detail Templates. These templates are pretty simple, they receive parameters and map single nodes according the target schema. In some cases, Xslt functions are used to calculate totals.
Behind the Scenes: Once again, take note the expressions used to call/inside these templates include referenced node positions only.
- The map is ready.
This blog post presents a simple publish-subscribe approach, all messages are published/subscribed using Ports in BizTalk. This solution requires the following BizTalk Artifacts:
- BizTalk Application. A simple BizTalk Application that references the EDI Application to make use of all the Schemas, Pipelines, etc. pre-built and provided by Microsoft.
- Parties, Profiles and Agreement. Also, a couple of Business Partners (Parties) with their corresponding Business Profiles and a simple Trading Partner Agreement.
- Receive Port and Location. The EDI 856 Messages will be picked up by BizTalk through the File Adapter, and they will be delivered to the Message Box. The EDI Disassembler component will take care of all the validations.
- Send Port. A send port will subscribe the messages received on the port described above, the Map and Pipeline created are used at this port to transform and transmit the the data to a target flat file.
Here we go, we are ready to test the solution. Once the Host Instance is running all we need to do is copy an EDI 856 file to the Input folder, BizTalk Server will pick up, validate, disassemble and translate it to a GenericASN instance. The result will be sent to the Output folder.
It’s ready. Fulfilling the Business Requirement, the EDI 856 Files are being translated to Flat Files, the results are grouped by Purchase Order/Measurement (Width) and sorted by Purchase Order Number. It’s time to reference my son’s favorite expression: It’s magic 🙂
In this blog post we have seen/explored a simple BizTalk Integration for EDI Messages, in specific the 856s – Advance Shipping Notice. I know, I know … I will cover the Acknowledgements, Exception Handling and Notifications in a future post, I think we have enough for now.