4 Work with the Dictionaries

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

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

For more details, refer to the Oracle Argus Safety Installation Guide > Section 10.2, Create Argus Safety Database Schema.

4.1 MedDRA Dictionary

4.1.1 Prerequisites

The system where these dictionaries will be installed has a minimum of 50 MB space.
the system has Oracle client installed, including the following:

SQLPLUS (Exe=sqlplus)

SQL*Loader (Exe=sqlldr)
there is an updated TNSNAMES file and Oracle client to connect to the Argus Safety database.
The Dictionary Management Tool is installed.
An Oracle database instance is available.
A SYSTEM or DBA user account is created.

Note:
The smq_list.asc and smq_content.asc files containing SMQ data must be placed in the same folder as the other dictionary files.

4.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 Dictionary Management Tool and click MedDRA Loader.

The Oracle Database Connect dialog box appears.
Enter the SYSTEM or DBA username and 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, check MedDRA J checkbox.
- To create a new MedDRA user, click Create User, enter the parameters, and click OK.
- To create a new role, click Create Role, enter the parameters, and click OK.
  
  The New MedDRA Role dialog box appears.
In the Dictionary to Load section, do the following:
1. From the drop-down, select the MedDRA Version.
2. Click Browse and select the dictionary files.
3. Select the MedDRA Browser checkbox.
4. From the Tablespace and Index drop-downs, select a table and an index.
5. Click Load.
The system loads the dictionary and a confirmation message appears.
Click OK.

4.1.3 Overwrite an Existing MedDRA or MedDRA J Dictionary

Open the Dictionary Management 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, check the MedDRA J checkbox.
3. From the User drop-down, select a user.
4. Enter the user password in the Password field; re-enter it in the Verify Password field.
5. From the Role drop-down, select a role.
6. From the Current Version to Overwrite drop-down, select the version to overwrite.
7. From the MedDRA Version drop-down, select the MedDRA version to load.
8. Click Browse and select the dictionary files.
9. Check the MedDRA Browser checkbox.
10. From the Tablespace and Index drop-downs, select a table and an index.
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.

4.1.4 Recode MedDRA

The MedDRA Recoding tool displays the following options for each case with the existing data elements after the case number in the XLS export or tab delimited file:

Current Workflow State
Current Workflow Group

These options are available for the end user logs.

The SOC/HLGT/HLT/PT/LLT and Synonym columns in the MedDRA schema and the MedDRA table have been expanded to 250 characters to conform to the ICH guidelines.

Argus Database Table	Location in Argus
CASE_PAT_HIST	Argus Safety > Case Form > Patient Tab > Parent Section > Other Relevant History
CASE_EVENT	Argus Safety > Case Form > Events Tab > Event Information
CASE_PROD_INDICATIONS	Argus Safety > Case Form > Products > Products Indication
CASE_ASSESS	Argus Safety > Case Form > Events Tab > Event Assessment > Event PT (Description)/LLT
CASE_DEATH_DETAILS	Argus Safety > Case Form > Events Tab > Seriousness Criteria > Death Details > Cause of Death & Autopsy Details
CASE_LAB_DATA	Argus Safety > Case Form > Patient Tab > Lab Data
LM_ALWAYS_SERIOUS_TERM	Argus Console > Code Lists > Always Serious Term List
LM_LABELED_TERM	Argus Console > Business Configuration > Products and Licenses > Datasheet
LM_PRODUCT	Argus Console > Business Configuration > Products and Licenses > Primary Indication
LM_LAB_TEST_TYPES	Argus Console > Code Lists > Lab Test Type

4.1.4.1 Logic to Recode MedDRA

Get the Lower Level Term (LLT).

For a case or LM data, if LLT (J) is different from the LLT (E), and either of them is moved to a different PT group in new MedDRA version, then the MedDRA Recode tool resolves the conflict by replacing LLT(J) with LLT(E) and recode as per the new MedDRA dictionary.
Check LLT_Code column in the MEDDRA_PREF_TERM_LLT table to see if LLT is not current (LLT_CURRENCY = N).

