Shift Indirect Transfer Edit Scripts

Scripts:

scripts/utilities/third_shift_indirect_transfer_edit_script.sql
scripts/utilities/first_shift_indirect_transfer_edit_script.sql

Overview

These utility scripts automatically create indirect time transfer edits for employees based on their scheduled vs. actual work hours. They analyze employee schedules, calculate time worked in indirect activities, and generate appropriate transfer edits to move time from direct to indirect work categories. The scripts are designed for specific shifts and employee classifications.

Parameters

Both scripts operate with:

@SpecificDate - The target date for processing (typically previous day)
Target Workgroups: WG2 = 23, WG5 = 162 (third shift) / WG5 = 174 (first shift)
Pay Classes: Specific employee classifications (e.g., 35)
Active Status: Only processes active employees (ACTIVESTATUS = 0)

Data Components

The scripts perform several complex operations:

Schedule Analysis - Retrieves employee schedules and time boundaries
Time Calculation - Calculates actual vs. scheduled work hours
Transfer Amount Determination - Calculates indirect time based on hours worked
Edit Generation - Creates supervisor edits for time transfers
Workgroup Mapping - Maps specific workgroups to indirect categories
Diagnostic Reporting - Provides detailed output of changes made

Output Format

Column	Data Type	Description
EmployeeName	varchar	Employee first and last name
TransferAmt	int	Amount of time transferred (in minutes)
SchEnd	varchar	Scheduled end time (formatted)
ActualEnd	varchar	Actual end time (formatted)
TransferTime	varchar	Time when transfer edit is effective (formatted)

Technical Implementation

The scripts use:

Cursor-based processing to iterate through eligible employees
Complex time calculations with grace periods and shift boundaries
Conditional logic for transfer amount determination based on hours worked
Dynamic workgroup mapping for indirect time categorization
Supervisor edit insertion with detailed audit trail
Table variables for tracking inserted edits and diagnostics

Transfer Amount Logic

6+ hours worked: 60 minutes indirect time
4-6 hours worked: 40 minutes indirect time
2-4 hours worked: 30 minutes indirect time
Less than 2 hours: No transfer

Workgroup Mapping

Specific WG4 values (719, 716, 726, etc.) map to indirect category 469
Default workgroup assignments maintained for other categories

Notes

Shift-Specific: Separate scripts for different shift patterns and requirements
Automated Processing: Designed for scheduled execution, typically daily
Grace Period Handling: Includes logic for early/late punch tolerances
Duplicate Prevention: Checks for existing edits to prevent duplicates
Audit Trail: Creates detailed supervisor edits with full tracking
Complex Time Logic: Handles overnight shifts and cross-day boundaries
Workgroup Flexibility: Configurable workgroup mappings for different indirect categories
Employee Eligibility: Filters by multiple criteria (workgroup, pay class, status)
Requires careful testing before production deployment
Monitor for performance impact during peak processing times
Verify workgroup mappings align with current business rules
Review diagnostic output to ensure correct processing

T-SQL

third_shift_indirect_transfer_edit_script.sql

--Example clients JSJ
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @SpecificDate DATE = DATEADD(Day, -1, {mindate})
DECLARE @LASTNAME VARCHAR(30);
DECLARE @FIRSTNAME VARCHAR(30);
DECLARE @ScheduleDate DATE;
DECLARE @ScheduledStartTime TIME;
DECLARE @ScheduledEndTime TIME;
DECLARE @ActualStartTime DATETIME;
DECLARE @ActualEndTime DATETIME;
DECLARE @TransTime DATETIME;
DECLARE @TotalTimeWorked DECIMAL(10, 2);
DECLARE @TransferAmt DECIMAL(10, 2);
DECLARE @FILEKEY INT;
DECLARE @WG1 INT;
DECLARE @WG2 INT;
DECLARE @WG3 INT;
DECLARE @WG4 INT;
DECLARE @WG5 INT;
DECLARE @EditExists INT;
DECLARE @TransferWG4 INT;

Set @WG5 = 174;

