How to Set Up an AP Unapproved Lookup Query That Emails All Users on a Reviewer

Use the below fields to set up an AP Unapprobed 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'