--01 Imprest Account Detail.sql -- SE rewrite of BO Imprest Account detail (FactImprestAccount) -- View: udv_transaction_detail_34 -- Note: Imprest-specific flags (InOut, GL, check lag, etc.) are not in SEViews. DECLARE @CorpCode varchar(15) = NULL; DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @DivisionName varchar(30) = NULL; DECLARE @TEFileNo varchar(50) = NULL; DECLARE @TEDetail bit = 1; -- 1 = line detail; 0 = TE rollup -- Line detail SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Division] AS DivisionName, NULL AS ImprestAcct, -- unable to find NULL AS DisplayType, -- NOT USED IN SE NULL AS PhsCtg, -- NOT USED IN SE td.[Transaction Amount] AS Amount, NULL AS SourceTrxDate, -- unable to find td.[Transaction Date] AS TrxDate, td.[Service] AS ServiceCode, -- description, not code NULL AS ServiceType, -- NOT USED IN SE NULL AS InOut, -- unable to find NULL AS Source, -- unable to find NULL AS DocumentNo, -- unable to find NULL AS CustVendNo, -- unable to find NULL AS IsCheck, -- unable to find NULL AS IsFeeOffset, -- unable to find NULL AS CashType, -- unable to find NULL AS GLDescription, -- unable to find NULL AS GLJrnEntry, -- unable to find NULL AS CheckLagDays -- unable to find FROM se_reports.dbo.[udv_transaction_detail_34] td WHERE td.[Transaction Date] BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR td.[Division] = @DivisionName ) AND (@TEFileNo IS NULL OR @TEFileNo = '' OR td.[File #] = @TEFileNo) AND @TEDetail = 1 ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #], td.[Transaction Date]; -- TE rollup (when @TEDetail = 0) SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Division] AS DivisionName, SUM(CAST(0.00 AS DECIMAL(18, 2))) AS CashIn, -- InOut — unable to find SUM(CAST(0.00 AS DECIMAL(18, 2))) AS CashOut, -- InOut — unable to find SUM(ISNULL(td.[Transaction Amount], 0)) AS NetAmount, COUNT(*) AS LineCount FROM se_reports.dbo.[udv_transaction_detail_34] td WHERE td.[Transaction Date] BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR td.[Division] = @DivisionName ) AND (@TEFileNo IS NULL OR @TEFileNo = '' OR td.[File #] = @TEFileNo) AND @TEDetail = 0 GROUP BY td.[Corp Code], td.[Org Name], td.[File #], td.[Last Name], td.[First Name], td.[Division] ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #];