Set Up an AP Unapproved Lookup Query That Emails All Users on a Reviewer
Use the below fields to set up an AP Unapproved Lookup Query.
Select clause:
SELECT i.APCo AS [APCo]
,i.UIMth AS [UIMth]
,i.UISeq AS [UISeq]
,l.Line AS [Line]
,i.Vendor AS [Vendor]
,i.APRef AS [APRef]
,i.InvTotal AS [InvTotal]
,r.Reviewer AS [Reviewer]
,v.Name AS [VendorName]
,vDDUP.EMail AS [RevEmail]
,e.Name AS [ReviewerFullName]
,l.Description AS [Description]
,l.GrossAmt AS [GrossAmt]
,r.DateAssigned AS [DateAssigned]
,i.Description AS [HeaderDescription]
FromWhere:
FROM bAPUI i(NOLOCK)
JOIN bAPUL l(NOLOCK) ON i.APCo = l.APCo AND i.UIMth = l.UIMth AND i.UISeq = l.UISeq
JOIN bAPUR r(NOLOCK) ON l.APCo = r.APCo AND l.UIMth = r.UIMth AND l.UISeq = r.UISeq AND l.Line = r.Line
JOIN bAPVM v(NOLOCK) ON i.VendorGroup = v.VendorGroup AND i.Vendor = v.Vendor
JOIN bHQRV e(NOLOCK) ON r.Reviewer = e.Reviewer
JOIN bHQRP on bHQRP.Reviewer = e.Reviewer
JOIN vDDUP on vDDUP.VPUserName = bHQRP.VPUserName
WHERE r.ApprvdYN = 'N' AND isnull(Rejected, 'N') = 'N' AND r.ApprovalSeq = (
- SELECT min(r2.ApprovalSeq)
- FROM bAPUR r2
- WHERE r2.APCo = r.APCo AND r2.UIMth = r.UIMth AND r2.UISeq = r.UISeq AND r2.Line = r.Line AND r2.ApprvdYN = 'N' AND isnull(r2.Rejected, 'N') = 'N'
- ) AND NOT EXISTS (
- SELECT TOP 1 1
- FROM bAPUR r3
- WHERE r3.APCo = r.APCo AND r3.UIMth = r.UIMth AND r3.UISeq = r.UISeq AND r3.Line = r.Line AND r3.ApprvdYN = 'N' AND r3.Rejected = 'Y'