| Oracle® Argus Safety BI Publisher Periodic Reporting Extensibility Guide Release 8.0 E56916-02 |
|
 Previous |
 Next |
| Oracle® Argus Safety BI Publisher Periodic Reporting Extensibility Guide Release 8.0 E56916-02 |
|
Previous |
Next |
This chapter describes the options available for extending an out-of-the-box BI Publisher Periodic report. It contains the following sections:
|
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
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 |
|
|
Packages |
There are 3 packages:
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:
It has INSERT and SELECT access to:
|
|
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). |
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. |
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-2 displays the structure of an out-of-the-box user exit.
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>
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.
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.
To add new columns to GTT and RM tables:
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.
Add the new columns to the replica table in the custom schema.
Grant the new table access to the out-of-the-box BIP schema.
Create a synonym for this object (public synonym). The BIP schema can access this new object.
Modify the user exit package corresponding to this GTT to fill in the new custom table and logistics to load the additional two columns.
Follow the same procedure for filling custom tables. You do not need to replicate a table from the BIP schema.
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:
Create the view in the custom schema.
Grant BIP schema access to the view.
Create a synonym for the view (public synonym).
You can use this view either in the BIP schema or in the BI data model.
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.
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.
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.
|
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:
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.
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.
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.
Let us consider the DSUR Main summary tabulation data structure to explain the naming conventions and drug key table functionality.
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.
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.
G3_DRUGKEY is the third group which prints Study and Comparator drugs in a table format.
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).
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.
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>
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.
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. |
The following lexical parameter code is available in the pkg_agg_rpt package.
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.
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;
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;
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;
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;
|
 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|