-- Declare a table variable to store the names of employees with inserted edits
DECLARE @InsertedEdits TABLE (
    EmployeeName VARCHAR(61), TransferAmt Int, SchEnd Datetime, Actualend Datetime, TransTime Datetime
);

--Fetch Details for first shift employees
DECLARE Employee_Cursor CURSOR FOR
SELECT 
    e.LASTNAME,
    e.FIRSTNAME,
    s.SCHDATE AS ScheduleDate,
    CAST(s.STARTTIME AS TIME) AS ScheduledStartTime,
    CAST(s.ENDTIME AS TIME) AS ScheduledEndTime,
    (
        SELECT TOP 1 t.DTTMSTAMP
        FROM TRANSACTIONS t
        WHERE t.FILEKEY = e.FILEKEY
            AND CAST(t.DTTMSTAMP AS DATE) = CASE
                                                WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.SCHDATE AS DATE)
                                                ELSE CAST(s.SCHDATE AS DATE)
                                            END
            AND CAST(t.DTTMSTAMP AS TIME) BETWEEN DATEADD(HOUR, -3, CAST(s.STARTTIME AS TIME)) AND CASE
                                                                                                        WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.ENDTIME AS TIME)
                                                                                                        ELSE '23:59:59'
                                                                                                    END
        ORDER BY t.DTTMSTAMP
    ) AS ActualStartTime,
    (
        SELECT TOP 1 t.DTTMSTAMP
        FROM TRANSACTIONS t
        WHERE t.FILEKEY = e.FILEKEY
            AND CAST(t.DTTMSTAMP AS DATE) = CASE
                                                WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.SCHDATE AS DATE)
                                                ELSE DATEADD(DAY, 1, CAST(s.SCHDATE AS DATE))
                                            END
            AND CAST(t.DTTMSTAMP AS TIME) BETWEEN CASE
                                                      WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.STARTTIME AS TIME)
                                                      ELSE '00:00:00'
                                                  END
                                             AND DATEADD(MINUTE, 239, CAST(s.ENDTIME AS TIME))
        ORDER BY t.DTTMSTAMP DESC
    ) AS ActualEndTime,
	ROUND(COALESCE(SUM(ph.HOURS), 0), 2) AS TotalTimeWorked,
	CASE 
        WHEN SUM(ph.HOURS) >= 6 THEN 60
        WHEN SUM(ph.HOURS) BETWEEN 4 AND 6 THEN 40
        WHEN SUM(ph.HOURS) BETWEEN 2 AND 4 THEN 30
        ELSE 0
    END AS TransferAmt,
	e.FILEKEY,
	e.WG1,
	e.WG2,
	e.WG3,
	e.WG4
FROM 
    EMPLOYEES e
    INNER JOIN SCHEDULES s ON e.FILEKEY = s.FILEKEY
	LEFT JOIN PAYDESHIST ph ON e.FILEKEY = ph.FILEKEY
                            AND CAST(ph.EVENTDATE AS DATE) = CAST(DateAdd(Day,1,s.SCHDATE) AS DATE)
                            AND ph.PAYDESNUM IN (49)
WHERE 
    CAST(s.SCHDATE AS DATE) = @SpecificDate
    AND e.WG2 = 23
    AND e.WG5 = 162
    AND e.PAYCLASS IN (35)
    AND e.ACTIVESTATUS = 0
    AND s.SCHTYPE = 0
GROUP BY
    e.LASTNAME,
    e.FIRSTNAME,
    s.SCHDATE,
    s.STARTTIME,
    s.ENDTIME,
    e.FILEKEY,
	e.WG1,
	e.WG2,
	e.WG3,
	e.WG4
