Construct a JSON Request Body for runQuery() API
The Oracle Primavera Cloud Data Service runQuery() API is required to fetch the in-progress activities from a workspace in Oracle Primavera Cloud.
A POST request runs queries against Primavera Cloud Data Service. The query to fetch the in-progress activities from a workspace is as below:
Select a.ACTIVITY_NAME, a.ACTIVITY_CODE, a.PROJ_ID, a.STATUS from SM_ACTIVITY a, CO_PROJECT b, WS_WORKSPACE c where a.status='IN_PROGRESS' and a.PROJ_ID=b.PROJ_ID and b.WORKSPACE_ID = c.WORKSPACE_ID and c.WORKSPACE_CODE = “OICWS”;
Convert the query to a JSON request to send it to the runQuery() API. Your JSON request should contain the following:
- Tables: The query should consist of one or more objects specifying the application database tables. For example, in the above query,
Select a.* indicates SM_ACTIVITY
indicates a table.CO_PROJECT
andWS_WORKSPACE
tables are also part of the query but the requested columns are only from theSM_ACTIVITY
table. The other tables are used for joining the condition. Therefore, the table name is set as:"tables": [{ "tableName": "SM_ACTIVITY"}]
- Columns: The query should contain a list of database table columns. The API returns only the specified columns. The other table columns are not included in the response. The above query includes the following columns:
ACTIVITY_NAME, ACTIVITY_CODE, PROJ_ID,
andSTATUS
. The columns are listed as:ACTIVITY_NAME, ACTIVITY_CODE, PROJ_ID and STATUS"columns": [ "ACTIVITY_NAME", "ACTIVITY_CODE", "PROJ_ID", "STATUS" ]
- Conditions: The query should contain a set of conditions that the column data must meet in order to be included in the response. The above query has the following conditions:
status='IN_PROGRESS'
andWORKSPACE_CODE = “OICWS”.
To fetch the in-progress activities from a workspace, the status code should be in-progress and the workspace code should be the user-specified code of the workspace from where the activities are fetched.
Your JSON query should look like this:
{ "name": "Project Activity", "pageSize": "10", "nextTableName": "SM_ACTIVITY", "tables": [ { "tableName": "SM_ACTIVITY", "columns": [ "ACTIVITY_NAME", "ACTIVITY_CODE", "PROJ_ID", "STATUS" ], "condition": { "operator": "AND", "conditions": [ { "columnName": "STATUS", "operator": "EQUALS", "value1": "IN_PROGRESS" }, { "columnName": "PROJ_ID", "operator": "IN", "val-ue1": "(SELECT PROJ_ID FROM CO_PROJECT PROJ, WS_WORKSPACE WS WHERE PROJ.WORKSPACE_ID=WS.WORKSPACE_ID AND WS.NAME='OICWS')" } ] } } ] }
Last Published Tuesday, November 28, 2023