Skip Headers

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

Previous | Next |

# 6 Extending a BIP Report

This chapter describes the options available for extending an out-of-the-box BI Publisher Periodic report. It contains the following sections:

- Section 6.1, "Database Layer"

- Section 6.2, "Extending the BI Publisher Data Model"

Note: Oracle encourages customers to extend reports for their use but is not obliged to support the custom or extended code and is not responsible for any loss or damage caused by the extended code.

BI Publisher Periodic Reporting has the following customizable layers:

- Database layer

- BI Publisher layer

  This further comprises:

  - Data Model layer

  - Report Layout layer

## 6.1 Database Layer

Objects specific to BIP Periodic Reporting are present in a separate schema created during the installation of the Argus Safety database. This schema only has a limited set of objects and access privileges. Table 6-1 illustrates these objects and privileges.

Table 6-1 Objects and Access Privileges

Tables | Global Temporary tables populate the report. RM tables persist data for Argus Mart OBIEE dashboards. Configuration tables store report parameter prompts and case series data.
Packages | There are 3 packages: The main package loads the temp table data. The utility package holds the commonly used functions while loading the temp table data. The user exit package customizes the loaded temp table data. None of the packages are wrapped.
Views | Used for grouping and accessed in the BI Publisher data model.
Access Grants | BI Publisher has read-only access to the following objects in the Argus Safety application schema: List and configuration tables Case series tables Case tables Common packages such as gss, gss_util, gss_periodic, p_initialize_access and gss_wnds It has INSERT and SELECT access to: CMN_REG_REPORTS PER_RPT_QUEUE PER_RPT_STATUS CMN_PER_SUB_CHILD CASE_REG_REPORTS Report output tables
Invoker Rights | BIP packages are created with Invoker rights with CURRENT_USER as the AUTHID.
Enterprise Security | The new schema implements the Argus Safety enterprise security features. For data selection, call gss_util.set_context (uname, enterprise).

### 6.1.1 Configuration Extensibility

You can update the out-of-the-box data in the CFG_RPT_AGG_PARAMS table to modify report names. This configuration change is used for:

- Updating the parameter prompt text in the report output.

- Modifying the order of displaying report parameter prompts.

- Validating details of the parameter.

Note: These are configuration changes and do not impact any other functionality. Also, there is no UI for this table. You can use any database tool connecting to the Argus Safety application schema.

### 6.1.2 Extending with User Exits

You can use user exits to customize BIP Periodic Report data present in the GTT and RM Tables. Every population algorithm contains a user exit at the end during the database selection. A user exit lets you:

- Update Records

- Insert Records

- Delete Records

Table 6-2 contains the list of user exits.

Table 6-2 List of User Exits

Procedure | Usage
--- | ---
p_modify_case_temp | Executed at the end of case population procedure. Customization to populated cases can be done here.
p_modify_event_temp | Executed at the end of event population procedure. Customization to populated cases can be done here.
p_modify_drug_temp | Executed at the end of drug population. Customization of populated drugs can be taken up here.
p_modify_evt_assess_temp | Executed at the end of event assessment population. Customization of populated event assessment data can be taken up here.
p_modify_healthauthids_temp | Executed at the end of Health authority details population.
p_modify_drugnames_temp | Executed at the end of drug name details population.

There is also a user exit for each RM table.

Figure 6-1 Extending with User Exits

Description of "Figure 6-1 Extending with User Exits"

Figure 6-2 displays the structure of an out-of-the-box user exit.

Figure 6-2 Structure of an Out-of-the-box User Exit

Description of "Figure 6-2 Structure of an Out-of-the-box User Exit"

An out-of-the-box user exit only has a null statement between the log handlers. You can add logic (as necessary) to insert, update, or delete rows from corresponding or related tables.

A sample extension requirement is shown below:

Requirement

Update the CUSTOMCASE01 column using truncated or formatted study name value based on a condition.

Solution

Modify the p_modify_case_temp user exit. Write the following update statement within the p_modify_case_temp procedure:

    Update GTT_RPT_AGG_CASE
    Set CUSTOMCASE01 = Uformat(studyname)
    Where <condn>

