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

HL7 real-time feed · ClarityFlow pipeline

SeaRad · Daily-Visit-Processing-HL7

pipeline SeaRad/Daily-Visit-Processing-HL7 · db SeaRad_ProdMirror · updated 2025-09-17
classification: flow · 8 Flow markers

trigger

How it's scheduled

Real-time loop — every 10 min (~144×/day)

New appointments land continuously on the HL7 real-time feed. Front-of-house work is time-boxed — the estimate and letter have to reach the patient before the visit — so the pipeline polls every 10 minutes to check eligibility, price the good-faith estimate, and send the patient letter within minutes of an appointment being booked, rather than waiting for an overnight batch. It stands down while Daily - Processing - SeaRad runs (the heavier nightly pass).

Scheduled taskCadenceServerStateCoordination
\MDC\HL7 - Processing - SeaRadevery 10 min (from 00:01)prod-db-1enabledyields to Daily - Processing - SeaRad

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
Estimate
→
Letters
Letters
→
Work queue & sync
Work queueDashboardsSync

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 statusFee schedule / contractCPT / procedure logic

Payers in SQLMedicaidMedicare

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

Daily-Visit-Processing-HL7

7 tasks · SeaRad
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. ▶Parse-HL7sub-job · 4↗
    1. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=SeaRad_ProdMirror-mdcssh-sftpremote directory=/HL7local directory={HL7Directory}
    2. LoadHL7FilesLoadHL7Files
      Parses and imports patient demographics, visits, and charges from HL7 message files located in a specified directory.
      message folder path={HL7Directory}message processed folder path={HL7Archive}add timestamp to file name
    3. Update HL7ProcessLog + HL7Delta + Visit + VisitChargesqlparse_hl7__update_hl7processlog_hl7delta_visit_visitcharge.sql
      ✨ Logs HL7 message processing and identifies new patient and order updates from archived clinical data for downstream integration.
      writes HL7DeltaHL7ProcessLogHL7ProcessLogArchiveKeys · reads HL7ArchiveHL7OrderHL7PatientHL7ProcessLogHL7ProcessLogArchiveKeysOrderSourceIDMapTransformPatient
    4. ▶Load-Transform-HL7sub-job · 12↗Update on [date] --> Added ECC=1 in Loadpatients task as per ticket# https://mdclarity.zendesk.com/agent/tickets/14141
      1. TransformPatientsqlload_transform_hl7__transformpatient.sql
        ✨ Updates patient demographic and contact information by extracting and standardizing data from incoming HL7 ADT and order messages.
        writes TransformPatient · reads HL7DeltaHL7OrderHL7PatientTransformPatient
      2. TransformPatientInsurancesqlload_transform_hl7__transformpatientinsurance.sql
        ✨ Identifies insurance changes from HL7 messages and updates standardized patient insurance records, including coordination of benefits and group details.
        writes PatientIDInsuranceDeltaTransformPatientInsurance · reads HL7DeltaHL7PatientInsuranceMapPatientIDInsuranceDeltaTransformPatientInsurance
      3. Update PatientDateAppointmentIDMapsqlload_transform_hl7__update_patientdateappointmentidmap.sql
        ✨ Builds a mapping between patients and scheduled dates to generate unique internal identifiers for incoming HL7 messages.
        writes PatientDateAppointmentIDMap · reads HL7DeltaPatientDateAppointmentIDMap
      4. Update OrderSourceIDDeltasqlload_transform_hl7__update_ordersourceiddelta.sql
        ✨ Updates order mappings and identifies appointments impacted by new messages, flagging those where all associated orders are cancelled.
        writes OrderSourceIDDeltaOrderSourceIDMap · reads HL7DeltaOrderSourceIDMapPatientDateAppointmentIDMapPatientIDInsuranceDeltaTransformVisit
        ↳ feeds from Merge TransformVisit, TransformPatientInsurance, Update PatientDateAppointmentIDMap
      5. Load ImportTransformVisitsqlload_transform_hl7__load_importtransformvisit.sql
        ✨ Loads visit details into a staging table, mapping facility, provider, and service type information for upcoming appointment processing.
        writes ImportTransformVisit · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMap
        ↳ feeds from Update OrderSourceIDDelta
      6. Merge TransformVisitsqlload_transform_hl7__merge_transformvisit.sql
        ✨ Merges staged visit data into the main transformation table by replacing existing records with updated appointment and facility information.
        writes TransformVisit · reads ImportTransformVisit
        ↳ feeds from Load ImportTransformVisit
      7. Load ImportTransformVisitInsurancesqlload_transform_hl7__load_importtransformvisitinsurance.sql
        ✨ Loads insurance coverage details from HL7 orders into a staging table, organizing primary, secondary, and tertiary payers for each visit.
      8. Merge TransformVisitInsurancesqlload_transform_hl7__merge_transformvisitinsurance.sql
        ✨ Updates the main insurance transformation table by replacing existing coverage records with the latest staged insurance information for each visit.
        writes TransformVisitInsurance · reads ImportTransformVisitInsurance
      9. Load ImportTransformVisitChargesqlload_transform_hl7__load_importtransformvisitcharge.sql
        ✨ Loads procedure charges into a staging table, cross-walking clinical codes to standard billing formats and service types for cost estimation.
        writes ImportTransformVisitCharge · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMapSeaRadCPTCrosswalk
        ↳ feeds from Update OrderSourceIDDelta
      10. Merge TransformVisitChargesqlload_transform_hl7__merge_transformvisitcharge.sql
        ✨ Merges staged procedure charges into the main transformation table, ensuring the most recent billing codes are associated with each visit.
        writes TransformVisitCharge · reads ImportTransformVisitCharge
        ↳ feeds from Load ImportTransformVisitCharge
      11. LoadPatientsLoadPatients
        Upsert patient records into the patient dimension.
        · reads PatientTransformPatient
        ↳ feeds from TransformPatient
        auto patient query
      12. Update HL7ProcessLogsqlload_transform_hl7__update_hl7processlog.sql
        ✨ Updates the HL7 process log to mark the most recent active processing task as completed.
        · reads HL7ProcessLog
  2. ▶Load-ALL-Medicare-Schedulessub-job · 9↗
    1. Load ContractMapsqlload_all_medicare_schedules__load_contractmap.sqldisabled
      ✨ Builds Medicare contract mappings for all facilities to define professional billing logic and effective coverage periods.
      writes ContractMap · reads ContractMapFacility
    2. ▶Load-Medicare-DME-Schedulessub-job · 3↗
      1. Load All Medicare DME Schedulessqlload_medicare_dme_schedules__load_all_medicare_dme_schedules.sql
        ✨ Loads Medicare Durable Medical Equipment fee schedules and facility mappings for the specified state into the system.
      2. LoadEntitiesFromQueryLoadEntitiesFromQuery
        Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
        writes Schedule · reads Schedulemapschedule
        ↳ feeds from Copy Medicare-DME Schedules to All Insurances
        entity type=Schedulecustom query
      3. Copy Medicare-DME Schedules to All Insurancessqlload_medicare_dme_schedules__copy_medicare_dme_schedules_to_all_insurances.sql
        ✨ Replicates Medicare Durable Medical Equipment schedules across all other insurance providers to ensure consistent pricing maps.
        writes ScheduleMap · reads DimensionMemberScheduleMap
    3. ▶Load-Medicare-Drug-Schedulessub-job · 3↗
      1. Load All Medicare Drug Schedulessqlload_medicare_drug_schedules__load_all_medicare_drug_schedules.sql
        ✨ Loads Medicare drug fee schedules and facility mappings into the system based on current Medicare pricing data.
      2. LoadEntitiesFromQueryLoadEntitiesFromQuery
        Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
        writes Schedule · reads Schedulemapschedule
        ↳ feeds from Copy Medicare-Drug Schedules to All Insurances
        entity type=Schedulecustom query
      3. Copy Medicare-Drug Schedules to All Insurancessqlload_medicare_drug_schedules__copy_medicare_drug_schedules_to_all_insurances.sql
        ✨ Copies Medicare drug pricing schedules to all other insurance providers within the schedule mapping configuration.
        writes ScheduleMap · reads DimensionMemberScheduleMap
    4. ▶Load-Medicare-Lab-Schedulessub-job · 11↗Update this with dbo.GetMedicareLabAmount when completed
      1. Load Medicare-Lab-2015LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads DimensionMemberFacilityMedicareScheduleDataLab
        schedule=Medicare-Lab-2015schedule type=Medicare-Lab
      2. Load Medicare-Lab-2016LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads DimensionMemberFacilityMedicareScheduleDataLab
        schedule=Medicare-Lab-2016schedule type=Medicare-Lab
      3. Load Medicare-Lab-2017LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads DimensionMemberFacilityMedicareScheduleDataLab
        schedule=Medicare-Lab-2017schedule type=Medicare-Lab
      4. Load Medicare-Lab-2018LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2023, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2018schedule type=Medicare-Lab
      5. Load Medicare-Lab-2019LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2023, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2019schedule type=Medicare-Lab
      6. Load Medicare-Lab-2020LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2023, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2020schedule type=Medicare-Lab
      7. Load Medicare-Lab-2021LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2022, Load Medicare-Lab-2023, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2021schedule type=Medicare-Lab
      8. Load Medicare-Lab-2022LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2023, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2022schedule type=Medicare-Lab
      9. Load Medicare-Lab-2023LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2024
        schedule=Medicare-Lab-2023schedule type=Medicare-Lab
      10. Load Medicare-Lab-2024LoadSchedule
        writes FeeScheduleRecordScheduleMap · reads CommonDataDimensionMemberFacilityFeeScheduleRecordScheduleMap
        ↳ feeds from Copy Medicare-Lab Schedules to All Insurances, Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2023
        schedule=Medicare-Lab-2024schedule type=Medicare-Lab
      11. Copy Medicare-Lab Schedules to All Insurancessqlload_medicare_lab_schedules__copy_medicare_lab_schedules_to_all_insurances.sql
        ✨ Replicates Medicare laboratory fee schedules to all other insurance providers to maintain uniform pricing maps.
        writes ScheduleMap · reads DimensionMemberScheduleMap
        ↳ feeds from Load Medicare-Lab-2015, Load Medicare-Lab-2016, Load Medicare-Lab-2017, Load Medicare-Lab-2018, Load Medicare-Lab-2019, Load Medicare-Lab-2020, Load Medicare-Lab-2021, Load Medicare-Lab-2022, Load Medicare-Lab-2023, Load Medicare-Lab-2024
    5. ▶Load-Medicare-RVU-Schedulessub-job · 4↗
      1. Load Medicare RVU Schedules Clinicsqlload_medicare_rvu_schedules__load_medicare_rvu_schedules_clinic.sql
        ✨ Calculates and loads Medicare clinic fee schedules based on Relative Value Units and regional conversion factors.
      2. Load Medicare RVU Schedules Hospitalsqlload_medicare_rvu_schedules__load_medicare_rvu_schedules_hospital.sqldisabled
        ✨ Builds and updates Medicare RVU fee schedules and facility mappings for hospital-based services using regional zip code data.
      3. LoadEntitiesFromQueryLoadEntitiesFromQuery
        Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
        writes Schedule · reads Schedulemapschedule
        ↳ feeds from Copy Medicare-RVU Schedules to All Insurances
        entity type=Schedulecustom query
      4. Copy Medicare-RVU Schedules to All Insurancessqlload_medicare_rvu_schedules__copy_medicare_rvu_schedules_to_all_insurances.sql
        ✨ Replicates Medicare RVU schedule mappings across all non-government insurance providers to standardize fee schedule assignments.
        writes ScheduleMap · reads DimensionMemberScheduleMap
    6. ▶Load-Endoscopy-Base-Schedulessub-job · 5↗Copy from SSR
      1. [DISABLED]Load Endoscopy-Base-2015LoadScheduledisabled
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataEndoscopyBase
        schedule=Endoscopy-Base-2015schedule type=Endoscopy-BaseDISABLED
      2. [DISABLED]Load Endoscopy-Base-2016LoadScheduledisabled
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataEndoscopyBase
        schedule=Endoscopy-Base-2016schedule type=Endoscopy-BaseDISABLED
      3. Load-From-Commondatasqlload_endoscopy_base_schedules__load_from_commondata.sql
        ✨ Loads Medicare endoscopy base schedules and facility mappings from common data for specific regional localities.
        writes ScheduleMapcodeschedulerecord · reads FacilitymedicareEndoscopyschedulemedicareEndoscopyscheduleMapschedulemap
        ↳ feeds from Copy Endoscopy-Base Schedules to All Insurances
      4. LoadEntitiesFromQueryLoadEntitiesFromQuery
        Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
        writes Schedule · reads Schedulemapschedule
        ↳ feeds from Copy Endoscopy-Base Schedules to All Insurances, Load-From-Commondata
        entity type=Schedulecustom query
      5. Copy Endoscopy-Base Schedules to All Insurancessqlload_endoscopy_base_schedules__copy_endoscopy_base_schedules_to_all_insurances.sql
        ✨ Copies Medicare endoscopy base schedules to all other insurance providers within the schedule mapping table.
        writes ScheduleMap · reads DimensionMemberScheduleMap
        ↳ feeds from Load-From-Commondata
    7. ▶Load-Multiple-Procedure-Schedulessub-job · 11↗Copy from SSR
      1. Load Multiple-Procedure-2014LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2014schedule type=Multiple-Procedure
      2. Load Multiple-Procedure-2015LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2015schedule type=Multiple-Procedure
      3. Load Multiple-Procedure-2016LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2016schedule type=Multiple-Procedure
      4. Load Multiple-Procedure-2017LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2017schedule type=Multiple-Procedure
      5. Load Multiple-Procedure-2018LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2018schedule type=Multiple-Procedure
      6. Load Multiple-Procedure-2019LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2019schedule type=Multiple-Procedure
      7. Load Multiple-Procedure-2020LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2020schedule type=Multiple-Procedure
      8. Load Multiple-Procedure-2021LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2021schedule type=Multiple-Procedure
      9. Load Multiple-Procedure-2022LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2021schedule type=Multiple-Procedure
      10. Load Multiple-Procedure-2023LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2023schedule type=Multiple-Procedure
      11. Load Multiple-Procedure-2024LoadSchedule
        writes CodeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataMultipleProcedure
        schedule=Multiple-Procedure-2024schedule type=Multiple-Procedure
    8. ▶Load-MidLevelProvider-Schedulessub-job · 10↗Copy from SSR
      1. Load Mid-Level Provider ScheduleLoadSchedule
        writes CodeScheduleRecordScheduleMap · reads FacilityInsuranceProviderProviderTaxonomy
        schedule=Mid-Level-Providersschedule type=Mid-Level-Providers
      2. Load Medicare Mid-Level-Charges 2016 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2016schedule type=Mid-Level-Charges
      3. Load Medicare Mid-Level-Charges 2017 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2017schedule type=Mid-Level-Charges
      4. Load Medicare Mid-Level-Charges 2018 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2018schedule type=Mid-Level-Charges
      5. Load Medicare Mid-Level-Charges 2019 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2019schedule type=Mid-Level-Charges
      6. Load Medicare Mid-Level-Charges 2020 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2020schedule type=Mid-Level-Charges
      7. Load Medicare Mid-Level-Charges 2021 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2021schedule type=Mid-Level-Charges
      8. Load Medicare Mid-Level-Charges 2022 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2022schedule type=Mid-Level-Charges
      9. Load Medicare Mid-Level-Charges 2023 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2023schedule type=Mid-Level-Charges
      10. Load Medicare Mid-Level-Charges 2024 scheduleLoadSchedule
        writes FeeScheduleRecordScheduleMap · reads ChargeTypeFacilityInsuranceMedicareScheduleDataRVU
        schedule=Medicare-Mid-Level-Charges-2024schedule type=Mid-Level-Charges
    9. Load Medicare Carveout Schedule 2019LoadSchedule
      writes FeeScheduleRecordScheduleMap · reads DimensionMemberFacilityImportMedicareCarveoutInsurance
      schedule=Medicare-Carveout-2019schedule type=Carve-Outs
  3. ▶Load-ALL-Medicaid-Schedulessub-job · 4↗
    1. ContractMapsqlload_all_medicaid_schedules__contractmap.sqldisabled
      ✨ Updates contract mappings for Medicaid by associating facilities with standardized professional fee schedules and effective date ranges.
      writes ContractMap · reads ContractMapFacility
    2. Load All WA Medicaid Schedulessqlload_all_medicaid_schedules__load_all_wa_medicaid_schedules.sql
      ✨ Loads regional Medicaid fee schedules and schedule mappings into the system for non-hospital facilities and specific service types.
      writes FeeScheduleRecordScheduleMap · reads CommonDataFeeScheduleRecordScheduleMap
      ↳ feeds from Copy Medicaid Schedules to All Insurances
    3. LoadEntitiesFromQueryLoadEntitiesFromQuery
      Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
      writes Schedule · reads ScheduleMap
      ↳ feeds from Copy Medicaid Schedules to All Insurances, Load All WA Medicaid Schedules
      entity type=Schedulecustom query
    4. Copy Medicaid Schedules to All Insurancessqlload_all_medicaid_schedules__copy_medicaid_schedules_to_all_insurances.sql
      ✨ Propagates Medicaid fee schedule mappings to other insurance providers within the system to ensure consistent pricing across similar plans.
      writes ScheduleMap · reads DimensionMemberScheduleMap
      ↳ feeds from Load All WA Medicaid Schedules
  4. ▶Populate-Dimensionssub-job · 4↗
    1. Load EligibilityInsurance Dimensionsqlpopulate_dimensions__load_eligibilityinsurance_dimension.sql
      ✨ Populates the EligibilityInsurance dimension with insurance providers that have valid benefits API references for automated eligibility checks.
      writes ImportDimensionMember · reads DimensionMemberInsurance
      ↳ feeds from Load State Dimensions, Load Time Dimensions
    2. Load QuoteInsurance Dimensionsqlpopulate_dimensions__load_quoteinsurance_dimension.sql
      ✨ Updates the QuoteInsurance dimension by identifying insurance carriers associated with active contract maps in the system.
      writes ImportDimensionMember · reads ContractMapInsurance
    3. Load Time Dimensionssqlpopulate_dimensions__load_time_dimensions.sql
      ✨ Builds time-based dimension members for days, months, and years to support temporal reporting across the pipeline.
      writes DimensionMember · reads TransformVisit
    4. Load State Dimensionssqlpopulate_dimensions__load_state_dimensions.sql
      ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
      writes DimensionMember · reads Facility
  5. ▶Load-Visitssub-job · 21↗
    1. Load VisitDeltasqlload_visits__load_visitdelta.sql
      ✨ Identifies new or updated visits with future appointment dates to determine which records require processing in the pipeline.
      writes VisitDelta · reads VisitDelta
      ↳ feeds from Augment Visit Delta
    2. Load TempBatchVisitsqlload_visits__load_tempbatchvisit.sql
      ✨ Flags visits requiring eligibility verification based on insurance details, service types, and scheduled appointment timing.
      writes TempBatchVisit · reads DimensionMemberEligibilityTxnTransformVisitTransformVisitInsuranceTransformVisitInsuranceCoverageStatusVisit
      ↳ feeds from Load TransformVisitInsuranceCoverageStatus, Sync Visits and Quotes
    3. Augment Visit Deltasqlload_visits__augment_visit_delta.sql
      ✨ Adds visit records requiring new insurance eligibility lookups to the change tracking table for processing.
      writes VisitDelta · reads TempBatchVisitVisitDelta
      ↳ feeds from Load TempBatchVisit, Load VisitDelta
    4. LoadVisitBatchLoadVisitBatch
      Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
      writes QuoteBatchVisitBatch · reads PatientTransformVisitTransformVisitChargeTransformVisitInsuranceVisitVisitDelta
      ↳ feeds from Augment Visit Delta, Load VisitDelta, Sync Visits and Quotes
      custom visit querycustom charge queryauto service type querycustom coverage queryadd zero charge
    5. CheckBenefitsflow
      Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
      · reads TempBatchVisitVisitBatch
      ↳ feeds from Load TempBatchVisit, LoadVisitBatch
      custom query
    6. 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
    7. AutoApplyBenefitsAutoApplyBenefits
      Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    8. CalculateVisitsflow
      Compute the good-faith estimate (CalculateVisits).
      · reads DimensionMemberVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querycalculate chargesbatch mode
    9. LogVisitChangeEventsBatchLogVisitChangeEventsBatch
      Compare batch visits against production records to detect and log specific property changes for auditing and compliance tracking.
    10. AutoSendLettersAutoSendLetters
      Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
    11. Load TransformVisitInsuranceCoverageStatussqlload_visits__load_transformvisitinsurancecoveragestatus.sql
      ✨ Updates insurance coverage statuses for visits by analyzing the most recent eligibility transaction results and error logs.
      writes TempEligibilityStatsTransformVisitInsuranceCoverageStatus · reads TempEligibilityStatsTransformVisitInsuranceVisitBatch
      ↳ feeds from LoadVisitBatch
    12. ▶Load-CustomProperty-CoverageStatussub-job · 2↗
      1. Load Primary Coverage StatusUpdateCustomPropertyFromQuery
        · reads TransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=PrimaryCoverageStatuscustom querybatch mode
      2. Load Secondary Coverage StatusUpdateCustomPropertyFromQuery
        · reads TransformVisitInsuranceCoverageStatusVisitBatch
        entity=Visitcustom property key=SecondaryCoverageStatuscustom querybatch mode
    13. Custom Property Has Medicare Part C CoverageUpdateVisitCustomPropertyFromQuerydisabled
      · reads EligibilityTxnVisitbatch
      custom visit update custom property queryDISABLED
    14. Custom Property Has Medicare Part C CoverageUpdateCustomPropertyFromQuery
      · reads CustomPropertyMapEligibilityTxnVisitBatch
      ↳ feeds from LoadVisitBatch, Sync Visits and Quotes
      entity=Visitcustom property key=HasMedicarePartCCoverageauto querybatch mode
    15. 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
    16. ExportBatchVisitEligibilityflow
      Push the batch to the app / export results.
      · reads EligibilityTxnVisitBatch
      ↳ feeds from LoadVisitBatch
      custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchive
    17. SearchForInsuranceflowdisabled
      Find coverage for visits missing insurance.
      · reads EligibilityTxnBatch
      custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
    18. ▶Generate-Reportsub-job · 4↗Generates the Daily Export report and pushed data into the Archive report for Load-Visits and Load-Visits-Reprocess
      1. Load TempEligibilityReportsqlgenerate_report__load_tempeligibilityreport.sql
        ✨ Builds a temporary table of eligibility results for specific visits and insurance plans to support reporting.
        writes TempEligibilityReport
      2. Load TempInsSearchResultssqlgenerate_report__load_tempinssearchresults.sql
        ✨ Loads active insurance search results into a temporary table to identify new coverage found during the eligibility process.
        writes TempInsSearchResults
      3. Generate Reportsqlgenerate_report__generate_report.sql
        ✨ Generates a comprehensive batch eligibility report by consolidating patient visit data with primary, secondary, and tertiary insurance verification results.
        writes BatchEligibility_ResultsReport
      4. Archive Reportsqlgenerate_report__archive_report.sql
        ✨ Updates the eligibility report archive by replacing existing visit records with the most recent verification results.
        writes BatchEligibility_ResultsReportArchive · reads BatchEligibility_ResultsReport
        ↳ feeds from Generate Report
    19. Custom PropertiesUpdateVisitCustomPropertyFromQuerydisabled
      · reads BatchEligibility_ResultsReportCustomPropertyMap
      auto visit update custom property queryDISABLED
    20. ▶Load-Visit-Factsub-job · 1↗
      1. Load VisitFactsqlload_visit_fact__load_visitfact.sql
        ✨ Updates the visit fact table by refreshing records with recent activity, including payments, eligibility checks, and patient communications.
        writes VisitFact · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisit
    21. ▶Update-Dashboard-Tablessub-job · 1↗
      1. UpdateDashboardTablesUpdateDashboardTables
        Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.
  6. Data-Integrity-Reportsub-jobdisabled
  7. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.

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_benefits
Auto-apply parsed benefits to visits
auto_send_letters
Send GFE / patient letters