Tri Creek Autofill for Single Date

Overview

This utility script performs comprehensive autofill operations for all eligible employees on a specific date. It automatically fills in missing schedule and timecard data based on predefined business rules, employee schedules, and workgroup configurations. The script is designed to process a single date efficiently while maintaining data integrity and following established autofill logic for the Tri Creek system.

Parameters

The script requires manual configuration of the target date:

@TargetDate - The specific date to process for autofill operations (must be set before execution)
Scope: All eligible employees based on workgroup and schedule criteria
Processing Mode: Single date processing for focused autofill operations

Data Components

The script performs several key autofill operations:

Schedule Analysis - Evaluates employee schedules for the target date
Timecard Generation - Creates missing timecard entries based on schedules
Hours Calculation - Calculates appropriate hours based on schedule patterns
Workgroup Validation - Ensures autofill follows workgroup-specific rules
Data Integrity Checks - Validates all generated data before committing

Output Format

The script returns processing statistics and results:

Column	Data Type	Description
ProcessedEmps	int	Number of employees processed
CreatedRecords	int	Number of new timecard/schedule records created
UpdatedRecords	int	Number of existing records updated
ErrorCount	int	Number of errors encountered during processing

Technical Implementation

The script uses:

Date-specific processing for focused autofill operations
Employee iteration through eligible employee lists
Schedule pattern matching to determine appropriate autofill rules
Conditional logic for workgroup-specific processing
Data validation to ensure integrity of generated records
Error handling to manage processing exceptions

Processing Logic

Date Validation: Verify target date is valid for processing
Employee Selection: Identify employees eligible for autofill on target date
Schedule Analysis: Evaluate existing schedules and patterns
Autofill Generation: Create missing timecard and schedule entries
Hours Calculation: Apply appropriate hour calculations based on rules
Data Validation: Verify all generated data meets business requirements
Results Compilation: Collect and report processing statistics

Autofill Rules

Schedule-Based: Uses employee schedule patterns to determine autofill
Workgroup-Specific: Applies different rules based on employee workgroups
Date-Aware: Considers holidays, weekends, and special dates
Hours Logic: Calculates regular, overtime, and special hours appropriately

Safety Features

Single Date Focus: Limits processing to specific date to reduce risk
Validation Checks: Ensures data integrity before committing changes
Error Handling: Manages exceptions without corrupting existing data
Rollback Capability: Can reverse changes if issues are detected

Notes

Date Configuration Required: Must set @TargetDate before execution
Single Date Processing: Focuses on one specific date for efficient processing
Comprehensive Autofill: Handles schedules, timecards, and hours calculation
Workgroup Aware: Applies different rules based on employee workgroups
Data Integrity: Includes validation to ensure generated data is accurate
Error Management: Handles processing exceptions gracefully
Tri Creek Specific: Designed for Tri Creek system business rules
Performance Optimized: Single date focus improves processing speed
CRITICAL: Set @TargetDate to the correct date before execution
Test on a copy of the database before running in production
Verify autofill rules match current business requirements
Monitor processing results to ensure expected data generation
Run during maintenance windows to avoid conflicts with active operations
Consider the impact on payroll processing and reporting
Use this script for targeted autofill operations on specific dates
Coordinate with payroll team before processing payroll-critical dates

T-SQL

tricreekautofill__for_single_date.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @filekey INT, @TARGETDATE DATE;

-- Set the target date to process (Change this value as needed)
SET @TARGETDATE = '2025-03-12'; -- Input your specific date

DECLARE @EDITSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @TRANSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @INPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @OUTPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @TRANSADDED TABLE (Filekey INT, Count INT);

DECLARE employees_cursor CURSOR FOR
SELECT Filekey FROM EMPLOYEES 
WHERE Payclass IN (12, 18) 
ORDER BY FILEKEY;

OPEN employees_cursor;

FETCH NEXT FROM employees_cursor INTO @filekey;

