Jaspersoft Studio 6.9.0 "Error generating report." with "socket write error" in text of error.

I'm sorry - I don't know what my anonymized JDBC URL is...but my JRXML code is below.  Maybe there's something really simple that I'm overlooking...

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="invoiceable_items" pageWidth="1234" pageHeight="954" orientation="Landscape" columnWidth="1234" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" whenResourceMissingType="Empty" uuid="7f369dea-094c-458f-97fb-6f6d351dea7f">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="net.sf.jasperreports.print.keep.full.text" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="OnCore Test2"/>
    <parameter name="fromDate" class="java.util.Date"/>
    <parameter name="thruDate" class="java.util.Date"/>
    <parameter name="mgmtGroup" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="oncologyGroup" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="department" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="protocol" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="ProtocolInclude" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="ProtocolStatus" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="Unplanned" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="Additional" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="VisitStatus" class="java.util.Collection">
        <defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
    </parameter>
    <parameter name="userContactId" class="java.lang.String" isForPrompting="false">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <queryString>
        <![CDATA[select 
    event_data.type,
    event_data.protocol_id,
    event_data.item,
    event_data.milestone,
    event_data.sequence_number,
    event_data.initials,
    event_data.visit_status,
    event_data.occurred_date,
    event_data.direct,
    event_data.total_cost,
    event_data.withhold,
    event_data.withholding_pct,
    event_data.sponsor_name,
    event_data.indirect_flag,
    event_data.trigger_type,
    event_data.reoccurring,
    event_data.exclusion,
    event_data.comments,
    NVL(event_data.comments, event_data.eval_notes) as more_info,
    event_data.status,
    event_data.unplanned_visit,
    event_data.additional,
    event_data.eval_notes,
    pcl_info.protocol_no, 
    pcl_info.departments,
    pcl_info.management_groups, 
    pcl_info.oncology_groups, 
    pcl_info.primary_sponsor, 
    pcl_info.secondary_sponsors,
    pcl_info.indirect_cumulative,
    pcl_info.protocol_indirect,
    pcl_info.subject_indirect,
    pcl_info.milestone_indirect,
    pcl_info.not_indirect_multiplier,
    pcl_info.indirect_multiplier,
    pcl_privileges.has_invoiceable_items,
    pcl_privileges.has_visit_variations,
    pcl_privileges.has_subject_identification
from (

select 
    type, 
    protocol_id, 
    item,
    NULL milestone,
    NULL sequence_number,
    NULL initials,
    NULL visit_status,
    occurred_date,
    direct,
    total_cost,
    withhold,
    withholding_pct,
    sponsor_name,
    indirect_flag,
    trigger_type,
    reoccurring,
    case when type = 'Protocol Related' and trigger_type is not null and reoccurring = 'Y' then 'Include'
    when type = 'Protocol Related' and (reoccurring is null or reoccurring = 'N') then 'Include'
    when type = 'Protocol Related' and trigger_type is null and reoccurring = 'Y' then 'Exclude'
    end as exclusion,
    comments,
    status,
    NULL unplanned_visit,
    NULL additional,
    NULL eval_notes
from
(
SELECT
  'Protocol Related' type,
  a.protocol_id,
  a.budget_event_description item,
  a.occurred_date,
  NVL(a.sponsor_cost,0) direct,
  null total_cost,
  a.withhold,
  nvl(a.withholding_pct,0) withholding_pct,
  (
    SELECT
      sponsor_name
    FROM
      smrs_pcl_sponsor sps,
      smrs_sponsor ss
    WHERE
      sps.pcl_sponsor_id = a.sponsor
    AND sps.sponsor      = ss.sponsor
  )
  sponsor_name,
  a.indirect_flag,
  a.trigger_type,
  b.reoccurring,
  b.comments,
  c.status,
  row_number () over (partition by b.pcl_budget_event_id order by c.status_date desc, c.created_date desc) rn
FROM
  sv_pcl_invoicables a
  left outer join
  sv_fin_pcl_events b
  on a.protocol_id = b.protocol_id
  and a.budget_event_id = b.budget_event_id
  and a.pcl_budget_event_id = b.pcl_budget_event_id
  left outer join
  sv_pcl_status c
  on a.protocol_id = c.protocol_id
  where 1=1
  and a.na_for_invoice is null or a.na_for_invoice = 0

)
where (rn = 1)

  UNION ALL

  SELECT
  'Subject Milestone' type,
  protocol_id,
  NULL item,
  (visit_description) milestone,
  sequence_number,
  initials,
  visit_status,
  TO_CHAR(visit_date, 'MM/DD/YYYY') occurred_date,
  NVL(sponsor_cost, 0) direct,
  NULL total_cost,
  withhold,
  NVL(withholding_pct, 0) withholding_pct,
  sponsor_name,
  'N' indirect_flag,
  NULL trigger_type, 
  NULL reoccurring,
  NULL exclusion,
  NULL comments,
  NULL status,
  NULL unplanned_visit,
  NULL additional,
  NULL eval_notes
FROM

  (
    SELECT
      protocol_id,
      pcl_milestone_id,
      (
      CASE
        WHEN visit_description IS NULL
        THEN NULL
        WHEN (
            SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no) IS NOT NULL
        THEN 'Arm ' ||
          (
            SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no
          )
          || ': ' || visit_description
        ELSE visit_description
      END ) visit_description,
      na_for_invoice,
      withhold,
      withholding_pct,
      visit_status,
      visit_date,
      cost,
      sponsor,
      sponsor_cost,
      (
        SELECT
          sponsor_name
        FROM
          smrs_pcl_sponsor sps,
          smrs_sponsor ss
        WHERE
          sps.pcl_sponsor_id = a.sponsor
        AND sps.sponsor      = ss.sponsor
      )
      sponsor_name,
      protocol_subject_id,
      sequence_number,
      initials,
      TO_CHAR(date_submitted, 'MM/DD/YYYY') submitted_date
    FROM
      (
        SELECT
          vst.protocol_id,
          vst.pcl_milestone_id,
          vst.arm_no,
          vst.visit_id,
          vst.visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when vst.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when vst.visit_date is not null then 'Occurred'
          end as visit_status,
          vst.visit_date,
          vst.cost,
          vst.sd_study_spec_id,
          vst.budget_version_no,
          vst.na_for_invoice,
          vst.withhold,
          vst.withholding_pct,
          vst.sponsor,
          vst.sponsor_cost,
          NULL sponsor_due,
          vst.protocol_subject_id,
          vst.sequence_number,
          vst.initials,
          vst.uncertified_item,
          vst.date_completed,
          vst.date_monitored,
          vst.date_submitted
        FROM
          sv_pcs_visit_invoicables vst
          left outer join
          sv_sub_calendar cal
          on vst.visit_id = cal.sd_pcs_tracking_id
          and vst.protocol_subject_id = cal.protocol_subject_id
        UNION
        SELECT
          ext.protocol_id,
          ext.pcl_milestone_id,
          ext.arm_no,
          ext.visit_id,
          ext.visit_string visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when ext.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when ext.visit_date is not null then 'Occurred'
          end as visit_status,
          ext.visit_date,
          ext.cost,
          ext.sd_study_spec_id,
          ext.budget_version_no,
          ext.na_for_invoice,
          ext.withhold,
          ext.withholding_pct,
          ext.sponsor,
          ext.sponsor_cost,
          NULL sponsor_due,
          ext.protocol_subject_id,
          ext.sequence_number,
          get_subject_name(ext.protocol_subject_id, 'Y') initials,
          ext.uncertified_item,
          ext.date_completed,
          ext.date_monitored,
          ext.date_submitted
        FROM
          sv_pcs_extended_invoicables ext          
          left outer join
          sv_sub_calendar cal
          on ext.visit_id = cal.sd_pcs_tracking_id
          and ext.protocol_subject_id = cal.protocol_subject_id
        UNION
        SELECT
          ext.protocol_id,
          ext.pcl_milestone_id,
          ext.arm_no,
          ext.visit_id,
          ext.visit_string visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when ext.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when ext.visit_date is not null then 'Occurred'
          end as visit_status,
          ext.visit_date,
          ext.cost,
          ext.sd_study_spec_id,
          ext.budget_version_no,
          ext.na_for_invoice,
          ext.withhold,
          ext.withholding_pct,
          ext.sponsor,
          ext.sponsor_cost,
          NULL sponsor_due,
          ext.protocol_subject_id,
          ext.sequence_number,
          get_subject_name(ext.protocol_subject_id, 'Y') initials,
          ext.uncertified_item,
          ext.date_completed,
          ext.date_monitored,
          ext.date_submitted
        FROM
          sv_pcs_oem_invoicables ext
          left outer join
          sv_sub_calendar cal
          on ext.visit_id = cal.sd_pcs_tracking_id
          and ext.protocol_subject_id = cal.protocol_subject_id
        UNION
        SELECT
          rev.protocol_id,
          rev.milestone_id,
          rev.arm_no,
          rev.visit_id,
          rev.visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when rev.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when rev.visit_date is not null then 'Occurred'
          end as visit_status,
          rev.visit_date,
          rev.cost,
          rev.sd_study_spec_id,
          rev.budget_version_no,
          rev.na_for_invoice,
          rev.withhold,
          rev.withholding_pct,
          rev.sponsor,
          rev.sponsor_cost,
          rev.sponsor_due,
          rev.protocol_subject_id,
          rev.sequence_number,
          rev.initials,
          rev.uncertified_item,
          rev.date_completed,
          rev.date_monitored,
          rev.date_submitted
        FROM
          sv_invoice_reversals rev
          left outer join
          sv_sub_calendar cal
          on rev.visit_id = cal.sd_pcs_tracking_id
          and rev.protocol_subject_id = cal.protocol_subject_id
        WHERE
          milestone_id IS NOT NULL
      )
      a,
      (
        SELECT
          NULL spon,
          NULL milestone_id,
          'Y' include_na
        FROM
          dual
      )
      b
    WHERE
      1                       = 1
    AND (b.spon              IS NULL
    OR NVL(a.sponsor, b.spon) = b.spon)
    AND (b.milestone_id      IS NULL
    OR a.pcl_milestone_id    IN
      (
        SELECT pcl_milestone_id FROM sv_pcs_milestone_ref WHERE milestone_id_ref = b.milestone_id
      ))
    AND (NVL(b.include_na,'N')       = 'Y'
    OR NVL(a.na_for_invoice, 0)      = 0)
    AND (NVL(visit_description,' ') != 'Screen Failed')
    UNION ALL
    SELECT
      protocol_id,
      pcl_milestone_id,
      visit_description,
      na_for_invoice,
      withhold,
      withholding_pct,
      visit_status,
      visit_date,
      cost,
      sponsor,
      sponsor_cost,
      (
        SELECT
          sponsor_name
        FROM
          smrs_pcl_sponsor sps,
          smrs_sponsor ss
        WHERE
          sps.pcl_sponsor_id = a.sponsor
        AND sps.sponsor      = ss.sponsor
      )
      sponsor_name,
      protocol_subject_id,
      sequence_number,
      initials,
      submitted_date
    FROM
      (
        SELECT
          evt.protocol_id,
          pcl_milestone_id,
          visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when evt.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when evt.visit_date is not null then 'Occurred'
          end as visit_status,
          evt.visit_date,
          NULL sae_no,
          NULL osr_id,
          cost,
          na_for_invoice,
          withhold,
          withholding_pct,
          sponsor,
          sponsor_cost,
          evt.protocol_subject_id,
          sequence_number,
          initials,
          NULL submitted_date
        FROM
          sv_pcs_event_invoicables evt
          left outer join
          sv_sub_calendar cal
          on evt.protocol_subject_id = cal.protocol_subject_id
          and evt.visit_date = cal.visit_date
        WHERE
          evt.visit_description   != 'Screen Failed'
        AND TRUNC(evt.billed_date) < TRUNC(NVL(
          (
            SELECT
              MIN(start_date)
            FROM
              onc_pcl_invoicing_rule rl
            WHERE
              rl.protocol_id = evt.protocol_id
            AND rl.rule_type = 'NB'
            AND (rl.sponsor IS NULL
            OR rl.sponsor    = evt.sponsor)
          )
          , evt.billed_date + 1))
        UNION
        SELECT
          evt.protocol_id,
          pcl_milestone_id,
          visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when evt.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when evt.visit_date is not null then 'Occurred'
          end as visit_status,
          evt.visit_date,
          NULL sae_no,
          NULL osr_id,
          cost,
          na_for_invoice,
          withhold,
          withholding_pct,
          sponsor,
          sponsor_cost,
          evt.protocol_subject_id,
          sequence_number,
          get_subject_name(evt.protocol_subject_id, 'Y') initials,
          NULL submitted_date
        FROM
          sv_invoicable_screen_failures evt
          left outer join
          sv_sub_calendar cal
          on evt.protocol_subject_id = cal.protocol_subject_id
          and evt.visit_date = cal.visit_date
        WHERE
          TRUNC(evt.billed_date) < TRUNC(NVL(
          (
            SELECT
              MIN(start_date)
            FROM
              onc_pcl_invoicing_rule rl
            WHERE
              rl.protocol_id = evt.protocol_id
            AND rl.rule_type = 'NB'
            AND (rl.sponsor IS NULL
            OR rl.sponsor    = evt.sponsor)
          )
          , evt.billed_date + 1))
        UNION
        SELECT
          protocol_id,
          pcl_milestone_id,
          visit_description,
          case when cal.missed_flag = 'Y' then 'Missed'
          when saes.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when saes.visit_date is not null then 'Occurred'
          end as visit_status,
          saes.visit_date,
          sae_no,
          NULL osr_id,
          cost,
          na_for_invoice,
          withhold,
          withholding_pct,
          sponsor,
          sponsor_cost,
          saes.protocol_subject_id,
          sequence_number,
          initials,
          NULL submitted_date
        FROM
          sv_invoicable_saes saes
          left outer join
          sv_sub_calendar cal
          on saes.protocol_subject_id = cal.protocol_subject_id
          and saes.visit_date = cal.visit_date
        UNION
        SELECT
          protocol_id,
          pcl_milestone_id,
          milestone,
          case when cal.missed_flag = 'Y' then 'Missed'
          when osrs.visit_date is null then 'Missed'
          when cal.na_flag = 'Y' then 'NA'
          when cal.visit_status = 'Planned' then 'Planned'
          when osrs.visit_date is not null then 'Occurred'
          end as visit_status,
          osrs.visit_date,
          NULL sae_no,
          osr_id,
          cost,
          na_for_invoice,
          withhold,
          withholding_pct,
          sponsor,
          sponsor_cost,
          osrs.protocol_subject_id,
          sequence_number,
          initials,
          NULL submitted_date
        FROM
          sv_invoicable_osrs osrs
          left outer join
          sv_sub_calendar cal
          on osrs.protocol_subject_id = cal.protocol_subject_id
          and osrs.visit_date = cal.visit_date
      )
      a
      where nvl(na_for_invoice, 0) = 0
  )

  UNION ALL

  SELECT
  'Pass Through' type,
  a.protocol_id protocol_id,
  budget_event_description item,
  (
  CASE
    WHEN (
        SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no) IS NOT NULL
    THEN 'Arm '||
      (
        SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no
      )
      || ': ' || visit_description
    ELSE visit_description
  END) milestone,
  sequence_number,
  initials,
  NULL visit_status,
  tracking_date occurred_date,
  NVL(ptec.visit_direct, 0) direct,
  NVL(ptec.visit_total, 0) total_cost,
  --sponsor,--id of the sponsor
  withhold,
  NVL(withholding_pct, 0) withholding_pct,
  --na_for_invoice,
  (
    SELECT
      sponsor_name
    FROM
      smrs_pcl_sponsor sps,
      smrs_sponsor ss
    WHERE
      sps.pcl_sponsor_id = a.sponsor
    AND sps.sponsor      = ss.sponsor
  )
  sponsor_name,
  'N' indirect_flag,
  NULL trigger_type, 
  NULL reoccurring,
  NULL exclusion,
  NULL comments,
  NULL status,
  NULL unplanned_visit,
  NULL additional,
  NULL eval_notes
FROM
  (
    SELECT DISTINCT
      pcl_evaluation_id,
      pcl_budget_event_id,
      protocol_id,
      tracking_date eval_date,
      TO_CHAR(tracking_date, 'MM/DD/YYYY') tracking_date,
      tracking_id,
      a.sd_ss_tx_cycle_visit_id,
      visit_eval_id,
      a.arm_no,
      arm_code arm,
      na_for_invoice,
      a.sd_study_spec_id,
      a.budget_version_no,
      (
        SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id
      )
      calendar_version_no,
      (
        SELECT
          TO_CHAR(version_date, 'MM/DD/YYYY')
        FROM
          onc_pcl_budget_version
        WHERE
          sd_study_spec_id    = a.sd_study_spec_id
        AND budget_version_no = a.budget_version_no
      )
      budget_version_date,
      NULL sponsor_cost,
      NULL sponsor_due,
      sponsor,
      withhold,
      withholding_pct,
      (
        SELECT
          sponsor_name
        FROM
          smrs_pcl_sponsor sps,
          smrs_sponsor ss
        WHERE
          sps.pcl_sponsor_id = a.sponsor
        AND sps.sponsor      = ss.sponsor
      )
      sponsor_name,
      NVL(budget_event_description, clinical_procedure) budget_event_description,
      visit_description,
      protocol_subject_id,
      sequence_number,
      get_subject_name(a.protocol_subject_id, 'Y') initials,
      to_number(NULL) uncertified_item
    FROM
      sv_pcs_procedure_invoicables a
    UNION ALL
    SELECT DISTINCT
      a.pcl_evaluation_id,
      a.pcl_budget_event_id,
      a.protocol_id,
      visit_date eval_date,
      TO_CHAR(a.visit_date, 'MM/DD/YYYY') tracking_date,
      a.visit_id tracking_id,
      NULL sd_ss_tx_cycle_visit_id,
      visit_eval_id,
      a.arm_no,
      (
        SELECT arm_code FROM smrs_pcl_step_arm WHERE arm_no = a.arm_no
      )
      arm,
      na_for_invoice,
      a.sd_study_spec_id,
      a.budget_version_no,
      (
        SELECT version_no FROM sd_study_spec WHERE sd_study_spec_id = a.sd_study_spec_id
      )
      calendar_version_no,
      (
        SELECT
          TO_CHAR(version_date, 'MM/DD/YYYY')
        FROM
          onc_pcl_budget_version
        WHERE
          sd_study_spec_id    = a.sd_study_spec_id
        AND budget_version_no = a.budget_version_no
      )
      budget_version_date,
      a.sponsor_cost,
      a.sponsor_due,
      a.sponsor,
      a.withhold,
      a.withholding_pct,
      (
        SELECT
          sponsor_name
        FROM
          smrs_pcl_sponsor sps,
          smrs_sponsor ss
        WHERE
          sps.pcl_sponsor_id = a.sponsor
        AND sps.sponsor      = ss.sponsor
      )
      sponsor_name,
      DECODE(a.pcl_budget_event_id, NULL, NVL(c.clinical_procedure, d.event_description),
      (
        SELECT
          budget_event_description
        FROM
          onc_budget_event
        WHERE
          budget_event_id = e.budget_event_id
      )
      ) budget_event_description,
      a.visit_description,
      a.protocol_subject_id,
      a.sequence_number,
      a.initials,
      uncertified_item
    FROM
      sv_invoice_reversals a,
      onc_pcl_evaluation b,
      sd_study_spec_evaluation c,
      pf_lab_category d,
      onc_pcl_budget_event e
    WHERE
      a.milestone_id           IS NULL
    AND a.pcl_evaluation_id     = b.pcl_evaluation_id
    AND b.sd_study_spec_eval_id = c.sd_study_spec_eval_id
    AND c.lab_category_id       = d.lab_category_id(+)
    AND a.pcl_budget_event_id   = e.pcl_budget_event_id(+)
  )
  a
LEFT JOIN
  (
    SELECT /*+ RULE */
      t.pcl_evaluation_id,
      t.pcl_budget_event_id,
      t.sd_ss_tx_cycle_visit_id,
      t.arm_no,
      (t.pcl_evaluation_id || ':' || NVL(t.pcl_budget_event_id, 0) || ':' || t.sd_ss_tx_cycle_visit_id || ':' || NVL(
      t.arm_no, 0)) KEY,
      SUM(t.invoice_cost * t.pk_count) visit_direct,
      SUM(t.invoice_cost * t.pk_count * DECODE(fp.budget_only_indirect, 'Y', fp.net_rate_without_indirect, DECODE(
      t.indirect_flag, 'Y', fp.net_rate_with_indirect, fp.net_rate_without_indirect))) visit_total
    FROM
      sv_pcl_evaluation_visit_costs t,
      sv_fin_parameters fp,
      sv_evaluation_pass_thru_visits pt
    WHERE
      fp.protocol_id                  = t.protocol_id
    AND t.invoice_cost                > 0
    AND t.pcl_evaluation_id           = pt.pcl_evaluation_id
    AND NVL(t.pcl_budget_event_id, 0) = NVL(pt.pcl_budget_event_id, 0)
    AND (pt.visit_id                 IS NULL
    OR (t.sd_ss_tx_cycle_visit_id     = pt.visit_id
    AND NVL(t.arm_no, 0)              = NVL(pt.arm_no, 0)))
    GROUP BY
      t.pcl_evaluation_id,
      t.pcl_budget_event_id,
      t.study_spec_id,
      t.sd_ss_tx_cycle_visit_id,
      t.arm_no
  )
  ptec
ON
  ptec.key = a.pcl_evaluation_id || ':' || NVL(a.pcl_budget_event_id, 0) || ':' || a.sd_ss_tx_cycle_visit_id || ':' ||
  NVL(a.arm_no, 0)
WHERE (a.na_for_invoice IS NULL
OR a.na_for_invoice      = 0)

  UNION ALL

select  
    st1.type,
    st1.protocol_id, 
    st1.item,
    st1.milestone,
    st1.sequence_number,
    st1.initials,
    st1.visit_status,
    st1.occurred_date,
    st1.direct,
    st1.total_cost,
    st1.withhold,
    st1.withholding_pct,
    st1.sponsor_name,
    st1.indirect_flag,
    st1.trigger_type,
    st1.reoccurring,
    NULL exclusion,
    st1.comments,
    st1.status,
    st1.unplanned_visit,
    vv.additional,
    vv.eval_notes

from (

  SELECT
  'Visit Variation' type,
  a.protocol_id,
  LISTAGG((case when evaluation_detail is not null then evaluation2 || ': ' || evaluation_detail else evaluation2 end), '; ') within group (order by eval_id)
  item,
  (case when arm_code is not null then 'Arm ' || arm_code || ': ' || visit_string else visit_string end)
  milestone,
  a.sequence_number,
  a.initials,  
  case when a.missed_visit = 'Y' then 'Missed' 
  when a.na_visit = 'Y' then 'NA' 
  end as visit_status,
  nvl(a.visit_date, (case when missed_visit = 'Y' then 'Missed' when na_visit = 'Y' then 'NA' end)) occurred_date,
  sum(
    case when (nvl(missed_visit, 'N') = 'Y' or nvl(na_visit, 'N') = 'Y' or billed_date is null) then
      (case when nvl(is_soc, 0) = 1 or nvl(is_pass_thru, 0) = 1 then 0 else nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * eval_count_sum end) * (case when nvl(unplanned_visit, 'N') = 'Y' then 1 else -1 end)
    else
      (case when not (nvl(additional, 0) = 1 and nvl(soc_flag, 'N') = 'Y') then
        (
          case when nvl(additional, 0) = 1 then nvl(nvl(eval_cost, non_scheduled_eval_cost),0)
            when missed_count is not null and missed_count > 0 then nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * missed_count * -1
            when nvl(is_soc, 0) = 1 and nvl(soc_flag, 'N') = 'N' then nvl(nvl(eval_cost, non_scheduled_eval_cost),0)
            else nvl(nvl(eval_cost, non_scheduled_eval_cost),0) * -1
          end
        )
      else 0 end)
    end
    ) direct,
  sum(
    case when (nvl(missed_visit, 'N') = 'Y' or nvl(na_visit, 'N') = 'Y' or billed_date is null) then
      (case when nvl(is_soc, 0) = 1 or nvl(is_pass_thru, 0) = 1 then 0 else nvl(nvl(eval_due, non_scheduled_eval_due),0) * eval_count_sum end) * (case when nvl(unplanned_visit, 'N') = 'Y' then 1 else -1 end)
    else
      (case when not (nvl(additional, 0) = 1 and nvl(soc_flag, 'N') = 'Y') then
        (
          case when nvl(additional, 0) = 1 then nvl(nvl(eval_due, non_scheduled_eval_due),0)
            when missed_count is not null and missed_count > 0 then nvl(nvl(eval_due, non_scheduled_eval_due),0) * missed_count * -1
            when nvl(is_soc, 0) = 1 and nvl(soc_flag, 'N') = 'N' then nvl(nvl(eval_due, non_scheduled_eval_due),0)
            else nvl(nvl(eval_due, non_scheduled_eval_due),0) * -1
          end
        )
      else 0 end)
    end
    ) total_cost,
  'N' withhold,
  0 withholding_pct,
    (
        SELECT
          sponsor_name
        FROM
          smrs_pcl_sponsor sps,
          smrs_sponsor ss
        WHERE
          sps.pcl_sponsor_id = a.eval_sponsor
        AND sps.sponsor      = ss.sponsor
    )
  sponsor_name,
  'N' indirect_flag,
  null trigger_type, 
  NULL reoccurring,
  NULL comments,
  NULL status,
  a.unplanned_visit,
  a.evaluation_,
  a.protocol_subject_id
FROM
--original query for visit variations tab of financials console
(

SELECT
  row_number() over (ORDER BY
  sv.visit_date) row_num,
  sp.protocol_id protocol_id,
  upper(SUBSTR(ss.subject_first_name,1,1) || SUBSTR(ss.subject_middle_name,1,1) || SUBSTR(ss.subject_last_name,1,1))
  initials,
  sv.sd_pcs_tracking_id,
  NVL(sv.missed_flag,'N') missed_visit,
  NVL(sv.na_flag,'N') na_visit,
  sv.billed_date,
  DECODE(vs.description, 'UnPlanned', 'Y', 'N') unplanned_visit,
  (
    SELECT DISTINCT
      1
    FROM
      sv_invoice_reversals
    WHERE
      protocol_id = sp.protocol_id
    AND visit_id  = sv.sd_pcs_tracking_id
  )
  invoiced_uncertified,
  DECODE(ve.additional, 'Y', 1, 0) additional,
  ve.sd_pcs_tracking_evaluation_id evaluation_,
  get_evaluation_name(NULL, ve.sd_pcs_tracking_evaluation_id) evaluation2,
  DECODE(ed.visit_eval_id, NULL, NULL,
  (
    SELECT
      be.budget_event_description
    FROM
      onc_pcl_budget_event pbe,
      onc_budget_event be
    WHERE
      be.budget_event_id        = pbe.budget_event_id
    AND pbe.pcl_budget_event_id = ed.pcl_budget_event_id
  )
  ) evaluation_detail,
  TO_CHAR(ve.evaluation_date,'MM/DD/YYYY') evaluation_date,
  ve.evaluation eval_id,
  DECODE(ph.code,'ONT',sv.arm_no,'') arm_no,
  DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) soc_flag,
  DECODE(ed.visit_eval_id, NULL, ve.missed_flag, ed.missed_flag) missed_flag,
  DECODE(ed.visit_eval_id, NULL, ve.not_applicable, ed.not_applicable) not_applicable,
  DECODE(vs.description, 'UnPlanned', sv.visit_description, NVL(
  (
    SELECT
      revised_visit_string
    FROM
      sd_ss_tx_cycle_visit
    WHERE
      sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id
  )
  , sv.visit_string)) visit_string,
  DECODE(ed.visit_eval_id, NULL, ve.missed_count, ed.missed_count) missed_count,
  (
    SELECT
      1
    FROM
      dual
    WHERE
      EXISTS
      (
        SELECT
          1
        FROM
          sv_evaluation_soc_visits so
        WHERE
          so.sd_study_spec_eval_id       = ve.evaluation
        AND NVL(ve.additional, 'N')      = 'N'
        AND so.budget_version_no         = sv.budget_version_no
        AND (so.visit_id                IS NULL
        OR (NVL(so.arm_no,0)             = NVL(sv.arm_no,0)
        AND so.visit_id                  = sv.sd_ss_tx_cycle_visit_id))
        AND (ed.visit_eval_id           IS NULL
        OR so.pcl_budget_event_id        = ed.pcl_budget_event_id
        AND ed.pcl_budget_event_item_id IS NULL)
      )
  )
  is_soc,
  (
    SELECT
      1
    FROM
      dual
    WHERE
      EXISTS
      (
        SELECT /*+ RULE */
          1
        FROM
          sv_evaluation_pass_thru_visits so
        WHERE
          so.sd_study_spec_eval_id       = ve.evaluation
        AND NVL(ve.additional, 'N')      = 'N'
        AND so.budget_version_no         = sv.budget_version_no
        AND (so.visit_id                IS NULL
        OR (NVL(so.arm_no,0)             = NVL(sv.arm_no,0)
        AND so.visit_id                  = sv.sd_ss_tx_cycle_visit_id))
        AND (ed.visit_eval_id           IS NULL
        OR so.pcl_budget_event_id        = ed.pcl_budget_event_id
        AND ed.pcl_budget_event_item_id IS NULL)
      )
  )
  is_pass_thru,
  ve.sd_pcs_tracking_evaluation_id,
  ed.pcl_budget_event_id,
  get_visit_eval_variation_notes(ve.sd_pcs_tracking_evaluation_id, ed.visit_eval_detail_id) eval_notes,
  CASE
    WHEN sv.missed_flag = 'Y'
    OR sv.na_flag       = 'Y'
    THEN sv.verified_flag
    ELSE DECODE(ed.visit_eval_id, NULL, ve.verified_flag, ed.verified_flag)
  END verified_flag,
  CASE
    WHEN sv.missed_flag = 'Y'
    OR sv.na_flag       = 'Y'
    THEN sv.invoice_flag
    ELSE DECODE(ed.visit_eval_id, NULL, ve.invoice_flag, ed.invoice_flag)
  END invoice_flag,
  CASE
    WHEN (sv.missed_flag = 'Y'
    OR sv.na_flag        = 'Y')
    AND (
        SELECT
          COUNT(*)
        FROM
          sd_pcs_visit_evaluation eval,
          sd_pcs_visit_eval_detail det
        WHERE
          eval.sd_pcs_tracking_id                                                = sv.sd_pcs_tracking_id
        AND eval.sd_pcs_tracking_evaluation_id                                   = det.visit_eval_id(+)
        AND DECODE(det.visit_eval_id, NULL, eval.invoice_flag, det.invoice_flag) = 'Y' ) > 0
    THEN 'Y'
    ELSE 'N'
  END partial_invoice_flag,
  CASE
    WHEN (ve.additional = 'Y'
    AND ve.evaluation  IS NULL)
    THEN 0
    ELSE
      (
        SELECT
          SUM (NVL (visit_cost, 0))
        FROM
          sv_pcl_evaluation_visit_costs ec
        WHERE
          ec.study_spec_id                 = sv.sd_study_spec_id
        AND ec.sd_study_spec_eval_id       = ve.evaluation
        AND ec.budget_version_no           = sv.budget_version_no
        AND ec.sd_ss_tx_cycle_visit_id     = sv.sd_ss_tx_cycle_visit_id
        AND NVL (ec.arm_no, 0)             = NVL (sv.arm_no, 0)
        AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL,
          ed.pcl_budget_event_id), 0)
      )
  END eval_cost,
  get_subj_visit_eval_pcl_cost(ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id) non_scheduled_eval_cost,
  get_subj_visit_eval_pcl_due(sp.protocol_id, ve.sd_pcs_tracking_evaluation_id, ed.pcl_budget_event_id)
  non_scheduled_eval_due,
  CASE
    WHEN (ve.additional = 'Y'
    AND ve.evaluation  IS NULL)
    THEN 0
    ELSE
      (
        SELECT
          SUM (NVL (ec.visit_cost, 0) * DECODE(ec.indirect_flag, 'Y', par.net_rate_with_indirect,
          par.net_rate_without_indirect))
        FROM
          sv_pcl_evaluation_visit_costs ec
        WHERE
          ec.study_spec_id                 = sv.sd_study_spec_id
        AND ec.sd_study_spec_eval_id       = ve.evaluation
        AND ec.budget_version_no           = sv.budget_version_no
        AND ec.sd_ss_tx_cycle_visit_id     = sv.sd_ss_tx_cycle_visit_id
        AND NVL (ec.arm_no, 0)             = NVL (sv.arm_no, 0)
        AND NVL(ec.pcl_budget_event_id, 0) = NVL(DECODE(ve.alternate_event_id, ed.pcl_budget_event_id, NULL,
          ed.pcl_budget_event_id), 0)
      )
  END eval_due,
  NVL(
  (
    SELECT
      MIN(pk_count)
    FROM
      sv_ss_eval_visits
    WHERE
      sd_study_spec_eval_id     = ve.evaluation
    AND NVL(ve.additional, 'N') = 'N'
    AND sd_ss_tx_cycle_visit_id = sv.sd_ss_tx_cycle_visit_id
    AND study_spec_id           = sv.sd_study_spec_id
  )
  , 1) eval_count_sum,
  (
    SELECT
      sponsor
    FROM
      onc_pcl_evaluation
    WHERE
      sd_study_spec_eval_id = ve.evaluation
    AND budget_version_no   = sv.budget_version_no
  )
  eval_sponsor,
  (
    SELECT
      arm_code
    FROM
      smrs_pcl_step_arm a
    WHERE
      protocol_id = sp.protocol_id
    AND arm_no    = sv.arm_no
  )
  arm_code,
  ps.sequence_number,
  TO_CHAR(sv.visit_date,'MM/DD/YYYY') visit_date,
  ps.protocol_subject_id,
  ph.description phase
FROM
  smrs_protocol sp,
  smrs_subject ss,
  smrs_pcl_cent_subject ps,
  sd_pcs_tracking sv,
  sd_pcs_visit_evaluation ve,
  pf_code ph,
  pf_code vs,
  (
    SELECT
      *
    FROM
      sd_pcs_visit_eval_detail
    WHERE
      pcl_budget_event_id        IS NOT NULL
    AND pcl_budget_event_item_id IS NULL
  )
  ed,
  (
    SELECT
      protocol_id,
      net_rate_with_indirect,
      net_rate_without_indirect
    FROM
      sv_fin_parameters
  )
  par
WHERE
  sp.protocol_id                                                                                                  = par.protocol_id
AND ps.protocol_id                                                                                                = sp.protocol_id
AND ps.subject_no                                                                                                 = ss.subject_no
AND sv.protocol_subject_id                                                                                        = ps.protocol_subject_id
AND ve.sd_pcs_tracking_id                                                                                         = sv.sd_pcs_tracking_id
AND ve.sd_pcs_tracking_evaluation_id                                                                              = ed.visit_eval_id(+)
AND ph.code_id                                                                                                    = sv.phase
AND sv.visit_status                                                                                               = vs.code_id
AND vs.category                                                                                                   = 'VISIT_STATUS'
AND vs.description                                                                                               IN ('Acknowledged','UnPlanned')
AND ( (DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'N'
AND NVL(ve.additional, 'N')                                                                                       = 'N'
AND EXISTS
  (
    SELECT
      1
    FROM
      sv_evaluation_soc_visits so
    WHERE
      so.sd_study_spec_eval_id = ve.evaluation
    AND so.budget_version_no   = sv.budget_version_no
    AND (ed.visit_eval_id     IS NULL
    OR so.pcl_budget_event_id  = ed.pcl_budget_event_id)
    AND (so.visit_id          IS NULL
    OR (NVL(so.arm_no,0)       = NVL(sv.arm_no,0)
    AND so.visit_id            = sv.sd_ss_tx_cycle_visit_id))
  ) )
OR (DECODE(ed.visit_eval_id, NULL, ve.soc_flag, DECODE(ve.alternate_event_id, NULL, ed.soc_flag, ve.soc_flag)) = 'Y'
AND NVL(ve.additional, 'N')                                                                                    = 'N'
AND NOT EXISTS
  (
    SELECT
      1
    FROM
      sv_evaluation_soc_visits so
    WHERE
      so.sd_study_spec_eval_id = ve.evaluation
    AND so.budget_version_no   = sv.budget_version_no
    AND (ed.visit_eval_id     IS NULL
    OR so.pcl_budget_event_id  = ed.pcl_budget_event_id)
    AND (so.visit_id          IS NULL
    OR (NVL(so.arm_no,0)       = NVL(sv.arm_no,0)
    AND so.visit_id            = sv.sd_ss_tx_cycle_visit_id))
  ) )
OR (ed.visit_eval_id         IS NULL
AND (NVL(ve.missed_flag,'N')  = 'Y'
OR NVL(ve.not_applicable,'N') = 'Y'
OR NVL(ve.missed_count,0)     > 0))
OR (ed.visit_eval_id         IS NOT NULL
AND (NVL(ed.missed_flag,'N')  = 'Y'
OR NVL(ed.not_applicable,'N') = 'Y'
OR NVL(ed.missed_count,0)     > 0))
OR NVL(ve.additional,'N')     = 'Y'
OR NVL(sv.missed_flag,'N')    = 'Y'
OR NVL(sv.na_flag,'N')        = 'Y'
OR vs.description             = 'UnPlanned'
OR sv.billed_date            IS NULL )
--exclusions for items that can be invoiced
and nvl(decode(ed.visit_eval_id, null, ve.invoice_flag, ed.invoice_flag), 'N') = 'N' and nvl(sv.invoice_flag,'N') = 'N'
and (nvl(ve.additional,'N') = 'N' or (ve.additional = 'Y' and nvl(ve.soc_flag,'N') = 'N' and nvl(ve.missed_flag,'N') = 'N'))
and sv.billed_date is not null
and (ve.additional = 'Y')
ORDER BY
  initials,
  sv.visit_date,
  sv.sd_pcs_tracking_id,
  evaluation,
  ve.sd_pcs_tracking_evaluation_id,
  evaluation_detail

) a

group by a.protocol_id, a.initials, a.visit_date, a.sd_pcs_tracking_id,
--only group rows that are missed, na, or not billed yet, otherwise use the row_num as a unique identifier to split out all other rows
(case when (nvl(missed_visit,'N') != 'N' or nvl(na_visit, 'N') != 'N' or billed_date is null) then -1 else row_num end),
additional,
unplanned_visit,
invoice_flag,
missed_visit,
na_visit,
a.sequence_number,
a.eval_sponsor,
(
  CASE
  WHEN arm_code IS NOT NULL THEN
    'Arm ' || arm_code || ': ' || visit_string
  ELSE
    visit_string
  END),
a.evaluation_,
a.protocol_subject_id

) st1

