Help & Support Center
Search:  
Contents
:
IndexPrintBookmark

Home > Guides > Administrator Guide > Integration > HRIS Systems > Sage HRMS  > Push Via SQL Web API

Push Via SQL Web API

Need Help on this Topic? Email Support


This process will require users to update/complete one page inside Cyber Recruiter to transfer hired applicants to Sage HRMS.  The page will be under the Applicant menu item or may be part of the On-boarding process.

Establishing Connectivity

The process for transferring new hires from Cyber Recruiter to Sage HRMS using a dynamic link requires the setup of a Web API connection and knowledge of the SQL information to connect to the Sage HRMS database.

On a Web Server that can access the Sage HRMS SQL database via Port 1433 (or another port*)

NOTE: This integration uses SOAP protocols.  So traffic using this protocol must not be blocked or a rule must be set up in the firewall to allow the program to use the protocol. 

​

Ensure the following components are set up

  1. .NET 3.5 SP1 needs to be installed/activated
  2. The account accessing the database from the program must be an SQL account (not Windows Authentication) 
  3. The application pool account for the site (Usually Network Service) needs full control registry access to hklm/software/wow6432/visibilitysoftware/abraCRconnect

The Application Pool should have the following attributes

  1. .NET CLS Version = v4.0
  2. Enable 32-Bit Application = True
  3. Management Pipeline Mode = Integrated 
  4. Process Model Identity = NetworkService

​

*Note - if you are not on version 11 or if you will be using another port please contact Technical Support for different instructions.

  1. Files (Passcode 1104): https://visibility.awsapps.com/workdocs/index.html#/share/document/70bee118f104c9e0dac2274b10ab7696418e3b17e65f2112128b8133901d3ef6
  2. In IIS, create a publicly available Website Application that points to the folder where the file was unzipped. Make note of the URL. We can’t detail this process here as it will always depend on the current IIS configuration. (Note: while this website/URL will need to be public, it would not be publicized. Also, anyone accessing it would have to have the "key" to be able to use the API.)
  3. Create a custom account with db_owner access to the Sage Database.  Using the sa password is NOT recommended.
  4. Once the website is created, right-click/Run As Administrator on AbraAPISetup.exe. In the setup screen which appears enter the following information
    • SQL Server = Sever where the Sage HRMS data is stored
    • Database = Name of the Sage HRMS database
    • User ID = User ID created in step 3
    • Password = Password for this user ID
  5. Click the Test button to verify the connection to the database.
  6. Click the Generate button to generate a unique authorization key.  This is the key which is entered into the Cyber Recruiter setup page.
  7. Click Submit to save the changes.

 

NOTE: If the hire into Sage does not work as expected and there is a registry error in the error log, go to the computer where the setup program was run (abraapisetup) and edit the following registry keys to and give full control permissions to the following users. IUSR, IIS_IUSRS, Network Service. The database information is saved as an encrypted connection string in the web.config file.  The CSKey in the registry provides the key. 

HKLM\Software\WOW6432\Visibility Software\AbracrConnect

​

Enable Sage HRMS in Cyber Recruiter

Once the connection information is obtained, take the following steps in the admin section to activate this interface.

Step 1: Set Global Defaults

Page Location: Admin > Links > Pre Defined Links > Sage HRMS 


Question

Response

Result

Integration to Sage HRMS  enabled?

Yes

Sage HRMS-specific page and menu items will be added in Cyber Recruiter.

Action Needed: Highlight the Users in the Drop-Down List who should have access to the Sage HRMS-specific functions in Cyber Recruiter.  Hold the CRTL key down to select more than one User.

No

Users will not see any references to Sage HRMS in Cyber Recruiter.

Do you use Sage HRMS  Payroll?

No

Payroll is not an option when using an SQL integration.  Only HR Related fields will appear during the transfer process.

Transfer Method SQL Server (WebAPI)

Capitalize everything?

 Yes

All data transferred to Sage HRMS will appear in capitalized lettering.

No

All data transferred to Sage HRMS will appear as it was entered in Cyber Recruiter.

Transfer Benefits Yes/No

Select 'Yes' to have the integration automatically add any benefits which are established as 'automatic add' 

 

Note: The integration looks only to the Standard Eligibility tab to determine the parameters for the automatic addition (salary type, employee type, age, etc.). It does NOT take into account anything listed on the Custom Eligibility tab.
Transfer Deductions N/A N/A
Transfer Taxes N/A N/A

Step 2: Define Additional Required Configuration and Options

Tab  Field  Note 
SQL Server (Web API) Enter the required Web API connection information to connect Cyber Recruiter to Sage HRMS.  This is the Authorization Key from the Setup on the server
  Web API URL Enter the URL for the Web API (created as noted above)
  API Authorization Key Enter the Authorization Key (created above)
Misc. Fields Identify any fields currently in Cyber Recruiter which can be passed to Sage HRMS' misc. fields. Use the drop-down menus to identify the location of the information in Cyber Recruiter.

