Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Stephen Bryant
    Extract Data from SaaS to VBCS ApplicationAnswered
    Topic posted June 10, 2019 by Stephen BryantBronze Trophy: 5,000+ Points, tagged Adapters, Connection, ERP Cloud, Integration, Mapping, Orchestration, PaaS, REST, SOAP, Web Services 
    185 Views, 41 Comments
    Title:
    Extract Data from SaaS to VBCS Application
    Summary:
    Extract Data using an OTBI Report
    Content:

    Hello,

    I have a requirement to retrieve data from our Oracle ERP Cloud applications. I need to extract the data via an OTBI report and then display those results in a VBCS application.  I realize that the report is retrieved from SaaS using a SOAP service (that I can connect to in the SOAP UI) but VBCS only uses REST so a conversion is necessary.

    Has anyone done this?  In particular, how does the mapping from SOAP to REST work?

    Thanks,

    Steve

    Version:
    19.1.3

    Best Comment

    Kunal Sonje

    Yes, Correct. We have used the same in one of the extension and works perfectly fine.

    Comment

     

    • Mani

      have you tried BIP REST api to run report? here is an illustration of the same. hope it helps

    • Kunal Sonje

      Hi Steve,

      You can create REST based integration in OIC which will act as wrapper and will be called from VBCS.

      In OIC integration -

      • Call BIP report
      • Write data to stage
      • Read data
      • Send JSON response back to VBCS

      Thanks,

      Kunal

      • Stephen Bryant

        Kunal,

        Thanks for your reply.  Support is telling me the BIP Rest Service is not supported in Fusion Applications.  I think you are saying that I would retrieve the SaaS data using the SOAP Service and then use a REST wrapper in OIC with a mapping to the SOAP response.

        Is that correct?  That was the original approach I was taking.

        Thanks again,

        Steve

        • Kunal Sonje

          Yes, Correct. We have used the same in one of the extension and works perfectly fine.

          • Stephen Bryant

            Kunal,

            I am having trouble here.  I am not sure how do this exactly.  Here is what I am doing.

            Create REST Call from VBCS with the JSON

            Map is created but not updated yet.

            Create SOAP call to ERP

            Map is Created

            I've never worked with SOAP, only REST so I am unsure how to first, bring down the file from ERP to stage and second, how to map the data.  

            Do you have any helpful steps (in detail) or screenshots that could help?  I could figure it out from there.

            Sorry for such a big ask but I am stuck and appreciate all your help.

            Thanks,

            Steve

             

            • Kunal Sonje

              Hi Steve,

              PFA snapshot with details. Let me know if you need any other inputs from my side.

              Thanks,

              Kunal

              Report.png (42KB)
              • Stephen Bryant

                Thanks so much Kunal!

                I will keep you posted!

                Steve

              • Stephen Bryant

                Kunal,

                Thanks for the information.  I believe it will be helpful in the future.  There is a slight difference in the data I am trying to retrieve.  My report is an Oracle Transactional Business Intelligence Report (OTBI) and not a BIP (xmlpserver) report.

                If you have any information on that, that would be great.  For now I am going to look on the other forums.

                Thanks,

                Steve

              • Stephen Bryant

                Kunal and Mani,

                Here is the latest in this saga.  I've successfully made the SOAP call to retrieve the OTBI Report and the results are in XML.  As I understand it, I need to write the file and decodeBase64 string.  

                This is where I am running into problems.  I'm not sure exactly what to map from the return results nor how to do the decode.  I mapped the rowset from the result and applied the decodeBase64 function in the mapper but that is not working.

                Following are two screenshots showing the mapper and the decode function.

                Any help of course is greatly appreciated.

                Thanks,

                Steve

                • Stephen Bryant

                  Here is the other screenshot.

                  • Jayashree Keerthivasan

                    Hi Steve,

                    Can you please try to use the decodebase64toReference as shown in the screenshot below.

                    Thank you,

                    Jayashree

                    • Stephen Bryant

                      Thanks Jayashree.  Much appreciated.

                      Unfortunately I am still getting a 500 error.

                      Here is what I used based on the screenshot you uploaded:

                       

                      <xsl:value-of select='oraext:decodeBase64($getOTBIInvoiceHoldsReport/nsmpr1:executeXMLQueryResult/nsmpr1:return/nsmpr1:reportBytes)'/>

                       

                      I am not sure how to map to the opaque element as you can see in the screenshot below, there are three items under executeXMLQueryResult/return so I mapped the highest node.  I'm not sure if that is correct.

                      This is an OTBI report that returns XML and I've never done this before so your help very appreciated.

                      I need to run off to another meeting now but will check back later.

                      Thanks,

                      Steve

                       

                       

                      • Mani

                        try this old thread for details on decoding BIP output

                        • Stephen Bryant

                          Mani,

                          Okay, I've managed to write the file by not decoding base 64.  However, I am having trouble with the xsd for reading the file.

                          Here is the part I do not quite understand form the old thread,

                          "While Reading, build XSD, with starting XML tag as ROWSET -> ROW ->Your elements from BI report (I used flattenXML as true in BI report).."

                          I have the elements for the BI report.  Do you have an example of the XSD file I can follow?  I am not sure of the syntax for the ROWSET Tag or flatten XML attribute.  Also, it appears we do not need to decode the file?  Is that true?

                          Thanks.

                          Steve 

                           

                           

                           

                           

                          • Mani

                            Steve,

                            haven't tried the approach Sridhar used. I can answer any questions you may have on the approach that I suggested in that thread. 

                            • Stephen Bryant

                              Mani,

                              Okay, no problem.  I will continue with your approach.  Right now, the problem is decoding the file.  I cannot seem to write the file to the OIC directory.  I will play with that a little more and get back to you.

                              Thanks, you've been a great help.

                              Steve

                            • Stephen Bryant

                              Mani,

                              I am uploading the iar file for you to review at your convenience.  I'm not sure what the problem is.  I am at the write file/decodeBase64ToReference point.  That is where the problem is.

                              Now, to digress.   I've configured the integration as an "app driven orchestration" starting with the Trigger for the REST endpoint and then proceed to get the report via SOAP.  The confusing part of that process is I have to provide a Request Body which is the JSON of my OTBI Report.  This seems counter-intuitive to me since that it was I want to receive to show in VBCS.  Check out the file and you will see what I mean.

                              Of course, only if you have time.

                              Thanks,

                              Steve  

                              • Stephen Bryant

                                sorry, here is the file.

                                OTBI_GET_REPORT_01.00.0000.iar (45KB)
                              • Stephen Bryant

                                Mani,

                                Okay, I can see the data in the error message attached.  You will notice an error as follows:

                                Invalid logical path [<rowset xmlns=\\\"urn:schemas-microsoft-com:xml-analysis:rowset\\\">

                                In the XML returned in the SOAP UI you can see this tag.  Somehow, this causing the XPATH function, "EncodeBase64ToReference" to fail.

                                Again, this is an OTBI custom analysis, not a BI Publisher (XDO) report.  I cannot find any data anywhere that indicates how to retrieve OTBI Custom Analyses in Integration Cloud.

                                This simply does not work at this point and I have no idea how to fix it.

                                Thanks,

                                Steve

                                xmlError.txt (6KB)
                                • Hemanth Lakkaraju

                                  XML-22044: (Error) Extension function error: Error invoking 'encodeReferenceToBase64':'java.io.IOException:

                                  The function is encodeReferenceToBase64 and the parameter to this should be a file reference. Looks like you are assigning a a decodeBase64 (xml content) value to this function which is invalid and is the reason of failure.

                            • Stephen Bryant

                              Mani,

                              I am using the SOAP Web Service to extract OTBI Data.  This is different from the thread you referred me to earlier.  I am attaching a how to document I received from Oracle that describes how I am retrieving data.

                              If I use the web services mentioned in the thread, I cannot see the OTBI Analyses files.  They do not exist  on XMLPSERVER.  I can only access the OTBI Analyses using the SAW web service indicated in the attached document.  That is where I am having the problems.  

                              Is this the right approach for integration cloud?  I cannot read the file at all at this point.  I really need help.

                              Thanks,

                              Steve

                              Retrieve Oracle SaaS Data using OTBI Report Final.pdf (586KB)
                              • Mani

                                Hi Steve,

                                yes, you cannot use XML report webservice. the MOS thread that I posted earlier does have two parts - one for XMLP and the second one for OTBI. the webservice they listed for OTBI seems to be same as the one mentioned in pdf you attached above. have you tried it in SoapUI? were you able to fetch result?

                                I haven't used OTBI analysis webservice before but will give it a try.

                                Thanks,

                                • Stephen Bryant

                                  Mani,

                                  Thanks for getting back.  Yeah, I am able to retrieve the content in the SoapUI no problem.  

                                  Let me know how it goes.

                                  This has been really frustrating so I appreciate your patience!

                                  Steve

                                  • Mani

                                    ok, I just tested a sample OTBI analysis in SoapUI and it appears to be a 2 step process as detailed in the document. I could fetch the output and it is not encrypted unlike BIP report output. so here is what you can try in OIC

                                    1. create a SOAP adapter based connection in OIC for url https://[YourHost]/analytics-ws/saw.dll/wsdl/v7

                                    2. first invoke SAWSessionService using above connection

                                    3. then invoke XmlViewService making use of SessionId fetched in step#2

                                    only concern is passing password directly in mapper to generate SessionId. but for now you can try creating orchestration to see how it works

                                    • Stephen Bryant

                                      Mani,

                                      Yes, that is what I have done.  I can get to the point of fetching the report data and writing it to a directory and reading it using the same schema.  However I need a schema that breaks out the data so I can reference it in VBCS.  But ICS will not take my schema to accomplish this.  ICS only wants the same schema used in the write process.  

                                      I'm not sure what is going on there.

                                      Here is the schema again.

                                      Steve

                                      opaqueSchema2.xml (1011 Bytes)
                                      • Mani

                                        I haven't seen how the output comes back in OIC but per response that's coming back from XmlViewService in SoapUI, the output is not encrypted for OTBI analysis. so, if you write the output using a stage activity, you'd get access to individual elements in the output schema right? Add a mapper activity after Stage Write and see if individual elements from the OTBI are available. 

                                        • Stephen Bryant

                                          Mani,

                                          This thread sure is growing!

                                          Since the report output is not encrypted, do we still need to use an opaque schema?

                                          Steve

                                        • Stephen Bryant

                                          Mani,

                                          Check out the attached file.  This is the endodedBase64 data.  I sent the file to an FTP Site and was able see the data.  However, I cannot read the data at all.  It is definitely a schema issue.  The trick is how to create a schema that will work with this data.

                                          Thanks again,

                                          Steve

                                          encoded64OutputfromSOAP.txt (4KB)
                                          • Hemanth Lakkaraju

                                            This is not encodedBase64 text. Probably you meant decodedBase64 data.

                                          • Mani

                                            Below is the schema of the response from OTBI webservice. Element rowset contains XML string with required data (not encoded). you might have to use OIC funciton like parseEscapedXML to retrieve individual elements from it in Stage File write.

                                             

                                            <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="urn://oracle.bi.webservices/v7" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                                              <xs:element name="executeXMLQueryResult">
                                                <xs:complexType>
                                                  <xs:sequence>
                                                    <xs:element name="return">
                                                      <xs:complexType>
                                                        <xs:sequence>
                                                          <xs:element type="xs:string" name="rowset"/>
                                                          <xs:element type="xs:string" name="queryID"/>
                                                          <xs:element type="xs:string" name="finished"/>
                                                        </xs:sequence>
                                                      </xs:complexType>
                                                    </xs:element>
                                                  </xs:sequence>
                                                </xs:complexType>
                                              </xs:element>
                                            </xs:schema>

                                            • Stephen Bryant

                                              Thank Mani,

                                              One interesting thing is this:  I had to use the endodeBase64 function in the mapping to get it to even write.  That is not a type; it is encode!  The confusing issue is the output that was written is XML.  Any clue on why that would be?  It's like it does the opposite.  Here is the mapping I used:

                                              <xsl:value-of select = 'oraext:encodeBase64($getOTBIInvoiceHoldsReport/nsmpr1:executeXMLQueryResult/nsmpr1:return/nsmpr1:rowset)'>

                                              Thanks,

                                              Steve

                                            • Stephen Bryant

                                              Mani,

                                              I've managed to retrieve the columns using your schema on the write stage and I generated the other schema based on the xml output and used that in the read stage.  I then mapped the columns to the REST endpoint.  The only remaining issue is the REST Service does not return data, only the columns with blank rows.  

                                              I tried parsing at the write stage without success.

                                              Any ideas?

                                              Sorry to be high maintenance as this is all new to me.

                                              Thanks again,

                                              Steve

                                            • Stephen Bryant

                                              Mani,

                                              Success!!!  I had a mistake in the schema.  However, the data comes accross like this:

                                               

                                              {
                                                  "description": "Duplicate-10002 FS-100002 FS-100002 FS-1124 FS-1125 FS-16 FS-26 FS-26 FS-27 FS-27 FS-7 FS-CRE1125",
                                                  "invoiceDate": "2019-01-05 2018-12-09 2018-12-09 2018-11-11 2018-11-11 2018-07-25 2018-10-01 2018-10-01 2018-10-11 2018-10-11 2018-05-02 2018-11-15",
                                                  "holdName": "Invoice is missing invoice lines and needs completion. Invoice is missing invoice lines and needs completion. Total of invoice distributions does not equal invoice amount. Invoice is missing invoice lines and needs completion. Invoice is missing invoice lines and needs completion. Total of invoice lines does not equal invoice amount. Invoice is missing invoice lines and needs completion. Total of invoice distributions does not equal invoice amount. Invoice is missing invoice lines and needs completion. Total of invoice distributions does not equal invoice amount. Potential Duplicate Invoice for the supplier, invoice date and Invoice amount combination is identified. Verify that this is not a duplicate. Invoice is missing invoice lines and needs completion.",
                                                  "holdDate": "2019-01-09 2018-12-13 2018-12-13 2018-11-16 2018-11-19 2018-07-25 2018-10-21 2018-10-21 2018-10-31 2018-10-31 2018-05-02 2018-11-19",
                                                  "numberOfHolds": "1 1 1 1 1 1 1 1 1 1 1 1",
                                                  "holdSource": "SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM dan.cassidy@yahoo.com SYSTEM",
                                                  "holdAmount": "250 230000 230000 300 300 2000 1500 1500 1500 1500 150 300",
                                                  "supplier": "Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier Federal Supplier"

                                              I think that is okay for VBCS since I have the columns.  What do you think?

                                              Steve

                                               

                                              • Stephen Bryant

                                                Mani,

                                                This is definitely not in the right format.  It is basically a string listing out column-all data then then next column-all data and so on.

                                                Not sure how to fix but I will experiment.  

                                                Steve

                                                • Stephen Bryant

                                                  Mani,

                                                  Okay, I went back and assigned the same schema as the read stage to my rest endpoint and that aligned the data.  The rest response is attached.

                                                  Please take a look and let me know if this makes sense.

                                                  Thanks again,

                                                  Steve

                                                  restResponse.txt (6KB)
                                • Stephen Bryant

                                  Mani,

                                  I've written the file to an ICS directory using endodedBase64ToReference.   I can read it as long as I use the exact schema I wrote it with.  However, I need to get access to the individual fields in the data so I created another XML schema (attached) but cannot seem to upload it as ICS thinks it is invalid.  

                                  Have you had any luck?

                                  Thanks,

                                  Steve

                                  opaqueSchema2.xml (1KB)
    • Stephen Bryant

      Thanks Mani and Kunal.  I will try your suggestions and post back.

      Steve

    • Stephen Bryant
      Thanks Hemanth. Interestingly the only way I could get the file written was to use the encodeBase64 function.  I thought it was a little odd that XML was returned using the function but that is what I should have mentioned.  Anyway, I will work with converter.  

Yes, I kept it simple but once this works I will move on to the real situation.  

I appreciate everyone’s help. 
Thanks,
Steve

     

    community forum