5 Integrating TMS with Other Systems

Setting Up the Sponsor Instance

At the sponsor site you must do the following:

• Create operating system file directories for temporary data and error file storage; see "Creating Directories on the Database Server".

• Run the Initialize Disconnected System Integration Systems job with DSI Instance Type set to Sponsor; see "Running the DSI Initialization Job".

• Register each remote database by running the job; from the Definitions menu, select Jobs, then choose Register Remote DSI Databases; see "Register Remote Databases".

• Enter information about the external source data system(s) (such as Oracle Clinical) used by each source site. You need only define any particular external system once; for example, if you have five source sites using Oracle Clinical, you need only define Oracle Clinical once. If you have already defined Oracle Clinical as an external system because your own organization is using it with TMS, you do not need to define it again. See "Defining External System Information in TMS".

• Define each DSI for each remote database/external system combination; see "Defining Disconnected System Integrations".

• Map XML tag metadata so that the system can interpret the data received from the source site and send the source site data it can interpret; see "Defining XML Tag Mappings".

• Set reference codelist values; see "Setting DSI Preferences with Reference Codelist Settings".

• Grant privileges to users who will run the DSI import and export processes; see "Granting User Privileges".

• Ensure that the source site has the same version of the dictionary or dictionaries that you are using; see "Synchronizing Dictionary Contents Between Sites".

• Ensure that any source site not using TMS knows how to validate XML files against the TMS XML schema before sending them to you; see "Validation of Non-TMS XML Files".

Setting Up the Source Instance

At the source site you must do the following:

• Create operating system file directories for temporary data and error file storage; see "Creating Directories on the Database Server".

• Enter information about the external source data system(s) (such as Oracle Clinical) whose data you are sending to the sponsor via DSI. If you have already set up the external system for integration with TMS, you do not need to define it again. See "Defining External System Information in TMS".

• Run the Initialize Disconnected System Integration Systems job with DSI Instance Type set to Source; see "Running the DSI Initialization Job".

• Define Disconnected System Integration with the sponsor; see "Defining Disconnected System Integrations".

  Note:

  You must define the external source data system, initialize DSIs, and register the sponsor's database before you can do this step.

• Define X Areas to specify discrete data collection and processing units (such as studies); see "Defining X Areas".

• Set reference codelist values; see "Setting DSI Preferences with Reference Codelist Settings".

• Grant privileges to users who will run the DSI import and export processes; see "Granting User Privileges".

• Ensure that the dictionaries you are using are synchronized with the sponsor's dictionaries; see "Synchronizing Dictionary Contents Between Sites".

Creating Directories on the Database Server

You must create three operating system directories on the database server to temporarily hold XML files:

• Input directory. The import process operates on files sent from the remote system to the Input directory.

• Output directory. The export process creates one or more XML files in the Output directory.

• Error directory. Both the import and export processes write an error file to the Error directory if they process any data with errors or warnings.

You must grant Delete privileges on the Input directory to the Oracle User Database Process.

Defining the External Source Data System

You must define each external source data system (such as Oracle Clinical) as an external system in TMS. You need to define each external system only once. If you have already defined an external system because you are using it with TMS, you do not need to define it again.

At a sponsor site, if you have multiple source sites using a particular external source data system, enter information about that system only once.

See "Defining External System Information in TMS".

Running the DSI Initialization Job

Before you can export or import data you must initialize your site as either a sponsor or source site. To run the job:

1. Select Definition, then Jobs, then Initialize Disconnected System Integrations. The Initialize Disconnected System Integrations job pop-up window appears.

2. For the job-specific parameter DSI Instance Type, select either Source or Sponsor.

3. If necessary, enter the name of your report server; see "Running a Job".

4. Submit the job. See "Running a Job".

   Note:

   If you used the DSI feature before installing TMS Release 4.6 and have not yet done so, you must declare your TMS instance to be either a sponsor or a source site by running the DSI initialization job. If you need to act as a sponsor for some studies and a source for other studies, you must set up a different database for each role.

   The initialization job will fail if:

   • you try to initialize as a sponsor but any of your DSI definitions includes your own database as the Instance name.

   • you try to initialize as a source but any of your DSI definitions includes a database other than your own as the Instance name.

   If you have this situation, first delete the X Areas associated with the incorrect instance and then delete the DSI definition that includes the incorrect instance.

Register Remote Databases

