3 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 the Oracle Primavera Cloud Data Service. The following query is used to fetch the in-progress activities from a workspace:

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 the SM_ACTIVITY table. The CO_PROJECT and WS_WORKSPACE tables are also part of the query, but the requested columns are only from the SM_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, and STATUS. 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' and WORKSPACE_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')"
                    } ]            }
        } ]
}