Skip to content

SAIF.Domain database tables

SAIF.Domain never issues raw SQL: every table below is reached through NHibernate-mapped entities via IRepositoryFactory/IUnitOfWork GetRepository<T>().Add/Delete/Query(), with SQL generated at runtime from ClassMaps in Data/SAIF.Data.NHibernate/Mappings/**. The database is Oracle* and no table is accessed with an explicit schema qualifier in this library's source, so every row below is [unqualified]**.

Table catalog

Table Database Schema/Link CRUD Purpose Primary business object
CBP_BILL Oracle* [unqualified]** C Converted-bill-processing (CBP) bill header created from an indexed ebill or regenerated from an existing bill BillRegenerator, GenerateCbpBills
CBP_BILL_SERVICE Oracle* [unqualified]** C CBP bill service line items, inserted by cascade when a bill is created or regenerated BillRegenerator, GenerateCbpBills
CBP_BILL_STATUS Oracle* [unqualified]** C Bill status history row appended on every Bill.ModifyStatus call Bill
CBP_NOTE Oracle* [unqualified]** CD Audit notes attached to a bill or claim AuditNote
CERTIFICATE_HOLDER Oracle* [unqualified]** CU Policy certificate-holder record (create, update, expire, supersede) CertificateHolder
EBILL Oracle* [unqualified]** RU Electronic bill header read and updated across the ebilling assignment, indexing, CBP generation, and EDI-response lifecycle BillAssignment, BillServiceIndexer, EobBuilder, File277CaBuilder, File835Builder, GenerateCbpBills
EBILL_BATCH Oracle* [unqualified]** CU Ebilling batch header, its lifecycle status, and submission metadata Batch
EBILL_EOB_ADJUSTMENT Oracle* [unqualified]** C Explanation-of-benefits (EOB) payment adjustment rows EobBuilder
EBILL_EOB_REMITTANCE_ADVICE Oracle* [unqualified]** C EOB remittance-advice (RARC) rows EobBuilder
EBILL_ERROR_CONDITION Oracle* [unqualified]** C Error/pend conditions recorded during patient, provider, and bill-service indexing ErrorConditionHandler
EBILL_SERVICE Oracle* [unqualified]** C Ebill service line items parsed from an inbound 837 file File837Extractor
ENDORSE_CODE Oracle* [unqualified]** R Endorsement/document codes used to select renewal-packet output documents PacketDefinition
MCO_GSA_LOCATION Oracle* [unqualified]** R Managed-care organization (MCO) location referenced by the Guidewire-backed claim read model Claim
PROVIDER_LOCATION Oracle* [unqualified]** R Provider address/location record used for claim legal-representative lookup and EOB provider resolution Claim, EobBuilder

CBP_BILL

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Header record for a converted-bill-processing (CBP) bill, created either from an indexed ebill (OP-DOM-06) or by cloning an existing bill during regeneration (OP-DOM-16)
Access pattern Direct ORM (IRepository<CbpBill>.Add)
CRUD operations Create
Key columns used BILL_ID (primary key)
Sequence CBP_BILL_SEQ
Related tables CBP_BILL_SERVICE, CBP_BILL_STATUS
Business objects BillRegenerator, GenerateCbpBills

Operations referencing CBP_BILL

Business rules referencing CBP_BILL

SQL referencing CBP_BILL

CBP_BILL_SERVICE

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Service line items belonging to a CBP bill, added as children under Cascade.AllDeleteOrphan
Access pattern ORM cascade insert (no direct IRepository<CbpBillService>.Add call)
CRUD operations Create
Key columns used BILL_ID (parent CBP_BILL key)
Sequence N/A
Related tables CBP_BILL
Business objects BillRegenerator, GenerateCbpBills

Operations referencing CBP_BILL_SERVICE

Business rules referencing CBP_BILL_SERVICE

SQL referencing CBP_BILL_SERVICE

CBP_BILL_STATUS

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Status-history row appended every time a bill's status changes
Access pattern ORM cascade insert on CbpBill.StatusHistory
CRUD operations Create
Key columns used BILL_ID (parent CBP_BILL key)
Sequence BILL_STATUS_SEQ
Related tables CBP_BILL
Business objects Bill

Operations referencing CBP_BILL_STATUS

Business rules referencing CBP_BILL_STATUS

SQL referencing CBP_BILL_STATUS

CBP_NOTE

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Audit notes attached to a bill (category B) or a claim (category C)
Access pattern Direct ORM Add/Delete, plus cascade insert via CbpBill.Notes
CRUD operations Create, Delete
Key columns used BILL_ID (parent CBP_BILL key)
Sequence CBP_NOTE_SEQ
Related tables CBP_BILL
Business objects AuditNote

Operations referencing CBP_NOTE

Business rules referencing CBP_NOTE

SQL referencing CBP_NOTE

CERTIFICATE_HOLDER

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Policy certificate-holder record: create, update, expire, and supersede lifecycle
Access pattern Direct ORM Add, plus dirty-check flush updates
CRUD operations Create, Update
Key columns used Holder identifier (entity primary key)
Sequence CERT_HOLD_SEQUENCE
Related tables —
Business objects CertificateHolder

Operations referencing CERTIFICATE_HOLDER

Business rules referencing CERTIFICATE_HOLDER

SQL referencing CERTIFICATE_HOLDER

EBILL

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Electronic bill header tracked through assignment, indexing, CBP generation, EOB, and EDI-response statuses
Access pattern LINQ Query() for reads; dirty-check flush for status updates
CRUD operations Read, Update
Key columns used EBILL_SEQ_NUM, status code
Sequence N/A
Related tables —
Business objects BillAssignment, BillServiceIndexer, EobBuilder, File277CaBuilder, File835Builder, GenerateCbpBills

Operations referencing EBILL

  • OP-DOM-01 BillAssignment, OP-DOM-05 BillServiceIndexer.IndexBillServices, OP-DOM-06 GenerateCbpBills.Generate, OP-DOM-11 File277CaBuilder.Build277ResponseFile, OP-DOM-12 EobBuilder.GenerateEobs (see Operations and processes)

Business rules referencing EBILL

SQL referencing EBILL

EBILL_BATCH

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Ebilling batch header and lifecycle status (NEW, COMP, EXTR, IMAGE, INDEX, EOB, DLOAD, ACK, SETUP)
Access pattern Direct ORM Add; dirty-check flush for status updates
CRUD operations Create, Update
Key columns used Batch identifier, status code, type
Sequence EBILL_BATCH_SEQ
Related tables —
Business objects Batch

Operations referencing EBILL_BATCH

Business rules referencing EBILL_BATCH

SQL referencing EBILL_BATCH

EBILL_EOB_ADJUSTMENT

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Payment-adjustment rows generated for MCO fee and service-level EOB adjustments
Access pattern Direct ORM Add
CRUD operations Create
Key columns used Audit/service identifier
Sequence EBILL_EOB_ADJUSTMENT_SEQ
Related tables CBP_AUDITED_SERVICE (join column per NHibernate mapping, not otherwise read by this library)
Business objects EobBuilder

Operations referencing EBILL_EOB_ADJUSTMENT

Business rules referencing EBILL_EOB_ADJUSTMENT

SQL referencing EBILL_EOB_ADJUSTMENT

EBILL_EOB_REMITTANCE_ADVICE

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Remittance-advice (RARC) rows generated alongside EOB adjustments
Access pattern Direct ORM Add
CRUD operations Create
Key columns used Audit/service identifier
Sequence EBILL_EOB_REMIT_ADVICE_SEQ
Related tables CBP_AUDITED_SERVICE (join column per NHibernate mapping)
Business objects EobBuilder

Operations referencing EBILL_EOB_REMITTANCE_ADVICE

Business rules referencing EBILL_EOB_REMITTANCE_ADVICE

SQL referencing EBILL_EOB_REMITTANCE_ADVICE

EBILL_ERROR_CONDITION

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Error and pend conditions recorded during patient, provider, and bill-service indexing
Access pattern Direct ORM Add
CRUD operations Create
Key columns used Pended flag, provider-error flag
Sequence EBILL_ERROR_CONDITION_SEQ
Related tables —
Business objects ErrorConditionHandler

Operations referencing EBILL_ERROR_CONDITION

Business rules referencing EBILL_ERROR_CONDITION

SQL referencing EBILL_ERROR_CONDITION

EBILL_SERVICE

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Ebill service line items parsed from an inbound X12 837 file
Access pattern ORM cascade insert (Ebill.AddService)
CRUD operations Create
Key columns used EBILL_SEQ_NUM (parent EBILL key)
Sequence EBILL_SERVICE_SEQ
Related tables EBILL
Business objects File837Extractor

Operations referencing EBILL_SERVICE

Business rules referencing EBILL_SERVICE

SQL referencing EBILL_SERVICE

ENDORSE_CODE

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Endorsement/document codes evaluated to decide which output documents belong in a renewal packet
Access pattern LINQ Query()
CRUD operations Read
Key columns used CorrespondTypeCode, OutputDocumentType
Sequence N/A
Related tables —
Business objects PacketDefinition

Operations referencing ENDORSE_CODE

Business rules referencing ENDORSE_CODE

SQL referencing ENDORSE_CODE

MCO_GSA_LOCATION

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Managed-care organization (MCO) location resolved from a Guidewire MCO identifier for the Claim read model
Access pattern LINQ Query()
CRUD operations Read
Key columns used Id (matched against the Guidewire MCO ID)
Sequence N/A
Related tables —
Business objects Claim

Operations referencing MCO_GSA_LOCATION

SQL referencing MCO_GSA_LOCATION

PROVIDER_LOCATION

Field Value
Database Oracle*
Schema/Link [unqualified]**
Purpose Provider address/location record used both for the claim legal-representative lookup on Claim and for provider resolution during EOB generation
Access pattern LINQ Query()
CRUD operations Read
Key columns used Id
Sequence N/A
Related tables —
Business objects Claim, EobBuilder

Operations referencing PROVIDER_LOCATION

Business rules referencing PROVIDER_LOCATION

SQL referencing PROVIDER_LOCATION

Table relationship diagram

The following diagram shows the relationships discoverable in this library's ORM navigation and cascade mappings.

erDiagram
    CBP_BILL ||--o{ CBP_BILL_SERVICE : "cascade insert"
    CBP_BILL ||--o{ CBP_BILL_STATUS : "cascade insert"
    CBP_BILL ||--o{ CBP_NOTE : "cascade insert"

Additional joins used only for lookups (not persisted relationships) exist between PROVIDER_LOCATION and Claim/EobBuilder, and between MCO_GSA_LOCATION and Claim, but these are query-time filters rather than mapped foreign keys, so they are omitted from the diagram above*.

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

  • Data/SAIF.Data.NHibernate/Mappings/Claims/Bills/CbpBillMap.cs, CbpBillServiceMap.cs, CbpBillStatusMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Claims/Bills/CbpNoteMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Policies/Certificates/CertificateHolderMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Claims/Ebilling/EbillMap.cs, EbillBatchMap.cs, EbillEobAdjustmentMap.cs, EbillEobRemittanceAdviceMap.cs, EbillErrorConditionMap.cs, EbillServiceMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Policies/EndorseCodeMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Claims/MCOLocationMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/OutputDocuments/OutputDocumentTypeCodeMap.cs
  • Data/SAIF.Data.NHibernate/Mappings/Providers/ProviderLocationMap.cs
  • Domain/SAIF.Domain/Claims/Bills/BillRegenerator.cs
  • Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs, GenerateCBPBills.cs, File837Extractor.cs
  • Domain/SAIF.Domain/Model/Claims/Bills/Bill.cs, Model/Claims/Bills/Notes/AuditNote.cs
  • Domain/SAIF.Domain/Model/Claims/Ebilling/Batch.cs, ErrorConditionHandler.cs
  • Domain/SAIF.Domain/Model/Claims/Claim.cs
  • Domain/SAIF.Domain/Model/Policies/Certificates/CertificateHolder.cs
  • Domain/SAIF.Domain/Tasks/Documents/Packets/PacketDefinition.cs