At the sponsor site only, register the database used by each source site for DSI by running the job; from the Definition menu, select Jobs, then choose Register Remote DSI Databases. You must register remote databases one at a time.

The job takes the following parameters:

• Instance. Enter the source site database name.

• External System. From the list, choose the external source data system (such as Oracle Clinical) whose data will be sent to the remote system via DSI.

  Note:

  You must define the external source data system before you can register the remote database. See "Defining the External Source Data System".

• Report Server Name. Enter the name of the report server that will run the job.

Defining Disconnected System Integrations

From the Definition menu, select Define Disconnected System Integrations, then the Definitions tab.

Set up Disconnected System Integration by entering information on both sponsor and source sites:

1. Enter information about each external source data system (such as Oracle Clinical) that the site is using:

   • System. From the list of values, choose the correct external system. The list of values includes all systems defined as external systems in TMS.

   • Instance. From the list of values, choose the correct database location of the external system. The list of values includes all registered remote databases.

2. Remote Partner. Enter the name of the organization that owns the remote database, or other information to help you identify the Disconnected System Integration. The system does not use this field.

3. Enter the names, with the full path, of the Input, Output, and Error directories you created on the database server (see "Creating Directories on the Database Server"). For example:

   /root/app/oracle/admin/DSI_database_name/xmlworkdir/partnerx_database_input

   where xmlworkdir is your DSI directory, and partnerx_database_input is your input directory for XML files from partner X's remote database.

   Note:

   When you enter this information, the system verifies that the locations are correct. However, if you later change the directories on the server, any export or import job that uses the directory will fail.

4. Save.

Defining X Areas

From the Definition menu, select Define Disconnected Integration Systems, then the X Areas tab.

Define X Areas on the source site only. The first time the XML Import process on the sponsor site encounters data from a particular X Area, it automatically creates a corresponding X Area on the sponsor site.

An X Area is a data collection unit. If you are using Oracle Clinical as your external source data system, X Areas correspond to studies, and the X Area ID is the same as the Study ID. DSI limits the data in any one data exchange to data from a single X Area; by defining an X Area for each study, for example, you ensure that data from only one study is loaded and processed at a time.

During the export/import process, each X Area being processed is locked on both systems, so that data from a particular X Area cannot be exported and imported at the same time. TMS uses the same locks that Oracle Clinical Batch Validation uses; therefore DSI export/import cannot occur at the same time as Oracle Clinical Batch Validation.

You can then change the X Area status at the sponsor site as well as the source site (see Step 3 below).

To define an X Area:

1. In the Define Disconnected System Integrations window's Definitions tab, query for the external system and instance combination for which you need to define an X Area.

2. Click the X Areas tab. The X Areas window opens.

3. Insert a row by selecting Record, then Insert.

4. In the X Area column, enter the ID used by the external source data system for the data collection unit. If Oracle Clinical is the external source data system, enter the Study ID.

5. In the X Area Status column, the status of a new X Area is automatically set to Active. You can change the status later, when appropriate. The possible statuses are:

   • Active. X Areas are automatically set to Active when they are first created. If data from an inactive X Area is sent to either system, the X Area status is automatically changed back to Active.

   • Inactive. Set the X Area's status to Inactive when you no longer expect any data changes. If data is received for the X Area, the import job processes the data, sets the X Area status to Active, and gives a warning.

   • Complete. When you no longer need to use DSI for a particular X Area, you can set the X Area's status to Complete. If data is sent to either system for an X Area with a status of Complete, the import job does not process the data and returns an error.

6. Click in the X Area Name field to display the list of values. The system displays the X Areas defined in the sponsor system and database you specified in the Definitions tab.

7. Select an X Area Name.

8. Enter a description of the X Area in the Description column.

9. Save.

Defining XML Tag Mappings

(Sponsor site only.) From the Definition menu, select Define Disconnected Integration Systems, then the Tag Mappings tab.

XML files exported from both sponsor and source sites use the same tags, but different sites may use different tag values for the same entities. For example, for a particular study a sponsor might use the dictionary MedDRA in a domain called ALLCODING, while a source site might used MedDRA in a domain named SPONSOR_NAME for the same study.

Use this window on the sponsor site only to map the values used by each source site to the names used in your system. The sponsor system uses these mappings to interpret data sent by the source site and to send data to the source site with the names used by the source site.

The system translates the instance and X Area names provided in the XML file to IDs.

