Defining External System Information in TMS

When a pharmaceutical application, or external system, is fully or partially integrated with TMS, you can bring information into TMS from the external system to provide context for each verbatim term occurrence (source term, question response). For example, if TMS is integrated with Oracle Clinical, when Oracle Clinical sends a question response to TMS for classification, it also sends information about the response: its associated Patient, Study, Project, DCM, Visit, Document Number, Investigator, and Discrepancy ID.

In the case of Oracle Clinical, these database columns are predefined. If you are using TMS with a different external system, you must specify any database column information you want to bring into TMS from the external system, associated with each verbatim term occurrence. You can specify up to eight columns from the external system's database for this purpose. TMS then stores all such data in its database, linked to the original verbatim term occurrence (source term).

In addition, you can specify up to twelve details per column for propagation into TMS associated with each verbatim term occurrence. Column details are not predefined for Oracle Clinical, but as with other external systems, you can specify them.

Detail information is retrieved via either a view or a function defined for this purpose within the external system (see Define Views in the External System and Define Functions in the External System).

You can set up external system information in TMS for any number of external systems, and specify full, partial, or no integration for any of them.

For external systems other than Oracle Clinical, use the Define External Systems window to specify what information you want to have available in TMS as well as its label and display size. See Setting Up External System Columns and Details.

For all external systems, do the following for each column for which you wish to import additional details:

• Create a view or function. See Defining Views and Functions in the External System.

• Grant access privileges within the external system to the view for all users who need to see the information within TMS.

This section includes:

• Where External System Information Appears
  
• Defining the External System in TMS
  
• Setting Up External System Columns and Details
  
• Defining Views and Functions in the External System
  
• Setting Up External System Drill-down Queries
  

Where External System Information Appears

If the external system is fully integrated with TMS, TMS displays external system column information in TMS Windows.

TMS Windows

TMS windows can display external system information from partially and fully integrated systems. This information is for query purposes only.

External system columns from fully integrated systems appear in the following windows: Classify VT Omissions, Approve VTAs, Reclassify Verbatim Terms, High-Level Reclassification, and Browse VT Classification Data. For partially integrated systems, external system information is available in the Classify VT Omissions window only.

If detail information is available for a particular column, the text in the column's field appears in blue. The user can select Drill Down from the field to see the detail information in the External Drill-Down pop-up window.

Defining the External System in TMS

To integrate TMS with any system other than Oracle Clinical, you must complete the steps in this section.

The general external system information is pre-defined for Oracle Clinical. If you are using Oracle Clinical, you can skip this section and proceed to Setting Up External System Columns and Details.

To define an external system:

1. Select Definition, then Define External Systems. The Define External Systems window appears.
2. In the External System field, enter the integration key that will identify the external system to TMS. For example, Oracle Clinical's is pre-defined as OCL.
3. Enter the name of the external system in the Application Name field. This name will appear in the Classify VT Omissions, High-Level Reclassification, and Browse VT Classification Data windows in the External Systems list that allows you to see and query on the external system information.
4. From the Integration Level list, choose Full, Partial or None according to the level of integration between TMS and the external system. See Full Integration and Partial Integration.
5. Enter a Description of this external system.
6. Skip the Object HTML Data Function field. This field is not currently used by TMS.
7. Display X Area Name Function (optional). Enter a database function name for displaying a name corresponding to an X Area value in reports where the X Area is displayed. If you leave this field empty, TMS reports display the numerical X Area ID.

   The function should accept only the X Area as parameter and return a string representing the name of the X Area. Note that if the function is not owned by TMS, then execute on the function will need to be granted to TMS.

   If Oracle Clinical is the external system, TMS automatically displays the study name. If you prefer to display something else, you can write a function for that purpose, entering the function name in this field in place of the default function name, rxc_tms_access.studyName.

