/* Imprest Account — transactional extract (RAC + ReloAction-linked TE attrs) BackOffice: frmRptImprestAccount Crystal: ImprestAccount.rpt SP: dbo.RA_ImprestAccount + form PopulateTable post-process Mirrors BO for one client: 1) TE universe: DisplayType='C' OR ImprestAcct=1 (DISTINCT — BO can insert both) 2) DOTA In (SOP/CM/GLR) on [@StartDate,@EndDate] 3) DOTA Out (APP/DM/GLP) on [@StartDate-CheckLag,@EndDate] 4) Check lag on Out checks (non-GLP, DocumentNo<>'CASH') 5) Synthetic Out for incoming FEE (except GLR) 6) ISAG excludes EQU/LON Beginning balance: latest tblCorpAccountBalance on/before @StartDate (ReloAction). Ending balance (BO CalculateTotal): BegBalance + SUM(Amount) in date window. Run: DOTA portion on RAC. Balance query needs ReloAction (or linked server). If ReloAction is not local, run the balance SELECT on ReloAction separately. */ DECLARE @CorpCode varchar(15) = 'KPHC'; -- required DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @DivisionName varchar(30) = NULL; -- NULL / '' / '(All Divisions)' = all DECLARE @CheckLagDays smallint = 6; -- form txtChecklag default DECLARE @CheckStartDate datetime = DATEADD(day, -@CheckLagDays, @StartDate); IF OBJECT_ID('tempdb..#ImprestTE') IS NOT NULL DROP TABLE #ImprestTE; CREATE TABLE #ImprestTE ( TEFileNo varchar(15) NOT NULL PRIMARY KEY, CorpCode varchar(15) NULL, FormalName varchar(40) NULL, TELastName varchar(50) NULL, TEFirstName varchar(50) NULL, DivisionName varchar(30) NULL, ImprestAcct bit NULL, DisplayType varchar(1) NULL ); -- Prefer ReloAction TE attrs when available via synonym/link; else RAC tblTEMain1 INSERT INTO #ImprestTE (TEFileNo, CorpCode, FormalName, TELastName, TEFirstName, DivisionName, ImprestAcct, DisplayType) SELECT DISTINCT RTRIM(m.TEFileNo), RTRIM(m.CorpCode), cl.FormalName, m.TELastName, m.TEFirstName, m.DivisionName, m.ImprestAcct, m.DisplayType FROM dbo.tblTEMain1 m INNER JOIN dbo.tblCorpClient cl ON m.CorpCode = cl.CorpCode WHERE m.CorpCode = @CorpCode AND (m.DisplayType = 'C' OR m.ImprestAcct = 1) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR m.DivisionName = @DivisionName ); IF OBJECT_ID('tempdb..#ImprestTxn') IS NOT NULL DROP TABLE #ImprestTxn; CREATE TABLE #ImprestTxn ( TEFileNo varchar(15) NULL, PhsCtg varchar(7) NULL, Amount money NULL, SourceTrxDate datetime NULL, TrxDate datetime NULL, ServiceCode varchar(5) NULL, ServiceType smallint NULL, InOut varchar(1) NULL, Source char(3) NULL, DocumentNo varchar(21) NULL, CustVendNo varchar(15) NULL, IsCheck bit NOT NULL, IsFeeOffset bit NOT NULL, CashType varchar(7) NULL, GLDescription varchar(31) NULL, GLJrnEntry int NULL ); INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.Amount, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'I', 'SOP', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTAReceipts v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.TrxDate BETWEEN @StartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.Amount, v.DOCDATE, v.DOCDATE, v.ServiceCode, v.ServiceType, 'I', 'CM', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTACRMemos v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.DOCDATE BETWEEN @StartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.Amount, v.TRXDATE, v.TRXDATE, v.ServiceCode, v.ServiceType, 'I', 'GLR', '', '', 0, 0, 'CASH', v.GLDescription, v.GLJrnEntry FROM dbo.vwDOTAGLReceiptsGP v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.TRXDATE BETWEEN @StartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.DEBITAMT, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'O', 'APP', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTAPaymentsOps v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.DEBITAMT <> 0 AND v.TrxDate BETWEEN @CheckStartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.CREDITAMT, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'O', 'DM', v.DMVOUCHER, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTAPaymentsOps v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.CREDITAMT > 0 AND v.TrxDate BETWEEN @CheckStartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.DEBITAMT, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'O', 'APP', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTADebitMemos v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.DEBITAMT < 0 AND v.TrxDate BETWEEN @CheckStartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.CRDTAMNT, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'O', 'DM', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTADebitMemos v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.CRDTAMNT > 0 AND v.TrxDate BETWEEN @CheckStartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.DEBITAMT, v.TrxDate, v.TrxDate, v.ServiceCode, v.ServiceType, 'O', 'APP', v.DocumentNo, v.CustVendNo, 0, 0, 'CASH', '', 0 FROM dbo.vwDOTAPaymentsByDM v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.TrxDate BETWEEN @CheckStartDate AND @EndDate; INSERT INTO #ImprestTxn SELECT RTRIM(v.GROUPID), v.CODEID, v.Amount, v.TRXDATE, v.TRXDATE, v.ServiceCode, v.ServiceType, 'O', 'GLP', '', '', 0, 0, 'CASH', v.GLDescription, v.GLJrnEntry FROM dbo.vwDOTAGLPaymentsGP v INNER JOIN #ImprestTE te ON RTRIM(v.GROUPID) = te.TEFileNo WHERE v.TRXDATE BETWEEN @CheckStartDate AND @EndDate; IF @CorpCode = 'ISAG' DELETE FROM #ImprestTxn WHERE SUBSTRING(PhsCtg, 1, 3) IN ('EQU', 'LON'); UPDATE #ImprestTxn SET TrxDate = DATEADD(day, @CheckLagDays, SourceTrxDate), IsCheck = 1 WHERE InOut = 'O' AND Source <> 'GLP' AND ISNULL(DocumentNo, '') <> 'CASH'; INSERT INTO #ImprestTxn SELECT TEFileNo, PhsCtg, Amount * -1, SourceTrxDate, TrxDate, ServiceCode, ServiceType, 'O', Source, DocumentNo, CustVendNo, 0, 1, 'CASH', '', 0 FROM #ImprestTxn WHERE InOut = 'I' AND LEFT(PhsCtg, 3) = 'FEE' AND Source <> 'GLR'; -- Detail (Crystal Pm-TEDetail style) SELECT te.CorpCode, te.FormalName, te.TEFileNo, te.TELastName, te.TEFirstName, te.DivisionName, t.PhsCtg, t.Amount, t.SourceTrxDate, t.TrxDate, t.ServiceCode, t.InOut, t.Source, t.DocumentNo, t.CustVendNo, t.IsCheck, t.IsFeeOffset, t.GLDescription FROM #ImprestTxn t INNER JOIN #ImprestTE te ON t.TEFileNo = te.TEFileNo WHERE t.TrxDate BETWEEN @StartDate AND @EndDate ORDER BY t.TrxDate, te.TEFileNo, t.InOut, t.Source; -- Daily net activity SELECT CAST(t.TrxDate AS date) AS TrxDate, SUM(CASE WHEN t.InOut = 'I' THEN t.Amount ELSE 0 END) AS CashIn, SUM(CASE WHEN t.InOut = 'O' THEN t.Amount ELSE 0 END) AS CashOut, SUM(t.Amount) AS NetAmount FROM #ImprestTxn t WHERE t.TrxDate BETWEEN @StartDate AND @EndDate GROUP BY CAST(t.TrxDate AS date) ORDER BY TrxDate; -- Period net (BO CalculateTotal uses SUM(Amount)) SELECT COUNT(*) AS LineCount, SUM(t.Amount) AS PeriodNetAmount FROM #ImprestTxn t WHERE t.TrxDate BETWEEN @StartDate AND @EndDate; /* -- Beginning balance (run on ReloAction if not local): SELECT TOP (1) CorpCode, BalanceDate, Balance FROM dbo.tblCorpAccountBalance WHERE CorpCode = @CorpCode AND BalanceDate <= @StartDate ORDER BY BalanceDate DESC; */