SQL-042: PolicyOutputDocumentRepository.GetHeldAndPendingAgencyViewablePolicyOutputDocuments¶
This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by PolicyOutputDocumentRepository.GetHeldAndPendingAgencyViewablePolicyOutputDocuments.
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-042 |
| 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 | GetHeldAndPendingAgencyViewablePolicyOutputDocuments |
Parameters¶
| Parameter | Direction | Type | Max length | Description |
|---|---|---|---|---|
:policyNumber |
Input | long | N/A | Method argument bound into the LINQ expression (statements 1 and 2) |
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:78, using the same GetAccountNumber (:164) and EagerLoadQuery (:27) helpers as SQL-041.
public IEnumerable<PolicyOutputDocument> GetHeldAndPendingAgencyViewablePolicyOutputDocuments(long policyNumber)
{
var accountNumber = GetAccountNumber(policyNumber);
return EagerLoadQuery().AgencyViewable().HeldAndPending().ForPolicyOrAccount(policyNumber, accountNumber).ToList();
}
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> HeldAndPending(this IQueryable<PolicyOutputDocument> query)
{
return query.Where(x => new[] { DocumentStatusCode.HeldDocumentStatusCode, DocumentStatusCode.PendingDocumentStatusCode }.Contains(x.DocumentStatus.Code));
}
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 IN ('HELD', 'PEND')
AND POD.policy_num = :policyNumber
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 is never executed. p.Policy.PolicyNumber == policyNumber short-circuits to the FK POD.policy_num.
- new[] { HELD, PEND }.Contains(x.DocumentStatus.Code) is an in-memory constant array Contains, which becomes IN ('HELD', 'PEND'). NHibernate may bind the two values as parameters instead of literals.
- IsAgencyViewable is a YesNoType flag, so the boolean member test becomes = 'Y'.
- 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¶
Business rules¶
Note: Statements marked with
**could not be confirmed; treat as unknown until verified.
Sources¶
Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:78