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

Batch / file feed · RevFind pipeline

OneGI · RevFind-Daily-Processing

Process ECW Accounts

pipeline OneGI/RevFind-Daily-Processing · db OneGI_ProdMirror · updated 2026-05-11
classification: revfind · 0 Flow markers

trigger

How it's scheduled

Daily batch — once at 23: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 - ONEGIdaily at 23: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)
→
Contract model
CTR
→
Payer rules
→
Work queue & sync

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

Expected reimbursementDenial / underpaymentCPT / procedure logic

Payers in SQLAetnaAnthemBCBSHumanaMedicaidMedicareUnitedHealthcare

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

RevFind-Daily-Processing

11 tasks · OneGI
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. ▶RevFind-Source-Load-PM-Datasub-job · 17↗Main job that calls all other Source jobs to load each file
    1. DownloadDownload
      Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
      server=OneGI_ProdMirror-mdcssh-sftpremote directory=/local directory={SourceDataFolderECW}remote archive directory=/Archive/
    2. ▶RevFind-Source-Load-ECW-ChargeItemssub-job · 1↗
      1. Load ECW ExportChargeItemsProcessFile
        writes ImportECWChargeItemsSourceECWChargeItems
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWChargeItemsrow terminator=Newline
    3. ▶RevFind-Source-Load-ECW-Doctorssub-job · 1↗
      1. Load ECW ExportDoctorsProcessFile
        writes ImportECWDoctorsSourceECWDoctors
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWDoctorsrow terminator=Newline
    4. ▶RevFind-Source-Load-ECW-EdiInvAdjustmentssub-job · 1↗
      1. Load ECW EdiInvAdjustmentsProcessFile
        writes ImportECWEdiInvAdjustmentsSourceECWEdiInvAdjustments
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvAdjustmentsrow terminator=Newline
    5. ▶RevFind-Source-Load-ECW-EdiInvCPTsub-job · 1↗
      1. Load ECW EdiInvCPTProcessFile
        writes ImportECWEdiInvCPTSourceECWEdiInvCPT
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvCPTrow terminator=Newline
    6. ▶RevFind-Source-Load-ECW-EdiInvDiagnosissub-job · 1↗
      1. Load ECW ExportEdiInvDiagnosisProcessFile
        writes ImportECWEdiInvDiagnosisSourceECWEdiInvDiagnosis
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvDiagnosisrow terminator=Newline
    7. ▶RevFind-Source-Load-ECW-EdiInvEobCassub-job · 1↗
      1. Load ECW ExportEdiInvEobCasProcessFile
        writes ImportECWEdiInvEobCasSourceECWEdiInvEobCas
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvEobCasrow terminator=Newline
    8. ▶RevFind-Source-Load-ECW-EdiInvEobsub-job · 1↗
      1. Load ECW ExportEdiInvEobProcessFile
        writes ImportECWEdiInvEOBSourceECWEdiInvEOB
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvEOBrow terminator=Newline
    9. ▶RevFind-Source-Load-ECW-EdiInvInsurancesub-job · 1↗
      1. Load ECW Export EdiInvInsuranceProcessFile
        writes ImportECWEdiInvInsuranceSourceECWEdiInvInsurance
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvInsurancerow terminator=Newline
    10. ▶RevFind-Source-Load-ECW-EdiInvoicesub-job · 1↗
      1. Load ECW ExportEdiInvoiceProcessFile
        writes ImportECWEdiInvoiceSourceECWEdiInvoice
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiInvoicerow terminator=Newline
    11. ▶RevFind-Source-Load-ECW-EdiPaymentDetailsub-job · 1↗
      1. Load ECW ExportEdiPaymentDetailProcessFile
        writes ImportECWEdiPaymentDetailSourceECWEdiPaymentDetail
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiPaymentDetailrow terminator=Newline
    12. ▶RevFind-Source-Load-ECW-EdiRemarkCodessub-job · 1↗
      1. Load ECW ExportEdiRemarkCodesProcessFile
        writes ImportECWEdiRemarkCodesSourceECWEdiRemarkCodes
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWEdiRemarkCodesrow terminator=Newline
    13. ▶RevFind-Source-Load-ECW-Facilitiessub-job · 1↗
      1. Load ECW FacilitiesProcessFile
        writes ImportECWFacilitiesSourceECWFacilities
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWFacilitiesrow terminator=Newline
    14. ▶RevFind-Source-Load-ECW-FeeSchedulesub-job · 1↗
      1. Load ECW ExportFeeScheduleProcessFile
        writes ImportECWFeeScheduleSourceECWFeeSchedule
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWFeeSchedulerow terminator=Newline
    15. ▶RevFind-Source-Load-ECW-Insurancesub-job · 1↗
      1. Load ECW ExportInsuranceProcessFile
        writes ImportECWInsuranceSourceECWInsurance
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWInsurancerow terminator=Newline
    16. ▶RevFind-Source-Load-ECW-PatientInsurancesub-job · 1↗
      1. Load ECW ExportPatientInsuranceProcessFile
        writes ImportECWPatientInsuranceSourceECWPatientInsurance
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWPatientInsurancerow terminator=Newline
    17. ▶RevFind-Source-Load-ECW-Patientssub-job · 1↗
      1. Load ECW ExportPatientsProcessFile
        writes ImportECWPatientsSourceECWPatients
        file path={FilePath}archive directory={SourceDataArchiveECW}base table name=ECWPatientsrow terminator=Newline
  2. ▶RevFind-Transform-InsuranceMap-ECWsub-job · 1↗Loads the InsuranceMap table
    1. Load-InsuranceMapLoad-InsuranceMap
      writes InsuranceMap · reads InsuranceMapSourceECWInsurance
      entity type=InsuranceMapcustom querykey prefix=IM
  3. ▶RevFind-Transform-ChargeTypesub-job · 6↗Loads the ChargeType Table, and the ChargeType, ProcedureCode, ProcedureCodeCategory dimensions
    1. Merge ChargeTypeMergeSql
      writes ChargeType · reads SourceECWChargeItemsSourceECWEdiInvCPT
      merge data source type=ThisDatabasetable name=ChargeTypemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    2. Merge ProcedureCode DimensionMemberMergeSql
      writes DimensionMember · reads ChargeTypeProcedureCode
      ↳ feeds from Merge ChargeType
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    3. Merge ChargeType into DimensionMemberMergeSql
      writes DimensionMember · reads ChargeType
      ↳ feeds from Merge ChargeType
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Update ChargeMaster SchedulesUpdateFeePricingdisabled
      writes FeePricingRecord · reads SourceChargeMaster
      schedule insert type=Replaceschedule type=Chargessource table=SourceChargeMasterDISABLED
    5. Load Charges SchedulesLoadFeeSchedulesdisabled
      Generate time-based fee schedules from pricing records to establish effective date ranges for facility and insurance billing combinations.
      writes ScheduleScheduleMap · reads FeePricingRecord
      DISABLED
    6. Merge ProcedureCategory DimensionMergeSql
      writes DimensionMember · reads DimensionMember
      ↳ feeds from Merge ChargeType into DimensionMember, Merge ProcedureCode DimensionMember
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  4. ▶RevFind-Transform-Accountssub-job · 11↗Account Transformation Steps
    1. Merge AccountKeyMapMergeSql
      writes AccountKeyMap · reads SourceECWEdiInvoice
      merge data source type=ThisDatabasetable name=AccountKeyMapmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    2. Load AccountDeltasqlrevfind_transform_accounts__load_accountdelta.sql
      ✨ Identifies and loads accounts that have been modified across various source tables since the last processing date.
      writes AccountDelta · reads AccountDeltaDateAccountKeyMapSourceECWEdiInvAdjustmentsSourceECWEdiInvCPTSourceECWEdiInvDiagnosisSourceECWEdiInvEOBSourceECWEdiInvEobCasSourceECWEdiInvInsurance
      ↳ feeds from Merge AccountKeyMap, Update AccountDeltaDate
    3. Merge TransformPatientMergeSql
      writes TransformPatient · reads SourceECWPatients
      merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    4. Load TransformTransaction (delete/insert)sqlrevfind_transform_accounts__load_transformtransaction_delete_insert.sql
      ✨ Updates the transaction transformation table by processing and categorizing electronic remittance advice data for modified accounts.
      writes TransformTransaction · reads AccountDeltaAccountKeyMapAccountLevelPaymentsAdjustmentCodeRollupSourceECWEdiInvAdjustmentsSourceECWEdiInvCPTSourceECWEdiInvEOBSourceECWEdiInvEobCas
      ↳ feeds from Load AccountDelta, Merge AccountKeyMap
    5. Merge TransformTransactionsMergeSqldisabled
      writes TransformTransaction · reads AccountKeyMapAdjustmentCodeRollupSourceECWEdiInvAdjustmentsSourceECWEdiInvCPTSourceECWEdiInvEOBSourceECWEdiInvEobCasSourceECWEdiInvInsuranceSourceECWEdiInvoice
      merge data source type=ThisDatabasetable name=TransformTransactionmerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    6. Merge TransformChargeMergeSql
      writes TransformCharge · reads AccountKeyMapSourceECWDoctorsSourceECWEDIInvoiceSourceECWEdiInvCPTSourceECWEdiInvDiagnosisSourceECWEdiInvInsuranceSourceECWFacilities
      ↳ feeds from Merge AccountKeyMap
      merge data source type=ThisDatabasetable name=TransformChargemerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    7. Merge TransformAccountMergeSql
      writes TransformAccount · reads AccountKeyMapSourceECWEdiInvDiagnosisSourceECWEdiInvInsuranceSourceECWEdiInvoice
      ↳ feeds from Merge AccountKeyMap
      merge data source type=ThisDatabasetable name=TransformAccountmerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    8. Merge TransformChargeTransactionMeasuresMergeSql
      writes TransformChargeTransactionMeasure · reads AccountKeyMapTransformChargeTransformTransaction
      ↳ feeds from Load TransformTransaction (delete/insert), Merge AccountKeyMap, Merge TransformCharge
      merge data source type=ThisDatabasetable name=TransformChargeTransactionMeasuremerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
    9. Merge TransformPatientInsuranceMergeSqldisabled
      writes TransformPatientInsurance · reads InsuranceMapSourceECWPatientInsurance
      merge data source type=ThisDatabasetable name=TransformPatientInsurancemerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    10. Merge TransformAccountInsuranceMergeSql
      writes TransformAccountInsurance · reads AccountKeyMapSourceECWEdiInvInsuranceSourceECWEdiInvoice
      ↳ feeds from Merge AccountKeyMap
      merge data source type=ThisDatabasetable name=TransformAccountInsurancemerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    11. Update AccountDeltaDatesqlrevfind_transform_accounts__update_accountdeltadate.sql
      ✨ Updates the reference timestamp to track the last time account changes were processed in the pipeline.
      writes AccountDeltaDate
  5. ▶RevFind-Process-Entitiessub-job · 3↗Load Facility, Provider, Patients
    1. Load FacilityLoadEntities
      writes Facility · reads FacilitySourceECWFacilities
      entity type=Facilitycustom querykey prefix=F
    2. Merge ProvidersMergeSql
      writes Provider · reads SourceECWDoctorsTransformAccount
      merge data source type=ThisDatabasetable name=Providermerge mode=InsertUpdateSoftDeletebulk copy timeout seconds=300bulk copy use table lock
    3. Load PatientsLoadPatients
      Upsert patient records into the patient dimension.
      · reads PatientTransformPatient
      custom patient query
  6. ▶RevFind-Process-DimensionMemberssub-job · 10↗Loads all dimensions used for filtering and reporting
    1. Load Dimension MembersLoadDimensionMembers
      Synchronize facility, provider, and insurance metadata into the dimension member table to refresh dashboard filters and reporting categories.
      writes DimensionMember
      load insuranceload quote insuranceload eligibility insuranceload providerload bill type
    2. Merge DenialCategory DimensionMergeSql
      writes DimensionMember · reads AdjustmentCodeRollup
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    3. Merge Place Of Service DimensionMergeSql
      writes DimensionMember · reads SourceECWEdiInvoiceSourceECWFacilities
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    4. Merge Allowed Status DimensionMergeSql
      writes DimensionMember
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    5. Merge Diagnosis Code DimensionMergeSql
      writes DimensionMember · reads DimensionMemberSourceECWEdiInvDiagnosis
      ↳ feeds from Load Dimension Members, Merge Allowed Status Dimension, Merge DenialCategory Dimension, Merge Facility Dimension, Merge Facility Group Dimension, Merge Facility Type Dimension, Merge Modifier Dimension, Merge Place Of Service Dimension, Merge ProviderType Dimension
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    6. Merge Modifier DimensionMergeSql
      writes DimensionMember · reads ProcedureModifierTransformCharge
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    7. Merge Facility Group DimensionMergeSql
      writes DimensionMember · reads FacilitySourceFacilityGroupToTIN
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    8. Merge Facility DimensionMergeSql
      writes DimensionMember · reads Facility
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    9. Merge Facility Type DimensionMergeSql
      writes DimensionMember · reads Facility
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    10. Merge ProviderType DimensionMergeSql
      writes DimensionMember · reads Provider
      merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  7. ▶RevFind-Process-Accountssub-job · 8↗Load Account-Level Tables
    1. Augment AccountDeltaReprocessChangedConfigs
      reprocess accounts
    2. Augment AccountDelta ReprocessChangedConfigurationssqlrevfind_process_accounts__augment_accountdelta_reprocesschangedconfigurations.sql
      ✨ Flags specific patient accounts for reprocessing by adding them to the delta tracking table based on configuration changes.
      writes AccountDelta · reads AccountDeltaAccountsToReprocess
    3. Merge ChargeMeasuresMergeSql
      writes ChargeMeasures · reads TransformChargeTransformChargeTransactionMeasureTransformTransaction
      merge data source type=ThisDatabasetable name=ChargeMeasuresmerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
    4. LoadAccountDataLoadAccountData
      Imports historical billing data from external systems into core account tables while preserving manual user modifications and application fields.
      · reads AccountChargeAccountDeltaAccountKeyMapChargeMeasuresChargeTypeFacilityInsuranceMapPatient
      ↳ feeds from Augment AccountDelta ReprocessChangedConfigurations, Merge ChargeMeasures
      custom account charge querycustom account querycustom account remit querycustom account insurance query
    5. ▶RevFind-Process-CustomPropertiessub-job · 1↗Loads any Custom Properties used for business workflow use cases
      1. DeletedClaim - UpdateCustomPropertyFromQueryUpdateCustomPropertyFromQuery
        · reads AccountAccountKeyMapCustomPropertyMap
        entity=Accountcustom property key=DeletedClaimcustom query
    6. ▶RevFind-Process-DimensionMemberssub-job · 10↗Loads all dimensions used for filtering and reporting
      1. Load Dimension MembersLoadDimensionMembers
        Synchronize facility, provider, and insurance metadata into the dimension member table to refresh dashboard filters and reporting categories.
        writes DimensionMember
        load insuranceload quote insuranceload eligibility insuranceload providerload bill type
      2. Merge DenialCategory DimensionMergeSql
        writes DimensionMember · reads AdjustmentCodeRollup
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      3. Merge Place Of Service DimensionMergeSql
        writes DimensionMember · reads SourceECWEdiInvoiceSourceECWFacilities
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      4. Merge Allowed Status DimensionMergeSql
        writes DimensionMember
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      5. Merge Diagnosis Code DimensionMergeSql
        writes DimensionMember · reads DimensionMemberSourceECWEdiInvDiagnosis
        ↳ feeds from Load Dimension Members, Merge Allowed Status Dimension, Merge DenialCategory Dimension, Merge Facility Dimension, Merge Facility Group Dimension, Merge Facility Type Dimension, Merge Modifier Dimension, Merge Place Of Service Dimension, Merge ProviderType Dimension
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      6. Merge Modifier DimensionMergeSql
        writes DimensionMember · reads ProcedureModifierTransformCharge
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      7. Merge Facility Group DimensionMergeSql
        writes DimensionMember · reads FacilitySourceFacilityGroupToTIN
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      8. Merge Facility DimensionMergeSql
        writes DimensionMember · reads Facility
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      9. Merge Facility Type DimensionMergeSql
        writes DimensionMember · reads Facility
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      10. Merge ProviderType DimensionMergeSql
        writes DimensionMember · reads Provider
        merge data source type=ThisDatabasetable name=DimensionMembermerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    7. ▶Revfind-Process-Post-Load-Account-Routingsub-job · 8↗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. Create AccountDeltaRouting Tablesqlrevfind_process_post_load_account_routing__create_accountdeltarouting_table.sql
        ✨ Builds a staging table to store insurance routing logic and comments for accounts requiring updates.
      2. GAVA - Anthem Routingsqlrevfind_process_post_load_account_routing__gava_anthem_routing.sqldisabled
        ✨ Routes Anthem and BCBS accounts to specific plan categories based on member ID prefixes and eligibility data.
        writes AccountDeltaRouting · reads AccountDeltaSourceAnthemBCBSEligibilityPrefixDataaccountfacilitytransformAccountInsurance
      3. GCM - Anthem TN Routingsqlrevfind_process_post_load_account_routing__gcm_anthem_tn_routing.sqldisabled
        ✨ Routes Tennessee Anthem accounts to specific provider networks based on member ID prefixes and plan names.
        writes AccountDeltaRouting · reads AccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDataaccounttransformAccountInsurance
      4. AccountDeltaRouting Mergesqlrevfind_process_post_load_account_routing__accountdeltarouting_merge.sqldisabled
        ✨ Updates account and insurance records with new routing information and appends relevant processing notes to the account history.
        writes AccountAccountInsuranceAccountNote · reads AccountDeltaRoutingAccountNoteaccountDelta
      5. Merge Account Label CommentsMergeSqldisabled
        writes AccountNote · reads AccountDeltaAccountDeltaRouting
        merge data source type=ThisDatabasetable name=AccountNotemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
      6. GAVA - UHC Physician Statussqlrevfind_process_post_load_account_routing__gava_uhc_physician_status.sql
        ✨ Flags UnitedHealthcare account charges with physician or non-physician status based on provider credentials for differential rate calculation.
        · reads AccountChargeCommonDataaccountaccountDeltaprovider
      7. GAVA - VA Region (Anthem + Aetna + Human Medicare)sqlrevfind_process_post_load_account_routing__gava_va_region_anthem_aetna_human_medicare.sql
        ✨ Appends geographic region codes to account charges for specific payers based on facility zip codes.
        · reads AccountChargeAccountDeltaaccountfacilitysourceVAFacilityRegion
      8. ALL UHC - Add UHC procedure category to code stringsqlrevfind_process_post_load_account_routing__all_uhc_add_uhc_procedure_category_to_code_string.sql
        ✨ Updates account charges by appending United Healthcare procedure categories to code strings for relevant insurance accounts.
        · reads AccountAccountChargeChargeTypeaccountDelta
    8. ▶RevFind-Process-Scoringsub-job · 2↗Incrementally scores all accounts + medicare scenarios
      1. Score All AccountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        incrementalupdate dimensions
      2. Score Medicare Scenario AccountsBatchProcessAccounts
        writes AccountEstimate · reads QuoteBatchVisitBatch
        scenario insurance=Medicareincremental
  8. ▶Revfind-Process-Post-Load-Account-Routingsub-job · 8↗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. Create AccountDeltaRouting Tablesqlrevfind_process_post_load_account_routing__create_accountdeltarouting_table.sql
      ✨ Builds a staging table to store insurance routing logic and comments for accounts requiring updates.
    2. GAVA - Anthem Routingsqlrevfind_process_post_load_account_routing__gava_anthem_routing.sqldisabled
      ✨ Routes Anthem and BCBS accounts to specific plan categories based on member ID prefixes and eligibility data.
      writes AccountDeltaRouting · reads AccountDeltaSourceAnthemBCBSEligibilityPrefixDataaccountfacilitytransformAccountInsurance
    3. GCM - Anthem TN Routingsqlrevfind_process_post_load_account_routing__gcm_anthem_tn_routing.sqldisabled
      ✨ Routes Tennessee Anthem accounts to specific provider networks based on member ID prefixes and plan names.
      writes AccountDeltaRouting · reads AccountDeltaFacilitySourceAnthemBCBSEligibilityPrefixDataaccounttransformAccountInsurance
    4. AccountDeltaRouting Mergesqlrevfind_process_post_load_account_routing__accountdeltarouting_merge.sqldisabled
      ✨ Updates account and insurance records with new routing information and appends relevant processing notes to the account history.
      writes AccountAccountInsuranceAccountNote · reads AccountDeltaRoutingAccountNoteaccountDelta
    5. Merge Account Label CommentsMergeSqldisabled
      writes AccountNote · reads AccountDeltaAccountDeltaRouting
      merge data source type=ThisDatabasetable name=AccountNotemerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
    6. GAVA - UHC Physician Statussqlrevfind_process_post_load_account_routing__gava_uhc_physician_status.sql
      ✨ Flags UnitedHealthcare account charges with physician or non-physician status based on provider credentials for differential rate calculation.
      · reads AccountChargeCommonDataaccountaccountDeltaprovider
    7. GAVA - VA Region (Anthem + Aetna + Human Medicare)sqlrevfind_process_post_load_account_routing__gava_va_region_anthem_aetna_human_medicare.sql
      ✨ Appends geographic region codes to account charges for specific payers based on facility zip codes.
      · reads AccountChargeAccountDeltaaccountfacilitysourceVAFacilityRegion
    8. ALL UHC - Add UHC procedure category to code stringsqlrevfind_process_post_load_account_routing__all_uhc_add_uhc_procedure_category_to_code_string.sql
      ✨ Updates account charges by appending United Healthcare procedure categories to code strings for relevant insurance accounts.
      · reads AccountAccountChargeChargeTypeaccountDelta
  9. ▶RevFind-Process-Scoringsub-job · 2↗Incrementally scores all accounts + medicare scenarios
    1. Score All AccountsBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      incrementalupdate dimensions
    2. Score Medicare Scenario AccountsBatchProcessAccounts
      writes AccountEstimate · reads QuoteBatchVisitBatch
      scenario insurance=Medicareincremental
  10. ▶Revfind-Process-ContractModelingsub-job · 1↗Scores all scenario models in a "Queued" state
    1. ProcessScenarioModelProcessScenarioModel
      Calculates account estimates for queued scenario models to perform what-if analysis for contract modeling and pricing.
  11. ▶RevFind-Process-Reportssub-job · 1↗
    1. Recovery Reportsqlrevfind_process_reports__recovery_report.sql
      ✨ Builds a temporary report comparing initial and current allowed amounts to track financial recovery from insurance appeals.
      writes TempChargeRecoveryReport · reads AccountAccountAppealStatusAccountAppealSummaryAccountChargeAccountEstimateChargeChargeTypeFacilityPatient