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

sub-job

ECP · Revfind-AccountInsurance-Custom-Routing

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

called by Load-Account-Data-RevFind, Daily-Accounts-Processing-RevFind

sub-job ECP/Revfind-AccountInsurance-Custom-Routing · db ECP_ProdMirror · updated 2026-05-20

task tree

What the sub-job runs

Tasks in order, each with what it does — typed stepsfrom the MDClarity task docs (click to open the reference), sub-jobsyou can expand inline (▶) to reveal their own tasks, and SQL steps summarized by Gemini (✨). Every step shows the tables it writes/reads and which earlier step it feeds from(sqlglot lineage).

sub-job

Revfind-AccountInsurance-Custom-Routing

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