Automated Mainely Tubs Payroll Journal Entries and Helped Keep Employee Data in Sync with Workato

A Workato-based integration between Paylocity and NetSuite - syncing active employee records automatically and turning every payroll run into a single, balanced, GL-mapped journal entry, with dedicated run and error logging across both automations.

 

About the Company

Name	Mainely Tubs
Industry	Retail Industry
Size	Payroll and GL mapping span 30+ departments, several territories, and multiple sell locations/subsidiaries in NetSuite
Location	Scarborough, Maine-US
Years in Operation	47+ years (since 1978)
Relevant Details	Mainely Tubs runs payroll and HR through Paylocity and uses NetSuite as its financial system of record. The two platforms had no native integration prior to this engagement. Employee updates and every payroll journal entry were entered manually.

 

Challenges and Objectives

Application Landscape

• HR & Payroll: Paylocity, accessed through a dedicated Paylocity connector (Employee, Pay Statement Details, and related objects).
• Financial System of Record: NetSuite, via its SOAP web services connector (Employee upsert, Journal Entry upsert, employee entity search/validation).
• Automation Platform: Workato - two connected automations, an Employee Master Sync and a Payroll Journal Entry Sync.
• Data Layer: 2 Workato Lookup Tables (Department Mapping, Supervisor Mapping) plus 3 dedicated Data Tables (Payroll JE Run Log, Employee Detail Log, Error Log) and a separate Employee Sync Error Log.
• Scheduling: Time-based triggers - Employee Master Sync runs every 30 days; Payroll Journal Entry Sync runs nightly on a month-end-aware schedule.

Challenges

Prior to this engagement, Paylocity and NetSuite operated independently - a common starting point for companies running best-in-class, purpose-built HR/payroll and financial systems side by side. That created a few specific challenges:

• No Native Connection: Employee changes and every payroll journal entry had to be entered into NetSuite by hand, with no automated bridge between the two systems.
• Complex Pay Code Mapping: Dozens of Paylocity pay codes - earnings, employee and employer taxes, deductions, and trust items - each needed to land on the correct NetSuite GL account and the correct side of the ledger, consistently, on every pay run.
• Balancing Every Journal Entry: A journal entry that doesn't balance is unusable in NetSuite, so the sync had to guarantee total debits equal total credits on every check date automatically, without a manual plug entry.
• Reconciliation Blocked by Blended Cash Lines: The original design combined every withholding type into one blended cash line per employee, which made it impossible for the client's accounting lead to isolate and hold HSA withholdings - which settle out of the bank account on a delay - until they actually cleared.
• Data Quality on the Source Side: Paylocity records didn't always map cleanly to NetSuite - including missing or mismatched external IDs, sign conventions on reimbursement-style codes, and miscoded pay codes that surfaced only once real payroll data ran through the pipeline.
• No Ongoing Visibility: There was no built-in way to see, after the fact, which employees were processed on a given run, which check dates balanced, or why a run had failed.

Objectives

• Hands-Off Employee Sync: Automatically keep active Paylocity employees current in NetSuite - department, supervisor, title, contact and address details - without manual entry, while leaving terminated-employee handling to the client's own process.
• Automatic, Balanced Payroll JEs: Automatically turn every payroll run into a single, correctly balanced NetSuite journal entry, with every pay code mapped to the right GL account and side.
• Reconciliation-Friendly Cash Detail: Restructure the cash side of each journal entry into a small, fixed set of company-wide buckets - separating HSA withholdings from the rest of take-home pay - so the client's accounting lead can reconcile against the bank on his own timeline.
• Full Traceability: Log every run, every employee processed, and every error to dedicated data tables so issues can be found and fixed quickly.
• Accurate Mapping at Scale: Maintain department and supervisor mapping through dedicated lookup tables that can be updated without touching recipe logic.

 

Our Solution

Solution

Twenty20 Systems designed and built two connected Workato automations - an Employee Master Sync and a Payroll Journal Entry Sync - that keep Paylocity and NetSuite in step automatically, sharing a lookup-table-driven mapping layer and a dedicated logging layer for traceability.

Solution Architecture

Automation 1: Employee Master Sync

Use Case 1: Pull and Filter Active Employees

• A scheduled trigger runs every 30 days and pulls the full employee roster from Paylocity.
• Records are filtered down to active employees only (status code A) - terminated employees are intentionally excluded and handled through the client's own internal process.

Use Case 2: Resolve, Enrich & Sync to NetSuite

• For each active employee, the framework fetches full employee detail from Paylocity and upserts an Employee record in NetSuite, keyed to the Paylocity employee ID as the external ID so re-runs update the existing record rather than duplicating it.
• Department and supervisor are resolved through two dedicated lookup tables (Department Mapping, Supervisor Mapping) rather than hardcoded values, so org changes don't require touching recipe logic.
• Custom NetSuite fields (Territory, Sell Location, Paylocity employee number), contact details, address, hire date, and gender are all mapped from the Paylocity record in the same step.
• Any failure is caught and written to a dedicated Employee Sync Error Log rather than failing silently.

Automation 2: Payroll Journal Entry Sync

Use Case 1: Pull and Map Every Pay Statement

• A nightly, month-end-aware scheduled trigger loops every employee and pulls their Paylocity pay statement detail for the target period through a dedicated processing function.
• Every pay code - earnings, employee and employer taxes, deductions, and trust items - is mapped to its NetSuite GL account and the correct debit or credit side through a maintained pay-code-to-GL reference table.
• Department, territory, and sell-location dimensions are resolved from the employee's Paylocity cost centers so each line carries the correct NetSuite dimensions.
• NetSuite employee entities are validated before any line is built against them, catching missing or mismatched external IDs before they can produce a bad journal entry.

Use Case 2: Merge, Net & Balance the Combined Entry

• Every employee's resolved lines for a check date are merged into a single company-wide journal entry, netting duplicate account/department/dimension combinations into one line rather than posting one line per employee per code.
• A single “NET PAY” (or “NET PAY ADJUSTMENT”) plug line is calculated and added automatically so total debits equal total credits on every entry - no manual balancing.
• The finished entry is upserted into NetSuite as a Journal Entry, with every run, every processed employee, and any error logged to three dedicated data tables (Run Log, Employee Detail Log, Error Log) for full traceability.

Use Case 3: Cash-Bucket Restructuring for Bank Reconciliation

• Fixed, Company-Wide Buckets: The cash side of the entry is restructured into 6 fixed, company-wide bucket totals per check date - Direct Deposit, Taxes, Trust Agency, 401K, HSA Paylocity, and HSAEE - each carrying a fixed department, location, and territory confirmed with the client's accounting lead, so every bucket merges into exactly one line company-wide.
• Expense Side Untouched: P&L accounting is completely unaffected - every pay code still posts to its existing GL account exactly as before; only the cash-side representation changes.
• Validated Against the Full Employee Population: The bucket logic and its data-quality fixes - a miscoded deduction code and a sign-handling issue on a reimbursement-style pay code - were confirmed correct across the full employee population, not just a test sample.

 

Supporting Infrastructure

Traceability & Run Logging

• Three dedicated Workato data tables record every payroll JE run (Run Log), every employee processed within that run (Employee Detail Log), and every error encountered (Error Log) - giving the team a queryable history rather than relying on NetSuite's list view alone.
• A separate Employee Sync Error Log captures failures from the Employee Master Sync automation.

Lookup-Table-Driven Mapping

Department and supervisor mapping are maintained in dedicated lookup tables rather than hardcoded in recipe logic, so organizational changes don't require a recipe edit.

Feature	Functionality
Once in 30-Day Employee Sync	Active Paylocity employees kept current in NetSuite automatically
Lookup-Table-Driven Mapping	Department & supervisor resolved via maintained reference tables, no hardcoded logic
Full Pay-Code-to-GL Mapping	Every earnings/tax/deduction/trust code mapped to the correct NetSuite account & side
Automatic JE Balancing	NET PAY plug line calculated and added so every entry balances
Company-Wide Line Netting	Per-employee lines merged into one line per account/department combination
Reconciliation-Ready Cash Buckets	Cash side split into 6 fixed, company-wide buckets so HSA can be isolated for reconciliation
Entity Validation	NetSuite employee entities checked before journal lines are built against them
Full Run Traceability	Every run, employee, and error logged to dedicated data tables

 

Implementation Overview

• Connected Paylocity's Employee, Pay Statement Details, and related objects to NetSuite's Employee and Journal Entry records through NetSuite's SOAP connector.
• Brought the Employee Master Sync to live, stable production first, establishing the Department and Supervisor Mapping lookup tables as the shared mapping layer for both automations.
• Built out the full pay-code-to-GL reference table covering dozens of earnings, tax, deduction, and trust codes, refined through structured validation against the client's own numbers.
• Ran July as the payroll JE pipeline's first full production run, then worked through a structured validation pass with the client - resolving a missing NetSuite external ID, a stale hardcoded date filter left over from a catch-up run, and a reversed bonus-code mapping, and confirming a legitimate off-cycle entry that had appeared to be missing.
• Replaced the pipeline's hardcoded catch-up date filter with a parameterized target_year/target_month, so ongoing monthly runs default to the current month automatically.
• Removed the temporary test-employee filter used during bucket development and validated the restructured cash buckets against the full employee population, with fixed department, location, and territory values confirmed with the client's accounting lead so every bucket merges into one line company-wide.

 

Results

Measurable Outcomes or Benefits Achieved

• Automated, Consistent Employee Sync: Active employee records - department, supervisor, title, and contact details - are kept current in NetSuite automatically every 30 days, removing manual data entry for every new hire and employee change.
• Automated, Balanced Payroll Journal Entries: Every payroll run is automatically turned into a single, correctly balanced NetSuite journal entry, with every pay code mapped to the right GL account, removing manual JE preparation entirely.
• Reconciliation-Ready Cash Detail: Restructuring the cash side of each entry into 6 fixed, company-wide buckets lets the client's accounting lead isolate HSA withholdings from the rest of take-home pay and reconcile each bucket independently.
• Hardened Through Real Payroll Validation: Structured validation against July's first full production run caught and resolved four distinct data issues before the pipeline became the client's standing monthly process.
• Increased System Reliability: Every run, processed employee, and error is now logged to dedicated data tables, giving the team full traceability instead of relying on NetSuite's list view alone.

 

Customer Testimonials or Feedback

[Pending - to be added once Mainely Tubs provides a quote for external use.]

 

Conclusion

Key Takeaways

• Automated employee and payroll journal entry sync replaced manual data entry between Paylocity and NetSuite, keeping both systems reliably in step.
• Automatic entry balancing turned payroll journal entry preparation from a manual task into a single validated output per check date.
• Full run, employee, and error logging gives the team a queryable history of the sync, not just the current state in NetSuite.

At Twenty20 Systems, we helped Mainely Tubs bring Paylocity and NetSuite into a single, connected system built on Workato. By combining automated employee and payroll data sync with automatic journal entry balancing and full run/error traceability, we replaced manual employee updates and manual payroll JE preparation with a validated, repeatable monthly process.

The result: employee data that stays current automatically, payroll journal entries that balance every time without manual intervention, and reconciliation-ready cash detail that supports monthly bank reconciliation.