For information on the required structure, tags, and generated names of DSI XML files, see Appendix B, "Disconnected System Integration XML Files."

For each mapping:

1. Click in the first empty row. The fields become enterable.

2. XML Tag. From the list, select the metadata to map. The choices are:

   • Dictionary Short Name

   • Domain Name

3. Remote System Value. Enter the value used at the source site for a dictionary short name or domain name (whichever you selected in the XML Tag field).

   Note:

   If you are mapping the dictionary short name, and either site is using a virtual dictionary, enter the short name of the associated base dictionary, not the virtual dictionary.

   If you are mapping the domain name, use the virtual domain name if there is one.

4. Central TMS Value. From the list, select the value used at the sponsor site for the same dictionary or domain whose source site name is listed in the XML Value column. See Table 5-1, "Tag Mapping" for details.

5. Save.

Setting DSI Preferences with Reference Codelist Settings

The local reference codelist TMS_DSI contains parameters whose settings determine important aspects of DSI behavior. The parameters are: OUTSYNCCSP, CREVTA, GLOBALVTA, and XAPPVTA. The parameters and their settings are described below.

To change the settings for TMS_DSI parameters:

1. From the Definition menu, select Local Reference Codelists.

2. In the Name field, query for TMS_DSI.

3. For each parameter, enter a single-letter setting in the Long Value field and select the Active box. (Selecting Default has no effect.)

4. Save.

OUTSYNCCSP The DSI import process checks dictionary terms required for new VTAs created at the exporting site against dictionary terms on the verbatim term or classification level at the importing site. If a dictionary term used in a new VTA is not included at the importing site, and the dictionary versions were properly synchronized when DSI was set up, any such difference shows that a dictionary term has been added at the exporting site. The value of the OUTSYNCCSP setting determines the way the import process handles out-of-sync dictionary data.

• Fatal Error (E). If the import process finds a new VTA whose dictionary term is not present at the importing site:

  • The process ends with a Fatal Error status.

  • Information about the missing dictionary term is included in the error file.

  • Any records that were successfully processed (before the record with an error) remain in the database, but also appear in the error file.

• Warning (W). If the import process finds a new VTA whose dictionary term is not present in the importing system:

  • The import job imports source data records as omissions.

  • In the error file, it associates a warning with the VTA record whose dictionary term is missing.

  • On the sponsor site only, the import job creates a Discrepancy Message associated with an omission for the source term whose VTA's dictionary term is missing in the importing system. This Action is visible in the importing system's Classify VT Omissions window. It is not sent to the external source data system, as other Actions are. The Action remains associated with the omission until it is resolved.

• Neither (N). If the import process finds a new VTA whose dictionary term is not present in the importing system:

  • The import job imports source data records as omissions.

  • It creates a Discrepancy Message associated with an omission for the source term whose VTA's dictionary term is missing in the importing system. This Action is visible in the importing system's Classify VT Omissions window. It is not sent to the external source data system, as other Actions are. The Action remains associated with the Omission until it is resolved.

  Note:

  If you choose the setting N, the system does not include these dictionary synchronization errors in the error file. They are visible only in the importing system's Classify Omissions window, where you can query for them in the Action Text field with the string %out of sync%.

The default value (and recommended setting) is E.

CREVTA (Sponsor site only.) Determines whether the sponsor site imports new VTAs from the source site approved, unapproved, or both. DSI does not use the value entered for this parameter at the source site.

• Approved (A). All VTAs created at the source site are imported as Approved VTAs, regardless of whether they were approved at the source site or not. This setting is not recommended.

• Unapproved (U). All VTAs created at the source site are imported as Unapproved VTAs, regardless of whether they were approved at the source site or not.

• XML (X). VTAs created at the source site are imported with the same approval status—Approved or Unapproved—given by the source site, as reflected in the XML file.

The default value is X.

GLOBALVTA Determines whether Global VTAs are accepted by the importing system.

• Allow Global VTA (A). Global and Domain VTAs are imported without warnings, if they have no errors.

• Fail Global VTA (F). Global VTAs are not accepted for import. The system creates an error for the record.

• Domain Only (D). Global VTAs are not accepted for import. The system creates a warning for the record and a Domain VTA instead of a Global one.

The default value is A.

XAPPVTA Enforces whether or not export files must contain only Approved VTAs or can contain Unapproved VTAs as well.

• Yes (Y). Export files must contain only Approved VTAs. Unapproved VTAs are not included in the file. Only when a VTA has been approved is it exported.