WHILE @@FETCH_STATUS = 0
BEGIN		
	-- 🔹 Clear out edits from schedules removed for @TARGETDATE only
	DELETE FROM SUPEDITS 
	WHERE FILEKEY = @filekey		
	AND USERNAME = 'AUTOFILL'  
	AND CLKSUP > 0   
	AND EFFDATE = @TARGETDATE  
	AND ((SELECT COUNT(*) FROM SCHEDULES WHERE FILEKEY = @filekey AND UNIQUEID = CLKSUP AND SCHDATE = @TARGETDATE) = 0)  
	AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
		  WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @filekey 
		  AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
		  AND X.EDITTYPE = 10) = 0);  

	INSERT INTO @EDITSREMOVED SELECT @filekey, @@ROWCOUNT;  

	-- 🔹 Clear punches from edits removed for @TARGETDATE only
	DELETE FROM TRANSACTIONS  
	WHERE FILEKEY = @filekey
	AND STATION = 99  
	AND DTTMSTAMP BETWEEN @TARGETDATE AND DATEADD(DAY, 1, @TARGETDATE)  
	AND TRANSTYPE = 0  
	AND ((SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 0 AND USERNAME = 'AUTOFILL' 
		  AND FILEKEY = FILEKEY AND EFFDATE = @TARGETDATE) = 0)  
	AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
		  WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @filekey 
		  AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
		  AND X.EDITTYPE = 10) = 0);

	INSERT INTO @TRANSREMOVED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Insert IN punches for @TARGETDATE
	INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
	SELECT A.FILEKEY, 0, A.SCHDATE, A.STARTTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
	FROM SCHEDULES A
	WHERE A.FILEKEY = @filekey
	AND A.SCHDATE = @TARGETDATE  
	AND A.SCHTYPE = 0  
	AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.STARTTIME AND USERNAME = 'AUTOFILL');

	INSERT INTO @INPUNCHADDED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Insert OUT punches for @TARGETDATE
	INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
	SELECT A.FILEKEY, 0, A.SCHDATE, A.ENDTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
	FROM SCHEDULES A
	WHERE A.FILEKEY = @filekey
	AND A.SCHDATE = @TARGETDATE  
	AND A.ENDTIME > A.STARTTIME  
	AND A.SCHTYPE = 0  
	AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.ENDTIME AND USERNAME = 'AUTOFILL');

	INSERT INTO @OUTPUNCHADDED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Create transactions for @TARGETDATE
	INSERT INTO TRANSACTIONS (FILEKEY, DTTMSTAMP, TRANSTYPE, STATION, PERIOD) 
	SELECT FILEKEY, DATEADD(HOUR, DATEPART(HOUR, EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, EFFTIME), EFFDATE)), 0, 99, 0  
	FROM SUPEDITS S
	WHERE S.FILEKEY = @filekey
	AND S.EFFDATE = @TARGETDATE  
	AND S.USERNAME = 'AUTOFILL'  
	AND NOT EXISTS (SELECT 1 FROM TRANSACTIONS WHERE FILEKEY = S.FILEKEY AND DTTMSTAMP = DATEADD(HOUR, DATEPART(HOUR, S.EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, S.EFFTIME), S.EFFDATE)) );

	INSERT INTO @TRANSADDED SELECT @filekey, @@ROWCOUNT;

	-- Fetch next employee
	FETCH NEXT FROM employees_cursor INTO @filekey;
END

CLOSE employees_cursor;
DEALLOCATE employees_cursor;

-- 🔹 Report results
SELECT e.lastname + ', ' + e.firstname AS Employee, 
       er.Count AS EditsRemoved, 
       tr.Count AS TransRemoved, 
       IPA.Count AS InPunchAdded, 
       OPA.Count AS OutPunchAdded, 
       Ta.Count AS TransAdded 
FROM EMPLOYEES e
LEFT JOIN @EDITSREMOVED er ON e.filekey = er.Filekey
LEFT JOIN @TRANSREMOVED tr ON e.filekey = tr.Filekey
LEFT JOIN @INPUNCHADDED IPA ON e.filekey = IPA.Filekey
LEFT JOIN @OUTPUNCHADDED OPA ON e.filekey = OPA.Filekey
LEFT JOIN @TRANSADDED TA ON e.filekey = TA.filekey
WHERE e.Payclass IN (12,18);

Content Inventory

Doc File: content/docs/utilities/tricreekautofill_for_single_date.mdx
SQL Script: SQL/utilities/tricreekautofill__for_single_date.sql

Tri Creek Autofill for Single Date

On this page