SQL-008: Create CbpBill

This ORM statement performs a create operation on CbpBill via BuildNewBillRecord, SaveBill, executed from Services/SAIF.Services/Claims/Bills/BillEntryService.cs (L210), Services/SAIF.Services/Claims/Bills/BillEntryService.cs (L295).

Classification

Field	Value
SQL ID	SQL-008
Operation type	Create
Execution method	ORM (IRepository.Add)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	CbpBill
Criteria property	N/A — ORM
Lifecycle method	BuildNewBillRecord, SaveBill

Parameters

Parameter	Direction	Type	Max length	Description
:Id	Input	long	N/A	Identifier from CBP_BILL_SEQ.NEXTVAL
:UpdateNumber	Input	int	N/A	Version column (Version()); NHibernate writes its initial value
:PatientReferenceNumber	Input	string	N/A
:MailRoomReceivedDate	Input	DateTime?	N/A
:SaifClaimNumber	Input	string	N/A
:SaifClaimCheckCharacter	Input	string	N/A
:WorkerFirstName	Input	string	N/A
:WorkerMiddleInitial	Input	string	N/A
:WorkerLastName	Input	string	N/A
:WorkerPhoneNumber	Input	string	N/A
:WorkerBirthDate	Input	DateTime?	N/A
:WorkerAddressLine1	Input	string	N/A
:WorkerAddressLine2	Input	string	N/A
:WorkerAddressLine3	Input	string	N/A
:WorkerAddressLine4	Input	string	N/A
:GenderCode	Input	string	N/A
:PatientAccountNumber	Input	string	N/A
:ProviderTaxId	Input	string	N/A
:ProviderAddressLine1	Input	string	N/A
:ProviderAddressLine2	Input	string	N/A
:ProviderCityName	Input	string	N/A
:ProviderStateCode	Input	string	N/A
:ProviderZipCode	Input	decimal?	N/A
:ProviderLicenseNumber	Input	string	N/A
:AdmissionDate	Input	DateTime?	N/A
:DischargeDate	Input	DateTime?	N/A
:StatementFromDate	Input	DateTime?	N/A
:StatementThruDate	Input	DateTime?	N/A
:WorkerTaxId	Input	string	N/A
:TowId	Input	long?	N/A
:IsRebill	Input	bool	N/A	Bound as 'Y'/'N' (YesNoType)
:CurrentBillStatusId	Input	long?	N/A
:PriorityNumber	Input	int	N/A
:UpdateUser	Input	string	N/A
:UpdateDate	Input	DateTime	N/A
:ReferringProviderName	Input	string	N/A
:BillTotalAmount	Input	decimal	N/A
:DrgCode	Input	string	N/A
:BillPackageQuantity	Input	int?	N/A
:WorkflowBypassCode	Input	string	N/A
:CurrentResponsiblePartyId	Input	string	N/A
:CurrentStatusEffectiveDate	Input	DateTime?	N/A
:CurrentUpdateUser	Input	string	N/A
:CurrentNextReviewDate	Input	DateTime?	N/A
:UbBillTypeCode	Input	string	N/A
:CbpAdmissionTypeCode	Input	string	N/A
:ImageId	Input	long?	N/A
:ClaimId	Input	long?	N/A
:BillType	Input	CbpBillTypeCode	N/A	Identifier of the referenced CbpBillTypeCode entity (References(x => x.BillType))
:BillingProvider	Input	ProviderLocation	N/A	Identifier of the referenced ProviderLocation entity (References(x => x.BillingProvider))
:BillStatus	Input	CbpBillStatusCode	N/A	Identifier of the referenced CbpBillStatusCode entity (References(x => x.BillStatus))
:BillEntrySource	Input	CbpBillEntrySourceCode	N/A	Identifier of the referenced CbpBillEntrySourceCode entity (References(x => x.BillEntrySource))

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 (identifier from sequence CBP_BILL_SEQ.NEXTVAL)

LINQ statement

Source: Services/SAIF.Services/Claims/Bills/BillEntryService.cs:210, Services/SAIF.Services/Claims/Bills/BillEntryService.cs:295.

// Services/SAIF.Services/Claims/Bills/BillEntryService.cs:210
unitOfWork.GetRepository<CbpBill>().Add(dataBill);

// Services/SAIF.Services/Claims/Bills/BillEntryService.cs:295
unitOfWork.GetRepository<CbpBill>().Add(dataBill);

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 sequence CBP_BILL_SEQ (GeneratedBy.Native("CBP_BILL_SEQ")) before the INSERT; NHibernate assigns it to CbpBill.Id. - UPDATE_NUM is the optimistic-concurrency version column (Version(x => x.UpdateNumber)); NHibernate sets its initial value on INSERT. - :IsRebill 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: BillType → BILL_TYPE_CODE (entity table CBP_BILL_TYPE_CODE not accessed); BillingProvider → PROVIDER_LOCATION_SEQ_NUM (entity table PROVIDER_LOCATION not accessed); BillStatus → CURRENT_BILL_STATUS_CODE (entity table CBP_BILL_STATUS_CODE not accessed); BillEntrySource → CBP_BILL_ENTRY_SOURCE_CODE (entity table CBP_BILL_ENTRY_SOURCE_CODE not accessed). - Cascade: HasMany(x => x.Diagnoses) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.Services) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.Procedures) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.StatusHistory) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Cascade: HasMany(x => x.Notes) is mapped with Cascade.AllDeleteOrphan(); any transient instances in that association are saved in their own statements (not fabricated here). - Two call sites (:210 and :295) produce the same INSERT shape. - BILL_TYPE_CODE is populated only through the References mapping; UB_BILL_TYPE_CODE is a separate Map column.

Operations and processes

• BillEntryService.BuildNewBillRecord — see Operations and processes
• BillEntryService.SaveBill — see Operations and processes

Business rules

• SVC-BR-BIL-080 BillTotalAmount NotNull - 'Error - Bill total amount is required.'
• SVC-BR-BIL-081 BillTypeCode NotNull - 'Error - Bill type is required.'
• SVC-BR-BIL-082 DrgCode ≤3 - 'Error - DRG code can be a maximum 3 characters.'
• SVC-BR-BIL-083 Claim number must be valid (ClaimQueryProvider.IsValidClaim) - 'Error - Claim not found.'---'error---claim-not-found.')
• SVC-BR-BIL-084 UbBillTypeCode numeric - 'Error - UB Bill Type Code is invalid. Must be numeric.'
• SVC-BR-BIL-085 CBPAdmissionTypeCode must be in the acceptable list - 'Error - CBP Admission Type Code is invalid.'
• SVC-BR-BIL-086
• SVC-BR-BIL-087 A bill with no existing row is created with status PEN, entry source CBE
• SVC-BR-BIL-087 A bill with no existing row is created with status PEN, entry source CBE

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

Sources

• Services/SAIF.Services/Claims/Bills/BillEntryService.cs (L210, L295)