13 Work with the Dictionaries

For each dictionary, you need to create a schema with the Schema Creation Tool and then load the dictionary.

Schema Name	Description
MedDRA Schema	To enable MedDRA, create this schema by using the MedDRA Loader Tool when MedDRA is loaded to the new database tables.
J Drug Schema	To enable J Drug, create this schema.
WHO Schema	To enable WHO, create this schema by using the WHO Loader Tool when WHO is loaded to the new database tables.

For more details, refer to the Section 11.4, "Create Argus Safety Database Schema".

13.1 Prerequisites to Load the Dictionaries

To work with MedDRA and MedDRA J dictionaries, make sure:
- the system where these dictionaries will be installed has a minimum of 50 MB space
- the schema creation tool is installed
- Oracle database instance is available
- a SYSTEM user account is created
  
  Note:
  If loading MedDRA V8 or V8.1, the smq_list.asc and smq_content.asc files containing SMQ data must be placed in the same folder as the other dictionary files.
To work with WHO-DRUG dictionary, make sure:
- Windows workstation PC is available to load the WHO-DRUG data
- the system has Oracle client installed, including the following:
  
  SQLPLUS (Exe=sqlplusw)
  
  SQL*Loader (Exe=sqlldr)
- there is an updated TNSNAMES file and Oracle client to connect to the Argus Safety database.
- the following WHO-DRUG dictionary data files are available:
  
  bna.dd ccode.dd
  
  dda.dd ddsource.dd
  
  ing.dd man.dd
  
  dd.dd ina.dd
- the format of the WHO-DRUG dictionary data files is Text and alternate rows are not blank.
  
  Note:
  WHO-DRUG is loaded using sql*load with DIRECT=TRUE option. Because of sql*loader restrictions, no one should have access to the Argus Safety system while WHO-DRUG is being loaded.

13.2 Load MedDRA Dictionary

Open the Schema Creation Tool, and click MedDRA Loader.

The Oracle Database Connect dialog box appears.
Enter the SYSTEM user password, Database name, and click OK.

The MedDRA Dictionary Loader dialog box appears.
Do the following:
- To load MedDRA dictionary for the first time, select Load to New Tables.
- To load a MedDRA J dictionary, select MedDRA J.
- In the Tablespace Information section, select the tablespace and index from the respective drop-down lists.
- To create a new MedDRA user, click Create User.
The New MedDRA User dialog box appears.
Enter the parameters, and click OK.

The MedDRA Dictionary Loader dialog box appears.
Click Create Role.

The New MedDRA Role dialog box appears.
Enter the new role name in the New Role field, and click OK.

The MedDRA Dictionary Loader dialog box appears.
In the Dictionary to Load section, do the following:
1. Select the MedDRA Version being uploaded from the drop-down list.
2. To locate the dictionary files, click Browse, and select the files.
3. To use this dictionary version in the Argus Safety MedDRA Browser, select the MedDRA Browser checkbox.
4. Select the MedDRA version to be loaded from the MedDRA Version drop-down list, and click Load.
The system loads the dictionary and a confirmation message appears.
Click OK.

13.3 Overwrite an Existing MedDRA Dictionary

Open the Schema Creation Tool, and click MedDRA Loader.

The Oracle Database Connect dialog box appears.
Enter the SYSTEM user password, the Database name, and click OK.

The MedDRA Dictionary Loader dialog box appears.
Do the following:
1. Select Overwrite.
2. To load a MedDRA J dictionary, select MedDRA J.
3. From the Tablespace and Index drop-down lists, select a tablespace and index.
4. From the User drop-down list, select a user.
5. Enter the user password in the Password field; re-enter it in the Verify Password field.
6. From the Role drop-down list, select a role.
7. From the Current Version to Overwrite drop-down list, select the version to overwrite.
8. From the MedDRA Version drop-down list, select the MedDRA version to load.
9. To go to the directory where the dictionary files reside, click Browse, and select the dictionary files.
10. To use the dictionary version in the Argus Safety MedDRA Browser, check the MedDRA Browser checkbox.
11. Click Load.
The Oracle Database Connect dialog box appears.
Enter the SYSTEM user password, the Database name and click OK.

When overwriting the dictionary is complete, the Dictionary Load dialog box appears.
Click OK.

13.4 Recode Events