• No (N). Export files can contain Approved and Unapproved VTAs.

The default value is N.

Synchronizing Dictionary Contents Between Sites

The source site must use the same version of each dictionary that the sponsor uses for the same study, and dictionary data (including VTA subtypes Accepted and Misspelled) at the two sites must be identical at the verbatim term and classification levels.

When you set up DSI, you must ensure that the sponsor and source site use the same dictionary data; for example, the sponsor can provide dictionary data to load onto the source site. If dictionary synchronization errors do occur, you must correct them.

The DSI import process checks that the coding levels—the verbatim term and classification levels—on the importing site contain all the terms used on the other site. The import process does not compare all terms on the dictionary coding levels of the two dictionaries; it checks only those dictionary terms used in VTAs being processed. You can use the OUTSYNCCSP reference codelist setting to specify what you want the import process to do if the importing site is missing dictionary terms used by the other site. See OUTSYNCCSP.

Granting User Privileges

You must grant the role TMS_DSI_PRIV to all users who should be able to see the Maintain DSI Files window and run the incremental export and import jobs, all of which are located in the VTA Maintenance menu path, under Disconnected System Integration.

You must grant the role TMS_DEFINE_PRIV to all users who should be able to see the Define Disconnected System Integrations window, run the Force Rederivation job from that window, or to run the Register Remote Databases job. This is the same role that allows access to all the other tasks in the Definition menu path.

For information on granting user privileges, see "Assigning Roles to a User".

Processing Source Data

At the source site, you process terms from the source data system as usual in TMS, loading data into TMS and running Autoclassification. (If you are using Oracle Clinical, this happens automatically during Batch Validation.) The sponsor may or may not want you to manually classify omissions and assign Actions.

Exporting Data

At the source site, you use a batch job to extract source terms and any source term deletions and classifications (VTAs) from your dictionary coding system to an XML file in a predefined format (see Appendix B, "Disconnected System Integration XML Files" for more information).

On a source site, the export job extracts the following types of data:

• All verbatim term omissions (VTOs) with a timestamp greater than the last export's timestamp

• All source terms with a timestamp greater than the last export's timestamp

• For each source term, contextual information (such as patient ID and document number) and any classifications (VTAs) made at the source site

• New and changed Informative Notes of type Workflow associated with verbatim terms

• Source data deletions, if any

• All VTAs created locally for source data from the selected X Area since the last export of the same X Area

  Note:

  If the local reference codelist TMS_DSI parameter XAPPVTA is set to Y, the system exports only Approved VTAs. Nonapproved VTAs are not exported. If set to N, both Approved and Nonapproved VTAs are exported.

Sending the XML File to the Sponsor Site

DSI does not handle transporting the XML file to the sponsor site. You must do it manually; for example, zipping the file and sending it via either FTP or email.

To avoid sending the same files to the sponsor for unnecessary reprocessing, manually delete files from the output directory after sending them to the other site.

Importing Data

After the sponsor processes your data—using Autoclassification and any necessary manual classifications, reclassifications, declassifications, or Action assignments—the sponsor sends an XML file with the updated information to your DSI input directory (see "Import DSI Data").

You must run the import job; from the VTA Maintenance menu, select Disconnected System Integration, then choose Import DSI Data.

You can see the status of exports and imports; from the VTA Maintenance menu, select Disconnected System Integration, then Maintain DSI Files. For instructions, see "Viewing XML File and Record Statuses".

This section describes how the import process handles the following situations: Classification Conflicts, Global VTA Conflicts, Source Term Actions, and Missing Dictionary Terms.

Updating External System Data

You must run the necessary job in the external source data system to synchronize its data with the sponsor site's TMS repository.

If the source data system is Oracle Clinical, the Batch Validation job derives TMS values into Oracle Clinical.

Importing Data

A source site sends data in one or more XML files to your input directory for processing. Each XML file contains data for a single X Area. Typically, an X Area corresponds to a study; see "Defining X Areas".

To import the data into your system, run the import job; from the VTA Maintenance menu, select Disconnected System Integration, then Import DSI Data (see "Import DSI Data"). The import job runs on all XML files in the input directory.

As part of the import process, Autoclassification runs on all omissions in the X Area, including those sent by the source site.

You can see the status of import files and data; from the VTA Maintenance menu, select Disconnected System Integration, then Maintain DSI Files. For instructions, see "Viewing XML File and Record Statuses".

