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'