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

jburczyk · September 20, 2019

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

swood_1 · September 20, 2019

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?

jburczyk · September 25, 2019

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"?>

<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">
 
 </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">
 
 </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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 <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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 </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">
 
 <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">
 
 <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">
 
 </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">
 
 <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">
 
 </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">
 
 </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">
 
 </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">
 
 </textElement>
 <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
 </textField>
 </band>
 </pageFooter>
</jasperReport>

Sign In

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

Recommended Posts

jburczyk

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

swood_1

Link to comment

Share on other sites

jburczyk

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore