SAP Fieldglass Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

SAP Fieldglass Version Support

The connector leverages the SAP Fieldglass REST API to enable bidirectional access to SAP Fieldglass data.

Establish a Connection

Obtain Your Credentials

Please contact your SAP Fieldglass representative to obtain an OAuthClientId, OAuthClientSecret, and EnvironmentURL.

Connect to SAP Fieldglass

To authenticate to production environments, specify the following:

• APIKey: Your SAP Fieldglass API Key. To obtain this, log in to the SAP Fieldglass Integration Tools and click Create API Application Key.
• OAuthClientId: The OAuth Client ID that you obtained from your SAP Fieldglass representative.
• OAuthClientSecret: OAuth Client Secret that you obtained from your SAP Fieldglass representative.
• EnvironmentURL: The SAP Fieldglass Environment URL that you obtained from your SAP Fieldglass representative.

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 SAP Fieldglass connector.

User Defined Views

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

SSL Configuration

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

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 SAP Fieldglass and then processes the rest of the query in memory (client-side).

User Defined Views

The SAP Fieldglass connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used 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 as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM AuditTrails 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

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting 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 SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The SAP Fieldglass connector models SAP Fieldglass objects as relational tables and views. A SAP Fieldglass object has relationships to other objects; in the tables, these relationships are expressed through foreign keys. The following sections show the available API objects and provide more information on executing SQL to SAP Fieldglass APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Connectors as Views

SAP Fieldglass Connectors are exposed as views and internally use the default connector names, such as Active Worker Download.

In order to query the connectors as views, they should first be enabled and configured in SAP Fieldglass using the Integration Tools.

Make sure to set the ConnectorDataFormat property based on the configured download data format.

In order to list Connectors that are not part of the exposed views or have custom names, set the ConnectorNames property to a comma-separated list of Connector names. Make sure to correctly write the Connector names including spaces.

Connector Custom Fields

By default, the IncludeCustomFields property is set to false. Set it to true to list custom fields or fields that are not part of the current schemas.

To include the custom fields, the connector needs to make an individualized request per Connector, which may cause poor performance when listing metadata.

Selecting From Reports in SAP Fieldglass

The connector offers the possibility to select reports as relational views.

Selecting data from your reports

To access the data from your reports, query a view with the reports name. All the possible views can be seen using the sys_views system table.

SELECT * FROM [Job Posting Report 1]

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

SAP Fieldglass Connector Views

ActiveWorkers

Retrieve all active workers using the Active Worker Download connector.

Columns

ActiveWorkersSingleLine

Retrieve all active workers using the Active Worker Single Line Download connector.

Columns

ActiveWorkOrderProfileWorkers

Retrieve all active work orders including profile workers using the Active Work Order Profile Worker Download connector.

Columns

ActiveWorkOrders

Retrieve all active work orders using the Active Work Order Download connector.

Columns

Approvals

Query this table for approvals in SAPFieldglass.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following columns and operators. The remaining filter criteria is executed client-side within the connector.

• ModuleId supports the = operator.
• WorkItemId supports the '=' operator. This must be used alongside ModuleId.

For example, the following queries are processed server-side:

SELECT * FROM Approvals WHERE ModuleId = '70'
SELECT * FROM Approvals WHERE ModuleId = '70' AND WorkItemId = '300'

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.

ApprovedExpenseSheets

Retrieve approved expense sheets using the Approved Expense Sheet Download connector.

Columns

ApprovedTimeSheets

Retrieve all approved time sheets using the Approved Time Sheet Download connector.

Columns

Assignments

Retrieve all active assignments using the Assignment Download connector.

Columns

AuditTrails

Query this table for audit trails in SAPFieldglass.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following column and operator. The remaining filter criteria is executed client-side within the connector.

• Status supports the = operator.

For example, the following query is processed server-side:

SELECT * FROM AuditTrails WHERE Status = 'OK'

Columns

BusinessUnits

Retrieve business units using the Business Unit Download connector.

Columns

CATSTimeSheets

Retrieve all time sheets entered in SAP CATS (Cross-Application Time Sheet) that are now in Draft, Rejected, and/or Pending Approval status within a provided parameter using the CATS Time Sheet Download connector.

View Specific Information

The following pseudo-columns should be specified in the WHERE clause in order to query the view:

• StartDate specifies the start date of time sheets.
• EndDate specifies the end date of time sheets.

For example:

SELECT * FROM CATSTimeSheets WHERE StartDate = '2023/01/01' AND EndDate = '2023/04/01'

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.

CostCenters

Retrieve cost centers using the Cost Center Download connector.

Columns

DistributionLists

Retrieve distribution lists using the Distribution List Download connector.

Columns

DraftRejectedTimeSheets

Retrieve all time sheets in draft/rejected approval status using the Draft Rejected Time Sheet Download connector.

