Update Pay Designations to Benefit Schedules

Script: scripts/utilities/update_paydes_to_benefit_schedules.sql

Overview

This utility script updates schedule records by converting pay designation-based benefits (SCHTYPE = 3) to proper benefit schedules (SCHTYPE = 2). It matches employee pay classes with benefit pay designations to ensure proper benefit assignment in the scheduling system.

Parameters

This script does not require input parameters but operates on:

• Source Schedule Type: SCHTYPE = 3 (pay designation-based schedules)
• Target Schedule Type: SCHTYPE = 2 (benefit schedules)

Data Components

The script performs a three-table join operation:

1. SCHEDULES - Target table for updates
2. BENEFITPAYDES - Lookup table for benefit pay designations
3. EMPLOYEES - Source table for employee pay class information

Output Format

The script returns a simple message indicating the number of affected rows:

Technical Implementation

The script uses:

• Multi-table UPDATE with implicit joins (comma-separated FROM clause)
• Conditional matching on FILEKEY, PAYDESNUM, and CLASS
• SCHTYPE conversion from 3 to 2
• @@ROWCOUNT to report the number of affected records

Notes

• Schedule Type Conversion: Changes SCHTYPE from 3 (pay designation) to 2 (benefit schedule)
• Pay Class Matching: Uses employee pay class to determine correct benefit assignment
• Benefit Assignment: Updates the BENEFIT field with the appropriate benefit value
• Data Integrity: Ensures proper relationship between employees, pay classes, and benefits
• Batch Operation: Processes all qualifying records in a single transaction
• Row Count Reporting: Provides feedback on the number of records affected
• Run during maintenance windows to avoid conflicts with scheduling operations
• Verify benefit assignments after running to ensure correct mappings

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

UPDATE SCHEDULES SET BENEFIT = B.BENEFIT, SCHTYPE = 2

FROM SCHEDULES S, BENEFITPAYDES B, EMPLOYEES E

WHERE E.FILEKEY = S.FILEKEY

    AND S.BENEFIT = B.PAYDESNUM

    AND B.CLASS = E.PAYCLASS

    AND S.SCHTYPE = 3

Select Convert(varchar,@@ROWCOUNT)+' Rows Changed'

Content Inventory

• Doc File: content/docs/utilities/update_paydes_to_benefit_schedules.mdx
• SQL Script: SQL/utilities/update_paydes_to_benefit_schedules.sql