ADP Export

Documentation for the ADP export script that generates an export file for ADP payroll system for McCloone company (PV6 code)

Parameters

{mindate} - Start date for the export period
{maxdate} - End date for the export period

Data Components

The report extracts and organizes the following data:

Company Code - Fixed value 'PV6' for McCloone
Batch ID - Fixed value 'TIMEBANK'
Employee ID - Employee IDNUM
Regular Hours - Sum of hours from pay designations 1, 59, and 60
Overtime Hours - Sum of hours from pay designation 2
Special Hours Codes - Various codes based on pay designation (H, S, V, F, J, CP)
Special Hours - Sum of hours from pay designations 4, 5, 6, 9, 10, and 69
Cost Center Numbers - Mapped from WG4 values to specific cost center codes

Technical Implementation

The script uses:

CASE statements to map pay designations to appropriate codes
Aggregation with SUM and GROUP BY to combine hours by employee and type
Round function to ensure hours are rounded to 2 decimal places
Filtering for active employees (STATUS != 2) and only McCloone company (WG1 = 22)

Example Usage

-- Run for a specific date range
DECLARE @MIN DATE = '7/1/2023'
DECLARE @MAX DATE = '7/15/2023'

-- Execute with parameters replaced
SELECT
    'PV6' as 'CO Code',
    'TIMEBANK' as 'Batch ID',
    -- Rest of the query with @MIN and @MAX substituted for `{mindate}` and `{maxdate}`

T-SQL

adp_export.sql

--McCloone
Select
    'PV6' as 'CO Code',
    'TIMEBANK' as 'Batch ID',
    e.IDNUM as 'File #',
    Round(SUM(Case when pd.paydesnum IN (1,59,60) then pd.hours else null end),2) as 'Reg Hours',
    Round(SUM(Case when pd.paydesnum = 2 then pd.hours else null end),2) as 'O/T Hours',
    CASE 
		WHEN pd.paydesnum = 4 THEN 'H'
		WHEN pd.paydesnum = 5 THEN 'S'
                      WHEN pd.paydesnum = 6 THEN 'V'
                      WHEN pd.paydesnum = 9 THEN 'F'
		WHEN pd.paydesnum = 10 THEN 'J'
		WHEN pd.paydesnum = 69 THEN 'CP'
		ELSE NULL 
	END AS 'Hours 3 Code',
    SUM(Case when pd.paydesnum IN (4,5,6,9,10,69) then pd.hours else null end) as 'Hours 3',
    CASE 
		WHEN pd.WG4 = 495 Then '706010050'
		WHEN pd.WG4 = 496 Then '706010100'
		WHEN pd.WG4 = 497 Then '706010115'
		WHEN pd.WG4 = 721 Then '706010115'
		WHEN pd.WG4 = 498 Then '706010150'
		WHEN pd.WG4 = 499 Then '706010170'
		WHEN pd.WG4 = 500 Then '706010180'
		WHEN pd.WG4 = 501 Then '710000215'
		WHEN pd.WG4 = 502 Then '710000220'
		WHEN pd.WG4 = 503 Then '710000230'
		WHEN pd.WG4 = 504 Then '710000240'
		WHEN pd.WG4 = 722 Then '710000270'
		WHEN pd.WG4 = 505 Then '710000280'
		WHEN pd.WG4 = 506 Then '710000290'
		WHEN pd.WG4 = 507 Then '810000300'
		WHEN pd.WG4 = 527 Then '810000305' --No Longer Used
		WHEN pd.WG4 = 528 Then '810000310'
		WHEN pd.WG4 = 508 Then '810000320'
		WHEN pd.WG4 = 509 Then '810000330'
		WHEN pd.WG4 = 510 Then '810000340'
		WHEN pd.WG4 = 531 Then '710000100'
		WHEN pd.WG4 = 530 Then '710000050'
		WHEN pd.WG4 = 532 Then '710000115'
		WHEN pd.WG4 = 533 Then '710000150'
		WHEN pd.WG4 = 534 Then '710000170'
		WHEN pd.WG4 = 535 Then '710000180'
		ELSE Null
	End as 'Temp Cost Number'
From
    EMPLOYEES e
    Join
    PAYDESHIST pd on E.FILEKEY = pd.FILEKEY
Where
	pd.EVENTDATE between {mindate} and {maxdate}
    AND e.STATUS != 2
    and e.WG1 = 22 --McCloone - PV6
    AND pd.PAYDESNUM in (1,2,4,5,6,9,10,59,60,69)
GROUP BY 
    e.idnum, pd.paydesnum, pd.wg4

Content Inventory

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