This section describes how the import process handles the following situations: Adding New Source Terms, Deleting Source Terms, Classification Conflicts, Global VTA Conflicts, Importing Approved and/or Unapproved VTAs, and Missing Dictionary Terms.

Processing Source Site Data

After running Autoclassification, you must manually process the following source site data. When you update a record, the system sets its Update box to Y, causing the next export job to extract the record to send back to the source site.

Exporting Data

When you have finished processing data from the source site, you must extract the updated data for the relevant X Area into an XML file and send it back to the source site. You can either export data from a single X Area (see "Export DSI Data") or from all active X Areas at once (see "Export Active X Areas"). Both jobs export only changed data, using both timestamps and the Update setting to determine which records have been updated.

If necessary, you can force the export of all data by using the Force Rederivation job (see "Force Rederivation") to set all records' Update boxes to Y. Then you must run either Export DSI Data or Export Active X Areas to export the data.

Sending the XML File to the Source Site

DSI does not handle transporting the XML file to the source site. You must do it manually; for example, zipping the file and sending it via either FTP or email.

To avoid sending the same files to the partner for unnecessary reprocessing, manually delete files from the output directory after sending them to the other site.

Removing X Area Data from the Sponsor Site

Faulty data may be imported into the sponsor site. Therefore, if you have special privileges, you can delete all data belonging to a specific X Area. See "Delete DSI X Areas". You can then reexport all X Area data from the source site (see "Force Rederivation") and reimport the X Area.

Viewing File and Record Status

The Maintain DSI Files window has two sections: X Areas and Files. After you execute a query, the system displays all the X Areas that meet the filter and query criteria in the X Areas section. In the Files section, the system displays all the files that meet the criteria for the X Area selected in the X Areas section.

To view the files for a different X Area, click its name in the X Area column. In the Files section, the system displays its files that meet the filter and query criteria. For each file, the system displays the following information:

• File Name. The file name is generated by the system at the time of export or import (see "XML File Names" in Appendix B, "Disconnected System Integration XML Files" for information on the naming convention).

• Process Type: Import or Export.

• Process Status. The way the system determines the process status depends in part on the settings of the local reference codelist TMS_DSI. See"Using DSI from the Source Site" and "Using DSI from the Sponsor Site" for the implications of the statuses on your site. The possible statuses are:

  • Success. All records were processed successfully, with no warnings or errors. There is no error file.

  • Warnings. All records were processed successfully, but at least one record has an error. You can see warnings associated with each record with an error in the error file. Depending on the OUTSYNCCSP reference codelist setting, there may be a discrepancy message associated with VTA records whose dictionary term is not included in your dictionary.

  • Errors. Some records may have been processed successfully, but at least one record failed import and remains in the error file associated with an error message.

  • Fatal. The job failed due to a problem with the directory or the XML file itself, or the source site and sponsor dictionaries were out of synch and OUTSYNCCSP was set to fail in that situation.

• Manual Status. This status is meant to indicate the state of your work on files with warnings or errors, for use in querying. The system does not enforce any behavior related to the manual status. The possible statuses are:

  • New.

  • Pending.

  • Fixed.

• # Source Data Records. The following three columns concern source term records that are new, updated, or deleted. To see warnings and errors, click View File or select Options, then choose View File.

  • OK: The number of source terms successfully processed without warnings.

  • Warning: The number of source terms successfully processed but with warnings.

  • Failed: The number of source terms with errors; these records were not successfully processed.

• # VTA Data Records. This includes new verbatim term assignments (VTAs, or classifications) reclassified and declassified VTAs, and newly approved or unapproved VTAs. The following three columns concern records that are new VTAs:

  • OK: The number of VTAs successfully processed without warnings.

  • Warning: The number of VTAs successfully processed but with warnings. You can see the warnings in the error file.

  • Failed: The number of VTAs with errors; these records were not successfully processed. You can see the errors in the error file.

• Process Start Time. The time the import or export process began.

• Process End Time. The time the import or export process finished.

• Process Elapsed. The total amount of time spent processing the file, in HH:MM:SS format.

• Audit Information. The following three fields contain audit information for the file selected above:

  • Created By

  • Creation Time

  • Modified By. The user ID of the person who last changed the manual status.

  • Modification Time. The timestamp of the last manual status change.

• Filter. The system displays the current filter settings.

Deleting Files

