Syncing Oracle InForm Queries with Python

A study runs its discrepancy management in an external tracker but issues and resolves queries inside Oracle InForm, and the two stop agreeing within a day: a data manager closes a query in the tracker while InForm still shows the site an Opened marking, a re-run of the sync fires a second <Query> submission and InForm now displays two markings on the same item, and a Candidate query that was never issued to the site leaks into the tracker as if it were live. Clinical data managers see phantom open counts, Python ETL engineers watch a re-sync duplicate markings that InForm will not let them delete, and reviewers find an audit trail that only tells half the story. This page sits inside Query Routing Workflows for CRAs, part of the broader Clinical Query Generation & Discrepancy Management discipline, and is the InForm-specific companion to Syncing Discrepancy Status Across Multiple EDC Vendors: where that guide builds the vendor-neutral canonical state machine, this one maps InForm’s marking model onto it and drives writes back into InForm through its ODM web services, reusing the read/authentication mechanics from Extracting Data from Oracle InForm with Python. Built correctly, every push and closure is idempotent and each change is attributable under 21 CFR Part 11 on both sides of the sync.

The worker below reads InForm’s current query markings, diffs them against the external tracker’s canonical state, and pushes only the deltas back into InForm as ODM marking submissions — never re-sending a marking that already exists.

Root Cause: How InForm’s Marking Model Differs from Rave and Veeva Query Models #

A sync built against Medidata Rave or Veeva Vault assumes the query is the unit of state: one query object, one status field, and a status write flips it. Oracle InForm does not model discrepancies that way, and every duplicate-marking and phantom-query bug traces back to that mismatch.

In InForm, the thing attached to a data point is a marking, and a query is one type of marking that lives inside a marking group. A marking group binds all the markings raised against a single item — automatic edit-check queries, manual queries, and sticky notes — into one lifecycle container. What Rave exposes as a flat QueryStatus enum, InForm exposes as a marking whose state advances through Candidate → Opened → Answered → Closed, with Reissued folding an answered marking back to an open one and Deleted voiding a marking that should never have been raised. The Candidate state has no clean analogue in Rave or Veeva: it is a proposed query that exists in InForm but has not yet been issued to the site, so treating it as OPEN inflates the tracker with discrepancies the site cannot see.

The second divergence is the write path. Rave and Veeva accept a status update against a query identity; InForm accepts an ODM submission through the InForm Adapter, where a query is expressed as a vendor-extension element (pf:Query) nested under the item’s ItemData, carrying the target State and the marking-group context. InForm processes that submission as a new marking event, not a mutation — so a re-run that resubmits an already-applied Opened marking creates a duplicate rather than being ignored. Idempotency therefore cannot be delegated to InForm; it has to be enforced by the sync worker before the write, using the canonical state machine defined in the vendor-neutral Syncing Discrepancy Status Across Multiple EDC Vendors guide and the transport contracts described in EDC API Architecture for Clinical Trials.

Step-by-Step Implementation #

Each step is a single-responsibility building block. Compose them into one worker that reads InForm, diffs against the tracker, and writes only the necessary deltas back — reusing the extraction mechanics from Extracting Data from Oracle InForm with Python.

1. Authenticate against the InForm Adapter web service #

The InForm Adapter exposes ODM-based endpoints over HTTPS with an authenticated principal. Bind a study-scoped service account, keep credentials in a secret store, and open one reusable session so every read and write in the sync run is attributable to a single named principal.

# 21 CFR Part 11 relevance: every InForm read and write is Attributable to one named
# service principal; credentials come from a secret store, never source control.
import os
import requests

def inform_session(base_url: str, study_oid: str) -> requests.Session:
    s = requests.Session()
    s.auth = (os.environ["INFORM_SVC_USER"], os.environ["INFORM_SVC_PASSWORD"])
    s.headers.update({
        "Content-Type": "application/xml; charset=utf-8",
        "X-Inform-Study": study_oid,                 # study-scoped Adapter routing
        "User-Agent": "cdp-query-sync/1.0",
    })
    s.base_url = base_url.rstrip("/")                 # e.g. https://inform.example.com/Adapter
    return s

