Integration

Get Involved. Join the Conversation.

Topic

    Douglas Otsuka
    Error Stage File Loop For Each
    Topic posted June 4, 2018 by Douglas OtsukaBronze Medal: 1,250+ Points, tagged Adapters, Connection, Integration, REST, SOAP 
    340 Views, 34 Comments
    Title:
    Error Stage File Loop For Each
    Content:

    Hi, 

     

    I have the follow flow, i trying to read the payload and map the for-each to get the root element Folio, but when his call the loop nothing happens... i saw in the log and the xml it's been read in stage.

    <folio billNo="417723" clientID="1268075">
          <data>
            <trxCode>2005</trxCode>
            <description>ROOM SERVICE ALIMENTOS</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>36</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37601682</trxNo>
            <chequeNo>004003420180603004711</chequeNo>
          </data>
          <data>
            <trxCode>2006</trxCode>
            <description>MINIBAR ALIMENTOS</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>10</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37599295</trxNo>
            <chequeNo>005008620180602203229</chequeNo>
          </data>
          <data>
            <trxCode>2006</trxCode>
            <description>MINIBAR ALIMENTOS</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>-10</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37599297</trxNo>
            <chequeNo>005008620180602203229</chequeNo>
          </data>
          <data>
            <trxCode>2010</trxCode>
            <description>LOBBY BAR BEBIDAS ALCOOLICAS</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>10</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37601103</trxNo>
            <chequeNo>003020420180602234214</chequeNo>
          </data>
          <data>
            <trxCode>1000</trxCode>
            <description>DIARIA</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>Y</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37593302</trxNo>
            <chequeNo>37593300</chequeNo>
          </data>
          <data>
            <trxCode>1000</trxCode>
            <description>DIARIA</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>Y</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37613470</trxNo>
            <chequeNo>37613468</chequeNo>
          </data>
          <data>
            <trxCode>9999</trxCode>
            <description>DIARIA</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>Y</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37593300</trxNo>
            <chequeNo>37593300</chequeNo>
          </data>
          <data>
            <trxCode>9999</trxCode>
            <description>DIARIA</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>228.59</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37593301</trxNo>
            <chequeNo>37593300</chequeNo>
          </data>
          <data>
            <trxCode>9999</trxCode>
            <description>DIARIA</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>Y</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37613468</trxNo>
            <chequeNo>37613468</chequeNo>
          </data>
          <data>
            <trxCode>9999</trxCode>
            <description>DIARIA</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>228.59</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37613469</trxNo>
            <chequeNo>37613468</chequeNo>
          </data>
          <data>
            <trxCode>2021</trxCode>
            <description>BREAKFAST</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37593304</trxNo>
            <chequeNo>37593300</chequeNo>
          </data>
          <data>
            <trxCode>2021</trxCode>
            <description>BREAKFAST</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>0</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37613472</trxNo>
            <chequeNo>37613468</chequeNo>
          </data>
          <data>
            <trxCode>8001</trxCode>
            <description>TAXA SERV. A&amp;B</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>1</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37601104</trxNo>
            <chequeNo>003020420180602234214</chequeNo>
          </data>
          <data>
            <trxCode>8001</trxCode>
            <description>TAXA SERV. A&amp;B</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>1</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37599296</trxNo>
            <chequeNo>005008620180602203229</chequeNo>
          </data>
          <data>
            <trxCode>8001</trxCode>
            <description>TAXA SERV. A&amp;B</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>-1</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37599298</trxNo>
            <chequeNo>005008620180602203229</chequeNo>
          </data>
          <data>
            <trxCode>8001</trxCode>
            <description>TAXA SERV. A&amp;B</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>3.6</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37601683</trxNo>
            <chequeNo>004003420180603004711</chequeNo>
          </data>
          <data>
            <trxCode>8100</trxCode>
            <description>ISS SOBRE HOSPEDAGEM</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>10.13</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>Y</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37593303</trxNo>
            <chequeNo>37593300</chequeNo>
          </data>
          <data>
            <trxCode>8100</trxCode>
            <description>ISS SOBRE HOSPEDAGEM</description>
            <businessdate>02-JUN-2018</businessdate>
            <amount>10.13</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>Y</isIss>
            <isPayment>N</isPayment>
            <payType></payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37613471</trxNo>
            <chequeNo>37613468</chequeNo>
          </data>
          <data>
            <trxCode>9000</trxCode>
            <description>DINHEIRO</description>
            <businessdate>03-JUN-2018</businessdate>
            <amount>-70.86</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>Y</isPayment>
            <payType>01</payType>
            <depYn>N</depYn>
            <depType></depType>
            <idDepOrig></idDepOrig>
            <ccNumber></ccNumber>
            <ccValDate></ccValDate>
            <trxNo>37616833</trxNo>
            <chequeNo></chequeNo>
          </data>
          <data>
            <trxCode>9040</trxCode>
            <description>MASTERCARD</description>
            <businessdate>01-JUN-2018</businessdate>
            <amount>-457.18</amount>
            <confirmationNo>44226430</confirmationNo>
            <isIss>N</isIss>
            <isPayment>Y</isPayment>
            <payType>02</payType>
            <depYn>Y</depYn>
            <depType>A</depType>
            <idDepOrig></idDepOrig>
            <ccNumber>5454545454545454</ccNumber>
            <ccValDate>08/21</ccValDate>
            <trxNo>37575260</trxNo>
            <chequeNo></chequeNo>
          </data>
        </folio>
        

     

    Image:

    Comment

    • Jorge Herreria

      Hi

      Can you please share the Xpath Expression created in the For-each, please?

      And also an screen shot of the activity inside the for-each

    • Douglas Otsuka

      Follow the expression,

       

      The activity inside it's just a log for a test i check in the activity stream and he doesnt register the log not any once

       

      • Jorge Herreria

        Based on the Repeating Element value, I derive the Schema specifies <folio> as repeating (aka unbounded); then the payload example shows <data> as repeating as well. Therefor you need a nested For-Each loop to loop through-out both:

        Keep For-Each you have. Inside of  it  create another For-Each. It will be a nested one.  For the nested)For-Each select the $Teste/folio/data; maybe it will be $Tested/data... You'll see "data" under the $Teste tree; that's the one you want. Be sure you choose "data" from $Teste

        Now do your activity inside the nested for-each.

        If this works, may I ask you to set this as the correct answer to your question, please?

        Cheers

        Jorge

      • Hemanth Lakkaraju

        Repeating element from sample xml above shows as data but the configuration shows here as folio. You might want to update the repeating element expression in the for-each to point till data element for the loop to work.

    • Douglas Otsuka

      Hi,

      I tried to make the changes... But it still not working

       

    • Douglas Otsuka

      For each-map

    • Jorge Herreria

      hmm... Can you upload a picture of the inner for-each definition (Editor) please?

      Also upload a picture of the "asd" activity definition (Editor) please?

    • Douglas Otsuka

      Follo the print

    • Douglas Otsuka
    • Jorge Herreria

      You For-Each look good. You are doing it right, yes

      Now I'm suspicious on the xpath created at the for-each (maybe there is a bug). I'm suspicious the code is placing prefixes (nssrcmpr) and they might not be needed. If the Read File Stage schema defines the schema as unqualified (default), then the not all prefixes in the xpath are needed... The qualifiedFromDefault="qualified" attribute under <schema> node defines the qualification or not...

      If you knwo, can you check the schemas? or if you want, export the orchestration and upload the IAR to take a look.

    • Douglas Otsuka

      Hi

      Follow the schema of the read file stage

      <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:element name="SendBillItens">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="cmfbillItens">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="folio" maxOccurs="unbounded" minOccurs="0">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="data" maxOccurs="unbounded" minOccurs="0">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element type="xs:string" name="trxCode"/>
                                <xs:element type="xs:string" name="description"/>
                                <xs:element type="xs:string" name="businessdate"/>
                                <xs:element type="xs:string" name="amount"/>
                                <xs:element type="xs:string" name="confirmationNo"/>
                                <xs:element type="xs:string" name="isIss"/>
                                <xs:element type="xs:string" name="isPayment"/>
                                <xs:element type="xs:string" name="payType"/>
                                <xs:element type="xs:string" name="depYn"/>
                                <xs:element type="xs:string" name="depType"/>
                                <xs:element type="xs:string" name="idDepOrig"/>
                                <xs:element type="xs:string" name="ccNumber"/>
                                <xs:element type="xs:string" name="ccValDate"/>
                                <xs:element type="xs:string" name="trxNo"/>
                                <xs:element type="xs:string" name="chequeNo"/>
                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                        <xs:attribute type="xs:int" name="billNo" use="optional"/>
                        <xs:attribute type="xs:int" name="clientID" use="optional"/>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute type="xs:string" name="resortCode"/>
                  <xs:attribute type="xs:string" name="chaveAcesso"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>

       

      • Hemanth Lakkaraju
        Basically, the problem here is your schema doesn't have any targetNamespace - so when stage read, the xml will be associated with blank namespace something like - <sample xmlns=""/> instead of <sample> but the mapper is looking at a namespace - http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate based on the expressions seen above.
        Try below steps:
        1. Download the file to ICS
        2. Use decodeBase64(encodeReferenceToBase64 (fileReference from Download Response) - This will give xml as string.
        3. Use string manipulation to replace xmlns="" with xmlns="http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate"
        4. Use stage write using opaque schema and assign the encodeBase64(manipulated string in step3) to the opaque element.
        5. Then use stage read from the write location.
        • Douglas Otsuka

           Hemanth LakkarajuBronze Trophy: 5,000+ Points

           

          I not found the function replace i tried with translate and i got the error bellow

        • Douglas Otsuka

          Hi, 

           

          Could you please check if in doing right ?

           

           

          • Hemanth Lakkaraju

            Translate is not the right function. you have to use nested string-manipulation functions like concat(substring-before(), substring-after()) on the decoded string to apply the required namespace and then convert it back to base64.

          • Hemanth Lakkaraju

            Here is the updated flow with minor changes that I explained in earlier comment.

            1. Download the file to ICS
            2. Use decodeBase64(encodeReferenceToBase64 (fileReference from Download Response) - This will give xml as string.
            3. Use string manipulation to add targetNamespace xmlns="http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate"
            4. Use stage write using opaque schema and assign the encodeBase64(manipulated string in step3) to the opaque element.
            5. Then use stage read from the write location.
            • Douglas Otsuka

              Hi Hemanth, 

              The solution that you provided works, but the problem for file above 1 mb still... when i tested for file bellow 1 mb the integration works, when i try for file above 1 mb return error 

              File too big to read or doesnt exists orabpel.cmfitens_to_gl.ExecLetBxExe25.execute(ExecLetBxExe25.java:715)

              • Jorge Herreria

                The fact that step#3  (manipulation to add targetNamespace ) made the StageFile be able to read is telling me the actual payload (data) is NOT qualified. Therefor the XSD has to be unqualified elementFromDefault....

                It is possible to attache the full payload data? 

    • Douglas Otsuka
    • Jorge Herreria

      Give it a shot updating the schema.xsd  in the ReadStageFile with the attached one . I added the targetNamespace attribute:


      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns0="http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate"
                  targetNamespace="http://xmlns.oracle.com/cloud/adapter/nxsd/surrogate" elementFormDefault="qualified">  

       

    • Douglas Otsuka

      Hi, 

      the both solution return error in the read stage file “file too big or does not exist

    • Douglas Otsuka

      The file has less then 1 mb

    • Douglas Otsuka
    • Jorge Herreria

      The error does no look to be related with the suggested changes, or the changes took the process to the next step/issue.

      Check your FTP connections