Short Paid Invoices

Invoices that are partially paid are a recurring issue at many law firms. The following query will return current day short paid invoices, their original value, the current day payment and the balance left on the invoice. You'll notice that I did not use common table expressions in this query. The reason for this is that this query was developed for an Extend event, and in Extend the final line must always start with SELECT, so starting with WITH won't work!

SELECT 
Invoices.InvMatter
,clname1 AS 'Client Name'
,mdesc1 AS 'Matter Description'
,tkinit AS 'BA #'
,tklast+', '+tkfirst AS 'BillAty'
,ldesc AS 'BA Location'
,Invoices.invInvoice
,Invoices.invDate
,Invoices.invFeeAmt
,Invoices.invCostAmt
,Invoices.invAmount
,Credits.pmtToday
,Credits.woffToday
,Invoices.invAmount-Credits.pmtAmount-Credits.woffAmount AS Balance
FROM
(
SELECT
linvoice AS invInvoice
,lmatter AS invMatter
,ltradat AS invDate
,SUM(CASE WHEN lcfco='F' THEN lamount ELSE 0 END) AS invFeeAmt
,SUM(CASE WHEN lcfco='C' THEN lamount ELSE 0 END) AS invCostAmt
,SUM(lamount) AS invAmount
FROM ledger
INNER JOIN ledcode ON llcode=lccode
WHERE lcfco IS NOT NULL
GROUP BY linvoice,lmatter,ltradat
) AS Invoices
INNER JOIN
(
SELECT
inv.linvoice AS invInvoice
,inv.lmatter AS invMatter
,inv.ltradat AS invDate
,SUM(CASE WHEN ccode.lccollhs='Y' AND cred.ltradat >= GETDATE()-1 THEN cred.lamount ELSE 0 END) AS pmtToday
,SUM(CASE WHEN ccode.lccollhs='N' AND cred.ltradat >= GETDATE()-1 THEN cred.lamount ELSE 0 END) AS woffToday
,SUM(CASE WHEN ccode.lccollhs='Y' THEN cred.lamount ELSE 0 END) AS pmtAmount
,SUM(CASE WHEN ccode.lccollhs='N' THEN cred.lamount ELSE 0 END) AS woffAmount
FROM ledger cred
INNER JOIN ledcode ccode ON cred.llcode=ccode.lccode
INNER JOIN ledger inv ON cred.laptoin=inv.lindex
INNER JOIN ledcode icode ON inv.llcode=icode.lccode
WHERE ccode.lcfco IS NULL
AND icode.lcfco IS NOT NULL
AND cred.lzero <> 'R'
AND EXISTS (SELECT * FROM ledger p INNER JOIN ledcode pc ON p.llcode=pc.lccode WHERE pc.lcfco IS NULL AND p.ltradat >= GETDATE()-1
    AND p.linvoice=cred.linvoice
    AND p.lmatter=cred.lmatter
    AND p.lzero<> 'R')
GROUP BY inv.linvoice,inv.lmatter,inv.ltradat
) AS Credits ON Invoices.invInvoice=Credits.invInvoice
   AND Invoices.invMatter=Credits.invMatter
   AND Invoices.invDate=Credits.invDate
INNER JOIN matter ON Invoices.invMatter=mmatter
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN client ON mclient=clnum
INNER JOIN location ON tkloc=locode
WHERE Invoices.invAmount-Credits.pmtAmount-Credits.woffAmount <> 0

Extend & Imaging - Vouchers not Imaged

Another way you can use Extend and Image Connect together is to identify which transactions in Elite do not have a corresponding barcode attached. The query below looks at vouchers in Elite and compares it to Image Connect to see if a barcode has been attached. If not, the voucher and user will appear in the results (and therefore receive an email from Extend stating as such). This will help ensure all transactions are properly scanned. The query below assumes use of AD authentication which should allow you to simply add on your domain to the username to arrive at the email address of the user. If you don't use AD authentication, I'd suggest using an user access UDF field for this purpose.

SELECT vo_id
,fmsbtid
,ap.apnum
,ap.appynm
,votrdt
,ufullname
,ufullname+'@yourdomain.com' AS email
,voamt
FROM apvo
INNER JOIN fmsbatch ON vobtid=fmsbtid AND trtype='VO'
INNER JOIN ap ON apvo.apnum=ap.apnum
INNER JOIN usmaster ON fmsbatch.opuserid=usmaster.userid
WHERE NOT EXISTS (SELECT * FROM [YourImagingDBServer].[IMAGING].[dbo].[Profiles] WHERE CAST(vo_id AS VARCHAR(12))=TransID1 AND AppTypeID=28 AND ProfileToAppStatus=1)
AND votrdt >= '12/1/10'
AND voamt > 0
ORDER BY opuserid,votrdt

Extend & Image Connect

Extend is one of the most useful tools in the Enterprise arsenal. Image Connect (or whatever imaging solution is in use) is also a very helpful tool for documenting transactions. Below, I've married the two to email out items scanned to the matter number for management to see what items are being documented at that level. Note that since your Imaging db is likely on a different server than your Elite db, you'll need to create a linked server on your Elite db server to access the remote Imaging server. There are a few assumptions made here: 1. The document is scanned on the same day the profile is created 2. The user has access to the file paths containing the images on the Image Connect application server.

SELECT Operators.FullName
,AppTypes.Description AS AppDesc
,Profiles.ProfileID
,Profiles.TransID1
,Profiles.Description AS ProfDesc
,Profiles.CreateDate
,Barcodes.BarcodeValue
,'<a href="file:///'+[ImagePath].[Path]+''+ImagePath.TempPath+''+CAST(Images.BarcodeGUID AS VARCHAR(255))+'.'+Images.FileExtension+'">View Image</a>'
FROM [imagingserver].[imaging].[dbo].[Profiles]
INNER JOIN [imagingserver].[imaging].[dbo].[Images] ON Profiles.ImageID=Images.ImageID
INNER JOIN [imagingserver].[imaging].[dbo].[ImagePath] ON Images.ImageID=ImagePath.ImageID
INNER JOIN [imagingserver].[imaging].[dbo].[Operators] ON Profiles.UserID=Operators.OperatorID
INNER JOIN [imagingserver].[imaging].[dbo].[AppTypes] ON Profiles.ApptypeID=Apptypes.AppTypeID
INNER JOIN [imagingserver].[imaging].[dbo].[Barcodes] ON Images.BarcodeGUID=Barcodes.BarcodeGUID
WHERE ImageStored = 1
AND Profiles.AppTypeID ='17'
AND ProfileToAppStatus=1
AND Profiles.CreateDate >= '12/1/10'

Checks with Descriptions for Specific Bank Account

The following will provide a list of checks disbursed on a specific bank account. You can add date qualifications or any other criteria as well. It will return a line for each 'check line', so check numbers and amounts will repeat for checks with multiple lines. This was originally developed for an Extend event, but we ended up going a different direction. This uses the check line description user defined function.

SELECT
csd.cknum,
csd.ckdate,
csd.apnum,
csd.ckamt,
csd.appynm,
csddt.ckline,
csddt.vo_id,
csddt.glnum,
csddt.glrc1,
csddt.amt,
dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS ckdes,
apvo.voinv,
apvo.voindt,
'********'+RIGHT(ap.apaccount,4) AS acctno
FROM
csd
INNER JOIN csddt ON csd.cknum=csddt.cknum AND csd.baid=csddt.baid
INNER JOIN apvo ON csddt.vo_id = apvo.vo_id
INNER JOIN ap ON csd.apnum=ap.apnum
WHERE csd.baid = 'BANKIACH'
AND voiddt IS NULL