Workgroup Code Correction Report

Overview

This utility script corrects inconsistent workgroup codes in the WORKGROUP5, WORKGROUP6, and WORKGROUP7 tables and provides a detailed report of all changes made. It identifies records where the CODE field doesn't match the expected value (WGNUM for groups 5 and 7, NAME for group 6), updates them to maintain data consistency, and generates a comprehensive report showing exactly what corrections were performed. This script is identical to the low_wg_code_correction script but with enhanced reporting focus.

Parameters

This script has no input parameters - it analyzes and corrects all workgroup records based on predefined business rules.

Data Components

The script processes three workgroup tables with different correction rules:

1. WORKGROUP5 - CODE should match WGNUM (numeric code)
2. WORKGROUP6 - CODE should match NAME (descriptive code)
3. WORKGROUP7 - CODE should match WGNUM (numeric code)

For each workgroup, it identifies and corrects:

• Records where CODE doesn't match the expected value
• Records where CODE is NULL

Output Format

The script returns a comprehensive diagnostic report showing all corrections made:

Report Structure

1. Workgroup 5 section header
2. Individual change descriptions for Workgroup 5
3. Workgroup 6 section header
4. Individual change descriptions for Workgroup 6
5. Workgroup 7 section header
6. Individual change descriptions for Workgroup 7

Technical Implementation

The script uses:

• Table variables for tracking records that need correction
• Conditional UPDATE statements based on workgroup-specific rules
• While loops for processing corrections (though simplified to single iteration)
• UNION queries for comprehensive reporting
• JOIN operations to compare before/after values
• Detailed change tracking for audit purposes

Correction Rules

Processing Logic

1. Identification: Find records with incorrect or NULL codes
2. Storage: Store problematic records in table variables for reporting
3. Correction: Update codes according to workgroup-specific rules
4. Reporting: Generate detailed change report with before/after values

Notes

• Data Consistency: Ensures workgroup codes follow standardized rules
• Comprehensive Coverage: Processes all three workgroup tables
• Detailed Reporting: Shows exactly what changes were made with before/after values
• NULL Handling: Corrects both incorrect and missing codes
• Workgroup-Specific Rules: Applies different logic per workgroup type
• Safe Processing: Uses table variables to track changes
• Audit Trail: Provides complete record of corrections performed
• Report Focus: Emphasizes detailed change reporting for audit purposes
• Identical Logic: Same correction logic as low_wg_code_correction script
• Run during maintenance windows to avoid conflicts with active operations
• Review the correction rules to ensure they match current business requirements
• Verify workgroup data integrity after script execution
• Consider the impact on reports and processes that depend on workgroup codes
• Test on a copy of the database before running in production
• Monitor for any applications that might depend on the old code values
• Use this script when detailed change reporting is required for audit purposes
• Save the report output for documentation and compliance requirements

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @WG5C int, @WG6C int, @WG7C int

DECLARE @WG5T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))
DECLARE @WG6T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))
DECLARE @WG7T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))

------WORKGROUP 5
INSERT INTO @WG5T Select wg5.WGNUM, wg5.CODE, wg5.NAME from WORKGROUP5 WG5 where wg5.CODE!=Cast(wg5.WGNUM as varchar) or wg5.CODE is null

Set @WG5C = (Select Count(*) from @WG5T)

While @WG5C>0
	Begin
		update WORKGROUP5 set CODE=WGNUM where WGNUM in (Select WGNUM from @WG5T)
		Set @WG5C=0
	End

------WORKGROUP 6
INSERT INTO @WG6T Select wg6.WGNUM, wg6.CODE, wg6.NAME from WORKGROUP6 WG6 where wg6.CODE!=wg6.NAME or wg6.CODE is null

Set @WG6C = (Select Count(*) from @WG6T)

While @WG6C>0
	Begin
		update WORKGROUP6 set CODE=Name where WGNUM in (Select WGNUM from @WG6T)
		Set @WG6C=0
	End

------WORKGROUP 7
INSERT INTO @WG7T Select wg7.WGNUM, wg7.CODE, wg7.NAME from WORKGROUP7 WG7 where wg7.CODE!=Cast(wg7.WGNUM as varchar) or wg7.CODE is null

Set @WG7C = (Select Count(*) from @WG7T)

While @WG7C>0
	Begin
		update WORKGROUP7 set CODE=WGNUM where WGNUM in (Select WGNUM from @WG7T)
		Set @WG7C=0
	End

------DISPLAY RESULTS
Select 'Workgroup 5',1
UNION
Select t5.NAME+' code was changed from ' + t5.CODE +' to '+ w5.code as Diagnostics,2 from @WG5T t5, WORKGROUP5 w5 where t5.WGNUM=w5.WGNUM
UNION
Select Null,3
UNION
Select 'Workgroup 6',4
UNION
Select t6.NAME+' code was changed from ' + t6.CODE +' to '+ w6.code as Diagnostics,5 from @WG6T t6, WORKGROUP6 w6 where t6.WGNUM=w6.WGNUM
UNION
Select Null,6
UNION
Select 'Workgroup 7',7
UNION
Select t7.NAME+' code was changed from ' + t7.CODE +' to '+ w7.code as Diagnostics,8 from @WG7T t7, WORKGROUP7 w7 where t7.WGNUM=w7.WGNUM
order by 2

Content Inventory

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