SQL-005: Create Case via SaveRequest¶

This ORM statement performs a create operation on Case via SaveRequest, executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1176).

Classification¶

Field	Value
SQL ID	SQL-005
Operation type	Create
Execution method	ORM (IRepository.Add)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`Case`
Criteria property	N/A — ORM
Lifecycle method	`SaveRequest`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Id`	Input	`long`	N/A	Identifier from `LEGAL_COLLECTION_SEQ.NEXTVAL`
`:UpdateNum`	Input	`int`	N/A	Version column (`Version()`); NHibernate writes its initial value
`:PolicyNum`	Input	`long?`	N/A
`:SaifClaimNum`	Input	`string`	N/A
`:ReceivedDate`	Input	`DateTime`	N/A
`:RecordingCountyCode`	Input	`string`	N/A
`:State`	Input	`string`	N/A
`:CourtCaseNum`	Input	`string`	N/A
`:CurrentBalanceAmount`	Input	`decimal`	N/A
`:UpdateUser`	Input	`string`	N/A
`:UpdateDate`	Input	`DateTime`	N/A
`:CreditRepUser`	Input	`CreditRepUser`	N/A	Identifier of the referenced `CreditRepUser` entity (`References(x => x.CreditRepUser)`)
`:InterestCalcCode`	Input	`InterestCalcCode`	N/A	Identifier of the referenced `InterestCalcCode` entity (`References(x => x.InterestCalcCode)`)
`:AnnualInterestRate`	Input	`InterestRateValue`	N/A	Identifier of the referenced `InterestRateValue` entity (`References(x => x.AnnualInterestRate)`)
`:JudgmentCode`	Input	`JudgmentCode`	N/A	Identifier of the referenced `JudgmentCode` entity (`References(x => x.JudgmentCode)`)
`:JudgmentYears`	Input	`JudgmentYearsValue`	N/A	Identifier of the referenced `JudgmentYearsValue` entity (`References(x => x.JudgmentYears)`)
`:CollectionCode`	Input	`CollectionCode`	N/A	Identifier of the referenced `CollectionCode` entity (`References(x => x.CollectionCode)`)
`:StatusCode`	Input	`StatusCode`	N/A	Identifier of the referenced `StatusCode` entity (`References(x => x.StatusCode)`)

Tables¶

Table	Access	Columns used
LEGAL_COLLECTION	INSERT	LEGAL_COLLECTION_SEQ_NUM, UPDATE_NUM, POLICY_NUM, SAIF_CLAIM_NUM, RECEIVED_DATE, RECORDING_COUNTY_CODE, STATE, COURT_CASE_NUM, CURRENT_BALANCE_AMT, UPDATE_USER, UPDATE_DATE, CREDIT_REP_USER, INTEREST_CALC_OPTION_CODE, INTEREST_ANNUAL_RATE, JUDGMENT_CODE, JUDGMENT_YEARS, LEGAL_COLLECTION_CODE, LEGAL_COLLECTION_STATUS_CODE (identifier from sequence `LEGAL_COLLECTION_SEQ.NEXTVAL`)

LINQ statement¶

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1176.

// Services/SAIF.Services/Collections/CollectionsService.cs:1176
UnitOfWork.GetRepository<DataModel.Case>().Add(caseData);

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

INSERT INTO LEGAL_COLLECTION (
  LEGAL_COLLECTION_SEQ_NUM,
  UPDATE_NUM,
  POLICY_NUM,
  SAIF_CLAIM_NUM,
  RECEIVED_DATE,
  RECORDING_COUNTY_CODE,
  STATE,
  COURT_CASE_NUM,
  CURRENT_BALANCE_AMT,
  UPDATE_USER,
  UPDATE_DATE,
  CREDIT_REP_USER,
  INTEREST_CALC_OPTION_CODE,
  INTEREST_ANNUAL_RATE,
  JUDGMENT_CODE,
  JUDGMENT_YEARS,
  LEGAL_COLLECTION_CODE,
  LEGAL_COLLECTION_STATUS_CODE)
VALUES (
  :Id,
  :UpdateNum,
  :PolicyNum,
  :SaifClaimNum,
  :ReceivedDate,
  :RecordingCountyCode,
  :State,
  :CourtCaseNum,
  :CurrentBalanceAmount,
  :UpdateUser,
  :UpdateDate,
  :CreditRepUser,
  :InterestCalcCode,
  :AnnualInterestRate,
  :JudgmentCode,
  :JudgmentYears,
  :CollectionCode,
  :StatusCode)

Translation notes: - :Id is the value fetched from sequence LEGAL_COLLECTION_SEQ (GeneratedBy.Native("LEGAL_COLLECTION_SEQ")) before the INSERT; NHibernate assigns it to Case.Id. - UPDATE_NUM is the optimistic-concurrency version column (Version(x => x.UpdateNum)); NHibernate sets its initial value on INSERT. - Reference FK values are bound from the associated entity's identifier; the referenced tables are not read or joined by this statement: CreditRepUser → CREDIT_REP_USER (entity table CREDIT_REP_USER not accessed); InterestCalcCode → INTEREST_CALC_OPTION_CODE (entity table INTEREST_CALC_OPTION_CODE not accessed); AnnualInterestRate → INTEREST_ANNUAL_RATE (entity table INTEREST_RATE_VALUE not accessed); JudgmentCode → JUDGMENT_CODE (entity table JUDGMENT_CODE not accessed); JudgmentYears → JUDGMENT_YEARS (entity table JUDGEMENT_YEARS_VALUE not accessed); CollectionCode → LEGAL_COLLECTION_CODE (entity table LEGAL_COLLECTION_CODE not accessed); StatusCode → LEGAL_COLLECTION_STATUS_CODE (entity table LEGAL_COLLECTION_STATUS_CODE not accessed). - Cascade: HasMany(x => x.Activities) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.Debtors) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.Transactions) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Only LEGAL_COLLECTION is written by this statement; child Activities / Debtors / Transactions rows are written by cascade in their own statements (see cascade notes).

Operations and processes¶

CollectionsService.SaveRequest — see Operations and processes

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

Sources¶

Services/SAIF.Services/Collections/CollectionsService.cs (L1176)