Oracle Sales Cloud Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Oracle Sales Version Support

Establish a Connection

Oracle Sales uses Basic authentication over SSL; after setting the following connection properties, you are ready to connect:

• Username: Set this to the user name that you use to log into your Oracle Cloud service.
• Password: Set this to your password.
• HostURL: Set this to the Web address (URL) of your Oracle Cloud service.

Custom Fields and Objects

Schemas for standard Oracle Sales tables are internally stored in Jitterbit Connector for Oracle Sales and used to minimize the amount of sent requests and payload received. By default, Jitterbit Connector for Oracle Sales will use these tables and not expose any of your instance's custom objects. While custom fields are exposed since IncludeCustomFields defaults to true.

The Jitterbit Connector for Oracle Sales supports dynamically retrieving schemas for objects and custom fields from within your Oracle Sales specific instance by following the steps described below.

Include Custom Fields

In case your schema needs to include custom fields (columns) added to Oracle Sales standard objects (tables), you can use the IncludeCustomFields connection property. Setting this property to true will result in one or more extra calls being issued and will affect Jitterbit Connector for Oracle Sales behavior in the following ways:

• Custom fields will be retrieved and listed along with the rest of the columns in your schema.
• All internally stored columns will be replaced with dynamically generated columns based on the latest API version.

Set IncludeCustomFields to false to improve performance.

Include Custom Objects

In case you need to access custom objects (tables), created to your Oracle Sales instance, you can use the IncludeCustomObjects connection property. By default, this property is set to false, and when queries are attempted on custom objects, they will fail to execute due to the internal schema being used. Similarly to IncludeCustomFields, setting this property to true, will issue one or more extra requests.

Generate Schema Files

If you need to regularly work with custom fields and/or custom objects, you can use the GenerateSchemaFiles connection property to store locally a dynamically generated schema and avoid constantly using IncludeCustomFields and IncludeCustomObjects, which in return will decrease execution time and payload.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Oracle Sales connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Oracle Sales and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Jitterbit Connector for Oracle Sales supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Opportunities WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

The Jitterbit Connector for Oracle Sales models the Oracle Sales objects as relational tables. Tables support both retrieving and updating data.

You can work with all the tables in your account. The connector connects to Oracle Sales and gets the list of tables and the metadata for the tables by calling the appropriate Web services. Any changes you make to your Oracle Sales account, such as adding a new table, adding new columns, or changing the data type of a column, will immediately be reflected when you use the connector to connect.

Tables

Oracle Sales objects have relationships to other objects; in the tables, these relationships are expressed through foreign keys. Oracle Sales objects are also part of an application that the account contains. For example, the table Opportunities represents the Opportunity object, which is part of the Sales application.

Tables

The connector models the data in Oracle Sales as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Oracle Sales Tables

AccountAddresses

The address resource is used to view, create, and update addresses of an account. An address represents the location information of an account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountAddressPurposes

The address purpose resource is used to view, create, or modify the address purpose. The address purpose describes the use of an address, such as shipping address or billing address.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountContactPoints

The contact point resource is used to view, create, update, and delete contact points for an account. Contact points can be geographical addresses, phone numbers, e-mail IDs, URLs, messenger IDs, and so on.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountNotes

The note resource is used to capture comments, information, or instructions for an account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountOrganizationContacts

The account contacts resource is used to view, create, update, and delete an account contact. It refers to a person who functions as a contact for an account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountPrimaryAddresses

The primary address resource is used to view, create, and update primary address of an account. A primary address is the default communication address of an account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountRelationships

The relationship resource is used to view, create, and update account relationships.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AccountResources

The sales team member resource represents a resource party and is assigned to a sales account team. A sales account team member has a defined access role for the sales account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Accounts

The sales team member resource represents a resource party and is assigned to a sales account team. A sales account team member has a defined access role for the sales account.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Activities

Table to capture task and appointment information.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ActivityAssignees

Table used to capture the assignees/resources associated to the activity.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ActivityContacts

Table used to capture the contacts associated to the activity.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ActivityNotes

Note data objects that capture comments, information or instructions for an Oracle Fusion Applications business object.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ActivityObjectives

Table used to capture the objectives associated to the activity.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Assets

Usage information for the operation Assets.rsd.

Columns