Decisions:
- If LLT cannot be found in MEDDRA_PREF_TERM_LLT then record as exception to be noted in LOG file.
- If for a record LLT(J) term is non-current as per the new upgrading MedDRA Dictionary but LLT(E) is current, then MedDRA recode only refreshes the hierarchy of 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.
  
  If for a record, LLT(E) becomes non-current, then the MedDRA recode tool when recoded with the MedDRA J dictionary, replaces LLT(E) and LLT(J) with the PT code and recode if the Process Non-Current Terms checkbox is checked.
- If LLT is current then keep LLT as it is.
- If a current LLT can be found in previous step then continue with next step else go to 1 and select the next set of Terms.
Based on the LLT, get the Preferred Term (PT_CODE) from MEDDRA_PREF_TERM_LLT. Get the rest of the hierarchy from MEDDRA_MD_HIERARCHY, based on PT_CODE and PRIMARY_SOC_FG = 'Y'.
Match all 5 levels of Code and Description and update the data, if required.
Populate the following columns:
- DICT_ID = Current MedDRA Dictionary ID, present under Case Form Configuration.
- CODE_STATUS = 1 (displaying that this set of terms has been encoded).
When you create an Event Group by selecting a term at PT level, and that event group is attached to a datasheet, the hierarchy stored in the datasheet is based on the Primary SOC flag, which is always current.

In the LM_LABELED_TERMS table:
- If only the English hierarchy is populated, then the application fetches data for the Japanese hierarchy based on the English hierarchy populated, and performs recoding based on the new MedDRA dictionary.
- If only the Japanese hierarchy is populated, then the application uses the PT code to populate the English hierarchy, and performs recoding based on the new MedDRA dictionary.
- If neither the English hierarchy, nor the Japanese hierarchy are populated in the table but the PT code or term is populated, then the application uses the PT code or term to populate both the English hierarchy and the Japanese hierarchy, and performs recoding based on the new MedDRA dictionary.
For case or LM data, if LLT(J) is different from LLT(E), and either of them is moved to a different PT group in the new MedDRA version, then the MedDRA Recode tool resolves the conflict by replacing LLT(J) with LLT(E), and recoding based on the new MedDRA dictionary.

For case or LM data, if MedDRA terms are coded with the English dictionary, then during MedDRA Recode, the Japanese hierarchy is populated based on the English hierarchy for both the case and LM data variables.

The following log files are created with detailed old and new values:

Log filename	Scenario	Message
(Case Form) - MedDRA_Recode_Success_YYYY_MM_DD_HH_MIN (LM Data) - MedDRA_Recode_Success_LM_YYYY_MM_DD_HH_MIN	When LLT(E) is Non-Current	LLT(E) is non-current in target MedDRA dictionary: LLT(E) and LLT(J) replaced with PT
(Case Form) - MedDRA_Recode_Success_YYYY_MM_DD_HH_MIN (LM Data) - MedDRA_Recode_Success_LM_YYYY_MM_DD_HH_MIN	When LLT(E) and LLT(J) not under same hierarchy	LLT(E) and LLT(J) not under same hierarchy in target MedDRA dictionary: LLT(J) replaced with LLT(E)
(Case Form) - MedDRA_Recode_Success_YYYY_MM_DD_HH_MIN (LM Data) MedDRA_Recode_Success_LM_YYYY_MM_DD_HH_MIN	When LLT(E) is Non-Current and LLT(E) and LLT(J) not under same hierarchy in target MedDRA Dictionary	LLT(E) and LLT(J) not under same hierarchy in target MedDRA dictionary: LLT(J) replaced with LLT(E). LLT(E) is non-current in target MedDRA dictionary: LLT(E) and LLT(J) replaced 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.

4.1.4.2 Run the Dictionary Management Tool to Recode Events

Open the Dictionary Management 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

4.1.4.3 Recode MedDRA terms at the Enterprise level

