Secondary Salary Processing

This utility script processes secondary salary information by parsing and distributing salary data from HR custom fields into individual employee field records. It handles both Salary 2 and Salary 3 configurations and provides diagnostic feedback on the number of records processed.

Parameters

No external parameters required - the script operates on existing employee HR data.

Data Components

The script consists of 2 main processing sections:

Salary 2 Processing - Parses Custom5 field data into employee fields 4, 5, and 6
Salary 3 Processing - Parses Custom6 field data into employee fields 7, 8, and 9

Output Format

The script returns diagnostic information:

Column	Description
Diagnostics	Summary of records processed (format: "X - Salary 2 Edits / Y - Salary 3 Edits")

Technical Implementation

The script uses:

UPDATE statements with subqueries to parse HR custom fields
String manipulation functions (LEFT, SUBSTRING, RIGHT)
Conditional processing with NULL and empty string checks
@@ROWCOUNT system variable for tracking affected records
Employee field mapping for salary distribution data

Field Mapping Structure

Salary 2 (Custom5 field):

Employee Field 4: Pay Record (first 2 characters)
Employee Field 5: Amount (characters 4-9, 6 characters)
Employee Field 6: Distribution (remaining 19 characters)

Salary 3 (Custom6 field):

Employee Field 7: Pay Record (first 2 characters)
Employee Field 8: Amount (characters 4-9, 6 characters)
Employee Field 9: Distribution (remaining 19 characters)

Notes

Only processes records where custom fields contain data (not empty or NULL)
Updates existing employee field records rather than creating new ones
Requires proper employee field structure to be in place
Custom5 and Custom6 fields must follow specific format: PayRecord(2) + Amount(6) + Distribution(19)
Diagnostic output helps verify processing completion
Should be run as part of payroll processing workflow

T-SQL

secondary_salary.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @S2records smallint
Declare @S3records smallint
Declare @Results varchar(255)

---Split PayRecord, Amount and Distribution into individual fields for Salary 2
Update EF set ASSTRING = (Select LEFT(Custom5,2) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=4 and ((Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
Update EF set ASSTRING = (Select SUBSTRING(Custom5,4,6) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=5 and ((Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
Update EF set ASSTRING = (Select RIGHT(Custom5,19) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=6 and ((Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom5 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
SELECT @S2records = @@ROWCOUNT


---Split PayRecord, Amount and Distribution into individual fields for Salary 3
Update EF set ASSTRING = (Select LEFT(Custom6,2) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=7 and ((Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
Update EF set ASSTRING = (Select SUBSTRING(Custom6,4,6) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=8 and ((Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
Update EF set ASSTRING = (Select RIGHT(Custom6,19) from EMPLOYEEHR where OWNERID=EF.FILEKEY) from employeefields EF where FIELDID=9 and ((Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) !='' or (Select custom6 from EMPLOYEEHR where OWNERID=ef.FILEKEY) Is Not Null)
SELECT @S3records = @@ROWCOUNT

SELECT @RESULTS = CAST(@S2records AS VARCHAR(20))+' - Salary 2 Edits / ' + CAST(@S3records AS VARCHAR(20)) + ' - Salary 3 Edits '

Select @RESULTS As Diagnostics

Content Inventory

Doc File: content/docs/reports/pay_information/secondary_salary.mdx
SQL Script: SQL/reports/pay_information/secondary_salary.sql

Secondary Salary Processing

On this page