Matters Waiting Closeout - No Attorney Time

I love helping accounting folks and billing reviewers too. This was a recent request from one of my favorite firms. Can we flag all the matters waiting accounting close out, where an attorney didn't create an entry in that billing cycle? If you have clients that don't like to receive legal bills without attorney time on them, this a good ask.

Here is how I solved it:

1. One of the best things I ever did in ProLaw Back Office Administration is use the professional class field under professionals to split professionals between attorneys from non attorneys. I always recommend you designate one professional field to create this data point. Attorneys have a million labels for the type of attorneys they are; attorney, associate, partner, senior partner, equity partner, junior associate, senior associate etc, etc. I just need one field to determine the difference between attorneys and staff, and this situation is why. The code below utilizes the professional class field from professionals to group the attorneys from non attorneys.

2. Every ProLaw One Office firm should have a "Waiting Accounting Closeout" or some similar status so that front office can remove a matter from their responsible queue and put that matter(s) into the accounting queue. You can't close a matter that still needs to be billed, and an attorney shouldn't have to sift through accounting billing matters to work. If you open 100 matters a year, not as important, 3000+, pivotal to front office processing/management.

3. This analysis is only from a fee perspective. Costs don't matter and aren't evaluated in this code.

Below is the code I used for my client. I dropped the code directly into an ODBC workbook (excel connecting directly to ProLaw SQL) and then created a pivot to summarize the information. The code is also written against all non billed entries. If you just want to analyze WIP, then use the stage column to filter your results. I set the ODBC to refresh on opening the file, so the accounting/billing professional can just open the file.

Screenshot of SQL Code in Development Window

This code allows our billing department the flexibility to analyze the prebills prior to creating the BSRs, and hopefully decreasing our billing review time for our senior attorneys.

Code to analyze all Waiting Accounting Matters:

select matterid, shortdesc, AreaOfLaw, clientsort, mattertype, Status, Professionals.ProfClass, Professionals.ProfName, sum(units) Hours

from matters left outer join Transactions on matters.Matters = transactions.Matters Left ouTer join components on COMPONENTS.COMPONENTS=TRANSACTIONS.COMPONENTS left outer join Professionals on transactions.Professionals = Professionals.Professionals

WHERE MATTERS.STATUS LIKE '%WAIT%' and COMPONENTS.COMPTYPE='F' AND EXISTS(SELECT * FROM TRANSACTIONS Left ouTer join components on COMPONENTS.COMPONENTS=TRANSACTIONS.COMPONENTS WHERE (STAGE='WIP') and COMPONENTS.COMPTYPE='F' AND MATTERS.MATTERS=TRANSACTIONS.MATTERS)

group by matterid, shortdesc, AreaOfLaw, clientsort, mattertype, Status, Professionals.ProfClass, Professionals.ProfName

order by AreaOfLaw, MatterType, clientsort

Code for a list of matters that only have non attorney time in Waiting Accounting matters:

select DISTINCT matterid, shortdesc, AreaOfLaw, clientsort, mattertype, Status

from matters left outer join Transactions on matters.Matters = transactions.Matters left outer join Professionals on transactions.Professionals = Professionals.Professionals Left ouTer join components on COMPONENTS.COMPONENTS=TRANSACTIONS.COMPONENTS

where matters.Status like 'wait%' and COMPONENTS.COMPTYPE='F' and

matters.MatterID not in

(select distinct m2.MatterID from transactions left outer join matters m2 on transactions.Matters = m2.Matters left outer join Professionals on transactions.Professionals = Professionals.Professionals Left ouTer join components on COMPONENTS.COMPONENTS=TRANSACTIONS.COMPONENTS where transactions.Stage <> 'billed' and Professionals.ProfClass = 'Professional' and m2.Status like 'wait%' and COMPONENTS.COMPTYPE='F')

and matters.MatterID in

(select distinct m3.Matters from transactions left outer join matters m3 on transactions.Matters = m3.Matters left outer join Professionals on transactions.Professionals = Professionals.Professionals Left ouTer join components on COMPONENTS.COMPONENTS=TRANSACTIONS.COMPONENTS where transactions.Stage <> 'billed' and Professionals.ProfClass = 'Administrative' and m3.Status like 'wait%' and COMPONENTS.COMPTYPE='F')