SQL-007: Bill regenerate insert (CbpBill)

BillRegenerator.Regenerate clones an existing bill into a new CbpBill entity and adds it to the repository, the first step of regenerating a bill with a new entry source.

Classification

Field	Value
SQL ID	SQL-007
Operation type	Create
Execution method	ORM (IRepository.Add)
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	BillRegenerator
Criteria property	N/A — ORM
Lifecycle method	Regenerate

Tables

Table	Access	Columns used
CBP_BILL	INSERT	BILL_ID, UPDATE_NUM, PATIENT_REFERENCE_NUM, MAIL_ROOM_RECEIVED_DATE, SAIF_CLAIM_NUM, SAIF_CLAIM_CHK_VALUE, WORKER_FIRST_NAME, WORKER_MIDDLE_INITIAL, WORKER_LAST_NAME, WORKER_PHONE_NUM, WORKER_BIRTH_DATE, WORKER_ADDRESS_LINE1, WORKER_ADDRESS_LINE2, WORKER_ADDRESS_LINE3, WORKER_ADDRESS_LINE4, GENDER_CODE, PATIENT_ACCOUNT_NUM, PROVIDER_TAX_ID, PROVIDER_ADDRESS_LINE1, PROVIDER_ADDRESS_LINE2, PROVIDER_CITY_NAME, PROVIDER_STATE_CODE, PROVIDER_ZIP_CODE, PROVIDER_LICENSE_NUM, ADMISSION_DATE, DISCHARGE_DATE, STATEMENT_FROM_DATE, STATEMENT_THRU_DATE, WORKER_TAX_ID, TOWID, REBILL_FLAG, CURRENT_BILL_STATUS_ID, PRIORITY_NUM, UPDATE_USER, UPDATE_DATE, REFERRING_PROVIDER_NAME, BILL_TOTAL_AMT, DRG_CODE, BILL_PACKAGE_QUANTITY, WORKFLOW_BYPASS_CODE, CURRENT_RESPONSIBLE_PARTY_ID, CURRENT_STATUS_EFF_DATE, CURRENT_UPDATE_USER, CURRENT_NEXT_REVIEW_DATE, UB_BILL_TYPE_CODE, CBP_ADMISSION_TYPE_CODE, IMAGE_SEQ_NUM, CLAIM_ID, BILL_TYPE_CODE, PROVIDER_LOCATION_SEQ_NUM, CURRENT_BILL_STATUS_CODE, CBP_BILL_ENTRY_SOURCE_CODE

LINQ statement

Source: Domain/SAIF.Domain/Claims/Bills/BillRegenerator.cs:31

// Domain/SAIF.Domain/Claims/Bills/BillRegenerator.cs:26-31
var clonedBill = RegenBillData(bill.Data, source, currentUser);
RegenBillServiceData(bill.Data, clonedBill, currentUser);
RegenBillProcedureData(bill.Data, clonedBill, currentUser);
RegenBillDiagnosisData(bill.Data, clonedBill, currentUser);

factory.GetRepository<CbpBill>().Add(clonedBill);

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

