---
title: SoilService
summary: API reference for SoilService.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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