SQL-016: CBP bill insert (CbpBill)¶

For each indexed ebill bill, GenerateCbpBills.Generate builds a new CbpBill entity (type, dates, worker data, priority/rebill flags) and adds it to the repository as the first step of CBP-bill generation.

Classification¶

Field	Value
SQL ID	SQL-016
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	`GenerateCbpBills`
Criteria property	N/A — ORM
Lifecycle method	`Generate`

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/Ebilling/GenerateCBPBills.cs:34

// Domain/SAIF.Domain/Claims/Ebilling/GenerateCBPBills.cs:29-34
var newCbpBill = GenerateBillData(uow, bill, ebillingQueryProvider, currentUser, billResults, ccclaimApiService);
GenerateBillServiceData(bill, newCbpBill, ebillingQueryProvider, errorConditionHandler, currentUser, billResults, serviceCodeManager);
GenerateBillProcedureData(uow, bill, newCbpBill, ebillingQueryProvider, currentUser, billResults);
GenerateBillDiagnosisData(bill, newCbpBill, ebillingQueryProvider, currentUser, billResults);

uow.GetRepository<CbpBill>().Add(newCbpBill);

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(...)). - Reference bind variables (:<Property>) carry the referenced entity's identifier value. - :IsRebill is bound as 'Y'/'N' (YesNoType). - The bill is built in GenerateBillData (GenerateCBPBills.cs:224+). - Cascades (CbpBillMap.cs:74-95): Services (SQL-017), Procedures, Diagnoses, StatusHistory, Notes — child INSERTs not fabricated here. - Lines 36-39 (ebill update) are SQL-032.

Operations and processes¶

OP-DOM-06 GenerateCbpBills.Generate — adds the new CbpBill and its services/procedures/diagnoses to the repository (see Operations and processes)

Business rules¶

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/Ebilling/GenerateCBPBills.cs