INSERT INTO CBP_BILL (
  BILL_ID,
  UPDATE_NUM,
  PATIENT_REFERENCE_NUM,
  MAIL_ROOM_RECEIVED_DATE,
  SAIF_CLAIM_NUM,
  SAIF_CLAIM_CHK_VALUE,
  WORKER_FIRST_NAME,
  WORKER_MIDDLE_INITIAL,
  WORKER_LAST_NAME,
  WORKER_PHONE_NUM,
  WORKER_BIRTH_DATE,
  WORKER_ADDRESS_LINE1,
  WORKER_ADDRESS_LINE2,
  WORKER_ADDRESS_LINE3,
  WORKER_ADDRESS_LINE4,
  GENDER_CODE,
  PATIENT_ACCOUNT_NUM,
  PROVIDER_TAX_ID,
  PROVIDER_ADDRESS_LINE1,
  PROVIDER_ADDRESS_LINE2,
  PROVIDER_CITY_NAME,
  PROVIDER_STATE_CODE,
  PROVIDER_ZIP_CODE,
  PROVIDER_LICENSE_NUM,
  ADMISSION_DATE,
  DISCHARGE_DATE,
  STATEMENT_FROM_DATE,
  STATEMENT_THRU_DATE,
  WORKER_TAX_ID,
  TOWID,
  REBILL_FLAG,
  CURRENT_BILL_STATUS_ID,
  PRIORITY_NUM,
  UPDATE_USER,
  UPDATE_DATE,
  REFERRING_PROVIDER_NAME,
  BILL_TOTAL_AMT,
  DRG_CODE,
  BILL_PACKAGE_QUANTITY,
  WORKFLOW_BYPASS_CODE,
  CURRENT_RESPONSIBLE_PARTY_ID,
  CURRENT_STATUS_EFF_DATE,
  CURRENT_UPDATE_USER,
  CURRENT_NEXT_REVIEW_DATE,
  UB_BILL_TYPE_CODE,
  CBP_ADMISSION_TYPE_CODE,
  IMAGE_SEQ_NUM,
  CLAIM_ID,
  BILL_TYPE_CODE,
  PROVIDER_LOCATION_SEQ_NUM,
  CURRENT_BILL_STATUS_CODE,
  CBP_BILL_ENTRY_SOURCE_CODE)
VALUES (
  :Id,
  :UpdateNumber,
  :PatientReferenceNumber,
  :MailRoomReceivedDate,
  :SaifClaimNumber,
  :SaifClaimCheckCharacter,
  :WorkerFirstName,
  :WorkerMiddleInitial,
  :WorkerLastName,
  :WorkerPhoneNumber,
  :WorkerBirthDate,
  :WorkerAddressLine1,
  :WorkerAddressLine2,
  :WorkerAddressLine3,
  :WorkerAddressLine4,
  :GenderCode,
  :PatientAccountNumber,
  :ProviderTaxId,
  :ProviderAddressLine1,
  :ProviderAddressLine2,
  :ProviderCityName,
  :ProviderStateCode,
  :ProviderZipCode,
  :ProviderLicenseNumber,
  :AdmissionDate,
  :DischargeDate,
  :StatementFromDate,
  :StatementThruDate,
  :WorkerTaxId,
  :TowId,
  :IsRebill,
  :CurrentBillStatusId,
  :PriorityNumber,
  :UpdateUser,
  :UpdateDate,
  :ReferringProviderName,
  :BillTotalAmount,
  :DrgCode,
  :BillPackageQuantity,
  :WorkflowBypassCode,
  :CurrentResponsiblePartyId,
  :CurrentStatusEffectiveDate,
  :CurrentUpdateUser,
  :CurrentNextReviewDate,
  :UbBillTypeCode,
  :CbpAdmissionTypeCode,
  :ImageId,
  :ClaimId,
  :BillType,
  :BillingProvider,
  :BillStatus,
  :BillEntrySource)

Translation notes: - :Id is the value fetched from CBP_BILL_SEQ (GeneratedBy.Native("CBP_BILL_SEQ") on Oracle). - :UpdateNumber is the initial version value NHibernate seeds on insert (version column mapped with Version(...)). The clone sets UpdateNumber = 0 (BillRegenerator.cs:162); NHibernate seeds the version on save. - Reference bind variables (:<Property>) carry the referenced entity's identifier value. - :IsRebill is bound as 'Y'/'N' (YesNoType). - Clone is built in RegenBillData (BillRegenerator.cs:123-170+). Claim = bill.Claim (line 128) has no mapping in CbpBillMap — only ClaimId → CLAIM_ID is persisted. - Cascades (CbpBillMap.cs:74-95, all AllDeleteOrphan): Diagnoses, Services (SQL-008), Procedures, StatusHistory, Notes. Child INSERTs are not fabricated here; procedure/diagnosis cascades have no SQL ID in this batch. - The following clone.ModifyStatus(...) (lines 35-43) produces SQL-006 (and SQL-002 when note text is supplied).

Operations and processes

• OP-DOM-16 BillRegenerator.Regenerate — clones the bill and sets status Entered with responsible party SYSTEM (see Operations and processes)

Business rules

• DOM-BR-160

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/Claims/Bills/BillRegenerator.cs