SQL-040: PolicyOutputDocumentRepository.GetHeldDocumentsForNotification

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

Classification

Field	Value
SQL ID	SQL-040
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	PolicyOutputDocumentRepository
Criteria property	N/A — ORM
Lifecycle method	GetHeldDocumentsForNotification

Tables

Table	Access	Columns used
policy_output_document	SELECT	output_document_seq_num, update_num, policy_period_eff_date, agency_seq_num, agent_num, agency_viewed_flag, agency_delivery_flag, agency_delete_date, create_user, create_date, update_user, update_date, release_date, hold_task_seq_num, task_user, source_key_text, ACCOUNT_NUM, policy_num, document_status_code

LINQ statement

Source: Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:63. The Held() extension is applied at Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:194.

public IEnumerable<PolicyOutputDocument> GetHeldDocumentsForNotification()
{
    return Query().Held().Where(x => x.HoldTaskId == null && x.TaskUser != null).ToList();
}

public static IQueryable<PolicyOutputDocument> Held(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(x => x.DocumentStatus.Code == DocumentStatusCode.HeldDocumentStatusCode);
}

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
  POD.output_document_seq_num,
  POD.update_num,
  POD.policy_period_eff_date,
  POD.agency_seq_num,
  POD.agent_num,
  POD.agency_viewed_flag,
  POD.agency_delivery_flag,
  POD.agency_delete_date,
  POD.create_user,
  POD.create_date,
  POD.update_user,
  POD.update_date,
  POD.release_date,
  POD.hold_task_seq_num,
  POD.task_user,
  POD.source_key_text,
  POD.ACCOUNT_NUM,
  POD.policy_num,
  POD.document_status_code
FROM policy_output_document POD
WHERE POD.document_status_code = 'HELD'
  AND POD.hold_task_seq_num IS NULL
  AND POD.task_user IS NOT NULL

Translation notes: - Query() is used, not EagerLoadQuery(), so there are no fetch joins; every predicate is on policy_output_document columns and output_document, output_document_type_code, POLICY, and SUB_POLICY are not accessed by this statement. - p.DocumentStatus.Code is the identifier of DocumentStatusCode, so it short-circuits to the FK POD.document_status_code; document_status_code is never joined. HeldDocumentStatusCode is inlined as the literal 'HELD'. - == null / != null become IS NULL / IS NOT NULL. - Lazy OutputDocument / Policy proxies that callers touch later load in separate by-id statements, which are not fabricated here.

Operations and processes

• Policy output documents

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

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