SQL-043: PolicyOutputDocumentRepository.GetPolicyOutputDocumentsForInbox

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

Classification

Field	Value
SQL ID	SQL-043
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	GetPolicyOutputDocumentsForInbox

Parameters

Parameter	Direction	Type	Max length	Description
:releaseDate	Input	DateTime	N/A	Method argument bound into the LINQ expression
:agencyId	Input	long	N/A	Method argument bound into the LINQ expression
:producerId	Input	long	N/A	Method argument bound into the LINQ expression (second overload only)

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 (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:84 (three-argument overload) and :89 (with producerId). Both call ReleasedAgencyInboxViewableQuery (:94), which composes ReleasedAgencyViewablePolicyOutputDocumentQuery() (:159) and EagerLoadQuery() (:27).

public IEnumerable<PolicyOutputDocument> GetPolicyOutputDocumentsForInbox(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId)
{
    return ReleasedAgencyInboxViewableQuery(showOnlyDocuments, releaseDate, agencyId).ToList();
}

public IEnumerable<PolicyOutputDocument> GetPolicyOutputDocumentsForInbox(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId, long producerId)
{
    return ReleasedAgencyInboxViewableQuery(showOnlyDocuments, releaseDate, agencyId).ForProducer(producerId).ToList();
}

private IQueryable<PolicyOutputDocument> ReleasedAgencyInboxViewableQuery(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId)
{
    var query = ReleasedAgencyViewablePolicyOutputDocumentQuery().AgencyInboxViewable().ReleasedOnOrAfter(releaseDate).ForAgency(agencyId);

    switch (showOnlyDocuments)
    {
        case ShowOnlyDocuments.Unread:
            query = query.NonViewed().NotAgencyDeleted();
            break;
        case ShowOnlyDocuments.All:
            query = query.NotAgencyDeleted();
            break;
        case ShowOnlyDocuments.Deleted:
            query = query.AgencyDeleted();
            break;
        default:
            query = query.NonViewed().NotAgencyDeleted();
            break;
    }

    return query;
}

private IQueryable<PolicyOutputDocument> ReleasedAgencyViewablePolicyOutputDocumentQuery()
{
    return EagerLoadQuery().AgencyViewable().Released();
}

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> AgencyInboxViewable(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.OutputDocument.DocumentType.IsAgencyInboxViewable);
}

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

public static IQueryable<PolicyOutputDocument> ForAgency(this IQueryable<PolicyOutputDocument> query, long agencyId)
{
    return query.Where(p => p.AgencyId == agencyId);
}

public static IQueryable<PolicyOutputDocument> NonViewed(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyViewed == false);
}

public static IQueryable<PolicyOutputDocument> NotAgencyDeleted(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyDeleteDate == null);
}

public static IQueryable<PolicyOutputDocument> AgencyDeleted(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyDeleteDate != null);
}

public static IQueryable<PolicyOutputDocument> ForProducer(this IQueryable<PolicyOutputDocument> query, long producerId)
{
    return query.Where(p => p.AgentId == producerId);
}

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,
  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 ODTC.agency_inbox_flag = 'Y'
  AND POD.release_date >= :releaseDate
  AND POD.agency_seq_num = :agencyId
  -- showOnlyDocuments = Unread (and default):
  AND POD.agency_viewed_flag = 'N'
  AND POD.agency_delete_date IS NULL
  -- showOnlyDocuments = All: replace the two lines above with
  --   AND POD.agency_delete_date IS NULL
  -- showOnlyDocuments = Deleted: replace the two lines above with
  --   AND POD.agency_delete_date IS NOT NULL
  AND POD.agent_num = :producerId -- overload with producerId only

Translation notes: - No GetAccountNumber statement is issued: the parameterless ReleasedAgencyViewablePolicyOutputDocumentQuery() used here has no policy filter. - The switch runs in .NET while the query is composed. Only one predicate set is present in any given statement. The default branch is the same as Unread. - IsAgencyViewable / IsAgencyInboxViewable / AgencyViewed are YesNoType flags, so a boolean member test becomes = 'Y' and == false becomes = 'N'. - 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. - 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. - 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-060
• SDN-BR-061

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

Sources

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