Tri Creek Autofill for Single Date and File Key

Overview

This utility script performs targeted autofill operations for a specific employee on a specific date. It automatically fills in missing schedule and timecard data for the designated employee based on their individual schedule patterns, workgroup configurations, and established business rules. This script is ideal for correcting individual employee data issues or processing specific employee autofill requirements without affecting the entire workforce.

Parameters

The script requires manual configuration of both target date and employee:

@TargetDate - The specific date to process for autofill operations (must be set before execution)
@TargetFileKey - The specific employee filekey to process (must be set before execution)
Scope: Single employee processing for precise autofill control
Processing Mode: Targeted processing for individual employee correction

Data Components

The script performs several key autofill operations for the specified employee:

Employee Validation - Verifies the target employee exists and is eligible for processing
Schedule Analysis - Evaluates the employee's schedule for the target date
Timecard Generation - Creates missing timecard entries based on employee's schedule
Hours Calculation - Calculates appropriate hours based on individual schedule patterns
Workgroup Validation - Ensures autofill follows employee's workgroup-specific rules
Data Integrity Checks - Validates all generated data before committing

Output Format

The script returns processing statistics and results for the specific employee:

Column	Data Type	Description
EmployeeID	varchar	Employee identifier (filekey) processed
ProcessedDate	datetime	Date that was 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:

Employee-specific processing for targeted autofill operations
Date-specific processing for focused date handling
Individual schedule analysis to determine appropriate autofill rules
Employee workgroup logic for personalized processing rules
Data validation to ensure integrity of generated records
Error handling to manage processing exceptions for the individual

Processing Logic

Parameter Validation: Verify target date and filekey are valid
Employee Verification: Confirm employee exists and is eligible for processing
Schedule Analysis: Evaluate employee's existing schedules and patterns
Autofill Generation: Create missing timecard and schedule entries for the employee
Hours Calculation: Apply appropriate hour calculations based on employee's rules
Data Validation: Verify all generated data meets business requirements
Results Compilation: Collect and report processing statistics for the employee

Autofill Rules

Employee-Specific: Uses individual employee schedule patterns and preferences
Workgroup-Aware: Applies rules specific to the employee's workgroup
Date-Sensitive: Considers holidays, weekends, and special dates for the employee
Hours Logic: Calculates regular, overtime, and special hours based on employee patterns

Safety Features

Single Employee Focus: Limits processing to one employee to minimize risk
Date Limitation: Processes only the specified date
Validation Checks: Ensures data integrity before committing changes
Error Isolation: Contains errors to single employee without affecting others
Rollback Capability: Can reverse changes if issues are detected

Notes

Dual Configuration Required: Must set both @TargetDate and @TargetFileKey before execution
Individual Employee Processing: Focuses on one specific employee for precise control
Single Date Processing: Processes only the specified date for the employee
Comprehensive Autofill: Handles schedules, timecards, and hours calculation for the individual
Workgroup Aware: Applies rules specific to the employee's workgroup
Data Integrity: Includes validation to ensure generated data is accurate
Error Isolation: Errors affect only the target employee
Tri Creek Specific: Designed for Tri Creek system business rules
Precision Tool: Ideal for correcting individual employee data issues
CRITICAL: Set both @TargetDate and @TargetFileKey to correct values before execution
Verify the employee filekey exists in the system before running
Test on a copy of the database before running in production
Use this script for individual employee corrections or specific data fixes
Monitor processing results to ensure expected data generation for the employee
Consider the impact on the individual employee's payroll processing
Coordinate with the employee or supervisor before making autofill changes
Use this script when bulk processing is not appropriate or necessary

T-SQL

tricreekautofill__for_single_date_and_filekey.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TARGETDATE DATE, @TARGETFILEKEY INT;

-- Set the target date and filekey (Change these values as needed)
SET @TARGETDATE = '2025-03-12';  -- Input the specific date
SET @TARGETFILEKEY = 701;      -- Input the specific FileKey (Employee ID)

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);

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

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

-- 🔹 Clear punches from edits removed
DELETE FROM TRANSACTIONS  
WHERE FILEKEY = @TARGETFILEKEY
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 = @TARGETFILEKEY 
      AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
      AND X.EDITTYPE = 10) = 0);

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

-- 🔹 Insert IN punches
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 = @TARGETFILEKEY
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 @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Insert OUT punches
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 = @TARGETFILEKEY
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 @TARGETFILEKEY, @@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 = @TARGETFILEKEY
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 @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Report results for the specified employee
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)
AND e.Filekey = @TARGETFILEKEY;

Content Inventory

Doc File: content/docs/utilities/tricreekautofill_for_single_date_and_filekey.mdx
SQL Script: SQL/utilities/tricreekautofill__for_single_date_and_filekey.sql

Tri Creek Autofill for Single Date and File Key

On this page