Insurance Receivables Worklist

What is this worklist?

This worklist was created by Destinations Consulting and is provided at no charge to your practice. This worklist was edited from the existing Compulink worklist called "Insurance Receivables" to include charges billed within 0-30 days.

How do you use this worklist?

From the main Compulink screen, select the green Data Mining button. From this screen, select "Criteria":


From the screen that pops up, select Add:

You can name this worklist whatever you'd like. We've named ours "Insurance Receivables (Includes 0-30 Days)". Make sure to include a description of this worklist for anyone else who may access these worklists. Copy and paste the following script into the SQL Script field:

Select payor.payorguid as "PAYORGUID", localname as "Insurance",
round(sum(totalcur),2) as "0-30",
round(sum(total30),2) as "31-60",
round(sum(total60),2) as "61-90",
round(sum(total90),2) as "91-120",
round(sum(total120),2) as ">120"
from
(
select ins1unique as insurance, insexpect1 as totalcur, 0 as total30, 0 as total60, 0 as total90, 0 as total120 from ledger
where insexpect1 > 0 and inspaid1 is null
and ledger.svc_from <= curdate() and ledger.svc_from >= curdate() - 30
union all
select ins2unique as insurance, insexpect2, 0, 0, 0, 0 from ledger
where insexpect2 > 0 and inspaid2 is null
and ledger.svc_from <= curdate() and ledger.svc_from > curdate() - 30
union all
select ins3unique as insurance, insexpect3, 0, 0, 0, 0 from ledger
where insexpect3 > 0 and inspaid3 is null
and ledger.svc_from <= curdate() and ledger.svc_from >= curdate() - 30
union all

select ins1unique as insurance, 0 as totalcur, insexpect1 as total30, 0 as total60, 0 as total90, 0 as total120 from ledger
where insexpect1 > 0 and inspaid1 is null
and ledger.svc_from <= curdate() - 31 and ledger.svc_from >= curdate() - 60
union all
select ins2unique as insurance, 0, insexpect2, 0, 0, 0 from ledger
where insexpect2 > 0 and inspaid2 is null
and ledger.svc_from <= curdate() - 31 and ledger.svc_from > curdate() - 60
union all
select ins3unique as insurance, 0, insexpect3, 0, 0, 0 from ledger
where insexpect3 > 0 and inspaid3 is null
and ledger.svc_from <= curdate() - 31 and ledger.svc_from >= curdate() - 60
union all

select ins1unique as insurance, 0 as totalcur, 0 as total30, insexpect1 as total60, 0 as total90, 0 as total120  from ledger
where insexpect1 > 0 and inspaid1 is null
and ledger.svc_from <= curdate() - 61 and ledger.svc_from >= curdate() - 90
union all
select ins2unique as insurance, 0, 0, insexpect2, 0, 0 from ledger
where insexpect2 > 0 and inspaid2 is null
and ledger.svc_from <= curdate() - 61 and ledger.svc_from > curdate() - 90
union all
select ins3unique as insurance, 0, 0, insexpect3, 0, 0 from ledger
where insexpect3 > 0 and inspaid3 is null
and ledger.svc_from <= curdate() - 61 and ledger.svc_from >= curdate() - 90
union all

select ins1unique as insurance, 0 as totalcur, 0 as total30, 0 as total60, insexpect1 as total90, 0 as total120 from ledger
where insexpect1 > 0 and inspaid1 is null
and ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120
union all
select ins2unique as insurance, 0, 0, 0, insexpect2, 0 from ledger
where insexpect2 > 0 and inspaid2 is null
and ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120
union all
select ins3unique as insurance, 0, 0, 0, insexpect3, 0 from ledger
where insexpect3 > 0 and inspaid3 is null
and ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120
union all
select ins1unique as insurance, 0 as totalcur, 0 as total30, 0 as total60, 0 as total90, insexpect1 as total120 from ledger
where insexpect1 > 0 and inspaid1 is null
and ledger.svc_from <= curdate() - 121
union all
select ins2unique as insurance, 0, 0, 0, 0, insexpect2 from ledger
where insexpect2 > 0 and inspaid2 is null
and ledger.svc_from <= curdate() - 121
union all
select ins3unique as insurance, 0, 0, 0, 0, insexpect3 from ledger
where insexpect3 > 0 and inspaid3 is null
and ledger.svc_from <= curdate() - 121
) as totals
inner join insured on totals.insurance = insured.insunique
inner join payor on payor.payorguid = insured.payorguid
group by insurance, payor.payorguid
order by ">120" desc, "91-120" desc, "61-90" desc, "31-60" desc, "0-30" desc

Select "Patient" for the Query Type and change Worklist from "N" to "Y". The Display Field Text should be:

Insurance=300,0-30=75,31-60=75,61-90=75,91-120=75,>120=75

Because this worklist opens a child worklist for each insurance, you need to create a second query and connect the two together by typing "Payorguid" into the Child Param List field.

Just as with the parent query, you will need to add this text into the SQL Script field:

