Low Invalid Workgroup

Overview

This utility script identifies and processes invalid workgroup assignments by analyzing pay designation history against employee and transaction rate configurations. It detects when employees have pay designation history with workgroup combinations that don't match their current employee record or available transaction rates, then creates supervisor edits to flag these invalid workgroup situations. The script also manages cleanup of outdated invalid workgroup edits.

Parameters

The script uses placeholder parameters that need to be replaced before execution:

@STARTDATE - Set to {mindate} placeholder (requires replacement)
@ENDDATE - Set to {maxdate} placeholder (requires replacement)
Edit Type: 404 (employee state edits)
Reason Code: 18 (invalid workgroup reason)

Example

Low Invalid Workgroup Example

Data Components

The script performs several key operations:

Existing Edit Analysis - Counts current invalid workgroup edits in date range
Invalid Workgroup Detection - Identifies pay history with invalid workgroup combinations
Edit Creation - Creates new supervisor edits for newly detected invalid workgroups
Edit Cleanup - Removes outdated invalid workgroup edits
Request Generation - Creates requests for affected employees
Results Reporting - Provides comprehensive processing statistics

Low Invalid Workgroup Example

EXISTS/NOT EXISTS logic for duplicate prevention
FULL OUTER JOIN for comprehensive request generation
Date range filtering for targeted processing

Invalid Workgroup Detection Logic

Employee Mismatch: Pay history workgroups don't match employee record workgroups
Transaction Rate Mismatch: Pay history workgroups don't match any available transaction rates
Comprehensive Check: Both employee and transaction rate validations must fail

Workgroup Validation Rules

Employee Check: All 7 workgroup fields (WG1-WG7) must match between PAYDESHIST and EMPLOYEES
Transaction Rate Check: Workgroups must match or be 0 (wildcard) in EMPTRANRATES
Invalid Condition: Record fails both employee and transaction rate validations

Processing Steps

Build Existing Edits: Collect current invalid workgroup edits in date range
Detect Invalid Events: Find pay history with invalid workgroup combinations
Create New Edits: Add supervisor edits for newly detected invalid workgroups
Remove Outdated Edits: Delete edits that no longer have corresponding invalid events
Generate Requests: Create processing requests for affected employees

Notes

Parameter Replacement Required: Replace {mindate} and {maxdate} placeholders before execution
Workgroup Validation: Performs comprehensive validation against employee and transaction rate data
Edit Management: Both creates new and removes outdated invalid workgroup edits
Request Generation: Creates processing requests for affected employees
Date Range Specific: Only processes records within specified date range
Reason Code 18: Uses specific reason code for invalid workgroup situations
Comprehensive Checking: Validates all 7 workgroup fields (WG1-WG7)
Transaction Rate Awareness: Considers wildcard (0) values in transaction rates
Replace placeholder parameters with actual dates before execution
Verify workgroup validation logic matches current business rules
Test on a small date range before processing large periods
Monitor the diagnostic output to understand processing results
Consider the impact on payroll processing and employee records
Run during maintenance windows to avoid conflicts with active operations
Review generated requests to ensure appropriate follow-up actions

T-SQL

low_invalid_wg.sql

SET NOCOUNT ON   

DECLARE @STARTDATE datetime  
DECLARE @ENDDATE datetime  
DECLARE @EXistingEDITS smallint  
DECLARE @InvalidWG smallint  
DECLARE @EDITSADDED smallint  
DECLARE @EditsRemoved smallint  
DECLARE @RESULTS varchar(255) 

DECLARE @EDITS table (FILEKEY int, EVENTDATE datetime, REASON smallint)
DECLARE @EVENTS table (EVENTDATE datetime, FILEKEY int, REASON smallint)
DECLARE @EDITSTOREMOVE Table (FILEKEY int)

SELECT @STARTDATE = {mindate}
SELECT @ENDDATE = {maxdate}  
SELECT @EXistingEDITS = 0  
SELECT @InvalidWG = 0  
SELECT @EDITSAdded = 0  
SELECT @EditsRemoved = 0  
SELECT @RESULTS = ''  

--build existing edits  
INSERT INTO @EDITS SELECT FILEKEY, EFFDATE, REASON FROM SUPEDITS WHERE EDITTYPE = 404 AND REASON IN (18) and EFFDATE between @STARTDATE and  @ENDDATE
SELECT @EXistingEDITS = @@ROWCOUNT

--build IWP events  
INSERT INTO @EVENTS   
SELECT DISTINCT EVENTDATE, FILEKEY, 18   
FROM PAYDESHIST P  
WHERE P.EVENTDATE BETWEEN @STARTDATE AND @ENDDATE  
AND  
 (SELECT COUNT(*) FROM EMPLOYEES E WHERE E.FILEKEY = p.FILEKEY AND E.WG1 = P.WG1 AND E.WG2 = P.WG2   
 AND E.WG3 = P.WG3 AND E.WG4 = P.WG4 AND E.WG5 = P.WG5 AND E.WG6 = P.WG6 AND E.WG7 = P.WG7) = 0  
AND  
 (SELECT COUNT(*) FROM EMPTRANRATES T WHERE T.FILEKEY = p.FILEKEY AND  
  (T.WG1 = P.WG1 OR T.WG1 = 0) AND  
  (T.WG2 = P.WG2 OR T.WG2 = 0) AND  
  (T.WG3 = P.WG3 OR T.WG3 = 0) AND  
  (T.WG4 = P.WG4 OR T.WG4 = 0) AND
  (T.WG3 = P.WG5 OR T.WG5 = 0) AND
  (T.WG3 = P.WG6 OR T.WG6 = 0) AND
  (T.WG3 = P.WG7 OR T.WG7 = 0)
 ) = 0
 Select @InvalidWG = @@ROWCOUNT

INSERT INTO SUPEDITS  
SELECT E.FILEKEY, 404, E.EVENTDATE, '12/30/1899 08:00', 'INVALIDWG', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, E.EVENTDATE, E.REASON, 0, 0, '', E.EVENTDATE, E.EVENTDATE, E.EVENTDATE,0,0,0,0,0,0,0,0  
FROM @EVENTS E  
WHERE (SELECT COUNT(*) FROM @EDITS WHERE FILEKEY = E.FILEKEY and EVENTDATE = E.EVENTDATE AND REASON = E.REASON) = 0
Select @EDITSADDED = @@ROWCOUNT

INSERT INTO @EDITSTOREMOVE
Select Distinct s.filekey FROM SUPEDITS S  
WHERE s.EDITTYPE = 404   
AND s.REASON IN (18)
AND s.EFFDATE BETWEEN @STARTDATE AND @ENDDATE  
AND (SELECT COUNT(*) FROM @EVENTS WHERE FILEKEY = s.FILEKEY and EVENTDATE = S.EFFDATE AND REASON = S.REASON) = 0 

DELETE S FROM SUPEDITS S  
WHERE s.EDITTYPE = 404   
AND s.REASON IN (18)
AND s.EFFDATE BETWEEN @STARTDATE AND @ENDDATE  
AND (SELECT COUNT(*) FROM @EVENTS WHERE FILEKEY = s.FILEKEY and EVENTDATE = S.EFFDATE AND REASON = S.REASON) = 0 
SELECT @EditsRemoved = @@ROWCOUNT

Insert INTO REQUESTS
Select Distinct ev.filekey,3,GETDATE(),1,0
from @EVENTS ev
FULL OUTER JOIN @EDITSTOREMOVE ER
on ev.FILEKEY = ER.FILEKEY

SELECT @RESULTS = CAST(@EXistingEDITS AS VARCHAR(20))+' - Existing Edits / ' + CAST(@InvalidWG AS VARCHAR(20)) + ' - Invalid WG Events / ' + CAST(@EDITSADDED AS VARCHAR(20)) + ' - Edits Created / ' + CAST(@EditsRemoved AS VARCHAR(20)) + ' - Edits Removed'

SELECT @RESULTS As Diagnostics

Content Inventory

Doc File: content/docs/utilities/low_invalid_wg.mdx
SQL Script: SQL/utilities/low_invalid_wg.sql
AOD Browser Report: public/aod/browser_reports/utilities/low_invalid_wg.browser_report
Screenshot: public/img/screenshots/reports/enhancements/invalidwg.jpg

Low Invalid Workgroup

On this page