--01 Supplier Diversity Spend Detail.sql -- SE rewrite of BO Supplier Diversity Spend detail (FactSupplierDiversitySpend) -- Views: udv_transaction_detail_34, udv_profile_information_view_34, udv_service_detail_view_34 -- Note: BO fact prefilters PaidTo=2 / PaidBy=1 / not denied / not track-only -- (TrackOnly = Payment Type 'Client Already Paid'). -- SE approximates with Pay To = Organization and supplier Designations present. -- View column name is misspelled: [Desginations]. -- BO VendorId = SE SupplierID — needs to be added to expense views (not mapped yet). DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, sd.[Client Name] AS ParentCompany, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Transaction Date] AS TransactionDate, NULL AS VendorParentID, -- Not used in report COALESCE(NULLIF(sd.[Supplier Name], ''), td.[Payee Name]) AS CompanyName, NULL AS VendorId, -- Not used in report td.[Payee Name] AS Payee, td.[Expense Category] AS Category, ISNULL(td.[Paid Amount], td.[Transaction Amount]) AS Amount, sd.[Desginations] AS DiversityCertification, NULL AS SubClassDescr, -- Not used in report sd.[Supplier Type] AS VendorType, NULL AS DiversityComments -- Not used in report FROM se_reports.dbo.[udv_transaction_detail_34] td JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] LEFT JOIN se_reports.dbo.[udv_service_detail_view_34] sd ON sd.[File #] = td.[File #] AND NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL WHERE td.[Transaction Date] BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) AND CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END <> 1 AND ( td.[Pay To] = 'Organization' OR NULLIF(LTRIM(RTRIM(sd.[Desginations])), '') IS NOT NULL ) ORDER BY COALESCE(NULLIF(sd.[Supplier Name], ''), td.[Payee Name]), td.[Corp Code], td.[Transaction Date] DESC, td.[File #];