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

Batch / file feed · RevFind pipeline

ECP · Daily-Accounts-Processing-RevFind

Overall Processing job for RevFind Batch pipeline

pipeline ECP/Daily-Accounts-Processing-RevFind · db ECP_ProdMirror · updated 2025-10-22
classification: revfind · loads 4 visit tables

Loads visit tables
Besides its back-office work, this RevFind pipeline also loads a visit dimension/fact table (Load VisitFact, Load VisitStatus Dimension, Load VisitType Dimensions, Load-Visit-Fact-RevFind) — the shared rows Flow builds on. It runs no front-of-house work (no eligibility, estimate, or letters), so it stays RevFind.

trigger

How it's scheduled

Daily batch — once at 01:00

The Batch / file feed arrives as a scheduled drop, so one overnight pass is enough: it ingests the day's file and works the whole batch to process the visits. A tighter loop would just re-scan the same file.

Scheduled taskCadenceServerStateCoordination
\MDC\Daily - Processing - ECPdaily at 01:00prod-db-3enabledstandalone

shape

The RevFind back-office spine

RevFind pipelines share a back-office shape: ingest → load accounts → reconcile remits → model contracts → surface underpayments. Lit stages are the ones this pipeline runs.

Ingest
Batch / file feed
→
Accounts / AR
ACCT
→
Remit (835)
835
→
Contract model
→
Payer rules
→
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).

Good-faith estimateFee schedule / contractExpected reimbursementDenial / underpaymentCPT / procedure logic

Payers in SQLAetnaAnthemBCBSCignaHumanaMedicaidMedicare

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).

revfind

Daily-Accounts-Processing-RevFind

