Skip Headers

Oracle® Argus Safety BI Publisher Periodic Reporting Extensibility Guide Release 8.0 E56916-02 |
| Contents

Previous | Next |

# 4 Database Architecture

This chapter contains the following sections:

- Section 4.1, "Database Architecture"

- Section 4.2, "Argus Safety Database Updates"

- Section 4.3, "Access Grants to BI Publisher Owner"

## 4.1 Database Architecture

BI Publisher Periodic Reporting is a customizable Argus Safety feature. The database components and changes specific to BIP reports are explained in the sections that follow.

### 4.1.1 BI Publisher Periodic Reporting Owner Schema

Installing the Argus Safety database prompts for the creation of the BI Publisher Owner user. This schema contains all database objects needed for BI Publisher Periodic Reporting. It also has access to some Argus Safety schema objects through synonyms.

Note: You cannot update case data from the BIP Publisher Owner schema. You can only update the Periodic Report status related tables such as CMN_REG_REPORTS, PER_REPORT_QUEUE and PER_REPORT_STS. The system updates the CASE_REG_REPORTS table for final reports. The system accesses the report blob tables from the BIP Owner to store the report output in Argus Safety.

The schema name is configurable at the time of creation. The system makes an entry in the Common Profile switches to store this schema name for reference by Argus Mart.

For report generation, this schema holds:

- Tables

- Views

- The unzip utility function

- A Java object

- Packages

- Database links

- Database jobs

### 4.1.2 Tables

Tables in the BIP schema populate the case data:

- Global Temporary tables (GTT) temporarily store data for report output generation. These are the only tables used in the BIP data model.

- RM_ tables store persist data and are copies of the GTT tables. The system stores parameters in these tables based on the Persist data.

- Configuration tables handle parameters and transactions.

- SUPPORT tables store information such as parameters and their values, case series details, and BIP job details.

Table 4-1 provides the list of tables in the BIP Owner schema and their users.

Table 4-1 List of Tables in BIP Owner Schema