2. Read the current InForm query and marking states via ODM export #

Pull the marking groups for the study through the Adapter’s ODM export, then parse the pf:Query extension elements into a flat map keyed by CDISC coordinate. Capture the marking-group id and the raw InForm State, because both are needed to write back without creating a duplicate.

# ALCOA+ requirement: Original — the InForm ODM export is parsed read-only; the raw
# marking state (Candidate/Opened/Answered/Closed) is preserved before any mapping.
from lxml import etree

PF = "http://www.phaseforward.com/InForm/Query/1.0"     # InForm query extension ns

def read_inform_markings(s: requests.Session, study_oid: str) -> dict:
    resp = s.get(f"{s.base_url}/odm/queries", params={"studyoid": study_oid})
    resp.raise_for_status()
    root = etree.fromstring(resp.content)
    markings = {}
    for q in root.iter(f"{{{PF}}}Query"):
        coord = (q.get("SubjectKey"), q.get("FormOID"),
                 q.get("ItemOID"), q.get("RepeatKey") or "0")
        markings[coord] = {
            "marking_group_id": q.get("MarkingGroupID"),   # required for idempotent write
            "query_id": q.get("QueryID"),
            "inform_state": q.get("State"),                # raw InForm state, unmodified
        }
    return markings

3. Diff the InForm state against the external discrepancy tracker #

Normalize both sides to the shared canonical vocabulary, then compute the delta per coordinate. Crucially, drop InForm Candidate markings — they are not yet issued — and only emit an action when the canonical states genuinely differ, so a re-run over unchanged data yields an empty action list.

# 21 CFR Part 11 relevance: mapping is version-controlled data, tagged to the DB-lock
# milestone; Candidate markings are excluded so un-issued queries never reach the tracker.
INFORM_TO_CANONICAL = {
    "Candidate": None,                # not issued to site -> never synced outward
    "Opened":    "OPEN",
    "Answered":  "ANSWERED",
    "Reissued":  "OPEN",              # reopened marking folds back to OPEN
    "Closed":    "CLOSED",
    "Deleted":   "VOID",
}

def diff_states(inform: dict, tracker: dict) -> list[dict]:
    actions = []
    for coord, m in inform.items():
        canon = INFORM_TO_CANONICAL.get(m["inform_state"])
        if canon is None:                              # skip Candidate / Deleted noise
            continue
        want = tracker.get(coord)                      # canonical state the tracker holds
        if want and want != canon:
            actions.append({"coord": coord, "from": canon, "to": want,
                            "marking_group_id": m["marking_group_id"]})
    return actions

4. Push new queries and closures into InForm as ODM marking submissions #

Build one ODM pf:Query submission per action. Reuse the existing marking_group_id when advancing an existing marking so InForm treats it as the same discrepancy; omit it only when opening a genuinely new query. A deterministic TransactionID derived from the coordinate and target state makes the submission idempotent — InForm rejects a replay with the same id instead of raising a duplicate marking.

# 21 CFR Part 11 relevance: a deterministic TransactionID makes the write idempotent;
# re-running the sync cannot create a second marking on the same item (no duplicates).
import hashlib

def submit_query(s: requests.Session, study_oid: str, action: dict) -> requests.Response:
    subj, form, item, rep = action["coord"]
    txn = hashlib.sha256(
        f"{subj}|{form}|{item}|{rep}|{action['to']}".encode()).hexdigest()[:32]
    mg = action.get("marking_group_id") or ""          # reuse group => same discrepancy
    body = f"""<ODM xmlns:pf="{PF}">
      <pf:Query StudyOID="{study_oid}" SubjectKey="{subj}" FormOID="{form}"
                ItemOID="{item}" RepeatKey="{rep}" MarkingGroupID="{mg}"
                TargetState="{action['to']}" TransactionID="{txn}"/>
    </ODM>"""
    resp = s.post(f"{s.base_url}/odm/submit", data=body.encode())
    resp.raise_for_status()                            # 409 => already applied, safe to skip
    return resp

