SQL-047: PolicyOutputDocumentRepository.GetAccountNumber

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by PolicyOutputDocumentRepository.GetAccountNumber.

Classification

Field	Value
SQL ID	SQL-047
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	WCIS_DBA* (package owner; unqualified inside package); [unqualified]**
Command type	N/A (ORM-generated)
Business object	PolicyOutputDocumentRepository
Criteria property	N/A — ORM
Lifecycle method	GetAccountNumber

Parameters

Parameter	Direction	Type	Max length	Description
:policyNumber	Input	long	N/A	Method argument bound into the LINQ expression

Tables

Table	Access	Columns used
POLICY	SELECT	POLICY_NUM, POLICY_NAME, LEGAL_NAME, SECURE_POLICY_CODE, OFFICE_CODE, ACCOUNT_NUM, POLICY_STATUS_CODE, MARKET_TYPE_CODE, POLICY_RELATIONSHIP_POLICY_NUM, POLICY_RELATIONSHIP_CODE, customer_service_team_code, ENTITY_CODE, TRADING_SEQ_NUM, RATE_ACCOUNT, AGENT_NUM

LINQ statement

Source: Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:164.

private string GetAccountNumber(long policyNumber)
{
    return _uow.GetRepository<SAIF.Data.Model.Policies.Policy>().Query()
        .FirstOrDefault(p => p.PolicyNumber == policyNumber)?.AccountNumber;
}

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT *
FROM (
  SELECT
    P.POLICY_NUM,
    P.POLICY_NAME,
    P.LEGAL_NAME,
    P.SECURE_POLICY_CODE,
    P.OFFICE_CODE,
    P.ACCOUNT_NUM,
    P.POLICY_STATUS_CODE,
    P.MARKET_TYPE_CODE,
    P.POLICY_RELATIONSHIP_POLICY_NUM,
    P.POLICY_RELATIONSHIP_CODE,
    P.customer_service_team_code,
    P.ENTITY_CODE,
    P.TRADING_SEQ_NUM,
    P.RATE_ACCOUNT,
    P.AGENT_NUM
  FROM POLICY P
  WHERE P.POLICY_NUM = :policyNumber
)
WHERE ROWNUM <= 1

Translation notes: - FirstOrDefault on the Oracle10 dialect is a ROWNUM <= 1 wrapper. ?.AccountNumber is read in memory from the materialized Policy entity, and ACCOUNT_NUM is selected as part of the full entity. - This private helper is used by SQL-041, SQL-042, and SQL-044 as a separate first statement. Its return value does not affect their SQL — ForPolicyOrAccount builds an accountDocs query but returns only policyDocs (the Concat is commented out). - The repository is obtained through _uow.GetRepository<Policy>(), so it runs on the same Oracle unit of work as PolicyOutputDocumentRepository. - All Policy reference navigations (status, market type, office, entity, agent, rate account, trading-as, CSR team, relationship) are lazy References; only their FK columns are selected here, and none is joined. The HasMany collections are not loaded.

Operations and processes

• Policy output documents

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

• Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:164