--01 Vendor Usage Detail.sql -- SE rewrite of BO Vendor Usage detail (FactVendorUsage) -- Views: udv_service_detail_view_34, udv_profile_information_view_34 -- BO VendorID = SE SupplierID — needs to be added to expense/service views (not mapped yet). -- UsageReportType approximated from which SE service fields are populated: -- HHG = domestic/surface/air HHG tracking dates present -- TLV = TLV booking / property fields present -- MiscOrders = Order Date / Service Order Details otherwise DECLARE @UsageReportType varchar(20) = 'HHG'; -- HHG | TLV | MiscOrders DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @SrvcProvider varchar(100) = NULL; -- SE Consultant name DECLARE @AlaskaOnly bit = 0; ;WITH UsageRows AS ( SELECT CASE WHEN sd.[TLV Booking Status] IS NOT NULL OR sd.[TLV Move In Date] IS NOT NULL OR sd.[TLV Number of Properties] IS NOT NULL THEN 'TLV' WHEN sd.[Estimated Pack Start Date - Domestic] IS NOT NULL OR sd.[Actual Pack Start Date - Domestic] IS NOT NULL OR sd.[Estimated Pack Start Date - Surface] IS NOT NULL OR sd.[Estimated Pack Start Date - Air] IS NOT NULL THEN 'HHG' ELSE 'MiscOrders' END AS UsageReportType, sd.[Corp Code] AS CorpCode, pf.[Client] AS FormalName, sd.[Client Name] AS ParentCompany, sd.[File #] AS TEFileNo, pf.[Last Name] AS TELastName, pf.[First Name] AS TEFirstName, CASE WHEN sd.[Domestic or International] LIKE '%International%' THEN CAST(2 AS smallint) WHEN sd.[Domestic or International] LIKE '%Domestic%' THEN CAST(1 AS smallint) ELSE CAST(0 AS smallint) END AS ServiceType, sd.[Consultant] AS SrvcProvider, sd.Consultant AS SrvcFirstName, -- SE has fullname sd.Consultant AS SrvcLastName, -- SE has fullname COALESCE( sd.[Order Date], sd.[TLV Booking Status Date], sd.[Service Initiated Date], sd.[Service Active Date] ) AS OrderDate, pf.[Client Name] AS VendorParentID, sd.[Supplier Name] AS CompanyName, sd.[Desginations] AS CompanySubName, -- approximate (designations string) CAST(NULL AS varchar(50)) AS VendorID, -- Not used in report CASE WHEN sd.[Service (Workflow Template Name)] = 'Auto Shipment' THEN 1 ELSE 0 END AS HHGAutoShipment, sd.[Service (Workflow Template Name)] AS ServiceCode, -- description sd.[Service (Workflow Template Name)] AS ServiceDescription, sd.[Dept Work State] AS DPTWorkState, sd.[Dest Work State] AS DSTWorkState FROM se_reports.dbo.[udv_service_detail_view_34] sd JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON sd.[File #] = pf.[File #] WHERE NULLIF(LTRIM(RTRIM(sd.[Supplier Name])), '') IS NOT NULL ) SELECT u.UsageReportType, u.CorpCode, u.FormalName, u.ParentCompany, u.TEFileNo, u.TELastName, u.TEFirstName, u.ServiceType, u.SrvcProvider, u.SrvcFirstName, u.SrvcLastName, u.OrderDate, u.VendorParentID, u.CompanyName, u.CompanySubName, u.VendorID, u.HHGAutoShipment, u.ServiceCode, u.ServiceDescription, u.DPTWorkState, u.DSTWorkState FROM UsageRows u WHERE u.UsageReportType = @UsageReportType AND u.OrderDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR u.CorpCode = @CorpCode) AND (@SrvcProvider IS NULL OR @SrvcProvider = '' OR u.SrvcProvider LIKE '%' + @SrvcProvider + '%') AND ( @AlaskaOnly = 0 OR u.DPTWorkState IN ('AK', 'Alaska') OR u.DSTWorkState IN ('AK', 'Alaska') ) ORDER BY u.CompanyName, u.CorpCode, u.OrderDate, u.TEFileNo;