### 6.1.3 Extending Global Temporary Tables

You might not need to extend the Global Temporary tables as the out-of-the-box table itself contains multiple flexible columns. Each GTT and RM table contains:

- 15 columns of type VARCHAR2 (4000)

- 4 columns to support DATE fields. The data type is VARCHAR2(8)

- 2 clob columns

You can fill these columns by using user exits.

### 6.1.4 Extending using Custom Objects

You must retain the integrity of the reports while extending out-of-the-box periodic report database objects. You must create a new custom schema (using the naming standard BIP_CUSTOM) where you can deploy your custom objects.

#### 6.1.4.1 Adding New Columns to GTT or RM Tables

To add new columns to GTT and RM tables:

1. Create a replica of the GTT in the custom schema. For example, if the customer needs new columns in the GTT_RPT_AGG_CASE table, create the replica of this table in the custom schema, named XX_ GTT_RPT_AGG_CASE.

2. Add the new columns to the replica table in the custom schema.

3. Grant the new table access to the out-of-the-box BIP schema.

4. Create a synonym for this object (public synonym). The BIP schema can access this new object.

5. Modify the user exit package corresponding to this GTT to fill in the new custom table and logistics to load the additional two columns.

#### 6.1.4.2 Filling Custom Tables

Follow the same procedure for filling custom tables. You do not need to replicate a table from the BIP schema.

#### 6.1.4.3 Filling Custom Views

You might need to deploy custom views for solving complicated logistics. This functionality is limited to accessing the objects of the BIP schema only.

To fill custom views, perform the following steps:

1. Create the view in the custom schema.

2. Grant BIP schema access to the view.

3. Create a synonym for the view (public synonym).

You can use this view either in the BIP schema or in the BI data model.

#### 6.1.4.4 Filling Custom Packages

Follow the same procedure for filling custom packages.

### 6.1.5 BIP Custom Schema for Cloud

For Argus Safety on Cloud, you might not be allowed to make changes as in the BIP Owner schema. Therefore, you can execute a script that creates a new schema on which you can add your own views, synonyms, packages, functions, and so on.

The new schema is created in the Argus Safety database and grants access to the BIP Owner.

### 6.1.6 Adding or Modifying a View

You can create your own view in the custom schema. If these views directly access BIP schema objects, you might need to provide grants. After the BIP schema is created, it has access to this view and can be utilized in the package through user exits.

### 6.1.7 Adding a Column to the Existing Table

Temp tables provide custom columns for customer use. However, there might be instances when you want to add further tables. You can do this in the following ways:

- Adding the columns directly into the table and manipulating it.

- If you are not allowed to add directly, you can create a replica of the table in the custom schema along with the new columns needed.

The BIP schema is provided a grant for the new object and a synonym is created.

Then, the data can be inserted in the new table.

## 6.2 Extending the BI Publisher Data Model

Note: While extending BI Publisher reports, irrespective of whether the extension is in the data model layer or the layout, Oracle recommends taking a complete backup of the report in another catalog folder and then proceeding with the extension.

This section contains the following:

- Section 6.2.1, "Data Model Query Naming Convention"

- Section 6.2.2, "Data Model Nested Queries"

- Section 6.2.3, "Data Structure Groups"

- Section 6.2.4, "DSUR Summary Table Naming Conventions"

- Section 6.2.5, "Case Series Tables"

- Section 6.2.6, "Lexical Parameters"

### 6.2.1 Data Model Query Naming Convention

The BI Publisher data model queries follow a standard naming convention:

Q<Query level no>_<Report section identification>

For example,

Q1_DSURLINELISTING: First level query of the DSUR line listing section.

Q1_MAINDSURSUMTAB: First level query of DSUR Main Summary tabulation.

Q2_DEATHDSURSUMTAB: Second level query of DSUR Fatal Summary Tabulation.

Q4_CMAINDSURSUMTAB: Fourth level query of DSUR Cumulative Main ST.

Q2_CONSUMTAB: Second level query of Consumer ST.