5. Reconcile the canonical state across vendors and chain the audit trail #

Record each accepted submission as an append-only, hash-chained event so the reconciled status is tamper-evident and defensible on both sides. This is the same canonical ledger the multi-vendor guide builds, so an InForm Closed and a Rave C collapse to one trusted CLOSED for the routing layer.

# 21 CFR Part 11 relevance: an append-only hash-chained ledger IS the audit trail;
# altering any historical status invalidates every downstream hash (Enduring, Consistent).
import json
from datetime import datetime, timezone

def record_sync(action: dict, txn_id: str, prev_hash: str, store) -> dict:
    event = {"coord": "|".join(action["coord"]), "system": "oracle_inform",
             "from_state": action["from"], "to_state": action["to"],
             "transaction_id": txn_id, "prev_hash": prev_hash,
             "applied_at": datetime.now(timezone.utc).isoformat()}
    event["event_hash"] = hashlib.sha256(
        (prev_hash + json.dumps(event, sort_keys=True)).encode()).hexdigest()
    store.append(event)                                # never updated in place
    return event

Verification and Audit Trail #

Because the sync writes into a validated EDC, “the marking is correct” must be provable from the ledger, not asserted. Capture at least the fields below on the sync side; the InForm side retains its own marking audit within the boundaries described in Audit Trail Boundaries in EDC Systems.

To confirm the sync, assert three properties against fixtures. Re-running the worker over unchanged data must produce zero submissions and zero new ledger entries — proof that the TransactionID and the diff both short-circuit. A tracker-side closure of a query that InForm shows as Opened must emit exactly one TargetState="CLOSED" submission that reuses the existing marking_group_id, and a second run must return HTTP 409 (already applied) rather than opening a duplicate. Finally, an InForm Candidate marking must never appear in the tracker. Run any mapping change in shadow mode against an archived ODM export first, reconciling the derived canonical state against the validated baseline before it drives live Query Routing Workflows for CRAs.

Edge Cases and Vendor-Specific Gotchas #

Marking group versus query semantics. Multiple markings — an auto edit-check query, a manual query, and a sticky note — can share one marking group on the same item, so a coordinate-only key can collide two distinct discrepancies into one. Key on the MarkingGroupID plus the marking type when a group holds more than one query, and never advance a marking whose group id you did not read back from InForm; submitting without the group id opens a new query instead of moving the existing one.

State mapping across vendors. InForm’s Candidate and Reissued states have no direct Rave or Veeva equivalent, and mapping them naively corrupts the unified count: Candidate must map to nothing (it is un-issued), while Reissued must fold to canonical OPEN so a reopened InForm marking reconciles against a Rave reopen. Keep the INFORM_TO_CANONICAL table version-controlled and tagged to the database-lock milestone, and align the ItemOID coordinate against CDISC ODM vs CDASH Schema Mapping so the same CRF item resolves identically across systems.

Idempotent re-sync without duplicate markings. InForm processes each ODM submission as a new marking event, so a retried or replayed push will happily create a second marking that the study cannot easily delete. Derive the TransactionID deterministically from the coordinate and target state, treat an HTTP 409 as success rather than an error, and throttle retries with the backoff patterns in Handling API Rate Limits in Clinical Sync so a transient timeout never becomes a duplicate query.

Frequently Asked Questions #

Related #

• Syncing Discrepancy Status Across Multiple EDC Vendors — the vendor-neutral canonical state machine this InForm mapping plugs into.
• Query Routing Workflows for CRAs — the parent layer that assigns owners and SLAs to the reconciled status.
• Extracting Data from Oracle InForm with Python — the read-side Adapter authentication and ODM parsing reused here.
• EDC API Architecture for Clinical Trials — the transport contracts InForm’s ODM web services fit into.
• Clinical Query Generation & Discrepancy Management — the parent reference for this discipline.