Legacy Pipeline Atlas worker-1snapshot 2026-07-15

HL7 real-time feed · ClarityFlow pipeline

RIA · Flow-HL7-Processing-AutoSendLettersForFutureVisits

Daily processing job for HL7 AutoSendLettersForFutureVisits

pipeline RIA/Flow-HL7-Processing-AutoSendLettersForFutureVisits · db RIA_ProdMirror · updated 2026-07-10
classification: flow · 10 Flow markers

trigger

How it's scheduled

Daily batch — once at 19:01

The HL7 real-time feed arrives as a scheduled drop, so one overnight pass is enough: it ingests the day's file and works the whole batch to check eligibility, price the good-faith estimate, and send the patient letter. A tighter loop would just re-scan the same file.

Scheduled taskCadenceServerStateCoordination
\MDC\HL7 - Processing - RIA - Nightlydaily at 19:01prod-db-3enabledstandalone

shape

Against the canonical Flow spine

Every Flow pipeline is a variation on one shape. Lit stages are the ones this pipeline runs; the labels match the capability lens.

Ingest
HL7 real-time feed
→
Eligibility & benefits
EligibilityBenefitsDiscovery
→
Estimate
BundlesEstimate
→
Letters
LettersFuture
→
Work queue & sync
DashboardsSync

computed concepts · from the SQL

What it computes

Business concepts mined from this pipeline's SQL text — the meaning behind the typed steps. Each links to the concept lens (who else computes it).

Coverage statusBundlesCPT / procedure logic

task tree

What the pipeline runs

Tasks in order, each with what it does — typed stepsfrom the MDClarity task docs (click to open the reference), sub-jobsyou can expand inline (▶) to reveal their own tasks, and SQL steps summarized by Gemini (✨). Every step shows the tables it writes/reads and which earlier step it feeds from(sqlglot lineage).

flow

Flow-HL7-Processing-AutoSendLettersForFutureVisits