### 6.2.2 Data Model Nested Queries

BIP Periodic reports follow the model of Nested queries.

Consider the PBRER 6.2 Cumulative Summary Tabulation queries.

Query 1: Q1_PBRER62

    SELECT ct.soc g1pbrer62soc,
            COUNT (ct.CASE_STUDY_DRUG)  cnt_study_drug,
            COUNT (ct.CASE_COMPARATOR)  cnt_case_comparator,
            COUNT (ct.case_blinded)     cnt_blinded,
            COUNT (ct.case_placebo)     cnt_placebo,
            COUNT (ct.case_nosdgiven)   cnt_nosdgiven,
            COUNT (ct.case_num)         cnt_case_num
       FROM v$rpt_clinicalsummary ct
         WHERE ct.REG_REPORT_ID = pkg_agg_rpt.f_get_report_id
        AND (ct.Sec62cumflag = 'Y' AND NVL(ct.sec63nonintcumflag,'N') <> 'Y')
        AND (ct.casetype = 'C' AND ct.eventseriousflag = 'Y')
        AND ct.Clinicaldrugrole != 6
    GROUP BY  ct.ev_socdisplaynbr,ct.soc
    ORDER BY  ct.ev_socdisplaynbr,ct.soc;

In QUERY 1, the column SOC is given an alias g1pbrer62soc

Grouping: g1 -> Group 1

pbrer62 -> Report section

soc -> Column name

Sorting: Order by SOCDISPLAYNBR and SOC. Users can modify the sorting columns by changing the data model queries for each group.

Query 2: Q2_PBRER62

    SELECT  ct.reaction                 g2pbrer62reaction,
            COUNT (ct.CASE_STUDY_DRUG)  cnt_study_drug,
            COUNT (ct.CASE_COMPARATOR)  cnt_case_comparator,
            COUNT (ct.case_blinded)     cnt_blinded,
            COUNT (ct.case_placebo)     cnt_placebo,
            COUNT (ct.case_nosdgiven)   cnt_nosdgiven,
            COUNT (ct.case_num)         cnt_case_num
       FROM v$rpt_clinicalsummary ct
        WHERE ct.REG_REPORT_ID = pkg_agg_rpt.f_get_report_id
         AND (ct.Sec62cumflag = 'Y' AND NVL(ct.sec63nonintcumflag,'N') <> 'Y')
        AND (ct.casetype = 'C' AND ct.eventseriousflag = 'Y')
        AND ct.clinicaldrugrole != 6
        AND ct.soc = :g1pbrer62soc
    GROUP BY ct.reaction
    ORDER BY ct.reaction;

In the above QUERY 2 the column REACTION given an alias "g2pbrer62reaction"

Grouping:g2 -> Group 2

pbrer62 -> Report section

reaction -> Column name.

Sorting: Order by Event Reaction, Users can modify the sorting columns by changing the data model queries for each group.

In Query 2, the group1 column SOC is passed in the where condition AND ct.soc = :g1pbrer62soc.

Hence, Query 2 fetches rows only for the SOCs from Query 1. This NESTED query model is used throughout BIP Periodic reports.

### 6.2.3 Data Structure Groups

For Queries 1 and 2, the sample data structure is depicted below. This can be found in Datamodel -> Code tab.

