SQL-041: PolicyOutputDocumentRepository.GetReleasedAgencyViewablePolicyOutputDocuments

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

Classification

Field	Value
SQL ID	SQL-041
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	GetReleasedAgencyViewablePolicyOutputDocuments

Parameters

Parameter	Direction	Type	Max length	Description
:policyNumber	Input	long	N/A	Method argument bound into the LINQ expression (statements 1 and 2)
:documentCategory	Input	string	N/A	Method argument bound into the LINQ expression (second overload only)

Tables

Table	Access	Columns used
POLICY	SELECT (GetAccountNumber, separate statement)	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
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 (LEFT OUTER, fetch)	output_document_seq_num, update_num, image_seq_num, document_create_date, description, create_user, create_date, update_user, update_date, output_document_type_code
output_document_type_code	JOIN (LEFT OUTER, fetch)	code, code_descr, agency_view_flag, agency_inbox_flag, manual_add_flag, freeform_flag, sort_priority_num, SOURCE_SYSTEM_CODE, PACKAGE_NAME, image_file_code, image_category_code, image_document_type_code, output_document_category_code, Direct_Write_Notify_Text_Code
POLICY	JOIN (LEFT OUTER, fetch; two aliases — P = Policy, P2 = Policy.RelationshipPolicy)	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 (both aliases)
SUB_POLICY	JOIN (LEFT OUTER, collection fetch)	Sub_Policy_Seq_Num, SUBMISSION_STATUS_CODE, POLICY_NUM

LINQ statement

Source: Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:68 (single-argument overload) and :73 (with documentCategory). The shared query is built at :152 (ReleasedAgencyViewablePolicyOutputDocumentQuery), :164 (GetAccountNumber), and :27 (EagerLoadQuery).

public IEnumerable<PolicyOutputDocument> GetReleasedAgencyViewablePolicyOutputDocuments(long policyNumber)
{
    return ReleasedAgencyViewablePolicyOutputDocumentQuery(policyNumber).ToList();
}

public IEnumerable<PolicyOutputDocument> GetReleasedAgencyViewablePolicyOutputDocuments(long policyNumber, string documentCategory)
{
    return ReleasedAgencyViewablePolicyOutputDocumentQuery(policyNumber).ForCategory(documentCategory).ToList();
}

private IQueryable<PolicyOutputDocument> ReleasedAgencyViewablePolicyOutputDocumentQuery(long policyNumber)
{

    var accountNumber = GetAccountNumber(policyNumber);
    return EagerLoadQuery().AgencyViewable().Released().ForPolicyOrAccount(policyNumber, accountNumber);
}

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

private IQueryable<PolicyOutputDocument> EagerLoadQuery()
{
    return Query()
            .Fetch(p => p.OutputDocument)
            .ThenFetch(o => o.DocumentType)
            .Fetch(p => p.Policy)
            .ThenFetch(p => p.SubmissionPolicies)
            .Fetch(p => p.Policy)
            .ThenFetch(p => p.RelationshipPolicy);
}

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

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

public static IQueryable<PolicyOutputDocument> ForCategory(this IQueryable<PolicyOutputDocument> query, string documentCategory)
{
    if (documentCategory.IsNullOrWhiteSpace() || documentCategory == "ALL")
        return query;

    return query.Where(p => p.OutputDocument.DocumentType.DocumentCategory.Code == documentCategory);
}

public static IQueryable<PolicyOutputDocument> ForPolicyOrAccount(this IQueryable<PolicyOutputDocument> query,
    long policyNumber, string accountNumber)
{

    var policyDocs = query.Where(p => p.Policy.PolicyNumber == policyNumber);

    var accountDocs = query.Where(x => x.AccountNumber == accountNumber)
                           .Where(y => (y.Policy.PolicyNumber == y.Policy.RelationshipPolicy.PolicyNumber && y.Policy.Relationship.Code.IsIn(RelationshipTypes)) 
                                    || (y.Policy.Relationship.Code == null) 
                                    || (!y.Policy.Relationship.Code.IsIn(RelationshipTypes)));

    //if (accountNumber.IsNullOrEmpty() || accountDocs.IsNull())
        return policyDocs;

    //var result = policyDocs.Concat(accountDocs);
    //return result;
}

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
-- 1. GetAccountNumber(policyNumber) (same statement as SQL-047)
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

