Select
  hec_cas_cases.CaseNumber,
  bil_billposition_transmitionstatuses.TransmitionCode,
  bil_billposition_transmitionstatuses.IsActive,
  bil_billposition_transmitionstatuses.TransmitionStatusKey
From
  hec_cas_cases Inner Join
  hec_cas_case_billcodes
    On hec_cas_cases.HEC_CAS_CaseID = hec_cas_case_billcodes.HEC_CAS_Case_RefID Inner Join
  hec_bil_billposition_billcodes
    On hec_cas_case_billcodes.HEC_BIL_BillPosition_BillCode_RefID = hec_bil_billposition_billcodes.HEC_BIL_BillPosition_BillCodeID Inner Join
  hec_bil_billpositions
    On hec_bil_billposition_billcodes.BillPosition_RefID = hec_bil_billpositions.HEC_BIL_BillPositionID Inner Join
  bil_billposition_transmitionstatuses
    On hec_bil_billpositions.Ext_BIL_BillPosition_RefID = bil_billposition_transmitionstatuses.BillPosition_RefID
Where
  hec_cas_cases.CaseNumber = '00012548' And
  bil_billposition_transmitionstatuses.TransmitionStatusKey = 'aftercare'