X12 850 Purchase Orders are structured text-based documents that can be complex to parse and process. Converting them to JSON format simplifies data manipulation and integration with modern systems. This blog will guide you through parsing an X12 850 purchase order into JSON using MuleSoft’s X12 connector.
What You’ll Need
- A MuleSoft Anypoint Studio
- Basic understanding of MuleSoft and DataWeave
- An X12 850 purchase order sample
Steps Involved
1. Create a MuleSoft Flow:
- Create a new Mule flow and add an HTTP listener to receive the X12 850 file.
- Add an X12 connector configuration with necessary details like sender/receiver ID, qualifiers, and EDI version.
2. Parse the X12 850:
- Use the X12 Read operation to parse the incoming X12 850 message. This will convert the EDI data into a DataWeave-friendly format.
3. Transform to JSON:
- Use DataWeave to map the parsed X12 data into a JSON structure. You can create a custom data structure to match your specific requirements.
4. Handle Errors:
- Implement error handling using try-catch blocks to gracefully handle exceptions during parsing or transformation.
Example
Consider the below X12 850 Purchase Order:
ISA*00* *00* *ZZ*NTORETAIL *01*MYTHICAL *200915*0103*U*00401*000000018*0*P*>~
GS*PO*NTORETAIL*MYTHICAL*20200915*0103*18*X*004010~
ST*850*0001~
BEG*00*NE*PO-DEMO-20200912-002**20191018~
REF*VR*7429271~
PER*BD*Tim Brown*EM*tbrown@nto.com~
PO1*1*20**34.45****MG*324791~
PID*F****8.5″ x 11″ Copy Paper, 20 lbs, 92 Brightness, 5000/Carton (324791)~
N1*ST*Salesforce West*92*338~
N3*50 Fremont St*Suite 1456~
N4*San Francisco*CA*94105*US~
PO1*2*80**1.95****MG*2072174~
PID*F****Hype Tank Highlighters, Chisel, Yellow, Dozen (29203)~
N1*ST*Salesforce East*92*367~
N3*350 Mission St*Floor 5~
N4*San Francisco*CA*94105*US~
PO1*3*65**5.08****MG*163865~
PID*F****Notepads, 8.5″ x 11.75″, Wide Ruled, White, 50 Sheets/Pad, 12 Pads/Pack (51295/2~
N1*ST*Salesforce Tower*92*587~
N3*415 Mission St*Floor 3~
N4*San Francisco*CA*94105*US~
PO1*4*30*PH*11.77****MG*467951~
PID*F****Lightweight Moving & Storage Packing Tape, 1.88″W x 54.6 Yards, Clear, 6 Rolls (~
N1*ST*Tableau Palo Alto*92*1564~
N3*260 California Ave*Ste 300~
N4*Palo Alto*CA*94306*US~
CTT*4~
SE*26*0001~
GE*1*18~
IEA*1*000000018~
To parse the above Purchase Order, we will need X12 Connectors’ Read Operation. Create a Mule application and add an X12 connector from Exchange. Drag Read operation from X12 connector to separate flow.
<x12:read doc:name="Read" doc:id="a656f058-6d5a-4e0b-a21d-c2e93ebaea65" config-ref="X12_Read_850"/>
Read Operation requires X12 EDI Config reference:
<x12:config name="X12_Read_850" doc:name="X12 EDI Config" doc:id="023cf750-ebcd-4d2f-8c3b-199c68cae6f4" lineEnding="CRLF" sendUniqueGroupNumbers="true" requireUniqueInterchanges="false">
<x12:schemas >
<x12:schema value="/schemas/850.esl" />
</x12:schemas>
</x12:config>
In the config, we need to provide a schema for our 850 Purchase Order; you can find that schema in the X12 EDI Jar File:
Look for the 850.esl file and copy it inside the resources -> schemas folder. There are many versions of 850 Purchase Orders. We are using the 004010 version.
Also, copy the basedefs.esl file from the same 004010 version folder and paste it into resources -> schemas folder:
We need to transform the output of the Read operation to JSON, Drag and drop the Transform Message component next to the Read Operation:
<sub-flow name="x12-850-to-json-flow" doc:id="ad66438d-9f29-4f9b-87e4-2e946ab5ff37" >
<x12:read doc:name="Read" doc:id="a656f058-6d5a-4e0b-a21d-c2e93ebaea65" config-ref="X12_Read_850"/>
<ee:transform doc:name="X12 850 to Json" doc:id="7f9eddd0-e60f-478e-bcf4-4c1041c11540" >
<ee:message >
<ee:set-payload resource="dwl/edi-to-json.dwl" />
</ee:message>
</ee:transform>
</sub-flow>
DWL Script used to transform payload to JSON:
%dw 2.0
output application/json
var poNumber = payload.TransactionSets.v004010."850".Heading."020_BEG".BEG03[0]
---
payload.TransactionSets.v004010."850" map ( v850 , indexOfV850 ) -> {
PurchaseOrder: {
POPurpose: if ( v850.Heading."020_BEG".BEG01 == "00" ) "New" else if ( v850.Heading."020_BEG".BEG01 == "01" ) "Cancel" else "Update",
POType: v850.Heading."020_BEG".BEG02 default "",
PONumber: v850.Heading."020_BEG".BEG03 default "",
CustomerId: "A43836686",
CustomerName: "XYZ Incorporated",
BuyerName: v850.Heading."060_PER".PER02[0] default "",
BuyerEmailID: v850.Heading."060_PER".PER04[0] default "",
PODate: v850.Heading."020_BEG".BEG05 as String default "",
POLineItems: v850.Detail."010_PO1_Loop" map ( v010PO1Loop , indexOfV010PO1Loop ) -> {
PurchaseOrderLineId: poNumber ++ "-" ++ indexOfV010PO1Loop,
LineNum: v010PO1Loop."010_PO1".PO101 default "",
Quantity: v010PO1Loop."010_PO1".PO102 as String default "",
UnitPrice: v010PO1Loop."010_PO1".PO104 as String default "",
ItemDescription: v010PO1Loop."050_PID_Loop"."050_PID".PID05[0] default "",
SupplierItemNum: v010PO1Loop."010_PO1".PO109 default "",
POLineLocation: {
LineLocationId: v010PO1Loop."350_N1_Loop"."350_N1".N104[0] default "",
ShipToLocation: {
ShipToLocationCode: v010PO1Loop."350_N1_Loop"."350_N1".N104[0] default "",
ShipToLocationName: v010PO1Loop."350_N1_Loop"."350_N1".N102[0] default "",
Address: {
AddressLine1: v010PO1Loop."350_N1_Loop"."370_N3"[0].N301[0] default "",
AddressLine2: v010PO1Loop."350_N1_Loop"."370_N3"[0].N302[0] default "",
City: v010PO1Loop."350_N1_Loop"."380_N4".N401[0] default "",
PostalCode: v010PO1Loop."350_N1_Loop"."380_N4".N403[0] default "",
State: v010PO1Loop."350_N1_Loop"."380_N4".N402[0] default "",
Country: v010PO1Loop."350_N1_Loop"."380_N4".N404[0] default ""
}
}
}
}
}
}
Let’s try to understand each segment inside the 850 Purchase Order EDI
This EDI document represents a purchase order (PO) from a company named “NTORETAIL” to various Salesforce locations. Let’s break down each segment and its meaning:
Interchange Header (ISA):
- Identifies the sender and receiver, along with control information for the entire EDI transmission.
Group Header (GS):
- Defines a specific group of transactions within the interchange (in this case, all purchase orders).
Transaction Set Header (ST):
- Identifies the transaction type (850 – Purchase Order) and control number (0001).
Beginning Segment for Purchase Order (BEG):
- Provides general information about the purchase order, including type (NE – New Order), PO number (“PO-DEMO-20200912-002”), and order date (“20191018”).
Reference Number (REF):
- Contains a vendor reference number (“VR*7429271”).
- Details the contact person for the purchase order (Tim Brown, with email address).
- This segment repeats four times, detailing each ordered item.
- Sequence number (1, 2, 3, 4)
- Ordered quantity (20, 80, 65, 30)
- Unit price (34.45, 1.95, 5.08, 11.77)
- Unit of Measure (MG)
- Product/Service ID (324791, 2072174, 163865, 467951)
- This segment follows each PO1 segment, describing the ordered product.
- This segment repeats four times, detailing each ordered item’s “Sold To” information.
- Entity type (ST – Ship To)
- Name of the Salesforce location
- Seller’s code (92)
- Buyer’s code (338, 367, 587, 1564)
- These segments follow each N1 segment, providing the detailed shipping address for each Salesforce location.
- Indicates the total number of items ordered (4).
- Marks the end of the purchase order details.
- Indicates the end of the purchase order group (with 18 segments).
- Marks the end of the entire EDI transmission, referencing the control number from the ISA segment (000000018).
To understand the Read Operation output, please visit the link below:
X12 EDI Message Structure Hierarchy
Additional Considerations
- X12 Segment and Element Names: Refer to X12 standards for accurate segment and element names.
- Data Types: Handle different data types (numeric, date, etc.) appropriately in DataWeave.
- Complex Structures: For complex X12 structures, use recursive functions or arrays in DataWeave.
By following these steps and considering the key points, you can effectively parse X12 850 purchase orders into JSON using MuleSoft’s X12 connector and leverage the data for further processing and integration.
