7 Replies Latest reply: Feb 20, 2013 5:36 PM by vladodias RSS

    File Adapter format translation problem

    opthomas
      This is my incoming payload from a legacy application

      "66386","9002011319","01-FEB-2013","","2114.11","ITEM","0.12","","85411","2310","0","85","GST","68085"
      "66386","9002011319","01-FEB-2013","","2114.11","ITEM","15.00","","85411","2311","0","85","GST","68085"
      "66386","9002011319","01-FEB-2013","","2114.11","ITEM","1823.24","","85411","2313","0","85","GST","68085"
      "66386","9002011319","01-FEB-2013","","2114.11","TAX","275.75","","","","","","GST",""
      "66386","9002013706","01-FEB-2013","","717.60","ITEM","624.00","","85411","2319","0","85","GST","68232"
      "66386","9002013706","01-FEB-2013","","717.60","TAX","93.60","","","","","","GST",""
      "66386","9002026588","03-FEB-2013","","2706.30","ITEM","2353.30","","85411","2319","0","85","GST","68085"
      "66386","9002026588","03-FEB-2013","","2706.30","TAX","353.00","","","","","","GST",""

      What we have here is 3 different record sets. Field 2 is the invoice number. What I need to do is translate this into something like

      <Container>
      <invoiceHeader>
      <InvoiceLines>

      The first 4 elements in each line are the header details and everything after that is line level detail.

      So the resultant xml will look something like this........ Please note I have left A LOT of the detail for xml out of this

      <container>
           <invoice>
                <Vendor>66386<>
                <InvoiceNum>9002011319<>
                <InvoiceDate>01-FEB-2013<>
                <Description/>
                <InvoiceAmount>2114.11<>
                <Lines>
                     <Line>
                          <LineType>ITEM<LineType>
                          <LineAmount>0.12<LineAmount>
                          <LineDescription/>
                          <Code1>85411<Code1>
                          <Code2>2310<Code2>
                          <Code3>0<Code3>
                          <Code4>85<Code4>
                          <TaxCode>GST<TaxCode>
                          <PONumber>68085<PONumber>
                     </Line>
                     <Line>
                          <>ITEM<><>15.00<><><><>85411<><>2311<><>0<><>85<><>GST<><>68085<>
                     </Line>
                     <Line>
                          <>ITEM<><>1823.24<><><><>85411<><>2313<><>0<><>85<><>GST<><>68085<>
                     </Line>
                     <Line>
                          <>TAX<><>275.75<><><><><><><><><><><><>GST<><><>
                     </Line>
                </Lines>
           </invoice>               
           <invoice>
                <>66386<><>9002013706<><>01-FEB-2013<><><><>717.60<>
                <Lines>
                     <Line>
                          <>ITEM<><>624.00<><><><>85411<><>2319<><>0<><>85<><>GST<><>68232<>
                     </Line>
                     <Line>
                          <>TAX<><>93.60<><><><><><><><><><><><>GST<><><>
                     </Line>
                </Lines>
           </invoice>
           <invoice>
                <>66386<><>9002026588<><>03-FEB-2013<><><><>2706.30<>
                <Lines>
                     <Line>
                          <>ITEM><>2353.30<><><><>85411<><>2319<><>0<><>85<><>GST<><>68085<>
                     </Line>
                     <Line>               
                          <>TAX<><>353.00<><><><><><><><><><><><>GST<><><>
                     </Line>
                </Lines>
           </invoice>
      </container>
        • 1. Re: File Adapter format translation problem
          Sanjay Bharatiya
          Hi,

          The resultant XML you have posted is not correct. Please validate it and post the correct one.

          Thanks,
          Sanjay
          • 2. Re: File Adapter format translation problem
            opthomas
            <?xml version="1.0" encoding="windows-1252" ?>
            <container>
            <invoices>
            <invoice>
            <invoiceheader>
            <Vendor>66386</Vendor>
            <InvoiceNum>9002011319</InvoiceNum>
            <InvoiceDate>01-FEB-2013</InvoiceDate>
            <Description/>
            <InvoiceAmount>2114.11</InvoiceAmount>
            </invoiceheader>
            <invoiceLines>
            <line>
            <LineType>ITEM</LineType>
            <LineAmount>0.12</LineAmount>
            <LineDescription/>
            <Code1>85411</Code1>
            <Code2>2310</Code2>
            <Code3>0</Code3>
            <Code4>85</Code4>
            <TaxType>GST</TaxType>
            <PONumber>68085</PONumber>
            </line>
            <line>
            <LineType>ITEM</LineType>
            <LineAmount>15.00</LineAmount>
            <LineDescription/>
            <Code1>85411</Code1>
            <Code2>2311</Code2>
            <Code3>0</Code3>
            <Code4>85</Code4>
            <TaxType>GST</TaxType>
            <PONumber>68085</PONumber>
            </line>
            <line>
            <LineType>ITEM</LineType>
            <LineAmount>1823.24</LineAmount>
            <LineDescription/>
            <Code1>85411</Code1>
            <Code2>2313</Code2>
            <Code3>0</Code3>
            <Code4>85</Code4>
            <TaxType>GST</TaxType>
            <PONumber>68085</PONumber>
            </line>
            <line>
            <LineType>TAX</LineType>
            <LineAmount>275.75</LineAmount>
            <LineDescription/>
            <Code1/>
            <Code2/>
            <Code3/>
            <Code4/>
            <TaxType>GST</TaxType>
            <PONumber/>
            </line>
            </invoiceLines>
            </invoice>
            <invoice>
            <invoiceheader>
            <Vendor>66386</Vendor>
            <InvoiceNum>9002013706</InvoiceNum>
            <InvoiceDate>01-FEB-2013</InvoiceDate>
            <Description/>
            <InvoiceAmount>717.60</InvoiceAmount>
            </invoiceheader>
            <invoicelines>
            <line>
            <LineType>ITEM</LineType>
            <LineAmount>624.00</LineAmount>
            <LineDescription/>
            <Code1>85411</Code1>
            <Code2>2319</Code2>
            <Code3>0</Code3>
            <Code4>85</Code4>
            <TaxType>GST</TaxType>
            <PONumber>68232</PONumber>
            </line>
            <line>
            <LineType>TAX</LineType>
            <LineAmount>93.60</LineAmount>
            <LineDescription/>
            <Code1/>
            <Code2/>
            <Code3/>
            <Code4/>
            <TaxType>GST</TaxType>
            <PONumber/>
            </line>
            </invoicelines>
            </invoice>
            <invoice>
            <invoiceheader>
            <Vendor>66386</Vendor>
            <InvoiceNum>9002026588</InvoiceNum>
            <InvoiceDate>03-FEB-2013</InvoiceDate>
            <Description/>
            <InvoiceAmount>2706.30</InvoiceAmount>
            </invoiceheader>
            <invoicelines>
            <line>
            <LineType>ITEM</LineType>
            <LineAmount>2353.30</LineAmount>
            <LineDescription/>
            <Code1>85411</Code1>
            <Code2>2319</Code2>
            <Code3>0</Code3>
            <Code4>85</Code4>
            <TaxType>GST</TaxType>
            <PONumber>68085</PONumber>
            </line>
            <line>
            <LineType>TAX</LineType>
            <LineAmount>353.00</LineAmount>
            <LineDescription/>
            <Code1/>
            <Code2/>
            <Code3/>
            <Code4/>
            <TaxType>GST</TaxType>
            <PONumber/>
            </line>
            </invoicelines>
            </invoice>
            </invoices>
            </container>
            • 3. Re: File Adapter format translation problem
              vladodias
              Hi,

              I suggest you do the transformation in two steps, firstly use the native translation (fileAdapter) to translate it to a simpler flat structure, repeating the invoice number and invoice details. Then you can use a xslt to create the final structure, breaking down by invoice number. I reckon it's going to be hard to go from native to the master/detail structure in only one step.

              Cheers,
              Vlad
              • 4. Re: File Adapter format translation problem
                opthomas
                Ok... But just a little confused on how to get the uniqueness at the header level
                • 5. Re: File Adapter format translation problem
                  vladodias
                  InvoiceNum looks like a good identifier, you can use distinct-values on InvoiceNum and then get all the lines for the invoice...
                  • 6. Re: File Adapter format translation problem
                    opthomas
                    Sorry Vlad ...... Having a mental block on this one. Do you have some example code you could provide?
                    • 7. Re: File Adapter format translation problem
                      vladodias
                      Say you got a structure like bellow with repetitive ids...
                      <root>
                      <entry><id>1</id><detail>1detail_a</detail></entry>
                      <entry><id>1</id><detail>1detail_b</detail></entry>
                      <entry><id>2</id><detail>2detail_c</detail></entry>
                      <entry><id>2</id><detail>2detail_d</detail></entry>
                      </root>
                      Now you want to eliminate the duplicates to achieve something like this...
                      <root>
                      <entry>
                      <id>1</id>
                      <details>
                      <detail>1detail_a</detail>
                      <detail>1detail_b</detail>
                      </details>
                      </entry>
                      <entry>
                      <id>2</id>
                      <details>
                      <detail>2detail_c</detail>
                      <detail>2detail_d</detail>
                      </details>
                      </entry>
                      </root>
                      To do it you need a transformation like this...
                      <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
                      <xsl:template match="/">
                      <xsl:variable name="root" select="/"/>
                      <root>
                      <xsl:for-each select="distinct-values(/root/entry/id)">
                      <xsl:variable name="id" select="."/>
                      <entry>
                      <id><xsl:value-of select="$id"/></id>
                      <details>
                      <xsl:for-each select="$root/root/entry[id = $id]/detail">
                      <detail><xsl:value-of select="."/></detail>
                      </xsl:for-each>
                      </details>
                      </entry>
                      </xsl:for-each>
                      </root>
                      </xsl:template>
                      </xsl:stylesheet>
                      Cheers,
                      Vlad