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 Harmony 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¶