/* Fee Transfer — Paid Crystal: FeeTransferPaid.rpt BO: frmCashReceiptsPosting.GetFees() then Crystal (Pm-Initials, Pm-Detail) GetFees (CnAcctg / RAC) loads tblTmpFRRptData: 1) Paid FEE invoices from vwFeeTransferPaid where POSTEDDT in date range (POSTEDDT = cash receipt apply date APFRDCDT) 2) Credit memos from vwFeeTransferCM for those SOP numbers (TrxDate stored as @EndDate, not CM POSTEDDT) 3) Credit memos from vwFeeTransferCMHistory for those SOP numbers (TrxDate stored as @EndDate) This script mirrors that without Initials / tblTmpFRRptData — for verification. Run on RAC. Pm-Detail = 1 → use detail result; 0 → use summary. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @FeeRows TABLE ( DocumentNo varchar(21) NOT NULL, TrxDate datetime NULL, PhsCtg varchar(20) NULL, -- CODEID Amount money NULL, ServiceCode varchar(10) NULL, RowSource varchar(20) NOT NULL -- Paid / CM / CMHistory ); -- 1) Paid FEE invoices (same as GetFees insert from vwFeeTransferPaid) INSERT INTO @FeeRows (DocumentNo, TrxDate, PhsCtg, Amount, ServiceCode, RowSource) SELECT SOPNUMBE, POSTEDDT, CODEID, AMOUNT, ServiceCode, 'Paid' FROM dbo.vwFeeTransferPaid WHERE POSTEDDT >= @StartDate AND POSTEDDT <= @EndDate; -- 2) CMs current — only for SOPs already in paid set; TrxDate = @EndDate (BO behavior) INSERT INTO @FeeRows (DocumentNo, TrxDate, PhsCtg, Amount, ServiceCode, RowSource) SELECT SOPNUMBE, @EndDate, CODEID, Amount, ServiceCode, 'CM' FROM dbo.vwFeeTransferCM WHERE SOPNUMBE IN (SELECT DISTINCT DocumentNo FROM @FeeRows WHERE RowSource = 'Paid'); -- 3) CMs history — same SOP filter; TrxDate = @EndDate INSERT INTO @FeeRows (DocumentNo, TrxDate, PhsCtg, Amount, ServiceCode, RowSource) SELECT SOPNUMBE, @EndDate, CODEID, Amount, ServiceCode, 'CMHistory' FROM dbo.vwFeeTransferCMHistory WHERE SOPNUMBE IN (SELECT DISTINCT DocumentNo FROM @FeeRows WHERE RowSource = 'Paid'); -- Detail (Crystal Pm-Detail = checked) — grain of tblTmpFRRptData SELECT DocumentNo AS InvoiceNo, TrxDate, PhsCtg AS CodeId, ServiceCode, Amount, RowSource FROM @FeeRows ORDER BY DocumentNo, RowSource, PhsCtg, ServiceCode; -- Summary by invoice (typical Crystal rollup when detail off) SELECT DocumentNo AS InvoiceNo, MIN(CASE WHEN RowSource = 'Paid' THEN TrxDate END) AS PaidDate, SUM(Amount) AS NetFeeAmount, SUM(CASE WHEN RowSource = 'Paid' THEN Amount ELSE 0 END) AS PaidInvoiceAmount, SUM(CASE WHEN RowSource IN ('CM', 'CMHistory') THEN Amount ELSE 0 END) AS CreditMemoAmount FROM @FeeRows GROUP BY DocumentNo ORDER BY InvoiceNo; -- Grand totals SELECT COUNT(DISTINCT DocumentNo) AS InvoiceCount, SUM(Amount) AS NetFeeAmount, SUM(CASE WHEN RowSource = 'Paid' THEN Amount ELSE 0 END) AS PaidInvoiceAmount, SUM(CASE WHEN RowSource IN ('CM', 'CMHistory') THEN Amount ELSE 0 END) AS CreditMemoAmount FROM @FeeRows;