--- title: "CollectionsRepository" summary: API reference for CollectionsRepository status: draft owner: SAIF.Core documentation team last_reviewed: 2026-09-24 library: SAIF.Data.NHibernate reference_type: package --- # CollectionsRepository `CollectionsRepository` is the `ICollectionsRepository` implementation for `Case` (mapped to `LEGAL_COLLECTION`), and it is the single widest surface in this library for reading and writing the legal-collections subsystem: debtor/case lookups, address resolution, payment application, interest/statement selection, and the "SOIL" (State of Oregon Intercept, inferred) debt-intercept batch pipeline. Every read is a named-query call (`Session.GetNamedQuery()`) against Oracle package `WCIS_DBA.PK80010_LEGAL_COLLECTIONS`/`PK80011_LEGALCOL_SOIL`, and the three write operations (`ApplyPayments`, `CreateSoilDebtorReferenceRecords`, `CreateSoilAdjustmentRecords`) run those packages directly via inline `CreateSqlQuery().ExecuteUpdate()` calls rather than through NHibernate's entity change-tracking (`saif-data-nhibernate-analysis.md` OP-SDN-03 through OP-SDN-06). ## Purpose - Provide lookup and search operations over collections cases and their debtors (reference number, category totals, debtor name/SSN, addresses, free-text search) for collections case management screens/services. - Apply payments to a collection case's cost transactions by invoking the Oracle `APPLYPAYMENTS` procedure, per [SDN-BR-010](../../business-rules.md#sdn-br-010-collection-case-payments-are-applied-by-the-oracle-package-applypayments-for-the-case-and-payment-date). - Select transactions/debtors for interest calculation, missing-payment follow-up, and statement generation. - Drive the SOIL debt-intercept batch: identify SOIL-eligible debtors (rule [SDN-BR-011](../../business-rules.md#sdn-br-011-soil-debtor-eligibility%3A-balance-%E2%89%A5-0%2C-include-on-soil-flag-'y'%2C-no-third-party%2C-worker-id-present)), snapshot reference records, and create/adjust SOIL transactions filtered by truncated create date ([SDN-BR-012](../../business-rules.md#sdn-br-012-soil-transactions-are-selected-by-the-truncated-create-date)). ## Context - Namespace: `SAIF.Data.NHibernate.Repositories` - Assembly/package: `SAIF.Data.NHibernate` (ships inside the `SAIF.SAIFCode.Core` NuGet package) - Base class: `Repository` from `SAIF.Framework.Data.NHibernate` (external, resolved via Legends CLI — see Inherited public members below). - Implements: `ICollectionsRepository`, defined in `SAIF.Data`*. ## Public members | Member | Signature | Purpose | | --- | --- | --- | | CollectionsRepository(NHibernateUnitOfWork) | `public CollectionsRepository(NHibernateUnitOfWork unitOfWork)` | Constructor; passes `unitOfWork` to the `Repository` base. | | GetCasesByReferenceNumber | `public ICollection GetCasesByReferenceNumber(string referenceId)` | Returns the `CaseHeader` rows matching a case/reference number via named query `Collections_GetCasesByReferenceNumber` ([SQL-004](../../sql/SQL-004-collections-repository-get-cases-by-reference-number.md)). | | GetCategoryTotals | `public ICollection GetCategoryTotals(long caseId)` | Returns per-category subtotal/current-balance rows for a case via `Collections_GetCategoryTotals` ([SQL-005](../../sql/SQL-005-collections-repository-get-category-totals.md)). | | GetDebtorName | `public String GetDebtorName(long id)` | Resolves a display name for a debtor id across affiliate/policy/third-party/person/company sources via `Collections_GetDebtorName` ([SQL-006](../../sql/SQL-006-collections-repository-get-debtor-name.md)). | | GetPrimaryDebtorName | `public string GetPrimaryDebtorName(long id)` | Resolves the primary (first-listed) debtor's display name for a case via `Collections_GetPrimaryDebtorName` ([SQL-007](../../sql/SQL-007-collections-repository-get-primary-debtor-name.md)). | | GetDebtorSsn | `public DebtorSsn GetDebtorSsn(long id)` | Returns the debtor's SSN record via `Collections_GetDebtorSSN`; PII-sensitive — see [Security and authorization](../../security-authorization.md) (SC-SDN-03) ([SQL-008](../../sql/SQL-008-collections-repository-get-debtor-ssn.md)). | | GetFraudOtherAddress | `public ICollection GetFraudOtherAddress(long locId)` | Returns contact info for a fraud/other-type debtor location via `Collections_GetFraudOtherAddress` ([SQL-009](../../sql/SQL-009-collections-repository-get-fraud-other-address.md)). | | GetPolicyAddress | `public ICollection GetPolicyAddress(long id)` | Returns contact info for a policy-type debtor via `Collections_GetPolicyAddress` ([SQL-010](../../sql/SQL-010-collections-repository-get-policy-address.md)). | | GetThirdPartyAddress | `public ICollection GetThirdPartyAddress(long thirdPartySeqNum)` | Returns contact info for a third-party debtor via `Collections_GetThirdPartyAddress` ([SQL-011](../../sql/SQL-011-collections-repository-get-third-party-address.md)). | | SearchCollections | `public ICollection SearchCollections(string lastName, string firstName, string ssn, string policyName, string showClosedCases, string uniqueRunId)` | Multi-branch debtor/case search by last/first name, SSN, policy name, and a closed-cases flag via `Collections_SearchCollections` ([SQL-012](../../sql/SQL-012-collections-repository-search-collections.md)); accepts an SSN parameter — see [Security and authorization](../../security-authorization.md) (SC-SDN-03). | | ApplyPayments | `public void ApplyPayments(decimal caseId, DateTime tranDate)` | Executes `WCIS_DBA.PK80010_LEGAL_COLLECTIONS.APPLYPAYMENTS(:inSeqNum, :pmt_date)` via inline SQL and `ExecuteUpdate()`, allocating unapplied payments across cost transactions for the case as of the transaction date ([SDN-BR-010](../../business-rules.md#sdn-br-010-collection-case-payments-are-applied-by-the-oracle-package-applypayments-for-the-case-and-payment-date), [SQL-053](../../sql/SQL-053-collections-repository-apply-payments.md)). | | GetInterest | `public ICollection GetInterest(string caseId, DateTime runDate)` | Returns per-transaction interest amounts for a case as of a run date via `Collections_GetCaseInterest` ([SQL-013](../../sql/SQL-013-collections-repository-get-interest.md)). | | GetMissingPolicyPayments | `public ICollection GetMissingPolicyPayments(DateTime selectDate)` | Returns policy financial transactions not yet posted as collection payments as of a select date via `Collections_GetMissingPolicyPayments` ([SQL-014](../../sql/SQL-014-collections-repository-get-missing-policy-payments.md)). | | GetThirdPartyPayments | `public ICollection GetThirdPartyPayments(DateTime selectDate)` | Returns third-party distribution payments to post as of a select date via `Collections_GetThirdPartyPayments` ([SQL-015](../../sql/SQL-015-collections-repository-get-third-party-payments.md)). | | DebtorsWithoutPayments | `public ICollection DebtorsWithoutPayments(DateTime lastStatementDate)` | Returns debtors who received no payment since a given last-statement date, for follow-up/statement processing via `Collections_getDebtorsWithoutPayments` ([SQL-016](../../sql/SQL-016-collections-repository-debtors-without-payments.md)). | | DebtorsWithExpiredLocs | `public ICollection DebtorsWithExpiredLocs(DateTime selectDate)` | Returns debtors whose letter-of-credit/location record has expired as of a select date via `Collections_getDebtorsWithExpiredLocs` ([SQL-017](../../sql/SQL-017-collections-repository-debtors-with-expired-locs.md)). | | BalanceBroughtToMinAmountTransactions | `public ICollection BalanceBroughtToMinAmountTransactions(DateTime selectDate)` | Returns transactions where the balance was brought to the configured minimum amount as of a select date via `Collections_getBalanceBroughtToMinAmount` ([SQL-018](../../sql/SQL-018-collections-repository-balance-brought-to-min-amount-transactions.md)). | | CreateSoilDebtorReferenceRecords | `public void CreateSoilDebtorReferenceRecords(DateTime runDate, string uniqueRunId)` | Executes `WCIS_DBA.PK80011_LEGALCOL_SOIL.CREATE_REFERENCE_RECORDS(:create_date_in, :run_id)` via inline SQL, snapshotting SOIL-eligible debtors into `SOIL_DEBTOR_REFERENCE` for the run ([SDN-BR-011](../../business-rules.md#sdn-br-011-soil-debtor-eligibility%3A-balance-%E2%89%A5-0%2C-include-on-soil-flag-'y'%2C-no-third-party%2C-worker-id-present), [SQL-002](../../sql/SQL-002-collections-repository-create-soil-debtor-reference-records.md)). | | GetWorkerIdForDebtors | `public ICollection GetWorkerIdForDebtors()` | Returns worker ids for all currently SOIL-eligible debtors (balance ≥ 0, `INCLUDE_ON_SOIL_FLAG='Y'`, no third party, worker id present) via `Collections_getWorkerIdForSoilDebtor` ([SDN-BR-011](../../business-rules.md#sdn-br-011-soil-debtor-eligibility%3A-balance-%E2%89%A5-0%2C-include-on-soil-flag-'y'%2C-no-third-party%2C-worker-id-present), [SQL-019](../../sql/SQL-019-collections-repository-get-worker-id-for-debtors.md)). | | GetWorkerIdForSoilTrans | `public ICollection GetWorkerIdForSoilTrans(DateTime createDate)` | Returns worker ids for SOIL reference records created on the truncated `createDate` via `Collections_getWorkerIdForSoilTrans` ([SDN-BR-012](../../business-rules.md#sdn-br-012-soil-transactions-are-selected-by-the-truncated-create-date), [SQL-020](../../sql/SQL-020-collections-repository-get-worker-id-for-soil-trans.md)). | | CreateSoilAdjustmentRecords | `public void CreateSoilAdjustmentRecords(DateTime runDate, string uniqueRunId)` | Executes `WCIS_DBA.PK80011_LEGALCOL_SOIL.CREATE_SOIL_TRANSACTIONS(:create_date_in, :run_id)` via inline SQL, creating/adjusting/removing DOR (Department of Revenue) intercept balance transactions for the run ([SQL-003](../../sql/SQL-003-collections-repository-create-soil-adjustment-records.md)). | | DebtorLastStatementBalance | `public decimal DebtorLastStatementBalance(DateTime statementDate, long debtorId)` | Returns the debtor's balance as of the given last-statement date via `Collections_GetDebtorLastStatementBalance` ([SQL-021](../../sql/SQL-021-collections-repository-debtor-last-statement-balance.md)). | ## Inherited public members Members in this section are inherited from `Repository` in `SAIF.Framework.Data.NHibernate`. They are available on this type through inheritance. | Member | Signature | Purpose | Origin | | --- | --- | --- | --- | | Add | `public void Add(Case entity)` | Registers a new `Case` entity for insertion on the next unit-of-work commit.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | Update | `public void Update(Case entity)` | Marks an existing `Case` entity as modified for the next unit-of-work commit.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | Delete | `public void Delete(Case entity)` | Registers a `Case` entity for deletion on the next unit-of-work commit.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | Query | `public IQueryable Query()` | Returns a LINQ-queryable session projection over `Case` for building ad hoc filters.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | Get | `public Case Get(object id)` | Loads a `Case` by primary key, returning `null` if not found.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | Load | `public Case Load(object id)` | Loads a `Case` by primary key as a (possibly proxied) reference, throwing if not found.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | | SystemDate | `public DateTime SystemDate { get; }` | Current system date/time as tracked by the underlying session/unit of work.† | `SAIF.Framework.Data.NHibernate` — `RepositoryBase` | ## Behavioral notes - All 21 read/write operations route through `Session.GetNamedQuery()` or `Session.CreateSqlQuery()`, never through the inherited `Repository` CRUD surface; every one is essentially a typed wrapper over an Oracle package procedure/function in `WCIS_DBA.PK80010_LEGAL_COLLECTIONS` or `WCIS_DBA.PK80011_LEGALCOL_SOIL`*. - The three write operations (`ApplyPayments`, `CreateSoilDebtorReferenceRecords`, `CreateSoilAdjustmentRecords`) call `ExecuteUpdate()` directly against a stored procedure and do not go through NHibernate's dirty-checking/flush pipeline, so they take effect immediately regardless of the enclosing unit-of-work's commit/rollback state*. - "SOIL" is inferred to mean the State of Oregon Intercept program from the package name `LEGALCOL_SOIL`; the expansion is not independently verified (`saif-data-nhibernate-analysis.md` open question). ## Constraints and usage notes - `GetDebtorSsn` and `SearchCollections` expose SSN data with no access check at this layer; callers must restrict access themselves (SC-SDN-03, [Security and authorization](../../security-authorization.md)). - All parameters are bound via `SetParameter`/`SetDecimal`/`SetDateTime`, so none of these calls are subject to SQL injection from caller-supplied strings (SC-SDN-05, [Security and authorization](../../security-authorization.md)). > **Note:** Statements marked with `†` are derived from dependency library > documentation via the Legends CLI. Statements marked with `*` were inferred > from code structure or contextual evidence. Statements marked with `**` could > not be confirmed; treat as unknown until verified. ## Related documentation - [Index](../../index.md) - [Overview](../../overview.md) - [Architecture](../../architecture.md) - [Dependencies](../../dependencies.md) - [Operations and processes](../../operations-processes.md) - [Business rules](../../business-rules.md) - [Database tables](../../database-tables.md) - [SQL catalog](../../sql/index.md) - [Procedures](../../procedures/index.md) - [Security and authorization](../../security-authorization.md) - [API reference](../index.md) ## Sources - `Data/SAIF.Data.NHibernate/Repositories/CollectionsRepository.cs`