SQL-028: CertificateHolder supersede update

CertificateHolder.Supersede sets IsSuperseded to true and links the superseding holder on the loaded CertificateHolder. NHibernate persists the change via dirty-check detection at flush*.

Classification

Field	Value
SQL ID	SQL-028
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	Supersede

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:73

// Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs:68-77
public void Supersede(certData.CertificateHolder newCertficateHolder)
{

    DataItem.SupersededByCertificateHolder = newCertficateHolder;

    DataItem.IsSuperseded = 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. - :IsSuperseded = 'Y' (YesNoType, CertificateHolderMap.cs:20). - :SupersededByCertificateHolder = identifier of newCertficateHolder (CertificateHolderMap.cs:43) — the prior page omitted this column (set at line 71). - Prior page named the column IS_SUPERSEDED; the mapped column is SUPERSEDED_FLAG. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function).

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