---
title: "SQL catalog"
summary: Index of all SQL statements used by SAIF.Data.NHibernate, grouped by operation type
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data.NHibernate
reference_type: package
---


# SAIF.Data.NHibernate SQL catalog

This page indexes all 55 SQL statements, ORM-generated queries, and stored procedure calls in this library, grouped by CRUD type. This library mixes NHibernate ORM statements with concrete inline SQL and stored-procedure calls. For ORM statements, the page separates the verbatim C# ORM/LINQ expression, in its `## LINQ statement` section, from the inferred SQL fabricated from that expression and the NHibernate mappings, in its `## Sample SQL` section — the inferred SQL is not concrete SQL captured from source. For concrete and stored-procedure statements, the `## Sample SQL` section is the literal SQL text found in source, and no `## LINQ statement` section is present.

## Create operations

| SQL ID | Description | Type | Tables | Business object |
| --- | --- | --- | --- | --- |
| [SQL-001](SQL-001-claim-score-repository-get-claim-score-category.md) | Get claim score category | Stored Proc | `ANALYTICS_DB.dbo.CSG_CLM_SGMNTN_REQUEST_CLAIM`, `EIS_PROD.dbo.WCS_POLICY`, `EIS_STAGING.dbo.BDS_CTL_MESSAGES`, `result set from dbo.bp_clm_segmntn_complexity_score_dtl**` | `ClaimScoreRepository` |
| [SQL-002](SQL-002-collections-repository-create-soil-debtor-reference-records.md) | Create soil debtor reference records | Stored Proc | `SOIL_DEBTOR_REFERENCE`, `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `CLM_DBA.TEMP_DATA_PERSON_ADDRESS`, `THRDPARTY`, `PERSON` | `CollectionsRepository` |
| [SQL-003](SQL-003-collections-repository-create-soil-adjustment-records.md) | Create soil adjustment records | Stored Proc | `DEPT_REVENUE_SOIL_ADJUSTMENT`, `LEGAL_COLLECTION_DEBTOR`, `SOIL_DEBTOR_REFERENCE`, `LEGAL_COLLECTION`, `AFFILIATE`, `CLM_DBA.TEMP_DATA_PERSON_ADDRESS`, `THRDPARTY`, `PERSON`, `DEPT_REVENUE_SOIL_BALANCE` | `CollectionsRepository` |
| [SQL-053](SQL-053-collections-repository-apply-payments.md) | Apply payments | Stored Proc | `LEGAL_COLLECTION_TRANSACTION`, `LEGAL_COLLECTION_TRAN_CODE`, `LC_TRAN_CATEGORY_CODE`, `LEGAL_COLLECTION_PAYMENT_REF` | `CollectionsRepository` |

## Read operations

| SQL ID | Description | Type | Tables | Business object |
| --- | --- | --- | --- | --- |
| [SQL-004](SQL-004-collections-repository-get-cases-by-reference-number.md) | Get cases by reference number | Stored Proc | `LEGAL_COLLECTION`, `LEGAL_COLLECTION_CODE`, `POLICY` | `CollectionsRepository` |
| [SQL-005](SQL-005-collections-repository-get-category-totals.md) | Get category totals | Stored Proc | `LEGAL_COLLECTION_TRANSACTION`, `LEGAL_COLLECTION`, `LC_TRAN_CATEGORY_CODE`, `LEGAL_COLLECTION_TRAN_CODE` | `CollectionsRepository` |
| [SQL-006](SQL-006-collections-repository-get-debtor-name.md) | Get debtor name | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `AFFILIATE`, `POLICY`, `THRDPARTY`, `PERSON`, `THRDPARTY_FIRM`, `COMPANY` | `CollectionsRepository` |
| [SQL-007](SQL-007-collections-repository-get-primary-debtor-name.md) | Get primary debtor name | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `AFFILIATE`, `POLICY`, `THRDPARTY`, `PERSON`, `THRDPARTY_FIRM`, `COMPANY` | `CollectionsRepository` |
| [SQL-008](SQL-008-collections-repository-get-debtor-ssn.md) | Get debtor ssn | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `AFFILIATE`, `THRDPARTY`, `PERSON` | `CollectionsRepository` |
| [SQL-009](SQL-009-collections-repository-get-fraud-other-address.md) | Get fraud other address | Stored Proc | `LOCATION_VIEW` | `CollectionsRepository` |
| [SQL-010](SQL-010-collections-repository-get-policy-address.md) | Get policy address | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `LOCATION` | `CollectionsRepository` |
| [SQL-011](SQL-011-collections-repository-get-third-party-address.md) | Get third party address | Stored Proc | `THRDPARTY`, `PERSON_ADDRESS`, `ADDRESS`, `THRDPARTY_FIRM`, `COMPANY_ADDRESS`, `PHONE`, `PERSON_PHONE` | `CollectionsRepository` |
| [SQL-012](SQL-012-collections-repository-search-collections.md) | Search collections | Stored Proc | `LEGAL_COLLECTION`, `LEGAL_COLLECTION_DEBTOR`, `CLM_DBA.TEMP_DATA_PERSON_ADDRESS`, `AFFILIATE`, `THRDPARTY`, `THRDPARTY_FIRM`, `PERSON`, `COMPANY`, `POLICY`, `POLICY_SEARCH`, `MARKET_TYPE_CODE` | `CollectionsRepository` |
| [SQL-013](SQL-013-collections-repository-get-interest.md) | Get interest | Stored Proc | `LEGAL_COLLECTION`, `LEGAL_COLLECTION_TRANSACTION` | `CollectionsRepository` |
| [SQL-014](SQL-014-collections-repository-get-missing-policy-payments.md) | Get missing policy payments | Stored Proc | `POLICY_FINANCIAL_TRANS`, `LEGAL_COLLECTION`, `LEGAL_COLLECTION_TRANSACTION` | `CollectionsRepository` |
| [SQL-015](SQL-015-collections-repository-get-third-party-payments.md) | Get third party payments | Stored Proc | `THRDPARTY_DISTRIBUTION`, `THRDPARTY`, `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION` | `CollectionsRepository` |
| [SQL-016](SQL-016-collections-repository-debtors-without-payments.md) | Debtors without payments | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `LEGAL_COLLECTION_TRANSACTION` | `CollectionsRepository` |
| [SQL-017](SQL-017-collections-repository-debtors-with-expired-locs.md) | Debtors with expired locs | Stored Proc | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION`, `LOCATION` | `CollectionsRepository` |
| [SQL-018](SQL-018-collections-repository-balance-brought-to-min-amount-transactions.md) | Balance brought to min amount transactions | Stored Proc | `LEGAL_COLLECTION`, `LEGAL_COLLECTION_TRANSACTION` | `CollectionsRepository` |
| [SQL-019](SQL-019-collections-repository-get-worker-id-for-debtors.md) | Get worker id for debtors | Inline | `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION` | `CollectionsRepository` |
| [SQL-020](SQL-020-collections-repository-get-worker-id-for-soil-trans.md) | Get worker id for soil trans | Inline | `SOIL_DEBTOR_REFERENCE`, `LEGAL_COLLECTION`, `LEGAL_COLLECTION_DEBTOR` | `CollectionsRepository` |
| [SQL-021](SQL-021-collections-repository-debtor-last-statement-balance.md) | Debtor last statement balance | Stored Proc | `LEGAL_COLLECTION_TRANSACTION`, `LEGAL_COLLECTION_DEBTOR`, `LEGAL_COLLECTION_TRAN_CODE` | `CollectionsRepository` |
| [SQL-022](SQL-022-group-image-repository-image-found.md) | Image found | LINQ (inferred SQL) | `Image_Group` | `GroupImageRepository` |
| [SQL-023](SQL-023-group-image-repository-get-image-group.md) | Get image group | LINQ (inferred SQL) | `Image_Group` | `GroupImageRepository` |
| [SQL-024](SQL-024-group-image-repository-get-image-group-or-default.md) | Get image group or default | LINQ (inferred SQL) | `Image_Group` | `GroupImageRepository` |
| [SQL-025](SQL-025-group-image-repository-get-filtered-image-groups.md) | Get filtered image groups | LINQ (inferred SQL) | `Image_Group`, `group_association`, `image` | `GroupImageRepository` |
| [SQL-026](SQL-026-group-repository-group-found.md) | Group found | LINQ (inferred SQL) | `group_association` | `GroupRepository` |
| [SQL-027](SQL-027-group-repository-get-group-or-default.md) | Get group or default | LINQ (inferred SQL) | `group_association` | `GroupRepository` |
| [SQL-028](SQL-028-group-repository-get-group.md) | Get group | LINQ (inferred SQL) | `group_association` | `GroupRepository` |
| [SQL-029](SQL-029-image-category-code-repository-image-document-type-found.md) | Image document type found | LINQ (inferred SQL) | `image_category_code`, `image_document_type_code` | `ImageCategoryCodeRepository` |
| [SQL-030](SQL-030-image-category-code-repository-get-document-type-or-default.md) | Get document type or default | LINQ (inferred SQL) | `image_category_code`, `image_document_type_code` | `ImageCategoryCodeRepository` |
| [SQL-031](SQL-031-image-category-code-repository-get-image-category-codes.md) | Get image category codes | LINQ (inferred SQL) | `image_category_code` | `ImageCategoryCodeRepository` |
| [SQL-032](SQL-032-investigation-repository-extensions-get-subject-name.md) | Get subject name | Inline | `INVESTIGATION`, `SUBJECT`, `PERSON`, `COMPANY`, `PROVIDER_LOCATION`, `POLICY` | `InvestigationRepositoryExtensions` |
| [SQL-033](SQL-033-investigation-repository-extensions-get-claim-id.md) | Get claim id | Inline | `INVESTIGATION` | `InvestigationRepositoryExtensions` |
| [SQL-034](SQL-034-missing-time-loss-repository-get-missing-time-loss-payments.md) | Get missing time loss payments | Stored Proc | `Unknown** (member absent from the retrieved package)` | `MissingTimeLossRepository` |
| [SQL-035](SQL-035-policies-repository-get-policy-standard-premium.md) | Get policy standard premium | Stored Proc | `DUAL`, `internals delegated to pk73059_prem_hist_summary (not retrieved**)` | `PoliciesRepository` |
| [SQL-036](SQL-036-policy-output-document-repository-get-un-released-documents-prior-to-date.md) | Get un released documents prior to date | LINQ (inferred SQL) | `policy_output_document`, `output_document`, `output_document_type_code`, `POLICY`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-037](SQL-037-policy-output-document-repository-get-policy-output-document.md) | Get policy output document | ORM (IRepository.Get) | `policy_output_document` | `PolicyOutputDocumentRepository` |
| [SQL-038](SQL-038-policy-output-document-repository-get-policy-output-documents-by-source-key-text.md) | Get policy output documents by source key text | LINQ (inferred SQL) | `policy_output_document`, `output_document`, `output_document_type_code`, `POLICY`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-039](SQL-039-policy-output-document-repository-get-policy-output-documents-by-image-id.md) | Get policy output documents by image id | LINQ (inferred SQL) | `policy_output_document`, `output_document`, `output_document_type_code`, `POLICY`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-040](SQL-040-policy-output-document-repository-get-held-documents-for-notification.md) | Get held documents for notification | LINQ (inferred SQL) | `policy_output_document` | `PolicyOutputDocumentRepository` |
| [SQL-041](SQL-041-policy-output-document-repository-get-released-agency-viewable-policy-output-documents.md) | Get released agency viewable policy output documents | LINQ (inferred SQL) | `POLICY`, `policy_output_document`, `output_document`, `output_document_type_code`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-042](SQL-042-policy-output-document-repository-get-held-and-pending-agency-viewable-policy-output-documents.md) | Get held and pending agency viewable policy output documents | LINQ (inferred SQL) | `POLICY`, `policy_output_document`, `output_document`, `output_document_type_code`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-043](SQL-043-policy-output-document-repository-get-policy-output-documents-for-inbox.md) | Get policy output documents for inbox | LINQ (inferred SQL) | `policy_output_document`, `output_document`, `output_document_type_code`, `POLICY`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-044](SQL-044-policy-output-document-repository-get-policy-output-documents.md) | Get policy output documents | LINQ (inferred SQL) | `POLICY`, `policy_output_document`, `output_document`, `output_document_type_code`, `SUB_POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-045](SQL-045-policy-output-document-repository-check-document-exist.md) | Check document exist | LINQ (inferred SQL) | `policy_output_document`, `output_document` | `PolicyOutputDocumentRepository` |
| [SQL-046](SQL-046-policy-output-document-repository-get-released-agency-viewable-child-output-documents.md) | Get released agency viewable child output documents | LINQ (inferred SQL) | `policy_output_document`, `output_document`, `output_document_type_code`, `POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-047](SQL-047-policy-output-document-repository-get-account-number.md) | Get account number | LINQ (inferred SQL) | `POLICY` | `PolicyOutputDocumentRepository` |
| [SQL-048](SQL-048-providers-repository-get-provider-by-tax-id-batch.md) | Get provider by tax id batch | LINQ (inferred SQL) | `PROVIDER` | `ProvidersRepository` |
| [SQL-049](SQL-049-providers-repository-get-provider-addresses.md) | Get provider addresses | LINQ (inferred SQL) | `PROVIDER_ADDRESS`, `PROVIDER_LOCATION` | `ProvidersRepository` |