ORDER BY 
    e.LASTNAME,
    e.FIRSTNAME,
    s.STARTTIME;

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor INTO 
    @LASTNAME, @FIRSTNAME, @ScheduleDate, @ScheduledStartTime, @ScheduledEndTime, 
    @ActualStartTime, @ActualEndTime, @TotalTimeWorked, @TransferAmt, @FILEKEY, @WG1, @WG2, @WG3, @WG4;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Set the @TransTime variable based Left Late Grace Period
    IF @ActualEndTime BETWEEN CAST(DATEADD(Day, 1,@ScheduleDate) AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME) AND DATEADD(MINUTE, 10, CAST(DATEADD(Day, 1,@ScheduleDate) AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME))
    BEGIN
        SET @TransTime = CAST(DATEADD(Day, 1,@ScheduleDate) AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME);
    END
    ELSE
    BEGIN
        SET @TransTime = @ActualEndTime;
    END

	-- Initialize @TransferWG4 with the default value of @WG4
	SET @TransferWG4 = @WG4;

	-- Check if @WG4 is in the specified list
	IF @WG4 IN (719, 716, 726, 727, 352, 439, 519, 520, 568, 704, 705)
	BEGIN
		SET @TransferWG4 = 469
	END

	-- If @WG4 is originally 465, then set @TransferWG4 to 469
	--IF @WG4 = 465
	--BEGIN
		--SET @TransferWG4 = 469
	--END

	--Make sure edit doesn't exists 
    SELECT @EditExists = ISNULL(MIN(UNIQUEID), 0)
    FROM Supedits
    WHERE EDITTYPE = 101
        AND FILEKEY = @FILEKEY
        AND USERNAME = 'IndirectTR'
        AND CAST(EFFDATE AS DATE) = CAST(@ActualEndTime AS DATE);
	
	--Insert Edit if conditions are true.
    IF @EditExists = 0 AND @TransferAmt > 0 AND @TransTime IS NOT NULL
    BEGIN
        INSERT INTO Supedits (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, TSDATETIME, CANCELON, PREVEFFDATE, PREVEFFTIME, WG1, WG2, WG3, WG4, WG5,
			CLKSUP, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, REASON, SITE, CANCEL, CANCELBY, PREVWG4, PREVWG5)
		VALUES (
            @FILEKEY,
            101,
            @ActualEndTime,
            DATEADD(MINUTE, -@TransferAmt, @TransTime),
            'IndirectTR',
			GETDATE(),
			GETDATE(),
			GETDATE(),
			GETDATE(),
			@WG1,
			@WG2,
			@WG3,
			@TransferWG4,
			@WG5,
			0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
        );
        
        -- Insert the diagnostic details into the table variable
        INSERT INTO @InsertedEdits (EmployeeName, TransferAmt, SchEnd, Actualend, TransTime)
        VALUES (@FIRSTNAME + ' ' + @LASTNAME, @TransferAmt, @ScheduledEndTime, @ActualEndTime, DATEADD(MINUTE, -@TransferAmt, @TransTime));
    END;
	--Fetch Next employees
    FETCH NEXT FROM Employee_Cursor INTO 
        @LASTNAME, @FIRSTNAME, @ScheduleDate, @ScheduledStartTime, @ScheduledEndTime, 
        @ActualStartTime, @ActualEndTime, @TotalTimeWorked, @TransferAmt, @FILEKEY, @WG1, @WG2, @WG3, @WG4;
END;

CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

-- diagnostic report
SELECT EmployeeName, TransferAmt, Format(SchEnd, 'hh:mm tt') as SchEnd, Format(Actualend, 'MM/dd/yyyy hh:mm tt') as ActualEnd, Format(TransTime, 'MM/dd/yyyy hh:mm tt') as TransferTime
FROM @InsertedEdits
order by EmployeeName;

first_shift_indirect_transfer_edit_script.sql

--Example clients JSJ
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @SpecificDate DATE = {mindate}  --'2024-04-10' 
DECLARE @LASTNAME VARCHAR(30);
DECLARE @FIRSTNAME VARCHAR(30);
DECLARE @ScheduleDate DATE;
DECLARE @ScheduledStartTime TIME;
DECLARE @ScheduledEndTime TIME;
DECLARE @ActualStartTime DATETIME;
DECLARE @ActualEndTime DATETIME;
DECLARE @TransTime DATETIME;
DECLARE @TotalTimeWorked DECIMAL(10, 2);
DECLARE @TransferAmt DECIMAL(10, 2);
DECLARE @FILEKEY INT;
DECLARE @WG1 INT;
DECLARE @WG2 INT;
DECLARE @WG3 INT;
DECLARE @WG4 INT;
DECLARE @WG5 INT;
DECLARE @EditExists INT;
DECLARE @TransferWG4 INT;