For multi-tenant environment, the Dictionary Management Tool allows recoding of MedDRA terms at the Enterprise level.

The MedDRA Recoding tool displays an additional multi-select list of all active Enterprises. This lists all active Enterprise Short Names in the alphabetical order.

New Schema Owner is no longer required. As per the recoding logic, cases are also re-coded to the MedDRA version that is configured in Console.

Displays a note below the Existing MedDRA Version to Re-Code list.

Note:

Cases will be re-coded to the MedDRA version configured in Console for the respective Enterprises.

The MedDRA recoding tool only re-codes the items that match the selected Enterprises and values selected in the Existing MedDRA Version to Re-Code list.

The log file specifies the Enterprise Short Name with every log record that is processed for a particular Enterprise.

4.2 WHO Drug Dictionary

4.2.1 Prerequisites

Windows workstation is available to load the WHODrug data.
the system has Oracle client installed, including the following:

SQLPLUS (Exe=sqlplus)

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

4.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 Dictionary Management 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. From the Dictionary Format drop-down, select an option.
3. To create new user, click Create User.
  
  Enter the information required to create a new user and click OK.
4. To create new role, click Create Role.
  
  Enter the New Role name and click OK.
5. From the drop-down, select the Dictionary Version.
6. Click Browse, navigate to the dictionary files, and click Select.
Click Load.
Click OK.
Enter the SYSTEM or DBA user password, the database name, and click OK.

4.2.3 Overwrite an Existing WHO Drug Dictionary

From the Dictionary Management 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. From the Dictionary Format drop-down, select an option.
3. From the User drop-down, select a user.
4. Enter the user password in the Password field; re-enter it in the Verify Password field.
5. From the Role drop-down, select a role.
6. From the Current Version to Overwrite drop-down, select the version to overwrite.
7. From the drop-down, select the Dictionary Version.
8. Click Browse, navigate to the dictionary files, and click Select.
9. From the Tablespace and Index drop-downs, select a table and an index.
10. Click Load.
11. 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.

4.2.4 Load WHO Drug Dictionary Format C

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

Open the Dictionary Management 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. Select Dictionary Format—Format C or Format C3.
  Note:
  - For Dictionary Format, Format C3, WHODrug 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, WHODrug 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).
3. To create new user, click Create User.
  
  Enter the parameters and click OK.
4. To create a new role, click Create Role.
  
  Enter the parameters and click OK.
5. From the drop-down, select the Dictionary Version.
6. Click Browse, navigate to the dictionary files and click Select.
Click Load.
From the Tablespace and Index drop-downs, select a table and an index.
Enter the SYSTEM or DBA user password, the database name, and click OK.
When the dictionary is loaded successfully, click OK.

4.3 J Drug Dictionary

4.3.1 Prerequisites

The system where these dictionaries will be installed has a minimum of 50 MB space.
The Dictionary Management 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.

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

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

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

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

4.3.2.4 Modify the.mdb file

The current .mdb file shows only a single drop-down 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.

4.3.3 Load J Drug Dictionary

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

Open the Dictionary Management 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. To create a new J-Drug user, click Create User, enter the parameters, and click OK.
3. To create a new role, click New Role, enter the parameters, and click OK.
In the Dictionary to Load section and do the following:
1. Select the J-Drug Version to be loaded from the drop-down.
2. Click Browse, navigate to the dictionary files and select the files.
3. Check the J-Drug Browser checkbox.
In the Tablespace Information section, select a table and an index from the drop-downs.
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.

4.3.4 Overwrite an Existing J Drug Dictionary

Open the Dictionary Management 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 user from the User drop-down.
3. Enter the user password in the Password field; re-enter it in the Verify Password field.
4. Select the appropriate role from the Role drop-down.
5. Select the J Drug dictionary version to load from the Dictionary Version drop-down.
6. Click Browse, navigate to the dictionary files and select the files.
7. In the Tablespace Information section, select a table and an index from the drop-downs.
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