Monday, May 10, 2010

Missing Records in Whitehill Document Catalog

A while back we looked at missing files in Whitehill where the appropriate record existed in the Document Catalog. Sometimes, however, the Document Catalog is never updated. The following query will help identify which invoices have been created and not inserted in the Document Catalog.
SELECT linvoice
, ltradat
, lmatter
, clname1
, mdesc1
, bop
, SUM(lamount) AS invamount
FROM ledger
INNER JOIN matter ON lmatter=mmatter
INNER JOIN client ON mclient=clnum
INNER JOIN batch ON lbatch=bbatch
WHERE ltradat >= '5/1/2010'
AND lzero <> 'R'
AND linvoice <> '0'
AND llcode IN ('FEES','HCOST','SCOST')
AND mclient <> '123456'
AND linvoice NOT IN
 FROM Document_Catalog
 WHERE EMP_ID NOT IN ('DRAFT','SEC_9999','TK_9999')
AND linvoice NOT IN (SELECT linvoice FROM v_jointinv WHERE NumMtr > 1)
AND batch.bop NOT IN ('abc','def')
GROUP BY linvoice,ltradat,lmatter,mdesc1,clname1,bop
ORDER BY bop,linvoice
You'll notice in the code above, I've set some exclusions. We have a view that counts the number of matters per invoice. Any invoices with more than one matter (e.g. a joint invoice) are excluded from this query and are included in a separate query that doesn't include matter information. I've also included the ability to exclude particular users and clients from the query. Regarding the subquery on the Document Catalog table, any non-numeric values will need to be excluded to enable the matching against the EMP_ID field (or you can cast linvoice as VARCHAR).

No comments:

Post a Comment