SQL-135: Retrieve Certificate¶

This ORM statement performs a read operation on Certificate via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/Certificates/CertificateService.cs (L177, L191, L463, L612).

Classification¶

Field	Value
SQL ID	SQL-135
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`Certificate`
Criteria property	N/A — ORM
Lifecycle method	`GetCertificateDataById, GetCertificates, GetCertificatesReadyForBatchDistribution, GetEditableCertificates`

Parameters¶

Parameter	Direction	Type	Max length	Description
`policyNumber`	Input	**	N/A	`c.PolicyNumber == policyNumber` → column POLICY_NUM
`policyPeriodEffectiveDate`	Input	**	N/A	`c.PolicyPeriodEffectiveDate == policyPeriodEffectiveDate` → column POLICY_PERIOD_EFF_DATE
`CertData.Certificate.CertificateTypes.Bid`	Input	**	N/A	`c.CertificateType != CertData.Certificate.CertificateTypes.Bid` → column CERTIFICATE_TYPE_CODE
`CertData.Certificate.DistributionStatuses.ReadyToPrint`	Input	**	N/A	`a.DistributionStatusCertificateHolder == CertData.Certificate.DistributionStatuses.ReadyToPrint` → column CERT_HOLDER_DIST_STATUS_CODE
`CertData.Certificate.DistributionStatuses.ReadyToPrint`	Input	**	N/A	`a.DistributionStatusPolicyHolder == CertData.Certificate.DistributionStatuses.ReadyToPrint` → column POLICY_HOLDER_DIST_STATUS_CODE
`CertData.Certificate.CertificateTypes.Bid`	Input	**	N/A	`a.CertificateType != CertData.Certificate.CertificateTypes.Bid` → column CERTIFICATE_TYPE_CODE
`cutoff`	Input	**	N/A	`c.CreateDate >= …` → column CREATE_DATE
`certificateId`	Input	**	N/A	`c.Id == certificateId` → column CERTIFICATE_SEQ_NUM

Tables¶

Table	Access	Columns used
CERTIFICATE_DETAIL	SELECT	`CERTIFICATE_SEQ_NUM`, `POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`, `POLICY_PERIOD_EXP_DATE`, `ISSUE_DATE`, `issue_user`, `JOB_DESCR`, `OTHER_COVERAGE_AFFORDED`, `CANCEL_NOTICE_FLAG`, `CANCELLATION_CERTIFICATE_FLAG`, `RENEWAL_CERTIFICATE_FLAG`, `RENEWED_FLAG`, `IMAGE_SEQ_NUM`, `osc_limits_text`, `osc_officers_excluded_flag`, `osc_subrogation_waived_flag`, `CERTIFICATE_TYPE_CODE`, `POLICY_HOLDER_DIST_STATUS_CODE`, `CERT_HOLDER_DIST_STATUS_CODE`, `CREATE_USER`, `CREATE_DATE`, `UPDATE_USER`, `UPDATE_DATE`, `OSC_POLICY_SEQ_NUM`, `OSC_POLICY_PERIOD_SEQ_NUM`, `EMP_LIAB_STAT_CODE`, `CERT_HOLD_SEQ_NUM`, `TRADING_SEQ_NUM`

LINQ statement¶