The field name relates to which miscellaneous field the information will be stored in when transferred to Sage HRMS on the page specified in the parenthesis
  Misc. 1 (HR Status) Available Fields: Status, State Date, Referral Source, Employee Referred By, Desired Salary, PIN, Applicant Misc. 1-20
  Misc. 2 (HR Status) Available Fields: Status, State Date, Referral Source, Employee Referred By, Desired Salary, PIN, Applicant Misc. 1-20
  Misc. 3 (HR Status) Available Fields: Status, State Date, Referral Source, Employee Referred By, Desired Salary, PIN, Applicant Misc. 1-20
  Misc. 4 (HR Status) Available Fields: Applicant Misc. 1 - 20
  Misc. 1 (Current Pay) Available Fields: Status, State Date, Referral Source, Employee Referred By, Desired Salary, PIN, Applicant Misc. 1-20
  Misc. 2 (Current Pay) Available Fields: Status, State Date, Referral Source, Employee Referred By, Desired Salary, PIN, Applicant Misc. 1-20
  Misc. 3 (Current Pay) Available Fields: Applicant Misc. 1 - 20
  Misc. 4 (Current Pay) Available Fields: Applicant Misc. 1 - 20
     
  Emergency Contact 1 Pick from the misc. fields available to map the Name, Relationship and Phone Number of the contact.  This will NOT show on the hire page but will transfer over.
  Emergency Contact 2 Pick from the misc. fields available to map the Name, Relationship and Phone Number of the contact.  This will NOT show on the hire page but will transfer over.
     
Advanced Text Box Use this field to enter in an SQL statement to pass additional information over to Sage HRMS.  Fields can be passed to hrpersonal as well as hrtkpers.  Data can ONLY be sent to hrtkpers (custom screen with 'single record option') and not to the multiple record database. 

Please contact technical support for assistance in building these statements.

Configuration

Options

This tab includes options for turning on or off the view and required status of several fields of information to be transferred to Sage.

​

Note: If the Org Levels are not enabled on this tab, they will not show on the screen and blank fields will be passed to hrpersnl in Sage HRMS.
  Update Adjusted Seniority Date Setting this field to Yes allows the user to change the Adjusted Seniority field in Sage for rehires/job changes. If not enabled, the field will not be passed to Sage.
  Show EEO Information Setting this field to Yes will allow a user to view and update the EEO, Disability and Veteran information on file for the applicant/employee.  If set to No the system will display a Yes in the EEO, Disability and Veteran fields if they have a value in them or a No if the fields are blank.
  Require Marital Status Set this field to Yes to make the Marital Status field required.
  Require Birth Date Set this field to Yes to make the Birth Date field required
  Default Value for I9 Choose one of the options listed if a default is desired. Not selecting an option will present a blank field for updating.
  Default Value for Pay Frequency Select the option desired for the default pay frequency.
  Supervisors Select a value for this field to make either, both or neither of the Supervisor or Secondary Supervisor fields required. 
  Show OLevel 1 Set this field to Yes to display and update the Organization Level information for Sage HRMS. Choose the Yes radial button to make it Required if necessary. Note: if this field is not enabled (set to yes) then a blank field will be passed to Sage HRMS.
  Show OLevel 2 Set this field to Yes to display and update the Organization Level information for Sage HRMS. Choose the Yes radial button to make it Required if necessary. Note: if this field is not enabled (set to yes) then a blank field will be passed to Sage HRMS.
  Show OLevel 3 Set this field to Yes to display and update the Organization Level information for Sage HRMS. Choose the Yes radial button to make it Required if necessary. Note: if this field is not enabled (set to yes) then a blank field will be passed to Sage HRMS.
  Show OLevel 4 Set this field to Yes to display and update the Organization Level information for Sage HRMS. Choose the Yes radial button to make it Required if necessary. Note: if this field is not enabled (set to yes) then a blank field will be passed to Sage HRMS.
  Show OLevel 5 Set this field to Yes to display and update the Organization Level information for Sage HRMS. Choose the Yes radial button to make it Required if necessary. Note: if this field is not enabled (set to yes) then a blank field will be passed to Sage HRMS.
  Send Skills Set to Yes to send over the skills  records (make sure the skills codes in both systems match)
  Send Education Set to Yes to send over the educational records (based on HRMS fields available)
  Send Previous Employment Set to Yes to send over the employment records (based on HRMS fields available)

 

When using the 'Advanced' feature, the statement is a SQL query to pull information from Cyber Recruiter and post to Sage.  Information can be sent to hrpersnl and hrtkpers.  Data can ONLY be sent to hrtkpers (custom screen with 'single record option') and not to the multiple record database. 

When sending information to hrpersnl, {ReferralSource}, {Empno}, and {Company} will all get substituted in and allow for a valid SQL statement that can be run against the Sage SQL database. 

UPDATE hrpersnl set p_source=left('{ReferralSource}',25) where rtrim(ltrim(p_empno))=rtrim(ltrim('{Empno}')) and p_company='{Company}'

 

To send over data to a date field (p_vacdate, for example):

 

UPDATE hrpersnl set p_vacdate=convert(datetime,'{AMisc2}',101) where rtrim(ltrim(p_empno))=rtrim(ltrim('{Empno}')) and p_company='{Company}'

For custom fields in the hrtkpers table, a row needs to be added so the statement would be something like this. NOTED: There can only be one insert row so all fields to send over need to be in the same row.

INSERT hrtkpers(u_empno,u_company,u_TEPAYGR) values('{Empno}','{Company}','{WhateverFromCR}')

{WhateverFromCR}, needs to correspond to some fields in Cyber Recruiter that gets substituted into that custom script.  The possible values for it are basically all the same {} App and Req fields that are available when making a correspondence template.

See also

Hiring with Sage HRMS