This section describes the database objects that are needed for Flexible PMDA E2B (R3) Paper Report generation.
Installing the Argus Safety database prompts for the creation of the BIP Schema Owner. This schema contains all database objects needed for BI Publisher PMDA (R3) Paper Reports.
For report generation, this schema holds:
Tables
Packages
Java objects
The (R3) specific tables in the schema hold the generated E2B (R3) XML data and also helps in effective data handling.
Global Temporary tables (GTT) temporarily store data for report output generation. These are the only tables used in the BIP data model.
Log Tables store logging information based on the configuration in the CFG_RPT_AGG_PARAMS.
This table <RPT_EXPD_XML_LOG> is populated while executing the PMDA (R3) Paper reports based on the parameter 'Populate Log Tables Yes/No' in the configuration table being 1.
Table 8-1 List of Tables in BIP Owner Schema specific to PMDA (R3) Paper Reports.
| Table Name | Type | Purpose |
|---|---|---|
| GTT_RPT_EXPD_XML | Global Temp | Stores the E2B XML data from the SAFETYREPORT table in ESM Schema owner. |
| GTT_RPT_EXPD_XML_LAB | Global Temp | Extracted lab tests data from the XML is stored in this table. Data pertains to a single case for a user in a session. |
| GTT_RPT_EXPD_LAB_MAT | Global Temp | Matrix formatted lab test data is stored here. Data pertains to a single case for a user in a session. |
| RPT_EXPD_XML_LOG | Log | Log data for GTT_RPT_EXPD_XML. |
| RPT_EXPD_XML_LAB_LOG | Log | Log data for GTT_RPT_EXPD_XML_LAB. |
| RPT_EXPD_LAB_MAT_LOG | Log | Log data for GTT_RPT_EXPD_LAB_MAT. |
Data is logged into the log tables if the report needs to be debugged. For this, the parameter Populate Log Tables Yes/No is set to 1 in the CFG_RPT_AGG_PARAMS table present in the Argus application schema. The default value is 0.
For details about tables, columns, and column mappings with Argus Safety, refer to the Argus Flexible Reporting Data Model.xls.
The XML Data from the ESM Owner is manipulated as needed for the (R3) Output. This manipulation is performed using a Java stored procedure E2BXmlParser.
The BI Publisher Owner schema holds the following package types:
Utility Package
Data load package
PKG_EXPD_RPT_UTIL - Utility package
This package contains all the accessory procedures and functions required for generating PMDA (R3) paper reports.
Utility Package Details
| Procedure/Function | Usage |
| f_get_biprep_path | Obtains the BIP report path from configured flexible code lists. |
| f_get_codelist_code | Obtains the Code list CODE from the CODE_LIST_DETAIL_DISCRETE table. |
| p_get_expd_rpt_params | Obtains the records from the CFG_RPT_AGG_PARAMS table for the PMDA (R3) Paper report template (Default template id is 11). |
| f_get_rpt_ctr | Obtains the count from the RPT_SAVED_MSG table for the report to be generated. |
| f_get_codelist_val | Obtains the Code list Display_Value from CODE_LIST_DETAIL_DISCRETE table. |
| f_get_rpt_catg | Function to get the Reporting Category from CMN_REG_REPORTS. |
| f_get_ja_date | Function to convert varchar date column in lab matrix to Japanese date format. |
| p_get_blinded_text | Procedure to get the blinded text and blinded text element for B.4.k.2.2. |
PKG_EXPD_RPT - Data Load Package
This package is used to populate PMDA (R3) Global temporary tables referred to in the BI Publisher data model to generate the PMDA (R3) Paper report.
Data Load Package Details
| Procedure/Function | Usage |
| p_ins_expd_rpt | This procedure extracts and inserts XML data from ESM owner's SAFETYREPORT table and stores it in GTT_RPT_EXPD_XML. |
| p_upd_expd_rpt_decode | This procedure replaces the code value in the xml with decoded descriptions. |
| p_upd_blinded_elements | Based on the profile, the elements marked for blinded are masked in the XML Data. |
| p_upd_decade | Handles the patient or parent age when the age unit is in decades. |
| p_upd_j10_element | Based on the profile, this procedure masks the j10 element in the XML. |
| f_upd_null_flavor | Null flavor handling for common elements. |
| p_upd_meddra_terms | Handles the MedDRA terms in the XML. |
| f_get_m2_descj | Obtains the DESCRIPTION_J value from CFG_M2. |
| p_upd_country_codes | Updates the country codes in the XML. |
| f_get_pmda_title | Generates the Title of the report. |
| f_before_data | This function Populates temp tables used by the report. |
| f_get_report_id | Function to get the PN_REG_REPORT_ID parameter value. |
| p_set_report_id | Procedure to set the PN_REG_REPORT_ID parameter value to Global variable. |
| f_after_report | Function to delete the unwanted rows from the tables. |
| P_lab_matrix | This Procedure inserts necessary data for Lab Matrix. |
| p_upd_drug_trtmnt | This Procedure updates XML data for drugtreatmentduration. |
List of tables used for PMDA (R3) Paper forms from the Argus Schema:
| Table Name | Purpose |
| CFG_RPT_AGG_PARAMS | Contains the parameters passed for each report, segregates the parameters that are part of the report header, and selects the default values. |
| SAFETY_ERR_LOG | Stores errors, warnings, and debugs that occur during execution. Pushes errors into the Argus Safety error log. |
The following objects from ESM_OWNER schema must be given access to the BIP Owner schema for PMDA (R3) Paper report generation.
SELECT access for the following tables:
SAFETYREPORT
CFG_M2
CFG_E2B
V$SAFETYREPORT
V$CFG_M2
V$CFG_E2B
EXECUTE access for the following packages:
ESM_PMDA_UTL
ESM_UTL