ADP Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8803 of the connector.

Get Started¶

ADP Version Support

The connector leverages the following ADP APIs to enable access to ADP Workforce Now data:

• Applicant Onboard V2 API
• Payroll Data Input API
• Payroll Output API
• Team Time Cards API
• Time Cards API
• Time Off Balances API
• Time Off Request API
• Validation Table Code List API
• Work Assignment API
• Work Assignment API
• Work Schedules API
• Worker Management API
• Workers API

Establish a Connection¶

Before You Connect¶

Before you can establish a connection, you must contact ADP and ask that they provide you with an OAuth app and its associated credentials.

These credentials can only be obtained from ADP and can't be retrieved directly.

Connect to ADP¶

Connect to ADP by specifying the following properties:

• OAuthClientId: Set this to the client ID of the app provided by ADP.
• OAuthClientSecret: Set this to the client secret of the app provided by ADP.
• SSLClientCert: Set this to the certificate provided by ADP.
• SSLClientCertPassword: Set this to the password of the certificate.
• UseUAT: The connector makes requests to the production environment by default. If using a developer account, set UseUAT = true.
• RowScanDepth: The maximum number of rows to scan for the custom fields columns available in the table. The default value will be set to 100. Setting a high value may decrease performance.

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

User Defined Views¶

The ADP 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 Workers 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.

Insert Parent and Child Records¶

Use Case¶

When inserting records, often there is a need to fill in details about child records that have a dependency on a parent.

For instance, when dealing with a CRM system, Invoices often cannot be entered without at least one line item. Since invoice line items can have several fields, this presents a unique challenge when offering the data as relational tables.

When reading the data, it is easy enough to model an Invoice and an InvoiceLineItem table with a foreign key connecting the two. However, during inserts, the CRM system requires both the Invoice and the InvoiceLineItems to be created in a single submission.

To solve this sort of problem, our tools offer child collection columns on the parent. These columns can be used to submit insert statements that include details of both the parent and the child records.

For example, let's say that the Invoice table contains a single column called InvoiceLineItems. During the insert, we can pass the details of the records that must be inserted to the InvoiceLineItems table into Invoice record's InvoiceLineItems column.

The following subsection describes how this might be done.

Methods for Inserting Parent/Child Records¶

The connector facilitates two methods for inserting parent/child records: temporary table insertion and XML aggregate insertion.

