I wrote a pair of queries to find documents that would be
excluded from the global change on the basis of ProcessingStatus.

The queries are complicated enough that I thought that I better
record the query as well as the results, so if someone looks at
the results and finds a problem, I'll have recorded exactly what
query produced those results and can, hopefully, quickly pinpoint
the problem.

If I got them right, these queries are the same as the queries in
the global change for this issue (Bug #4721), except that the
handling of the ProcessingStatus is reversed.  Where before I was
interest in documents meeting certain criteria and not containing
certain ProcessingStatus value, in these queries I want the docs
that meet the same criteria, but ONLY those that have the
ProcessingStatus values of interest.

50 documents were found among the "Never registered in CTGov"
group, only 1 in the "Blocked From CTGov" group.

I only looked for the three values 'Hold', 'Abstract in review'
and 'Needs administrative information'.  If I added 'Pending' and
'Merged', many more would have been found.

-- Query1: InScopeProtocols with no NCTID.
-- "Never registered in CTGOV".
-- Excludes docs not in NCTID because they haven't progressed that far
SELECT d.id, qproc.value
  FROM document d
  JOIN doc_type t
    ON d.doc_type = t.id
   AND t.name = 'InScopeProtocol'
  JOIN query_term qproc
    ON d.id = qproc.doc_id
 WHERE d.id NOT IN (
        SELECT doc_id
          FROM query_term
         WHERE path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
           AND value = 'ClinicalTrials.gov ID'
	)
   AND d.id NOT IN (
        SELECT doc_id
          FROM query_term
         WHERE path IN (
           '/InScopeProtocol/CTGovOwnershipTransferContactLog/CTGovOwnershipTransferContactResponse',
           '/InScopeProtocol/CTGovOwnershipTransferInfo/CTGovOwnerOrganization'
         )
    )
   AND qproc.path IN (
          '/InScopeProtocol/ProtocolProcessingDetails/ProcessingStatuses/ProcessingStatusInfo/ProcessingStatus',
          '/InScopeProtocol/ProtocolProcessingDetails/ProcessingStatus'
           )
      AND qproc.value in (
            'Hold',
            'Abstract in review',
            'Needs administrative information'
           )
   AND d.id NOT IN (
        SELECT doc_id
          FROM query_term
         WHERE path =
          '/InScopeProtocol/ProtocolProcessingDetails/MissingRequiredInformation/MissingInformation'
    )
 ORDER BY d.id

RESULTS:
id          value
----------- ---------------
65322       Hold
66842       Hold
66911       Hold
67116       Hold
67317       Hold
67397       Hold
68651       Hold
69333       Hold
69334       Hold
69335       Hold
69400       Hold
304673      Hold
326945      Hold
361738      Hold
387736      Hold
393492      Hold
437079      Hold
446093      Hold
446095      Hold
446098      Hold
446180      Hold
446181      Hold
446209      Hold
446219      Hold
446221      Hold
446223      Hold
446291      Hold
446292      Hold
446293      Hold
446295      Hold
446296      Hold
446299      Hold
447050      Hold
447138      Hold
448901      Hold
448903      Hold
449651      Hold
449675      Hold
449933      Hold
449936      Hold
449937      Hold
462941      Hold
463938      Hold
483224      Hold
641925      Hold
648401      Hold
648401      Hold
665396      Needs administrative information
669300      Needs administrative information
670445      Needs administrative information

(50 row(s) affected)



-- Query2: InScopeProtocols with an NCTID but are blocked from CTGOV.
-- "Blocked from CTGOV".
SELECT qIdType.doc_id, qproc.value
  FROM query_term qIdType
  JOIN query_term qBlocked
    ON qIdType.doc_id = qBlocked.doc_id
  JOIN query_term qproc
    ON qIdType.doc_id = qproc.doc_id
 WHERE qIdType.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
   AND qIdType.value = 'ClinicalTrials.gov ID'
   AND qBlocked.path = '/InScopeProtocol/BlockedFromCTGov'
   AND qIdType.doc_id NOT IN (
        SELECT doc_id
          FROM query_term
         WHERE path IN (
           '/InScopeProtocol/CTGovOwnershipTransferContactLog/CTGovOwnershipTransferContactResponse',
           '/InScopeProtocol/CTGovOwnershipTransferInfo/CTGovOwnerOrganization'
         )
    )
   AND qproc.path IN (
          '/InScopeProtocol/ProtocolProcessingDetails/ProcessingStatuses/ProcessingStatusInfo/ProcessingStatus',
          '/InScopeProtocol/ProtocolProcessingDetails/ProcessingStatus'
           )
           AND qproc.value in (
            'Hold',
            'Abstract in review',
            'Needs administrative information'
           )
 ORDER BY qIdType.doc_id

RESULTS:
id          value
----------- ---------------
299686      Abstract in review

(1 row(s) affected)