CDR Tickets

Issue Number 3260
Summary Report of lead organizations on trials submitted by Oncore
Created 2010-10-29 09:28:18
Issue Type Improvement
Submitted By Kline, Bob (NIH/NCI) [C]
Assigned To Kline, Bob (NIH/NCI) [C]
Status Closed
Resolved 2010-11-02 15:37:25
Resolution Fixed
Path /home/bkline/backups/jira/ocecdr/issue.107588
Description

BZISSUE::4950
BZDATETIME::2010-10-29 09:28:18
BZCREATOR::Bob Kline
BZASSIGNEE::Bob Kline
BZQACONTACT::Lakshmi Grama

[On 10/28/2010 5:19 PM, Bitenas, Diana wrote]

Bob,

In an effort to identify recipients for a letter to the OnCore community from NCI, can you assist with this time-sensitive request by executing an SQL query outputting distinct lead organization names (i.e., no repeats) for all Inscope documents with a Submission Method of “Oncore”?

Thanks in advance,

Diana

[I replied]

Doesn't look like it will be as simple as an SQL query, as I don't see any indexed elements or attributes whose names contain the substring of 'Submission' but I should be able to get something for you by tomorrow using a brute-force parse of all of the documents.

[Diana followed up]

Bob,

Thanks for your prompt reply. To clarify, the XML element would appear as follows: <SubmissionMethod>Oncore</SubmissionMethod>. For the output, I’d like the data appearing in the LeadOrganizationID element.

Thanks again,

Diana

Comment entered 2010-10-29 09:30:23 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-10-29 09:30:23
BZCOMMENTOR::Bob Kline
BZCOMMENT::1

[from Lakshmi]

Don't we have protocol source = oncore

[my reply]

We have a ProtocolSource element, but 'Oncore' isn't one of the values:

SELECT DISTINCT value
FROM query_term
WHERE path = '/InScopeProtocol/ProtocolSources/ProtocolSource/SourceName'

Industry mail submission
Legacy
NCI Liaison Office-Brussels

Industry email submission
Investigator/contact mail submission
National Cancer Institute of Canada
NCI-CCR
NCI-DCEG
Investigator/contact email submission
NCI-CTEP
Other NIH Institute
INCa
Industry web submission
Investigator/contact web submission
NCI designated Cancer Center
NCI-DCP
Translational Research Informatics Center - Japan

We do have several hundred rows in the query_term table for 'UpdateMode' with 'Oncore' as the value. Is it always certain that the original submitter for the protocol is identical with the source of updates for changes to status and participation information?

There is a SubmissionMethod element as part of the ProtocolSource block, but it's not indexed. That's what I was referring to last night when I said I could find the information by a brute-force parse of all of the InScopeProtocol Documents. Another possibility might be to use the tables on the pdqupdate server which record all of the protocols which were submitted electronically by Oncore.

Bob

Comment entered 2010-10-29 10:39:26 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-10-29 10:39:26
BZCOMMENTOR::Bob Kline
BZCOMMENT::2

Comment entered 2010-10-29 10:39:26 by Kline, Bob (NIH/NCI) [C]

Attachment Report4950.txt has been added with description: Requested report

Comment entered 2010-10-29 10:40:01 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-10-29 10:40:01
BZCOMMENTOR::Bob Kline
BZCOMMENT::3

Adjusting status (which you can't do when you post an attachment).

Comment entered 2010-10-29 10:43:13 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-10-29 10:43:13
BZCOMMENTOR::Bob Kline
BZCOMMENT::4

Sent report to Diana.

Comment entered 2010-11-02 15:37:25 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-11-02 15:37:25
BZCOMMENTOR::Bob Kline
BZCOMMENT::5

Discussed the logic I used for the report with Lakshmi this morning. I'm closing the issue. If the logic turns out to be other than what was needed, we can re-open the issue.

Attachments
File Name Posted User
Report4950.txt 2010-10-29 10:39:26

Elapsed: 0:00:00.001085