SQL-046: PolicyOutputDocumentRepository.GetReleasedAgencyViewableChildOutputDocuments

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

Classification

Field	Value
SQL ID	SQL-046
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	GetReleasedAgencyViewableChildOutputDocuments

Parameters

Parameter	Direction	Type	Max length	Description
:parentPolicyNumber	Input	long	N/A	doc.Policy.RelationshipPolicy.PolicyNumber (client-side)
:releaseDate	Input	DateTime	N/A	doc.ReleaseDate.Value
:documentDate	Input	DateTime	N/A	doc.OutputDocument.DocumentDate

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
output_document	JOIN (inner, Where navigation)	output_document_seq_num, output_document_type_code, document_create_date
output_document_type_code	JOIN (inner, Where navigation)	code, agency_view_flag
POLICY	JOIN (inner, Where navigation)	POLICY_NUM, POLICY_RELATIONSHIP_POLICY_NUM

LINQ statement

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

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0")]
public IEnumerable<PolicyOutputDocument> GetReleasedAgencyViewableChildOutputDocuments(PolicyOutputDocument doc)
{
    if (doc.ReleaseDate.HasValue)
    {
        return Query()
            .AgencyViewable()
            .ForParentPolicy(doc.Policy.RelationshipPolicy.PolicyNumber)
            .Released()
            .ForReleaseDate(doc.ReleaseDate.Value)
            .ForDocumentDate(doc.OutputDocument.DocumentDate);
    }
    else
    {
        throw new Exception("Document does not is released!");
    }
}

public static IQueryable<PolicyOutputDocument> AgencyViewable(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.OutputDocument.DocumentType.IsAgencyViewable);
}

public static IQueryable<PolicyOutputDocument> ForParentPolicy(this IQueryable<PolicyOutputDocument> query, long parentPolicyNumber)
{
    return query.Where(p => p.Policy.RelationshipPolicy.PolicyNumber == parentPolicyNumber && p.Policy.PolicyNumber != p.Policy.RelationshipPolicy.PolicyNumber);
}

public static IQueryable<PolicyOutputDocument> Released(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.DocumentStatus.Code == DocumentStatusCode.ReleasedDocumentStatusCode);
}

public static IQueryable<PolicyOutputDocument> ForReleaseDate(this IQueryable<PolicyOutputDocument> query, DateTime releaseDate)
{
    return query.Where(p => p.ReleaseDate == releaseDate);
}

public static IQueryable<PolicyOutputDocument> ForDocumentDate(this IQueryable<PolicyOutputDocument> query, DateTime documentDate)
{
    return query.Where(p => p.OutputDocument.DocumentDate == documentDate);
}

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
  INNER JOIN output_document OD
    ON OD.output_document_seq_num = POD.output_document_seq_num
  INNER JOIN output_document_type_code ODTC
    ON ODTC.code = OD.output_document_type_code
  INNER JOIN POLICY P
    ON P.POLICY_NUM = POD.policy_num
WHERE ODTC.agency_view_flag = 'Y'
  AND P.POLICY_RELATIONSHIP_POLICY_NUM = :parentPolicyNumber
  AND POD.policy_num <> P.POLICY_RELATIONSHIP_POLICY_NUM
  AND POD.document_status_code = 'RELE'
  AND POD.release_date = :releaseDate
  AND OD.document_create_date = :documentDate

Translation notes: - When doc.ReleaseDate has no value, the method throws System.Exception("Document does not is released!") and no SQL is issued. - Deferred execution: the method returns the composed IQueryable without materializing it. The SQL runs when the caller enumerates it. - p.Policy.PolicyNumber short-circuits to POD.policy_num. p.Policy.RelationshipPolicy.PolicyNumber needs the POLICY row of p.Policy, which gives the join to P, and then short-circuits to P.POLICY_RELATIONSHIP_POLICY_NUM — there is no second POLICY alias for RelationshipPolicy. NHibernate may expand != between two nullable columns with extra IS NULL guards; that does not change the result, because the preceding equality already requires a non-null relationship policy number. - IsAgencyViewable is a YesNoType flag, so the boolean member test becomes = 'Y'. - Query() is used, not EagerLoadQuery(), so there is no fetch and SUB_POLICY is never touched. - document_status_code and output_doc_image_type_xref are never joined (same short-circuit/lazy-collection reasoning as SQL-036).

Operations and processes

• Policy output documents

Business rules

• SDN-BR-063

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

Sources

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