## Update operations

| SQL ID | Description | Type | Tables | Business object |
| --- | --- | --- | --- | --- |
| [SQL-050](SQL-050-get-next-bill-from-queue-unused.md) | Legacy `getNextBillFromQueue` definition; not called by any repository method (unused) | Stored Proc | `Unknown** (PK82039_BILLENTRY_TASK_PROCESS not-found-by-cli)` | `ClaimBillEntry.hbm.xml (no caller)` |
| [SQL-051](SQL-051-restore-bill-to-queue-unused.md) | Legacy `restoreBillToQueue` definition; not called by any repository method (unused) | Stored Proc | `Unknown** (PK82039_BILLENTRY_TASK_PROCESS not-found-by-cli)` | `ClaimBillEntry.hbm.xml (no caller)` |
| [SQL-052](SQL-052-applypayments-unused.md) | Legacy `APPLYPAYMENTS` definition; not called by any repository method (unused) | Stored Proc | `LEGAL_COLLECTION_TRANSACTION`, `LEGAL_COLLECTION_TRAN_CODE`, `LC_TRAN_CATEGORY_CODE`, `LEGAL_COLLECTION_PAYMENT_REF` | `Collections.hbm.xml (no caller)` |
| [SQL-054](SQL-054-group-image-repository-delete.md) | Delete | LINQ (inferred SQL) | `Image_Group`, `image` | `GroupImageRepository` |
| [SQL-055](SQL-055-policy-period-payroll-audit-repository-track-in-report.md) | Track in report | Stored Proc | `Remote: wcis_dba.pk73101_image.track_in_report@POLDB (tables not visible**)` | `PolicyPeriodPayrollAuditRepository` |

## 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)
- [Security and authorization](../security-authorization.md)
- [Database tables](../database-tables.md)
- [Procedures](../procedures/index.md)
- [API reference](../api/index.md)

## Sources
- `Data/SAIF.Data.NHibernate/Mappings/**`