Select patient.patunique,
(select LOCALNAME from PAYOR where PAYORGUID = :PAYORGUID) as "Payor",
Trim(Patient.LAST )+', '+Trim( Patient.FIRST) as "Name",
cast(Bill.billunique as SQL_CHAR) as "Acct#",
round(sum(totalcur),2) as "0-30",
round(sum(total30),2) as "31-60",
round(sum(total60),2) as "61-90",
round(sum(total90),2) as "91-120",
round(sum(total120),2) as ">120"

 from
(
select ledger.patunique as patient,insured.insunique, insexpect1 as totalcur, 0 as total30, 0 as total60, 0 as total90, 0 as total120 from ledger
inner join insured on insured.insunique = ledger.ins1unique
where insexpect1 > 0 and InsPaid1 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() and ledger.svc_from >= curdate() - 30

union all

select ledger.patunique, insured.insunique, insexpect2, 0, 0,0, 0 from ledger
inner join insured on insured.insunique = ledger.ins2unique
where insexpect2 > 0 and InsPaid2 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() and ledger.svc_from > curdate() - 30

union all

select ledger.patunique, insured.insunique, insexpect3, 0, 0,0, 0 from ledger
inner join insured on insured.insunique = ledger.ins3unique
where insexpect3 > 0 and InsPaid3 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() and ledger.svc_from >= curdate() - 30

union all

select ledger.patunique as patient,insured.insunique, 0 as totalcur, insexpect1 as total30, 0 as total60, 0 as total90, 0 as total120 from ledger
inner join insured on insured.insunique = ledger.ins1unique
where insexpect1 > 0 and InsPaid1 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 31 and ledger.svc_from >= curdate() - 60

union all

select ledger.patunique, insured.insunique, 0, insexpect2, 0, 0,0 from ledger
inner join insured on insured.insunique = ledger.ins2unique
where insexpect2 > 0 and InsPaid2 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 31 and ledger.svc_from > curdate() - 60

union all

select ledger.patunique, insured.insunique, 0, insexpect3, 0, 0,0 from ledger
inner join insured on insured.insunique = ledger.ins3unique
where insexpect3 > 0 and InsPaid3 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 31 and ledger.svc_from >= curdate() - 60

union all

select ledger.patunique, insured.insunique, 0 as totalcur, 0 as total30, insexpect1 as total60, 0 as total90, 0 as total120  from ledger
inner join insured on insured.insunique = ledger.ins1unique
where insexpect1 > 0 and InsPaid1 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 61 and ledger.svc_from >= curdate() - 90

union all

select ledger.patunique,insured.insunique,  0, 0, insexpect2, 0,0 from ledger
inner join insured on insured.insunique = ledger.ins2unique
where insexpect2 > 0 and InsPaid2 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 61 and ledger.svc_from > curdate() - 90

union all

select ledger.patunique,insured.insunique, 0, 0, insexpect3, 0,0 from ledger
inner join insured on insured.insunique = ledger.ins3unique
where insexpect3 > 0 and InsPaid3 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 61 and ledger.svc_from >= curdate() - 90

union all

select ledger.patunique, insured.insunique, 0 as totalcur, 0 as total30, 0 as total60, insexpect1 as total90, 0 as total120 from ledger
inner join insured on insured.insunique = ledger.ins1unique
where insexpect1 > 0 and InsPaid1 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120

union all

select ledger.patunique, insured.insunique,0, 0, 0, insexpect2,0 from ledger
inner join insured on insured.insunique = ledger.ins2unique
where insexpect2 > 0 and InsPaid2 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120

union all

select ledger.patunique,insured.insunique, 0, 0, 0, insexpect3,0 from ledger
inner join insured on insured.insunique = ledger.ins3unique
where insexpect3 > 0 and InsPaid3 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 91 and ledger.svc_from >= curdate() - 120

union all

select ledger.patunique, insured.insunique, 0 as totalcur, 0 as total30, 0 as total60, 0 as total90, insexpect1 as total120 from ledger
inner join insured on insured.insunique = ledger.ins1unique
where insexpect1 > 0 and InsPaid1 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 121

union all

select ledger.patunique, insured.insunique, 0, 0, 0, 0, insexpect2 from ledger
inner join insured on insured.insunique = ledger.ins2unique
where insexpect2 > 0 and InsPaid2 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 121

union all

select ledger.patunique,insured.insunique,  0, 0, 0, 0, insexpect3 from ledger
inner join insured on insured.insunique = ledger.ins3unique
where insexpect3 > 0 and InsPaid3 is null
and payorguid = :PAYORGUID and
ledger.svc_from <= curdate() - 121

) as totals
inner join patient on patient.patunique = totals.patient
inner join bill on patient.billunique = bill.billunique
inner join insured on insured.insunique = totals.insunique
group by Name, patient, "Acct#", patient.patunique
order by ">120" desc, "91-120" desc, "61-90" desc, "31-60" desc, "0-30" desc

For your Display Field List, enter the following:

Name=150,Acct#-50,0-30=50,31-60=50,61-90=50,91-120=50,>120=50


How will I access this worklist?

From any screen in Compulink, select the Worklist icon at the top and double-click the worklist. It will take a few minutes to run.

Back to Free Stuff