Query 1 -> Q1_PBRER62 is source for the group G1_PBRER62 and the group G2_PBRER62 is NESTED under G1_PBRER62.

    <group name="G1_PBRER62" label="" source="Q1_PBRER62">
     <element name="CNT_STUDY_DRUG" value="CNT_STUDY_DRUG" label="CNT_STUDY_DRUG" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
     <element name="CNT_CASE_COMPARATOR" value="CNT_CASE_COMPARATOR" label="CNT_CASE_COMPARATOR" dataType="xsd:double" breakOrder="" fieldOrder="3"/>
     <element name="G1_TEXT" value="G1PBRER62SOC" label="G1PBRER62SOC" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
     <element name="CNT_BLINDED" value="CNT_BLINDED" label="CNT_BLINDED" dataType="xsd:double" breakOrder="" fieldOrder="4"/>
     <element name="CNT_PLACEBO" value="CNT_PLACEBO" label="CNT_PLACEBO" dataType="xsd:double" breakOrder="" fieldOrder="5"/>
     <element name="CNT_CASE_NUM" value="CNT_CASE_NUM" label="CNT_CASE_NUM" dataType="xsd:double" breakOrder="" fieldOrder="7"/>
    <element name="CNT_NOSDGIVEN" value="CNT_NOSDGIVEN" label="CNT_NOSDGIVEN" dataType="xsd:double" breakOrder="" fieldOrder="6"/>
    <group name="G2_PBRER62" label="" source="Q2_PBRER62">
    <element name="CNT_STUDY_DRUG" value="CNT_STUDY_DRUG" label="CNT_STUDY_DRUG" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
    <element name="CNT_CASE_COMPARATOR" value="CNT_CASE_COMPARATOR" label="CNT_CASE_COMPARATOR" dataType="xsd:double" breakOrder="" fieldOrder="3"/>
    <element name="G2_TEXT" value="G2PBRER62REACTION" label="G2PBRER62REACTION" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
    <element name="CNT_BLINDED" value="CNT_BLINDED" label="CNT_BLINDED" dataType="xsd:double" breakOrder="" fieldOrder="4"/>
    <element name="CNT_PLACEBO" value="CNT_PLACEBO" label="CNT_PLACEBO" dataType="xsd:double" breakOrder="" fieldOrder="5"/>
    <element name="CNT_CASE_NUM" value="CNT_CASE_NUM" label="CNT_CASE_NUM" dataType="xsd:double" breakOrder="" fieldOrder="7"/>
    <element name="CNT_NOSDGIVEN" value="CNT_NOSDGIVEN" label="CNT_NOSDGIVEN" dataType="xsd:double" breakOrder="" fieldOrder="6"/>
    </group>
    </group>

There can be multiple rows in the group G2_PBRER62 for one row from G1_PBRER62.

For group columns such as SOC (QUERY 1) and REACTION (QUERY 2), the element names vary.

In the sample data structure, the element name G1_TEXT denotes that it is a group column and the value is G1PBRER62SOC alias name given in QUERY 1.

For QUERY 2 the element name G2_TEXT denotes that it is the second group column and the value is G2PBRER62REACTION alias name given in QUERY 2.

### 6.2.4 DSUR Summary Table Naming Conventions

Let us consider the DSUR Main summary tabulation data structure to explain the naming conventions and drug key table functionality.

1. The first group name is G1_DSURSUMTAB. It is different for all DSUR summary sections.

   For fatal summary tabulation, the group name is G1_DSURSUMTAB1.

   For Cumulative main ST section, the group name is G1_DSURSUMTAB3.

   For Cumulative fatal ST section, the group name is G1_DSURSUMTAB4.

   The grouping column is Follow-Up text.The element name is G1_TEXT1 and is used in dsur_sum.rtf.

2. The second group name is G2_DSURSUMTAB. The second (and subsequent) group names are the same for all DSUR summary tabulation sections. This effectively uses the DSUR summary sub-template. The grouping column is Sponsor study Number. The element name is G2_TEXT1 and is used in dsur_sum.rtf.

3. G3_DRUGKEY is the third group which prints Study and Comparator drugs in a table format.

   Figure 6-3 Study and Comparator Drugs Format

   Description of "Figure 6-3 Study and Comparator Drugs Format"

   In the summary tabulation, IMP 1 is printed under IMP Treatment1 and IMP 2 is printed under IMP Treatment2. The titles are configurable (flexible code list: BIP_PROD_CATEGORY).

4. The fourth group name is G4_DSURSUMTAB. It is the same for all DSUR summary tabulation sections. The grouping column is SOC. The element name is G4_TEXT1 and is used in dsur_sum.rtf.