Source: Services/SAIF.Services/Policies/Certificates/CertificateService.cs:177

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:177-179
            var certificates = unitOfWork.GetRepository<CertData.Certificate>().Query()
                    .Where(c => c.PolicyNumber == policyNumber && c.PolicyPeriodEffectiveDate == policyPeriodEffectiveDate
                        && c.CertificateType != CertData.Certificate.CertificateTypes.Bid).ToList();

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:191-195
            var certificates = unitOfWork.GetRepository<CertData.Certificate>().Query()
                .Where(a => (a.DistributionStatusCertificateHolder == CertData.Certificate.DistributionStatuses.ReadyToPrint ||
                            a.DistributionStatusPolicyHolder == CertData.Certificate.DistributionStatuses.ReadyToPrint) &&
                            a.CertificateType != CertData.Certificate.CertificateTypes.Bid);
            foreach (var cert in certificates)

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:463-464
            var certificates = Mapper.Map<Certificate[]>(unitOfWork.GetRepository<CertData.Certificate>().Query()
                                .Where(c => c.PolicyNumber == policyNumber && c.CreateDate >= cutoff && c.CertificateType != CertData.Certificate.CertificateTypes.Bid).ToList());

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:612-614
                return unitOfWork.GetRepository<CertData.Certificate>()
                                 .Query()
                                 .Single(c => c.Id == certificateId);

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
-- Services/SAIF.Services/Policies/Certificates/CertificateService.cs:177
SELECT
  CD.CERTIFICATE_SEQ_NUM,
  CD.POLICY_NUM,
  CD.POLICY_PERIOD_EFF_DATE,
  CD.POLICY_PERIOD_EXP_DATE,
  CD.ISSUE_DATE,
  CD.issue_user,
  CD.JOB_DESCR,
  CD.OTHER_COVERAGE_AFFORDED,
  CD.CANCEL_NOTICE_FLAG,
  CD.CANCELLATION_CERTIFICATE_FLAG,
  CD.RENEWAL_CERTIFICATE_FLAG,
  CD.RENEWED_FLAG,
  CD.IMAGE_SEQ_NUM,
  CD.osc_limits_text,
  CD.osc_officers_excluded_flag,
  CD.osc_subrogation_waived_flag,
  CD.CERTIFICATE_TYPE_CODE,
  CD.POLICY_HOLDER_DIST_STATUS_CODE,
  CD.CERT_HOLDER_DIST_STATUS_CODE,
  CD.CREATE_USER,
  CD.CREATE_DATE,
  CD.UPDATE_USER,
  CD.UPDATE_DATE,
  CD.OSC_POLICY_SEQ_NUM,
  CD.OSC_POLICY_PERIOD_SEQ_NUM,
  CD.EMP_LIAB_STAT_CODE,
  CD.CERT_HOLD_SEQ_NUM,
  CD.TRADING_SEQ_NUM
FROM CERTIFICATE_DETAIL CD
WHERE CD.POLICY_NUM = :policyNumber
  AND CD.POLICY_PERIOD_EFF_DATE = :policyPeriodEffectiveDate
  AND CD.CERTIFICATE_TYPE_CODE <> 'BID'

-- Services/SAIF.Services/Policies/Certificates/CertificateService.cs:191
SELECT
  CD.CERTIFICATE_SEQ_NUM,
  CD.POLICY_NUM,
  CD.POLICY_PERIOD_EFF_DATE,
  CD.POLICY_PERIOD_EXP_DATE,
  CD.ISSUE_DATE,
  CD.issue_user,
  CD.JOB_DESCR,
  CD.OTHER_COVERAGE_AFFORDED,
  CD.CANCEL_NOTICE_FLAG,
  CD.CANCELLATION_CERTIFICATE_FLAG,
  CD.RENEWAL_CERTIFICATE_FLAG,
  CD.RENEWED_FLAG,
  CD.IMAGE_SEQ_NUM,
  CD.osc_limits_text,
  CD.osc_officers_excluded_flag,
  CD.osc_subrogation_waived_flag,
  CD.CERTIFICATE_TYPE_CODE,
  CD.POLICY_HOLDER_DIST_STATUS_CODE,
  CD.CERT_HOLDER_DIST_STATUS_CODE,
  CD.CREATE_USER,
  CD.CREATE_DATE,
  CD.UPDATE_USER,
  CD.UPDATE_DATE,
  CD.OSC_POLICY_SEQ_NUM,
  CD.OSC_POLICY_PERIOD_SEQ_NUM,
  CD.EMP_LIAB_STAT_CODE,
  CD.CERT_HOLD_SEQ_NUM,
  CD.TRADING_SEQ_NUM
FROM CERTIFICATE_DETAIL CD
WHERE (CD.CERT_HOLDER_DIST_STATUS_CODE = 'READY'
    OR CD.POLICY_HOLDER_DIST_STATUS_CODE = 'READY')
  AND CD.CERTIFICATE_TYPE_CODE <> 'BID'