You can delete X Area files from view by selecting Record, then Delete. This does not delete the file itself, only the database information on the file. You can no longer view the file from this window.

Setting the Filter

The filter determines which X Areas and files are displayed in the Maintain DSI Files window. Your default filter settings are set by selecting the Definition menu, then Maintain Settings.

You can reset the filter at any time by clicking the Filter button in the Maintain DSI Files window. Enter values in the filter pop-up box to limit the files displayed in the Maintain DSI Files window. By default all files are displayed. All settings are optional.

• External System. From the list, select the external system where the source data was originally collected.

• Source Data DB. From the list, select the database of the external system where the source data was originally collected.

• X Area and X Area Name. From the list, select the X Area. The list of values in the X Area field includes both the X Area ID and name, and when you select a value, the system populates both fields.

• X Area Status. From the list, choose the current status of the X Area(s) for the file(s) you want to see. The choices are Active, Inactive, and Complete (for an explanation of the statuses, see "Defining X Areas").

• Last Export Between. Enter the start and end dates of the period in which jobs you want to see were executed. Use the format specified for your installation in OPA Settings (OPA_SQL_DATE_FORMAT); for example, if you use the format DD-MON-YYYY, enter 15-OCT-2004.

• Manual Status. From the list, select the manually set status of the file(s) you want to see. The choices are: New, Pending, and Fixed.

• Process Type. From the list, select the process that produced the file you want to see; either Export or Import.

• Process Between…and.… Enter the start and end dates of the period in which jobs you want to see were executed. The system returns all files that were even partially executed during that data range. Use the format specified for your installation in OPA Settings (OPA_SQL_DATE_FORMAT); for example, if you use the format DD-MON-YYYY, enter 15-OCT-2004.

• Process Status. Select one or more status. The system will display files resulting from jobs with the statuses you select, and that meet the other criteria.

Click one of the following buttons:

• Clear. Returns values to what they were when you opened the filter.

• Restore. Resets the filter to the default values (from the Definition menu, select Maintain Settings to set).

• OK. Saves the current settings. The filter pop-up window disappears, leaving the Maintain DSI Files window in view. You must execute a query (from the Query menu, select Execute Query or press F8) to display the files that meet the filter criteria. The filter criteria are displayed at the bottom of the Files section of the window. These settings persist the next time you open the Maintain DSI Files window.

• Cancel. Closes the Filter window without saving any changes.

Export DSI Data

This job extracts data for a single X Area into an XML file located in the Output directory. It processes only data that has been added, deleted, or updated since the last export job was run ("incremental" data processing). The export process should fail only if there is a problem with the receiving output directory. To run the job:

1. Go to Disconnected System Integration, then select Export DSI Data.

2. Enter values for the following parameters. A list of values is available for the first three.

   • External System. The name of the external system where the source data originated.

   • Instance. The database where the source data originated.

   • X Area. The X Area that corresponds to the unit whose data you want to export; typically a study or case.

   • Report Server Name. Enter the name of the report server you want to run the job.

3. Run the job. You can schedule this job to be run on a regular basis such as nightly or weekly; see "Scheduling Parameters".

Import DSI Data

The import process operates on all XML files currently in the input directory, and deletes them from that directory upon completion. If there are errors or warnings, or the job fails, the system inserts the errors into the XML file associated with the appropriate records, and moves the XML file to the error directory.

The import process begins by calling the XML parser to validate the structure of the XML file against the XML schema. If it is invalid, the job fails. The import job also validates the XML metadata against the mappings defined in the sponsor.

The data import job operates on data differently at the source site and the sponsor site; see "Using DSI from the Source Site", and "Using DSI from the Sponsor Site". The way it handles different errors depends on the settings of several reference codelist values in the importing system; see "Setting DSI Preferences with Reference Codelist Settings".

To run the job:

1. Go to Disconnected System Integration, then select Import DSI Data.

2. Enter the name of the report server you want to run the job.

3. Run the job. You can schedule this job to be run on a regular basis such as nightly or weekly; see "Scheduling Parameters".

Import Process Statuses Import jobs can end with four possible statuses: Success, Warning, Error, and Fatal Error. Jobs that end with a status of Success do not have error files. Jobs ending with all other statuses do have error files.

To create the error file, the import process inserts errors or warnings, associated with the appropriate record, into the XML file in the input directory and then moves the XML file into the error directory.

Export Active X Areas

