Skip to content

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 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.

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:222
unitOfWork.GetRepository<CertData.Certificate>().Add(newCertificate);

// Services/SAIF.Services/Policies/Certificates/CertificateService.cs:381
unitOfWork.GetRepository<CertData.Certificate>().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.

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

Business rules

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • Services/SAIF.Services/Policies/Certificates/CertificateService.cs (L222, L381)