Finding "In Progress" and "Completed" Invoices

Greetings...

Loving the new 2.1 version and have started to implement some of the Statement features that we have not really used before.  Looking at the on-screen Debtor's report, however, I'm finding a number of "Unbilled" invoices.  Yikes.  With further snooping, I'm also finding some older invoices that are still "In Progress".  Double Yikes!

So I am looking for a report, or at least an SQL query from which I can write a report, that will list simply the Customer, date, value and status of unfinalized invoices.  My queries are pretty crude and not getting extracting the info I need.  If someone even has a query they would be willing to share, I'd be happy to write a report around it and post it to the Catalog.

Thanks in advance,

Sam
Kamloops, Canada 

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Finding "In Progress" and "Completed" Invoices

Hey Sam,

The "Works In Progress" section allows In Progress and Completed invoices to be worked on. We find the section a little slow to load and it doesn't hold the current view page number when you dip in and out of Customers.

So we use a self written workspace, using this SQL. 

SELECT invoices.act_id AS ID, invoices.activity_start_time as invoice_date, cust.name AS customer,
MAX(inv_items.activity_start_time) as last_invoice_activity,
SUM(IF(fa.credit,fa.total*-1,fa.total)) as inv_total
FROM acts invoices
LEFT JOIN participations inv_cust ON inv_cust.act_id = invoices.act_id AND inv_cust.arch_short_name = 'participation.customer'
LEFT JOIN entities cust ON cust.entity_id = inv_cust.entity_id AND cust.arch_short_name = 'party.customerperson'
LEFT JOIN act_relationships ar ON ar.source_id = invoices.act_id AND ar.arch_short_name = 'actRelationship.customerAccountInvoiceItem'
LEFT JOIN acts inv_items ON inv_items.act_id = ar.target_id AND ar.arch_short_name = 'actRelationship.customerAccountInvoiceItem'
LEFT JOIN financial_acts as fa ON (fa.financial_act_id = inv_items.act_id)
WHERE (invoices.status = 'IN_PROGRESS' OR invoices.status = 'COMPLETED')
AND invoices.arch_short_name = 'act.customerAccountChargesInvoice'
GROUP BY invoices.act_id
ORDER BY invoice_date

Cheers

Matt

Re: Finding "In Progress" and "Completed" Invoices

Wow... that's great!  Thank you so much.  232 problem invoices have been exposed! That's bad.  I've just added "invoices.status as status" to SELECT to help see what procedurally might be going wrong. The IN PROGRESS invoices are the more troublesome to me as those really do just fall off the radar.  They don't show up in any debtor's report.

I must admit that I did forget that a WIP report would have picked these up, but this seems very quick and flexible.  My custom WIP report suppresses output of $0 invoices but it looks now like there are quite few of those.  Perhaps an update query at some point to finalize those would be in order.  I'll dig into it a bit more.

Thanks again.  I'll look at the stock WIP report and start putting something together if the existing WIP doesn't show all of this.

Awesome!

Sam 

 

Syndicate content