Skip to content

SQL-063: ClaimQueryProvider.GetOriginalAcceptance

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

Classification

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

Parameters

Parameter Direction Type Max length Description
claimId Input long N/A Method argument of ClaimQueryProvider.GetOriginalAcceptance bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table Access Columns used
CL_CLAIM_REQUEST_DECISION SELECT CLAIM_ID, PRIMARY_KEY_SEQ_NUM, REQUEST_DATE, REQUEST_DENIAL_REASON_CODE, REQUEST_DECISION_EFF_DATE, UPDATE_DATE, UPDATE_USER, UPDATE_NUM, REQUEST_DECISION_CODE, REQUEST_TYPE_CODE

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:296-298.

// Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:296
var repo = Factory.GetRepository<ClaimRequestDecision>();
return repo.Query().OrderBy(x => x.SubId)
    .FirstOrDefault(x => x.ClaimId == claimId && x.RequestType.Code == "I" && x.RequestDecision.Code == "A");

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 *
FROM (
  SELECT
    CCRD.CLAIM_ID,
    CCRD.PRIMARY_KEY_SEQ_NUM,
    CCRD.REQUEST_DATE,
    CCRD.REQUEST_DENIAL_REASON_CODE,
    CCRD.REQUEST_DECISION_EFF_DATE,
    CCRD.UPDATE_DATE,
    CCRD.UPDATE_USER,
    CCRD.UPDATE_NUM,
    CCRD.REQUEST_DECISION_CODE,
    CCRD.REQUEST_TYPE_CODE
  FROM CL_CLAIM_REQUEST_DECISION CCRD
  WHERE CCRD.CLAIM_ID = :claimId
    AND CCRD.REQUEST_TYPE_CODE = 'I'
    AND CCRD.REQUEST_DECISION_CODE = 'A'
  ORDER BY CCRD.PRIMARY_KEY_SEQ_NUM
)
WHERE ROWNUM <= 1

Translation notes: - x.RequestType.Code is the Id of RequestTypeCode, so it resolves to the owner column CCRD.REQUEST_TYPE_CODE with no join; CL_REQUEST_TYPE_CODE is not accessed as a separate table. - x.RequestDecision.Code is the Id of ClaimDecisionCode, so it resolves to the owner column CCRD.REQUEST_DECISION_CODE with no join; CLAIM_DECISION_CODE is not accessed as a separate table. - FirstOrDefault is applied after OrderBy, so the ROWNUM wrapper goes outside the ordered subquery, returning the earliest (lowest PRIMARY_KEY_SEQ_NUM) initial-request acceptance decision.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:296