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

MDClarity · task primitive

LoadVisits

Overview

LoadVisitsTask loads and prepares visits for pricing estimation by executing custom SQL queries to populate the VisitBatch table. It retrieves visit demographics, optionally adds charges, coverages, and service types, then synchronizes with existing visit data and includes visits from the reprocess queue.

Use this task when: You need to load scheduled visits into the batch processing pipeline with flexible custom queries, particularly when integrating with scheduling systems or databases with custom schemas.

Don't use this task when: You're importing visit data from HL7 feeds (use LoadHL7FilesTask), need to reprocess existing visits without reloading (use ReprocessChangedConfigurationsTask), or want to process visits already in the batch without reloading.

Parameters

Parameter Type Required Default Description
VisitQuery string Yes None SQL query returning visit data. Must include columns matching Visit entity properties (e.g., Key, SourceID, ScheduledTime, Facility, PatientKey, Insurance). See field instructions for full list of supported columns.
ChargeQuery string No None SQL query returning charges for visits. Must select SourceID, ChargeNumber, ChargeTypeID, Units, Codes. Joins to visits by SourceID. If omitted, visits load with zero charge only.
ServiceTypeQuery string No None SQL query returning service types for quote generation. Must select SourceID, ServiceType. Creates quotes for each service type and COB combination.
CoverageQuery string No None SQL query returning insurance coverages. Must select SourceID, Insurance, COB, and optionally MemberID, GroupID, GroupName, SourceInsuranceID, CoverageStatus. Updates or adds coverages to visits.
AddZeroCharge bool No true When true, automatically adds a zero-charge (ChargeType.ZeroID) as the first charge for each visit if not already present. Required for proper pricing calculations.

Execution Flow

flowchart TD
    A[Start LoadVisits] --> B[Truncate VisitBatch, QuoteBatch, CustomPropertyMapBatch tables]
    B --> C[Execute VisitQuery]
    C --> D[Load visits from query results]
    D --> E[Track which columns were provided]
    E --> F[Sync with existing Visit table data]
    F --> G{ChargeQuery provided?}
    G -->|Yes| H[Execute ChargeQuery for each visit by SourceID]
    G -->|No| I{AddZeroCharge = true?}
    H --> J[Add queried charges to visits]
    I -->|Yes| K[Add zero charge to each visit]
    I -->|No| L[Skip charge processing]
    J --> M{CoverageQuery provided?}
    K --> M
    L --> M
    M -->|Yes| N[Execute CoverageQuery]
    M -->|No| O{ServiceTypeQuery provided?}
    N --> P[Load all coverages into memory]
    P --> Q[Update/add coverages to visits by SourceID]
    Q --> O
    O -->|Yes| R[Execute ServiceTypeQuery]
    O -->|No| S[Check VisitReprocessQueue]
    R --> T[Load service types into memory map]
    T --> U[Create quotes for each service type x COB]
    U --> S
    S --> V[Load visits from reprocess queue]
    V --> W[Merge queue visits with batch visits]
    W --> X[Save all visits to VisitBatch table]
    X --> Y[Fill CustomPropertyMapBatch]
    Y --> Z[Load existing quotes for visits without new quotes]
    Z --> AA[End]

    style B fill:#ffe1e1
    style D fill:#e1f5ff
    style F fill:#e1f5ff
    style J fill:#e1f5ff
    style K fill:#e1f5ff
    style Q fill:#e1f5ff
    style U fill:#e1f5ff
    style X fill:#e1f5ff

Processing Algorithm

The task follows a multi-phase approach to build complete visit data:

  1. Visit Loading Phase: Execute VisitQuery and populate visit entities, tracking which properties were provided by the query
  2. Synchronization Phase: For visits with existing Key values, load the existing visit from the Visit table and merge only the properties NOT provided by the query (preserving existing data for unprovided fields)
  3. Charge Addition Phase:
    • If AddZeroCharge = true, insert a zero charge at position 0 for visits without one
    • If ChargeQuery provided, execute it once per visit using the visit's SourceID, and replace charges 1+ with query results
  4. Coverage Update Phase: If CoverageQuery provided, load all coverages into memory, then update visits by SourceID, removing coverages not in the query results
  5. Quote Generation Phase: If ServiceTypeQuery provided, create quotes for each (ServiceType, COB) combination per visit
  6. Reprocess Queue Merge: Add visits from VisitReprocessQueue that aren't already in the batch

Example Execution

Given 3 visits where Visit 1 and 2 have existing Keys:

Result: All 3 visits are saved with merged data, preserving existing information not specified in the query.

Usage Examples

Example 1: Load visits with basic demographics only

var task = new LoadVisitsTask
{
    VisitQuery = @"
        select
            VisitID as [Key],
            VisitID as SourceID,
            ScheduledDateTime as ScheduledTime,
            FacilityCode as Facility,
            PatientMRN as PatientKey,
            PrimaryInsuranceCode as Insurance
        from SchedulingSystem.Appointments
        where ScheduledDateTime >= GETDATE()
        and AppointmentStatus = 'Scheduled'",
    AddZeroCharge = true // Will add zero charge automatically
};

Example 2: Load visits with charges from CPT code table

