CRM and CX Blogs by SAP
Stay up-to-date on the latest developments and product news about intelligent customer experience and CRM technologies through blog posts from SAP experts.
cancel
Showing results for 
Search instead for 
Did you mean: 
yogananda
Product and Topic Expert
Product and Topic Expert
1,172
In this article, I will walk through all the steps you need to take to be able to create your own QbQuery API endpoint .







When to Create an QbQuery API ?


In general, consider an API if:

  1. Your data set is large, making download via FTP unwieldy or resource-intensive.

  2. Your users will need to access your data in real time, such as for display on another website or as part of an application.

  3. Your data changes or is updated frequently.

  4. Your users only need access to a part of the data at any one time.

  5. Your users will need to perform actions other than retrieve data, such as contributing, updating, or deleting data.


Architecture Flow





Prerequisites




  • APM Tenant (aka ProducerPro)

  • Authentication : User to have System API

  • Write your own SQL statements to get results

  • Test it with Postman




Authentication



  1. System APIs Feature must be enabled.

  2. Specified credentials (user/password) must reference a valid Producer Manager User.

  3. Producer Manager User must have access to the System API's AppArea. (Admin > Users).

  4. Client IP address must be valid based on the system.api.ip.whitelist option configuration. If this option is not specified, any Client IP address is valid.



Now, Lets test it to get CaldsessionId


























URI Path



/REST/auth/login



HTTP Method(s)



POST



Request Parameters



u - User ID of Producer Manager user (required)


p - Password of Producer Manager user (required)



Return MIME Type(s)



application/xml (default)


application/json



Response Codes



200 OK - Login successful.


403 Forbidden - Authentication failed.


500 Internal Server Error - Unexpected runtime error occurred. See Producer Manager Application Log.





cURL Example


curl --request POST 'http://tenantid-pmpro-uat.callidusondemand.com/PM/REST/auth/login' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'u=USERNAME' \
--data-urlencode 'p=PASSWORD'





Note : Upon successful authentication, the response contains the CaldSessionId value. The CaldSessionId must be included on the HTTP request header for subsequent API calls.


Prepare your own SQL query in SQL Workbench

Example is below


Test the results for your query


Finally, SQL formation looks like this...



select p.producerid as ProdNo,sub.Producerno,
pv.producertype from ProducerVer pv
join Producer p on p.producerno = pv.producerno
join (select max(ProducerVerno) as pvno,ProducerNo
from producerver
${rowLevelJoins}
where
${rowLevelAnd}
prosta <> 9
group by ProducerNo)sub on sub.pvno = pv.producerverno
and p.producerid= '${ProdNo}'


Go to Integration > Configuration > QbQuery


 

 
















Create your own QbQuery





QbQuery SQL



Summary of QbQuery



Now you can see your newly created


 
























URI Path(s)



/REST/query/run



HTTP Method(s)



GET



Request Parameters



qbqueryid - The QBQueryID of the query you wish to run. "System API" should be defined as one of the query contexts to allow the QBQueryID accessible in the API.


maxrows - Optional parameter indicating that the query should be limited to the maximum number of rows.


Query-specific Parameters - If the query to execute includes parameters, pass these by name as indicated within the query definition.



Return MIME Type(s)



application/xml (default)Z


application/json



Response Codes



200 OK - QBQuery executed successfully. Does not necessarily mean that rows were found.


400 Bad Request - Invalid QBQueryId or values were not supplied for one or more of the required query parameters.


403 Forbidden - Invalid session or user not authorized to use this API.


500 Internal Server Error - Unexpected runtime error occurred. See the Producer Manager Application Log.



cURL Example


This example runs the QBQuery with the QBQueryId value of "testityourself"
curl --request \
GET 'https://tenantid-pmpro-uat.callidusondemand.com/PM/REST/query/run?qbqueryid=testityourself&ProdNo='000253' \
--header 'CaldSessionId: A-hsbsa73le78rdcji9kosubi' \
--header 'Content-Type: application/xml'

Postman Example



 

Finally, Your QbQuery API's should bring value both to your organization and its intended users.