4 tasks · RIA
sub-job / Flow action SQL / native step▶ expand a sub-job inline · ↗ open it as its own page · a typed step opens its doc
  1. Recreate Bundle mapping tablesqlflow_hl7_processing_autosendlettersforfuturevisits__recreate_bundle_mapping_table.sql
    ✨ Builds a mapping table by extracting visit, insurance, and service code details from configuration data for use in calculations.
    writes TransformBundleMap · reads BundleTransformBundleMap
  2. ▶Flow-HL7-Transform-Nightlysub-job · 4↗HL7 Transformation Layer
    1. TruncateVisitDelta and OrderSourceIDDeltasqlflow_hl7_transform_nightly__truncatevisitdelta_and_ordersourceiddelta.sql
      ✨ Clears the temporary tracking tables to prepare the pipeline for a fresh processing cycle.
      writes OrderSourceIDDeltaVisitDelta
    2. Recalculate Changed Bundlessqlflow_hl7_transform_nightly__recalculate_changed_bundles.sql
      ✨ Identifies and recalculates expected charges from service bundles for future visits to ensure accurate cost estimates.
      writes OrderSourceIDDeltaVisitDelta · reads BundleChargeTypeHL7OrderImportTransformVisitChargeTransformInsuranceMapServiceTypeTransformOrderSourceIDMapTransformVisitTransformVisitInsurance
      ↳ feeds from ImportTransformVisitCharge, TruncateVisitDelta and OrderSourceIDDelta
    3. ImportTransformVisitChargesqlflow_hl7_transform_nightly__importtransformvisitcharge.sql
      ✨ Builds a staging table of visit charges by extracting and formatting procedure codes and units from HL7 order bundles.
      writes ImportTransformVisitCharge · reads BundleChargeTypeHL7OrderImportTransformVisitChargeOrderSourceIDDeltaTransformInsuranceMapServiceTypeTransformOrderSourceIDMapTransformVisitInsurance
      ↳ feeds from Recalculate Changed Bundles, TruncateVisitDelta and OrderSourceIDDelta
    4. MergeTransformVisitChargesqlflow_hl7_transform_nightly__mergetransformvisitcharge.sql
      ✨ Synchronizes the visit charge transformation table by inserting, updating, or deleting records based on the latest imported charge data.
      writes TransformVisitCharge · reads ImportTransformVisitCharge
      ↳ feeds from ImportTransformVisitCharge
  3. ▶Flow-HL7-Processing-LoadVisits-Nightlysub-job · 23↗
    1. Load TempBatchVisitsqlflow_hl7_processing_loadvisits_nightly__load_tempbatchvisit.sqldisabled
      ✨ Identifies visits requiring insurance eligibility checks or re-checks based on service types and proximity to the scheduled appointment date.
      writes TempBatchVisitVisitEligServiceTypesVisitServiceTypes · reads DimensionMemberTempBatchVisitTransformVisitTransformVisitInsTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisitEligServiceTypesVisitServiceTypes
    2. Augment Visit Deltasqlflow_hl7_processing_loadvisits_nightly__augment_visit_delta.sql
      ✨ Updates the change tracking table with future visits when insurance mappings change to ensure updated coverage information is processed.
    3. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess visits
    4. ReprocessChangedConfigs-AugmentVisitDeltasqlflow_hl7_processing_loadvisits_nightly__reprocesschangedconfigs_augmentvisitdelta.sql
      ✨ Flags visits requiring reprocessing from the queue and adds them to the change tracking table for updated data loading.
      writes VisitDeltaVisitKeyLookup · reads VisitDeltaVisitKeyLookup
    5. LoadVisitBatchLoadVisitBatch
      Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
      writes QuoteBatchVisitBatch · reads FacilityInsuranceMapPatientTransformVisitTransformVisitChargeTransformVisitInsuranceVisitVisitDelta
      ↳ feeds from ReprocessChangedConfigs-AugmentVisitDelta, Sync Visits and Quotes, Update Patient Email for Commercial Insurance
      custom visit querycustom charge queryauto service type querycustom coverage queryadd zero charge
    6. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatchdisabled
      Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
      DISABLED
    7. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      auto query
    8. Re-Check Benefitsflowdisabled
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      · reads EligibilityStatusMapEligibilityTxnTempBatchVisitVisitBatch
      custom queryDISABLED
    9. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    10. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querycalculate chargesbatch mode
    11. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    12. CheckVisitGFEComplianceBatchCheckVisitGFEComplianceBatchdisabled
      Audits Good Faith Estimate compliance for batch visits and identifies records requiring resends based on federal timing regulations.
      DISABLED
    13. Update Patient Email for Commercial InsuranceUpdateEntities
      writes Patient · reads VisitBatchpatient
      ↳ feeds from LoadVisitBatch
      entity type=Patientauto query
    14. ▶Load-CustomProperty-CoverageStatussub-job · 3↗
      1. Load Primary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=PrimaryCoverageStatusauto querybatch mode
      2. Load Secondary Coverage StatusUpdateCustomPropertyFromQuery
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=SecondaryCoverageStatusauto querybatch mode
      3. Load Tertiary Coverage StatusUpdateCustomPropertyFromQuerydisabled
        · reads CustomPropertyMapEligibilityTxnVisitBatchVisitCoverageBatch
        entity=Visitcustom property key=TertiaryCoverageStatusauto querybatch modeDISABLED
    15. AutoSendLettersAutoSendLetters
      Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
    16. AutoSendLettersForFutureVisitsAutoSendLettersForFutureVisitsdisabled
      Generates and sends cost estimate letters for upcoming scheduled visits based on patient preferences and configured qualification criteria.
      DISABLED
    17. Load TransformVisitInsuranceCoverageStatussqlflow_hl7_processing_loadvisits_nightly__load_transformvisitinsurancecoveragestatus.sql
      ✨ Updates insurance coverage statuses by analyzing recent eligibility transactions and mapping results to standardized active or inactive states.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
      ↳ feeds from LoadVisitBatch
    18. Sync Visits and QuotesSyncBatch
      Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
      writes CustomPropertyMapQuoteVisitVisitChangeEvent · reads CustomPropertyMapBatchQuoteBatchVisitBatchVisitChangeEventBatch
      ↳ feeds from LoadVisitBatch
      sync quotesync visitsync custom property mapsync visit change event
    19. ExportBatchVisitEligibilityflowdisabled
      Push the batch to the app / export results.
      · reads EligibilityTxnVisitBatch
      custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchiveDISABLED
    20. SearchForInsuranceflowdisabled
      Find coverage for visits missing insurance.
      · reads EligibilityTxnBatch
      custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
    21. Generate-Reportsub-jobdisabled
    22. ▶Flow-HL7-Processing-LoadVisitFactsub-job · 1↗
      1. Load VisitFactsqlflow_hl7_processing_loadvisitfact__load_visitfact.sql
        ✨ Updates the visit fact table with current status, letter activity, eligibility results, and payment totals for recent appointments.
        writes VisitFactVisitFactDelta · reads EligibilityTxnPatientLetterPaymentTxnVisitVisitCoverageVisitFactDelta
    23. Update-Dashboard-Tablessub-jobdisabled
  4. AutoSendLettersForFutureVisitsAutoSendLettersForFutureVisits
    Generates and sends cost estimate letters for upcoming scheduled visits based on patient preferences and configured qualification criteria.

markers

Why it classified as flow

Front-of-house actions found anywhere in the tree (including nested sub-jobs).

CalculateVisits
Compute the good-faith estimate
CheckBenefits
Real-time eligibility (270/271) benefit lookup
ExportBatchVisitEligibility
Export eligibility results
Load TransformVisitInsuranceCoverageStatus
ClarityFlow front-of-house step
LoadVisitBatch
ClarityFlow front-of-house step
SearchForInsurance
Insurance discovery for visits missing coverage
auto_apply_bundles
Apply procedure bundles for the estimate
auto_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters
auto_send_letters_for_future_visits
Pre-send letters for upcoming visits