/* Mortgage Referrals Sent Report BackOffice: frmRptMortgageRefSent Crystal: MortgageReferralSent.rpt DW fact: DatawarehouseNCM.dbo.FactMortgageReferralSent (loaded by PrcPopulateDatawarehouseTables from tblTEMortgage + company/contact) Filter by DimConsultantID / consultant (service provider = PrimaryContact). No profit center or initials on the fact. Source tables tblTEMortgage / tblCompany / tblCompanyContacts are not otherwise in DW dims. */ -- ============================================================================= -- DW report query (run on DatawarehouseNCM after populate) -- ============================================================================= /* DECLARE @StartDate DATETIME = '2026-01-01'; DECLARE @EndDate DATETIME = '2026-12-31'; DECLARE @CorpCode VARCHAR(4) = NULL; DECLARE @DimConsultantID INT = NULL; -- service provider filter 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, 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, dm.CorpCode, dm.FormalName, 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 (@CorpCode IS NULL OR dm.CorpCode = @CorpCode) AND (@DimConsultantID IS NULL OR f.DimConsultantID = @DimConsultantID); */ -- ============================================================================= -- ReloAction source extract (BO field mapping / validation) -- ============================================================================= /* tblTEMortgage tblCompany tblCompanyContacts These 3 tables are not in the data warehouse populate dims (only via FactMortgageReferralSent). */ /* Query to generate the sample report given by Ops */ SELECT m.ReferID, m.TEFileNo AS FileNumber, te.TELastName, te.TEFirstName, LTRIM(RTRIM(ISNULL(te.TELastName, '') + ', ' + ISNULL(te.TEFirstName, ''))) AS TransfereeName, te.DepartureOwnRent AS StatusHOvsRenter, case when m.RAMortgNotify =1 then 'Email Sent' else 'Not Sent' end AS Notification, LTRIM(RTRIM(ISNULL(sp.LastName, '') + ', ' + ISNULL(sp.FirstName, ''))) AS SenderName, m.ReferralDate, m.CompanyID, co.CompanyName AS MortgageCompanyName FROM dbo.tblTEMortgage m INNER JOIN dbo.tblCompany co ON m.CompanyID = co.CompanyID LEFT JOIN dbo.tblCompanyContacts cc ON m.ContactID = cc.ContactID INNER JOIN dbo.tblTEMain1 te ON m.TEFileNo = te.TEFileNo LEFT JOIN dbo.tblInitials sp ON te.PrimaryContact = sp.Initials LEFT JOIN dbo.tblRAProfitCenters pc ON sp.ProfitCenter = pc.ProfitCenter where m.TEFileNo='accx260003' ----------cursor generated query -------- SELECT m.ReferID, m.TEFileNo AS FileNumber, te.TELastName, te.TEFirstName, LTRIM(RTRIM(ISNULL(te.TELastName, '') + ', ' + ISNULL(te.TEFirstName, ''))) AS TransfereeName, te.DepartureOwnRent AS StatusHOvsRenter, case when m.RAMortgNotify =1 then 'Email Sent' else 'Not Sent' end AS Notification, m.Initials AS SenderInitials, te.PrimaryContact AS ConsultantInitials, sp.FirstName AS SenderFirstName, sp.LastName AS SenderLastName, LTRIM(RTRIM(ISNULL(sp.LastName, '') + ', ' + ISNULL(sp.FirstName, ''))) AS SenderName, m.ReferralDate, m.CompanyID, co.CompanyName AS MortgageCompanyName, m.ContactID, cc.FirstName AS ContactFirstName, cc.LastName AS ContactLastName, m.Selected, m.LoanAmount, m.LoanAppSent, m.BrokerFee, m.DirectBill, m.RSNNotReferred, m.RSNNotCaptured, m.ABADisclosureReceived, m.Comment, m.AddDate, m.LastUpdate, te.CorpCode, cl.FormalName, cl.ParentCompany, te.EmpNumber, sp.ProfitCenter, pc.Name AS ProfitCenterName, it.UserType AS TransfereeType FROM dbo.tblTEMortgage m INNER JOIN dbo.tblCompany co ON m.CompanyID = co.CompanyID LEFT JOIN dbo.tblCompanyContacts cc ON m.ContactID = cc.ContactID INNER JOIN dbo.tblTEMain1 te ON m.TEFileNo = te.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode LEFT JOIN dbo.tblInitials sp ON te.PrimaryContact = sp.Initials LEFT JOIN dbo.tblRAProfitCenters pc ON sp.ProfitCenter = pc.ProfitCenter LEFT JOIN dbo.tblInitials it ON te.PrimaryContact = it.Initials;