/* Imprest Account — detail (DatawarehouseNCM) Facts: dbo.FactImprestAccount BackOffice: frmRptImprestAccount / Crystal: ImprestAccount.rpt TrxDate is post check-lag (report date). SourceTrxDate is the original DOTA date. */ DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = all imprest clients in fact DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @DivisionName varchar(30) = NULL; DECLARE @TEFileNo varchar(15) = NULL; DECLARE @TEDetail bit = 1; -- 1 = line detail; 0 = TE rollup -- Line detail SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, f.ImprestAcct, f.DisplayType, f.PhsCtg, f.Amount, f.SourceTrxDate, f.TrxDate, f.ServiceCode, f.ServiceType, f.InOut, f.Source, f.DocumentNo, f.CustVendNo, f.IsCheck, f.IsFeeOffset, f.CashType, f.GLDescription, f.GLJrnEntry, f.CheckLagDays FROM dbo.FactImprestAccount f WHERE f.TrxDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR f.DivisionName = @DivisionName ) AND (@TEFileNo IS NULL OR @TEFileNo = '' OR f.TEFileNo = @TEFileNo) AND @TEDetail = 1 ORDER BY f.CorpCode, f.TrxDate, f.TEFileNo, f.InOut, f.Source, f.PhsCtg; -- TE rollup (when @TEDetail = 0) SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, SUM(CASE WHEN f.InOut = 'I' THEN f.Amount ELSE 0 END) AS CashIn, SUM(CASE WHEN f.InOut = 'O' THEN f.Amount ELSE 0 END) AS CashOut, SUM(f.Amount) AS NetAmount, COUNT(*) AS LineCount FROM dbo.FactImprestAccount f WHERE f.TrxDate BETWEEN @StartDate AND @EndDate AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR f.DivisionName = @DivisionName ) AND (@TEFileNo IS NULL OR @TEFileNo = '' OR f.TEFileNo = @TEFileNo) AND @TEDetail = 0 GROUP BY f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo;