SoilService

Implements ISoilService, the batch integration that exchanges debt data with the Oregon Department of Revenue's SOIL (State Offset and Interception Liability) program: it loads externally supplied balance files, synchronizes debtor reference records and adjustment transactions via stored procedures, and produces the fixed-width SOIL export file. It depends on IUnitOfWork for persistence and stored-procedure invocation, ICollectionsRepository for worker-ID lookups, IUserProfileService for update attribution, and IWorkerDataApiClient to enrich temp records with worker person/address data from Guidewire.

Purpose

• Reads a department-of-revenue balance file, consolidates same-SSN entries, and inserts/updates DeptRevenueSoilBalance records.
• Populates a temp person/address table from worker data and invokes the Create_reference_records stored procedure to refresh debtor-to-SOIL reference records.
• Populates the same temp table and invokes the create_SOIL_transactions stored procedure to generate SOIL adjustment transactions for a run date, then reports add/change/delete counts.
• Writes a fixed-width, comma-and-quote-delimited SOIL export file from the generated adjustment records for submission to the state.

Context

• Namespace: SAIF.Services.Collections
• Assembly/package: SAIF.Services (packaged inside the SAIF.SAIFCode.Core NuGet package)

Public members

Member	Signature	Purpose
UnitOfWork	protected readonly IUnitOfWork UnitOfWork	Repository/transaction gateway used for all balance, temp-table, and adjustment persistence, and for invoking SOIL stored procedures via CollectionsRepo.
CollectionsRepo	protected readonly ICollectionsRepository CollectionsRepo	Supplies worker-ID lookups for debtors/transactions and wraps the Create_reference_records/create_SOIL_transactions stored-procedure calls.
ProfileService	protected readonly IUserProfileService ProfileService	Resolves the current user for stamping updated balance records.
SoilService(...)	public SoilService(IUserProfileService profile, IUnitOfWork unitOfWork, ICollectionsRepository collectionsRepo, IWorkerDataApiClient workerDataApiClient)	Wires in the profile, unit-of-work, collections repository, and worker-data-API collaborators, enforcing that the repository and unit of work are non-null.
LoadDeptRevenueSoilBalance	public bool LoadDeptRevenueSoilBalance(string fileName, ILogWriter controlLog, ILogWriter controlReport)	Reads fixed-width balance lines from fileName, consolidates records sharing the same SSN, and inserts them as DeptRevenueSoilBalance rows in one transaction; returns false if the file does not exist.
UpdateSoilDebtorReference	public bool UpdateSoilDebtorReference(DateTime runDate, ILogWriter controlLog, ILogWriter controlReport)	Loads worker addresses into a temp table, invokes CollectionsRepo.CreateSoilDebtorReferenceRecords for runDate inside a transaction, then cleans up the temp rows; always returns true.
GenerateSoilAdjustments	public bool GenerateSoilAdjustments(DateTime runDate, ILogWriter controlLog, ILogWriter controlReport)	Loads worker addresses into a temp table, invokes CollectionsRepo.CreateSoilAdjustmentRecords for runDate inside a transaction, cleans up the temp rows, then tallies and logs added/changed/deleted adjustment counts for runDate; always returns true.
CreateSoilFile	public bool CreateSoilFile(DateTime runDate, string fileName, ILogWriter controlLog, ILogWriter controlReport)	Writes every DeptRevenueSoilAdjustment created on runDate to fileName as a fixed-format, ASCII-encoded SOIL record line, then logs add/change/delete record counts; always returns true.

Behavioral notes

• LoadDeptRevenueSoilBalance merges consecutive balance entries that share the same SSN (after sorting) by summing BalanceAmount and concatenating ReferenceText, then inserts the merged rows inside a single UnitOfWork.Begin()/Commit() transaction.
• UpdateSoilDebtorReference and GenerateSoilAdjustments both stage worker data in a shared temp table (TempDataPersonAddress) keyed by a newly generated Guid run ID, call a SOIL stored procedure through CollectionsRepo, and always delete the temp rows afterward via DeleteTempRecords, even though no explicit error handling guarantees this cleanup runs on exception*.
• The private InsertTempRecords/DeleteTempRecords helpers each wrap UnitOfWork in a using block and call Begin()/Commit() per worker or per delete, meaning many small transactions are issued rather than one batched transaction*.
• CreateSoilFile produces output using several module-level constant format strings (ProgCode = "879", a hardcoded interest-rate segment, and a placeholder mailing address), suggesting the exported layout follows a legacy, state-mandated fixed record format*.

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Services/SAIF.Services/Collections/SoilService.cs