SQL-027: CertificateHolder expire update

CertificateHolder.Expire sets IsExpired to true and stamps the updating user on the loaded CertificateHolder. NHibernate persists the change via dirty-check detection at flush*. (Reinstate performs the inverse, setting IsExpired to false.)

Classification

Field	Value
SQL ID	SQL-027
Operation type	Update*
Execution method	ORM (dirty-check flush*)
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	Expire

Tables

Table	Access	Columns used
CERTIFICATE_HOLDER	UPDATE	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, CERT_HOLD_SEQ_NUM

LINQ statement

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

// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:53-58
public void Expire()
{
    DataItem.IsExpired = true;
    DataItem.UpdateUser = userId;
    DataItem.UpdateDate = DateTime.Now;
}

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
UPDATE CERTIFICATE_HOLDER
SET
  POLICY_NUM = :PolicyNumber,
  CERT_HOLD_NAME1 = :Name1,
  CERT_HOLD_NAME2 = :Name2,
  EXPIRED_FLAG = :IsExpired,
  SUPERSEDED_FLAG = :IsSuperseded,
  CERT_HOLD_ADDR1 = :AddressLine1,
  CERT_HOLD_ADDR2 = :AddressLine2,
  CERT_HOLD_ADDR3 = :AddressLine3,
  CERT_HOLD_CITY = :City,
  CERT_HOLD_STATE = :State,
  CERT_HOLD_COUNTRY = :CountryCode,
  CERT_HOLD_ZIP = :ZipCode,
  CERT_HOLD_ZIP4 = :PlusFourCode,
  FOREIGN_ADDRESS_FLAG = :IsForeign,
  CERT_HOLD_PHONE = :PhoneNumber,
  CERT_HOLD_FAX = :FaxNumber,
  CERT_HOLD_EMAIL = :EmailAddress,
  CREATE_USER = :CreateUser,
  CREATE_DATE = :CreateDate,
  UPDATE_USER = :UpdateUser,
  UPDATE_DATE = :UpdateDate,
  SUPERSEDED_BY_CERT_HOLD_SEQNUM = :SupersededByCertificateHolder
WHERE CERT_HOLD_SEQ_NUM = :Id

Translation notes: - No explicit Update() call: under the stateful strategy RepositoryBase.Update is a no-op and the UPDATE is emitted by NHibernate dirty-checking at Commit() flush (Legends: SAIF.Framework.Data.NHibernate RepositoryBase / business-rules). The mapping declares no DynamicUpdate(), so NHibernate sends every updatable mapped column, not only the ones changed here. - No version column — no optimistic-concurrency predicate. - :IsExpired = 'Y' (YesNoType, CertificateHolderMap.cs:19). Reinstate() (lines 60-65) produces the same shape with 'N'. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function). - Prior page named the column IS_EXPIRED; the mapped column is EXPIRED_FLAG.

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

• DOM-BR-170
• DOM-BR-171
• DOM-BR-172

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