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

   Value	Description
   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.eventcorelatedcode = 'Y' OR
   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;