-- 2. Main query
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,
  OD.output_document_seq_num,
  OD.update_num,
  OD.image_seq_num,
  OD.document_create_date,
  OD.description,
  OD.create_user,
  OD.create_date,
  OD.update_user,
  OD.update_date,
  OD.output_document_type_code,
  ODTC.code,
  ODTC.code_descr,
  ODTC.agency_view_flag,
  ODTC.agency_inbox_flag,
  ODTC.manual_add_flag,
  ODTC.freeform_flag,
  ODTC.sort_priority_num,
  ODTC.SOURCE_SYSTEM_CODE,
  ODTC.PACKAGE_NAME,
  ODTC.image_file_code,
  ODTC.image_category_code,
  ODTC.image_document_type_code,
  ODTC.output_document_category_code,
  ODTC.Direct_Write_Notify_Text_Code,
  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,
  SP.Sub_Policy_Seq_Num,
  SP.SUBMISSION_STATUS_CODE,
  SP.POLICY_NUM,
  P2.POLICY_NUM,
  P2.POLICY_NAME,
  P2.LEGAL_NAME,
  P2.SECURE_POLICY_CODE,
  P2.OFFICE_CODE,
  P2.ACCOUNT_NUM,
  P2.POLICY_STATUS_CODE,
  P2.MARKET_TYPE_CODE,
  P2.POLICY_RELATIONSHIP_POLICY_NUM,
  P2.POLICY_RELATIONSHIP_CODE,
  P2.customer_service_team_code,
  P2.ENTITY_CODE,
  P2.TRADING_SEQ_NUM,
  P2.RATE_ACCOUNT,
  P2.AGENT_NUM
FROM policy_output_document POD
  LEFT OUTER JOIN output_document OD
    ON OD.output_document_seq_num = POD.output_document_seq_num
  LEFT OUTER JOIN output_document_type_code ODTC
    ON ODTC.code = OD.output_document_type_code
  LEFT OUTER JOIN POLICY P
    ON P.POLICY_NUM = POD.policy_num
  LEFT OUTER JOIN SUB_POLICY SP
    ON SP.POLICY_NUM = P.POLICY_NUM
  LEFT OUTER JOIN POLICY P2
    ON P2.POLICY_NUM = P.POLICY_RELATIONSHIP_POLICY_NUM
WHERE ODTC.agency_view_flag = 'Y'
  AND POD.document_status_code = 'RELE'
  AND POD.policy_num = :policyNumber
  AND ODTC.output_document_category_code = :documentCategory -- overload (policyNumber, documentCategory) only; omitted when documentCategory is null/whitespace or 'ALL'

Translation notes: - Statement 1 is GetAccountNumber (SQL-047) and runs eagerly before the main query is built. Its result is passed to ForPolicyOrAccount, which builds accountDocs but returns only policyDocs (the Concat is commented out). So accountNumber has no effect on the SQL, and the account-number branch (ACCOUNT_NUM, Policy.Relationship.Code IN (...)) is never executed. p.Policy.PolicyNumber == policyNumber short-circuits to the FK POD.policy_num. - IsAgencyViewable is a YesNoType flag, so the boolean member test becomes = 'Y'. - Extension resolution: Released, AgencyViewable, and ForCategory are defined in the repository file's own extensions class; ForPolicyOrAccount is defined in Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs and imported by using. The bodies are equivalent, so the SQL is the same either way. - EagerLoadQuery() contributes the same five LEFT OUTER JOINs described on SQL-036. The second .Fetch(p => p.Policy) reuses the same POLICY P join. - ThenFetch(p => p.SubmissionPolicies) fetches a collection: without Distinct(), the materialized list can contain the same PolicyOutputDocument more than once per matching SUB_POLICY row. - Where-clause navigation through OutputDocument / OutputDocument.DocumentType is written against the fetch aliases OD / ODTC. NHibernate 4.0 may emit a separate implicit (theta-style inner) join alias for the Where navigation in addition to the fetch join; the filtering meaning is the same. - 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. ReleasedDocumentStatusCode is inlined as the literal 'RELE'. - OutputDocumentTypeCode.ImageDocumentTypes (HasManyToMany via output_doc_image_type_xref) is lazy and never fetched or filtered, so output_doc_image_type_xref is not accessed. ODTC.image_* columns are selected only as the References(ImageDocumentType) FK; image_document_type_code is not joined. - ...DocumentType.DocumentCategory.Code is the identifier of OutputDocumentCategoryCode, so it short-circuits to ODTC.output_document_category_code; output_document_category_code is never joined. - POLICY.OFFICE_CODE is mapped twice (Map(OfficeCode) and References(Office)) and is selected once. Policy HasMany collections other than SubmissionPolicies load lazily and are not selected. - OutputDocument.PolicyOutputDocument (HasOne(...).Cascade.All()) is the inverse side of the same shared-PK one-to-one; the row is already in the session, so no additional statement is fabricated.

Operations and processes

• Policy output documents

Business rules

• SDN-BR-061
• SDN-BR-062

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

Sources

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