-- Services/SAIF.Services/Policies/Certificates/CertificateService.cs:463
SELECT
  CD.CERTIFICATE_SEQ_NUM,
  CD.POLICY_NUM,
  CD.POLICY_PERIOD_EFF_DATE,
  CD.POLICY_PERIOD_EXP_DATE,
  CD.ISSUE_DATE,
  CD.issue_user,
  CD.JOB_DESCR,
  CD.OTHER_COVERAGE_AFFORDED,
  CD.CANCEL_NOTICE_FLAG,
  CD.CANCELLATION_CERTIFICATE_FLAG,
  CD.RENEWAL_CERTIFICATE_FLAG,
  CD.RENEWED_FLAG,
  CD.IMAGE_SEQ_NUM,
  CD.osc_limits_text,
  CD.osc_officers_excluded_flag,
  CD.osc_subrogation_waived_flag,
  CD.CERTIFICATE_TYPE_CODE,
  CD.POLICY_HOLDER_DIST_STATUS_CODE,
  CD.CERT_HOLDER_DIST_STATUS_CODE,
  CD.CREATE_USER,
  CD.CREATE_DATE,
  CD.UPDATE_USER,
  CD.UPDATE_DATE,
  CD.OSC_POLICY_SEQ_NUM,
  CD.OSC_POLICY_PERIOD_SEQ_NUM,
  CD.EMP_LIAB_STAT_CODE,
  CD.CERT_HOLD_SEQ_NUM,
  CD.TRADING_SEQ_NUM
FROM CERTIFICATE_DETAIL CD
WHERE CD.POLICY_NUM = :policyNumber
  AND CD.CREATE_DATE >= :cutoff
  AND CD.CERTIFICATE_TYPE_CODE <> 'BID'

-- Services/SAIF.Services/Policies/Certificates/CertificateService.cs:612
SELECT
  CD.CERTIFICATE_SEQ_NUM,
  CD.POLICY_NUM,
  CD.POLICY_PERIOD_EFF_DATE,
  CD.POLICY_PERIOD_EXP_DATE,
  CD.ISSUE_DATE,
  CD.issue_user,
  CD.JOB_DESCR,
  CD.OTHER_COVERAGE_AFFORDED,
  CD.CANCEL_NOTICE_FLAG,
  CD.CANCELLATION_CERTIFICATE_FLAG,
  CD.RENEWAL_CERTIFICATE_FLAG,
  CD.RENEWED_FLAG,
  CD.IMAGE_SEQ_NUM,
  CD.osc_limits_text,
  CD.osc_officers_excluded_flag,
  CD.osc_subrogation_waived_flag,
  CD.CERTIFICATE_TYPE_CODE,
  CD.POLICY_HOLDER_DIST_STATUS_CODE,
  CD.CERT_HOLDER_DIST_STATUS_CODE,
  CD.CREATE_USER,
  CD.CREATE_DATE,
  CD.UPDATE_USER,
  CD.UPDATE_DATE,
  CD.OSC_POLICY_SEQ_NUM,
  CD.OSC_POLICY_PERIOD_SEQ_NUM,
  CD.EMP_LIAB_STAT_CODE,
  CD.CERT_HOLD_SEQ_NUM,
  CD.TRADING_SEQ_NUM
FROM CERTIFICATE_DETAIL CD
WHERE CD.CERTIFICATE_SEQ_NUM = :certificateId
-- NHibernate expects at most one row

Translation notes: - CertificateType uses CertificateTypeType (Data/SAIF.Data.NHibernate/CustomTypes/CertificateTypeType.cs:58: Bid -> 'BID'); DistributionStatus* use CertificateDistributionStatusType (Data/SAIF.Data.NHibernate/CustomTypes/CertificateDistributionStatusType.cs:56: ReadyToPrint -> 'READY'). - L191-194 is enumerated by foreach (L195); the loop body's cert.CertificateHolder... access lazy-loads the holder with separate statements (not fabricated here). - cutoff (L463) is a local -> bind :cutoff.

Operations and processes¶

CertificateService.GetCertificateDataById — see Operations and processes
CertificateService.GetCertificates — see Operations and processes
CertificateService.GetCertificatesReadyForBatchDistribution — see Operations and processes
CertificateService.GetEditableCertificates — see Operations and processes

Sources¶

Services/SAIF.Services/Policies/Certificates/CertificateService.cs (L177, L191, L463, L612)