8. The Discrepancy Message Omission Statuses list of values displays the list of allowed omission statuses for Actions of Action Type Single Term. For more information, see Defining and Using Actions.
9. The Multiple Term Action Omission Statuses list of values field defines the list of allowed omission statuses for Actions of Action Type Answerable or Unanswerable. See Action Types.
10. Save.

Setting Up External System Columns and Details

After you specify the general external system information, you can define up to eight columns that map to data columns in that external system. Subsequently, for each external system column, you can define up to twelve additional details that provide more information about that external system data.

For example, if you define Patient ID as one of the columns, you could define details for the related first name; last name; sex; screening, enrollment, and termination dates; and dates of birth and death—if the external system stores that information.

These processes are divided into two sections:

• Define External System Columns
  
• Define Column Details
  

Define External System Columns

Column names will appear as field labels in Classify VT Omissions, and if the system is fully integrated with TMS, in High-Level Reclassification, Approve VTAs, Reclassify Verbatim Terms, and Browse VT Classification Data. Oracle Clinical columns are pre-defined as Patient, Study, Project, DCM, Investigator, Visit, Document Number and Discrepancy ID.

You should define columns in the order you want them displayed. TMS assigns a read-only Map ID Number to each column row that you define in the Attributes block, numbering them consecutively and in ascending order. The Map ID Number is important: you use this number for other processes in TMS that use external system data, such as Defining Views and Functions in the External System.

Each column can store values of up to 500 characters (VARCHAR2).

The external system columns are defined for Oracle Clinical by default. To define column information for any other external system:

1. In the External System block, highlight the name of the external system for which you are defining information.
2. Click the Attributes tab and in the upper block of that tab, click in an empty row or insert a record.
3. Column Name. Enter text to appear as the label for the field that will contain information from this database column.
4. Enter a Description of the column.
5. Drill Down Type. If you plan to bring details about this column into TMS from the external system, specify a drill-down type here. Choose Function if you plan to use a function to provide detailed information from the external system to TMS, or View if you plan to use a view. Leave this field blank if you do not want detailed information from this column to be available in TMS.
6. If you entered a value in the Drill Down Type field, in the View/Func Name field, enter the name of the view or function that you will use to retrieve drill-down data for this column from the external system. If you specify a function, you must enter it in package_name.function_name format. Leave this field blank if you do not want detailed information for this column to be available to TMS.

   To create the view or function you want to use, see Define Views in the External System or Define Functions in the External System.

7. View Where Clause (Views only). If necessary, enter a Where clause to filter the information. For example:

   ext_col_det1 = :xv1 and ext_col_det2 = :xv2

   In addition to the eight xv variables, you can also reference the following bind variables in the Where clause:

   • source term ID (:sourceTermId)
   • name of the instance (:defInstanceName)
   • integration key of the external system (:defIntegrationKey)
   • dictionary content ID (:dictContentId)
   • update flag (:updateFlag)
   • domain ID (:defDomainId)
   • external area (:xarea)
   • source term alt key (:stAltKey)
8. Save.
9. Repeat. You can define up to eight columns per external system.

Define Column Details

If you define column detail information in this window, it is available from the TMS windows Classify VT Omissions, Approve VTAs, Reclassify Verbatim Terms, Browse VT Classification Data, and, if the external system is fully integrated with TMS, from High-Level Reclassification.

When you define detail information for a column, TMS displays the text in the field corresponding to the column in blue. The user can then use the drill-down function to see detail information.

Whether you chose a view or a function for retrieving external system data for this column, you must be sure to include the detail information you are defining here in the view or function. To create the view or function you want to use, see Define Views in the External System or Define Functions in the External System.

To define column detail display:

1. In the upper block of the Attributes tab, highlight the column for which you have created a view.
2. In the Details block, click in an empty row or insert a record.
3. Fill in the fields as follows:

   • Map ID – Display-only. TMS numbers column details consecutively by default. Maps to ext_col_det1…12 in the view.

   • Label – Enter the label text to appear in the drill-down window. This does not have to be the same as the database element's name.

   • Sort Order – In the Sort Order field, indicate where you want TMS to display this detail in relation to other details for this column. Number 1 appears highest on screen, Number 2 next, etc.

   • Value Length – Enter the display length for the detail.