5. The fifth group name is G5_DSURSUMTAB. It is the same for all DSUR summary tabulation sections. The grouping column is Event Reaction. The element name is G5_TEXT1 and is used in dsur_sum.rtf. The Column title appears from the code list BIP_PROD_CATEGORY and element name is G5_TEXT2.

       <group name="G1_DSURSUMTAB" label="G1_DSURSUMTAB" source="Q1_MAINDSURSUMTAB">
               <element name="G1_TEXT3" value="G1MAINDSURPSURFOLLOWUPFLAG" label="G1MAINDSURPSURFOLLOWUPFLAG" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
                      <element name="G1_TEXT1" value="G1MAINDSURPSURFOLLOWUPTEXT" label="G1MAINDSURPSURFOLLOWUPTEXT" dataType="xsd:string" breakOrder="" fieldOrder="2"/>
                      <element name="CNT_CASEID" value="CNT_CASEID" label="CNT_CASEID" dataType="xsd:double" breakOrder="" fieldOrder="3"/>
                      <group name="G2_DSURSUMTAB" label="G2_DSURSUMTAB" source="Q2_MAINDSURSUMTAB">
                         <element name="G2_TEXT1" value="G2MAINDSURSPONSORSTUDYNUMB" label="G2MAINDSURSPONSORSTUDYNUMB" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
                         <element name="CNT_CASEID" value="CNT_CASEID" label="CNT_CASEID" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
                         <group name="G3_DRUGKEY" label="G3_DRUGKEY" source="Q3_DRUGKEY1">
                            <element name="G3_DRUGROLE" value="G3_DRUGROLE" label="G3_DRUGROLE" dataType="xsd:string" breakOrder="" fieldOrder="2"/>
                            <element name="G3_COLUMNNUMBER" value="G3_COLUMNNUMBER" label="G3_COLUMNNUMBER" dataType="xsd:double" breakOrder="" fieldOrder="3"/>
                            <element name="G3_DRUGNAME" value="G3_DRUGNAME" label="G3_DRUGNAME" dataType="xsd:string" breakOrder="" fieldOrder="4"/>
                            <element name="G3_STUDYID" value="G3_STUDYID" label="G3_STUDYID" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
                         </group>
                         <group name="G4_DSURSUMTAB" label="G4_DSURSUMTAB" source="Q4_MAINDSURSUMTAB">
                            <element name="G4_TEXT1" value="G4MAINDSURSOC" label="G4MAINDSURSOC" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
                            <element name="CNT_CASEID" value="CNT_CASEID" label="CNT_CASEID" dataType="xsd:double" breakOrder="" fieldOrder="2"/>
                            <group name="G5_DSURSUMTAB" label="G5_DSURSUMTAB" source="Q5_MAINDSURSUMTAB">
                               <element name="G5_TEXT1" value="G5BMAINDSURREACTION" label="G5BMAINDSURREACTION" dataType="xsd:string" breakOrder="" fieldOrder="1"/>
                               <element name="G5_TEXT2" value="G5MAINDSURLISTCOL" label="G5MAINDSURLISTCOL" dataType="xsd:string" breakOrder="" fieldOrder="2"/>
                               <element name="G5_TEXT4" value="G5MAINDSURORD" label="G5MAINDSURORD" dataType="xsd:double" breakOrder="" fieldOrder="3"/>
                               <element name="CNT_CASEID" value="CNT_CASEID" label="CNT_CASEID" dataType="xsd:double" breakOrder="" fieldOrder="4"/>
                            </group>
                         </group>
                      </group>
                   </group>

### 6.2.5 Case Series Tables

The procedure pkg_agg_rpt.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.

#### 6.2.5.1 Common Queries (lexicals) Used in Reports

The following queries are available in the package header pkg_agg_rpt.

Table 6-3 pkg_agg_rpt Queries

