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