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	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

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