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

0

I'm new to Jaspersoft reports, but I'm using Jaspersoft Studio 6.9.0, and modifying a report to add some additional Parameters, per a usser's request.  I can't preview the report, because I'm getting the following message "Error generating report." with the errors below.  If just run the SQL with the lines including parameters commented out, the code runs fine.  If I delete anything related to parameters in the JRXML file, the report will complete.  I've deleted parameters one by one to see if I could figure out what the cause of the error was, but that hasn't been helpful.  Do these errors mean anything to anyone?

 

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$18(ReportController.java:525)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:443)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: net.sf.jasperreports.engine.JRException: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error
    at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:133)
    at net.sf.jasperreports.engine.fill.JRFillDataset.contributeParameters(JRFillDataset.java:1153)
    at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:665)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:455)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLRecoverableException: IO Error: Connection reset by peer: socket write error
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:421)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
    at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.getConnection(JdbcDataAdapterService.java:171)
    at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:131)
    ... 6 more
Caused by: java.net.SocketException: Connection reset by peer: socket write error
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    at oracle.net.ns.DataPacket.send(DataPacket.java:199)
    at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:211)
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
    ... 12 more


 

jburczyk's picture
Joined: Sep 20 2019 - 1:13pm
Last seen: 6 days 12 hours ago

Connection reset by peer is a network issue. Something is wrong in the connection between Studio and your Oracle database.

I don't see how the parameters would have any effect on the database connection.

Can you share the JDBC URL (anonymized) and the JRXML so we can see some details?

swood_1 - 3 weeks 6 days ago

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>
 

jburczyk - 3 weeks 1 day ago

0 Answers:

No answers yet
Feedback