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

Update operations

SQL ID	Description	Type	Tables	Business object
SQL-050	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	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	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	Delete	LINQ (inferred SQL)	Image_Group, image	GroupImageRepository
SQL-055	Track in report	Stored Proc	Remote: wcis_dba.pk73101_image.track_in_report@POLDB (tables not visible**)	PolicyPeriodPayrollAuditRepository

Related documentation

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• Security and authorization
• Database tables
• Procedures
• API reference

Sources

• Data/SAIF.Data.NHibernate/Mappings/**