How to set up a AP Unapproved Lookup Query that Emails All Users on a Reviewer

Keystyle Support Team -

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'

              )

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk