/* Client Synopsis - client-level cash position (as-of) Crystal: ClientSynopsis.rpt BO: frmRptClientSynopsis -> dbo.RA_ClientSynopsisNew (@SumType='B', @TEFilter='CorpCode') This mirrors the client-level (non-TE-detail) path of RA_ClientSynopsisNew: - Cash In (InOut 'I'): DOTA receipts + CR memo receipts + GL receipts, non-FEE CODEID - Cash Out (InOut 'O'): AP payments, debit memos, DM-only payments, GL payments; excludes FEE-315 - Voided-voucher add-back (balance only, when the as-of date is in the past) Accrued/SOP-billing rows only apply to the TE-detail path (@TEFilter='GROUPID') and are intentionally excluded here. Active TEs only (exclude tblTESBTOld), same as the BO tblTmpTEFileNos load. Filter: transactions on/before @ReportDate (balance). Set @DailyOnly = 1 to see one day's activity (= @ReportDate). Run on RAC. */ DECLARE @ReportDate datetime = CAST(GETDATE() AS date); DECLARE @CorpCode varchar(15) = NULL; -- NULL = all clients DECLARE @DailyOnly bit = 0; -- 0 = balance (<= @ReportDate); 1 = daily (= @ReportDate) IF OBJECT_ID('tempdb..#Txn') IS NOT NULL DROP TABLE #Txn; CREATE TABLE #Txn ( CorpCode varchar(15) NULL, InOut varchar(1) NULL, CashType varchar(7) NULL, Amount money NULL, TrxDate date NULL, IsBalanceOnly bit NOT NULL ); -- ---------- Cash In (receipts) ---------- INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'I', 'CASH', SUM(v.Amount), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTAReceipts v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE SUBSTRING(v.CODEID, 1, 3) <> 'FEE' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'I', 'CASH', SUM(v.Amount), CAST(v.DOCDATE AS date), 0 FROM dbo.vwDOTACRMemos v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE SUBSTRING(v.CODEID, 1, 3) <> 'FEE' AND v.DOCDATE <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.DOCDATE AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'I', 'CASH', SUM(v.Amount), CAST(v.TRXDATE AS date), 0 FROM dbo.vwDOTAGLReceiptsGP v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE SUBSTRING(v.CODEID, 1, 3) <> 'FEE' AND v.TRXDATE <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TRXDATE AS date); -- ---------- Cash Out (payments) ---------- INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.DEBITAMT), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTAPaymentsOps v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.DEBITAMT <> 0 AND v.CODEID <> 'FEE-315' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.CREDITAMT), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTAPaymentsOps v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.CREDITAMT > 0 AND v.CODEID <> 'FEE-315' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.DEBITAMT), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTADebitMemos v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.DEBITAMT < 0 AND v.CODEID <> 'FEE-315' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.CRDTAMNT), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTADebitMemos v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.CRDTAMNT > 0 AND v.CODEID <> 'FEE-315' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.DEBITAMT), CAST(v.TrxDate AS date), 0 FROM dbo.vwDOTAPaymentsByDM v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.CODEID <> 'FEE-315' AND v.TrxDate <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TrxDate AS date); INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(v.Amount), CAST(v.TRXDATE AS date), 0 FROM dbo.vwDOTAGLPaymentsGP v INNER JOIN dbo.tblTEMain1 m ON v.GROUPID = m.TEFileNo WHERE v.CODEID <> 'FEE-315' AND v.TRXDATE <= @ReportDate AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(v.TRXDATE AS date); -- ---------- Voided vouchers add-back (balance only, as-of in the past) ---------- IF @ReportDate < CAST(GETDATE() AS date) INSERT INTO #Txn (CorpCode, InOut, CashType, Amount, TrxDate, IsBalanceOnly) SELECT RTRIM(m.CorpCode), 'O', 'CASH', SUM(-v.DEBITAMT), CAST(h.DOCDATE AS date), 1 FROM dbo.mdPMDistWORK d INNER JOIN dbo.PM30600 v ON d.VCHRNMBR = v.VCHRNMBR AND d.DSTSQNUM = v.DSTSQNUM AND d.APTVCHNM = v.APTVCHNM AND d.CNTRLTYP = v.CNTRLTYP AND d.SPCLDIST = v.SPCLDIST INNER JOIN dbo.PM30200 h ON v.VCHRNMBR = h.VCHRNMBR INNER JOIN dbo.tblTEMain1 m ON d.GROUPID = m.TEFileNo WHERE h.DOCDATE <= @ReportDate AND h.VOIDPDATE > @ReportDate AND h.VOIDED = 1 AND v.DEBITAMT <> 0 AND d.CODEID <> 'FEE-315' AND m.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTOld) AND (@CorpCode IS NULL OR m.CorpCode = @CorpCode) GROUP BY RTRIM(m.CorpCode), CAST(h.DOCDATE AS date); -- ---------- Synopsis by client ---------- ;WITH s AS ( SELECT CorpCode, InOut, SUM(Amount) AS Amount FROM #Txn WHERE (@DailyOnly = 0) -- balance: everything <= @ReportDate OR (@DailyOnly = 1 AND TrxDate = @ReportDate AND IsBalanceOnly = 0) -- daily: just @ReportDate GROUP BY CorpCode, InOut ) SELECT s.CorpCode, c.FormalName, SUM(CASE WHEN s.InOut = 'I' THEN s.Amount ELSE 0 END) AS CashIn, SUM(CASE WHEN s.InOut = 'O' THEN s.Amount ELSE 0 END) AS CashOut, SUM(CASE WHEN s.InOut = 'I' THEN s.Amount ELSE 0 END) - SUM(CASE WHEN s.InOut = 'O' THEN s.Amount ELSE 0 END) AS NetCash FROM s LEFT JOIN dbo.tblCorpClient c ON RTRIM(c.CorpCode) = s.CorpCode GROUP BY s.CorpCode, c.FormalName ORDER BY s.CorpCode; IF OBJECT_ID('tempdb..#Txn') IS NOT NULL DROP TABLE #Txn;