/* Mortgage Referrals Sent — detail (DatawarehouseNCM) Fact: dbo.FactMortgageReferralSent BackOffice: frmRptMortgageRefSent Crystal: MortgageReferralSent.rpt Grain: one referral row (ReferID). Service provider = DimConsultant (PrimaryContact). No profit center / initials on the fact. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-12-31'; DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = all DECLARE @ParentRollup bit = 0; DECLARE @DimConsultantID int = NULL; -- service provider; NULL = all DECLARE @TEFileNo varchar(14) = NULL; SELECT f.ReferID, f.TEFileNo AS FileNumber, dm.TEName AS TransfereeName, f.DepartureOwnRent AS StatusHOvsRenter, CASE WHEN f.RAMortgNotify = 1 THEN 'Email Sent' ELSE 'Not Sent' END AS Notification, c.ConsultantName AS ServiceProvider, c.Initials AS ConsultantInitials, f.ReferralDate, f.CompanyID, f.MortgageCompanyName, f.ContactID, LTRIM(RTRIM(ISNULL(f.ContactLastName, '') + ', ' + ISNULL(f.ContactFirstName, ''))) AS ContactName, f.Selected, f.LoanAmount, f.LoanAppSent, f.BrokerFee, f.DirectBill, f.RSNNotReferred, f.RSNNotCaptured, f.ABADisclosureReceived, f.Comment, f.AddDate, f.LastUpdate, dm.CorpCode, dm.FormalName, dm.ParentCompany, f.DomesticIntl, f.DisplayType FROM dbo.FactMortgageReferralSent f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID LEFT JOIN dbo.DimConsultant c ON c.DimConsultantID = f.DimConsultantID WHERE (@StartDate IS NULL OR f.ReferralDate >= @StartDate) AND (@EndDate IS NULL OR f.ReferralDate <= @EndDate) AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 0 AND dm.CorpCode = @CorpCode) OR (@ParentRollup = 1 AND (dm.ParentCompany = @CorpCode OR dm.CorpCode = @CorpCode)) ) AND (@DimConsultantID IS NULL OR f.DimConsultantID = @DimConsultantID) AND (NULLIF(@TEFileNo, '') IS NULL OR f.TEFileNo = @TEFileNo) ORDER BY dm.CorpCode, f.ReferralDate, f.TEFileNo, f.ReferID;