SQL-009: CertificateHolder insert¶

The create-mode CertificateHolder constructor normalizes the holder's text/address fields and adds a new CertificateHolder entity to the repository.

Classification¶

Field	Value
SQL ID	SQL-009
Operation type	Create
Execution method	ORM (IRepository.Add)
Database	Oracle*
Schema	[unqualified]**
Command type	N/A — ORM (NHibernate generates SQL at runtime from the ClassMaps in `Data/SAIF.Data.NHibernate/Mappings/**`)
Business object	`CertificateHolder`
Criteria property	N/A — ORM
Lifecycle method	`CertificateHolder`

Tables¶

Table	Access	Columns used
CERTIFICATE_HOLDER	INSERT	`CERT_HOLD_SEQ_NUM`, `POLICY_NUM`, `CERT_HOLD_NAME1`, `CERT_HOLD_NAME2`, `EXPIRED_FLAG`, `SUPERSEDED_FLAG`, `CERT_HOLD_ADDR1`, `CERT_HOLD_ADDR2`, `CERT_HOLD_ADDR3`, `CERT_HOLD_CITY`, `CERT_HOLD_STATE`, `CERT_HOLD_COUNTRY`, `CERT_HOLD_ZIP`, `CERT_HOLD_ZIP4`, `FOREIGN_ADDRESS_FLAG`, `CERT_HOLD_PHONE`, `CERT_HOLD_FAX`, `CERT_HOLD_EMAIL`, `CREATE_USER`, `CREATE_DATE`, `UPDATE_USER`, `UPDATE_DATE`, `SUPERSEDED_BY_CERT_HOLD_SEQNUM`

LINQ statement¶

Source: Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:38

// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:30-38
DataItem = new certData.CertificateHolder();

MapContractToDataObject(DataItem, certificateHolderEdit);

DataItem.CreateUser = userId;
DataItem.CreateDate = DateTime.Now;
DataItem.UpdateUser = DataItem.CreateUser;
DataItem.UpdateDate = DataItem.CreateDate;
factory.GetRepository<certData.CertificateHolder>().Add(DataItem);

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 CERT_HOLD_SEQUENCE.NEXTVAL
FROM DUAL

INSERT INTO CERTIFICATE_HOLDER (
  CERT_HOLD_SEQ_NUM,
  POLICY_NUM,
  CERT_HOLD_NAME1,
  CERT_HOLD_NAME2,
  EXPIRED_FLAG,
  SUPERSEDED_FLAG,
  CERT_HOLD_ADDR1,
  CERT_HOLD_ADDR2,
  CERT_HOLD_ADDR3,
  CERT_HOLD_CITY,
  CERT_HOLD_STATE,
  CERT_HOLD_COUNTRY,
  CERT_HOLD_ZIP,
  CERT_HOLD_ZIP4,
  FOREIGN_ADDRESS_FLAG,
  CERT_HOLD_PHONE,
  CERT_HOLD_FAX,
  CERT_HOLD_EMAIL,
  CREATE_USER,
  CREATE_DATE,
  UPDATE_USER,
  UPDATE_DATE,
  SUPERSEDED_BY_CERT_HOLD_SEQNUM)
VALUES (
  :Id,
  :PolicyNumber,
  :Name1,
  :Name2,
  :IsExpired,
  :IsSuperseded,
  :AddressLine1,
  :AddressLine2,
  :AddressLine3,
  :City,
  :State,
  :CountryCode,
  :ZipCode,
  :PlusFourCode,
  :IsForeign,
  :PhoneNumber,
  :FaxNumber,
  :EmailAddress,
  :CreateUser,
  :CreateDate,
  :UpdateUser,
  :UpdateDate,
  :SupersededByCertificateHolder)

Translation notes: - :Id is the value fetched from CERT_HOLD_SEQUENCE (GeneratedBy.Native("CERT_HOLD_SEQUENCE") on Oracle). - No version column is mapped (Version is commented out, CertificateHolderMap.cs:41). - :IsExpired, :IsSuperseded, :IsForeign are bound as 'Y'/'N' (YesNoType). - Reference bind variables (:<Property>) carry the referenced entity's identifier value. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function). - Values other than create/update audit fields come from MapContractToDataObject (line 32). - Engine evidence: the Certs site registers its unit of work with the "oracle" factory (DependencyMaps/SAIF.DependencyMaps.Web.Certs/DependencyMap.cs:95-96).

Operations and processes¶

OP-DOM-17 CertificateHolder create/update/Expire/Reinstate/Supersede — normalizes text/address and adds or updates the holder (see Operations and processes)

Business rules¶

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs