--- title: "SQL-013: Create Certificate" summary: Create operation against Certificate via NHibernate ORM. status: draft owner: SAIF Doc Gen last_reviewed: 2026-09-24 library: SAIF.Services reference_type: package --- # SQL-013: Create Certificate This ORM statement performs a create operation on `Certificate` via `AddCertificate, RenewCertificate`, executed from `Services/SAIF.Services/Policies/Certificates/CertificateService.cs` (L222), `Services/SAIF.Services/Policies/Certificates/CertificateService.cs` (L381). ## Classification | Field | Value | | --- | --- | | **SQL ID** | SQL-013 | | **Operation type** | Create | | **Execution method** | ORM (IRepository.Add) | | **Database** | Oracle | | **Schema** | [unqualified]** | | **Command type** | N/A (ORM-generated) | | **Business object** | `Certificate` | | **Criteria property** | N/A — ORM | | **Lifecycle method** | `AddCertificate, RenewCertificate` | ## Parameters | Parameter | Direction | Type | Max length | Description | | --- | --- | --- | --- | --- | | `:Id` | Input | `long` | N/A | Identifier from `CERTIFICATE_SEQUENCE.NEXTVAL` | | `:PolicyNumber` | Input | `long` | N/A | | | `:PolicyPeriodEffectiveDate` | Input | `DateTime` | N/A | | | `:PolicyPeriodExpirationDate` | Input | `DateTime` | N/A | | | `:IssueDate` | Input | `DateTime` | N/A | | | `:IssueUser` | Input | `string` | N/A | | | `:JobDescription` | Input | `string` | N/A | | | `:OtherCoverages` | Input | `string` | N/A | | | `:CancelNoticeFlag` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:IsCancellationCertificate` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:IsRenewalCertificate` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:RenewedFlag` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:ImageId` | Input | `long?` | N/A | | | `:OscLimitsCode` | Input | `string` | N/A | | | `:AreOfficersExcluded` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:IsSubrogationWaived` | Input | `bool` | N/A | Bound as `'Y'`/`'N'` (`YesNoType`) | | `:CertificateType` | Input | `CertificateTypes` | N/A | Custom type `CertificateTypeType` | | `:DistributionStatusPolicyHolder` | Input | `DistributionStatuses` | N/A | Custom type `CertificateDistributionStatusType` | | `:DistributionStatusCertificateHolder` | Input | `DistributionStatuses` | N/A | Custom type `CertificateDistributionStatusType` | | `:CreateUser` | Input | `string` | N/A | | | `:CreateDate` | Input | `DateTime` | N/A | | | `:UpdateUser` | Input | `string` | N/A | | | `:UpdateDate` | Input | `DateTime` | N/A | | | `:OscPolicy` | Input | `OtherStatesPolicy` | N/A | Identifier of the referenced `?` entity (`References(x => x.OscPolicy)`) | | `:OscPeriod` | Input | `OtherStatesPolicyPeriod` | N/A | Identifier of the referenced `?` entity (`References(x => x.OscPeriod)`) | | `:Limits` | Input | `EmployerLiabilityLimit` | N/A | Identifier of the referenced `?` entity (`References(x => x.Limits)`) | | `:CertificateHolder` | Input | `CertificateHolder` | N/A | Identifier of the referenced `CertificateHolder` entity (`References(x => x.CertificateHolder)`) | | `:Dba` | Input | `TradingAs` | N/A | Identifier of the referenced `?` entity (`References(x => x.Dba)`) | ## Tables | Table | Access | Columns used | | --- | --- | --- | | [CERTIFICATE_DETAIL](../database-tables.md#certificate_detail) | INSERT | 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 (identifier from sequence `CERTIFICATE_SEQUENCE.NEXTVAL`) | ## LINQ statement Source: `Services/SAIF.Services/Policies/Certificates/CertificateService.cs:222`, `Services/SAIF.Services/Policies/Certificates/CertificateService.cs:381`. ```csharp // Services/SAIF.Services/Policies/Certificates/CertificateService.cs:222 unitOfWork.GetRepository().Add(newCertificate); // Services/SAIF.Services/Policies/Certificates/CertificateService.cs:381 unitOfWork.GetRepository().Add(newCertificate); ``` ## 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. ```sql -- Inferred from LINQ (NHibernate mapping translation); not concrete SQL SELECT CERTIFICATE_SEQUENCE.NEXTVAL FROM DUAL INSERT INTO CERTIFICATE_DETAIL ( 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) VALUES ( :Id, :PolicyNumber, :PolicyPeriodEffectiveDate, :PolicyPeriodExpirationDate, :IssueDate, :IssueUser, :JobDescription, :OtherCoverages, :CancelNoticeFlag, :IsCancellationCertificate, :IsRenewalCertificate, :RenewedFlag, :ImageId, :OscLimitsCode, :AreOfficersExcluded, :IsSubrogationWaived, :CertificateType, :DistributionStatusPolicyHolder, :DistributionStatusCertificateHolder, :CreateUser, :CreateDate, :UpdateUser, :UpdateDate, :OscPolicy, :OscPeriod, :Limits, :CertificateHolder, :Dba) ``` Translation notes: - `:Id` is the value fetched from sequence `CERTIFICATE_SEQUENCE` (`GeneratedBy.Native("CERTIFICATE_SEQUENCE")`) before the INSERT; NHibernate assigns it to `Certificate.Id`. - `:CancelNoticeFlag`, `:IsCancellationCertificate`, `:IsRenewalCertificate`, `:RenewedFlag`, `:AreOfficersExcluded`, `:IsSubrogationWaived` are bound as `'Y'` / `'N'` (`YesNoType`). - Reference FK values are bound from the associated entity's identifier; the referenced tables are **not** read or joined by this statement: `OscPolicy` → `OSC_POLICY_SEQ_NUM`; `OscPeriod` → `OSC_POLICY_PERIOD_SEQ_NUM`; `Limits` → `EMP_LIAB_STAT_CODE`; `CertificateHolder` → `CERT_HOLD_SEQ_NUM` (entity table `CERTIFICATE_HOLDER` not accessed); `Dba` → `TRADING_SEQ_NUM`. - Two call sites (`:222` and `:381`) produce the same INSERT shape. ## Operations and processes - CertificateService.AddCertificate — see [Operations and processes](../operations-processes.md) - CertificateService.RenewCertificate — see [Operations and processes](../operations-processes.md) ## Business rules - [SVC-BR-POL-020 Standard/Bid add needs MaintainOthers, MaintainOwn or the Ext variant of either](../business-rules.md#svc-br-pol-020-standard%2Fbid-add-needs-maintainothers%2C-maintainown-or-the-ext-variant-of-either) - [SVC-BR-POL-021 OSC add needs MaintainOthersOsc, MaintainOwnOsc or the Ext variant of either](../business-rules.md#svc-br-pol-021-osc-add-needs-maintainothersosc%2C-maintainownosc-or-the-ext-variant-of-either) - [SVC-BR-POL-025 A certificate already renewed (HasBeenRenewed) cannot be renewed again](<../business-rules.md#svc-br-pol-025-a-certificate-already-renewed-(hasbeenrenewed)-cannot-be-renewed-again>) > **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified. ## Sources - `Services/SAIF.Services/Policies/Certificates/CertificateService.cs (L222, L381)`