Export Specific Extensibility

For ICSR reports where the customization is limited to extending the existing structure of the HL7 representation, follow the next steps:

1. Copy profile:
   1. Refer to the General ICSR Extensibility section.
2. Add New Elements: You can add an element to the custom profile using the SQL script.
   1. Prepare the script for the sample element as follows:

   Field	Description
   PROFILE	Name of the custom profile and foreign key to CFG_PROFILE.PROFILE.
   DTD_ELEMENT	Name of the new element. Oracle recommends that the length does not exceed 30 characters.
   HIE_LEVEL	Hierarchy level of each ICH ICSR element. The level should be same as for the neighboring element.
   DTD_TYPE	Values are Template or User Defined. Should be same as for the neighboring element.
   DTD_LENGTH	Length of the element value. In the case of R3, if the element is required to support NF, the length of the element should be increased by +2. Also, NF will be wrapped in [ ] to identify it as a NF.
   LANGUAGE	Language of each ICH ICSR element and foreign key to CFG_LANGUAGE.LANGUAGE.
   MANDATORY_DTD_ELEMENT	Values are ‘Y’ (Yes) or ‘N’ (No). If the value is ‘Y’, the element should have sufficient data for the report to generate successfully; alternatively, the system will throw a validation error.
   MANDATORY	Reference for the ICH ICSR element. Values are ‘M’ (Mandatory), ‘MO’ (Mandatory Optional) and Blank. If the value is set to ‘MO’ for a parent element, the elements that exist under the parent are also marked as ‘MO’. MO works only for elements under the single parent. If MANDATORY_DTD_ELEMENT is set to ‘Y’ and MANDATORY is set to ‘MO’ for the same element, MANDATORY_DTD_ELEMENT takes the priority.
   ORDER_OF_EXECUTION	Order of each element in order to build the E2B report. For a new customized element, the order should be the order of execution of the previous element + 0.01. This order can be changed based on number custom elements expected.
   AE_SELECT_STMT_ELEMENT_ASSOC	Values depend on the level at which the query is written for the element. If the query is written at the parent element level, the value should be the name of the parent element. Alternatively, if the query is written at the level of the element itself, the value should be null. Queries written at the element level should return a single column value and row. Oracle recommends that the elements under a parent refer either to the parent element query or to individual queries for values. Opting for a hybrid approach may return incorrect results.
   AE_SELECT_STMT_COL_POSITION	Position of the element column to be populated, if the query is written on a parent element for its child elements. The data type should be synchronized with the query column, DTD element type, and staging table field.
   PARENT_ELEMENT	Name of the parent element.
   DATA_ELEMENT	ICH DTD element reference number.
   AE_SELECT_STMT	SQL Statement for Transmit logic.
   AE_CASE_FORM_GUI	Storage for the GUI Case Form Screen Name and Field Name.
   DTD_ELEMENT_TITLE	Title for the element as displayed in Decoded view.
   REPEATABLE	Indicator of whether the element is part of a repeatable node. Values are ‘1’ if true, and ‘-1’ if not.
   AE_USER_PROC	PL/SQL block for Import logic.
   DTD_ELEMENT_TYPE	It contains type of DTD Element as described in the CFG_DTD_ELEMENT_TYPE table. This column is foreign key to CFG_DTD_ELEMENT_TYPE.ID. Text (Alpha Numeric) E2B Code (Allowed values) Period Unit (Not recommended to use, use E2B Code) Yes No (Not recommended to use, use E2B Code) Country (Alpha Numeric) Date Format (Number) Date/Time (Number) MedDRA Version MedDRA Term/Code Note: Oracle recommends that Type ID, 2, 5, 6 have values in CFG_M2. For R2, Oracle recommends that Date, Date Format, MedDRA Version, and MedDRA Term/Code are transmitted together and that Date Format and MedDRA Version are placed before MedDRA Term/Code in the XML.
   UPDATE_FOR_NULLIFICATION	Flag to indicate that the DTD element is modified during the nullification report. This applies only for E2BR2.
   CHILD_ONLY_SQL	List of SQL for DTD elements that are part of a parent element SQL, when UPDATE_FOR_NULLIFICATION set to ‘1’.
   ALWAYS_IMPORT	Indication of whether the element is optional for import through difference report. Value ‘0’ represents the default and marks the element as optional, so users may decide to import it or not. Value ‘1’ denotes that the element is always imported. Value ‘2’ denotes that the Always Import option is disabled on the SM Mapping Utility at the element level. This indicator applies only to ICHICSR and ICHICSRMessageHeader (including its child) elements where the user has no option to check or uncheck the Always Import checkbox.
   DTD_ELEMENT_TITLE_J	Element title in Japanese.
   ALLOW_JAPANESE_CHARACTERS	This column indicates whether Japanese characters are allowed for the E2B element. Possible values are: ‘1’ d ‘Yes’, ‘0’ representing ‘No’, and ‘-1’ representing ‘N/A’.
   FIELD_LOCATION	Field location in Japanese.
   FIELD_LABEL	Name of the field.
   IS_ALLOWED_VAL_CHK	On population 1, denotes that it has allowed values in CFG_M2. This is applicable only for R3 reports with new validation framework enabled.
   DEPENDENT_ON	Name of the element to be transmitted along with this element under the same parent. This is applicable only for R3 reports with new validation framework enabled.
   BLIND_PMDA_AE_PAPER_RPT	N/A
   IS_BLIND_PMDA_DTD_ELEMENT	N/A

   Sample: Add an element to transmit reporter email (REPORTEREMAIL) under the parent element PRIMARYSOURCE after QUALIFICATION for the EMA custom profile EMA-21-CUSTOM.

   1. Identify the HIE_LEVEL – Get HIE_LEVEL of QUALIFICATION using the following query and add 0.01.
      For example if the HIE_LEVEL for QUALIFICATION is 57, then HIE_LEVEL for REPORTEREMAIL is 57.01.

      Note:

      The decimal can change based on number of elements after that element.

      Select * from cfg_e2b where profile = 'EMA-21-CUSTOM' and AE_SELECT_STMT_ELEMENT_ASSOC = 'PRIMARYSOURCE' order by order_of_execution

   2. If the association is at the parent level, add the column for REPORTEREMAIL as the last field in the query. Alternatively, write a separate query to retrieve the data.
   3. Update/Insert the mapping SQL in transmit_mapping_sql_id and in receipt_mapping_sql_id. The fields should match those explained in the aforementioned table.
