SMS (Shared Medical Systems) was Catholic Healthcare Trust's legacy hospital information and patient-accounting system. Syntra ETL archived ten years of SMS patient-financial, billing and registration data into the Oracle Autonomous (ATP) archive, with APEX and Power BI reporting, so records stay available for insurance/payer, legal and audit queries after the system was retired.
SMS held patient-account, billing and registration history — records tied to HIPAA, payer/insurance queries and legal discovery, with long retention requirements.
SMS (Shared Medical Systems, later part of Siemens) was CHT's hospital information system for patient registration, accounting and billing. Patient-financial records, claims and remittance history are routinely requested by payers and insurers, subpoenaed in legal matters, and reviewed in audits — and must be retained for years.
CHT archived ten years of SMS data into the Oracle ATP archive as part of the five-system project (~8 TB and ~150,000 attachments in total), then decommissioned SMS while keeping every record accessible and reportable.
Retiring SMS came with real data-engineering challenges.
The source systems were out of vendor support — raising security, compliance and continuity risk. Archiving and decommissioning removed that risk.
Minimal documentation survived and few functional users remained who understood the applications, so we reverse-engineered the data model and validated it with the business.
With no clean interface, we connected directly to the SMS SQL database to extract the complete patient-financial and billing history.
The system held thousands of tables; we profiled the schema to find exactly which held the patient-financial records worth archiving.
Ten years of records, consolidated into the Oracle ATP archive.
Account history, balances and financial classes.
Claims, charges, payments and remittance history.
Registration, admit/discharge/transfer and demographic history.
Charge detail, adjustments and payment posting.
Patient statements and related attachments.
Insurance/payer records tied to accounts and claims.
A governed sequence — completed and live in production.
Profiled the SMS source and finalized the ten-year archival record set — tables, fields and attachments in scope.
Extracted ten years of SMS history from the source, including documents/attachments, and standardized it for the archive.
Loaded the data into the consolidated Oracle Autonomous Transaction Processing (ATP) archival database.
Built the 15–20 most-used SMS reports in Oracle APEX and Microsoft Power BI for daily business needs.
Validated the archived SMS data against source, deployed with role-based access and audit logging, and went live in production.
Retained a complete backup of the original data on the cloud, and provided ongoing Syntra support for any query.
What Catholic Healthcare Trust staff use every day.
Account-history and financial-class reports for billing and audit queries.
Claims, charge-detail and remittance reports for payer/insurance queries.
Registration and ADT history reports.
Staff search and browse the archived records themselves in the Oracle APEX portal, with filters and Excel/PDF export.
A full backup of the original source data is retained on the cloud, in addition to the archive.
Syntra provides ongoing support for any query on the archived data, if and when one arises.
Ten years of SMS patient-financial, billing and registration data was archived to satisfy insurance/payer, legal and audit retention needs, and it stays searchable in the Oracle ATP archive.
Yes — patient accounts, claims, charges and remittance history are archived and reportable via APEX and Power BI, so billing, compliance and legal teams can respond to payer and insurance queries after SMS is retired.
Yes — access is role-based and fully audit-logged, appropriate for sensitive patient-financial data, with a complete cloud backup of the original data and ongoing Syntra support.
15–20 of the most-used SMS reports were built in Oracle APEX and Power BI — patient-account, billing/remittance and registration reports — for daily business needs.
Retiring a system like SMS? We can archive 10+ years of history into a secure, reportable Oracle archive — with backup and support. Tell us your systems.