4. Save.
5. Repeat for each detail, up to twelve details per column.

Defining Views and Functions in the External System

This section describes how to define and use views and functions to access data from the external system. You can also refer to the sample drill-down views and functions available on My Oracle Support.

This section includes:

• Define Views in the External System
  
• Define Functions in the External System
  

Define Views in the External System

To see up to twelve (12) detail values associated with a column, you must define a view in the external system and specify it in the Define External Systems window in TMS, in the Details block. The existence of a view name associated with an external column triggers the display of the column name in blue in TMS, indicating that drill-down information is available.

When you create the view, you must use the detail names shown below.

Note that you can define a Where clause for each column in the TMS user interface (see Define External System Columns). The Where clause is appended to the view definition at execution time.

Grant select privileges and create a synonym as shown in the last three lines of the statement. See Security for more information.

Connect to the database as a user with access to the external system's tables from which the view is created and use the script in Example 5-1 as a model.

Example 5-1 Sample External System View Definition Logic

CREATE VIEW view_name (

Code snipet	Description
ext_col_det1 , ext_col_det2 . . . , ext_col_det12	These detail names are fixed; TMS uses them in its internal code. You must define at least ext_col_det1…12. You can define more than 12 if you want, and use the select statement to limit the columns used. The detail number will be imported as the sort order in TMS; ext_col_det1 will be listed first, then ext_col_det2, etc.
) AS SELECT column_detail1 , column_detail2 . . . , column_detailn	- Select the actual column detail names from the external system that you want to use in TMS. You can specify up to twelve (12). If you need fewer details, enter null in each extra line of code.
FROM table_name1 . . . table_namen	Specify the external system's table or tables from which you want to create the view.
WHERE…	Describe the condition(s) you want. If the external system is Oracle Clinical, include a call to a predefined function to limit the data retrieved to studies to which the user has the proper security privileges (see Example 5-2). The call is: rxc_tms_access.studyAccess ([table_alias].[clinical_study_id])=1

GRANT SELECT ON view_name TO rxclin_read;

GRANT SELECT ON view_name TO tms WITH GRANT OPTION;

CREATE PUBLIC SYNONYM view_name FOR user.view_name;

Example 5-2 Sample External System View Definition Code

CREATE OR REPLACE VIEW dmo_dcms (
       ext_col_det1
,      ext_col_det2
,      ext_col_det3
,      ext_col_det4
,      ext_col_det5
,      ext_col_det6
,      ext_col_det7
,      ext_col_det8
,      ext_col_det9
,      ext_col_det10
,      ext_col_det11
,      ext_col_det12
,      clinical_study_id
,      response_id
,      order_ts
)
as select distinct
       resp.response_id
,      decode( resp.end_ts
       , to_date( 3000000, 'J') 
         , decode( rdcm.accessible_ts
           , to_date( 3000000, 'J'), 'C'
           , 'S'
           )
       , 'O'
       )
,      to_char( resp.response_entry_ts, 'DD-MON-YYYY HH24:MI:SS')
,      to_char( resp.end_ts, 'DD-MON-YYYY HH24:MI:SS')
,      resp.discrepancy_indicator
,      resp.validation_status
,      dcmq.question_name
,      decode( dcmq.enterable_flag
       , 'Y', 'E'
       , decode( dcmq.derived_flag
         , 'Y', 'D'
         , '-'
         )
       ) E