Open the Schema Creation Tool, click MedDRA Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the MedDRA Dictionary Loader dialog box, click Re-Code.
In the Event Re-Coding dialog box, do the following:
1. Select the Enterprise to recode.
  
  Note:
  If Argus is setup in Single Tenant Mode, you will only have one option here. If you are setup as a Multi-Tenant Database, you can choose which Enterprises to recode. Multiple enterprises can be selected.
2. Select the existing version of MedDRA that needs to be re-coded.
  - Select a specific version to only recode data coded with that version.
  - Select All to recode all existing coded data regardless of the version it is coded with.
3. Select either or all of the Process Current Terms, Process Non-Current Terms and/or Update dictionary version checkboxes.
4. Select Update Data if events are to be updated or select View Only if you are interested is just seeing what events will be coded without making the changes.
5. Select the Output File format.
  - Delimited Text
  - Excel Sheet output
6. Click on the Execute button to start the recoding process.
7. When the system displays the Connect to Database dialog box, enter the Schema Owner name, Password, and Database. Click OK.
  - Enter the schema owner name in the Argus Schema Owner field.
  - Enter the password in the Password field.
  - Enter the database name in the Database field.
8. The system recodes the following fields from Case Form and Code List.
  
  Field Location Name of Recoded Field
  
  Case Form Death Details
  Lab Data
  
  Other Relevant History
  
  Product Indications
  
  Events
  
  Case Diagnosis
  
  Code List Product Indication
  Lab Test Types

Field Location	Name of Recoded Field
Case Form	Death Details Lab Data Other Relevant History Product Indications Events Case Diagnosis
Code List	Product Indication Lab Test Types

13.4.1 Recode MedDRA for J Dictionary

If for a record either LLT(E) or LLT(J) term is non-current as per the new upgrading MedDRA Dictionary, then MedDRA recode only refreshes the hierarchy for both LLT(E) and LLT(J).

Note that for the records for which hierarchy is refreshed, the LLT Term's text and currency is also be refreshed based on the respective LLT codes.
Before recoding MedDRA, the re-coding logic verifies if both LLT (E) and LLT (J) belongs to the same hierarchy (that is, under the same PT) in the new upgrading MedDRA or not. And records the term only If they belong to the same hierarchy, else just creates an entry to the logs (for manual update later).
Re-coding the terms with English MedDRA remains as-is but the application is refreshed for the non-current LLT (E) with PT.
If you execute the MedDRA Recode with English MedDRA the preferences for executing will be limited as explained in the function flow for re-coding with J MedDRA.
The following log files are created with detailed old and new values:
- Log files for LLT Terms Non-current in new MedDRA:
  
  File Name (Case Form): MedDRA_Recode_Success_NonCurrentJ_YYYY_MM_DD_HH_MIN
  
  File Name (LM Data): MedDRA_Recode_Success_LM_NonCurrentJ_YYYY_MM_DD_HH_MIN
- Log files for LLT Terms belongs to different PT:
  
  File Name (Case Form): MedDRA_Recode_Failure_PTMisMatchJ_YYYY_MM_DD_HH_MIN
  
  File Name (LM Data): MedDRA_Recode_Failure_LM_PTMisMatchJ_YYYY_MM_DD_HH_MIN

13.4.2 Event Recoding Dialog Box Options

Option	Point E
Argus MedDRA Version to Re-code	Select the existing MedDRA version to re-code.
Enterprises	Select the enterprises to recode.
Data Update/View Options [Currency determined at LLT Level Only]	Check one or both of the following options: Process Current Terms (Using Primary SOC Path) Process Non-current Terms (Using Primary SOC Path) Select one of the following options: Update Data (Updates will be made to cases and to the audit log.) View Only (Updates will not be made to cases and to the audit log).
Output Log File Options	Select an output log file option and directory path for the log files.
Status	Displays status.

13.5 Load J Drug Dictionary

The J Drug Dictionary loader in the Schema Creation Tool now supports loading the English name from the English sub file that is part of J Drug Dictionary.

13.5.1 Before loading the J Drug Dictionary

The following is the information necessary to load the J Drug Dictionary data into the Argus Safety Japan application.

the dictionary distribution organization name and contact
file to be used
how the file to be used
if any necessary file is to be created
how to understand the current .mdb file that shows only a single drop-down list value for the release version on the J-drug dictionary loader.

Assumptions:

J-Drug Dictionary distributor organization (MT Kyogikai) is a different organization from Oracle thus there is a possibility that their specification, scheme or procedure may change in future as per their own discretion.

The following is the detailed information:

J-drug dictionary distributor organization information

Organization Name: MT Kyogikai

Contact Information:

URL: http://www.iyaku.info/

TEL: +81-3-3230-2867

FAX: +81-3-3239-3954

e-mail:mtk@iyaku.info
J-drug loader load procedure

J drug loader loads the following files using dictionary loading tool:
- All_Data.txt
- formulationcode.txt
- drugnameenglish.txt
All the files must be present to load the dictionary, and the file names must be same as mentioned above.

To create file All_Data.txt:

Copy the file received from MT Kyogikai to Al_Data.txt without character code conversion. This file must be a file which contains all the drug data records. A file that contains only the delta (difference from the previous release) must not be used for All_Data.txt.

Sample All_Data.txt'files:

To create formulationcode.txt file:

The file formulationcode.txt is a text file containing the drug formulation code information. You need to create this text file on your own. The drug formulation information is provided from MT Kyogikai on a document titled Drug Name Data File and English Name Sub File Summary. The formulation code list section provides the contents information of the formulationcode.txt file.

Format of the file formulationcode.txt:

- Physical file name: formulationcode.txt

- File format: CSV (Comma Separated Value) with 4 fields.

- Character Code: Shift-JIS code. (This file contains Kanji.)

- Field Information:

Sample formulationcode.txt:

The complete formulationcode.txt file as of Feb.2011 is available at:

https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1293240.1&attachid=1293240.1:formulationcode&clickstream=yes

To create drugnameenglish.txt file:

Copy the file received from MT Kyogikai, and rename the file to drugnameenglish.txt. This file is added in order to support English Names in J dictionary

Sample drugnameenglish.txt:
To modify the.MDB file:
1. Open the jdrug.mdb from the following location:
  
  <disk>:\Program Files\Oracle\Argus\DBInstaller
  
  A table appears with J_Drug table supported versions (second column).
2. To add a new version, modify the MedDRA Version column.
  
  For example, if 2015-OCT is the last version added, then to add a new version (2015-Dec) append the column value with a comma.
3. Similarly, modify other rows, and for other tables wherever the previous version number exists.

13.5.2 Load J Drug dictionary into the database

Open the Schema Creation Tool, and click J Drug Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the J Drug Dictionary Loader dialog box, do the following:
1. Select Load to New Tables if a J-Drug dictionary is not loaded before.
2. Locate the Tablespace Information section and select the tablespace and index from the drop-down lists.
3. Click Create User to create a new J-Drug user
In the New J-Drug User dialog box, enter the parameters, and click OK.
Click Create Role.
Enter the New Role name, and click OK.
In the J-Drug Dictionary Loader dialog box, locate the Dictionary to Load section an do the following:
1. Select the J-Drug Version to be loaded from the drop-down list.
2. Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.
3. Check the J-Drug Browser checkbox if this dictionary version is being used in the Argus Safety MedDRA Browser.
4. Click Load.
Click OK.

Note:
*Argus Safety will use and display J drug data from the latest J drug dictionary which is loaded in the database.
For example, if JDrug_Aug_2015 dictionary and JDrug_OCT_2015 dictionary are loaded in the database, then Argus Safety will use data from the latest dictionary i.e., JDrug_OCT_2015 dictionary.

13.6 Overwrite an Existing J Drug Dictionary

This section provides instructions for overwriting an existing J Drug dictionary and for recoding events.

Open the Schema Creation Tool, click J Drug Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the J Drug Dictionary Loader dialog box, locate the Loading Options section and do the following:
1. Select Overwrite.
2. Select the tablespace and index from the Tablespace and Index drop-down lists.
3. Select the user from the User drop-down list.
4. Enter the user password in the Password field; re-enter it in the Verify Password field.
5. Select the appropriate role from the Role drop-down list.
6. Select the J Drug dictionary version to load from the Dictionary Version drop-down list.
7. Click Browse to go to the directory where the dictionary files reside and select the appropriate dictionary files.
8. Click Load.
Enter the SYSTEM or DBA user password, the Database name, and click OK.

The Dictionary Load dialog box appears.
Click OK.

13.7 Load WHO-DRUG Dictionary

Note:

By uploading a version of WHODrug Enhanced, WHODrug Global or other UMC products, you confirm holding a valid license granted by the UMC for the uploaded UMC product.