Table Name | Type | Purpose
--- | --- | ---
RPT_AGG_PARAMS | SUPPORT | Stores the list of parameters that are passed down to run the report.
RPT_AGG_CASE_SERIES | SUPPORT | Stores case series information.
RPT_AGG_CS_CASES | SUPPORT | Stores all cases in the case series that are selected for BIP report generation. For example, Main case series, Cumulative case series, Section 6.2 case series, Ad hoc1 case series, and so on.
RPT_AGG_BIP_JOB | SUPPORT | Stores BIP job information and other transactional data. This table data is retained without purging.
GTT_RPT_AGG_CASE | Global Temp | Stores case information.
GTT_RPT_AGG_DRUG | Global Temp | Stores drug related information.
GTT_RPT_AGG_EVENT | Global Temp | Stores event related information.
GTT_RPT_AGG_EV2DRUG | Global Temp | Stores event to drugs related assessment details.
GTT_RPT_AGG_HEALTHAUTHID | Global Temp | Stores health authority details.
GTT_RPT_AGG_UNIQ_CASES | Global Temp | Maintains a list of unique cases.
GTT_RPT_AGG_DET_LIST | Global Temp | Temporary support table that fetches assessment data.
GTT_RPT_AGG_DRUGNAMES | Global Temp | Stores the drug names for reporting.
GTT_RPT_AGG_BIP_BLOB | CONFIG | Copies and holds the report output blob between the BIP Owner schema and the BIP Metadata repository database.
RPT_AGG_JOB_EXEC_STS | CONFIG | Used to avoid multiple report jobs fetching the report output at the same time.
RM_RPT_AGG_CASE | PERSIST | Persist table for GTT_RPT_AGG_CASE.
RM_RPT_AGG_DET_LIST | PERSIST | Persist table for GTT_RPT_AGG_DET_LIST.
RM_RPT_AGG_DRUG | PERSIST | Persist table for GTT_RPT_AGG_DRUG.
RM_RPT_AGG_DRUGNAMES | PERSIST | Persist table for GTT_RPT_AGG_DRUGNAMES.
RM_RPT_AGG_EV2DRUG | PERSIST | Persist table for GTT_RPT_AGG_EV2DRUG.
RM_RPT_AGG_EVENT | PERSIST | Persist table for GTT_RPT_AGG_EVENT.
RM_RPT_AGG_HEALTHAUTHID | PERSIST | Persist table for GTT_RPT_AGG_HEALTHAUTHID.
RM_RPT_AGG_PARAMS | PERSIST | Persist table for RPT_AGG_PARAMS.
RM_RPT_AGG_UNIQ_CASES | PERSIST | Persist table for GTT_RPT_AGG_UNIQ_CASES.
RPT_AGG_PARAMS | SUPPORT | Stores the list of parameters that are passed down to run the report.
RPT_AGG_CASE_SERIES | SUPPORT | Stores case series information.
RPT_AGG_CS_CASES | SUPPORT | Stores all cases of the case series that are selected for BIP report generation. For example, Main case series, Cumulative case series, Section 6.2 case series, Ad hoc1 case series, and so on.
RPT_AGG_BIP_JOB | SUPPORT | Stores BIP job information and other transactional data. Table data is retained without purging.
GTT_RPT_AGG_CASE | Global Temp | Stores case information.
GTT_RPT_AGG_DRUG | Global Temp | Stores drug related information.
GTT_RPT_AGG_EVENT | Global Temp | Stores event related information.
GTT_RPT_AGG_EV2DRUG | Global Temp | Stores event to drugs related assessment details.
GTT_RPT_AGG_HEALTHAUTHID | Global Temp | Stores health authority details.
GTT_RPT_AGG_UNIQ_CASES | Global Temp | Maintains a list of unique cases.
GTT_RPT_AGG_DET_LIST | Global Temp | Temporary support table that fetches assessment data.
GTT_RPT_AGG_DRUGNAMES | Global Temp | Stores drug names for reporting.
GTT_RPT_AGG_BIP_BLOB | CONFIG | Copies and holds the report output blob between the BIP Owner schema and the BIP Metadata repository database.
RPT_AGG_JOB_EXEC_STS | CONFIG | Used to avoid multiple report jobs fetching report output at the same time.
RM_RPT_AGG_CASE | PERSIST | Persist table for GTT_RPT_AGG_CASE.
RM_RPT_AGG_DET_LIST | PERSIST | Persist table for GTT_RPT_AGG_DET_LIST.
RM_RPT_AGG_DRUG | PERSIST | Persist table for GTT_RPT_AGG_DRUG.
RM_RPT_AGG_DRUGNAMES | PERSIST | Persist table for GTT_RPT_AGG_DRUGNAMES.
RM_RPT_AGG_EV2DRUG | PERSIST | Persist table for GTT_RPT_AGG_EV2DRUG.
RM_RPT_AGG_EVENT | PERSIST | Persist table for GTT_RPT_AGG_EVENT.
RM_RPT_AGG_HEALTHAUTHID | PERSIST | Persist table for GTT_RPT_AGG_HEALTHAUTHID.
RM_RPT_AGG_PARAMS | PERSIST | Persist table for RPT_AGG_PARAMS.
RM_RPT_AGG_UNIQ_CASES | PERSIST | Persist table for GTT_RPT_AGG_UNIQ_CASES.

For details about tables, columns, and column mappings with Argus Safety, refer to the embedded Argus Aggregate Reporting Data Model.

### 4.1.3 Views

The BIP Reporting data model uses the views in the BIP Owner schema to fetch data from Global Temporary tables. These views are:

- V$RPT_ALL_CLINICALSUMMARY

- V$RPT_CASESUMMARY

- V$RPT_CLINICALSUMMARY

- V$RPT_SECT61SUMMARY

### 4.1.4 Database Link

A default database link appears when you enable BI Publisher Periodic reporting. This link is created between the BI Publisher Owner schema and the BI Publisher Repository database and copies the report output blob to the Argus Safety database.

### 4.1.5 Java Objects and Function Call

BIP report output is stored in the metadata repository object in a zipped format. A Java object in the BI Publisher schema extracts the output file. This Java object is embedded in a function.

### 4.1.6 Packages

The BI Publisher Owner schema holds the following package types:

- Utility package

- Data load package

- User exits package

PKG_AGG_RPT_UTIL Utility Package

The utility package holds minor utility features for the data load. Table 4-2 provides a list of functions and procedures and their usage.

Table 4-2 Utility Package Details

Procedure/Function | Usage
--- | ---
f_get_query_details | Populates the case series or query prompts that the user can access.
f_print_as_text | Determines the water mark.
f_get_cs_name | Returns the case series name for a Case Series ID.
f_get_agency_name | Gets the agency name for the passed Agency ID.
p_fetchrptoutput | Copies the report output data into Argus tables, updates CMN_REG_REPORTS, updates the report status tables and stores the submission details of the final report.
f_get_duration | Returns a formatted duration for printing in a report. For example, 10 days.
f_get_codelist_val | Gets the code list display value.
f_get_enterprises | Gets the Active Enterprise list on the BIP console.
f_get_cmn_profile_flag | Fetches the cmn_profile value on key.
f_ConvertBlobToClob | Converts the blob data into clob.

PKG_AGG_RPT Data Load Package

The data load package handles the data extraction and derivations that prepare the data for reporting. Table 4-3 provides the complete list of procedures and functions present in this package.

Table 4-3 Data Load Package Details

Procedure/Function | Usage
--- | ---
Global Variables | Describes all parameters shown or hidden on the BIP report screen as package level variables. Parameter values are automatically stored by the BIP report during execution.
Lexical Variables | Normal package variables described according to the lexical parameters used in the BIP report.
p_pop_psur_case_temp | Populates the temp table GTT_RPT_AGG_CASE.
p_pop_psur_drug_temp | Populates the temp table GTT_RPT_AGG_DRUG.
p_pop_psur_event_temp | Populates the temp table GTT_RPT_AGG_EVENT.
p_pop_psur_ev2drug_temp | Populates the table GTT_RPT_AGG_EV2DRUG.
p_pop_psur_healthauthids_temp | Populates the temp table GTT_RPT_AGG_HEALTHAUTHID.
p_pop_psur_drugnames_temp | Populates the table GTT_RPT_AGG_DRUGNAMES.
f_before_data | The main function invoked from BI Publisher. Called from the Before Report trigger of the BI Publisher report.
f_get_report_id | Retrieves the PN_REG_REPORT_ID parameter value.
p_set_report_id | Sets the PN_REG_REPORT_ID parameter value to the global variable so it can be retrieved through f_get_report_id in BIP reports.
p_check_cs_case_ctr | Checks the counts of cases needed for the trailer page.
p_ins_rpt_status | Inserts the record into PER_RPT_STATUS for log reporting.
p_upd_rpt_status | Updates the status of the report on completion with success or failure.
f_after_report | Final trigger invoked by BI Publisher.
f_get_evtseriouscr_list | Gets the event seriousness criteria list.
f_get_dose_stringlist | Generates the dose string list.
f_get_uniq_patient_id | Obtains the unique patient ID.
p_updclinicaldrugrole | Updates the clinicaldrugrole column in GTT_RPT_AGG_DRUG table.
p_update_gtt_tables | Updates the GTT tables for follow-up.
FindAggRptJobID | Local procedure that hits the BI Publisher metadata repository tables, obtains the blob data, converts into clob for easy processing, and arrives at the Job ID through the supplied parameters to the BIP reports. The Job ID is then inserted into the RPT_AGG_BIP_JOB table.
pop_user_security_tables | Populates user security tables based on the user-security level.
p_populate_cover_params | Fills in the data for the RPT_AGG_PARAMS table needed for the cover page.
p_populate_listedness | Determines and populates listedness for each case-event-product based on the chosen algorithm.
p_copy_rpt_case_series | Copies all case series required for report execution into the RPT_AGG_CASE_SERIES and RPT_AGG_CS_CASES tables.
p_populate_dlp_cases | Populates DLP cases.
p_set_lex_conditions | Handles the conditions used to set lexical parameters.
p_pop_log_tables | Populates all RM_ tables.
p_pop_concurrency_errors | Populates the Concurrency Error Handling that mentions whether the case series is modified while a report is in progress.

