Overview

ClearWork passes JSON data to ClearProcess via REST integration. ClearProcess then uses connectors to transform the JSON data to the format and protocol expected by downstream systems. Use the menu to find the different connectors available in ClearProcess.

The Clear Background connector is used to make long-running, asynchronous calls into other Clear composite APIs (aka bots). There are five operations available: CreateJobAddTasksGetJobGetTasksGetTaskResults.

The general usage pattern is: CreateJob -> AddTasks -> GetTaskResults

GetJob and GetTasks can be used to gather information about running jobs. The primary difference between GetTasks and GetTaskResults is that the former returns a truncated set of information about the tasks in question. Results from Clear APIs can be quite large, which makes responses from GetTaskResults rather long at times.

CreateJob

Creates a job and returns a job number. This is a container for all running tasks, and creating it is the first step in defining a job. Jobs may be tagged with any number of strings to aid in finding related jobs and runs. ClearBackground is intentionally “dumb,” to allow for flexibility in the way that it is used.

The CreateJob task is also responsible for gathering and storing any credentials that will be used during the process. These credentials are stored in an encrypted manner with the help of AWS Secrets Manager.

Parameters

tags (optional)

A list of strings that can later be used to retrieve related jobs.

credentials (optional)

Additional credentials may be supplied to the CreateJob function when the user running the job does not have the correct credentials to execute the underlying tasks.

  • credential_type: Two types of credentials are accepted: password and cert
  • system_username: The username in the underlying system
  • system_id: An integer identifying the ClearProcess System that these credentials are intended for
  • secret: The password or certificate

Returns

Information about the Job that was just created. The UUID is used for referencing this job in other calls.

Example:

{
  "uuid": "2598ee82-dd63-412c-b57e-57ca1cc0d4c1",
  "complete": false,
  "completed_task_count": 0,
  "tags": [
    "Test Job"
  ],
  "created": "2019-08-07T08:49:14.342456+00:00",
  "task_count": 0,
  "subdomain": "sbx.clearui.com",
  "clear_username": "dustin"
}

AddTasks

Parameters

job_id

The UUID for the job to add tasks to.

tasks

A list of tasks, each with the following parameters:

  • api_name: The API name to execute (e.g. CreateSalesOrder)
  • parameters: A dictionary of parameters expected by the API. These are passed on to ClearProcess as-is.

example:

"tasks": [
            {
                'api_name': 'GetCustomer', 
                'parameters': {'id': 1}
            },
            {
                'api_name': 'GetCustomer', 
                'parameters': {'id': 1}
            }
        ]

Returns

Success or failure message

GetJob

Get information about a job or jobs. This information includes when the job was created, how many total tasks are in the job, how many are complete, etc. This function always returns a list.

Parameters

tags

Find jobs by tags. Tags are ANDed together. A job with all the given tags will be returned.

created_before

Find jobs created before a date

created_after

Find jobs created after a date

Returns

Information about the selected jobs.

Example:

[
    {
        "completed_task_count": 0,
        "clear_username": "dustin",
        "subdomain": "sbx.clearui.com",
        "complete": false,
        "created": "2019-08-07T08:49:14.342456+00:00",
        "uuid": "2598ee82-dd63-412c-b57e-57ca1cc0d4c1",
        "task_count": 0
    }
]

GetTasks

Get abbreviated information about a job’s tasks. Note the results are paged, and only the first 100 results will be returned by default; set offset and limit to get later pages.

Parameters

job_id

The UUID of the job

offset

Default: 0

The first task to retrieve.

limit

Default: 100

The total number of tasks to return

Returns

Job information which includes a list of tasks with abbreviated information. (does not include API call results)

Example:

{
    "complete": true,
    "uuid": "9ff02735-46ce-4f84-9788-e38420edd7d8",
    "clear_username": "dustin",
    "subdomain": "sbx.clearui.com",
    "completed_task_count": 2,
    "task_count": 2,
    "created": "2019-07-25T14:59:53.548991+00:00",
    "tasks": [
        {
            "error": true,
            "api_name": "GetUserAuthorizations",
            "completed_at": "2019-07-25T15:02:46.463971+00:00",
            "uuid": "298cb0a7-2f50-4640-8c07-535f5ffb0186",
            "created": "2019-07-25T15:00:14.982719+00:00",
            "sequence_num": 0
        },
        {
            "error": true,
            "api_name": "GetUserAuthorizations",
            "completed_at": "2019-07-25T15:00:21.405760+00:00",
            "uuid": "06c11f76-4e95-4628-b02a-df0e99524a68",
            "created": "2019-07-25T15:00:14.982804+00:00",
            "sequence_num": 1
        }
    ]
 }

