SQL-136: Retrieve Certificate via MarkCertificateAsPrinted¶

This ORM statement performs a read operation on Certificate via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/Certificates/ReportingService.cs (L353).

Classification¶

Field	Value
SQL ID	SQL-136
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	`MarkCertificateAsPrinted`

Parameters¶

Parameter	Direction	Type	Max length	Description
`certificate.Id`	Input	**	N/A	`a.Id == certificate.Id` → 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/ReportingService.cs:353

// Services/SAIF.Services/Policies/Certificates/ReportingService.cs:353
            var certificateData = unitOfWork.GetRepository<Data.Model.Policies.Certificates.Certificate>().Query().Single(a => a.Id == certificate.Id);

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/ReportingService.cs:353
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 = :Id
-- NHibernate expects at most one row

Translation notes: - certificate.Id -> bind :Id.

Operations and processes¶

ReportingService.MarkCertificateAsPrinted — see Operations and processes

Sources¶

Services/SAIF.Services/Policies/Certificates/ReportingService.cs (L353)