3. Update the Mapping: If the mapping query exists at the parent level, Oracle recommends that you add the column value (mapping column / function for the new element) at the end of the query. Alternatively, use a separate query to retrieve the value at the element level itself.
   The following represent the bind variables that can be used to achieve the required results.

   Bind Variable Name	Description
   :REPORT_ID	Refers to SAFETYREPORT.REPORT_ID.
   :AWARE_DATE%	Refers to CMN_REG_REPORTS.AWARE_DATE. This variable is available only to the mapping queries present for the nodes that are located directly under the safety report.
   :LICENSE_ID	Refers to the scheduled report license id (CMN_REG_REPORTS.LICENSE_ID)
   :PROD_SEQ_NUM	Refers to the product sequence number for the scheduled report (CMN_REG_REPORTS.PROD_SEQ_NUM)
   :NULLIFICATION	Refers to CMN_REG_REPORTS.NULLIFICATION. Indicates whether a report is nullification or not. This variable is available only to the mapping queries present for the nodes that are located directly under the safety report.
   :FOLLOWUP	Refers to the follow-up number of the report. For J reports, this variable represents the number of reports previously submitted. For Non-J, the variable refers directly to CMN_REG_REPORTS. FOLLOWUP_NUM.
   :CRR_CONFIDENTIALITY	Refers to the report level that is confidentially populated (i.e. CMN_REG_REPORTS.PROTECT). The variable is available only to the mapping queries present for the nodes that are located directly under the safety report.
   :COUNTRY_ID	Refers to the Country of Incidence.
   :AGENCY_ID	Refers to the Agency for which report is scheduled.
   :RPT_CATEGORY	Refers to the reporting category for the license for which the report is scheduled. The variable is applicable only to PMDA transmission.
   :CASE_ID	Refers to the Case ID.
   :DRAFT	Refers to the draft or final report.
   :PREV_REPORT	Refers to the ID of the ESM report that was previously submitted (CMN_REG_REPORTS.ESM_REPORT_ID).
   :MODULE	Possible values are ‘1’ for E2B Generation and ‘0’ for E2B check.

   1. Execute the query in SQL Developer. Then, update the query through the mapping utility or prepare an update statement on top of the base query that already exists for the element.