left outer join
sv_fin_visit_variations vv
on st1.protocol_id = vv.protocol_id
and st1.protocol_subject_id = vv.protocol_subject_id
and st1.evaluation_ = vv.sd_pcs_tracking_evaluation_id

  ) event_data

--protocol info
  left join

  (

  select pcl.protocol_id, pcl.protocol_no, pcl_dep.departments, pcl_mg.management_groups, pcl_og.oncology_groups, pcl_primary_sponsor.primary_sponsor, pcl_secondary_sponsors.secondary_sponsors,
      pcl_budget_parameters.indirect_cumulative,
      pcl_budget_parameters.protocol_indirect,
      pcl_budget_parameters.subject_indirect,
      pcl_budget_parameters.milestone_indirect,
      (1 + pcl_budget_parameters.overhead_cost_rate/100) not_indirect_multiplier,
      (case when pcl_budget_parameters.indirect_cumulative != 'Y' then (1 + (pcl_budget_parameters.overhead_cost_rate + pcl_budget_parameters.indirect_cost_rate) / 100)
        else ((1 + pcl_budget_parameters.overhead_cost_rate/100) * (1 + pcl_budget_parameters.indirect_cost_rate/100))
      end) indirect_multiplier
from smrs_protocol pcl

--get the protocol departments
left join (
SELECT
  a.protocol_id, LISTAGG((case when a.primary_flag = 'Y' then b.name || ' (P)' else b.name end), '; ') within GROUP (ORDER BY a.primary_flag DESC, b.name) departments
FROM
  smrs_pcl_department a
LEFT JOIN onc_department b
ON
  a.department_id = b.onc_department_id
GROUP BY
  a.protocol_id
) pcl_dep on pcl.protocol_id = pcl_dep.protocol_id

--get the management groups
left join (
SELECT
  protocol_id, LISTAGG((case when primary_flag='Y' then mg.name || ' (P)' else mg.name end), '; ') within group (order by primary_flag DESC, name)
  management_groups
FROM
  onc_pcl_management_group pmg,
  onc_org_unit_management_group rmg,
  onc_management_group mg
WHERE
  pmg.RESEARCH_MANAGEMENT_GROUP_ID = rmg.onc_ou_mgmt_group_id
AND rmg.onc_management_group_id = mg.onc_management_group_id
group by protocol_id
) pcl_mg on pcl.protocol_id = pcl_mg.protocol_id

--get the oncology groups
left join (
SELECT
  protocol_id, LISTAGG((case when pd.value = 'Y' then description || ' (P)' else description end), '; ') within group (order by pd.value DESC, description)
  oncology_groups
FROM
  smrs_pcl_detail pd,
  pf_code sc
WHERE
pd.detail    = sc.code_id
AND sc.category  = 'DOWG'
group by protocol_id
) pcl_og on pcl.protocol_id = pcl_og.protocol_id

left join (
SELECT
  sps.protocol_id,
  ss.sponsor_name primary_sponsor
FROM
  smrs_pcl_sponsor sps,
  smrs_sponsor ss
WHERE
  sps.sponsor   = ss.sponsor
  and principal_sponsor = 'Y'
) pcl_primary_sponsor on pcl_primary_sponsor.protocol_id = pcl.protocol_id

left join (
SELECT
  sps.protocol_id,
  LISTAGG(ss.sponsor_name, '; ') within group (order by sponsor_name) secondary_sponsors
FROM
  smrs_pcl_sponsor sps,
  smrs_sponsor ss
WHERE
  sps.sponsor   = ss.sponsor
  and principal_sponsor = 'N'
  group by sps.protocol_id
) pcl_secondary_sponsors on pcl_secondary_sponsors.protocol_id = pcl.protocol_id

left join (
SELECT
  pr.protocol_id,
  (
    SELECT description FROM pf_code WHERE code_id = bp.rate_base
  )
  rate_base,
  bp.rate_base rate_base_id,
  NVL(bp.overhead_cost_pct, 0) overhead_cost_rate,
  NVL(bp.indirect_cumulative, 'N') indirect_cumulative,
  NVL(bp.indirect_cost_pct, 0) indirect_cost_rate,
  bp.protocol_indirect,
  bp.subject_indirect,
  bp.milestone_indirect,
  NVL(bp.budget_only_indirect, 'N') budget_only_indirect
FROM
  smrs_protocol pr,
  onc_pcl_budget_params bp
WHERE
  pr.protocol_id = bp.protocol_id
) pcl_budget_parameters on pcl_budget_parameters.protocol_id = pcl.protocol_id

) pcl_info on event_data.protocol_id = pcl_info.protocol_id