Temporary (#TEMP) tables¶

The simplest way to enter data would be to use a #TEMP table, or temporary table, which the connector will store in memory.

Reference the #TEMP table with the following syntax:

TableName#TEMP

#TEMP tables are stored in memory for the duration of a connection.

Therefore, in order to use them, you cannot close the connection between submitting inserts to them, and they cannot be used in environments where a different connection may be used for each query.

Within that single connection, the table remains in memory until the bulk insert is successful, at which point the temporary table will be wiped from memory.

For example:

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

Once the InvoiceLineItems table is populated, the #TEMP table may be referenced during an insert into the Invoice table:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

Under the hood, the connector will read in values from the #TEMP table.

Notice that the ReferenceNumber was used to identify what Invoice the lines are tied to. This is because the #TEMP table may be populated and used with a bulk insert, where there are separate lines for each invoice. This enables the #TEMP tables to be used with a bulk insert. For example:

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

In this case, we are inserting two different Invoices. The ReferenceNumber is how we determine which Lines go with which Invoice.

Direct XML Insertion¶

Direct XML can be used as an alternative to #TEMP tables. Since #TEMP tables are not used to construct them, it does not matter if you use the same connection or close the connection after insert.

For example:

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

OR

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

Note that the ReferenceNumber is not present in these examples because the XML, by its nature, is passed against the parent record in full per insert. Since the complete XML must be constructed and submitted for each row, there is no need to provide something to tie the child back to the parent.

Now insert the values:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

OR

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Example for ADP¶

For a working example of how temp tables can be used to insert data in ADP, please see the following. In ADP, the Input_* tables are special input only tables designed for assisting with #TEMP table insertion. You do not need to actually append "#TEMP" to them to use them.

Note that key references such as Ids may be different in your environment:

// Insert into Input_configurationTags child table
INSERT INTO Input_configurationTags (TagCode, TagDataType, TagValues, ReferenceNumber) VALUES ('Earning Type', 'String', '"T"', '1')
INSERT INTO Input_configurationTags (TagCode, TagDataType, TagValues, ReferenceNumber) VALUES ('Deduction Type', 'String', '"T"', '2')
INSERT INTO Input_configurationTags (TagCode, TagDataType, TagValues, ReferenceNumber) VALUES ('Deduction Type', 'String', '"P"', '2')

// Insert into Input_EarningInputs child table
INSERT INTO Input_EarningInputs (AssociateOID,PayrollGroupCode,EarningCodeValue,RateValue,NumberOfHours,EarningConfigurationTags,ReferenceNumber) VALUES ('G3BGDF8JG32ERTGK','3TQ','R','50.50', '40', 'Input_configurationTags', '1')
INSERT INTO Input_EarningInputs (AssociateOID,PayrollGroupCode,EarningCodeValue,RateValue,NumberOfHours) VALUES ('G3GGY14BNGZ313W8','3U7','R','50.40', '41');
INSERT INTO Input_EarningInputs (AssociateOID,PayrollGroupCode,EarningCodeValue,NumberOfHours) VALUES ('G3BGDF8JG32ERTGK','3TQ','O','4');

// Insert into Input_DeductionInputs child table
INSERT INTO Input_DeductionInputs (AssociateOID,PayrollGroupCode,DeductionCodeValue,DeductionRateValue,DeductionAmountcurrencyCode) VALUES ('G3BGDF8JG32ERTGK','3TQ','A','10.50', 'USD');
INSERT INTO Input_DeductionInputs (AssociateOID,PayrollGroupCode,DeductionCodeValue,DeductionRateValue,DeductionAmountcurrencyCode) VALUES ('G3BGDF8JG32ERTGK','3U7','A','10', 'USD');

// Insert into Input_ReimbursementInputs child table
INSERT INTO Input_ReimbursementInputs (AssociateOID,PayrollGroupCode,ReimbursementCodeValue,ReimbursementAmountValue,ReimbursementAmountCurrencyCode,ReimbursementConfigurationTags,ReferenceNumber) VALUES ('G3BGDF8JG32ERTGK','3TQ','B','25.00', 'USD', 'Input_configurationTags', '2');
INSERT INTO Input_ReimbursementInputs (AssociateOID,PayrollGroupCode,ReimbursementCodeValue,ReimbursementAmountValue,ReimbursementAmountCurrencyCode) VALUES ('G3BGDF8JG32ERTGK','3U7','B','25.00', 'USD');

//Insert into PayrollRuns parent table
INSERT INTO PayrollRuns#TEMP (PayrollGroupCodeValue, PayrollProcessingJobID, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs,  ReimbursementInputs) VALUES ('3TQ', 'TestProcessing', 'G3BGDF8JG32ERTGK', '1', '050198', 'Input_EarningInputs', 'Input_DeductionInputs', 'Input_ReimbursementInputs');
INSERT INTO PayrollRuns#TEMP (PayrollGroupCodeValue, PayrollWeekNumber, PayrollProcessingJobID, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs,  ReimbursementInputs) VALUES ('3U7', '1', 'TestProcessing', 'G3GGY14BNGZ313W8', '1', '020024', 'Input_EarningInputs', 'Input_DeductionInputs', 'Input_ReimbursementInputs');

// Execute the bulk insert
INSERT INTO PayrollRuns (PayrollGroupCodeValue, PayrollWeekNumber, PayrollProcessingJobID, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs,  ReimbursementInputs) SELECT PayrollGroupCodeValue, PayrollWeekNumber, PayrollProcessingJobID, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs,  ReimbursementInputs FROM PayrollRuns#TEMP

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 ADP connector models ADP objects as relational tables and views. An ADP 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 ADP APIs.

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

Tables

The connector models the data exposed by the ADP APIs as relational Tables.

Views

Views describes the available read-only views.

Stored Procedures

Stored Procedures are function-like interfaces to ADP.

Tables¶

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

ADP Connector Tables¶

Input_AdditionalRemunerations¶

Create aggregates for WorkersWorkAssignments.AdditionalRemunerations using this as a TEMP table. This table values only last as long as the connection remains open. When the connection to ADP is closed, all tables names started with Input are cleared.

Columns¶

Input_configurationTags¶

Add configurationTags aggregate for Input_DeductionInputs.DeductionConfigurationTags OR Input_EarningInputs.EarningConfigurationTags OR Input_ReimbursementInputs.ReimbursementConfigurationTags using this as TEMP table. This table values only last as long as the connection remains open. When the connection to ADP is closed, all tables names started with Input are cleared.

Columns¶

Input_DeductionInputs¶

Create aggregates for PayrollRuns.DeductionInputs using this as a TEMP table. This table values only last as long as the connection remains open. When the connection to ADP is closed, all tables names started with Input are cleared.

Columns¶

Input_EarningInputs¶

Create aggregates for PayrollRuns.EarningInputs using this as a TEMP table. This table values only last as long as the connection remains open. When the connection to ADP is closed, all tables names started with Input are cleared.

Columns¶

Input_ReimbursementInputs¶

Create aggregates for PayrollRuns.ReimbursementInputs using this as a TEMP table. This table values only last as long as the connection remains open. When the connection to ADP is closed, all tables names started with Input are cleared.

Columns¶

PayrollRuns¶

Add and view the payroll runs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• ItemID supports the '=' comparison.
• PayrollRegionCodeValue supports the '=' comparison.
• PayrollGroupCodeValue supports the '=' comparison.
• PayrollScheduleReferenceScheduleEntryID supports the '=' comparison.
• PayrollScheduleReferencePayrollWeekNumber supports the '=' comparison.
• PayrollScheduleReferencePayrollYear supports the '=' comparison.
• PayrollScheduleReferencePayrollRunNumber supports the '=' comparison.
• Level supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PayrollRuns WHERE ItemID = 'TXSMIb+yh9UbJ9-im9au7g=='

SELECT * FROM PayrollRuns WHERE PayrollRegionCodeValue = 'BOST'

SELECT * FROM PayrollRuns WHERE PayrollGroupCodeValue = '3TN'

SELECT * FROM PayrollRuns WHERE PayrollScheduleReferenceScheduleEntryID = '20201117141612-l6OF8VuGHJD1ydLFoe5+nGBEm7rZkaRSorra0woRs04='

SELECT * FROM PayrollRuns WHERE PayrollScheduleReferencePayrollWeekNumber = '40'

SELECT * FROM PayrollRuns WHERE PayrollScheduleReferencePayrollYear = '2020'

SELECT * FROM PayrollRuns WHERE PayrollScheduleReferencePayrollRunNumber = '1'

SELECT * FROM PayrollRuns WHERE Level = 'payroll'

Insert¶

Following is an example of how to inserting pay data inputs into PayrollRuns table. For example:

INSERT INTO PayrollRuns (PayrollGroupCodeValue, PayrollProcessingJobID, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs, ReimbursementInputs) VALUES ('3U7', 'TestProcessing', 'G3BGDF8JG32ERTGK', '1', '020024', '[{"earningCode":{"codeValue":"R"},"modifierCode":{"codeValue":"1"},"rate":{"rateValue":"44.50"},"configurationTags":[{"tagCode":"ShiftCode","tagValues":["1"]}],"numberOfHours":40},{"earningCode":{"codeValue":"O"},"modifierCode":{"codeValue":"2"},"numberOfHours":4}]', '[{"deductionCode":{"codeValue":"A"},"deductionRate":{"rateValue":9.5,"currencyCode":"USD"}}]', '[{"reimbursementCode":{"codeValue":"B"},"reimbursementAmount":{"amountValue":25,"currencyCode":"USD"}}]')

Inserting pay data inputs using Temp Table.

INSERT INTO PayrollRunsEarningInputs#TEMP (EarningCodeValue, RateValue, NumberOfHours) VALUES ('R', '50.50', '40');
INSERT INTO PayrollRunsDeductionInputs#TEMP (DeductionCodeValue, DeductionRateValue, DeductionAmountcurrencyCode) VALUES ('A', '10', 'USD');
INSERT INTO PayrollRunsReimbursementInputs#TEMP (ReimbursementCodeValue, ReimbursementAmountValue, ReimbursementAmountCurrencyCode) VALUES ('B', '25.00', 'USD');

INSERT INTO PayrollRuns (PayrollGroupCodeValue, PayrollProcessingJobID, PayrollWeekNumber, AssociateOID, PayNumber, PayrollFileNumber, EarningInputs, DeductionInputs,  ReimbursementInputs) VALUES ('3U7', 'TestProcessing', '53', 'G3BGDF8JG32ERTGK', '1', '020024', 'PayrollRunsEarningInputs#TEMP', 'PayrollRunsDeductionInputs#TEMP', 'PayrollRunsReimbursementInputs#TEMP');

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.

Workers¶

Returns workers details.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Workers WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM Workers WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM Workers WHERE AsOfDate = '2020-01-01'

Insert¶

Following is an example of how to inserting into Workers table. For example:

INSERT INTO Workers (PayrollGroupCode, OnboardingTemplateCode, OnboardingTemplateCodeName, OnboardingStatusCode, OnboardingStatusCodeName,  HireReasonCode, HireReasonCodeName, WorkerOriginalHireDate, PersonLegalNameGivenName, PersonLegalNameFamilyName1, PersonBirthDate, PersonHighestEducationLevelCode) VALUES ('3UD', '15336_7354', 'HR Only (System)', 'complete', 'complete', 'new', 'TESTHIRE 4', '2020-11-10', 'TestGivenName', 'TestFamilyName', '1990-06-01', 'DOC')

Following is an example of how to inserting into Workers table with WorkAssignements. For example:

INSERT INTO WorkersWorkAssignments#TEMP (StandardHoursQuantity, PayCycleCodeValue, BaseRemunerationHourlyRateAmountValue, WageLawCoverageCodeValue, BaseRemunerationDailyRateAmountValue) VALUES ('45', '4', 300, 'N',  100)

INSERT INTO Workers (PayrollGroupCode, OnboardingTemplateCode, OnboardingTemplateCodeName, OnboardingStatusCode, OnboardingStatusCodeName, HireReasonCode, HireReasonCodeName, WorkerOriginalHireDate, PersonBirthDate, PersonLegalNameFamilyName1, PersonLegalNameGivenName, PersonDisabledIndicator, PersonGenderCode, PersonHighestEducationLevelCode, PersonLegalAddressCityName, PersonLegalAddressCountryCode, PersonLegalAddressCountrySubdivisionLevel1Code, PersonLegalAddressCountrySubdivisionLevel1SubdivisionType, PersonLegalAddressLineOne, PersonLegalAddressLineTwo, PersonLegalAddressLineThree, PersonLegalAddressNameCodeShortName, PersonLegalAddressPostalCode, PersonLegalNameFamilyName1Prefix, PersonLegalNameGenerationAffixCode, PersonLegalNameInitials, PersonLegalNameMiddleName, PersonLegalNameNickName, PersonLegalNameQualificationAffixCode, PersonMaritalStatusCode, PersonMilitaryDischargeDate, PersonMilitaryStatusCode, WorkAssignments) VALUES ('3TQ', '15336_7354', 'HR Only (System)', 'complete', 'complete', 'new', 'TESTHIRE 16', '2020-12-30', '1990-06-02', 'TestGivenName', 'TestFamilyName', 'FALSE', 'M', 'GRD', 'Millburn', 'US', 'NJ', 'state', 'LineOne', 'LineTwo', 'LineThree', 'Legal Residence', '07041', 'Prefix1', '2nd', 'I', 'MiddleName', 'NickName', 'CFA', 'M', '2013-04-01', '12', 'WorkersWorkAssignments#TEMP')

Update¶

Following is an example of how to Update a Workers table:

UPDATE Workers SET PersonLegalNameGenerationAffixCode = '2nd', PersonLegalNameGivenName = 'GivenName', PersonLegalNameFamilyName1 = 'FamilyName1', PersonLegalNameFamilyName1Prefix = 'Prefix1', PersonLegalNameFamilyName2 = 'FamilyName2', PersonLegalNameFamilyName2Prefix = 'Prefix2', PersonLegalNameInitials = 'C', PersonLegalNameMiddleName = 'MiddleName', PersonLegalNameNickName = 'NickName', PersonLegalNamePreferredSalutations = '[{"salutationCode":{"codeValue":"Mr."},"typeCode":{"shortName":"Social"},"sequenceNumber":1}]', PersonLegalNameQualificationAffixCode = 'CFA' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET PersonLegalAddressNameCodeShortName = 'Legal Residence', PersonLegalAddressLineOne = 'LineOne', PersonLegalAddressLineTwo = 'LineTwo', PersonLegalAddressCityName = 'Millburn',  PersonLegalAddressCountryCode = 'US', PersonLegalAddressCountrySubdivisionLevel1SubdivisionType = 'state', PersonLegalAddressPostalCode = '07041' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET PersonMaritalStatusCode = 'M', PersonMaritalStatusEffectiveDateTime = '2020-12-01T00:00:00Z' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET  PersonHighestEducationLevelCode = 'GRD' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET  PersonGenderCode = 'M' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET PersonBirthDate = '1990-06-01' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

UPDATE Workers SET PersonMilitaryClassificationCodes = '[{"codeValue":"R"}]' WHERE AssociateOID = 'G3DXX3CRDERXK3C9'

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.

WorkersPersonCommunicationEmails¶

Returns workers person communication emails.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersPersonCommunicationEmails WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersPersonCommunicationEmails WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM WorkersPersonCommunicationEmails WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update a WorkersPersonCommunicationEmails table:

UPDATE WorkersPersonCommunicationEmails SET EmailUri = 'test@test.com' WHERE AssociateOID = 'G3349PZGBADQY8H8'

Columns¶

WorkersPersonCommunicationFaxes¶

Returns workers person communication faxes.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersPersonCommunicationFaxes WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersPersonCommunicationFaxes WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM WorkersPersonCommunicationFaxes WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update a WorkersPersonCommunicationFaxes table:

UPDATE WorkersPersonCommunicationFaxes SET AreaDialing = '232', DialNumber = '1234567' WHERE AssociateOID = 'G3349PZGBADQY8H8'

Columns¶

WorkersPersonCommunicationLandlines¶

Returns workers person communication landlines.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersPersonCommunicationLandlines WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersPersonCommunicationLandlines WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM WorkersPersonCommunicationLandlines WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update a WorkersPersonCommunicationLandlines table:

UPDATE WorkersPersonCommunicationLandlines SET AreaDialing = '232', DialNumber = '1234567' WHERE AssociateOID = 'G3349PZGBADQY8H8'

Columns¶

WorkersPersonCommunicationMobiles¶

Returns workers person communication mobiles.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersPersonCommunicationMobiles WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersPersonCommunicationMobiles WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM WorkersPersonCommunicationMobiles WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update a WorkersPersonCommunicationMobiles table:

UPDATE WorkersPersonCommunicationMobiles SET AreaDialing = '232', DialNumber = '1234567' WHERE AssociateOID = 'G3349PZGBADQY8H8'

Columns¶

WorkersPersonCommunicationPagers¶

Returns workers person communication pagers.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerID supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersPersonCommunicationPagers WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersPersonCommunicationPagers WHERE WorkerID = 'DRH9M9NPW'

SELECT * FROM WorkersPersonCommunicationPagers WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update a WorkersPersonCommunicationPagers table:

UPDATE WorkersPersonCommunicationPagers SET AreaDialing = '232', DialNumber = '1234567' WHERE AssociateOID = 'G3349PZGBADQY8H8'

Columns¶

WorkersWorkAssignments¶

Returns workers details.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.
• WorkerIdValue supports the '=' comparison.
• AsOfDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkersWorkAssignments WHERE AssociateOID = 'G3349PZGBADQY8H7'

SELECT * FROM WorkersWorkAssignments WHERE WorkerIdValue = 'DRH9M9NPW'

SELECT * FROM WorkersWorkAssignments WHERE AsOfDate = '2020-01-01'

Update¶

Following is an example of how to Update BaseRemuneration:

UPDATE WorkersWorkAssignments SET StandardHoursQuantity = '45', BaseRemunerationEffectiveDate = '2020-12-21', PayCycleCodeValue = '4', BaseRemunerationHourlyRateAmountValue = 300, WageLawCoverageCodeValue = 'N', BaseRemunerationCode = 'ADJ', ItemId = '34321368N' WHERE AssociateOID = 'G3GMC21PJFZT7K4F'

Following is an example of how to Update AdditionalRemuneration using aggregates:

UPDATE WorkersWorkAssignments SET AdditionalRemunerations = '[{"remunerationTypeCode":{"code":"AE","name":"additional earnings"},"remunerationRate":{"rate":70,"currencyCode":"USD"},"effectiveDate":"2020-12-20","nameCode":{"code":"1FA"},"inactiveIndicator":false}]', itemid = '35777493N' WHERE AssociateOID = 'G3TGG0M57JZEXCP1'

Following is an example of how to Update AdditionalRemuneration using Temp Table:

INSERT INTO Input_AdditionalRemunerations#TEMP (RemunerationTypeCode, RemunerationRate, RemunerationCurrencyCode, effectiveDate, NameCode, InactiveIndicator) VALUES ('AE', '70', 'USD', '2021-01-04', 'R', false)

UPDATE WorkersWorkAssignments SET AdditionalRemunerations = 'Input_AdditionalRemunerations#TEMP', itemid = '35777493N' WHERE AssociateOID = 'G3TGG0M57JZEXCP1'

Following is an example of how to Update Worker Assignment Termination:

UPDATE WorkersWorkAssignments SET TerminationDate = '2020-01-31', LastWorkedDate = '2020-01-31', AssignmentStatusReasonCodeValue = 'A00', RehireEligibleIndicator = true, SeveranceEligibleIndicator = true, TerminationComments = 'Looking for better growth and oppurtunities', itemid = '00691088N' WHERE AssociateOID = 'G3TGG0M57JZECKRB'

Following is an example of how to Update Worker Type:

UPDATE WorkersWorkAssignments SET WorkerTypeCodeValue = 'F', ItemId = '31095304_1668', EventReasonCode = 'ADL', EffectiveDate = '2021-01-01' WHERE AssociateOID = 'G3Q8G47NKHBV1SMT'

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.

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.

ADP Connector Views¶

AdditionalRemunerationNameCode¶

Returns additional remuneration name code.

Columns¶

AssociatePaymentsAllocationsEarningsAndBenefits¶

View the associate payment allocation earnings and benefits in the payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsAllocationsEarningsAndBenefits WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsAllocationsEarningsAndBenefits WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsAllocationsEarningSections¶

View the earning sections for the associate payments allocations in payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsAllocationsEarningSections WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsAllocationsEarningSections WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsAllocationsNonStatutoryDeductions¶

View the non-statutory deductions for the associate payments allocations in payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsAllocationsNonStatutoryDeductions WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsAllocationsNonStatutoryDeductions WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsAllocationsStatutoryDeductions¶

View the statutory deductions for the associate payments allocations in payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryEarningsSections WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryEarningsSections WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsSummaryEarningsAndBenefits¶

View the associate payments earnings and benefits in the payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryEarningsAndBenefits WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryEarningsAndBenefits WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsSummaryEarningsSections¶

View the associate payments earnings sections items in the payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryEarningsSections WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryEarningsSections WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsSummaryNonStatutoryDeductions¶

To view the non statutory deduction for the associate in payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryNonStatutoryDeductions WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryNonStatutoryDeductions WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsSummaryPayrollAccumulations¶

To View the associate payments payroll accumulations in the payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryPayrollAccumulations WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryPayrollAccumulations WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

AssociatePaymentsSummaryStatutoryDeductions¶

View the statutory deduction for the associate in payroll outputs.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ItemId is required to make a request and the rest of the filter is executed client side within the connector.

• ItemId supports the '=' and IN comparisons.

SELECT * FROM AssociatePaymentsSummaryStatutoryDeductions WHERE ItemId = 'TXSMIb+yh9UbJ9-im9au7g=='
SELECT * FROM AssociatePaymentsSummaryStatutoryDeductions WHERE ItemId IN ('TXSMIb+yh9UbJ9-im9au7g==', 'XXSMIb+yh9UbJ9-im9au7g==')

Columns¶

CostCenters¶

Returns all Cost number codes setup for the client.

Table Specific Information¶

Select¶

No filters are supported server side for this table. All criteria will be handled client side within the connector.

For example, the following query is processed server side:

SELECT * FROM CostCenters

Columns¶

DeductionInputCode¶

Returns deduction input code.

Columns¶

EarningInputCode¶

Returns earning input code.

Columns¶

GenerationAffixCode¶

Returns legal name generation affix code.

Columns¶

HighestEducationLevelCode¶

Returns highest education level code value.

Columns¶

MaritalStatusCode¶

Returns marital status codes.

Columns¶

OnboardingTemplate¶

Returns onboarding template.

Columns¶

PaidTimeOffBalances¶

Returns Paid TimeOff Balances.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The AssociateOID is required to make a request and the rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.

SELECT * FROM PaidTimeOffBalances WHERE AssociateOID = 'G3349PZGBADQY8H7'

Columns¶

PaidTimeOffRequestEntries¶

Returns PaidTimeOff Requests.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The AssociateOID is required to make a request and the rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.

SELECT * FROM PaidTimeOffRequestEntries WHERE AssociateOID = 'G3349PZGBADQY8H7'

Columns¶

PaidTimeOffRequests¶

Returns PaidTimeOff Requests.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The AssociateOID is required to make a request and the rest of the filter is executed client side within the connector.

• AssociateOID supports the '=' comparison.

SELECT * FROM PaidTimeOffRequests WHERE AssociateOID = 'G3349PZGBADQY8H7'

Columns¶

PayrollGroup¶

Returns payroll group.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Category supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PayrollGroup WHERE Category = 'US'

Columns¶

PersonalContacts¶

Personal emergency contacts.

Table Specific Information¶

Select¶

The connector uses the ADP API to process WHERE clause conditions built with the AssociateOID column and '=' operator.

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

SELECT * FROM PersonalContacts WHERE AssociateOID = 'G3349PZGBADQY8H7'

The rest of the filter is executed client-side within the connector.

Columns¶

QualificationAffixCode¶

Returns work assignement worker type code.

Columns¶

ReimbursementInputCode¶

Returns reimbursement input code.

Columns¶

TeamTimeCards¶

View the Team Time Cards.

Table Specific Information¶

Select¶

The connector will use the ADP API to process WHERE clause conditions built with the following column and operator. The ManagerOID is required to make a request and the rest of the filter is executed client side within the connector.

• ManagerOID supports the '=' comparison.

SELECT * FROM TeamTimeCards WHERE ManagerOID = 'G3349PZGBADQY8H7'

Columns¶