/* Supplier Diversity Spend Report — supplier management extract BackOffice: frmRptSupplierDiversityRpt Crystal: SupplierDiversitySpend.rpt Parameterless extract for reporting / data warehouse loading. Report filters (transaction date range, client, detail vs summary) are applied downstream. */ SELECT te.TEFileNo, te.CorpCode, te.TELastName, te.TEFirstName, cl.FormalName, cl.ParentCompany, dtl.ExpDetailID, dtl.TransactionDate, dtl.SubmittedForPmt, dtl.VENDORID, dtl.Payee, dtl.Amount, dtl.PaidTo, dtl.PaidBy, dtl.DeniedExpense, dtl.TrackOnly, ec.CategoryID, ec.Category, ec.CompTypeID, vp.VendorParentID, vp.CompanyName, div.Description AS DiversityCertification, div.SubClassDescr, div.VendorType, div.Comments AS DiversityComments FROM dbo.tblTEExpenseDetail dtl INNER JOIN dbo.tblTEExpenseSheets sh ON dtl.ExpenseSheetID = sh.ExpenseSheetID INNER JOIN dbo.tblTEMain1 te ON sh.TEFileNo = te.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode INNER JOIN dbo.tblExpenseCategory ec ON dtl.CategoryID = ec.CategoryID LEFT JOIN dbo.tblVendorsGP gp ON dtl.VENDORID = gp.GPVendorId LEFT JOIN dbo.tblVendors v ON gp.VENDORID = v.VendorID LEFT JOIN dbo.tblVendorParent vp ON v.VendorParentID = vp.VendorParentID INNER JOIN dbo.vwSupplierDiversity div ON vp.VendorParentID = div.VendorParentid WHERE dtl.SubmittedForPmt IS NOT NULL AND dtl.VENDORID <> '' AND dtl.VENDORID IS NOT NULL AND dtl.PaidTo = 2 AND dtl.PaidBy = 1 AND dtl.DeniedExpense = 0 AND dtl.TrackOnly = 0;