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	Action
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.1, "Create Argus Safety Database Schema".

13.1 MedDRA Dictionary

13.1.1 Prerequisites

The system where these dictionaries will be installed has a minimum of 50 MB space.
The schema creation tool is installed.
An Oracle database instance is available.
A SYSTEM or DBA 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.

13.1.2 Load MedDRA or MedDRA J Dictionary

Use these instructions to load a new MedDRA or MedDRA-J dictionary while not overwriting any MedDRA dictionary versions you may already have loaded.

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:
- 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.
  
  a. Enter the parameters and click OK.
Click Create Role.

The New MedDRA Role dialog box appears.
Enter the new role name in the New Role field and click OK.
In the Dictionary to Load section, do the following:
1. From the drop-down list, select the MedDRA Version.
2. Click Browse and select the dictionary files.
3. 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.1.3 Overwrite an Existing MedDRA or MedDRA J 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. Click Browse and select the dictionary files.
10. 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.1.4 Recode MedDRA

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.
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
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.

13.1.4.1 Run the Schema Creation Tool to 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. In the Enterprises field, select an 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. In the Argus MedDRA Version to Re-code field, 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. In the Data Update/View Options [Currency determined at LLT Level Only] field:
  - Check one or all of the Process Current Terms, Process Non-Current Terms and/or Update dictionary version checkboxes.
  - Select one of the following options:
    
    - Update Data to reflect the updates in cases and audit log.
    
    - View Only to view what events will be coded without making any updates to cases and audit log.
4. In the Output Log File Options, select an output log file option and directory path for the log files.
  - Delimited Text
  - Excel Sheet output
5. Click on the Execute button to start the recoding process.
6. 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.
7. 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.2 WHO-Drug Dictionary

13.2.1 Prerequisites

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.
To display the WHO Drug Dictionary version in Argus, the CFG_DICTIONARIES_GLOBAL.VERSION column fetches data from Version.txt file.

If Version.txt file is missing during WHO Drug dictionary load then WHO Drug Dictionary version is displayed based on the value available in CFG_DICTIONARIES.VERSION_NUMBER column only.

13.2.2 Load WHO-DRUG Dictionary to New Tables

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.

Open the Schema Creation Tool and click Who Drug Loader.

A disclaimer message pop-up appear. Click OK.
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.2.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, navigate to the dictionary files and click Select.
4. Click Load.
5. View the WHO-Drug dictionary log file.
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.2.4 Load WHO-Drug Format C Dictionary

For information about format C, go to http://who-umc.org.

Open the Schema Creation Tool and 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 updated 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 updated to Varchar2(250) and Varchar2(110).
Click Create Role, enter the parameters and click OK.
In the WHO-Drug Dictionary Loader dialog box:
1. From the drop-down list, select the Dictionary Version.
2. Click Browse, navigate to the dictionary files and click Select.
Click Load.
Enter the SYSTEM or DBA user password, the database name, and click OK.
When the dictionary is loaded successfully, click OK.

13.3 J Drug Dictionary

13.3.1 Prerequisites

The system where these dictionaries will be installed has a minimum of 50 MB space.
The schema creation tool is installed.
An Oracle database instance is available.
A SYSTEM or DBA user account is created.
The dictionary distribution organization name and contact
- 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
Note:
J-Drug Dictionary distributor organization (MT Kyogikai) is a different organization from Oracle thus there is a possibility that their specifications or procedures may change in future as per their own discretion.

13.3.2 Create and Modify Required File

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.

13.3.2.1 create All_Data.txt file

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:

13.3.2.2 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 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:

Surrounding text describes formulationcode.jpg.

Sample formulationcode.txt:

Surrounding text describes formulationcode1.jpg.

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

13.3.2.3 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:

Surrounding text describes drugnameeng1.jpg.

13.3.2.4 Modify the.MDB file

The current .mdb file shows only a single drop-down list value for the release version on the J-drug dictionary loader. Modify this file to use the latest version of the dictionary.

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).
To add a new version, modify the MedDRA Version column.

For example, if 2015-OCT is the last version added, add 2015-DEC, (note that you must append a comma).
Similarly, modify other rows and tables wherever the previous version number exists.

13.3.3 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.

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 has not already been loaded.
2. In the Tablespace Information section, select the tablespace and index from the drop-down lists.
3. To create a new J-Drug user, click Create User.
4. 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, navigate to the dictionary files and select the files.
3. Check the J-Drug Browser checkbox.
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 JDrug_OCT_2015 dictionary.

13.3.4 Overwrite an Existing J Drug Dictionary

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, Loading Options section, 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, navigate to the dictionary files and select the 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.

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