computed concept · mined from SQL · both
CPT / procedure logic
Procedure-code / modifier logic (pricing, grouping, or filtering by CPT).
Pipelines
33/53
compute it in SQL
Customers
23
CCP, Commonwealth, ECP, EugeneGI, EyeSouth, HeatonEye…
Distinct SQL
138
distinct implementations
the SQL
Steps that compute it
The concrete SQL steps whose text computes cpt / procedure logic, in plain language (✨ Gemini gloss), most-reused first. The count is how many steps across all trees run this same SQL; the link opens an example pipeline's task tree.
Loads and updates the Modifier dimension by collecting unique modifier codes from multiple charge record fields.
Loads the procedure modifier dimension by extracting unique codes from account charges and mapping them to their descriptive labels.
Builds the procedure code dimension by extracting codes from charge types and mapping them to standard descriptions and categories.
Loads granular charge-level details including procedure codes, modifiers, and payment metrics for individual service line analysis.
Builds a temporary report comparing estimated and actual insurance payments to track recovered revenue from account appeals.
Updates the procedure code dimension with member IDs, descriptions, and category hierarchies to support clinical service reporting.
Loads detailed remittance line items, mapping procedure codes, service dates, and payment amounts from electronic remittance advice.
Updates the modifier dimension with codes and descriptions sourced from common procedure modifier reference data.
Transforms and loads charge-level details, including procedure codes, modifiers, and diagnosis information, for updated accounts into the charge table.
Updates the master transaction table with new or modified records from the imported transaction data.
Updates the charge transaction table by filtering and synchronizing charge-type records from the main transaction source.
Updates the payment transaction table by filtering and synchronizing payment-type records from the main transaction source.
Updates the adjustment transaction table by filtering and synchronizing adjustment-type records from the main transaction source.
Updates the transfer transaction table by filtering and synchronizing transfer-in and transfer-out records from the main transaction source.
Synchronizes individual electronic remittance advice records from the import staging area to the source record table.
Synchronizes granular electronic remittance advice details from the import staging area to the source detail table.
Updates the source fee schedule procedure table with the latest pricing and procedure code mappings from imported data.
Updates the master procedure code list with current descriptions, groups, and effective dates from the source system.
Updates the source procedure code modifier table with the latest descriptions and metadata from the imported source data.
Builds the charge master by joining fee schedules and procedure codes to maintain standard pricing and modifier details.
Loads formatted charge type definitions from the charge master to prepare display names and billing codes for the system.
Updates the final charge type table with claim-level and procedure-specific billing configurations for use across the application.
Populates procedure categories in the dimension table based on the parent relationships established for procedure codes.
Loads unique billing modifiers from transformed charge records into the dimension member table.
Builds the procedure modifier dimension by identifying unique modifiers used in charge records and applying descriptive labels.
Loads transformed service line details for outbound claims, including procedure codes, modifiers, and rendering provider information.
Loads detailed service line charges by mapping procedure codes, modifiers, and units while generating unique keys for charge matching.
Builds the Procedure Category dimension by extracting unique parent categories from existing procedure code records.
Loads and updates procedure modifier dimension members from charge records to ensure consistent reporting across the pipeline.
Loads detailed charge information, including service dates, procedure codes, and balances, for encounters identified in the current processing delta.
Builds a unified table of professional and institutional claim service lines, extracting procedure codes, charges, and provider details.
Transforms claim service lines into individual charge records linked to accounts, capturing procedure codes, units, and financial amounts.
Maps historical claim service lines to their latest charge versions to establish a consistent charge family for longitudinal tracking.
Consolidates remittance payments and adjustments into a single transaction table, categorizing reason codes for financial reporting and analysis.
Matches normalized remittance service lines to specific charges within the charge family pool using tiered logic and procedure codes.
Links remittance transactions to specific charges or accounts, providing a detailed view of payments and adjustments at the charge level.
Builds the procedure category dimension by extracting unique parent groupings from existing procedure code members.
Identifies and recalculates expected charges from service bundles for future visits to ensure accurate cost estimates.
Refreshes a detailed recovery report by joining account charges with internal analysis and appeal status for ad-hoc financial tracking.
Builds a mapping table by extracting visit, insurance, and service code details from configuration data for use in calculations.
Builds a temporary report summarizing recovered amounts, appeal statuses, and payment details for patient accounts and charges.
Rebuilds the charge measures table by aggregating financial data and denial categories for current charges within the delta set.
Loads granular charge-level details, including procedure codes, modifiers, and specific financial balances, for accounts in the delta set.
Loads detailed service charge information, including procedure codes, modifiers, and payment balances, for accounts requiring updates.
Loads insurance remittance adjustments and denial codes from electronic advice, linking them to specific charges and invoice chains.
Categorizes charge types by mapping procedure codes, modifiers, and service types from the fee schedule and charge data.
Updates the procedure code dimension by extracting codes and assigning parent categories for hierarchical reporting.
Builds procedure category and subcategory dimensions by mapping charge codes to standardized hierarchies for clinical reporting.
Loads modifier dimension members into the master dimension table to support reporting and filtering within the pipeline.
Loads procedure code modifier dimension members to support detailed billing analysis and accurate claim processing.
Builds the charge transformation table by calculating bill types, procedure codes, and balances for specific account encounters.
Creates a temporary mapping table to link charges with remittance data using procedure codes, modifiers, and service dates.
Aggregates transaction totals and links them to specific charges or accounts using previously defined matching keys and procedure codes.
Loads and updates charge types by consolidating charge codes, descriptions, and procedure codes from source billing systems.
Builds a temporary transaction table by combining payment and adjustment data from remittance records and categorizing them for recovery analysis.
Aggregates transaction data by claim and line item to calculate total payments, adjustments, and denials for recovery reporting.
Summarizes aggregated transactions to compare initial and current payment, denial, and adjustment totals across different coordination of benefits levels.
Builds a temporary mapping table to link charge records with remittance data based on procedure codes and service dates.
Builds an aggregated transaction table that links financial totals to specific accounts and charges for remittance reporting.
Populates detailed charge-level information, including procedure codes, modifiers, and payment metrics for accounts requiring updates.
Builds detailed account charge records by aggregating patient, insurance, and financial data for specific account deltas.
Builds a transformed chargemaster by combining services, supplies, and products with specific bill types and active pricing data.
Loads the charge type table with formatted display names and concatenated code strings for claim and service-level reporting.
Loads procedure codes into the dimension table and assigns parent categories based on CPT code suffixes.
Builds the procedure category dimension by extracting unique parent groupings from the existing procedure code dimension members.
Updates detailed charge-level information, including procedure codes, modifiers, and specific financial metrics for accounts in the current delta.
Updates the charge type dimension by mapping professional and facility fees to specific procedure codes, service types, and billing categories.
Loads and transforms charge-level data, including service details and insurance information, for accounts identified as having recent updates.
Loads and updates procedure modifier dimension members using charge data and standard modifier descriptions.
Builds procedure code dimension members by extracting codes from charge types and joining them with standard procedure descriptions.
Loads and updates detailed charge information for recent encounters, including codes, balances, and provider assignments.
Loads granular charge-level data, including procedure codes, modifiers, and calculated patient liability for individual healthcare services.
Inserts manual charge code entries into the chargemaster to address missing data for specific practices.
Builds the charge type table by mapping charge codes to display names, bill types, and specific billing rules or modifiers.
Loads the procedure code dimension by organizing unique codes and descriptions into a hierarchical structure for reporting and analysis.
Builds hierarchical dimensions for procedure categories and subcategories to enable grouped analysis of medical services and billing codes.
Loads unique procedure modifiers from source charges into the dimension table to ensure accurate billing and claim tracking.
Updates source charge records with procedure codes, financial balances, and rendering provider information for specific patient encounters.
Builds a temporary report comparing initial and current allowed amounts to track financial recovery from insurance appeals.
Updates account charges by appending United Healthcare procedure categories to code strings for relevant insurance accounts.
Loads granular charge-level details, including procedure codes, modifiers, and provider information, for comprehensive reporting on account financial activity.
Identifies and stores currently active Medicare Durable Medical Equipment procedure codes based on effective date ranges.
Builds the charge type catalog by categorizing professional and facility fees and generating encoded strings for billing and service types.
Loads and updates the modifier dimension table with procedure code modifier IDs and descriptions from source data.
Populates the account charge detail table with granular service, provider, and financial data for individual charges within processed accounts.
Builds transformed charge records containing service dates, procedure codes, and financial amounts for clinical encounters and claims.
Loads appointment data into the source table while mapping payer details and refreshing records from the import staging area.
Loads mobile appointment data into the source table by mapping fields from the mobile import staging area for processing.
Builds the mobile-specific source appointment table by transforming and importing data from the mobile application staging area.
Loads Pinehurst-specific appointment data into the source table while mapping payer identifiers and names from the import staging area.
Builds a transformed charge master table by calculating effective pricing and anesthesia status from fee schedules and charge types.
Loads unique procedure codes into the dimension table by extracting them from charge type data.
Loads endoscopy appointment details and parsed insurance payor information into the source table for cost estimate processing.
Loads fee schedule data into the source table, formatting procedure codes and updating records based on unique identifiers.
Synchronizes the charge type table by merging fee schedule and bundle data to define professional billing codes and descriptions.
Unpivots and cleans procedure codes from multiple appointment sources for visits identified in the delta table.
Calculates surgical CPT rates and units for standard data sources using fee schedules and bundled service logic.
Calculates surgical CPT rates and units specifically for Edgewater source data using fee schedules and bundled service logic.
Calculates surgical CPT rates and units for mobile source data using fee schedules and bundled service logic.
Calculates surgical CPT rates and units for Meditech source data using fee schedules and bundled service logic.
Builds the charge type dimension by extracting unique procedure codes and billing categories from the transformed charge master data.
Builds the ProcedureCode dimension by extracting unique procedure codes from the charge type source data.
Loads and refreshes encounter charge details from imported files into the source charge table.
Synchronizes visit type definitions and display names in the dimension table using data from HL7 messages and reference crosswalks.
Loads visit charges into a staging table by matching scheduled services against insurance-specific or default billing bundles.
Builds a staging table of visit charges by extracting and formatting procedure codes and units from HL7 order bundles.
Builds a structured table of individual charges, including procedure codes, modifiers, and balances, for accounts identified in the delta.
Populates the visit type dimension by mapping service IDs and CPT codes from order and crosswalk data.
Builds the charge type table by categorizing procedure codes into professional or facility bill types based on encounter data.
Loads procedure code dimension members by mapping charge types to their respective descriptions and CPT categories.
Builds the procedure category dimension by extracting parent categories from existing procedure code records.
Loads insurance eligibility service types by mapping procedure codes from account charges to standardized service type descriptions.
Updates eligibility service type dimensions by cross-referencing procedure codes with chargemaster data and standardized service mappings.
Loads charge-level details into the transformation table, calculating service units, amounts, and bill types for updated accounts.
Synchronizes procedure code dimension members by merging updates and new records from the common data source into the local repository.
Transforms and loads imported schedule data into the source appointment table, mapping patient demographics, insurance details, and procedure information.
Loads Medicare endoscopy base schedules and facility mappings from common data for specific regional localities.
Loads Medicare Durable Medical Equipment fee schedules and facility mappings for the specified state into the system.
Loads Medicare drug fee schedules and facility mappings into the system based on current Medicare pricing data.
Calculates and loads Medicare clinic fee schedules based on Relative Value Units and regional conversion factors.
Builds and updates Medicare RVU fee schedules and facility mappings for hospital-based services using regional zip code data.
Transforms appointment CPT codes and modalities into standardized visit charges and upserts them into the charge transformation table.
Loads visit details into a staging table, mapping facility, provider, and service type information for upcoming appointment processing.
Loads procedure charges into a staging table, cross-walking clinical codes to standard billing formats and service types for cost estimation.
Maps appointment identifiers to imported records and isolates duplicate entries to ensure data integrity within the appointment source.
Builds and updates the ChargeType table by merging procedure codes and descriptions from the charge master and appointment records.
Loads new procedure codes from appointment data into the ChargeType table when they are missing from the existing master list.
Builds and updates the Modifier dimension using procedure code modifiers and descriptions from the source system.
Updates charge-level details, including procedure codes, modifiers, and diagnosis information, for accounts identified in the incremental delta.
Builds procedure category dimension members by extracting parent groupings from existing procedure codes to support hierarchical reporting.
Loads chargemaster rates and facility mappings into the system to define standard billing amounts for medical procedures.
Loads self-pay fee schedules and facility mappings to establish fixed pricing for patients without insurance coverage.
Populates the charge type table with procedure codes and descriptions from the chargemaster to support accurate billing and claim reporting.
Loads unique procedure code dimension members extracted from charge type records for billing and clinical tracking.
Builds standardized remittance line items by processing service dates, procedure codes, and unique charge matching identifiers.
Updates granular charge-level details including procedure codes, modifiers, and payment information for individual service line items.
Transforms and loads charge-level data for delta accounts, including service dates, procedure codes, and balances across multiple sources.
Calculates and updates visit charges by mapping CPT codes and modifiers to standardized service types and charge definitions.
who computes it · 33
Pipelines that compute cpt / procedure logic
Grouped by product. Each links to its task tree.
the rhythm
When it runs
always-on loops · 5 · tick density = frequency
03691215182124
daily & monthly fires · 25
03691215182124
daily 00:30
00:30
daily 01:00
01:00
daily 01:00
01:00
daily 02:00
02:00
daily 02:30
02:30
daily 03:00
03:00
daily 03:15
03:15
daily 03:30
03:30
daily 03:45
03:45
daily 03:45
03:45
daily 04:30
04:30
daily 05:00
05:00
daily 06:30
06:30
daily 06:45
06:45
monthly 07:30
07:30 · 1st
daily 08:30
08:30
daily 09:15
09:15
daily 10:30
10:30
daily 11:15
11:15
daily 14:30
14:30
daily 19:01
19:01
daily 20:00
20:00
daily 22:15
22:15
daily 23:00
23:00
chained
runs after the source-data load
HL7 FHIR file/batch eligibility◆ monthly · shaded = overnight (00–06)