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
Thursday, December 16, 2010
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.
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'
Thursday, December 2, 2010
Calculating a Real Billing Realization
One potential issue with billing realization within Elite is that worked values are set based on the rate codes and exception rates in effect on each matter. This is great for certain purposes, but it's not as handy when trying to compare billed values to a "standard" or "rack" rate. While some firms have chosen to utilize the Accounting Information tab in Timekeeper Master to keep track of standard rates over time, many don't and simply use the Rates tab. This presents a problem with comparing worked values and billed values to a "standard" or "rack" rate for each timekeeper. The following query mostly solves this issue by comparing each timecard worked to the "standard" or "rack" rate in effect for that timekeeper at the time the timecard was worked. A true billing realization can then be calculated.
One of the neat things about this query is the method used to "create" an ending effective date for each record in the rate table. By default, this table only has a beginning effective date. Therefore, by joining the table to itself we are able to match the next highest effective date for a record and subtract a day from it to arrive at an ending date for that record.
Note: While this query is correctly configured to take multi-currency into account, it is assuming that everyone has a US Dollar rate. If that's not the case for your firm, modifications will need to be made. Also, for this example, Rate Code 2 is considered the "standard" or "rack" rate.
One of the neat things about this query is the method used to "create" an ending effective date for each record in the rate table. By default, this table only has a beginning effective date. Therefore, by joining the table to itself we are able to match the next highest effective date for a record and subtract a day from it to arrive at an ending date for that record.
Note: While this query is correctly configured to take multi-currency into account, it is assuming that everyone has a US Dollar rate. If that's not the case for your firm, modifications will need to be made. Also, for this example, Rate Code 2 is considered the "standard" or "rack" rate.
WITH rates AS ( SELECT b.tkinit AS tkrinit ,b.tkeffdate AS tkrdate1 ,MIN(CASE WHEN e.tkeffdate IS NULL THEN '1/1/2026' ELSE e.tkeffdate END)-1 AS tkrdate2 ,b.tkrt02 ,b.tkrtcur FROM timerate b LEFT OUTER JOIN timerate e ON b.tkinit=e.tkinit AND e.tkeffdate > b.tkeffdate AND b.tkrtcur=e.tkrtcur GROUP BY b.tkinit,b.tkeffdate,b.tkrt02,b.tkrtcur ) SELECT head1 AS 'Department' ,tkinit AS 'TK #' ,tklast+', '+tkfirst AS 'Timekeeper' ,SUM(tworkhrs) AS 'Worked Hrs' ,CAST(SUM(CASE WHEN rates.tkrt02 iS NULL THEN tworkhrs*dorates.tkrt02 ELSE tworkhrs*rates.tkrt02/cdrate END) AS DECIMAL(16,2)) AS 'R2 Fees' ,CAST(SUM(tworkdol/cdrate) AS DECIMAL(16,2)) AS 'Worked Fees' ,CAST(SUM(tbilldol/cdrate) AS DECIMAL(16,2)) AS 'Billed Fees' FROM timecard INNER JOIN timekeep ON ttk=tkinit INNER JOIN deptlab ON tkdept=delcode INNER JOIN periodt ON tbiper=pe INNER JOIN matter ON tmatter=mmatter INNER JOIN currates ON mcurrency=curcode AND pebedt BETWEEN cddate1 AND cddate2 AND trtype='A' LEFT OUTER JOIN rates ON rates.tkrinit=tkinit AND tworkdt BETWEEN rates.tkrdate1 AND rates.tkrdate2 AND mcurrency=rates.tkrtcur LEFT OUTER JOIN rates dorates ON dorates.tkrinit=tkinit AND tworkdt BETWEEN dorates.tkrdate1 AND dorates.tkrdate2 AND dorates.tkrtcur='DO' WHERE YEAR(pebedt)='2010' AND pebedt < '12/1/10' AND tstatus NOT IN ('E','NBP') GROUP BY tkdept,head1,tkinit,tklast,tkfirst ORDER BY tkdept,head1,tklastThe total results for Worked Hours, Worked Fees and Billed Fees should tie back to Timekeeper and Matter Stats in Inquiry.
Back in the Saddle
So, after a 6 month respite, I have returned and will begin adding new content to the blog once again! The last 6 months have been an exciting time for me and my family, having moved to Philadelphia, PA and joined a new law firm. The most visible changes you will see here include the addition of multi-timekeeper and multi-currency concepts to Enterprise SQL queries, as these are staples of my new employer. Thanks for visiting and I hope you will find the information here helpful!
Monday, May 10, 2010
Joint Invoice View
Often times it is helpful to know whether or not an invoice is a single matter or joint invoice. The following view is a pretty simple query that determines the answer to that question. You can then join this view to any query to include or exclude joint invoices from your results (or treat them differently).
USE [son_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[v_jointinv] AS SELECT linvoice, COUNT(DISTINCT lmatter) AS NumMtr, CASE WHEN COUNT(DISTINCT lmatter) >1 THEN 'Yes' ELSE 'No' END AS JointInv FROM ledger INNER JOIN ledcode ON llcode=lccode WHERE lcfco IS NOT NULL GROUP BY linvoice GO
Labels:
enterprise,
invoices,
Ledger,
sql
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 ( SELECT CAST(EMP_ID AS INT) 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,linvoiceYou'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).
Friday, April 16, 2010
Fee Discount by Year
This is a quick and dirty look at fee discounts by year for a given client. It utilizes the matter history table.
USE son_db; GO DECLARE @client NVARCHAR(10); SET @client = N'000001'; SELECT CAST(YEAR(peendt) AS VARCHAR(10)) AS 'Year' ,SUM(mhdowkdb) AS 'Worked' ,SUM(mhdobidb) AS 'Billed' ,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount' ,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %' FROM matths INNER JOIN periodt ON mhper=pe INNER JOIN matter ON mhmatter=mmatter WHERE mclient=@client GROUP BY YEAR(peendt) UNION SELECT 'Total' AS 'Year' ,SUM(mhdowkdb) AS 'Worked' ,SUM(mhdobidb) AS 'Billed' ,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount' ,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %' FROM matths INNER JOIN periodt ON mhper=pe INNER JOIN matter ON mhmatter=mmatter WHERE mclient=@client
Labels:
client,
enterprise,
fee,
sql
Thursday, March 25, 2010
General Ledger Transactions for a Range of Accounts
The following stored procedure will imitate a general ledger transaction report from within Elite, with easy use in Excel. The concept is faily simple with the first SELECT statement pulling Accounts Payable entries and the second SELECT statement pulling non-AP general journal entries. This also uses the GJ entry description user defined function. There are two variables passed to the stored procedure: beginning and ending dates. The GL accounts are hard coded, but could easily be changed to variables.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'usp_GL_Expenses' ) DROP PROCEDURE dbo.usp_GL_Expenses GO CREATE PROCEDURE dbo.usp_GL_Expenses @startdate DATETIME = NULL, @enddate DATETIME = NULL AS SELECT CAST(csddt.cknum AS VARCHAR(12)) AS 'Check #/GJ ID' ,csddt.ckline AS 'GJ Line' ,csddt.ckdate AS 'Date' ,MONTH(csddt.ckdate) AS 'Month' ,csddt.apnum AS 'Vendor #' ,csddt.apaddid AS 'Vendor Sub' ,ap.apname AS 'Vendor Name' ,apaddr.appynm AS 'Payee' ,csddt.vo_id AS 'Voucher' ,csddt.glnum AS 'GL Acct' ,csddt.glrc1 AS 'GL Dept' ,gl.gldes AS 'GL Description' ,gl.glnum + ' ' + gl.gldes AS 'GL Acct Description' ,csddt.amt AS 'Amount' ,ap.apct1 AS 'Vendor Type' ,voiddt AS 'Void Date' ,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Transaction Description' FROM csddt INNER JOIN ap ON csddt.apnum = ap.apnum INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid INNER JOIN apaddr ON csddt.apnum = apaddr.apnum AND csddt.apaddid=apaddr.apaddid INNER JOIN gl ON csddt.glnum = gl.glnum AND csddt.glrc1=gl.glrc1 WHERE (csddt.glnum BETWEEN '62500' AND '63320' OR csddt.glnum = '53100' OR gl.glnum BETWEEN '84100' AND '84199' OR gl.glnum BETWEEN '84400' AND '84499' OR csddt.glnum = '93145') AND csddt.ckdate BETWEEN @startdate AND @enddate UNION SELECT gjid ,gjline ,gjtrdt ,MONTH(gjtrdt) ,NULL ,NULL ,NULL ,NULL ,NULL ,gj.glnum ,gj.glrc1 ,gl.gldes ,gl.glnum + ' ' + gl.gldes AS 'glnumdes' ,ISNULL(dbamt,0)-ISNULL(cramt,0) ,NULL ,NULL ,des + ' ' + dbo.udfGJDes(gjid,gjline) FROM gj INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1 WHERE gjtrdt BETWEEN @startdate AND @enddate AND (gl.glnum BETWEEN '62500' AND '63320' OR gl.glnum = '53100' OR gl.glnum BETWEEN '84100' AND '84199' OR gl.glnum BETWEEN '84400' AND '84499' OR gl.glnum = '93145') GOTo execute the stored procedure, use this statement
EXECUTE dbo.usp_GL_Expenses '1/1/2010','12/31/2010' GOMake sure to grant execute to any other users that need access to this stored procedure.
GRANT EXECUTE ON dbo.usp_GL_Expenses TO elitereadonly; GO
General Journal Entry Description User Defined Function
Below is a user defined function to concatenate all lines of a GJ entry description. Unlike most other tables in Elite, the GJ description table only includes records for the second through x lines. The first line of the description is stored in the gj table itself. So, to fully utilize this udf, it will need to be concatenated to the first line from the gj table.
Here's an example of how to use it properly
USE [son_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udfGJDes] (@gjid VARCHAR(12), @gjline INT) RETURNS VARCHAR(1000) AS BEGIN DECLARE @description VARCHAR(1000) SELECT @description = '' SELECT @description = @description + ' ' + des FROM gjdes WHERE gjid = @gjid AND gjline = @gjline ORDER BY gjline RETURN LTRIM(RTRIM(@description)) END
Here's an example of how to use it properly
SELECT gjid ,gjline ,gjtrdt ,MONTH(gjtrdt) ,gj.glnum ,gj.glrc1 ,gl.gldes ,gl.glnum + ' ' + gl.gldes AS 'glnumdes' ,ISNULL(dbamt,0)-ISNULL(cramt,0) ,des + ' ' + dbo.udfGJDes(gjid,gjline) FROM gj INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1 WHERE gjtrdt BETWEEN '3/1/10' AND '3/31/10' AND gl.glnum = '20236'
Monday, March 15, 2010
Payments by Invoice Ledger Code
This is a simple little query to see how payments from a particular client have been applied by the original invoice ledger code. For example, you can determine how much of the payments were applied to fees or costs. You may need to tinker with the criteria to match your setups. This uses the matter description udf.
SELECT mmatter AS 'Matter #' ,dbo.udfMattDesc(mmatter) AS 'Matter Description' ,SUM(CASE WHEN inv.llcode = 'FEES' THEN pmt.lamount ELSE 0 END) as 'Fees Collected' ,SUM(CASE WHEN inv.llcode <> 'FEES' THEN pmt.lamount ELSE 0 END) AS 'Costs Recovered' ,SUM(pmt.lamount) AS TOTAL FROM ledger AS pmt INNER JOIN matter ON pmt.lmatter=mmatter INNER JOIN ledger AS inv ON pmt.laptoin=inv.lindex WHERE YEAR(pmt.ltradat)='2009' AND mclient='014523' AND pmt.llcode LIKE 'PAY%' GROUP BY mmatter
Monday, February 22, 2010
Linked Servers
As you know by now, I write quite a bit of SQL for mining data from the Enterprise database. Occasionally, I'll need some data from another database on a different SQL Server to pair with some information from Elite. In SQL Server, you can create a linked server to be able to join across SQL Servers. For example, we use Minisoft's ARCS accounts receivable program, and I've created a linked server to join ARCS notes to real-time Elite data.
- Create a Linked Server to the other SQL Server
USE master GO EXEC master.dbo.sp_addlinkedserver @server = N'ARCS' , @srvproduct=N'' , @provider=N'SQLOLEDB' , @datasrc=N'ARCS-SERVERNAME' , @catalog=N'ARCS-DBNAME' GO
- Add login credentials for the other SQL Server (if you don't use trusted login)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ARCS' , @locallogin = NULL , @useself = N'False' , @rmtuser = N'arcs-username' , @rmtpassword = N'arcs-password' GO
- Write Query to access ARCS from the Elite database
SELECT TOP 100 * FROM ARCS.ARCS-DBNAME.dbo.ARCS_HOLDS
Wednesday, February 17, 2010
Checks for a Vendor with General Ledger Distribution
It is fairly easy to pull a list of checks for a vendor within AP Inquiry in Elite Enterprise. However, sometimes you just need a little more information. The following query will pull a list of checks along with the GL accounts affected by those checks.
A couple things to note:
A couple things to note:
- Due to the GL distribution piece, there may be more than 1 row per check.
- The cash portion of the GL distribution has been excluded (take out the udtrtp line if you would like that included)
- The glnum and glrc1 are combined into one number
- The user indicated is the user who created the batch. More than one user can post items to a batch.
SELECT ap.apnum AS 'Vendor #' ,ap.apname AS 'Vendor Name' ,csddt.cknum AS 'Check #' ,csddt.ckline AS 'Line' ,csddt.baid AS 'Bank' ,csddt.ckdate AS 'Date' ,csddt.vo_id AS 'Voucher #' ,csddt.glnum+'-'+csddt.glrc1 AS 'GL #' ,gldes AS 'GL Description' ,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Description' ,csddt.amt AS 'Amount' ,fmsbatch.opuserid AS 'User' FROM csddt INNER JOIN ap ON csddt.apnum=ap.apnum INNER JOIN fmsbatch ON csddt.cdbtid=fmsbtid AND fmsbatch.trtype='CK' INNER JOIN gl ON csddt.glnum=gl.glnum AND csddt.glrc1=gl.glrc1 INNER JOIN csd ON csd.baid=csddt.baid AND csd.cknum=csddt.cknum WHERE csddt.ckdate >= '1/1/2009' AND ap.apnum = '123456' AND udtrtp <> 'CA' AND csd.voiddt IS NULL ORDER BY csddt.ckdate
Tuesday, February 9, 2010
Outstanding Trust Checks
In this post, I will be highlighting a common method that I use to automatically pull information from the Elite database directly into MS Excel. I am often asked for a report that is transformed from an ad hoc request into a monthly (or more regularly) deliverable. I try to automate these as much as is possible. As is typical, there's more than one way to accomplish this objective, but here's how I often solve the problem.
1. Write Query to pull the exact information I need. In this case, trust account #606 is required with check dates prior to 1/31/10 (tie to month-end bank statement). This is run after reconciling the account. Therefore, all non-checks should have a presentation flag of 'Y', and are excluded.
2.A. The code below will create a stored procedure that runs the code above with 2 variables passed to it. First, the bank account and second, the end date for transactions.
1. Write Query to pull the exact information I need. In this case, trust account #606 is required with check dates prior to 1/31/10 (tie to month-end bank statement). This is run after reconciling the account. Therefore, all non-checks should have a presentation flag of 'Y', and are excluded.
SELECT trsttran.trindex AS 'Index #', matter.mmatter AS 'Matter #', dbo.udfMattDesc(matter.mmatter) AS 'Matter Description', dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney', trsttran.trdate AS 'Date', trsttran.trdocnum AS 'Check #', ap.apname AS 'Vendor', dbo.udfTrustDesc(trsttran.trindex) AS 'Description', trsttran.tramount AS 'Amount' FROM matter INNER JOIN timekeep ON mbillaty=tkinit INNER JOIN trsttran ON trmatter = mmatter LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum WHERE trsttran.tracct = '606' AND trsttran.trpresflag='N' AND trsttran.trdate <= '1/31/2010' AND trsttran.trdocnum Is Not Null ORDER BY trsttran.trdate2. At this point, for an adhoc request, I will often copy/paste the report into MS Excel and off we go. However, if I expect to be running this on a frequent basis, I will use the MS Query add-in to MS Excel and paste the SQL into the MS Query. This works like a charm. However, there are often criteria that will need to be changed each time the report is run. In MS Query, you can add parameters by replacing the criteria value with a '?' (i.e. trdate <= ?) This will allow you to either set a prompt to request the value, or reference a specific cell for a value. BUT, this only works if you have a fairly simple query that can be "graphically represented" in MS Query. So if your query can't be "graphically represented", what do you do? Stored Procedure. (it's not as scary as it sounds)
2.A. The code below will create a stored procedure that runs the code above with 2 variables passed to it. First, the bank account and second, the end date for transactions.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'usp_Trust_OS_Checks' ) DROP PROCEDURE dbo.usp_Trust_OS_Checks GO CREATE PROCEDURE dbo.usp_Trust_OS_Checks @tracct VARCHAR(20) = NULL, @maxdate DATETIME = NULL AS SELECT trsttran.trindex AS 'Index #', matter.mmatter AS 'Matter #', dbo.udfMattDesc(matter.mmatter) AS 'Matter Description', dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney', trsttran.trdate AS 'Date', trsttran.trdocnum AS 'Check #', ap.apname AS 'Vendor', dbo.udfTrustDesc(trsttran.trindex) AS 'Description', trsttran.tramount AS 'Amount' FROM matter INNER JOIN timekeep ON mbillaty=tkinit INNER JOIN trsttran ON trmatter = mmatter LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum WHERE trsttran.tracct = @tracct AND trsttran.trpresflag='N' AND trsttran.trdate <= @maxdate AND trsttran.trdocnum Is Not Null ORDER BY trsttran.trdate GO2.B. You can execute the stored procedure as such. This will give you the exact same results as the original query.
EXECUTE dbo.usp_Trust_OS_Checks '606','01/31/2010' GO2.C. Next I need to grant permission for our read-only account to execute the stored procedure.
GRANT EXECUTE ON dbo.usp_Trust_OS_Checks TO elitereadonly; GO3. Now, how do we get a stored procedure to execute from MS Query within MS Excel?
- Within MS Excel, first choose 2 cells for input for the variables (go ahead and put the data there).
- Choose the Data menu, Import External Data, New Database Query.
- Choose the data source that corresponds to your Elite database.
- After logging in, you should see the query wizard, choose cancel.
- Choose Yes when prompted to continue editing in MS Query.
- Next you should see the "Add Tables" dialog, choose close. You have now arrived at the basic MS Query window (looks remarkably similar to MS Access).
- Under the View menu, choose the SQL option. Use the following code to execute the stored procedure.
{CALL dbo.usp_Trust_OS_Checks(?,?)}
- You will get an alert saying that the SQL cannot be represented graphically, choose OK.
- Next you will be prompted for the two parameters. Enter the data requested (in my case, 606 and 1/31/2010).
- You should now see the data in MS Query.
- From the File menu, choose Return Data to MS Excel.
- You will be prompted with the following dialog box.
- Choose the cell you would like the data to start in, and then choose the Parameters option. Here you have the option to either prompt for the value, use a static value, or get a value from a cell. If you choose the latter, you will have an option to automatically refresh the data when the value changes (if you have more than 1 parameter, I'd recommend not choosing this). When you are done, choose OK and OK and your data should magically appear within MS Excel.
Friday, January 29, 2010
Time Load VBA Scripting Example
One of the neat things about Enterprise is that it is customizable with VBA scripting. One of the uses I've found for this is to change the default checkboxes in many of the Enterprise screens to match how we do things. For instance, when our users need to manually upload time using Time Load, they have to select Expanded Line Format 2 and Workstation from the options. Rather than the users having to manually select these (and risk errors by choosing the wrong option), we can use VBA to default to these two choices. The code is shown below for this particular change.
Private Sub eisvbaform_forminitialize() Me.gpbInput0 = False Me.gpbInput4 = True Me.optDos = True Me.optUnix = False End SubWhen making VBA changes, I usually test at my user level first and then at the firm level. To make the changes at the firm level, when logging in, choose the Advanced option and the Select different user for form and VBA changes option. Browse to the form you want to edit and choose VBA Editor from the Design menu. Making these types of changes are simple, and can save administrators headaches caused by incorrect user input.
Tuesday, January 26, 2010
Check & Voucher Totals by Vendor
It is always interesting to look at year-end stats, and this query is no exception. This will show you the number and dollar amount of vouchers and checks by vendor that your Accounts Payable department processed in a given year, with the focus being on value of checks. Note, as it is currently written, this will not return any vendors with vouchers but no checks.
SELECT ap.apnum AS 'Vendor #', apname AS 'Vendor Name', csd.apaddid AS 'Address #', apaddr.appynm AS 'Payee Name', COUNT(cknum) AS '# Checks', SUM(ckamt) AS '$ Checks', nvo.numvoucher AS '# Vouchers', nvo.dolvoucher AS '$ Vouchers' FROM ap INNER JOIN csd ON ap.apnum = csd.apnum INNER JOIN apaddr ON csd.apaddid = apaddr.apaddid AND csd.apnum = apaddr.apnum LEFT OUTER JOIN ( SELECT apvo.apnum, apvo.apaddid, COUNT(vo_id) AS numvoucher, SUM(voamt) AS dolvoucher FROM apvo WHERE YEAR(votrdt)='2009' GROUP BY apvo.apnum,apvo.apaddid) AS nvo ON csd.apnum=nvo.apnum AND csd.apaddid = nvo.apaddid WHERE YEAR(ckdate) = '2009' AND (voiddt IS NULL OR ckstat <> 'V') GROUP BY ap.apnum,apname,csd.apaddid,apaddr.appynm,numvoucher, dolvoucher HAVING SUM(ckamt) > 0 ORDER BY SUM(ckamt) DESC
Labels:
accounts payable,
ap,
enterprise,
fms,
sql,
vendor,
voucher
Friday, January 15, 2010
Matter Exception Rate Update
Occasionally we have the need to input matter exception rates for a number of matters for a client, but not all (hence matter exceptions and not client exceptions). To my knowledge, there are at least three ways to accomplish this. 1) Manually input exception rates on all matters that need them (but what if there's 50!). 2) If you're on 3.5 or higher, you can create a matter udf called CEXRATE and input a different client number to utilise that client's exception rates. You could then create a dummy client, load the exception rates once and add this client number to each matter in the CEXRATE udf. 3) You can use the method below.
Step 1 - Input exception rates for one matter. Then view the exception rates in SQL. We want to copy the last row with the effective date of 5/1/2004.
Step 2 - Create temporary table to hold matters that need the exception rates & populate.
Step 6 - If it's all good, commit the transaction (if not, rollback)
DISCLAIMER - As always, test liberally in a test environment and deploy cautiously.
Step 1 - Input exception rates for one matter. Then view the exception rates in SQL. We want to copy the last row with the effective date of 5/1/2004.
SELECT * FROM mexrate WHERE mrmatter='2000-001'Results
mrmatter | mrtk | mreffdate | mrrate | mrdevper | mrdevdol | mrccode | mrtitle | mrmaximum | mrreason | mrrtnum |
---|---|---|---|---|---|---|---|---|---|---|
2000-001 | ! | 2000-01-01 00:00:00.000 | 200.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
2000-001 | ! | 2003-01-01 00:00:00.000 | 250.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
2000-001 | ! | 2003-03-01 00:00:00.000 | 300.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
2000-001 | ! | 2003-05-01 00:00:00.000 | 350.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
2000-001 | ! | 2004-04-01 00:00:00.000 | 400.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
2000-001 | ! | 2004-05-01 00:00:00.000 | 500.000 | NULL | NULL | ! | Senior Partner | N | NULL | NULL |
Step 2 - Create temporary table to hold matters that need the exception rates & populate.
CREATE TABLE ##mexrate1 ( mmatter VARCHAR(15)); INSERT INTO ##mexrate1 VALUES ('2000-003') INSERT INTO ##mexrate1 VALUES ('2000-010') ... --If there is a systematic way to identify the matters then you can use: INSERT INTO ##mexrate1 SELECT mmatter FROM matter WHERE ...(your criteria)...Step 3 - Execute cursor update below to cycle through the matters and insert new exception rates
BEGIN TRANSACTION mexrateupdate Declare c Cursor For Select Distinct mmatter From ##mexrate1 Open c DECLARE @MatId VARCHAR (15); Fetch next From c into @MatId While @@Fetch_Status=0 Begin INSERT INTO mexrate SELECT @MatId, mrtk, mreffdate, mrrate, mrdevper, mrdevdol, mrccode, mrtitle, mrmaximum, mrreason, mrrtnum FROM mexrate WHERE mrmatter = '2000-001' --The original matter to copy AND mreffdate = '5/1/2004' --The exception rates to copy Fetch next From c into @MatId End Close c Deallocate cStep 4 - Use SQL to view a matter that was updated & make sure everything looks good
SELECT * FROM mexrate WHERE mrmatter = '2000-010'Step 5 - Check the matter in Elite to make sure everything looks ok.
Step 6 - If it's all good, commit the transaction (if not, rollback)
ROLLBACK TRANSACTION mexrateupdate --if it's bad COMMIT TRANSACTION mexrateupdate --if it's goodStep 7 - Explicitly drop the temporary table
DROP TABLE ##mexrate1Step 8 - Be thankful you didn't have to manually input 25 exception rates for 75 matters...
DISCLAIMER - As always, test liberally in a test environment and deploy cautiously.
Labels:
cursor,
enterprise,
exception rates,
matter,
rate,
sql
Monday, January 4, 2010
Missing Files in Whitehill
For those that use Elite Document Studio by Whitehill/Skywire/Oracle Legal (whatever it's called these days) there's several moving parts that can wind up not working properly. One of these is that the document record is created in the Document_Catalog table, but the actual file doesn't make its way to the correct folder. This results in an error in Elite when trying to access an invoice pdf. So what do you do when you have a host of files missing from your bill directory, but the records are already in the Document_Catalog table (especially if only 1 of 4 converters wasn't functioning properly)?
Well, here's a solution. If you have any ideas on how to improve this, or re-write it altogether to be more efficient or effective, please share!
The first section of code here creates a temporary table and inserts into the temporary table the list of files of the targeted directory. In this case, we're looking at the bill directory on the Elite application server. You'll need to modify this path according to your environment. One note: the collation in the CREATE TABLE needs to be the same as the collation of your Document_Catalog table otherwise you may get errors when joining the two.
The results from above should appear like this:
By taking a quick look at the temporary table you should see this:
These next few lines of code will strip out the non-file lines from the temporary table.
Next we need to compare the files in the directory (temp table) to the records in the Document_Catalog.
You now have a list of invoices that are missing from your bill location. You can now recap these specific invoices rather than target all within a certain date range of the error.
Well, here's a solution. If you have any ideas on how to improve this, or re-write it altogether to be more efficient or effective, please share!
The first section of code here creates a temporary table and inserts into the temporary table the list of files of the targeted directory. In this case, we're looking at the bill directory on the Elite application server. You'll need to modify this path according to your environment. One note: the collation in the CREATE TABLE needs to be the same as the collation of your Document_Catalog table otherwise you may get errors when joining the two.
USE [son_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --Drop temporary table if it already exists IF OBJECT_ID(N'tempdb.dbo.#DirContents', N'U') IS NOT NULL DROP TABLE tempdb.dbo.#DirContents; GO --Create temporary table to store contents of directory CREATE TABLE #DirContents ( Line VARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS NULL) DECLARE @PathName VARCHAR(256) , @CMD VARCHAR(512) SET @PathName = '\\eliteappserver\elite\home\bill\' SET @CMD = 'DIR ' + @PathName + ' /TC' PRINT @CMD INSERT INTO #DirContents EXEC MASTER..xp_cmdshell @CMD
The results from above should appear like this:
Database name 'tempdb' ignored, referencing object in tempdb. DIR \\eliteappserver\elite\home\bill\ /TC (32568 row(s) affected)
By taking a quick look at the temporary table you should see this:
Line |
---|
Volume in drive \\eliteappserver\elite\home\bill is Data_LUN72 |
Volume Serial Number is ABCD-E123 |
NULL |
Directory of \\eliteappserver\elite\home\bill |
NULL |
07/17/2009 07:34 PM <DIR> . |
07/17/2009 07:34 PM <DIR> .. |
10/06/2008 01:09 PM 41,270 000001.000001.481826.10062008.pdf |
09/03/2008 01:00 PM 42,434 000567.000026.471323.06052008.pdf |
09/03/2008 01:00 PM 42,680 000567.000026.473887.07072008.pdf |
These next few lines of code will strip out the non-file lines from the temporary table.
DELETE FROM #DirContents WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %' OR Line LIKE '%<DIR>%' OR Line is null
Next we need to compare the files in the directory (temp table) to the records in the Document_Catalog.
WITH realfiles AS ( SELECT 'DocName' = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ), 'CreateDate' = LEFT(Line,10) FROM #DirContents WHERE LEFT(Line,10) LIKE '12/%%/2009' ), doccatfiles AS ( SELECT EMP_ID ,REVERSE(LEFT(REVERSE(CAST(FILE_PATH AS VARCHAR(400))),CHARINDEX('\',REVERSE(CAST(FILE_PATH AS VARCHAR(400))))-1)) AS 'DocName' FROM Document_Catalog WHERE FILE_DATE >= '12/1/09' AND DESC_DATE_2 >= '12/1/09' ) SELECT DISTINCT doccatfiles.EMP_ID FROM doccatfiles WHERE doccatfiles.DocName NOT IN (SELECT realfiles.DocName FROM realfiles)Note: Since we're doing so much text manipulation and comparing long strings of text, this query is rather slow. You'll see I limited the inputs here to only December 2009 to make the information more manageable. If you have a large issue, I would recommend separating it into batches like this.
You now have a list of invoices that are missing from your bill location. You can now recap these specific invoices rather than target all within a certain date range of the error.
Subscribe to:
Posts (Atom)