Consult workspace invoice bug

Submitted by Guest on Wed, 30/01/2013 - 07:36

General User's Discussion

It looks like sometimes the consult workspace is pulling the wrong invoice to work on. In the case of the the attached screenshots, it's actually pulling a finalized invoice, even though there's a non-finalized one available. This problem appears to be intermittent. This is running on version 1.6 (4930)

Cheers, Paul

Re: Consult workspace invoice bug

Submitted by tanderson on Wed, 30/01/2013 - 10:12.

The invoice is selected as follows:

1. Look for invoice items linked to the current patient visit.

If there are any, return the associated invoice. Use non-Finalised invoices in any; or

2. Look for the latest In Progress invoice for the customer; or

3. Look for the latest Completed invoice for the customer

The fact that its returning a Finalised invoice suggests that the Completed invoice is not linked to the current visit.

-Tim

Re: Consult workspace invoice bug

Submitted by pstadler (not verified) on Wed, 30/01/2013 - 12:36.

Hmm.. I'm not seeing the link in the database. I'm tracing through this path:

(in pseudo-sql)

participations.act_id a where act_arch_short_name=act.customerTask and startdate=theone I expected ->

act_relationships.target_id=a -> (looked at act_relationships.act_id or target_id = the invoice, or the task in question)

acts where act_id=act_relationship.source_id

and there's no reference across from the invoice displayed to the task that I'm working on here...

Some other screenshots from the db attached. Perhaps I'm looking in the wrong spot? In these screenshots, (and from the invoice hard-copy), the invoice ID is 117199, and the suspected link is to customerTask 118523.

Cheers, Paul

Attachment	Size
Screenshot from 2013-01-29 21:44:56.png	231.89 KB
Screenshot from 2013-01-29 21:45:39.png	172.75 KB

Re: Consult workspace invoice bug

Submitted by tanderson on Wed, 30/01/2013 - 13:00.

Use the following SQL verify which invoice is linked to which visit:

 select invoice.act_id invoiceId, invoice.status invoiceStatus, invoice.activity_start_time invoiceStartTime,
        invoiceItem.act_id invoiceItemId, visit.act_id visitId, customer.name
from acts invoiceItem
join act_relationships visitLink on invoiceItem.act_id = visitLink.target_id
    and invoiceItem.arch_short_name = "act.customerAccountInvoiceItem"
    and visitLink.arch_short_name = "actRelationship.patientClinicalEventChargeItem"
join acts visit on visit.act_id = visitLink.source_id    
join act_relationships invoiceLink on invoiceItem.act_id = invoiceLink.target_id
    and invoiceLink.arch_short_name = "actRelationship.customerAccountInvoiceItem"
join acts invoice on invoice.act_id = invoiceLink.source_id
join participations partic on partic.act_id = invoice.act_id and partic.arch_short_name = "participation.customer"
join entities customer on customer.entity_id = partic.entity_id
order by invoice.activity_start_time

Note that this will pull in every invoice linked to a visit, so constrain it on customer id or name, e.g.:

customer.entity_id = 1234567
customer.name = "Bar,Foo"

-Tim

Re: Consult workspace invoice bug

Submitted by pstadler (not verified) on Wed, 30/01/2013 - 23:14.

Thanks Tim,

I've run that query, and produced this output, filtered by (shortened) name:

 invoiceId invoiceStatus invoiceStartTime invoiceItemId visitId name
117199 POSTED 2013-01-22 15:07:31 117311 117197 T,Mary
117199 POSTED 2013-01-22 15:07:31 117312 117197 T,Mary
117199 POSTED 2013-01-22 15:07:31 117309 117197 T,Mary
117199 POSTED 2013-01-22 15:07:31 117313 117197 T,Mary
117199 POSTED 2013-01-22 15:07:31 117310 117197 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118618 118617 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118548 117750 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118689 118617 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118550 117750 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118687 118617 T,Mary
118547 IN_PROGRESS 2013-01-28 17:58:28 118549 117750 T,Mary

invoiceId is the one that's showing up for a consult action on taskId=118523 which relates to visitId=118425... so something is still looking fishy to me.

I've put a screenshot of those relationships here as well (sorry for the text formatting there).

Cheers, Paul

Attachment	Size
screenshot.png	113.63 KB

Re: Consult workspace invoice bug

Submitted by tanderson on Thu, 31/01/2013 - 19:37.

When you click on Consult in Appointments, it looks for a Visit close to the time of the selected appointment.

Specifically, it looks for a Visit whose:

startTime <= appointmentDate; or
startTime between appointmentDate 0:0:0 and appointmentDate 23:59:59; and whose
endTime is unset; or
endTime >=appointmentDate 23:59:59; or
endTime between appointmentDate 0:0:0 and appointmentDate 23:59:59

Multiple Visits may match the criteria. The one with the closest startTime <= the appointment is selected. If there are no Visits, one will be created.

In your case, the appointment was at 17:30 on 28/1.

The criteria matches the following Visits:

2012-06-28 12:31:06
2013-01-13 00:00:00
2013-01-22 12:30:00
2013-01-28 17:56:00

Visit 3. is selected as its startTime is before the appointment.

Once the Visit is determined, any Invoice linked to it will be used. In your case, the invoice was a Finalised one, dated 2013-01-22 15:07:31.

Its important to note that marking finished prior Visits completed will ensure they aren't accidentally selected when performing Check-In or Consults for subsequent Visits.

-Tim

Re: Consult workspace invoice bug

Submitted by pstadler (not verified) on Sun, 03/02/2013 - 23:57.

Thanks Tim,

Okay, this raised the question as to how the visit got in this status. I've done some more experimenting and uncovered a work-flow problem (not w/ OpenVPMS, but with our people work procedures). It turns out that as a result of having the dates within a visit going in chronological order as opposed to reverse-chrono, as it is w/ 1.6, the staff had been instructed to create a new visit for each day. In doing so, without the new visit+note button, they were manually creating new visits every day for any cats that had a longer than one day stay.

We're now going to retrain the staff to "reuse" the existing visit so the data are correct. I can't say how happy I am that the dates are in a uniform chronology now in the medical records. This was the reason we were creating a new visit for each day -- to effectively get the chronology we wanted to see. I don't think our staff know how to use the add visit+note button yet, but we'll train them up on that. This is very useful for phone call based visits. Anyway, thanks once again for spending the time to dig into this one with me... very informative.

Cheers, Paul