CREATE TABLE old_trial_transfers
    (old_id INTEGER NOT NULL PRIMARY KEY REFERENCES all_docs,
     new_id INTEGER NOT NULL UNIQUE      REFERENCES all_docs)

-- Find inactive InScope doc CDR IDs with an associated nct_id
  INSERT INTO old_trial_transfers
         (old_id, new_id)
  SELECT InScopeQ.doc_id, CTGovQ.doc_id
    FROM query_term InScopeQ
    JOIN query_term NctIdQ
      ON InScopeQ.doc_id = NctIdQ.doc_id
     AND LEFT(InScopeQ.node_loc, 8) = LEFT(NctIdQ.node_loc, 8)
    JOIN query_term CTGovQ
      ON CTGovQ.value = InScopeQ.value
    JOIN all_docs d
      ON InScopeQ.doc_id = d.id
    JOIN all_docs cd
      ON CTGovQ.doc_id = cd.id
     AND cd.active_status = 'A'
   WHERE InScopeQ.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDString'
     AND NctIdQ.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
     AND NctIdQ.value = 'ClinicalTrials.gov ID'
     AND CTGovQ.path = '/CTGovProtocol/IDInfo/NCTID'
     AND d.active_status = 'I'
     -- Following will work if we index the required path
     -- Otherwise, no harm done
     AND CTGovQ.doc_id NOT IN (
        SELECT doneQ.doc_id
          FROM query_term doneQ
         WHERE doneQ.path = '/CTGovProtocol/PDQProtocolIDs/PrimaryID'
         )
     -- List of problem documents
     -- Since all of these InScope docs are now blocked, we can't use the
     --   active_status to determine which ones were blocked for cause as
     --   opposed to being blocked because of the transfer
     AND InScopeQ.doc_id NOT IN (484669)
   ORDER BY CTGovQ.doc_id