This job extracts data for all active X Areas, creating a separate XML file in the Output directory for each active X Area. It processes only data that has been added, deleted, or updated since the last export job was run ("incremental" data processing). The export process should fail only if there is a problem with the receiving output directory.

1. Go to Disconnected System Integration, then select Export DSI Data.

2. Enter the name of the report server you want to run the job.

3. Run the job. You can schedule this job to be run on a regular basis such as nightly or weekly; see "Scheduling Parameters".

Export X Areas by External System/Instance

This job allows you to export data for a particular X Area from a single site. For example, a sponsor may want to export data from a single CRO rather than all CRO participating in the same study (X Area) at the same time.

To run the job:

1. Go to Disconnected System Integration, then select Export X Areas by External System/Instance.

2. From the External System drop-down list, select the external system to which you want to export data.

3. From the Instance drop-down list, select the database to which you want to export data.

4. Enter the name of the report server you want to run the job.

5. Run the job. You can schedule this job to be run on a regular basis such as nightly or weekly; see "Scheduling Parameters".

Import X Areas by External System/Instance

This job allows you to import data for a particular X Area from a single site. For example, a sponsor may want to import data from a single CRO rather than all CRO participating in the same study (X Area) at the same time.

To run the job:

1. Go to Disconnected System Integration, then select Import X Areas by External System/Instance.

2. From the External System drop-down list, select the external system from which you want to import data.

3. From the Instance drop-down list, select the database from which you want to import data.

4. Enter the name of the report server you want to run the job.

5. Run the job. You can schedule this job to be run on a regular basis such as nightly or weekly; see "Running a Job".

Delete DSI X Areas

Available on the sponsor site only. If faulty data has been loaded into the sponsor site or exported from the source site, it may be necessary to remove all data belonging to the X Area and start again. This job deletes all data belonging to a single X Area: the X Area definition, source terms, omissions, and the database records of the X Area files (not the files themselves).

To run the job:

1. Go to VTA Maintenance, select Disconnected System Integration, then Delete X Area.

2. Enter the following parameters:

   • External System. The name of the external system where the source data originated.

   • Instance. The database where the source data originated.

   • X Area. The X Area that corresponds to the unit whose data you want to export; typically a study or case.

   • Report Server Name. Enter the name of the report server you want to run the job.

If data has become corrupted on the sponsor site, you can export all relevant data from a particular X Area to the other system.

1. Delete the X Area on the sponsor site by running the Delete DSI X Areas job on the sponsor.

2. On the source site, run Force Rederivation on the same X Area.

3. On the source site, run Export DSI Data on the same X Area.

4. Send the data from the source site to the sponsor.

5. Run Import DSI Data on the sponsor site to recreate the X Area and all its data.

Delete Remote DSI Databases

To delete a registered remote database from a sponsor site, do the following:

1. Manually delete all DSI definitions to the remote database in the Define DSIs window, using Delete from the Record menu. This has the effect of deleting all the associated X Areas as well.

2. Run the Delete Remote DSI Databases job.

To run the job:

1. In the Definitions menu, click Jobs and then click Delete Remote DSI Databases. The job window opens.

2. In the job-specific parameter Remote Instance field, select the name of the remote instance from the drop-down list. The system displays only remote instances that are not part of a DSI definition.

3. Run the job. See "Running a Job".

Force Rederivation

This job marks all source terms and omissions in a single X Area for processing in the next export process, whether or not they have been added or modified since the last export job ("full" data processing). To actually process the data, you must run the Export DSI Data job.

If data has become corrupted on the other site, or dictionaries are no longer synchronized, you can export all relevant data from a particular X Area to the other system.

1. From the Definition menu, select Define Disconnected System Integrations, then X Areas, and click the Force Rederivation button. The system marks for mandatory processing (Update=Y) all data in this X Area of the types normally exported from the system (which vary, depending on whether the exporting system is a sponsor or a source site).

2. From the DSI Maintenance menu, select Export DSI Data and run the job; see "Export DSI Data".

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 and Defining HTML Layout Functions.

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.

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.

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;

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.

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;
/

Defining the Name and External System for a Query

Each query you define appears in the Group list for source data searches in the TMS Lite Browser.

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. This name displays in the Group list for source data searches in the TMS Lite Browser.

   • 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 TMS Lite Browser will display the columns for this query. Columns with lower numbers appear in the query on the left.

3. Save. TMS makes this query available upon your next login to the TMS Lite Browser.