Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Farhad Pagdiwala
    Oracle Service SOAP APIAnswered
    Topic posted March 16, 2017 by Farhad Pagdiwala 
    676 Views, 17 Comments
    Title:
    Oracle Service SOAP API
    Content:

    Hello:

        We're integrating our Oracle EBS with Oracle Service Cloud using Cast Iron integration tool.

        The Service Cloud SOAP has several APIs such as Create, Update, QueryObjects etc.

        I would like to know how to pass the number values to these APIs.

        For e.g.  I'm trying to update a custom object Orgs based on the account number which is uploaded along with customer name to this object.

                     I'm using QueryObjects() to first query the data. When I look at the body of this api it has the following elements in the body:

         1. Query    - I believe, we can pass ROQL query to this element with binding parameter. How can we do it?

         2. id

         3. LookupName

         4. CreatedTime

         5. UpdatedTIme.

         Questions:

           1. How can I pass the bind variable to the Query element?     

           2. What values have to be passed to id and LookupName?

              Can I pass account number to the id? If not then, pls. guide as to how can this be achieved?

        Note: I've uploaded the screenshot in case you would like to see how it looks in Cast Iron.

    Version:
    Service Cloud

    Best Comment

    Allan Schrum

    A vaguery of the permission model between QueryCSV and QueryObjects. It should not allow that query but does at the moment. I've an incident filed to fix that issue. Soon you will not be able to use QueryCSV unless you have permissions as well. The same should be true for AnalyticsReport where the custom object is exposed to analytics.

    Regards,

    -Allan

    Comment

    • Pramod Vasudeva Murthy

      You check this sample code given in documentation. I think you need to tweak the code a bit according to your needs. Try this and let us know.

      - VIP

    • Farhad Pagdiwala

      I've already looked at the code and it doesn't help. If you can please provide the specifics pertaining to each element in the API body that would be great!

      Thank you,

      Farhad P.

    • Pramod Vasudeva Murthy

      I am not sure if you have visited the link I shared, it is so descriptive and there is an example also. PFA a sample of SOAP request and response and also add-in code. Try this and let us know.

      - VIP

    • Farhad Pagdiwala

      Yes, I did visit the link, which you had provided in your earlier post. I've gone through the link. 

      Also, I checked your Query_CSV.zip and the C# code file has a simple query with no where clause.

      Also, your XML Request file has static value been passed to the where clause.

      I need to know, which values are mapped to the elements of QueryObjects() SOAP API.

      If you check the screenshot, which I've uploaded to my original post, you will know what I'm requesting for.

      Please advise.

    • Danette Beal

      Hi Farhad,

      Thank you for your question in the Community! It's great to see new people joining and participating. In the event you were not aware, this is a peer to peer Community and although we do have an Oracle presence who does comment frequently, this is not a support channel. Pramod is one of our great community leaders and I know he would be happy to help you but please know he is volunteering his time to contribute. What parts of the documentation are you confused about or are getting tripped up on. Maybe explaining what pieces are helpful and which ones you are still hung up on will help Pramod or others in guiding you to the right answers. I absolutely know it can be hard to share some of this because you just need to get to the right answer but i'm hoping maybe this will help you get where you need to be.

      Thanks again!

      Danette, Community Manager

    • Pramod Vasudeva Murthy

      Thanks Danette for your kind words..

      Farhad,

      I understood your requirement: Explaining each of them:

      • QueryObjects: A Connect SOAP Web Service method use to query records matching a condition. The return type is of type Object, hence we need to pass RNObject - which is a generic object accepting Standard Objects like Incident, Contact to Custom Objects.
      • Query: A ROQL query which has only object in select clause and has conditions in where clause. Point to keep in mind here is QueryObject expects an Object there shouldn't be any columns specified.
      • RNObject templates: This is an array of objects containing Standard objects or Custom Objects. 
      • pagesize: No of results that needs to be returned by query. Max value is 20,000.

      These parameters are enough to make QueryObject request. But before running this query it is best to execute them in Connect Object explorer in Site Admin page.

      And for passing each of them dynamically you need to use a concat method of strings and also better is you make a suitable programming language like PHP, Java or .NET. By mere XML you cannot pass values anyhow.

      PFA example code.

    • Farhad Pagdiwala

      Hi Danette:

                Thank you for commenting on this post. Yes, I agree and totally understand that this a peer to peer community platform with strong Oracle presence. Hence, I posted                my question on this platform to receive valuable insights and answers from the experts like Pramod.

      Pramod:

              Thank you for uploading QueryObjects code. I eventually found out that QueryCSV call would do for my requirement since, I'm plainly performing a select on a custom              object called Orgs.

                However, now I'm stuck a different point in integration wherein I'm able to pass in the query using QueryCSV call using a static ID value. However, when I'm passing a value using a variable say acct_num to the query SELECT CO.Orgs.Name FROM CO.Orgs WHERE CO.Orgs.AcctNum = $acct_num ($ token is needed for IBM Cast Iron notation for passing variable to the query). The integration is complaining with the following message:

      SOAP fault from Item: INVALID_REQUESTChained ID names must be specified with the @ symbol.

      I tried with the following token combinations for the variable to be passed to the query.

      $@acct_num

      $'@acct_num'

      @$acct_num.

      However, none of the above combinations work. Now, I'm trying to understand how to pass a dynamic variable to ROQL query where clause.

      Please let me know if you've encountered this issue and how to solve it.

       

    • Pramod Vasudeva Murthy

      Understood,

      What kind of a field is CO.Orgs.AcctNum? Is it a Menu, ID, Integer or a text field?

      There are different ways to send dynamic parameters to ROQL.

      - VIP

    • Farhad Pagdiwala

      It is a text field.

      Thanks,

      Farhad.

    • Pramod Vasudeva Murthy

      If its a text field, you could try this:

      "SELECT CO.Orgs.Name FROM CO.Orgs WHERE CO.Orgs.AcctNum = '".$acct_num."'"

      Embed it in a ' ' and see it makes a difference..

      - VIP

    • Farhad Pagdiwala

      On the other hand, I'm running my query using QueryObjects operation call. Upon, executing the operation in Integration tool, I'm receiving the below response from the operation.

       <?xml version="1.0" encoding="UTF-8"?>
      - <n0:QueryObjectsResponse xmlns:n0="urn:messages.ws.rightnow.com/v1_2" xmlns:n1="urn:faults.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         - <n0:Result>
              <n0:RNObjectsResult/>
            - <n0:Paging>
                 <n0:ReturnedCount>0</n0:ReturnedCount>
                 <n0:StartIndex>0</n0:StartIndex>
                 <n0:QueryHandle>MoreAvailable</n0:QueryHandle>
              </n0:Paging>
            - <n0:Fault>
                 <n1:exceptionCode>INVALID_REQUEST</n1:exceptionCode>
                 <n1:exceptionMessage>Unexpected error processing row 0: Not Allowed: Cannot perform requested function or action.
      While processing Custom Object Get() of CO|Orgs</n1:exceptionMessage>
              </n0:Fault>

      Any idea as to why I receiving the above exception message?

      Below is the input passed to the operation.

       <?xml version="1.0" encoding="UTF-8"?>
      - <QueryObjects xmlns="urn:messages.ws.rightnow.com/v1_2">
           <Query>SELECT CO.Orgs FROM CO.Orgs</Query>
         - <ObjectTemplates xmlns:p0="urn:generic.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="p0:GenericObject">
            - <ObjectType xmlns="urn:generic.ws.rightnow.com/v1_2">
                 <Namespace>CO</Namespace>
                 <TypeName>Orgs</TypeName>
              </ObjectType>
           </ObjectTemplates>
           <PageSize>10</PageSize>
        </QueryObjects>

      The reason behind using QueryObjects is because, UpdateObjects operation uses the same RN Objects structure. 

    • Pramod Vasudeva Murthy

      If you are using QueryObjects you cannot use individual column names it has to be a an OBJECT. 

      For eg:

      SELECT CO.Orgs.Name FROM CO.Orgs WHERE CO.Orgs.AcctNum = '".$acct_num."'" => WRONG

      SELECT CO.Orgs FROM CO.Orgs WHERE CO.Orgs.AcctNum = '".$acct_num."'" => RIGHT

      - VIP

    • Farhad Pagdiwala

      Yes, you're correct, and I'm using SELECT CO.Orgs FROM CO.Orgs as the query input.

      Below is the xml file passed to the SOAP operation call. As you can see in the query tag, that is the query been passed.

      <?xml version="1.0" encoding="UTF-8"?>
      - <QueryObjects xmlns="urn:messages.ws.rightnow.com/v1_2">
           <Query>SELECT CO.Orgs FROM CO.Orgs</Query>
         - <ObjectTemplates xmlns:p0="urn:generic.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="p0:GenericObject">
            - <ObjectType xmlns="urn:generic.ws.rightnow.com/v1_2">
                 <Namespace>CO</Namespace>
                 <TypeName>Orgs</TypeName>
              </ObjectType>
           </ObjectTemplates>
           <PageSize>10</PageSize>
        </QueryObjects>

      However, I'm receiving the below message from Service Cloud engine.

      <?xml version="1.0" encoding="UTF-8"?>
      - <n0:QueryObjectsResponse xmlns:n0="urn:messages.ws.rightnow.com/v1_2" xmlns:n1="urn:faults.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         - <n0:Result>
              <n0:RNObjectsResult/>
            - <n0:Paging>
                 <n0:ReturnedCount>0</n0:ReturnedCount>
                 <n0:StartIndex>0</n0:StartIndex>
                 <n0:QueryHandle>MoreAvailable</n0:QueryHandle>
              </n0:Paging>
            - <n0:Fault>
                 <n1:exceptionCode>INVALID_REQUEST</n1:exceptionCode>
                 <n1:exceptionMessage>Unexpected error processing row 0: Not Allowed: Cannot perform requested function or action. While processing Custom Object Get() of CO|Orgs</n1:exceptionMessage>
              </n0:Fault>

             

              Please advise.

       

    • Allan Schrum

      Perhaps because you do not have permissions to do a "Get" operation on the custom object CO.Orgs as the error message suggests? If you check your profile I think you will see that you do not have permission to read that object.

      Regards,

      -Allan

    • Farhad Pagdiwala

      Thank you Allan for the quick response. I checked the permissions set to the profile for custom objects and the CRUD permissions are not set.

      However, I'm able to fetch the data using QueryCSV operation call vs. QueryObjects call. Are there special permissions needed on the custom objects when used with QueryObjects vs. QueryCSV call?

      Thank you for guiding me on this issue.

      Best Regards,

      Farhad.