6 Extending a Flexible Aggregate Report

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.

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.

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>

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.

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.

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.

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.

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.

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.

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.

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>
   

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.

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;