Set @WG5 = 174;

-- Declare a table variable to store the names of employees with inserted edits
DECLARE @InsertedEdits TABLE (
    EmployeeName VARCHAR(61), TransferAmt Int, SchEnd Datetime, Actualend Datetime, TransTime Datetime
);

--Fetch Details for first shift employees
DECLARE Employee_Cursor CURSOR FOR
SELECT 
    e.LASTNAME,
    e.FIRSTNAME,
    s.SCHDATE AS ScheduleDate,
    CAST(s.STARTTIME AS TIME) AS ScheduledStartTime,
    CAST(s.ENDTIME AS TIME) AS ScheduledEndTime,
    (
        SELECT TOP 1 t.DTTMSTAMP
        FROM TRANSACTIONS t
        WHERE t.FILEKEY = e.FILEKEY
            AND CAST(t.DTTMSTAMP AS DATE) = CASE
                                                WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.SCHDATE AS DATE)
                                                ELSE CAST(s.SCHDATE AS DATE)
                                            END
            AND CAST(t.DTTMSTAMP AS TIME) BETWEEN DATEADD(HOUR, -3, CAST(s.STARTTIME AS TIME)) AND CASE
                                                                                                        WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.ENDTIME AS TIME)
                                                                                                        ELSE '23:59:59'
                                                                                                    END
        ORDER BY t.DTTMSTAMP
    ) AS ActualStartTime,
    (
        SELECT TOP 1 t.DTTMSTAMP
        FROM TRANSACTIONS t
        WHERE t.FILEKEY = e.FILEKEY
            AND CAST(t.DTTMSTAMP AS DATE) = CASE
                                                WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.SCHDATE AS DATE)
                                                ELSE DATEADD(DAY, 1, CAST(s.SCHDATE AS DATE))
                                            END
            AND CAST(t.DTTMSTAMP AS TIME) BETWEEN CASE
                                                      WHEN CAST(s.STARTTIME AS TIME) < CAST(s.ENDTIME AS TIME) THEN CAST(s.STARTTIME AS TIME)
                                                      ELSE '00:00:00'
                                                  END
                                             AND DATEADD(MINUTE, 239, CAST(s.ENDTIME AS TIME))
        ORDER BY t.DTTMSTAMP DESC
    ) AS ActualEndTime,
    ROUND(COALESCE(SUM(ph.HOURS), 0), 2) AS TotalTimeWorked,
    CASE 
        WHEN SUM(ph.HOURS) >= 6 THEN 60
        WHEN SUM(ph.HOURS) BETWEEN 4 AND 6 THEN 40
        WHEN SUM(ph.HOURS) BETWEEN 2 AND 4 THEN 30
        ELSE 0
    END AS TransferAmt,
    e.FILEKEY,
	e.WG1,
	e.WG2,
	e.WG3,
	e.WG4
FROM 
    EMPLOYEES e
    INNER JOIN SCHEDULES s ON e.FILEKEY = s.FILEKEY
    LEFT JOIN PAYDESHIST ph ON e.FILEKEY = ph.FILEKEY
                            AND CAST(ph.EVENTDATE AS DATE) = CAST(s.SCHDATE AS DATE)
                            AND ph.PAYDESNUM IN (1, 2, 49)
WHERE 
    CAST(s.SCHDATE AS DATE) = @SpecificDate
    AND e.WG2 = 23
    AND e.WG5 = 162
    AND e.PAYCLASS IN (25, 27)
    AND e.ACTIVESTATUS = 0
    AND s.SCHTYPE = 0
GROUP BY
    e.LASTNAME,
    e.FIRSTNAME,
    s.SCHDATE,
    s.STARTTIME,
    s.ENDTIME,
    e.FILEKEY,
	e.WG1,
	e.WG2,
	e.WG3,
	e.WG4