var task = new LoadVisitsTask
{
    VisitQuery = @"
        select
            v.VisitID as [Key],
            v.VisitID as SourceID,
            v.ScheduledDateTime as ScheduledTime,
            v.FacilityCode as Facility,
            v.PatientMRN as PatientKey,
            v.PrimaryInsuranceCode as Insurance
        from SchedulingSystem.Visits v",

    ChargeQuery = @"
        select
            VisitID as SourceID,
            ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY LineNumber) as ChargeNumber,
            ChargeType as ChargeTypeID,
            Quantity as Units,
            CPTCode as Codes
        from SchedulingSystem.VisitCharges",

    AddZeroCharge = true
};

Example 3: Load visits with multiple coverages and service types

var task = new LoadVisitsTask
{
    VisitQuery = @"
        select
            VisitID as [Key],
            VisitID as SourceID,
            ScheduledDateTime as ScheduledTime,
            FacilityCode as Facility,
            PatientMRN as PatientKey
        from SchedulingSystem.Visits",

    CoverageQuery = @"
        select
            c.VisitID as SourceID,
            c.InsuranceCode as Insurance,
            c.COB,
            c.MemberID,
            c.GroupNumber as GroupID
        from SchedulingSystem.VisitCoverages c
        order by c.VisitID, c.COB",

    ServiceTypeQuery = @"
        select
            VisitID as SourceID,
            ServiceTypeCode as ServiceType
        from SchedulingSystem.VisitServiceTypes",

    AddZeroCharge = true
};

Example 4: Incremental load of visits scheduled in date range

var task = new LoadVisitsTask
{
    VisitQuery = $@"
        select
            VisitID as [Key],
            VisitID as SourceID,
            ScheduledDateTime as ScheduledTime,
            FacilityCode as Facility,
            PatientMRN as PatientKey,
            PrimaryInsuranceCode as Insurance
        from SchedulingSystem.Visits
        where ScheduledDateTime between '{DateTime.Today:yyyy-MM-dd}'
          and '{DateTime.Today.AddDays(30):yyyy-MM-dd}'
        and Status = 'Scheduled'",

    AddZeroCharge = true
};

Related Tasks Comparison

Task Use Case Query Flexibility Data Source Synchronization
LoadVisits Custom SQL-based visit loading Full SQL flexibility Any queryable database Merges with existing visits
LoadHL7FilesTask Import from HL7 message files Fixed HL7 format HL7 ADT/SIU/DFT files Creates new visits
ReprocessChangedConfigurationsTask Queue existing visits for reprocessing No queries (uses existing data) Visit table Adds to reprocess queue
AutoApplyBundlesToVisitBatchTask Apply bundles to batch visits No queries (operates on VisitBatch) VisitBatch table No synchronization

Performance Considerations

Database Impact

Optimization Tips

Scenario Recommendation Rationale
Large visit counts Use indexed columns in WHERE clauses and avoid SELECT * Reduces query execution time and network transfer
Per-visit charge lookups Consider embedding charges in VisitQuery using PIVOT or FOR XML if possible Eliminates N+1 query pattern (one query per visit)
Multiple coverages per visit Ensure CoverageQuery has index on SourceID Improves join performance during coverage updates
Service type explosion Limit service types to those actually needed Each service type creates quotes for each COB (multiplicative)
Reprocessing existing visits Use Key column in VisitQuery to trigger synchronization Preserves existing charges, quotes, and custom properties
Frequent execution Schedule during off-hours Truncate operations briefly lock tables

Error Handling

The task uses fail-fast error handling for critical operations but continues processing where appropriate:

// Charge validation - fail fast if invalid
if (chargeNumber != visit.Charges.Count)
{
    throw new Exception("Invalid charge number for visit: " + visit.Key);
}

Important Behaviors:

Common Pitfalls

Issue Problem Solution
VisitQuery missing required columns Task fails with "column not found" error Include at minimum: SourceID, ScheduledTime, Facility, PatientKey. See VisitField for full list.
ChargeNumber not sequential Exception: "Invalid charge number for visit" ChargeQuery must return ChargeNumber as 1, 2, 3... for each visit. Use ROW_NUMBER() to generate sequential numbers.
ChargeQuery returns no results Visits load with only zero charge (if enabled) Verify SourceID values match between VisitQuery and ChargeQuery. Check for NULL SourceID values.
Duplicate COBs in CoverageQuery Warning logged, first COB kept, duplicates ignored Ensure CoverageQuery returns one row per (SourceID, COB) combination.
ServiceTypeQuery creates too many quotes Performance degrades, database bloat Each service type × each COB = one quote. Limit service types to those needed for estimation.
VisitQuery includes Key but visit doesn't exist Visits treated as new (Key not found in Visit table) Verify Key values exist in Visit table. Consider omitting Key for new visits (system generates automatically).
Existing charges/quotes lost Synchronization overwrites data provided in queries Only provide columns in VisitQuery that you want to update. Omitted columns preserve existing data.
AddZeroCharge = false breaks pricing Pricing calculations fail without zero charge Always use AddZeroCharge = true unless you provide zero charge explicitly in ChargeQuery.

Code References