Skip to Content
Technical Articles

APM – Build your own API through QbQuery

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.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.