Q_MESSAGE | Returns all WARNING type log messages that appear in the trailer section Warning Messages.
Q_ERROR | All ERROR type messages appear in trailer page under Error Messages section.
Q_CASES | Lists Cases of only Main Case series from the BIP tables RPT_AGG_CASE_SERIES and RPT_AGG_CS_CASES on the Cover page.
Q_REPORTCASESUMMARY | Returns Totals section on the Trailer page. The Total of valid and invalid case count appears (only cases from MAIN CASE SERIES are considered).
Q_WMARK | Populates water marks for the reports. For example, DRAFT, INTERNAL.
Q_MODCS | Case Series Modification history appears on the cover page for all BIP reports.
Q_HEAD | Fetches reg_report_id, report title, report hash, report footer, report from and to date, Previous date and DLP/Non-DLP values for printing on the Cover page as headers.
Q_TITLE | Defines titles for the DSUR Main line listing and Cumulative Summary tabulations based on the parameter Print Serious Adverse Events or Reactions.

The following queries are used in QA sections of the reports.

Table 6-4 QA Section Queries

Q_QA1 | Lists case numbers without any qualifying drugs. Considers all cases not available in the GTT_RPT_AGG_DRUG table but available in GTT_RPT_AGG_CASE.
Q_QA2 | Lists Case numbers without any qualifying events. Considers all cases not available in the GTT_RPT_AGG_EVENT table but available in GTT_RPT_AGG_CASE.
Q_QA3 | Lists Case numbers with undefined case level unlabeledness. Considers all cases with the column GTT_RPT_AGG_CASE.CASEUNLABELEDNESSCODEvalue set to NULL.
Q_QA4 | Lists Case numbers with undefined event level unlabeledness. Considers all cases with the column GTT_RPT_AGG_EVENT.EVENTUNLABELEDNESSCODE value set to NULL.
Q_QA5 | Lists Case numbers with undefined Case level seriousness. Considers cases with column GTT_RPT_AGG_CASE.CASESERIOUSFLAG value set to NULL.
Q_QA6 | Lists Case numbers with undefined Event level seriousness. Considers cases with column GTT_RPT_AGG_EVENT.EVENTSERIOUSFLAG value set to NULL.
Q_QA7 | Lists the Labels configured for drugs in the drug list. The query is different for PMAR, PBRER and DSUR and is available in the data models.
Q_QA9 | Lists Non-Clinical Case numbers (where the column GTT_RPT_AGG_CASE.CASETYPE !='C')
Q_QA13 | Summary of Unlocked Cases.
Q_QA14 | Cases with Missing Assessment.

### 6.2.6 Lexical Parameters

The following lexical parameter code is available in the pkg_agg_rpt package.

1. Include only HCP cases in summary tabulation: For value 1 (YES/HCP, AND ct.casemedicallyconfirmflag =Y), only medically confirmed cases are shown.

          IF PN_HCP_ONLY = 1 THEN
             GL_ST_FILTER_HCP:= ' AND ct.casemedicallyconfirmflag ='Y';
          ELSE
             GL_ST_FILTER_HCP:= ' ';
          END IF;

   The variable GL_ST_FILTER_HCP is used in the Data model of the PMAR Summary Tabulation sections to filter HCP cases.

2. Include Follow-up cases from summary tabulations: To filter out Follow-up cases in summary tabulation sections.

           IF PN_INC_FOLLOWUP = 1 THEN
             GL_ST_EXCLUDE_FOLLOWUP  := ' ';
           ELSE
             GL_ST_EXCLUDE_FOLLOWUP  := ' AND ct.psurfollowupflag ='N';
           END IF;

3. Exclude Non-Serious cases from summary tabulations: If the report parameter Exclude non serious cases from summary tabulations is set to Y, Grouping and Counts based on Non-Serious events that are part of serious cases are still printed. Only Non-serious cases and corresponding events are ignored based on the parameter value of Y.

           IF PN_EXC_NS_ST = 1 THEN
             GL_ST_FILTER_SERIOUS      := ' AND ct.caseseriousflag = 'Y'; --' AND ct.eventseriousflag ='Y';
           ELSE
             GL_ST_FILTER_SERIOUS      := ' ';
           END IF;

