TMS85 Export

Parameters

• {mindate} - Start date for the export period (commented out in sample, example: '6/18/2020')
• {maxdate} - End date for the export period (commented out in sample, example: '7/1/2020')

Data Components

The report creates a specialized fixed-width format with the following components:

• Record Header:

  • An initial dummy record with predefined values
  • Record format: '000178000000000000000000000000', '000000000', '000000000', etc.

• Employee Records:

  • Employee FILEKEY (internal ID)
  • Employee name (LASTNAME, FIRSTNAME)
  • Employee ID (repeated twice)
  • Pay designation number
  • Fixed code ('00')
  • Formatted hours (2 decimal places, no decimal point)
  • Work group 2 code
  • Work group 3 number
  • Formatted end date (MMddyy with no separators)

Technical Implementation

The script features:

• SET NOCOUNT ON and SET ANSI_WARNINGS OFF to control SQL Server behavior
• Temporary table (@Honey) to store and format the data
• Special string formatting:
  • Replace function to remove decimal points from hours
  • Format function for dates with slashes removed
  • Fixed-width string formatting
• Aggregation to sum hours by employee, pay designation, and work groups
• Filter for active employees only (ACTIVESTATUS=0)
• Rounding of hours to two decimal places
• Initial dummy record for system requirements

Output Format

The output is ordered by employee name and pay designation, with fixed-width fields and no delimiters between columns. The formatting is designed to match the TMS85 system's specific import requirements.

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE

/*		
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='6/18/2020'
SET @MAX='7/1/2020'



DECLARE @Honey TABLE(Filekey INT,
    Name VARCHAR(50),
    ID1 VARCHAR(50),
    ID2 VARCHAR(50),
    Paydesnum int,
    static1 varchar(2),
    Hours varchar(50),
    wg2code varchar(3),
    wg3num varchar(3),
    static2 varchar(8))

Insert INTO @Honey
select 0, '000178000000000000000000000000', '000000000', '000000000', 0, '00', '0000', '000', '000', '0000000'

Insert INTO @Honey
Select e.filekey,
    e.LASTNAME+', '+e.FIRSTNAME,
    e.IDNUM,
    e.IDNUM,
    pd.paydesnum,
    '00',
    Replace(Convert(varchar (50),format(Round(SUM(pd.hours),2),'00.00')),'.',''),
    w2.code,
    pd.WG3,
    Replace(format(@MAX,'MM/dd/yy'),'/','')

from EMPLOYEES e, PAYDESHIST pd, WORKGROUP2 w2
where e.FILEKEY=pd.FILEKEY
    and pd.WG2=w2.WGNUM
    and e.ACTIVESTATUS=0
    and pd.EVENTDATE between @MIN and @MAX

Group by e.FILEKEY,e.LASTNAME,e.FIRSTNAME,e.IDNUM,pd.PAYDESNUM,w2.CODE,pd.WG3


Select *
from @Honey
order by 2,5

Content Inventory

• Doc File: content/docs/payroll_exports/tms85_export.mdx
• SQL Script: SQL/payroll_exports/tms85_export.sql