--protocol privileges
left join (
    select protocol_id,
    (case when exists (select 1 from sv_user_pcl_permission where function_name = 'INVOICEABLE_ITEMS' and protocol_id = pcl.protocol_id and contact_id = $P{userContactId})
    then 'Y' else 'N' end) has_invoiceable_items,
    (case when exists (select 1 from sv_user_pcl_permission where function_name = 'VISIT_VARIATIONS' and protocol_id = pcl.protocol_id and contact_id = $P{userContactId})
    then 'Y' else 'N' end) has_visit_variations,
    (
      case
        when exists (
          select
            1
          from
            sv_user_pcl_permission
          where
            function_name in ('SUBJECT-IDENTIFICATION', 'SUBJECT-INITIALS-IDENTIFICATION')
            and protocol_id = pcl.protocol_id
            and contact_id = $P{userContactId}
        )
        then 'Y'
        else 'N'
      end
    ) has_subject_identification
    from smrs_protocol pcl
) pcl_privileges on event_data.protocol_id = pcl_privileges.protocol_id

--search parameters
where (
    occurred_date is null or TO_DATE(occurred_date, 'MM/DD/YYYY') between
    $P{fromDate}
    and $P{thruDate}
)
  and (
    exists
        ( select
            1
          from
            sv_pcl_mgmt_mgmtgroup
          where
            protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id
            and $X{IN, mgmt_group_description, mgmtGroup}
        )
    or (
      $X{IN, 'ONLY_TRUE_IF_NO_MGNT_GROUPS_SPECIFIED', mgmtGroup}
      and not exists
        ( select
            1
          from
            sv_pcl_mgmt_mgmtgroup
          where
            protocol_id = sv_pcl_mgmt_mgmtgroup.protocol_id
        )
    )
)    
and (
    smrs_pcl_detail.detail = pf_code.code_id and pf_code.category  = 'DOWG' and $X{IN, pf_code.description, oncologyGroup}
)
and (
    smrs_pcl_department.department_id = onc_department.onc_department_id and $X{IN, onc_department.name, department}
)
and (
    $X{IN, smrs_protocol.protocol_no, protocol}
)
and (
    Type = 'Protocol Related' and ($X{IN, Exclusion, ProtocolInclude})
)
and (
    Type = 'Protocol Related' and ($X{IN, sv_pcl_status.status, ProtocolStatus})
)
and (
    Type = 'Visit Variation' and ($X{IN, Unplanned_Visit, Unplanned})
)
and (
    Type = 'Visit Variation' and ($X{IN, sv_fin_visit_variations.additional, Additional})
)
and (
    Type = 'Visit Variation' and ($X{IN, Visit_Status, VisitStatus})
)]]>
    </queryString>
    <field name="TYPE" class="java.lang.String"/>
    <field name="PROTOCOL_ID" class="java.math.BigDecimal"/>
    <field name="ITEM" class="java.lang.String"/>
    <field name="MILESTONE" class="java.lang.String"/>
    <field name="SEQUENCE_NUMBER" class="java.lang.String"/>
    <field name="INITIALS" class="java.lang.String"/>
    <field name="VISIT_STATUS" class="java.lang.String"/>
    <field name="OCCURRED_DATE" class="java.lang.String"/>
    <field name="DIRECT" class="java.math.BigDecimal"/>
    <field name="TOTAL_COST" class="java.math.BigDecimal"/>
    <field name="WITHHOLD" class="java.lang.String"/>
    <field name="WITHHOLDING_PCT" class="java.math.BigDecimal"/>
    <field name="SPONSOR_NAME" class="java.lang.String"/>
    <field name="INDIRECT_FLAG" class="java.lang.String"/>
    <field name="TRIGGER_TYPE" class="java.lang.String"/>
    <field name="REOCCURRING" class="java.lang.String"/>
    <field name="EXCLUSION" class="java.lang.String"/>
    <field name="COMMENTS" class="java.lang.String"/>
    <field name="STATUS" class="java.lang.String"/>
    <field name="UNPLANNED_VISIT" class="java.lang.String"/>
    <field name="ADDITIONAL" class="java.lang.String"/>
    <field name="EVAL_NOTES" class="java.lang.String"/>
    <field name="PROTOCOL_NO" class="java.lang.String"/>
    <field name="DEPARTMENTS" class="java.lang.String"/>
    <field name="MANAGEMENT_GROUPS" class="java.lang.String"/>
    <field name="ONCOLOGY_GROUPS" class="java.lang.String"/>
    <field name="PRIMARY_SPONSOR" class="java.lang.String"/>
    <field name="SECONDARY_SPONSORS" class="java.lang.String"/>
    <field name="INDIRECT_CUMULATIVE" class="java.lang.String"/>
    <field name="PROTOCOL_INDIRECT" class="java.lang.String"/>
    <field name="SUBJECT_INDIRECT" class="java.lang.String"/>
    <field name="MILESTONE_INDIRECT" class="java.lang.String"/>
    <field name="NOT_INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/>
    <field name="INDIRECT_MULTIPLIER" class="java.math.BigDecimal"/>
    <field name="HAS_INVOICEABLE_ITEMS" class="java.lang.String"/>
    <field name="HAS_VISIT_VARIATIONS" class="java.lang.String"/>
    <field name="HAS_SUBJECT_IDENTIFICATION" class="java.lang.String"/>
    <field name="MORE_INFO" class="java.lang.String"/>
    <variable name="total_due" class="java.lang.String">
        <variableExpression><![CDATA[new DecimalFormat("¤#,##0.00").format(
$F{TOTAL_COST} == null ? 
    ("Y".equals($F{INDIRECT_FLAG}) ? $F{DIRECT}.multiply($F{INDIRECT_MULTIPLIER}) : $F{DIRECT}) 
    : $F{TOTAL_COST}
)]]></variableExpression>
    </variable>
    <variable name="initials_filtered" class="java.lang.String">
        <variableExpression><![CDATA[$F{INITIALS} == null ? "" :
("Y".equals($F{HAS_SUBJECT_IDENTIFICATION}) ? $F{INITIALS} : "**")]]></variableExpression>
    </variable>
    <variable name="More_Info" class="java.lang.String">
        <variableExpression><![CDATA[$F{COMMENTS}+$F{EVAL_NOTES}]]></variableExpression>
    </variable>
    <group name="Group1"/>
    <group name="Group2"/>
    <group name="Group3"/>
    <group name="Group4"/>
    <title>
        <band height="102">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <frame>
                <reportElement mode="Opaque" x="0" y="0" width="1434" height="72" backcolor="#006699" uuid="2f9e325f-5726-45f7-980d-45df3aefc4ea">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <staticText>
                    <reportElement x="0" y="0" width="946" height="72" forecolor="#FFFFFF" uuid="c8271c97-a3f6-4dc1-83e0-f40231bdab3f">
                        <property name="local_mesure_unitheight" value="pixel"/>
                        <property name="com.jaspersoft.studio.unit.height" value="px"/>
                    </reportElement>
                    <textElement verticalAlignment="Middle">
                        <font size="34" isBold="true"/>
                    </textElement>
                    <text><![CDATA[Invoiceable Items]]></text>
                </staticText>
                <staticText>
                    <reportElement x="1065" y="0" width="365" height="72" forecolor="#FFFFFF" uuid="c89de18d-0f2f-4fe9-a2d2-6cbee0c1c1e7">
                        <property name="com.jaspersoft.studio.unit.x" value="px"/>
                        <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    </reportElement>
                    <textElement textAlignment="Right" verticalAlignment="Middle">
                        <font size="14" isBold="false"/>
                    </textElement>
                    <text><![CDATA[All protocols, by item type]]></text>
                </staticText>
            </frame>
            <frame>
                <reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="0" y="72" width="1434" height="30" backcolor="#006699" uuid="a9a52697-082e-42e1-89ec-68dd0b049098">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <staticText>
                    <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="88" height="30" forecolor="#FFFFFF" uuid="3afa32d5-2f7d-4eb8-a131-28fdda4bc83b"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[Start Date:]]></text>
                </staticText>
                <staticText>
                    <reportElement stretchType="RelativeToTallestObject" x="167" y="0" width="106" height="30" forecolor="#FFFFFF" uuid="2d71ca4b-6729-46eb-a0a4-ab7decdab5c6"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[End Date:]]></text>
                </staticText>
                <staticText>
                    <reportElement stretchType="RelativeToTallestObject" x="345" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="88137e82-ed30-486c-90f6-8cc42aa0aae8"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[Management Group:]]></text>
                </staticText>
                <staticText>
                    <reportElement stretchType="RelativeToTallestObject" x="489" y="0" width="85" height="30" forecolor="#FFFFFF" uuid="93aec992-5a53-422e-9d64-77415d1aca57"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[Oncology Group:]]></text>
                </staticText>
                <staticText>
                    <reportElement stretchType="RelativeToTallestObject" x="658" y="0" width="77" height="30" forecolor="#FFFFFF" uuid="dbbde861-1ed7-4b0b-b0da-a0414790be83"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[Department:]]></text>
                </staticText>
                <textField pattern="MM/dd/yyyy">
                    <reportElement stretchType="RelativeToTallestObject" x="88" y="0" width="79" height="30" forecolor="#FFFFFF" uuid="55296cab-b55c-4bfa-95de-7c268febe10e"/>
                    <textFieldExpression><![CDATA[$P{fromDate}]]></textFieldExpression>
                </textField>
                <textField pattern="MM/dd/yyyy">
                    <reportElement stretchType="RelativeToTallestObject" x="273" y="0" width="72" height="30" forecolor="#FFFFFF" uuid="c091402d-3826-4336-a1f3-95cdc021eaaa"/>
                    <textFieldExpression><![CDATA[$P{thruDate}]]></textFieldExpression>
                </textField>
                <textField isBlankWhenNull="true">
                    <reportElement stretchType="RelativeToTallestObject" x="424" y="0" width="65" height="30" forecolor="#FFFFFF" uuid="d52ae49d-59a9-48b8-a32e-5e266c957752"/>
                    <textFieldExpression><![CDATA[$P{mgmtGroup}]]></textFieldExpression>
                </textField>
                <textField isBlankWhenNull="true">
                    <reportElement stretchType="RelativeToTallestObject" x="574" y="0" width="84" height="30" forecolor="#FFFFFF" uuid="a35aeb70-4c24-40fa-94e0-b0080aa8b983"/>
                    <textFieldExpression><![CDATA[$P{oncologyGroup}]]></textFieldExpression>
                </textField>
                <textField isBlankWhenNull="true">
                    <reportElement stretchType="RelativeToTallestObject" x="736" y="0" width="100" height="30" forecolor="#FFFFFF" uuid="d735e804-c8ec-4e05-b8d8-e98f1ab80fb3"/>
                    <textFieldExpression><![CDATA[$P{department}]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </title>
    <columnHeader>
        <band height="27" splitType="Stretch">
            <staticText>
                <reportElement mode="Opaque" x="0" y="0" width="89" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="b1dcfa37-fe41-4dd3-ba8c-1132cca71ed2"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Type]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="89" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="1928f95a-28f0-4497-995c-e008308bff8c"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Mgmt. Groups]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="167" y="0" width="106" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="9e8369e1-45fe-4a67-845f-0a4fe441169e"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Working Groups]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="273" y="0" width="72" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="fa7b5671-3185-4328-ba28-fa3f57d38c95"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Departments]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="345" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="8448a272-89ca-4966-8527-f2c38d4d6fa0">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Protocol No.]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="936" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="84e996e6-c9a7-427e-85da-56127c21d313"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Milestone]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="680" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5c8d0443-f637-4202-9259-38a4122fc2b7"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Item]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="1036" y="0" width="59" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="7939812e-20b2-4f33-ba4a-8ea25a774a7b"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Seq. No.]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="1095" y="0" width="51" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5031c035-52c7-43ab-91f3-68148a2b3ba5"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Initials]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="1146" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="f340c609-345e-4ae2-840c-96de64b1a3e2"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Occurred Date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="1246" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5fa29d59-5626-41fa-a7d6-e28fb34032ff"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Negotiated Cost]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="1346" y="0" width="88" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="a8adf438-22fa-4ba1-956c-8e9df0c11458"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Total Due]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="428" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="5e7fd45d-792f-499e-83b9-e2075c938224">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Principal Sponsor]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="511" y="0" width="86" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="4a6ebe9b-a072-4f48-bf3b-a8347dc047b9"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Other Sponsors]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="597" y="0" width="83" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="c591bc04-dc74-41d8-94d7-e2aab81e21fc"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Item Sponsor]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="858" y="0" width="78" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="6822b1bc-3d29-4e50-a96a-0be70ea18855"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Visit Status]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="758" y="0" width="100" height="22" forecolor="#006699" backcolor="#E6E6E6" uuid="2c3fed66-2675-4f75-96f9-d67194495e1d"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Comments/Eval Notes]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="32" splitType="Prevent">
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="89" height="27" uuid="3f984aa3-f917-4330-85e8-5a07cc66650a"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{TYPE}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="89" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="da3929a7-d752-4ba9-830c-fe93ef1cdc22"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{MANAGEMENT_GROUPS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="167" y="0" width="106" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="1257da62-9027-4101-b32c-504e606b701a"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ONCOLOGY_GROUPS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="273" y="0" width="72" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="dea22672-42b8-4c4e-bca6-d0eebfcd4181"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{DEPARTMENTS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="345" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e5b65c06-b8c8-47fb-b1fa-a1052e4a2111">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{PROTOCOL_NO}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="680" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="613bbd4e-6774-4cce-ad88-7e7fa8b3077d"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ITEM}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1036" y="0" width="59" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e1767861-73f9-47ca-ab3f-f59b839220d5"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{SEQUENCE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="936" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="17078554-b751-42c3-9b9d-7cf920281e08"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{MILESTONE}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1146" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="7019a529-e225-4ebd-9182-a1fde914daf1"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{OCCURRED_DATE}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1246" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="54f419e4-ad0d-484c-9902-eb4f5a76cd30"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[new DecimalFormat("¤#,##0.00").format($F{DIRECT})]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1346" y="0" width="88" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="8b8c19d6-ea68-4c05-8f6e-61ae94369082"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{total_due}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="428" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="9a905988-f100-45d0-9b3b-0fac589372a3">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{PRIMARY_SPONSOR}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="511" y="0" width="86" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="ccf720d2-779b-4bc4-b1b4-3fa253629c5d"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{SECONDARY_SPONSORS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="597" y="0" width="83" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="14dcbcda-6498-412f-b21e-a3fcb5018502"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{SPONSOR_NAME}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="1095" y="0" width="51" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="e3d0affb-afff-4270-8bc0-3172f3b15fb5"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{initials_filtered}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="858" y="0" width="78" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="a7830fc3-d9b8-4790-85d8-7810a9591f0d"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{VISIT_STATUS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement stretchType="RelativeToTallestObject" mode="Transparent" x="758" y="0" width="100" height="27" forecolor="#000000" backcolor="#FFFFFF" uuid="c9073fec-8b08-4620-bf9d-82060c93f0e4"/>
                <box topPadding="0" leftPadding="0" bottomPadding="0" rightPadding="0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{MORE_INFO}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="25" splitType="Prevent">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <staticText>
                <reportElement mode="Opaque" x="0" y="0" width="89" height="25" backcolor="#E6E6E6" uuid="00b71e79-08b5-44a3-af35-e114fc18433b">
                    <property name="local_mesure_unity" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <property name="local_mesure_unitheight" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.height" value="px"/>
                </reportElement>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <text><![CDATA[Report Date:]]></text>
            </staticText>
            <textField pattern="MM/dd/yyyy">
                <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="89" y="0" width="1155" height="25" backcolor="#E6E6E6" uuid="4306bff6-d9b7-498f-8124-8c583de632a9">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement textAlignment="Left" verticalAlignment="Middle">
                    <paragraph firstLineIndent="20"/>
                </textElement>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="false">
                <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1244" y="0" width="100" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="16866726-e0e9-4b0b-86b7-17528b27e5e7">
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                </reportElement>
                <textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of "]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report" isBlankWhenNull="false">
                <reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="1344" y="0" width="90" height="25" forecolor="#000000" backcolor="#E6E6E6" uuid="43c13917-328f-49cc-84e0-fd0ca42186ba">
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box leftPadding="2"/>
                <textElement textAlignment="Left" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
</jasperReport>