Author Archives: Mike Coyle

Data Integration 101 Using the EXTOL Business Integrator

Data Integration is defined as, “the combining of fragmented data residing in different sources and locations which are aligned to support business goals”.  There are many reasons to bring data of different types (flat file, DB2, or even spreadsheets), possibly residing on different servers, to one main location to be integrated together.  If you do Electronic Data Interchange (EDI), translating data between an EDI fixed format and application variable format files, then you have already been doing a piece of the data integration puzzle. Continue reading

To SDQ or Not to SDQ

Does the handling of the SDQ segments within your EDI, applications and business practices puzzle you?  Well, you’re not alone. SDQ segments have been around for a while now, but still seem to cause havoc for IT staff!

SDQ segments occur in several message types, including 852 and 860, but the most common message would be the 850 Purchase Order (PO).  The traditional PO included one Ship-To location per order and the layout was very similar to the document itself.  The purchase order layout included a header (PO number and date, purchaser name, address, etc), detail (item, quantity, and price) and a summary.  The disadvantages to the easy layout were the redundant use of information and the large batches of these documents.

To capture this data, your application system most likely included two files: header/summary and a detail using a unique key relationship.  The formatting was easy to read, having only one ship-to per order.  The traditional PO structure made it simple for applications as well, allowing easy-to-create formats in file setups.

The original reason for the SDQ segment was to decrease the number and the size of transactions by allowing multiple ship-to locations per order.   On a purchase order SDQ segment, it lists quantity per location and locations. In the P0102 element there is the total quantity.   The SDQ03 holds a location number and the SDQ04 lists the quantity for the location in the SDQ03. The SDQ05 and 06 follow the same pattern which is repeated up to SDQ22.  Hence “pairing” indicating location and quantity pairing, the segment contains multiple “pairs” for a specific item noted in the PO1 segment. There can be up to 500 SDQ segments containing ten “pairs” per segment.  Changing the traditional PO to a PO with an SDQ allowed partners to include more than one order per transaction.  It also accomplishes what it set out to do: decreasing the size of the orders and removing the repeating data.  However, creating new advantages creates new disadvantages.  Some of the disadvantages include the assumption that the location address information can be referenced by the user’s application. The SDQ multiple locations format doesn’t include address information for these locations.  Also, SDQ standard currently does not state how many “pairs” are required per segment.  You may have many SDQ segments each having a varying amount of “pairs” on each line. These disadvantages may not sound like major issues, but just ask the IT staff of other companies who needed a solution (sometimes very quickly) on how to capture this newly formatted data and the havoc it can cause.

Here in lies the question: what would you do if you receive a PO destined for multiple locations and currently you are configured with 2 files: a header/summary and detail?  Do you create additional fields for every pair in the existing detail file?  How many fields should be created?  How are the blank pairings handled within the application?  How will your application now respond to multiple detail lines in the same order?

Your translator and application will ultimately determine the route to take in handling multiple orders within an SDQ order.  However, it may be advantageous for you to look into creating a new SDQ file and not an extension of the detail file.  This would allow the writing of records on the PO1 for detail information and SDQ data to the SDQ file.  Verify if your current translator can write a new record for EACH pairing, ultimately eliminating the possibility of writing blank pairs to your application.  Keep in mind that in order to satisfy the needs of your application you still may need some type of post process.  This post process would handle application requirements, such as manipulation of quantities to even breaking out each SDQ pair into individual purchase orders.  Happy Mapping!