4. Add Extension Elements to DTD: This applies only to R2 profiles. Verify that the base DTD is be same as the parent profile from which the profile was copied. Oracle recommends that you change the name of the DTD file.
   The steps in this section will add the Extension element in the DTD file.
   1. Take the DTD file corresponding to the base profile chosen in the previous section of this document from the <Interchange InstallationDirectory>\Argus\InterchangeService\DTDFiles folder and make a copy of that profile. For example, copy the EMA-ICSR-V2.1.dtd profile and name it EMA-ICSR-V2.1-Extension.dtd.
   2. Open the file EMA-ICSR-V2.1-Extension.dtd and include the extension DTD Element "reporteremail?". To do so, add the element details in the header row, as highlighted in the following image:
      
   3. Save the updated DTD file in the same folder where all other DTD files exist on the ESM Server.
   4. Update the CFG_PROFILE.PROFILE_DTD field of the extended profile with the new DTD file name.

      Note:

      A quantifier (+,*,? or nothing) is a single character that immediately follows the element to which it applies, to restrict the number of successive occurrences of these items at the specified position in the element hierarchy. The quantifier may be either:

      + — for one or more occurrences of the item. The effective content of each occurrence may differ.

      *— for zero or any number of occurrences allowed. The item is optional and the effective content of each occurrence may differ.

      ? — for no more than one occurrence. The item is optional.

      If there is no quantifier, the specified item must occur exactly one time at the specified position in the content of the element.

5. Configure Reporting Destination for Extension Profile: Perform the following steps to configure the extension profile in Reporting Destination using Oracle Argus Safety Console.
   1. Log on to Argus Safety.
   2. Open the Console and click Code List | Reporting Destination.
   3. Select the agency name to modify and click the EDI tab.
   4. Select the extension profile from the message profile drop-down. For example: "EXTENDED E2B PROFILE"
   5. Enter the extension DTD file with full path into URL of Message DTD field. For example: C:\Program Files\Oracle\InterchangeService\DTD\EMA-ICSR-V2.1-Extension.dtd
      This step does not apply to R3 profiles.
   6. Click Save button to save the changes.
      Oracle Argus Safety is configured for E2B extension for the selected agency.

   Note:

   The Message DTD field is used only for the transmission of E2B extension. The field is not used for import, since the DTD path is already embedded in the E2B file.
6. Add Extension Elements Field in ESM Staging Schema Tables: This applies only to profiles where CFG_PROFILE.CREATE_REPOSITORY is set to 1. As a result,data is stored into the ESM Schema tables.
   Add a field with the same name and with appropriate data type and length under the respective table or view where the parent node name matches the table name.

   Additionally, perform the following configuration:

   1. If the name of the element exceeds 30 characters, abbreviate the column name and add an entry in the COLUMN_DTD_LKUP table.

      Field	Description
      TABLE_NAME	Table name
      COLUMN_NAME	Abbreviated column name
      DTD_ELEMENT	Actual DTD element name

      Sample:

      INSERT INTO COLUMN_DTD_LKUP (TABLE_NAME, COLUMN_NAME, TD_ELEMENT) VALUES ('TABLE_NAME', ‘COLUMN_NAME’, 'ELEMENT_NAME');

   2. Create an entry in the ESM_TAB_COLUMNS table for each column added in following table:

      Field	Description
      TABLE_NAME	Primary Key 1. Table Name
      COLUMN_NAME	Primary Key 2. Column name
      DATA_TYPE	Data Type of the column
      DATA_LENGTH	Data length of the column
      NULLABLE	Whether the column cab is null (‘Y’ or ‘N’)
      COLUMN_ID	COLUMN_ID of the newly added column. Find the max number for that table name in ESM_TAB_COLUMNS table and increment by 1.
      DATA_PRECISION	N/A
      DATA_SCALE	N/A

      A new node is added and CFG_PROFILE.CREATE_REPOSITORY is set to ‘1’.
   3. Create the new table with appropriate access and add a corresponding sequence in the name of PK of that table. If the table has FK from its parent, include the FK too. Make an entry in the ESM_PK_COLUMNS table.

      Field	Description
      TABLE_NAME	Table name
      COLUMN_NAME	Column name
      POSITION	Identifier for the ordering of the primary key
      SEQUENCE_TYPE	Indicator of what type of Sequence is used. ‘C’ denotes ‘CurrVal’, which is used mostly for the FK’s, whereas ‘N’ denotes ‘NextVal’ for the PK.

      Sample:

      Select MAX(COLUMN_ID) + 1 from ESM_TAB_COLUMNS where TABLE_NAME = 'PRIMARYSOURCE' order by column_id desc.

      Insert into ESM_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID) Values ('PRIMARYSOURCE', 'REPORTEREMAIL', 'VARCHAR2', 100, NULL, NULL, 'Y', 23);

      Note:

      Irrespective of whether CFG_PROFILE.CREATE_REPOSITORY is set to ‘1’ or not, any elements added under ‘SAFETYREPORT’ and ‘MHLWADMINITEMSICSR’ nodes should have the respective fields under the above mentioned tables.
7. Modify HL7 reports:
   1. Refer to the General ICSR Extensibility section.
8. Verify the Report Generation:
   1. Log on to Oracle Argus Safety book-in compliance case for the respective custom profile. Schedule and generate the report and verify the final report in the out folder.