4. List cases in the line listing under SOC for each diagnosis: Line Listing-> List Cases only once, under the primary event and List Cases under all events, details under the primary event.

   Table 6-5 List cases in the line listing under SOC for each diagnosis

   GL_LL_MAIN_NONPRI_CASEREF | PMAR main line listing section uses this variable.
   GL_LL_ADHOC1_NONPRI_CASEREF | PMAR Adhoc1 line listing section uses this variable.
   GL_LL_ADHOC2_NONPRI_CASEREF | PMAR Adhoc2 line listing section uses this variable.
   GL_LL_ADHOC3_NONPRI_CASEREF | PMAR Adhoc3 line listing section uses the variable.
   GL_LL_ADHOC4_NONPRI_CASEREF | PMAR Adhoc4 line listing section uses the variable.
   GL_LL_PRI_CASESOC_ONLY | To print case details under primary case SOC.
   GL_LL_DSUR_PRI_CASESOC_ONLY | DSUR reports.
   GL_LL_DSURMAIN_NONPRI_CASEREF | DSUR reports.
   L_LL_DSURDTH_NONPRI_CASEREF | DSUR reports.

   Code snippet:

           IF PN_LL_SOC = 1 THEN
             GL_LL_MAIN_NONPRI_CASEREF         := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g4mainsoc) ';
             GL_LL_ADHOC1_NONPRI_CASEREF       := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g4adhocsoc) ';
             GL_LL_ADHOC2_NONPRI_CASEREF       := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g4adhoc2soc) ';
             GL_LL_ADHOC3_NONPRI_CASEREF       := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g4adhoc3soc) ';
             GL_LL_ADHOC4_NONPRI_CASEREF       := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g4adhoc4soc) ';
             GL_LL_PRI_CASESOC_ONLY            := ' ';
             GL_LL_DSUR_PRI_CASESOC_ONLY       := ' ';
             GL_LL_DSURMAIN_NONPRI_CASEREF     := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g5asrsoc) ';
             GL_LL_DSURDTH_NONPRI_CASEREF      := '  AND EXISTS (SELECT 1 FROM gtt_rpt_agg_event pdt WHERE pdt.reg_report_id  = pc.reg_report_id AND pdt.case_id = pc.case_id AND pdt.soc  = :g12asrsoc) ';
           ELSE
             GL_LL_MAIN_NONPRI_CASEREF         := '  AND pc.primarycasesoc = :g4mainsoc ';
             GL_LL_ADHOC1_NONPRI_CASEREF       := ' AND pc.primarycasesoc = :g4adhocsoc ';
             GL_LL_ADHOC2_NONPRI_CASEREF       := '  AND pc.primarycasesoc = :g4adhoc2soc ';
             GL_LL_ADHOC3_NONPRI_CASEREF       := '  AND pc.primarycasesoc = :g4adhoc3soc ';
             GL_LL_ADHOC4_NONPRI_CASEREF       := '  AND pc.primarycasesoc = :g4adhoc4soc ';
             GL_LL_PRI_CASESOC_ONLY            := '  AND pc.primarycasesoc = pe.soc ';
             GL_LL_DSUR_PRI_CASESOC_ONLY       := '  AND pc.primarycasesoc = pe.soc ';
             GL_LL_DSURMAIN_NONPRI_CASEREF     := '  AND pc.primarycasesoc = :g5asrsoc ';
             GL_LL_DSURDTH_NONPRI_CASEREF      := '  AND pc.primarycasesoc = :g12asrsoc ';
           END IF;

5. Print Serious Adverse Events or Reactions: Filters out related events.

       IF PN_SAR_SAE = 1 THEN
             GL_SAR_SAE_PBRER_COND := ' AND ct.eventrptrelatedcode = 'Y';
             GL_SAR_SAE_DSUR_COND  := ' AND (ct.eventcorelatedcode = 'Y' OR ct.eventrptrelatedcode = 'Y') ';
             GL_SAR_SAE_DSUR_LL    := ' AND (pet.eventcorelatedcode = 'Y' OR pet.eventrptrelatedcode = 'Y') ';
           ELSE
             GL_SAR_SAE_PBRER_COND := ' ';
             GL_SAR_SAE_DSUR_COND  := ' ';
             GL_SAR_SAE_DSUR_LL    := ' ';
           END IF;