GetTaskResults

Get full information about a job’s tasks, including the results of the Task API calls. Note the results are paged, and only the first 100 results will be returned by default; set offset and limit to get later pages.

Parameters

job_id

The UUID of the job

offset

Default: 0

The first task to retrieve.

limit

Default: 100

The total number of tasks to return

Returns

Job information which includes a list of tasks with abbreviated information. (does not include API call results)

Example:

{
  "complete": true,
  "uuid": "9ff02735-46ce-4f84-9788-e38420edd7d8",
  "clear_username": "dustin",
  "subdomain": "sbx.clearui.com",
  "completed_task_count": 16,
  "task_count": 16,
  "created": "2019-07-25T14:59:53.548991+00:00",
  "tasks": [
    {
      "error_detail": "",
      "error": false,
      "result": {
        "errors": [],
        "new_rpc": true,
        "payload": {
          "GetUserAuthorizations": {
            "Auth_Count": 2990,
            "Auth_List2": true,
            "Authorizations": {
              "WLM": {
                "LAYGR": "*"
              },
              "D_IR": {
                "ACTVT": "*",
                "SPART": "*",
                "VKORG": "*",
                "VTWEG": "*",
                "PARTNER": "*"
              },
              [...]
              "W_WXP_HIER": {
                "ACTVT": "*",
                "PLHVS": "*",
                "PLSZN": "*"
              },
              "W_WXP_PLAN": {
                "ACTVT": "*",
                "PLSCH": "*",
                "PLSZN": "*"
              }
            }
          }
        },
        "_timings": {
          "overall": "0.638299942017",
          "Auth_List2": "0.599851846695"
        }
      },
      "api_name": "GetUserAuthorizations",
      "completed_at": "2019-07-25T15:02:46.463971+00:00",
      "uuid": "298cb0a7-2f50-4640-8c07-535f5ffb0186",
      "parameters": {},
      "created": "2019-07-25T15:00:14.982719+00:00",
      "sequence_num": 0
    },
    {
      "error_detail": "",
      "error": true,
      "result": {
        "errors": [],
        "new_rpc": true,
        "payload": {
          "GetUserAuthorizations": {
            "Auth_Count": 2990,
            "Auth_List2": true,
            "Authorizations": {
              "WLM": {
                "LAYGR": "*"
              },
              "D_IR": {
                "ACTVT": "*",
                "SPART": "*",
                "VKORG": "*",
                "VTWEG": "*",
                "PARTNER": "*"
              },
              [...]
              "W_WXP_HIER": {
                "ACTVT": "*",
                "PLHVS": "*",
                "PLSZN": "*"
              },
              "W_WXP_PLAN": {
                "ACTVT": "*",
                "PLSCH": "*",
                "PLSZN": "*"
              }
            }
          }
        },
        "_timings": {
          "overall": "0.840951919556",
          "Auth_List2": "0.718284130096"
        }
      },
      "api_name": "GetUserAuthorizations",
      "completed_at": "2019-07-25T15:00:21.405760+00:00",
      "uuid": "06c11f76-4e95-4628-b02a-df0e99524a68",
      "parameters": {},
      "created": "2019-07-25T15:00:14.982804+00:00",
      "sequence_num": 1
    }
  ]
}

The ClearProcess Oracle connector can be used to connect to any Oracle database, but it has been designed specifically to work with the Oracle E-Business Suite. (Oracle EBS) This guide demonstrates the various configuration options available in the ClearProcess Oracle Connector. There are three Operations available:

  • Select SELECT
  • Call Stored Procedure CALL_PROC
  • Commit COMMIT

Each of these is available through Clear Operations named SELECTCALL_PROC, and COMMIT. These specific Operation names are important, as they are used by the connector to select the operation to use.

Select SELECT

Performs basic SELECT queries against an Oracle database. There are several limitations to this functionality:

  • Only one table can be selected from per call
  • No joins
  • Only WHERE and ORDER BY clauses supported

These limitations are by design. Queries are kept simple in order to avoid performance degradation on your Oracle database. More complex queries should be designed by your DBAs and implemented as stored procedures, which may then be called by the Oracle Connector.

Parameters

table

The Oracle database table to select from

columns (optional)

A list of columns to select from that table. The columns parameter must be a list. If the columns parameter is omitted, SELECT * will be used.

where (optional)

