SQL-351: Update CbpDiagnosis via UpdateDiagnosis¶

This ORM statement updates a CBP_DIAGNOSIS row's descriptive text, category, and effective/expiration dates for a diagnosis code, executed from Services/SAIF.Services/Claims/ICDCodeService/Icd10CodeService.cs (L276). The SQL below is inferred from the IRepository<CbpDiagnosis>.Update call and the CbpDiagnosis NHibernate mapping, not literal SQL text in source.

Classification¶

Field	Value
SQL ID	SQL-351
Operation type	Update
Execution method	ORM (IRepository.Update)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`CbpDiagnosis`
Criteria property	N/A — ORM
Lifecycle method	`UpdateDiagnosis`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:DiagnosisCode`	Input	string	N/A	Identifier of the CbpDiagnosis being updated (`DIAGNOSIS_CODE`)
`:IcdVersionCode`	Input	string	N/A	New value of `ICD_VERSION_CODE`
`:DiagnosisDescription`	Input	string	N/A	New value of `DIAGNOSIS_DESCR`
`:DiagnosisShortDescription`	Input	string	N/A	New value of `DIAGNOSIS_SHORT_DESCR`
`:DiagnosisCategoryCode`	Input	string	N/A	New value of `DIAGNOSIS_CATEGORY_CODE`
`:IsValidMedicareReport`	Input	bool	N/A	New value of `VALID_MEDICARE_REPORT_FLAG`
`:EffectiveDate`	Input	DateTime	N/A	New value of `EFF_DATE`
`:ExpirationDate`	Input	DateTime?	N/A	New value of `EXP_DATE`
`:UpdateUser`	Input	string	N/A	New value of `UPDATE_USER`
`:UpdateDate`	Input	DateTime	N/A	New value of `UPDATE_DATE`
`:newVersion`	Input	int	N/A	Incremented `UPDATE_NUM` value
`:oldVersion`	Input	int	N/A	Current `UPDATE_NUM` value (optimistic-concurrency check)

Tables¶

Table	Access	Columns used
CBP_DIAGNOSIS	UPDATE	`DIAGNOSIS_CODE`, `UPDATE_NUM`, `ICD_VERSION_CODE`, `DIAGNOSIS_DESCR`, `DIAGNOSIS_SHORT_DESCR`, `DIAGNOSIS_CATEGORY_CODE`, `VALID_MEDICARE_REPORT_FLAG`, `EFF_DATE`, `EXP_DATE`, `UPDATE_USER`, `UPDATE_DATE`

LINQ statement¶

Source: Services/SAIF.Services/Claims/ICDCodeService/Icd10CodeService.cs:276

// Services/SAIF.Services/Claims/ICDCodeService/Icd10CodeService.cs:276
unitOfWork.GetRepository<CbpDiagnosis>().Update(cbpDiagnosis);

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 CBP_DIAGNOSIS
SET
  UPDATE_NUM = :newVersion,
  ICD_VERSION_CODE = :IcdVersionCode,
  DIAGNOSIS_DESCR = :DiagnosisDescription,
  DIAGNOSIS_SHORT_DESCR = :DiagnosisShortDescription,
  DIAGNOSIS_CATEGORY_CODE = :DiagnosisCategoryCode,
  VALID_MEDICARE_REPORT_FLAG = :IsValidMedicareReport,
  EFF_DATE = :EffectiveDate,
  EXP_DATE = :ExpirationDate,
  UPDATE_USER = :UpdateUser,
  UPDATE_DATE = :UpdateDate
WHERE DIAGNOSIS_CODE = :DiagnosisCode
  AND UPDATE_NUM = :oldVersion

Translation notes:

Version(x => x.UpdateNumber).Column("UPDATE_NUM") - NHibernate sets the incremented version and adds the old-version predicate.
Update(entity) emits an explicit UPDATE of every updatable mapped column (no DynamicUpdate() on this map).

Operations and processes¶

Icd10CodeService.UpdateDiagnosis — see Operations and processes

Sources¶

Services/SAIF.Services/Claims/ICDCodeService/Icd10CodeService.cs (L276)