13.7.1 Before loading the WHO-DRUG dictionary

Verify the following:

Windows workstation PC is available to load the WHO-DRUG data on
The PC has Oracle client installed, including the following:

SQLPLUS (Exe=sqlplusw)

SQL*Loader (Exe=sqlldr)

There is an updated TNSNAMES file and Oracle client to connect to the Argus Safety database.
The following WHO-DRUG dictionary data files are available:
- bna.dd
- ccode.dd
- dd.dd
- dda.dd
- ddsource.dd
- ina.dd
- ing.dd
- man.d
The format of the WHO-DRUG dictionary data files is Text and alternate rows are not blank.

Note:
WHO-DRUG is loaded using sql*load with DIRECT=TRUE option. Because of sql*loader restrictions, no one should have access to the Argus Safety system while WHO-DRUG is being loaded.

13.7.2 Load WHO-Drug Dictionary to New Tables

Launch the Schema Creation Tool, click Who Drug Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the WHO-Drug Dictionary Loader dialog box, do the following:
1. To load the dictionary into a separate schema, click Load New Tables.
2. To create new user, click Create User.
  
  Enter the information required to create a new user, and click OK.
3. To create new role, click Create Role.
  
  Enter the New Role name, and click OK.
In the Dictionary to Load section, enter the New Role name, and click OK.
Click Load.
Click OK.
Enter the SYSTEM or DBA user password, the Database name, and click OK.

13.7.3 Overwrite an Existing WHO-Drug Dictionary

From the Schema Creation Tool, click Who Drug Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the WHO-Drug Dictionary Loader dialog box, do the following:
1. Click Overwrite.
2. Select the dictionary version to load.
3. Click Browse to display the Select Folder dialog box and select the appropriate path, and click Select.
4. Click Load to load the dictionary.
5. View WHO-Drug dictionary log.
Enter the SYSTEM or DBA user password, the Database name, and click OK.

A confirmation message that the dictionary is loaded successfully appears.
Click OK.

13.7.4 Load WHO-Drug Dictionary in Different Format

Format C is a WHO-Drug dictionary format. For information about this format, go to http://who-umc.org.

To load the WHO-DRUG dictionary using the Format C option:

From the Schema Creation Tool, click Who Drug Loader.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
In the WHO-Drug Dictionary Loader dialog box do the following:
1. To load the dictionary into a separate schema, click Load New Tables.
2. To create new user, click Create User.
  
  Enter the parameters, and click OK.
3. Select Dictionary Format—Format C or Format C3.
  Note:
  - For Dictionary Format, Format C3, WHO Drug schema will have the table named WHO_DRUG_C3_MASTER and WHO_DRUG_C3_MEDICINAL_PRODUCT, instead of table WHO_DRUG_C_MASTER and WHO_DRUG_C_MEDICINAL_PRODUCT. These table will have the DRUG_NAME as Varchar2(1500).
    
    Besides, this schema will also have views as WHO_DRUG_C_MASTER and WHO_DRUG_C_MEDICINAL_PRODUCT which will point to the tables WHO_DRUG_C3_MASTER and WHO_DRUG_C3_MEDICINAL_PRODUCT but the Drug Name is tranced to Varchar2(250).
  - For Dictionary Format, Format B3, WHO Drug schema will have the table named WHO_B3_DRUG_DICT and WHO_B3_ATC_CODE, instead of table WHO_DRUG_DICT and WHO_ATC_CODE. These table will have the DRUG_NAME as Varchar2(1500) and ATC_TEXT VARCHAR2(110).
    
    Besides, this schema will also have views as WHO_DRUG_DICT and WHO_ATC_CODE which will point to the tables WHO_B3_DRUG_DICT and WHO_B3_ATC_CODE but the Drug Name is tranced to Varchar2(250) and Varchar2(110).
Click Create Role, enter the parameters, and click OK.
In the WHO-Drug Dictionary Loader dialog box:
1. Select the Dictionary Version to load from the drop-down list.
2. Click Browse to display the Select Folder dialog box and select the appropriate path.
Click Load.
Enter the SYSTEM or DBA user password, the Database name, and click OK.
When the dictionary is loaded successfully, click OK.

bna.dd	ccode.dd
dda.dd	ddsource.dd
ing.dd	man.dd
dd.dd	ina.dd