sub-job

SeaRad · Parse-HL7

called by Daily-Visit-Processing-HL7

sub-job SeaRad/Parse-HL7 · db SeaRad_ProdMirror · updated 2025-09-17

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

Parse-HL7

4 tasks · SeaRad

sub-job / Flow action SQL / native step▶ expand a sub-job inline · ↗ open it as its own page · a typed step opens its doc

DownloadDownload
Downloads files from remote FTP or SFTP servers to local directories with support for archiving and parallel transfers.
server=SeaRad_ProdMirror-mdcssh-sftpremote directory=/HL7local directory={HL7Directory}
LoadHL7FilesLoadHL7Files
Parses and imports patient demographics, visits, and charges from HL7 message files located in a specified directory.
message folder path={HL7Directory}message processed folder path={HL7Archive}add timestamp to file name
Update HL7ProcessLog + HL7Delta + Visit + VisitChargesqlparse_hl7__update_hl7processlog_hl7delta_visit_visitcharge.sql
✨ Logs HL7 message processing and identifies new patient and order updates from archived clinical data for downstream integration.
writes HL7DeltaHL7ProcessLogHL7ProcessLogArchiveKeys · reads HL7ArchiveHL7OrderHL7PatientHL7ProcessLogHL7ProcessLogArchiveKeysOrderSourceIDMapTransformPatient
▶Load-Transform-HL7sub-job · 12↗Update on [date] --> Added ECC=1 in Loadpatients task as per ticket# https://mdclarity.zendesk.com/agent/tickets/14141
1. TransformPatientsqlload_transform_hl7__transformpatient.sql
  ✨ Updates patient demographic and contact information by extracting and standardizing data from incoming HL7 ADT and order messages.
  writes TransformPatient · reads HL7DeltaHL7OrderHL7PatientTransformPatient
2. TransformPatientInsurancesqlload_transform_hl7__transformpatientinsurance.sql
  ✨ Identifies insurance changes from HL7 messages and updates standardized patient insurance records, including coordination of benefits and group details.
  writes PatientIDInsuranceDeltaTransformPatientInsurance · reads HL7DeltaHL7PatientInsuranceMapPatientIDInsuranceDeltaTransformPatientInsurance
3. Update PatientDateAppointmentIDMapsqlload_transform_hl7__update_patientdateappointmentidmap.sql
  ✨ Builds a mapping between patients and scheduled dates to generate unique internal identifiers for incoming HL7 messages.
  writes PatientDateAppointmentIDMap · reads HL7DeltaPatientDateAppointmentIDMap
4. Update OrderSourceIDDeltasqlload_transform_hl7__update_ordersourceiddelta.sql
  ✨ Updates order mappings and identifies appointments impacted by new messages, flagging those where all associated orders are cancelled.
  writes OrderSourceIDDeltaOrderSourceIDMap · reads HL7DeltaOrderSourceIDMapPatientDateAppointmentIDMapPatientIDInsuranceDeltaTransformVisit
  ↳ feeds from Merge TransformVisit, TransformPatientInsurance, Update PatientDateAppointmentIDMap
5. Load ImportTransformVisitsqlload_transform_hl7__load_importtransformvisit.sql
  ✨ Loads visit details into a staging table, mapping facility, provider, and service type information for upcoming appointment processing.
  writes ImportTransformVisit · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMap
  ↳ feeds from Update OrderSourceIDDelta
6. Merge TransformVisitsqlload_transform_hl7__merge_transformvisit.sql
  ✨ Merges staged visit data into the main transformation table by replacing existing records with updated appointment and facility information.
  writes TransformVisit · reads ImportTransformVisit
  ↳ feeds from Load ImportTransformVisit
7. Load ImportTransformVisitInsurancesqlload_transform_hl7__load_importtransformvisitinsurance.sql
  ✨ Loads insurance coverage details from HL7 orders into a staging table, organizing primary, secondary, and tertiary payers for each visit.
8. Merge TransformVisitInsurancesqlload_transform_hl7__merge_transformvisitinsurance.sql
  ✨ Updates the main insurance transformation table by replacing existing coverage records with the latest staged insurance information for each visit.
  writes TransformVisitInsurance · reads ImportTransformVisitInsurance
9. Load ImportTransformVisitChargesqlload_transform_hl7__load_importtransformvisitcharge.sql
  ✨ Loads procedure charges into a staging table, cross-walking clinical codes to standard billing formats and service types for cost estimation.
  writes ImportTransformVisitCharge · reads HL7OrderOrderSourceIDDeltaOrderSourceIDMapSeaRadCPTCrosswalk
  ↳ feeds from Update OrderSourceIDDelta
10. Merge TransformVisitChargesqlload_transform_hl7__merge_transformvisitcharge.sql
  ✨ Merges staged procedure charges into the main transformation table, ensuring the most recent billing codes are associated with each visit.
  writes TransformVisitCharge · reads ImportTransformVisitCharge
  ↳ feeds from Load ImportTransformVisitCharge
11. LoadPatientsLoadPatients
  Upsert patient records into the patient dimension.
  · reads PatientTransformPatient
  ↳ feeds from TransformPatient
  auto patient query
12. Update HL7ProcessLogsqlload_transform_hl7__update_hl7processlog.sql
  ✨ Updates the HL7 process log to mark the most recent active processing task as completed.
  · reads HL7ProcessLog