ORDER BY
    e.LASTNAME,
    e.FIRSTNAME,
    s.STARTTIME;

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor INTO 
    @LASTNAME, @FIRSTNAME, @ScheduleDate, @ScheduledStartTime, @ScheduledEndTime, 
    @ActualStartTime, @ActualEndTime, @TotalTimeWorked, @TransferAmt, @FILEKEY, @WG1, @WG2, @WG3, @WG4;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Set the @TransTime variable based Left Late Grace Period
    IF @ActualEndTime BETWEEN CAST(@ScheduleDate AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME) AND DATEADD(MINUTE, 10, CAST(@ScheduleDate AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME))
    BEGIN
        SET @TransTime = CAST(@ScheduleDate AS DATETIME) + CAST(@ScheduledEndTime AS DATETIME);
    END
    ELSE
    BEGIN
        SET @TransTime = @ActualEndTime;
    END

	-- Initialize @TransferWG4 with the default value of @WG4
	SET @TransferWG4 = @WG4;

	-- Check if @WG4 is in the specified list
	IF @WG4 IN (719, 716, 726, 727, 352, 439, 519, 520, 568, 704, 705)
	BEGIN
		SET @TransferWG4 = 469
	END

	-- If @WG4 is originally 465, then set @TransferWG4 to 469
	--IF @WG4 = 465
	--BEGIN
		--SET @TransferWG4 = 469
	--END

	--Make sure edit doesn't exists 
    SELECT @EditExists = ISNULL(MIN(UNIQUEID), 0)
    FROM Supedits
    WHERE EDITTYPE = 101
        AND FILEKEY = @FILEKEY
        AND USERNAME = 'IndirectTR'
        AND CAST(EFFDATE AS DATE) = CAST(@ActualEndTime AS DATE);
	
	--Insert Edit if conditions are true.
    IF @EditExists = 0 AND @TransferAmt > 0 AND @TransTime IS NOT NULL
    BEGIN
        INSERT INTO Supedits (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, TSDATETIME, CANCELON, PREVEFFDATE, PREVEFFTIME, WG1, WG2, WG3, WG4, WG5,
			CLKSUP, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, REASON, SITE, CANCEL, CANCELBY, PREVWG4, PREVWG5)
		VALUES (
            @FILEKEY,
            101,
            @ActualEndTime,
            DATEADD(MINUTE, -@TransferAmt, @TransTime),
            'IndirectTR',
			GETDATE(),
			GETDATE(),
			GETDATE(),
			GETDATE(),
			@WG1,
			@WG2,
			@WG3,
			@TransferWG4,
			@WG5,
			0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
        );
        
        -- Insert the diagnostic details into the table variable
        INSERT INTO @InsertedEdits (EmployeeName, TransferAmt, SchEnd, Actualend, TransTime)
        VALUES (@FIRSTNAME + ' ' + @LASTNAME, @TransferAmt, @ScheduledEndTime, @ActualEndTime, DATEADD(MINUTE, -@TransferAmt, @TransTime));
    END;
	--Fetch Next employees
    FETCH NEXT FROM Employee_Cursor INTO 
        @LASTNAME, @FIRSTNAME, @ScheduleDate, @ScheduledStartTime, @ScheduledEndTime, 
        @ActualStartTime, @ActualEndTime, @TotalTimeWorked, @TransferAmt, @FILEKEY, @WG1, @WG2, @WG3, @WG4;
END;

CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

-- diagnostic report
SELECT EmployeeName, TransferAmt, Format(SchEnd, 'hh:mm tt') as SchEnd, Format(Actualend, 'MM/dd/yyyy hh:mm tt') as ActualEnd, Format(TransTime, 'MM/dd/yyyy hh:mm tt') as TransferTime
FROM @InsertedEdits
order by EmployeeName;

Content Inventory

Doc File: content/docs/utilities/shift_indirect_transfer_scripts.mdx
SQL Script: SQL/utilities/first_shift_indirect_transfer_edit_script.sql

Shift Indirect Transfer Edit Scripts

On this page