As in Chapter 7, we're going to use invoices from Big Daddy's Gourmet Cocoa for our example. However, Big Daddy has now upgraded to a more capable order management and bookkeeping system. The new system supports a more comprehensive flat, hierarchical file structure than the CSV formats supported by the previous system.
The simple invoice example is composed of two levels of record groups. The group at the top level is the invoice itself, consisting of a header record, ship to address, one or more line item groups, and a summary record. The second group, the line item group, contains a line item record and an item description record.
This particular file has variable length records. Although Big Daddy's system uses variable length records, we could just as easily specify a fixed length record. Table 8.1 shows the logical layout of the invoice file.
Figure 8.1 shows the sample input invoice flat file.
Group | Record | Record Tag | Field Number | Field Name | Offset | Length | Data Type | Description |
---|---|---|---|---|---|---|---|---|
Invoice | Header | HDR | 1 | Record Tag | 0 | 3 | Alphanumeric | Record identifier |
2 | Customer Number | 3 | 20 | Alphanumeric | Identifier we have assigned to the customer in our system | |||
3 | Invoice Number | 23 | 20 | Alphanumeric | System-assigned invoice number | |||
4 | Invoice Date | 43 | 8 | Date | Date of invoice, formatted YYYYMMDD | |||
5 | PO Number | 51 | 20 | Alphanumeric | Customer purchase order number | |||
6 | Due Date | 71 | 8 | Date | Date that invoice amount is due for payment, formatted as YYYYMMDD | |||
Invoice | Ship To | SHP | 1 | Record Tag | 0 | 3 | Alphanumeric | Record identifier |
2 | Ship to Name | 3 | 40 | Alphanumeric | Name of the receiving location for the shipped order | |||
3 | Ship to Street 1 | 43 | 30 | Alphanumeric | First address line of the receiving location | |||
4 | Ship to Street 2 | 73 | 30 | Alphanumeric | Second address line of the receiving location | |||
5 | Ship to City | 103 | 20 | Alphanumeric | City of the receiving location | |||
6 | Ship to State or Province | 123 | 3 | Alphanumeric | State or province of the receiving location | |||
7 | Ship to Postal Code | 126 | 10 | Alphanumeric | Postal code of the receiving location | |||
8 | Ship to Country | 136 | 3 | Alphanumeric | Country of the receiving location | |||
Invoice/Line Item | Line | LIN | 1 | Record Tag | 0 | 3 | Alphanumeric | Record identifier |
2 | Item ID | 3 | 20 | Alphanumeric | Our identifier for the ordered item | |||
3 | Item Invoiced Quantity | 23 | 10 | Decimal number, space filled | The number of units to be invoiced | |||
4 | Item Unit Price | 33 | 10 | Implied decimal, two places, zero filled | Unit price in U.S. dollars | |||
5 | Extended Amount Due | 43 | 10 | Implied decimal, two places, zero filled | Total amount due for the invoiced item in U.S. dollars (unit price multiplied by the quantity invoiced) | |||
Invoice/Line Item | Item Description | DSC | 1 | Record Tag | 0 | 3 | Alphanumeric | Record identifier |
2 | Item Description | 3 | 80 | Alphanumeric | Description of the ordered item | |||
Invoice | Summary | SUM | 1 | Record Tag | 0 | 3 | Alphanumeric | Record identifier |
2 | Total Amount | 3 | 10 | Implied decimal, two places, zero filled | Total amount due on invoice in U.S. dollars | |||
3 | Number of Lines | 13 | 10 | Integer, space filled | Total number of invoice lines |
10 20 30 40 50 60 70 80 90 100 110 120 130 | | | | | | | | | | | | | | HDRBQ003 2002041 20021112AZ999345 20021212 SHPYazoo Grocers - NE Distribution Center 12 Industrial Parkway NW Portland ME 04101 LINHCVAN 120000000259000003108 DSCInstant Hot Cocoa Mix - Vanilla flavor LINHCMIN 240000000253000006072 DSCInstant Hot Cocoa Mix - Mint flavor SUM0000009180 2 HDRBQ003 2002042 20021112AW999346 20021212 SHPYazoo Grocers - SE Distribution Center Dock 37 3975 Hwy 75 Atoka OK 74525 LINHCVAN 360000000259000009324 DSCInstant Hot Cocoa Mix - Vanilla flavor LINHCMIN 720000000253000018216 DSCInstant Hot Cocoa Mix - Mint flavor SUM0000027540 2 HDRAY001 2002043 200211122002-0967 20021212 SHPCorner Drug and Sundries 14 Main Street Wichita KS 67201 LINHCVAN 240000000259000006216 DSCInstant Hot Cocoa Mix - Vanilla flavor SUM0000006216 1 HDRBR095 2002044 200211124397-0498 20021212 SHPBig Box Discounters - Store # 97 37 MegaMall Azusa CA 91702 LINHCMIN 1200000000253000030360 DSCInstant Hot Cocoa Mix - Mint flavor LINHCVAN 3600000000259000093240 DSCInstant Hot Cocoa Mix - Vanilla flavor LINHCDUC 2400000000259000062160 DSCInstant Hot Cocoa Mix - Dutch Chocolate flavor SUM0000185760 3 HDRBR095 2002045 200211124345-0498 20021212 SHPBig Box Discounters - Store # 45 45 Highway 76 Branson MO 65615 LINHCMIN 720000000253000018216 DSCInstant Hot Cocoa Mix - Mint flavor LINHCDUC 960000000259000024864 DSCInstant Hot Cocoa Mix - Dutch Chocolate flavor SUM0000043080 2 HDRDQ349 2002046 20021112987-43671 20021212 SHPMaple Leaf Grocers - DC #1 987 Yorkland Blvd Willowdale ON M2J 4Y8 CAN LINHCMOC 36000000000269000096840 DSCInstant Hot Cocoa Mix - Mocha flavor SUM0000096840 1 |
Listed below are the first three XML documents produced by the utility from this input file.
<?xml version="1.0" encoding="UTF-8"?> <FlatInvoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="FlatInvoice.xsd"> <Header> <RecordID>HDR</RecordID> <CustomerNumber>BQ003</CustomerNumber> <InvoiceNumber>2002041</InvoiceNumber> <InvoiceDate>2002-11-12</InvoiceDate> <PONumber>AZ999345</PONumber> <DueDate>2002-12-12</DueDate> </Header> <ShipTo> <RecordID>SHP</RecordID> <ShipToName> Yazoo Grocers - NE Distribution Center </ShipToName> <ShipToStreet1>12 Industrial Parkway NW</ShipToStreet1> <ShipToCity>Portland</ShipToCity> <ShipToStateOrProvince>ME</ShipToStateOrProvince> <ShipToPostalCode>04101</ShipToPostalCode> </ShipTo> <LineItemGroup> <LineItem> <RecordID>LIN</RecordID> <ItemID>HCVAN</ItemID> <ItemQuantity>12</ItemQuantity> <UnitPrice>2.59</UnitPrice> <ExtendedPrice>31.08</ExtendedPrice> </LineItem> <ItemDescription> <RecordID>DSC</RecordID> <Description> Instant Hot Cocoa Mix - Vanilla flavor </Description> </ItemDescription> </LineItemGroup> <LineItemGroup> <LineItem> <RecordID>LIN</RecordID> <ItemID>HCMIN</ItemID> <ItemQuantity>24</ItemQuantity> <UnitPrice>2.53</UnitPrice> <ExtendedPrice>60.72</ExtendedPrice> </LineItem> <ItemDescription> <RecordID>DSC</RecordID> <Description> Instant Hot Cocoa Mix - Mint flavor </Description> </ItemDescription> </LineItemGroup> <Summary> <RecordID>SUM</RecordID> <TotalAmount>91.80</TotalAmount> <NumberOfLines>2</NumberOfLines> </Summary> </FlatInvoice> |
<?xml version="1.0" encoding="UTF-8"?> <FlatInvoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="FlatInvoice.xsd"> <Header> <RecordID>HDR</RecordID> <CustomerNumber>BQ003</CustomerNumber> <InvoiceNumber>2002042</InvoiceNumber> <InvoiceDate>2002-11-12</InvoiceDate> <PONumber>AW999346</PONumber> <DueDate>2002-12-12</DueDate> </Header> <ShipTo> <RecordID>SHP</RecordID> <ShipToName> Yazoo Grocers - SE Distribution Center </ShipToName> <ShipToStreet1>Dock 37</ShipToStreet1> <ShipToStreet2>3975 Hwy 75</ShipToStreet2> <ShipToCity>Atoka</ShipToCity> <ShipToStateOrProvince>OK</ShipToStateOrProvince> <ShipToPostalCode>74525</ShipToPostalCode> </ShipTo> <LineItemGroup> <LineItem> <RecordID>LIN</RecordID> <ItemID>HCVAN</ItemID> <ItemQuantity>36</ItemQuantity> <UnitPrice>2.59</UnitPrice> <ExtendedPrice>93.24</ExtendedPrice> </LineItem> <ItemDescription> <RecordID>DSC</RecordID> <Description> Instant Hot Cocoa Mix - Vanilla flavor </Description> </ItemDescription> </LineItemGroup> <LineItemGroup> <LineItem> <RecordID>LIN</RecordID> <ItemID>HCMIN</ItemID> <ItemQuantity>72</ItemQuantity> <UnitPrice>2.53</UnitPrice> <ExtendedPrice>182.16</ExtendedPrice> </LineItem> <ItemDescription> <RecordID>DSC</RecordID> <Description> Instant Hot Cocoa Mix - Mint flavor </Description> </ItemDescription> </LineItemGroup> <Summary> <RecordID>SUM</RecordID> <TotalAmount>275.40</TotalAmount> <NumberOfLines>2</NumberOfLines> </Summary> </FlatInvoice> |
<?xml version="1.0" encoding="UTF-8"?> <FlatInvoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="FlatInvoice.xsd"> <Header> <RecordID>HDR</RecordID> <CustomerNumber>AY001</CustomerNumber> <InvoiceNumber>2002043</InvoiceNumber> <InvoiceDate>2002-11-12</InvoiceDate> <PONumber>2002-0967</PONumber> <DueDate>2002-12-12</DueDate> </Header> <ShipTo> <RecordID>SHP</RecordID> <ShipToName>Corner Drug and Sundries</ShipToName> <ShipToStreet1>14 Main Street</ShipToStreet1> <ShipToCity>Wichita</ShipToCity> <ShipToStateOrProvince>KS</ShipToStateOrProvince> <ShipToPostalCode>67201</ShipToPostalCode> </ShipTo> <LineItemGroup> <LineItem> <RecordID>LIN</RecordID> <ItemID>HCVAN</ItemID> <ItemQuantity>24</ItemQuantity> <UnitPrice>2.59</UnitPrice> <ExtendedPrice>62.16</ExtendedPrice> </LineItem> <ItemDescription> <RecordID>DSC</RecordID> <Description> Instant Hot Cocoa Mix - Vanilla flavor </Description> </ItemDescription> </LineItemGroup> <Summary> <RecordID>SUM</RecordID> <TotalAmount>62.16</TotalAmount> <NumberOfLines>1</NumberOfLines> </Summary> </FlatInvoice> |