sub-job

ECP · Load-Account-Data-RevFind

Load Account-Level Tables-RevFind

called by Daily-Accounts-Processing-RevFind

sub-job ECP/Load-Account-Data-RevFind · db ECP_ProdMirror · updated 2025-12-04

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

Load-Account-Data-RevFind

8 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

ReprocessChangedConfigurationsReprocessChangedConfigurations
Identify and queue visits or accounts affected by configuration changes to ensure historical data reflects updated pricing or insurance rules.
reprocess accounts
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
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
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
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
▶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
▶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
▶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