16 tasks · ECP
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. ▶Process-Snowflake-Data-Load-RevFind-ImportSQLsub-job · 29↗Download and process the snowflake data into source tables
    1. Snowflake required transaction isolation levelsqlprocess_snowflake_data_load_revfind_importsql__snowflake_required_transaction_isolation_level.sqldisabled
      ✨ Sets the transaction isolation level to ensure data consistency during the Snowflake import process.
    2. ImportBillingEntityImportBillingEntity
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportBillingEntity
      connection string={SnowflakeConnectionString}table name=ImportBillingEntityimport mode=Replacedata source type=Snowflake
    3. ImportChargeMasterImportChargeMaster
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportChargeMaster
      connection string={SnowflakeConnectionString}table name=ImportChargeMasterimport mode=Replacedata source type=Snowflake
    4. ImportPayersImportPayers
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportPayers
      connection string={SnowflakeConnectionString}table name=ImportPayersimport mode=Replacedata source type=Snowflake
    5. ImportProvidersImportProviders
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportProviders
      connection string={SnowflakeConnectionString}table name=ImportProvidersimport mode=Replacedata source type=Snowflake
    6. ImportLocationMapImportLocationMap
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportLocationMap
      connection string={SnowflakeConnectionString}table name=ImportLocationMapimport mode=Replacedata source type=Snowflake
    7. ImportLocationImportLocation
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportLocation
      connection string={SnowflakeConnectionString}table name=ImportLocationimport mode=Replacedata source type=Snowflake
    8. ImportPatientsImportPatients
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportPatients
      connection string={SnowflakeConnectionString}table name=ImportPatientsimport mode=Replacedata source type=Snowflake
    9. ImportPatientInsuranceImportPatientInsurance
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportPatientInsurance
      connection string={SnowflakeConnectionString}table name=ImportPatientInsuranceimport mode=Replacedata source type=Snowflake
    10. ImportClaimsImportClaims
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportClaims
      connection string={SnowflakeConnectionString}table name=ImportClaimsimport mode=Replacedata source type=Snowflake
    11. ImportEncountersImportEncounters
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportEncounters
      connection string={SnowflakeConnectionString}table name=ImportEncountersimport mode=Replacedata source type=Snowflake
    12. ImportEncounterInsuranceImportEncounterInsurance
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportEncounterInsurance
      connection string={SnowflakeConnectionString}table name=ImportEncounterInsuranceimport mode=Replacedata source type=Snowflake
    13. ImportChargesImportCharges
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportCharges
      connection string={SnowflakeConnectionString}table name=ImportChargesimport mode=Replacedata source type=Snowflake
    14. ImportAdjustmentsImportAdjustments
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportAdjustments
      connection string={SnowflakeConnectionString}table name=ImportAdjustmentsimport mode=Replacedata source type=Snowflake
    15. ImportPaymentsImportPayments
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportPayments
      connection string={SnowflakeConnectionString}table name=ImportPaymentsimport mode=Replacedata source type=Snowflake
    16. ImportRefundsImportRefunds
      Imports data from SQL Server or Snowflake queries into target tables using bulk copy operations with configurable loading modes.
      writes ImportRefunds
      connection string={SnowflakeConnectionString}table name=ImportRefundsimport mode=Replacedata source type=Snowflake
    17. Process-to-SourceBillingEntitysqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcebillingentity.sql
      ✨ Loads billing entity and practice configuration data from the import staging area into the source system tables.
      writes SourceBillingEntity · reads ImportBillingEntity
      ↳ feeds from ImportBillingEntity
    18. Process-to-SourceChargeMastersqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcechargemaster.sql
      ✨ Loads service item prices and CPT codes from the import staging area into the source charge master table.
      writes SourceChargeMaster · reads ImportChargeMaster
      ↳ feeds from ImportChargeMaster
    19. Process-to-SourcePayerssqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcepayers.sql
      ✨ Loads payer information from the import table into the source payer repository to support insurance identification.
      writes SourcePayers · reads ImportPayers
      ↳ feeds from ImportPayers
    20. Process-to-SourceProviderssqlprocess_snowflake_data_load_revfind_importsql__process_to_sourceproviders.sql
      ✨ Loads unique provider details from the import table into the source provider repository for clinical and billing attribution.
      writes SourceProvider · reads ImportProviders
      ↳ feeds from ImportProviders
    21. Process-to-SourceLocationMapsqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcelocationmap.sql
      ✨ Updates or inserts location mapping data to ensure consistent facility identification across different source systems.
      writes SourceLocationMap · reads ImportLocationMap
      ↳ feeds from ImportLocationMap
    22. Process-to-SourceLocationsqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcelocation.sql
      ✨ Synchronizes facility location data while applying place-of-service crosswalks to maintain accurate site-of-care records.
    23. Process-to-SourcePatientsqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcepatient.sql
      ✨ Refreshes patient demographic records by replacing existing entries with updated information from the latest import data.
      writes SourcePatient · reads ImportPatients
      ↳ feeds from ImportPatients
    24. Process-to-SourcePatientInsurancesqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcepatientinsurance.sql
      ✨ Updates patient insurance coverage details by replacing existing records with current policy and payer information.
      writes SourcePatientInsurance · reads ImportPatientInsurance
      ↳ feeds from ImportPatientInsurance
    25. Process-to-SourceClaimsqlprocess_snowflake_data_load_revfind_importsql__process_to_sourceclaim.sql
      ✨ Refreshes claim data by replacing existing records with updated billing, diagnosis, and payment information from the source system.
      writes SourceClaim · reads ImportClaims
      ↳ feeds from ImportClaims
    26. Process-to-SourceEncountersqlprocess_snowflake_data_load_revfind_importsql__process_to_sourceencounter.sql
      ✨ Updates clinical encounter records by replacing existing entries with the latest visit, provider, and billing status data.
      writes SourceEncounter · reads ImportEncounters
      ↳ feeds from ImportEncounters
    27. Process-to-SourceEncounterInsurancesqlprocess_snowflake_data_load_revfind_importsql__process_to_sourceencounterinsurance.sql
      ✨ Updates the source encounter insurance table by replacing existing records with newly imported data.
      writes SourceEncounterInsurance · reads ImportEncounterInsurance
      ↳ feeds from ImportEncounterInsurance
    28. Process-to-SourceChargesqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcecharge.sql
      ✨ Cleans charge modifiers and refreshes the source charge table with updated billing and service item data.
      writes SourceCharge · reads ImportChargesic
      ↳ feeds from ImportCharges
    29. Process-to-SourceTransactionsqlprocess_snowflake_data_load_revfind_importsql__process_to_sourcetransaction.sql
      ✨ Synchronizes the source transaction table by importing and updating adjustment and payment records from the staging tables.
      writes SourceTransaction · reads ImportAdjustmentsImportPaymentsImportRefunds
      ↳ feeds from ImportAdjustments, ImportPayments, ImportRefunds
  2. ▶Load-InsuranceMap-RevFindsub-job · 1↗
    1. LoadEntitiesFromQueryLoadEntitiesFromQuery
      Load or update entity records from custom SQL query results into their corresponding system tables using intelligent merge logic.
      writes InsuranceMap · reads InsuranceMapSourcePayers
      entity type=InsuranceMapcustom querykey prefix=IM
  3. ▶Process-EDIsub-job · 6↗
    1. Download Files from SFTPDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=ECP_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolderEDI}
    2. Download Files from SFTP (AMD)Download
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=ECP_ProdMirror-mdcssh-sftpremote directory=/AMD/local directory={SourceDataFolderEDI}
    3. Download Files from SFTP (Trizetto)Download
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=ECP_ProdMirror-mdcssh-sftpremote directory=/Trizetto/local directory={SourceDataFolderEDI}
    4. ▶Load-Edi-Filessub-job · 1↗
      1. ▶Load-Edi-Filesub-job · 3↗
        1. Bulk Insert File Into Databasesqlload_edi_file__bulk_insert_file_into_database.sqldisabled
          ✨ Loads raw electronic data interchange files into a staging table while identifying file types and subtypes for further processing.
          writes EDIFileStaging · reads SINGLE_CLOB
        2. Bulk Insert File Into Database via PowershellExecuteProcess
          command=powershell.exe
        3. Archive FilesExecuteProcess
          command=cmd.exe
    5. ▶TransformAndParseEDIsub-job · 5↗
      1. EDITransformsqltransformandparseedi__editransform.sql
        ✨ Loads unique EDI messages from staging into the transformation table while preventing duplicate records.
        writes EDITransform · reads EDIFileStagingEDITransform
      2. EDIBatchsqltransformandparseedi__edibatch.sql
        ✨ Builds a batch of unprocessed EDI messages, excluding empty records, for subsequent parsing and transformation.
        writes EDIBatch
      3. Clean EDIBatch (for bug w EDIFabric)sqltransformandparseedi__clean_edibatch_for_bug_w_edifabric.sqldisabled
        ✨ Updates EDI message formatting by replacing specific delimiters to ensure compatibility with the parsing engine.
        · reads EDIBatch
      4. EDIParserEDIParser
        writes EDIBatch
        table name=EDIBatch
      5. ▶Load-EDI-Metadatasub-job · 3↗
        1. Load EDIFacilityMapsqlload_edi_metadata__load_edifacilitymap.sql
          ✨ Loads the facility map with unique billing and service facility details extracted from electronic claim transactions.
          writes EDIFacilityMap · reads EDIFacilityMapTxn837P
        2. Load EDIProviderMapsqlload_edi_metadata__load_ediprovidermap.sql
          ✨ Builds the provider map by combining rendering provider information from claim transactions with national provider registry data.
          writes EDIProviderMap · reads EDIProviderMapNPIDataTxn837P
        3. Load EDIPayerMapsqlload_edi_metadata__load_edipayermap.sql
          ✨ Builds a mapping table of payer names and filing indicators from electronic remittance transactions to standardize payer metadata.
          writes EDIPayerMap · reads Txn835Txn835ClaimPayment
    6. ▶Load-Transform-RemitDatasub-job · 24↗
      1. Load RemitDeltasqlload_transform_remitdata__load_remitdelta.sql
        ✨ Identifies recently processed or updated electronic remittance claims to determine which records require incremental transformation and loading.
        writes RemitDelta · reads EDIFilesProcessedRemitDeltaTransformRemitTxn835Txn835ClaimPayment
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      2. Load TransformRemitsqlload_transform_remitdata__load_transformremit.sql
        ✨ Transforms raw remittance transaction data into a structured format by parsing payer details, payment amounts, and insurance filing indicators.
        writes TransformRemit · reads Txn835Txn835ClaimPaymentTxn835ClaimPaymentLineTxn835ClaimPaymentOtherClaimAmount
      3. Load TransformRemitEncounterMapsqlload_transform_remitdata__load_transformremitencountermap.sql
        ✨ Maps remittance claims to their respective data sources and patient accounts using provider identifiers and complex matching logic.
        writes TransformRemitEncounterMap · reads TransformRemitEncounterMap
      4. Update TransformRemitEncounterMapsqlload_transform_remitdata__update_transformremitencountermap.sql
        ✨ Updates remittance mapping records with patient account identifiers by matching service dates and charge IDs at the line level.
        · reads TransformChargeTransformRemitEncounterMapTransformRemitLinerem
        ↳ feeds from Load TransformRemitEncounterMap, Load TransformRemitLine
      5. Load TempRemitCOB (old)sqlload_transform_remitdata__load_tempremitcob_old.sqldisabled
        ✨ Builds a temporary table to determine claim status, member IDs, and coordination of benefits order for remittance data.
      6. Load TempRemitCOBsqlload_transform_remitdata__load_tempremitcob.sql
        ✨ Builds a temporary table mapping remittance data to encounters while calculating coordination of benefits and current claim status.
        writes TempRemitCOBTransformRemit · reads TempRemitCOBTransformRemitTransformRemitAdjustmentTransformRemitEncounterMap
        ↳ feeds from Load TransformRemit, Load TransformRemitAdjustment, Load TransformRemitEncounterMap
      7. Load TransformRemitAdjustmentsqlload_transform_remitdata__load_transformremitadjustment.sql
        ✨ Loads claim and line-level adjustments by unpivoting reason codes and amounts from electronic remittance advice transactions.
        writes TransformRemitAdjustment · reads TransformRemitTxn835ClaimAdjustment
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      8. Load TransformRemitLinesqlload_transform_remitdata__load_transformremitline.sql
        ✨ Loads detailed remittance line items, mapping procedure codes, service dates, and payment amounts from electronic remittance advice.
        writes TransformRemitLine · reads TransformRemitTxn835ClaimPaymentLine
        ↳ feeds from Load TempRemitCOB, Load TransformRemit
      9. Load TransformRemitAdjustmentAggregatesqlload_transform_remitdata__load_transformremitadjustmentaggregate.sqldisabled
        ✨ Aggregates claim adjustments to identify and categorize initial and current denials based on standardized reason code rollups.
        writes TransformRemitAdjustmentAggregate · reads AdjustmentCodeRollupTransformRemitTransformRemitAdjustmentTransformRemitLine
      10. Load TransformChargeTransactionMeasureRemitsqlload_transform_remitdata__load_transformchargetransactionmeasureremit.sqldisabled
        ✨ Summarizes financial metrics including payments, charges, and denials at the line level for remittance reporting and analysis.
        writes TransformChargeTransactionMeasureRemit · reads TransformRemitTransformRemitAdjustmentAggregateTransformRemitLine
      11. Load TransformTxn837sqlload_transform_remitdata__load_transformtxn837.sqldisabled
        ✨ Loads and transforms outbound claim header data, determining bill types and mapping patient, subscriber, and provider information.
        writes TransformTxn837 · reads Txn837PTxn837PServiceLine
      12. Load TransformTxn837Linesqlload_transform_remitdata__load_transformtxn837line.sqldisabled
        ✨ Loads transformed service line details for outbound claims, including procedure codes, modifiers, and rendering provider information.
        writes TransformTxn837Line · reads TransformTxn837Txn837PServiceLine
      13. Load TransformAccountsqlload_transform_remitdata__load_transformaccount.sqldisabled
        ✨ Builds the account transformation table by consolidating primary and secondary claim data, provider details, and insurance information from EDI sources.
        writes TransformAccount · reads EDIFacilityMapTransformTxn837
      14. Load TransformChargesqlload_transform_remitdata__load_transformcharge.sqldisabled
        ✨ Loads detailed service line charges by mapping procedure codes, modifiers, and units while generating unique keys for charge matching.
        writes TransformCharge · reads TransformAccountTransformTxn837TransformTxn837Line
      15. Merge TransformPatientsqlload_transform_remitdata__merge_transformpatient.sqldisabled
        ✨ Updates or inserts patient demographic records using the most recent contact and identity information found in claim transaction data.
        writes TransformPatient · reads TransformTxn837
      16. Load TempTransactions (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactions_for_revfind_recovery.sql
        ✨ Builds a temporary transaction table by combining payment and adjustment data from remittance records and categorizing them for recovery analysis.
        writes TempTransactions · reads TempTransactions
      17. Load TempTransactionsAgg (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsagg_for_revfind_recovery.sql
        ✨ Aggregates transaction data by claim and line item to calculate total payments, adjustments, and denials for recovery reporting.
        writes TempTransactionsAgg
      18. Load TempTransactionsAggTotal (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsaggtotal_for_revfind_recovery.sql
        ✨ Summarizes aggregated transactions to compare initial and current payment, denial, and adjustment totals across different coordination of benefits levels.
        writes TempTransactionsAggTotal · reads TempTransactionsAggTotal
      19. Load TempTransactionsAggTotalFinal (for RevFind Recovery)sqlload_transform_remitdata__load_temptransactionsaggtotalfinal_for_revfind_recovery.sqldisabled
        ✨ Produces the final consolidated summary of financial metrics, including total paid, denied, and adjusted amounts for each claim line.
        writes TempTransactionsAggTotalFinal
      20. Update TransformChargeTransactionMeasure (for RevFind Recovery)sqlload_transform_remitdata__update_transformchargetransactionmeasure_for_revfind_recovery.sqldisabled
        ✨ Updates the charge transaction measurement table with final calculated payment, patient responsibility, and contract adjustment totals for recovery tracking.
        · reads TempTransactionsAggTotalFinalTransformChargeTransactionMeasuret
      21. Append PatientID on TransformAccountsqlload_transform_remitdata__append_patientid_on_transformaccount.sqldisabled
        ✨ Updates account records with patient identifiers by matching claim transaction details against existing patient mapping data.
        · reads TransformAccountTransformPatientTransformTxn837ta
      22. Append TransformPatient, Second Append of PatientID to TransformAccountsqlload_transform_remitdata__append_transformpatient_second_append_of_patientid_to_transformaccount.sqldisabled
        ✨ Updates the patient master list and assigns patient identifiers to accounts using encounter and practice information.
        writes TempTransformPatientTransformPatient · reads TempTransformPatientTransformAccountta
      23. TempChargeLinkRemit (moved to Load Account Transform)sqlload_transform_remitdata__tempchargelinkremit_moved_to_load_account_transform.sqldisabled
        ✨ Builds a temporary mapping table to link charge records with remittance data based on procedure codes and service dates.
        writes TempChargeLinkRemit · reads ChargeTypeSourceChargeSourceEncounterTempChargeLinkRemitTransformCharge
      24. TempTransactionsAggTotalLink (moved to Load Account Transform)sqlload_transform_remitdata__temptransactionsaggtotallink_moved_to_load_account_transform.sqldisabled
        ✨ Builds an aggregated transaction table that links financial totals to specific accounts and charges for remittance reporting.
        writes TempTransactionsAggTotalLink · reads TempChargeLinkRemitTempTransactionsAggTotalTempTransactionsAggTotalLink
  4. ▶Load-Account-Transform-Tables-RevFindsub-job · 17↗NextGen Transforms-RevFind
    1. Fill AccountKeyMapsqlload_account_transform_tables_revfind__fill_accountkeymap.sql
      ✨ Maintains a mapping of source system identifiers and encounter details to unique internal account keys based on recent modifications.
    2. Create AccountDeltasqlload_account_transform_tables_revfind__create_accountdelta.sql
      ✨ Populates the delta table with accounts that have recent activity, modified charges, or updated location mappings since the last run.
      writes AccountDelta · reads AccountDeltaAccountKeyMapProcessLogSourceChargeSourceEncounterSourceLocationMapSourceTransactionTransformAccount
      ↳ feeds from Load TransformAccount, Load TransformCharge, Load TransformTransaction
    3. Load TransformPatientsqlload_account_transform_tables_revfind__load_transformpatient.sql
      ✨ Updates the transformed patient table with demographic information, contact details, and standardized identifiers from the source system.
      writes TransformPatient · reads SourcePatient
    4. Load TransformLocationsqlload_account_transform_tables_revfind__load_transformlocation.sql
      ✨ Builds the location transformation table by mapping source locations to standardized addresses and place of service codes.
      writes TransformLocation · reads SourceLocationSourceLocationMap
    5. Load TransformProvidersqlload_account_transform_tables_revfind__load_transformprovider.sql
      ✨ Updates the provider transformation table with standardized names, credentials, and NPI identifiers from source systems.
      writes TransformProvider · reads SourceProvider
    6. Load TransformPayersqlload_account_transform_tables_revfind__load_transformpayer.sql
      ✨ Loads the payer transformation table with standardized insurance carrier names, plan types, and financial classifications.
      writes TransformPayer · reads SourcePayers
    7. Load TransformChargesqlload_account_transform_tables_revfind__load_transformcharge.sql
      ✨ Builds the charge transformation table by calculating bill types, procedure codes, and balances for specific account encounters.
      writes TransformCharge · reads AccountDeltaAccountKeyMapSourceBillingEntitySourceChargeSourceEncounterSourceLocationMapTransformLocation
      ↳ feeds from Create AccountDelta, Load TransformLocation
    8. Load TransformTransactionsqlload_account_transform_tables_revfind__load_transformtransaction.sql
      ✨ Loads the transaction transformation table by categorizing financial adjustments, denials, and payments for specific patient accounts.
      writes TransformTransaction · reads AccountDeltaAdjustmentCodeRollupSourceTransactionTransformCharge
      ↳ feeds from Create AccountDelta, Load TransformCharge
    9. Load TransformAccountsqlload_account_transform_tables_revfind__load_transformaccount.sql
      ✨ Builds the account transformation table by aggregating encounter details, provider information, and financial balances for active accounts.
      writes TransformAccount · reads AccountDeltaAccountKeyMapSourceBillingEntitySourceEncounterSourceEncounterInsuranceSourceLocationMapTransformCharge
      ↳ feeds from Create AccountDelta, Load TransformCharge
    10. Load TransformAccountInsurancesqlload_account_transform_tables_revfind__load_transformaccountinsurance.sql
      ✨ Loads the account insurance transformation table by mapping primary, secondary, and tertiary coverage details for each account.
      writes TransformAccountInsurance · reads AccountDeltaSourceEncounterInsuranceTransformCharge
      ↳ feeds from Create AccountDelta, Load TransformCharge
    11. Load TransformRemitAdjustmentsqlload_account_transform_tables_revfind__load_transformremitadjustment.sqldisabled
      ✨ Builds the remittance adjustment transformation table by categorizing insurance denials and mapping reason codes to standardized adjustment types.
      writes TransformRemitAdjustment · reads AccountDelta
    12. Load TransformRemitsqlload_account_transform_tables_revfind__load_transformremit.sqldisabled
      ✨ Builds the remit transformation table by consolidating electronic remittance advice adjustments and transaction-level payment data for updated encounters.
      writes TransformRemit · reads EncounterDeltaTransformRemitTransformRemitAdjustmentTransformTransactionPaymentAdjustment
    13. Load TransformRemitAdjustmentAggregatesqlload_account_transform_tables_revfind__load_transformremitadjustmentaggregate.sqldisabled
      ✨ Aggregates remittance adjustments to calculate total contract, patient, and denial amounts at the charge level for updated accounts.
      writes TransformRemitAdjustmentAggregate · reads AccountDeltaTransformChargeTransformRemitAdjustment
    14. Load TransformChargeTransactionMeasuressqlload_account_transform_tables_revfind__load_transformchargetransactionmeasures.sql
      ✨ Calculates and loads transaction-based financial measures, including payments, adjustments, and denial categories, for each charge in updated accounts.
      writes TransformChargeTransactionMeasure · reads AccountDeltaTransformChargeTransformTransaction
      ↳ feeds from Create AccountDelta, Load TransformCharge, Load TransformTransaction
    15. Load TempChargeLinkRemitsqlload_account_transform_tables_revfind__load_tempchargelinkremit.sql
      ✨ Creates a temporary mapping table to link charges with remittance data using procedure codes, modifiers, and service dates.
      writes TempChargeLinkRemit · reads ChargeTypeSourceChargeTempChargeLinkRemitTransformAccountTransformCharge
      ↳ feeds from Load TransformAccount, Load TransformCharge
    16. Load TempTransactionsAggTotalLinksqlload_account_transform_tables_revfind__load_temptransactionsaggtotallink.sql
      ✨ Aggregates transaction totals and links them to specific charges or accounts using previously defined matching keys and procedure codes.
    17. Merge 835 Updates to TransformChargeTransactionMeasuresqlload_account_transform_tables_revfind__merge_835_updates_to_transformchargetransactionmeasure.sql
      ✨ Merges aggregated electronic remittance data into the charge transaction measure table to update payment, adjustment, and denial metrics.
      writes TransformChargeTransactionMeasure · reads TempTransactionsAggTotalLinkTransformCharge
      ↳ feeds from Load TransformCharge
  5. ▶Load-Entities-RevFindsub-job · 3↗Load Facility, Provider
    1. Load Facility Group Mapping Table for multi-practice data sourcessqlload_entities_revfind__load_facility_group_mapping_table_for_multi_practice_data_sources.sqldisabled
      ✨ Builds a facility grouping table that maps specific locations to standardized practice groups based on facility names and geographic locations.
      writes SourceFacilityGroupMapping · reads FacilityTransformLocation
    2. Load FacilityLoadEntities
      writes Facility · reads FacilitySourceFacilityGroupMappingSourceFacilityPOSMapTransformLocation
      entity type=Facilitycustom querykey prefix=F
    3. Load Providersqlload_entities_revfind__load_provider.sql
      ✨ Updates the provider registry by merging source data with national provider identifiers and taxonomy codes for active billing providers.
      writes Provider · reads NPIDataSourceProviderTransformChargeTransformProvider
  6. ▶Load-Charge-Dimension-RevFindsub-job · 10↗
    1. Load TempChargeSchedulessqlload_charge_dimension_revfind__load_tempchargeschedules.sqldisabled
      ✨ Builds a temporary table of chargemaster fee schedules by determining valid date ranges for professional and facility practices.
      writes TempChargeSchedules
    2. Load FeeScheduleRecordsqlload_charge_dimension_revfind__load_feeschedulerecord.sqldisabled
      ✨ Loads detailed fee schedule records by mapping chargemaster prices to specific codes and modifiers for professional and facility schedules.
      writes FeeScheduleRecord · reads SourceChargeMasterTempChargeSchedules
    3. Load ScheduleMapsqlload_charge_dimension_revfind__load_schedulemap.sqldisabled
      ✨ Builds the schedule map by linking charge schedules to facilities and insurance categories for professional and facility billing.
      writes ScheduleMap · reads FacilityTempChargeSchedules
    4. Load ContractMapsqlload_charge_dimension_revfind__load_contractmap.sqldisabled
      ✨ Updates the contract map to define fee logic and billing types for facilities and professional services.
      writes ContractMap · reads Facility
    5. Load ScheduleLoadEntitiesdisabled
      writes Schedule · reads TempChargeSchedules
      entity type=Schedulecustom queryDISABLED
    6. Load ChargeTypesqlload_charge_dimension_revfind__load_chargetype.sql
      ✨ Loads and updates charge types by consolidating charge codes, descriptions, and procedure codes from source billing systems.
      writes ChargeType · reads ProcedureCodeSourceBillingEntitySourceChargeSourceChargeMasterTransformCharge
    7. Load ChargeType Dimensionsqlload_charge_dimension_revfind__load_chargetype_dimension.sql
      ✨ Loads charge type members into the dimension table to support hierarchical reporting and data categorization.
      writes DimensionMember · reads ChargeType
      ↳ feeds from Load ChargeType
    8. Load ProcedureCode Dimensionsqlload_charge_dimension_revfind__load_procedurecode_dimension.sql
      ✨ Builds the procedure code dimension by extracting codes from charge types and mapping them to standard descriptions and categories.
      writes DimensionMember · reads ChargeTypeDimensionMemberProcedureCode
      ↳ feeds from Load ChargeType, Load ChargeType Dimension, Load Modifier Dimension, Load ProcedureCategory and Subcategory DM
    9. Load ProcedureCategory and Subcategory DMsqlload_charge_dimension_revfind__load_procedurecategory_and_subcategory_dm.sql
      ✨ Populates procedure categories in the dimension table based on the parent relationships established for procedure codes.
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Load ChargeType Dimension, Load Modifier Dimension, Load ProcedureCode Dimension
    10. Load Modifier Dimensionsqlload_charge_dimension_revfind__load_modifier_dimension.sql
      ✨ Loads unique billing modifiers from transformed charge records into the dimension member table.
      writes DimensionMember · reads TransformCharge
  7. ▶Load-Patients-Entity-RevFindsub-job · 1↗
    1. Execute LoadPatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatient
      custom patient query
  8. ▶Load-Account-Data-RevFindsub-job · 8↗Load Account-Level Tables-RevFind
    1. ReprocessChangedConfigurationsReprocessChangedConfigurations
      Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
      reprocess accounts
    2. Merge into AccountDelta from AccountsToReprocesssqlload_account_data_revfind__merge_into_accountdelta_from_accountstoreprocess.sql
      ✨ Identifies and adds unique accounts requiring reprocessing to the delta tracking table for incremental updates.
      writes AccountDelta · reads AccountDeltaAccountsToReprocess
    3. Load ChargeMeasuresqlload_account_data_revfind__load_chargemeasure.sql
      ✨ Calculates and loads detailed financial metrics for charges, including payments, adjustments, and denial categories, using insurance remittance data.
      writes ChargeMeasures · reads AccountDeltaTransformChargeTransformChargeTransactionMeasure
      ↳ feeds from Merge into AccountDelta from AccountsToReprocess
    4. Load ChargeMeasuresSummarysqlload_account_data_revfind__load_chargemeasuressummary.sql
      ✨ Summarizes financial metrics by charge and account, aggregating insurance payments and denial amounts across different coordination of benefits levels.
      writes ChargeMeasuresSummary · reads AccountDeltaChargeMeasures
      ↳ feeds from Load ChargeMeasure, Merge into AccountDelta from AccountsToReprocess
    5. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountDeltaChargeMeasuresChargeTypeInsuranceMapSourceNonCoveredChargesTempChargeLinkRemitTempTransactionsTransformAccount
      ↳ feeds from Load ChargeMeasure, Merge into AccountDelta from AccountsToReprocess
      custom account charge querycustom account querycustom account remit querycustom account insurance query
    6. ▶Load-Dimension-Members-RevFindsub-job · 18↗
      1. LoadInsuranceDimensionMembersLoadInsuranceDimensionMembers
        Synchronize insurance-specific dimension members from the insurance table to update contract and eligibility filtering options across the application.
        writes DimensionMember
        load insuranceload quote insuranceload eligibility insurance
      2. Load Time Dimensionssqlload_dimension_members_revfind__load_time_dimensions.sql
        ✨ Loads and updates time-based dimensions including days, months, and years based on encounter date ranges.
        writes DimensionMember · reads SourceEncounter
      3. Load VisitStatus Dimensionsqlload_dimension_members_revfind__load_visitstatus_dimension.sqldisabled
        ✨ Loads appointment status values into the visit status dimension to track patient encounter progress.
        writes DimensionMember · reads TransformVisit
      4. Load DenialCategory Dimensionsqlload_dimension_members_revfind__load_denialcategory_dimension.sql
        ✨ Loads denial category dimensions from adjustment code rollup data to categorize claim denials.
        writes DimensionMember · reads AdjustmentCodeRollup
      5. Load Provider Dimensionsqlload_dimension_members_revfind__load_provider_dimension.sql
        ✨ Loads and updates provider dimension members using provider IDs, names, and types from the source provider table.
        writes DimensionMember · reads Provider
      6. Load ProviderType Dimensionsqlload_dimension_members_revfind__load_providertype_dimension.sql
        ✨ Builds the provider type dimension by extracting unique parent categories from existing provider dimension records.
        writes DimensionMember · reads DimensionMember
        ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load Source Insurance Dimension, Load State Dimension, Load Time Dimensions, LoadInsuranceDimensionMembers
      7. Load Facility Dimensionssqlload_dimension_members_revfind__load_facility_dimensions.sql
        ✨ Loads facility dimension members by extracting facility keys, groups, and names from the facility source table.
        writes DimensionMember · reads Facility
      8. Load FacilityGroup Dimensionssqlload_dimension_members_revfind__load_facilitygroup_dimensions.sql
        ✨ Builds the facility group dimension by identifying unique parent groups from facility dimension records.
        writes DimensionMember · reads DimensionMember
        ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load DiagnosisCode Dimension, Load Facility Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderType Dimension, Load Source Insurance Dimension, Load State Dimension, Load Time Dimensions, LoadInsuranceDimensionMembers
      9. Load FacilityType Dimensionssqlload_dimension_members_revfind__load_facilitytype_dimensions.sql
        ✨ Loads facility type dimension members from facility records to enable standardized categorization across the revenue cycle pipeline.
        writes DimensionMember · reads Facility
      10. Load Practice Dimensionssqlload_dimension_members_revfind__load_practice_dimensions.sqldisabled
        ✨ Loads practice dimension members from the practice map for entities that have associated encounter records.
        writes DimensionMember · reads PracticeMapsourceencounter
      11. Load PlaceOfService Dimensionssqlload_dimension_members_revfind__load_placeofservice_dimensions.sql
        ✨ Builds and updates the place of service dimension by mapping charge data to standardized codes and descriptive names.
        writes DimensionMember · reads POSCodesTransformCharge
      12. Load VisitType Dimensionssqlload_dimension_members_revfind__load_visittype_dimensions.sqldisabled
        ✨ Loads and synchronizes visit type dimensions from event mapping data while removing entries no longer present in the source.
        writes DimensionMember · reads SourceEventTransformEventMap
      13. Load AccountStatus Dimensionsqlload_dimension_members_revfind__load_accountstatus_dimension.sqldisabled
        ✨ Builds the account status dimension by extracting unique encounter status values from the source encounter records.
        writes DimensionMember · reads SourceEncounter
      14. Load AllowedStatus Dimensionsqlload_dimension_members_revfind__load_allowedstatus_dimension.sql
        ✨ Builds allowed status dimensions to categorize how actual payments compare to expected insurance contract rates.
        writes DimensionMember
      15. Load DiagnosisCode Dimensionsqlload_dimension_members_revfind__load_diagnosiscode_dimension.sql
        ✨ Loads diagnosis code dimensions from charge data and assigns them to categories using standardized reference data.
        writes DimensionMember · reads DimensionMemberTransformCharge
        ↳ feeds from Load AllowedStatus Dimension, Load DenialCategory Dimension, Load Facility Dimensions, Load FacilityGroup Dimensions, Load FacilityType Dimensions, Load Modifier Dimension, Load PlaceOfService Dimensions, Load Provider Dimension, Load ProviderType Dimension, Load Source Insurance Dimension, Load State Dimension, Load Time Dimensions, LoadInsuranceDimensionMembers
      16. Load Modifier Dimensionsqlload_dimension_members_revfind__load_modifier_dimension.sql
        ✨ Builds the procedure modifier dimension by identifying unique modifiers used in charge records and applying descriptive labels.
        writes DimensionMember · reads ProcedureModifierTransformCharge
      17. Load State Dimensionsqlload_dimension_members_revfind__load_state_dimension.sql
        ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
        writes DimensionMember · reads Facility
      18. Load Source Insurance Dimensionsqlload_dimension_members_revfind__load_source_insurance_dimension.sql
        ✨ Loads the source insurance dimension by mapping insurance identifiers and names from the insurance mapping table.
        writes DimensionMember · reads InsuranceMap
    7. ▶Load-Custom-Properties-RevFindsub-job · 5↗Load custom properties from Load-Visits-RevFind
      1. Load Has Account Opportunity Adjusted TotalUpdateCustomPropertyFromQuery
        · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
        entity=Accountcustom property key=CustomProperty-[id]custom query
      2. Load Account Opportunity Adjusted TotalUpdateCustomPropertyFromQuery
        · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
        entity=Accountcustom property key=CustomProperty-[id]custom query
      3. Load Account Patient 65+UpdateCustomPropertyFromQuery
        · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
        entity=Accountcustom property key=CustomProperty-[id]custom query
      4. Load Has DenialUpdateCustomPropertyFromQuery
        · reads AccountAccountDeltaCustomPropertyMap
        entity=Accountcustom property key=CustomProperty-[id]custom query
      5. Load Uses 835UpdateCustomPropertyFromQuery
        · reads AccountDeltaCustomPropertyMapTransformChargeTransactionMeasure
        entity=Accountcustom property key=CustomProperty-[id]custom query
    8. ▶Revfind-AccountInsurance-Custom-Routingsub-job · 13↗Job to update Account and AccountInsurance tables prior to scoring for any custom rules needed to properly assign insurance and score (ie - Prefix Mapping, Age at Service mapping, etc.)
      1. Delete delta from AccountDeltaRoutingsqlrevfind_accountinsurance_custom_routing__delete_delta_from_accountdeltarouting.sql
        ✨ Clears existing routing entries for accounts identified in the current delta to prepare for updated insurance routing logic.
        writes AccountDeltaRouting · reads AccountDelta
      2. CVP_Midatlantic Insurance Routing (Anthem + Sentara Commercial)sqlrevfind_accountinsurance_custom_routing__cvp_midatlantic_insurance_routing_anthem_sentara_commercial.sqldisabled
        ✨ Routes specific commercial insurance accounts to Medicare plans for senior patients within the Mid-Atlantic facility group.
        writes AccountDeltaRouting · reads AccountDeltaFacilityaccountpatient
      3. Anthem Federal Routingsqlrevfind_accountinsurance_custom_routing__anthem_federal_routing.sql
        ✨ Identifies and routes Anthem Federal insurance accounts based on specific member identification number patterns and prefixes.
        writes AccountDeltaRouting · reads AccountDeltaAccountInsuranceFacilityaccountpatient
        ↳ feeds from Merge from AccountDeltaRouting
      4. United Healthcare to UHC Medicare Routingsqlrevfind_accountinsurance_custom_routing__united_healthcare_to_uhc_medicare_routing.sqldisabled
        ✨ Reassigns United Healthcare accounts to Medicare routing for patients aged sixty-five and older.
        writes AccountDeltaRouting · reads AccountDeltaFacilityaccountpatient
      5. Ohio Anthem/BCBS Routingsqlrevfind_accountinsurance_custom_routing__ohio_anthem_bcbs_routing.sql
        ✨ Routes Ohio-based Anthem accounts to specific plan types by analyzing historical eligibility response patterns for member ID prefixes.
        writes AccountDeltaRouting · reads AccountAccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDatapatienttransformAccountInsurance
        ↳ feeds from Merge from AccountDeltaRouting
      6. Humana Medicare Routingsqlrevfind_accountinsurance_custom_routing__humana_medicare_routing.sqldisabled
        ✨ Flags Humana accounts for Medicare routing based on member identification patterns within the insurance data.
        writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
      7. Grene Vision Aetna/Aetna Medicaresqlrevfind_accountinsurance_custom_routing__grene_vision_aetna_aetna_medicare.sql
        ✨ Routes Aetna accounts to Medicare plans based on specific member identification prefixes found in the insurance records.
        writes AccountDeltaRouting · reads AccountDeltaAccountInsuranceFacilityaccountpatient
        ↳ feeds from Merge from AccountDeltaRouting
      8. Eye Associates OK BCBS Mappingsqlrevfind_accountinsurance_custom_routing__eye_associates_ok_bcbs_mapping.sql
        ✨ Maps Oklahoma Blue Cross Blue Shield accounts to specific plan types using member prefixes and historical eligibility data.
        writes AccountDeltaRouting · reads AccountAccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDatatransformAccountInsurance
        ↳ feeds from Merge from AccountDeltaRouting
      9. Anthem Medicare Routingsqlrevfind_accountinsurance_custom_routing__anthem_medicare_routing.sqldisabled
        ✨ Routes Anthem insurance accounts to Medicare plans for patients who meet the age requirement for senior coverage.
        writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
      10. Patient Age >=65 Medicare Routing for All Remaining Payerssqlrevfind_accountinsurance_custom_routing__patient_age_65_medicare_routing_for_all_remaining_payers.sqldisabled
        ✨ Routes various commercial insurance accounts to corresponding Medicare plans for patients meeting the age requirement for senior coverage.
        writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
      11. Merge from AccountDeltaRoutingsqlrevfind_accountinsurance_custom_routing__merge_from_accountdeltarouting.sql
        ✨ Updates account and insurance tables with the final routed insurance keys determined during the custom routing process.
        writes AccountAccountInsurance · reads AccountDeltaRoutingaccountaccountDelta
        ↳ feeds from Anthem Federal Routing, Delete delta from AccountDeltaRouting, Eye Associates OK BCBS Mapping, Grene Vision Aetna/Aetna Medicare, Ohio Anthem/BCBS Routing
      12. Add Assumed Medicare Label to Medicare Routed Accountssqlrevfind_accountinsurance_custom_routing__add_assumed_medicare_label_to_medicare_routed_accounts.sql
        ✨ Applies a tracking label and documentation note to accounts that were automatically routed to Medicare plans.
        writes AccountLabelAccountNote · reads AccountDeltaAccountDeltaRoutingAccountLabelaccount
        ↳ feeds from Add B20 Denial Label, Anthem Federal Routing, Delete delta from AccountDeltaRouting, Eye Associates OK BCBS Mapping, Grene Vision Aetna/Aetna Medicare, Merge from AccountDeltaRouting, Ohio Anthem/BCBS Routing
      13. Add B20 Denial Labelsqlrevfind_accountinsurance_custom_routing__add_b20_denial_label.sql
        ✨ Flags accounts with specific denial codes from remittance data by applying a tracking label and documentation note.
        writes AccountLabelAccountNote · reads AccountDeltaAccountLabelAccountRemit
        ↳ feeds from Add Assumed Medicare Label to Medicare Routed Accounts
  9. ▶Revfind-AccountInsurance-Custom-Routingsub-job · 13↗Job to update Account and AccountInsurance tables prior to scoring for any custom rules needed to properly assign insurance and score (ie - Prefix Mapping, Age at Service mapping, etc.)
    1. Delete delta from AccountDeltaRoutingsqlrevfind_accountinsurance_custom_routing__delete_delta_from_accountdeltarouting.sql
      ✨ Clears existing routing entries for accounts identified in the current delta to prepare for updated insurance routing logic.
      writes AccountDeltaRouting · reads AccountDelta
    2. CVP_Midatlantic Insurance Routing (Anthem + Sentara Commercial)sqlrevfind_accountinsurance_custom_routing__cvp_midatlantic_insurance_routing_anthem_sentara_commercial.sqldisabled
      ✨ Routes specific commercial insurance accounts to Medicare plans for senior patients within the Mid-Atlantic facility group.
      writes AccountDeltaRouting · reads AccountDeltaFacilityaccountpatient
    3. Anthem Federal Routingsqlrevfind_accountinsurance_custom_routing__anthem_federal_routing.sql
      ✨ Identifies and routes Anthem Federal insurance accounts based on specific member identification number patterns and prefixes.
      writes AccountDeltaRouting · reads AccountDeltaAccountInsuranceFacilityaccountpatient
      ↳ feeds from Merge from AccountDeltaRouting
    4. United Healthcare to UHC Medicare Routingsqlrevfind_accountinsurance_custom_routing__united_healthcare_to_uhc_medicare_routing.sqldisabled
      ✨ Reassigns United Healthcare accounts to Medicare routing for patients aged sixty-five and older.
      writes AccountDeltaRouting · reads AccountDeltaFacilityaccountpatient
    5. Ohio Anthem/BCBS Routingsqlrevfind_accountinsurance_custom_routing__ohio_anthem_bcbs_routing.sql
      ✨ Routes Ohio-based Anthem accounts to specific plan types by analyzing historical eligibility response patterns for member ID prefixes.
      writes AccountDeltaRouting · reads AccountAccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDatapatienttransformAccountInsurance
      ↳ feeds from Merge from AccountDeltaRouting
    6. Humana Medicare Routingsqlrevfind_accountinsurance_custom_routing__humana_medicare_routing.sqldisabled
      ✨ Flags Humana accounts for Medicare routing based on member identification patterns within the insurance data.
      writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
    7. Grene Vision Aetna/Aetna Medicaresqlrevfind_accountinsurance_custom_routing__grene_vision_aetna_aetna_medicare.sql
      ✨ Routes Aetna accounts to Medicare plans based on specific member identification prefixes found in the insurance records.
      writes AccountDeltaRouting · reads AccountDeltaAccountInsuranceFacilityaccountpatient
      ↳ feeds from Merge from AccountDeltaRouting
    8. Eye Associates OK BCBS Mappingsqlrevfind_accountinsurance_custom_routing__eye_associates_ok_bcbs_mapping.sql
      ✨ Maps Oklahoma Blue Cross Blue Shield accounts to specific plan types using member prefixes and historical eligibility data.
      writes AccountDeltaRouting · reads AccountAccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDatatransformAccountInsurance
      ↳ feeds from Merge from AccountDeltaRouting
    9. Anthem Medicare Routingsqlrevfind_accountinsurance_custom_routing__anthem_medicare_routing.sqldisabled
      ✨ Routes Anthem insurance accounts to Medicare plans for patients who meet the age requirement for senior coverage.
      writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
    10. Patient Age >=65 Medicare Routing for All Remaining Payerssqlrevfind_accountinsurance_custom_routing__patient_age_65_medicare_routing_for_all_remaining_payers.sqldisabled
      ✨ Routes various commercial insurance accounts to corresponding Medicare plans for patients meeting the age requirement for senior coverage.
      writes AccountDeltaRouting · reads AccountAccountDeltaFacilitypatienttransformAccountInsurance
    11. Merge from AccountDeltaRoutingsqlrevfind_accountinsurance_custom_routing__merge_from_accountdeltarouting.sql
      ✨ Updates account and insurance tables with the final routed insurance keys determined during the custom routing process.
      writes AccountAccountInsurance · reads AccountDeltaRoutingaccountaccountDelta
      ↳ feeds from Anthem Federal Routing, Delete delta from AccountDeltaRouting, Eye Associates OK BCBS Mapping, Grene Vision Aetna/Aetna Medicare, Ohio Anthem/BCBS Routing
    12. Add Assumed Medicare Label to Medicare Routed Accountssqlrevfind_accountinsurance_custom_routing__add_assumed_medicare_label_to_medicare_routed_accounts.sql
      ✨ Applies a tracking label and documentation note to accounts that were automatically routed to Medicare plans.
      writes AccountLabelAccountNote · reads AccountDeltaAccountDeltaRoutingAccountLabelaccount
      ↳ feeds from Add B20 Denial Label, Anthem Federal Routing, Delete delta from AccountDeltaRouting, Eye Associates OK BCBS Mapping, Grene Vision Aetna/Aetna Medicare, Merge from AccountDeltaRouting, Ohio Anthem/BCBS Routing
    13. Add B20 Denial Labelsqlrevfind_accountinsurance_custom_routing__add_b20_denial_label.sql
      ✨ Flags accounts with specific denial codes from remittance data by applying a tracking label and documentation note.
      writes AccountLabelAccountNote · reads AccountDeltaAccountLabelAccountRemit
      ↳ feeds from Add Assumed Medicare Label to Medicare Routed Accounts
  10. Score-Accounts-Revfindsub-job
  11. ▶Load-Custom-Properties-RevFindsub-job · 5↗Load custom properties from Load-Visits-RevFind
    1. Load Has Account Opportunity Adjusted TotalUpdateCustomPropertyFromQuery
      · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
      entity=Accountcustom property key=CustomProperty-[id]custom query
    2. Load Account Opportunity Adjusted TotalUpdateCustomPropertyFromQuery
      · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
      entity=Accountcustom property key=CustomProperty-[id]custom query
    3. Load Account Patient 65+UpdateCustomPropertyFromQuery
      · reads AccountDeltaCustomPropertyMapInternalAccountAnalysis
      entity=Accountcustom property key=CustomProperty-[id]custom query
    4. Load Has DenialUpdateCustomPropertyFromQuery
      · reads AccountAccountDeltaCustomPropertyMap
      entity=Accountcustom property key=CustomProperty-[id]custom query
    5. Load Uses 835UpdateCustomPropertyFromQuery
      · reads AccountDeltaCustomPropertyMapTransformChargeTransactionMeasure
      entity=Accountcustom property key=CustomProperty-[id]custom query
  12. ▶Update-Dashboard-Tables-RevFindsub-job · 2↗
    1. ▶Load-Visit-Fact-RevFindsub-job · 1↗
      1. Load VisitFactsqlload_visit_fact_revfind__load_visitfact.sql
        ✨ Updates visit-level facts and metrics by refreshing data for visits with recent activity in payments, eligibility, or communications.
        writes VisitFactVisitFactDelta · reads EligibilityStatusMapEligibilityTxnPatientLetterPaymentTxnVisitVisitFactDelta
    2. UpdateDashboardTablesUpdateDashboardTables
      Regenerates optimized aggregate tables for all dashboard reports and updates their definitions to reflect the latest source data.
  13. Data-Integrity-Reportsub-job
  14. WorkqueueAssignmentWorkqueueAssignment
    Assigns accounts or visits to workqueue stacks by evaluating matching criteria and updating item lists based on search terms.
  15. ▶Load-Custom-Properties-RevFind-Copysub-job · 5↗Load custom properties from Load-Visits-RevFind
    1. Load Workqueue Custom Property Membersqlload_custom_properties_revfind_copy__load_workqueue_custom_property_member.sql
      ✨ Updates custom property members by mapping workqueue assignment criteria to specific reporting attributes.
      writes CustomPropertyMember · reads WorkqueueAssignmentCriteria
    2. Remove Workqueue Custom PropertiesRemoveCustomProperty
      · reads CustomPropertyMapWorkqueueWorkqueueAssignmentCriteriaWorkqueueCriteriaItemMapWorkqueueEntityMap
      entity=Accountcustom property key=CustomProperty-[id]custom query
    3. Load Workqueue Custom Property MapUpdateCustomPropertyFromQuery
      · reads WorkqueueWorkqueueAssignmentCriteriaWorkqueueCriteriaItemMapWorkqueueEntityMap
      entity=Accountcustom property key=CustomProperty-[id]custom query
    4. SyncBatchSyncBatchdisabled
      Synchronizes processed data from batch staging tables to production tables, updating visits, quotes, and custom property mappings.
      writes CustomPropertyMap · reads CustomPropertyMapBatch
      sync custom property mapDISABLED
    5. Delete and Insert Workqueue Dimensionsqlload_custom_properties_revfind_copy__delete_and_insert_workqueue_dimension.sql
      ✨ Builds the workqueue dimension by mapping specific custom property members to the dimension table.
      writes DimensionMember · reads CustomPropertyMember
      ↳ feeds from Load Workqueue Custom Property Member
  16. Refresh ad-hoc recovery reportsqldaily_accounts_processing_revfind__refresh_ad_hoc_recovery_report.sql
    ✨ Refreshes a detailed recovery report by joining account charges with internal analysis and appeal status for ad-hoc financial tracking.
    writes TempChargeRecoveryReport · reads AccountAccountAppealStatusAccountAppealSummaryAccountChargeAccountEstimateChargeChargeTypeCustomPropertyMapInternalAccountChargeAnalysis