View Specific Information

The following pseudo-columns should be specified in the WHERE clause in order to query the view:

• StartDate specifies the start date of time sheets.
• Company specifies the buyer company for which time sheets are retrieved.

For example:

SELECT * FROM DraftRejectedTimeSheets WHERE StartDate = '2023/01/01' AND Company = 'CDT'

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.

DraftTimeSheets

Retrieve all time sheets in Draft status using the Draft Time Sheet Download connector.

Columns

Locations

Retrieve locations using the Location Download connector.

Columns

RateCategories

Retrieve rate categories using the Rate Category Download connector.

Columns

RateCodes

Retrieve rate codes using the Rate Code Download connector.

Columns

RateComponentGroups

Retrieve rate component groups using the Rate Component Group Download connector.

Columns

RateComponents

Retrieve rate components using the Rate Component Download connector.

Columns

RateGrids

Retrieve rate grid codes using the Rate Grid Download connector.

Columns

RateGroups

Retrieve rate groups using the Rate Group Download connector.

Columns

RateSchedules

Retrieve rate schedules using the Rate Schedule Download connector.

Columns

RejectReasons

Query this table for reject reasons in SAPFieldglass.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following column and operator. The remaining filter criteria is executed client side within the connector.

• ModuleId supports the = operator.

For example, the following query is processed server-side:

SELECT * FROM RejectReasons WHERE ModuleId = '70'

Columns

RequisitionLineItems

Retrieve all high-level requisition information using the Requisition Line Item Detail Download connector. This connector differs from the Requisition Detail Download in that it adds SOW characteristic details. Each payment characteristic is added as a separate line and its corresponding details is sourced from the SOW.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following columns and operators. The remaining filter criteria is executed client-side within the connector.

• Include supports the '=' operator. This parameter specifies if work order or SOW details will be retrieved. Available values are: Work Order, SOW, and Both. By default, both types are retrieved.

For example, the following queries are processed server-side:

SELECT * FROM RequisitionLineItems WHERE Include = 'Work Order'
SELECT * FROM RequisitionLineItems WHERE Include = 'SOW'

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.

Requisitions

Retrieve all high-level requisition information using the Requisition Detail Download connector.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following columns and operators. The remaining filter criteria is executed client-side within the connector.

• Include supports the '=' operator. This parameter specifies if work order or SOW details will be retrieved. Available values are: Work Order, SOW, and Both. By default, both types are retrieved.

For example, the following queries are processed server-side:

SELECT * FROM Requisitions WHERE Include = 'Work Order'
SELECT * FROM Requisitions WHERE Include = 'SOW'

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.

Resources

Retrieve resources using the Resource Download connector.

Columns

SecurityWorkers

Retrieve all data on workers with Security IDs using the Worker Security Download connector.

Columns

SecurityWorkersSingleLine

Retrieve all data on workers with Security IDs using the Worker Security Single Line Download connector.

Columns

SecurityWorkersWithTransactionType

Retrieve worker records based on transaction type filtering logic using the Worker Security with Transaction Type Download connector.

Columns

SecurityWorkersWithTransactionTypeSingleLine

Retrieve worker records based on transaction type filtering logic using the Worker Security with Transaction Type Single Line Download connector.

Columns

ServiceEntrySheets

Retrieve service entry sheets using the Service Entry Sheet Download connector.

Columns

ServiceReceipts

Retrieve service receipts that are in Pending Approval, Approval Paused, Rejected and/or Approved status using the Service Receipt Download connector.

Columns

Sites

Retrieve site codes using the Site Download connector.

Columns

SOWBids

Retrieve SOW and SOW bid information using the SOW with Bid Download connector.

Columns

SOWCharacteristics

Retrieve SOW characteristic information using the Approved Characteristic Download connector.

Columns

SOWs

Retrieve Statement of Work (SOW) information using the SOW Download connector.

Columns

SOWWorkerRoles

Retrieve worker roles in all statuses using the SOW Worker Role Download connector.

Columns

Suppliers

Retrieve supplier configuration information using the Supplier Download connector.

Columns

TimeJournals

Retrieve time journals that are in Submitted, Consolidated, and/or Errored status depending on a provided parameter using the Time Journal Download connector.

View Specific Information

The connector will use the SAP Fieldglass API to process WHERE clause conditions built with the following columns and operators. The remaining filter criteria is executed client-side within the connector.

• Status supports the '=' operator. This parameter specifies the status of the time journal. Available values are: All, Pending Approval, Approved, Rejected, Consolidated, and Errored. By default, all time journals are retrieved.

For example, the following queries are processed server-side:

SELECT * FROM TimeJournals WHERE Status = 'Approved'
SELECT * FROM TimeJournals WHERE Status = 'Rejected'

Columns