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

sub-job

TheOregonClinic · RevFind-EDI-Transform-EDIData

called by RevFind-EDI-Daily-Processing

sub-job TheOregonClinic/RevFind-EDI-Transform-EDIData · db TheOregonClinic_ProdMirror · updated 2026-06-12

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-EDI-Transform-EDIData

18 tasks · TheOregonClinic
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. Load RemitDeltasqlrevfind_edi_transform_edidata__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 TransformRemitsqlrevfind_edi_transform_edidata__load_transformremit.sql
    ✨ Extracts and transforms electronic remittance advice data from raw transaction files into a structured format for processing.
    writes TransformRemit · reads Txn835Txn835ClaimPaymentTxn835ClaimPaymentLineTxn835ClaimPaymentOtherClaimAmount
  3. Load TransformRemitEncounterMapsqlrevfind_edi_transform_edidata__load_transformremitencountermap.sqldisabled
    ✨ Maps remittance claim identifiers to internal account records to link insurance payments with specific patient encounters.
    writes TransformRemitEncounterMap · reads TransformRemitEncounterMap
  4. Update TransformRemitEncounterMapsqlrevfind_edi_transform_edidata__update_transformremitencountermap.sqldisabled
    ✨ Updates remittance mapping records with patient account identifiers by matching service dates and charge IDs at the line level.
    · reads TransformChargeTransformRemitEncounterMapTransformRemitLinerem
  5. Load TempRemitCOB (old)sqlrevfind_edi_transform_edidata__load_tempremitcob_old.sqldisabled
    ✨ Builds a temporary table to determine claim status, member IDs, and coordination of benefits order for remittance data.
  6. Load TempRemitCOBsqlrevfind_edi_transform_edidata__load_tempremitcob.sql
    ✨ Determines the coordination of benefits order and current claim status by analyzing historical remittance and adjustment data.
    writes TempRemitCOBTransformRemit · reads TempRemitCOBTransformRemitTransformRemitAdjustment
    ↳ feeds from Load TransformRemit, Load TransformRemitAdjustment
  7. Load TransformRemitAdjustmentsqlrevfind_edi_transform_edidata__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 TransformRemitLinesqlrevfind_edi_transform_edidata__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 TransformTxn837sqlrevfind_edi_transform_edidata__load_transformtxn837.sql
    ✨ Consolidates and transforms professional and institutional claim submission data into a unified format for billing tracking.
    writes TransformTxn837 · reads Txn837ITxn837IServiceLineTxn837PTxn837PServiceLine
  10. Load TransformTxn837Linesqlrevfind_edi_transform_edidata__load_transformtxn837line.sql
    ✨ Builds a unified table of professional and institutional claim service lines, extracting procedure codes, charges, and provider details.
    writes TransformTxn837Line · reads TransformTxn837Txn837ITxn837IServiceLineTxn837PServiceLine
    ↳ feeds from Load TransformTxn837
  11. Load TransformAccountsqlrevfind_edi_transform_edidata__load_transformaccount.sql
    ✨ Loads account-level records by mapping claim data to patients, providers, and insurance payers using the latest claim versions.
    writes TransformAccount · reads EDIFacilityMapTransformTxn837
    ↳ feeds from Load TransformTxn837
  12. Load TransformChargesqlrevfind_edi_transform_edidata__load_transformcharge.sql
    ✨ Transforms claim service lines into individual charge records linked to accounts, capturing procedure codes, units, and financial amounts.
    writes TransformCharge · reads TransformAccountTransformTxn837TransformTxn837Line
    ↳ feeds from Load TransformAccount, Load TransformTxn837, Load TransformTxn837Line
  13. Load TransformChargeFamilyPoolsqlrevfind_edi_transform_edidata__load_transformchargefamilypool.sql
    ✨ Maps historical claim service lines to their latest charge versions to establish a consistent charge family for longitudinal tracking.
    writes TransformChargeFamilyPool · reads TransformChargeTransformChargeFamilyPoolTransformTxn837TransformTxn837Line
    ↳ feeds from Load TransformCharge, Load TransformTxn837, Load TransformTxn837Line
  14. Load TransformPatientMergeSql
    writes TransformPatient · reads TransformAccountTransformTxn837
    ↳ feeds from Load TransformAccount, Load TransformTxn837
    merge data source type=ThisDatabasetable name=TransformPatientmerge mode=InsertUpdatebulk copy timeout seconds=300bulk copy use table lock
  15. Load TransformRemitAccountMapsqlrevfind_edi_transform_edidata__load_transformremitaccountmap.sql
    ✨ Matches remittance advice records to patient accounts using tiered logic based on identifiers like Tax ID and service dates.
    writes TransformRemitAccountMap · reads TransformAccountTransformRemit
    ↳ feeds from Load TempRemitCOB, Load TransformAccount, Load TransformRemit
  16. Load TransformRemitTransactionssqlrevfind_edi_transform_edidata__load_transformremittransactions.sql
    ✨ Consolidates remittance payments and adjustments into a single transaction table, categorizing reason codes for financial reporting and analysis.
    writes TransformRemitTransactions · reads AdjustmentCodeRollupTransformRemitTransformRemitAdjustmentTransformRemitLine
    ↳ feeds from Load TempRemitCOB, Load TransformRemit, Load TransformRemitAdjustment, Load TransformRemitLine
  17. Load TransformChargeRemitMatchsqlrevfind_edi_transform_edidata__load_transformchargeremitmatch.sql
    ✨ Matches normalized remittance service lines to specific charges within the charge family pool using tiered logic and procedure codes.
    writes TransformChargeRemitMatch · reads TransformChargeFamilyPoolTransformChargeRemitMatchTransformRemitTransformRemitAccountMapTransformRemitLine
    ↳ feeds from Load TempRemitCOB, Load TransformChargeFamilyPool, Load TransformRemit, Load TransformRemitAccountMap, Load TransformRemitLine
  18. Load TransformAccountRemitsqlrevfind_edi_transform_edidata__load_transformaccountremit.sql
    ✨ Links remittance transactions to specific charges or accounts, providing a detailed view of payments and adjustments at the charge level.
    writes TransformAccountRemit · reads TransformAccountRemitTransformChargeRemitMatchTransformRemitAccountMapTransformRemitTransactions
    ↳ feeds from Load TransformChargeRemitMatch, Load TransformRemitAccountMap, Load TransformRemitTransactions