A list of dictionaries specifying three sub-parameters:

  • column: The column name to operate on
  • operator: Which comparison operator to use. Supported: =, <, >, <=, >=, <>, IN, LIKE
  • value: The value to compare against. For the IN operator, this value must be a list

Note that not all operators are supported. Comparisons are ANDed together. OR is not supported.

order_by (optional)

Order results by the specified column.

order_by_desc (optional)

Use descending order. Defaults to False, i.e. ascending.

Example

{
    "table": "ALL_TAB_COLS",
    "columns": ["COLUMN_NAME", "DATA_TYPE", "COLUMN_ID"],
    "where": [
      {
        "column": "TABLE_NAME", 
        "operator": "=", "value": 
        "{{ user_parameters.TABLE }}"
       }
    ],
    "order_by": "COLUMN_ID",
    "order_by_desc": True
}

Call Stored Procedure CALL_PROC

Performs calls to pre-defined stored procedures on the Oracle database.

Parameters

package

Stored procedures called by the ClearProcess Oracle Connector must be contained in a package. This parameter specifies the package name.

proc

The procedure name in Oracle.

params

A list of parameters to the stored procedure. Parameters that are PL/SQL Collections or Records may contain lists of sub-parameters. Each parameter is represented by a dictionary with the following keys:

  • name: The name of the parameter defined in Oracle
  • db_type: The Oracle type of the parameter (NUMBER, VARCHAR2, etc.) Records and collections should have the values PL/SQL RECORD and PL/SQL COLLECTION respectively.
  • value: The parameter value. For scalar parameters, this is the string, digit, boolean value, etc. For Records and Collections, these are lists of sub-parameters.
  • collection_of: Used only with Collection parameters. This parameter specifies what the collection holds. This may be a scalar type like VARCHAR2 or NUMBER, but frequently this is a Record type. In the case of a Record, specify the fully qualified type name, including package. e.g. PA_PROJECT_PUB.PROJECT_IN_REC_TYPE

Example

{
   "package": "PA_PROJECT_PUB",
   "proc": "CREATE_PROJECT",
   "params": [   
        {"name": "P_API_VERSION_NUMBER", "db_type": "NUMBER", "value": 1.0},
        {"name": "P_COMMIT", "db_type": "VARCHAR2(255)", "value": "F"},
        {"name": "P_INIT_MSG_LIST", "db_type": "VARCHAR2(255)", "value": "T"},
        {"name": "P_MSG_COUNT", "db_type": "NUMBER", "out": True},
        {"name": "P_MSG_DATA", "db_type": "VARCHAR2(255)", "out": True},
        {"name": "P_RETURN_STATUS", "db_type": "VARCHAR2(255)", "out": True},
        {"name": "P_WORKFLOW_STARTED", "db_type": "VARCHAR2(255)", "out": True},
        {
          "name": "P_PM_PRODUCT_CODE", 
          "db_type": "VARCHAR2(255)", 
          "value": "{{ user_parameters.HEADER.PROJECT_TYPE }}"
        },
        {
          "name": "P_OP_VALIDATE_FLAG", 
          "db_type": "VARCHAR2(255)", 
          "value": "F"},
        {
          "name": "P_PROJECT_IN", 
          "db_type": "PL/SQL RECORD", 
          "type_name": "PA_PROJECT_PUB.PROJECT_IN_REC_TYPE", 
          "value": [
            {
              "name": "PROJECT_NAME", 
              "db_type": "VARCHAR2(255)", 
              "value": "Project Name"
            },
            {
              "name": "LONG_NAME", 
              "db_type": "VARCHAR2(255)", 
              "value": "Project Long Name"
            }
        ]},
        {
          "name": "P_PROJECT_OUT", 
          "db_type": "PL/SQL RECORD", 
          "type_name": "PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE", 
          "out": True
        },
        {
          "name": "P_TASKS_IN", 
          "db_type": "PL/SQL COLLECTION", 
          "type_name": "PA_PROJECT_PUB.TASK_IN_TBL_TYPE", 
          "collection_of": "PA_PROJECT_PUB.TASK_IN_REC_TYPE", "value": [
            {
              "db_type": "PL/SQL RECORD", 
              "type_name": "PA_PROJECT_PUB.TASK_IN_REC_TYPE", 
              "value": 
                [
                  {
                    "name": "PM_TASK_REFERENCE", 
                    "db_type": "VARCHAR2(255)", 
                    "value": "Reference"
                  }
                ]
             }
        ]},
        {
          "name": "P_TASKS_OUT", 
          "db_type": "PL/SQL COLLECTION", 
          "type_name": "PA_PROJECT_PUB.TASK_OUT_TBL_TYPE", 
          "out": True
        }
    ]
}

Call Function CALL_FUNC

Performs calls to pre-defined functions on the Oracle database. These calls are similar to stored procedures, but functions are treated slightly differently by Oracle in that they return a result, in addition to any OUT parameters.

Parameters

package

Functions called by the Clear Oracle Adapter must be contained in a package. This parameter specifies the package name.

func

The function name in Oracle.

params

A list of parameters to the function. Parameters that are PL/SQL Collections or Records may contain lists of sub-parameters. Each parameter is represented by a dictionary with the following keys:

  • name: The name of the parameter defined in Oracle
  • db_type: The Oracle type of the parameter (NUMBER, VARCHAR2, etc.) Records and collections should have the values PL/SQL RECORD and PL/SQL COLLECTION respectively.
  • value: The parameter value. For scalar parameters, this is the string, digit, boolean value, etc. For Records and Collections, these are lists of sub-parameters.
  • collection_of: Used only with Collection parameters. This parameter specifies what the collection holds. This may be a scalar type like VARCHAR2 or NUMBER, but frequently this is a Record type. In the case of a Record, specify the fully-qualified type name, including package. e.g. PA_PROJECT_PUB.PROJECT_IN_REC_TYPE

Commit COMMIT

Some stored procedures provided by Oracle EBS allow you to pass a commit parameter, but this is not recommended. In general, APIs defined in Clear should be transactional in nature. i.e. if one step of an API fails, no data should change as a result of the call. For Oracle, this cannot be enforced by the ClearProcess. We must rely on users to supply F flags to commit parameters. Therefore, at the end of your Clear API involving Oracle Stored Procedures, you should call the COMMIT Operation.

There are no parameters for this operation. Simply call the COMMIT Operation and your transaction will be committed.

Parameters

None

DSN Setup

Each System in clear has a DSN. This is not the same as an Oracle DSN. Instead, the Oracle DSN is included as a parameter to the Clear DSN. The parameters for the Clear DSN are as follows:

Parameters

user

The database user that Clear will use to connect to the Oracle EBS database. Note that tasks performed within Oracle EBS are NOT performed by this user, but by the individual user who logs in to Oracle EBS via Clear.

password

The password for the Oracle database user.

oracle_dsn

This is the Oracle DSN that specifies connection parameters for the Oracle database, in the standard format.

oracle_use_saml_auth

If your Clear implementation uses SSO, this should be set to true. With this property enabled, authentication is done by the identity provider (such as Active Directory or Okta) that interfaces with Clear. The Oracle connector relies on that authentication for authentication within Oracle. Authorization is still handled by the Oracle backend. This property tells the Oracle connector to use the Oracle EBS username provided by a SAML assertion, rather than relying on the username used for non-SSO login.

Example

{
    "user": "CLEAR_ADMIN", 
    "password": "Lqb6uIlhZ4", 
    "oracle_dsn": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.12.41)(PORT=10810))(CONNECT_DATA=(SID=PROD)))", 
    "oracle_use_saml_auth": true
}

The SAP connector is based on SAP’s NetWeaver RFC SDK. It allows you to natively connect to an SAP system using an RFC connection and call remote-enabled functions, also known as BAPIs. There are over 3,500 BAPIs that have been released by SAP for public use, all of which are pre-delivered in ClearProcess as an API. You can view them in the ClearProcess builder. In addition to BAPIs, there are the following integration options:

  • iDoc – any SAP iDoc can be triggered from ClearProcess using the API IDOC_INBOUND_SYNCHRONOUS. See CreatePricingCondition in ClearProcess for an example.
  • BDC – If you have exhausted all of your options and cannot integrate with SAP via a BAPI or iDoc, there is an option to record a batch input session (transaction SHDB) and invoke the session from ClearProcess. This should always be a last resort and will only be supported after receiving written approval from Clear. See PayCustomerItems in ClearProcess for an example.

Setting Up the SAP Connection

Setting up a connection to your SAP system can be a little tricky because the documentation is scarce. Below are the most common types of connections. The entire list of SAP connection parameters can be found here:

Application Server

Use this type of connection if there is only one SAP application server and your users log in with a user name and password.

Load Balancer

Use this type of connection if you have a load balancer and your users log in with a user name and password.

Load Balancer + SSO

Use this type of connection if you have a load balancer and your users log in through a Single Sign On service.