,      resp.value_text
,      rdcm.patient
,      rdcm.document_number
,      rdcm.visit_number
,      known.CLINICAL_STUDY_ID
,      known.response_id
,      resp.response_entry_ts
from   responses           resp
,      dcm_questions       dcmq
,      received_dcms       rdcm
,      responses           known
where  resp.received_dcm_id       = known.received_dcm_id
  and  resp.dcm_question_group_id = known.dcm_question_group_id
  and  resp.repeat_sn             = known.repeat_sn
  and  rdcm.received_dcm_id       = known.received_dcm_id
  and  rdcm.dcm_subset_sn         = dcmq.dcm_que_dcm_subset_sn
  and  rdcm.dcm_layout_sn         = dcmq.dcm_que_dcm_layout_sn
  and  dcmq.dcm_question_id       = resp.dcm_question_id
  and  rxc_tms_access.studyAccess(known.clinical_study_id) = 1
order by resp.response_id, resp.response_entry_ts;
 
grant select on dmo_dcms to rxclin_read;
grant select on dmo_dcms to tms with grant option;
exec opa_ddl.createDropPublicSynonym ('dmo_dcms');

Define Functions in the External System

Functions can derive larger data points than TMS can handle in drill-down views. Using a function of type varchar2 enables you to return up to 32k of information back to TMS.

Each function can derive one column detail.

Following is the package body for a sample function. Note that the function must include all the parameters shown.

Example 5-3 Sample External System Drill-down Function

CREATE OR REPLACE PACKAGE BODY dmo_project IS
 
  FUNCTION showData(
             pSourceTermId     IN NUMBER
           , pOccurrenceId     IN NUMBER
           , pDefInstanceName  IN VARCHAR2
           , pIntegrationKey   IN VARCHAR2
           , pDictContentId    IN NUMBER
           , pUpdateFlag       IN VARCHAR2
           , pDefDomainId      IN NUMBER
           , pXArea            IN NUMBER
           , pSTAltKey         IN VARCHAR2
           , pXV1              IN VARCHAR2
           , pXV2              IN VARCHAR2
           , pXV3              IN VARCHAR2
           , pXV4              IN VARCHAR2
           , pXV5              IN VARCHAR2
           , pXV6              IN VARCHAR2
           , pXV7              IN VARCHAR2
           , pXV8              IN VARCHAR2
           ) RETURN VARCHAR2 IS
    rVal     VARCHAR2(2000) := NULL;
  BEGIN
    IF rxc_tms_access.studyAccess(pXArea) = 0 THEN
      rVal := 'You do not have access to this data';
    ELSE
      SELECT opo.program_code 
             ||': '||opo.description
             ||'  -  '||opr.project_code
             ||': '||opr.description
      INTO   rVal
      FROM   ocl_programs opo
      ,      ocl_projects opr
      WHERE  opr.project_code = pXV1
        AND  opo.program_code = opr.program_code
      ;
    END IF;
    RETURN rVal;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'The data could not be found in Oracle Clinical';
  END showData;
 
END dmo_project;
/

Setting Up External System Drill-down Queries

The Queries tab enables you to define external system drill-down queries, which are groupings of external system columns.

This section includes:

• Defining the Name and External System for a Query
  
• Defining Query Columns
  

Defining the Name and External System for a Query

To define a query:

1. In the External System block of the Define External Systems window, click the external system for which you want to define a query.
2. Click the Queries tab and in the upper block of that tab, either click in an empty row or insert a record.
3. Describe the query:

   • Short Name – Unique short name for the query.

   • Name – The display name for this query.

   • Description – Optional description about this query.

4. Save.

You can change the display name and description at any time. Proceed to Defining Query Columns to add the columns that compose this query.

Defining Query Columns

To define the columns that the external system query will display:

1. In the Query Columns block of the Queries tab, click in an empty row or add a record.
2. Enter and describe each external system column you want to include in the query:

   • Map ID. The unique ID of the external system column. The Map ID numbers and external system columns are available on the Attributes tab.

   • Col Order. The order, from left to right, in which the columns are displayed for this query. Columns with lower numbers appear in the query on the left.

3. Save.