---
title: "SQL-002: Read operation"
summary: SQL-002 documentation for SAIF.Business.Claim.Bill.AutomatedAudit.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-28
library: SAIF.Business.Claim.Bill.AutomatedAudit
reference_type: package
---

# SQL-002: Read operation

This document records the SQL used by `clsClaimBillHistory` in `SAIF.Business.Claim.Bill.AutomatedAudit`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-002 |
| **Operation type** | Read |
| **Execution method** | StringBuilder |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsClaimBillHistory` |
| **Criteria property** | `SQL` |
| **Lifecycle method** | `SQL` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ClaimID` | Input | Number | N/A | Binds ClaimID from active calling code or Oracle signature. |
| `:CurrentBillID` | Input | Number | N/A | Binds CurrentBillID from active calling code or Oracle signature. |
| `:XStartDate` | Input | Date | N/A | Binds XStartDate from active calling code or Oracle signature. |
| `:XEndDate` | Input | Date | N/A | Binds XEndDate from active calling code or Oracle signature. |
| `:StartDate` | Input | Date | N/A | Binds StartDate from active calling code or Oracle signature. |
| `:EndDate` | Input | Date | N/A | Binds EndDate from active calling code or Oracle signature. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CBP_BILL](../database-tables.md#cbp_bill) | READ | A, ADMISSION_DATE, ALLOWED_AMT, AND, ASV, AUDIT_SEQ_NUM, B, BIL, BILL_ID, BILL_ORIG_CODE, BILL_SERVICE_CODE, BILL_SERVICE_DATE |

## Sample SQL

```sql
Select BIL.BILL_ID as BillID, BIL.CURRENT_BILL_STATUS_CODE as StatusCode, BIL.ADMISSION_DATE as AdmissionDate, BIL.PROVIDER_LOCATION_SEQ_NUM as BillingProvLocID, BSV.BILL_SERVICE_SEQ_NUM as ServiceID, BSV.BILL_SERVICE_DATE as BilledServiceDate, BSV.BILL_SERVICE_TYPE_CODE as BilledServiceTypeCode, BSV.PROVIDER_SERVICE_CODE as BilledServiceCode, BSV.SERVICE_MODIFIER_CODE1 as BilledModifierCode1, BSV.SERVICE_MODIFIER_CODE2 as BilledModifierCode2, BSV.SERVICE_MODIFIER_CODE3 as BilledModifierCode3, BSV.SERVICE_MODIFIER_CODE4 as BilledModifierCode4, BSV.CHARGE_AMT as BilledChargeAmount, BSV.SERV_PROVIDER_LOC_SEQ_NUM as BilledServicingProvLocID, ASV.ORIG_BILL_ID as OriginalBillID, ASV.ORIG_AUDIT_SEQ_NUM as OriginalServiceID, ASV.BILL_ORIG_CODE as OriginalBillCode, ASV.SERV_PROVIDER_LOC_SEQ_NUM as ServicingProvLocID, ASV.CHARGE_AMT as ChargeAmount, ASV.ALLOWED_AMT as AllowedAmount, ASV.SERVICE_DATE as ServiceDate, COALESCE(ASV.BILL_SERVICE_CODE, ASV.NATIONAL_DRUG_CODE) as ServiceCode, ASV.NATIONAL_DRUG_CODE as NationalDrugCode, ASV.SERVICE_MODIFIER_CODE1 as ModifierCode1, ASV.SERVICE_MODIFIER_CODE2 as ModifierCode2, ASV.SERVICE_MODIFIER_CODE3 as ModifierCode3, ASV.SERVICE_MODIFIER_CODE4 as ModifierCode4, ASV.FEE_SCHEDULE_AMT as FeeScheduleAmount, ASV.BILL_SERVICE_TYPE_CODE as ServiceTypeCode 
FROM CBP_BILL BIL, CBP_BILL_SERVICE BSV, CBP_AUDITED_SERVICE ASV 
WHERE BIL.CLAIM_ID = :ClaimID 
  AND BIL.BILL_ID <> :CurrentBillID 
  AND BIL.CURRENT_BILL_STATUS_CODE NOT IN ('ENT', 'PEN', 'DIS') 
  AND BIL.BILL_ID = BSV.BILL_ID 
  AND ASV.BILL_ID = BIL.BILL_ID 
  AND BSV.BILL_SERVICE_SEQ_NUM = ASV.AUDIT_SEQ_NUM 
  AND ((BIL.STATEMENT_FROM_DATE > :XStartDate 
  AND BIL.STATEMENT_FROM_DATE < :XEndDate) 
  OR (BIL.STATEMENT_FROM_DATE <= :StartDate 
  AND BIL.STATEMENT_THRU_DATE >= :StartDate) 
  OR (BIL.STATEMENT_FROM_DATE <= :EndDate 
  AND BIL.STATEMENT_THRU_DATE >= :EndDate))Get Historical Bills 
  AND ServicesClaimIDCurrentBillIDStartDateEndDateXStartDateXEndDate
```

> **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
- `Business\SAIF.Business.Claim.Bill.AutomatedAudit\AuditUtilities\clsClaimBillHistory.vb`