Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Stephen Bryant
    OTBI Report to VBCS Rest Endpoint **SOLVED**Answered
    Topic posted June 28, 2019 by Stephen BryantBronze Trophy: 5,000+ Points, last edited July 3, 2019, tagged Adapters, API, Cloud, ERP Cloud, Integration, Mapping, Orchestration, REST, SOAP, Web Services, xml 
    204 Views, 18 Comments
    Title:
    OTBI Report to VBCS Rest Endpoint **SOLVED**
    Summary:
    Extracted OTBI Report - map to POST to store in VBCS Business Object
    Content:

    Hello,

    I've successfully extracted the contents of an OTBI Report from our ERP Cloud system and presented that data in a VBCS Application Page.  Thanks to those here who helped me (Mani, Hemanth, and others).  

    At this point, I want to do the same extract from SaaS (no problem) and post that data directly to a VBCS Business Object.  The problem I am having is the OTBI Report is in XML and the REST API for the Business Object wants JSON.  I tried simply mapping the XML output to the Rest API in the mapper but that does not work.

    The data is about 500 lines.  How would I go about this?  I thought the mapping automatically took care of this.

    I am able to successfully Post to the Business Object in POSTMAN in batch mode but cannot replicate this in OIC.

    Any help is greatly appreciated.

    Thanks,

    Steve

    Version:
    19.1.3
    Image:

    Best Comment

    Hemanth Lakkaraju

    Try rest connection till /resources and define the URI in rest adapter as /data and see if it works.

    Comment

     

    • Hemanth Lakkaraju

      The problem I am having is the OTBI Report is in XML and the REST API for the Business Object wants JSON.  I tried simply mapping the XML output to the Rest API in the mapper but that does not work. 

      The above screenshot tells the mapping seems to be between a Stage Read (possibly CSV) and REST request (probably JSON)

      I am able to successfully Post to the Business Object in POSTMAN in batch mode but cannot replicate this in OIC.

      What is the actual problem in this step? Is there an error from VBCS?

      • Stephen Bryant

        Hemanth,

        I've decided to take a step back and simplify my approach.  

        Here is where I am.

        I can send a single record to the Business Object by hard-coding the values in the mapper.

        The problem I run into is when I try to do a Batch Post to the Business Object. The correct content type is included in the headers. So I created a smaller data set which is a JSON file containing two records (batchPayloadTest.txt). I generated a schema by converting JSON to XML and the XML to XSD (batchXMLSchema.txt).  The REST Service Request is based on this JSON File (RestServiceRequestJSON.txt)

        I continue to get this error:

        <Line 1, Column 1>: XML-20108: (Fatal Error) Start of root element expected.Error while translating message to the mentioned format. Please make sure that the file content conforms to the schema.

        I am not sure how to get this to work.

         

        Attaching the files ...

         

        batchPayloadTest.txt (726 Bytes)
        • Hemanth Lakkaraju

          Can you attach the logs with error? looks like id field is mandatory and not mapped and since probably it is an integer (by naming standards) null value cannot be accepted causing the error.

          • Stephen Bryant

            Hemanth,

            Here are the logs along with a screenshot of the latest mapper.  I modified the OTBI Report to pull only two records to keep the dataset small for now.  I also generated the id to included in the payload.

            I am getting an "Invalid Operation for the Specified Resources" but this works in POSTMAN so can you check the mapper to make sure it looks okay.

            Thanks for your help.

            Steve

            oicphx1I_domain-diagnostic-logs-0702161815.zip (1011KB)
            • Stephen Bryant

              Here it the latest iar.

              LOAD_GLBALANCES_TWORECORDS_01.00.0000.iar (70KB)
            • Hemanth Lakkaraju

              Can you attach the postman screenshot to compare the payload? I'm seeing - "Invalid operation BATCH for the specified resource." error in the logs. This generally occurs for a REST call when path value is incorrect in case of a BATCH operation.

              • Stephen Bryant

                Thanks Hemanth.

                Attached is POSTMAN Payload and Response for Batch.

                Steve

                restPayloadPOSTMAN.docx (128KB)
                • Hemanth Lakkaraju

                  From the screenshot the endpoint being called is 

                  https://XXXXX.ocp.oraclecloud.com:443/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data

                  But the rest configuration in the integration seems to be calling

                  https://XXXXX.ocp.oraclecloud.com:443/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances

                  Entry from the activity stream:

                  [2019-07-03T10:50:56.105+00:00] [oicphx1I_server_2] [NOTIFICATION] [] [oracle.ics.trace.soa.bpel] [tid: 152] [userId: stephen.bryant@creoal.com] [ecid: 9c59eff9-2c76-4506-b5f8-c82d02a4d12f-00223da0,1:20948:99] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000MirTJIg5If0LzyH7iW1T4F9O0001gD]  [ICS Activity Stream Logging]: [Code:LOAD_GLBALANCES_TWORECORDS][Version:01.00.0000][Instance:22000135][Operation:update_glBalancesBO][Endpoint:https://XXXXX.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances][Endpoint Invocation Start(UTC):2019-07-03T10:50:55.258Z][Endpoint Invocation End(UTC):2019-07-03T10:50:56.103Z][Endpoint Invocation Time(ms):845][Total Execution Time(ms):960][Http Status Code:400]: Invocation of target service failed in 845 ms 

                  Try correcting the endpoint and see if it works! 

                  • Stephen Bryant

                    Hemanth,

                    I updated the REST Connection as follows:

                    https://integration021783-creoalconsulting.integration.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances

                    I am not getting this error. This error happens if I do not define the URI in the REST Stage.

                    However, I get a 404 error when I do define the URI in the REST Stage.  I will continue to play and I am attaching the logs.

                    : Fault Details : <ns2:APIInvocationError xmlns:ns2="http://xmlns.oracle.com/cloud/generic/rest/fault/REST/update_glBalancesBO"><ns2:type/><ns2:title/><ns2:detail/><ns2:errorCode/><ns2:errorDetails><ns2:type>http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.5.1</ns2:type><ns2:title>Internal Server Error</ns2:title><ns2:errorCode>500</ns2:errorCode><ns2:errorPath>&lt;![CDATA[POST https://integration021783-creoalconsulting.integration.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances/ returned a response status of 500 Internal Server Error]]&gt;</ns2:errorPath><ns2:instance>&lt;![CDATA[.The 500 Internal Server Error is a very general HTTP status code that means something has gone wrong on the server side, but the target service could not be more specific on what the exact problem is. You can trace the cURL representation of the request sent to the target service from the Oracle Integration Cloud server logs. Try invoking the target service using cURL. If the problem persists, contact the target service admin.]]&gt;</ns2:instance></ns2:errorDetails></ns2:APIInvocationError> :Application Error
                     

                    Thanks again,

                    Steve

                     

                     

                     

                    oicphx1I_domain-diagnostic-logs-0703121529.zip (1.1MB)
                    • Hemanth Lakkaraju

                      Try rest connection till /resources and define the URI in rest adapter as /data and see if it works.

                      • Stephen Bryant

                        Hemanth,

                        Still getting the 500 error.  Right now, this is just one record I am trying to bring in from OTBI.  If I provide the full URL whether created based on the REST Connection and REST Adapter or just by the REST Connection, I get a 500 error.  

                        Not sure what is going on.

                        Steve

                      • Stephen Bryant

                        If I hard code values in the mapper, it works.

                         

      • Stephen Bryant

        Here is the next file

        batchXMLSchema.txt (1KB)
      • Stephen Bryant

        Here is the last file

        RestServiceRequestJSON.txt (666 Bytes)
      • Stephen Bryant

        Hemanth,

        I have great news --- WE DID IT!  And when I say "We", I mean you and I.  Your suggestion to set the REST Connection to ".../Resources", then the REST Adapter URI to "/data" and then include the remaining in the Payload as "path":"/glBalances" worked!

        Everything is fine and I thank you a thousand times and then some for all your help.

        Thanks,

        Steve

      • Stephen Bryant

        Hemanth,

        One more question.

        I am able to bulk load 36 rows.  After that, the integration fails.  There seems to be a limit on the Business Object REST API.  I posted a question on the Visual Builder Forum for that.  In the meantime, I know we can chunk read files but can we post to the rest service by chunking? or do I have to do a loop?  I've done this sort of thing before loading FBDI files.  Also, do you know if we can paginate a Business Object REST API?

        Thanks,

        Steve

        • Hemanth Lakkaraju

          You may have to use loop for posting chunked messages and yes, you should be able to paginate REST API. Just add the related query parameters to rest adapter configuration.

      • Stephen Bryant

        Hemanth,

        It turned out the issue was in the data.  There were null values I did not account for that were in the complete data set.  That is a great lesson learned for me.

        Once again, thank you for all your help and patience!

        Steve

       

      community forum