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

sub-job

PPRM · Load-Visits

Runs visit and eligibility processing

called by Daily-Visit-Processing

sub-job PPRM/Load-Visits · db PPRM_ProdMirror · updated 2025-09-17

task tree

What the sub-job 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).

sub-job

Load-Visits

18 tasks · PPRM
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. Insert TempVisitBatchInsertTimesqlload_visits__insert_tempvisitbatchinserttime.sql
    ✨ Creates a temporary table to record the current timestamp for tracking when a batch of visits was loaded.
    writes TempVisitBatchInsertTime
  2. LoadVisitBatchLoadVisitBatch
    Load scheduled visits and associated charges into the processing pipeline using custom SQL queries for pricing estimation and quote generation.
    writes QuoteBatchVisitBatch · reads DimensionMemberPatientTransformVisitTransformVisitInsuranceVisitVisitDeltatransformvisitchargevisitdelta
    ↳ feeds from Sync QuoteBatch,VisitBatch,CustomPropertyMapBatch
    custom visit querycustom charge queryauto service type queryauto coverage queryadd zero charge
  3. AutoApplyBundlesToVisitBatchAutoApplyBundlesToVisitBatchdisabled
    Applies matching charge bundles to batch visits based on facility and insurance to ensure consistent pricing across service types.
    DISABLED
  4. Load TempBatchVisitsqlload_visits__load_tempbatchvisit.sqldisabled
    ✨ Populates a temporary table with visit and insurance details to identify which appointments require insurance eligibility verification.
    writes TempBatchVisit
  5. CheckBenefitsflowdisabled
    Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
    · reads InsuranceServiceTypeMapSourceEventTypeServiceTypeMapTempBatchVisit
    custom queryDISABLED
  6. Re-Check Benefitsflowdisabled
    Verifies insurance eligibility and retrieves detailed benefits information for visits by making real-time API calls to insurance providers.
    · reads CommonDataEligibilityTxnInsuranceServiceTypeMapSourceEventTypeServiceTypeMapTempBatchVisitTempVisitBatchInsertTime
    custom queryDISABLED
  7. AutoApplyBenefitsAutoApplyBenefitsdisabled
    Populates insurance benefits like copays and deductibles on batch visits using data from eligibility verification transactions.
    DISABLED
  8. CalculateVisitsflow
    Compute the good-faith estimate (CalculateVisits).
    · reads DimensionMemberVisitBatch
    ↳ feeds from LoadVisitBatch
    custom querycalculate chargesbatch mode
  9. ▶Load-Custom-Propertiessub-job · 10↗Load custom properties from Load-Visits
    1. UpdateVisitCustomPropertyFromQueryUpdateVisitCustomPropertyFromQuerydisabled
      · reads InsuranceSearch_ResultTransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    2. Custom Properties: OtherActiveCoverageUpdateVisitCustomPropertyFromQuerydisabled
      · reads TransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    3. Custom Properties: OtherInactiveCoverageUpdateVisitCustomPropertyFromQuerydisabled
      · reads TransformVisitInsuranceCoverageStatusVisitBatch
      custom visit update custom property queryDISABLED
    4. Custom Properties: Update MostRecentBatch to clear the previous recordsUpdateVisitCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapVisit
      custom visit update custom property queryDISABLED
    5. Custom Properties: MostRecentBatchUpdateVisitCustomPropertyFromQuerydisabled
      auto visit update custom property queryDISABLED
    6. Custom Properties: OAS ReasonUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapsourceappointmentchaintransformcustompropertiesvisitbatchvisitdelta
      entity=Visitcustom property key=OASReasonauto querybatch modeDISABLED
    7. Custom Properties: Has BundleUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMapTransformVisitChargeVisitBatchvisitdelta
      entity=Visitcustom property key=HasBundleauto querybatch modeDISABLED
    8. Custom Properties: GFE ReasonUpdateCustomPropertyFromQuerydisabled
      · reads CustomPropertyMaptransformcustompropertiesvisitbatchvisitdelta
      entity=Visitcustom property key=GFEReasoncustom querybatch modeDISABLED
    9. Custom Properties: Set LetterPrinted to NoUpdateCustomPropertyFromQuery
      · reads CustomPropertyMapVisitBatch
      entity=Visitcustom property key=LetterPrintedcustom querybatch mode
    10. UpdateCustomPropertyFromQuery (GFEDueDate)UpdateCustomPropertyFromQuery
      · reads SourceAppointmentSourceAppointmentChainTransformVisitVisitBatch
      entity=Visitcustom property key=GFEDueDatecustom querybatch mode
  10. AutoSendLettersAutoSendLetters
    Generates and sends patient cost estimate letters for visits in the batch table based on configured qualification and communication rules.
  11. Sync QuoteBatch,VisitBatch,CustomPropertyMapBatchSyncBatch
    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
  12. SearchForInsuranceflowdisabled
    Find coverage for visits missing insurance.
    · reads EligibilityStatusMapEligibilityTxnBatch
    custom querytable name=InsuranceSearch_Resultbatch modeDISABLED
  13. ExportBatchVisitEligibilityflowdisabled
    Push the batch to the app / export results.
    · reads EligibilityTxnTempVisitBatchInsertTime
    custom querytable name=BatchEligibility_Resultsarchive table name=BatchEligibility_ResultsArchiveDISABLED
  14. ▶Generate-Reportsub-job · 5↗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 reporting table consolidating the latest insurance eligibility transaction details, member IDs, and coverage results for each visit.
      writes TempEligibilityReport · reads TempEligibilityReport
    2. Load TempInsSearchResultssqlgenerate_report__load_tempinssearchresults.sql
      ✨ Loads a temporary table with successful insurance search results to identify and rank new coverage found for patients.
      writes TempInsSearchResults
    3. Generate Reportsqlgenerate_report__generate_report.sql
      ✨ Generates the final eligibility report by consolidating primary, secondary, and tertiary insurance results with any newly discovered coverage information.
      writes BatchEligibility_ResultsReport
    4. Create BatchEligibility_ResultsReportArchive if doesn't existsqlgenerate_report__create_batcheligibility_resultsreportarchive_if_doesn_t_exist.sql
      ✨ Builds the archive table for storing historical insurance eligibility and benefit results if it does not already exist.
    5. Archive Reportsqlgenerate_report__archive_report.sql
      ✨ Updates the eligibility results archive by replacing existing records with the latest report data for specific visits and dates.
      writes BatchEligibility_ResultsReportArchive · reads BatchEligibility_ResultsReport
      ↳ feeds from Generate Report
  15. Load TransformVisitInsuranceCoverageStatussqlload_visits__load_transformvisitinsurancecoveragestatus.sqldisabled
    ✨ Updates insurance coverage status for visits by linking them to the most recent successful or failed eligibility verification transactions.
  16. Load VisitDetailsqlload_visits__load_visitdetail.sqldisabled
    ✨ Loads comprehensive visit details including patient demographics, provider information, and insurance coverage status into a reporting table.
    writes VisitDetail · reads BatchEligibility_ResultsArchiveCustomPropertyMapEligibilityTxnPatientTransformVisitVisitVisitDeltaVisitDetail
  17. ▶Load-Visit-Factsub-job · 2↗
    1. Create Visit Factsqlload_visit_fact__create_visit_fact.sql
      ✨ Creates the VisitFact table to store aggregated visit data, including scheduling, insurance, and payment status, if it does not exist.
    2. Load Visit Factsqlload_visit_fact__load_visit_fact.sql
      ✨ Refreshes the VisitFact table by deleting and re-inserting records for visits with recent updates, payments, eligibility checks, or letters.
      writes VisitFact · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisit
  18. UpdateDashboardTablesUpdateDashboardTables
    Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.