PKG_AGG_RPT_USER_EXIT User Exit Package

Customers can place their code directly in this package to modify the data loaded. For example, the customer can modify the loaded case data by calling the user exit p_modify_case_temp.

Table 4-4 describes the procedures and functions in the user exit package.

Table 4-4 User Exit Package Details

Procedure/Function | Usage
--- | ---
p_modify_case_temp | Called at the end of the case population procedure. You can customize the populated cases here.
p_modify_event_temp | Called at the end of the event population procedure. You can customize the populated events here.
p_modify_drug_temp | Called at the end of the drug population procedure. You can customize the populated drugs here.
p_modify_evt_assess_temp | Called at the end of the assessment population procedure. You can customize the populated assessment here.
p_modify_healthauthids_temp | Called at the end of the health authority details population procedure. You can customize the populated health authority IDs here.
p_modify_drugnames_temp | Called at the end of the drug name details population procedure. You can customize the populated drug name here.
p_modify_rm_case_temp | Called after loading the RM_RPT_AGG_CASE table.
p_modify_rm_event_temp | Called after loading the RM_RPT_AGG_EVENT table.
p_modify_rm_drug_tem | Called after loading the RM_RPT_AGG_DRUG table.
p_modify_rm_evt_assess_temp | Called after loading the RM_RPT_AGG_EV2DRUG table.
p_modify_rm_healthauthids_temp | Called after loading the RM_RPT_AGG_HEALTHAUTHID table.
p_modify_rm_drugnames_temp | Called after loading the RM_RPT_AGG_DRUGNAMES table.

Figure 4-1 General Structure of a User Exit

Description of "Figure 4-1 General Structure of a User Exit"

### 4.1.7 BI Publisher Owner Schema Database Jobs

The BI Publisher Owner holds the following database jobs. You must create these jobs manually during installation.

For more information on these, refer to the Argus Safety Installation Guide.

Job for Report Output Copying

This is the first job created for calling the utility procedure p_fetchrptoutput. This transfers the completed report to Argus Safety.

The output is connected to the configuration using the configuration ID, BIP report name, and the draft/final option. The new output replaces the output with the same combination of key values.

Oracle recommends you execute this job every 3 minutes. However, you can customize execution according to your needs.

For large customers who run multiple concurrent reports, you can execute job runs every 3-10 minutes. For small customers who run only a few reports the whole day, you can execute runs on an hourly basis.

If required, you can customize the job to push the completed report output to other data sources instead of the Argus Safety database.

Job for Persist Data Purging

Another database job needed to purge the data present in the Persist (RM) tables is the Remove the data that exceeds the purge duration job.

This job is not needed if you prefer not to use the Persist data mechanism. This can be set to run once a day or once a week based on the data load.

## 4.2 Argus Safety Database Updates

The Argus Safety database has been enhanced to support BI Publisher Reporting.

### 4.2.1 Table Changes Required

Table 4-5 describes the database tables added to the Argus Safety database to handle the internal operations related to BI Publisher Periodic Reporting.

Table 4-5 Argus Safety Database tables

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.
CFG_BIP_REPORT_PARAMETERS | Stores the list of parameters that are passed in through the BI Publisher WebServices API.

### 4.2.2 Package Changes Required

Packages related to case series have been updated to store case series data.

The GSS_UTIL package has been updated to find if a user's access has expired.

A new package, pkg_rpt_log, is called from BIP packages and stores the error, warning and debug messages in the SAFETY_ERR_LOG table. Additionally, it stores the error messages in the traditional Argus Safety error log tables by calling Pkg_Console_Common.p_error_log.

## 4.3 Access Grants to BI Publisher Owner

You must provide access to the Argus database objects so the BIP schema can access them.

- SELECT access for Case related, Code list, and Configuration tables.

- SELECT, INSERT, UPDATE access for process tables such as CMN_REG_REPORTS, PER_RPT_QUEUE, PER_RPT_STS, CMN_SUB_REPORTS, and so on.

- EXECUTE access for various common packages such as PKG_RLS and GSS_UTIL.

Note: You need access for compiling BIP Packages in the BIP Owner schema.