/* Estimated Referral Revenue Estimated side mirrors pr_EstReferralRevenue / Crystal (ReloAction): DEPT = tblTEResaleClose + listing ReferralFeePercent / AgentCommission DEST = vwPurchases CalculatedReferral / EstimatedReferral / MonthToCOE = Crystal formulas Actual Referral (Crystal Shared NumberVar DOTAReferral) comes from accounting DOTA fee codes FEE-310 / FEE-290 / FEE-291, loaded into DatawarehouseNCM.dbo.FactEstReferralDOTA by PrcPopulateDatawarehouseTables (from TempETLFRData Process=GEN). Run estimate inserts against ReloAction. FactEstReferralDOTA must be reachable (same instance: DatawarehouseNCM.dbo... ; or adjust linked-server prefix). */ DECLARE @tblTERptTempFile TABLE ( CorpCode varchar(10), TEFileno varchar(14), EstCOE datetime, LineItem money, Type varchar(10), CompleteDate datetime, ExpenseCol1 decimal(18, 4), ExpenseCol2 decimal(18, 4), ExpenseCol3 decimal(18, 4), MonthToCOE decimal(10, 1), EstimatedReferral money, ActualReferralAmount money, DateReceived datetime ); --Get Dept Referral BEGIN TRANSACTION; INSERT INTO @tblTERptTempFile ( CorpCode, TEFileno, EstCOE, LineItem, Type, CompleteDate, ExpenseCol1, ExpenseCol2 ) SELECT tblTEmain1.CorpCode, TblTEMain1.TEFileNo, tblTEResaleClose.EstCOE, tblTEResaleClose.ResaleAmount, 'DEPT', tblTEResaleClose.ActualCOE, ( SELECT TOP 1 ReferralFeePercent FROM dbo.tblTEListingInfo LEFT OUTER JOIN dbo.tblTEListingDates ON tblTEListingInfo.ListingID = tblTEListingDates.ListingID WHERE TEFileNo = tblTEMain1.tefileno AND Resale = 1 ORDER BY ListingStartDate DESC ), ( SELECT TOP 1 AgentCommission FROM dbo.tblTEListingInfo LEFT OUTER JOIN dbo.tblTEListingDates ON tblTEListingInfo.ListingID = tblTEListingDates.ListingID WHERE TEFileNo = tblTEMain1.tefileno AND Resale = 1 ORDER BY ListingStartDate DESC ) FROM dbo.tblTEMain1 INNER JOIN dbo.tblCorpClient ON tblTEMain1.CorpCode = tblCorpClient.CorpCode INNER JOIN dbo.tblTEResaleClose ON tblTEMain1.TEFileno = tblTEResaleClose.TEFileno WHERE tblTEResaleClose.CancelDate IS NULL AND tblTEResaleClose.EstCOE IS NOT NULL AND tblTEResaleClose.EstCOE >= '6/25/2026' AND tblTEResaleClose.EstCOE <= '7/02/2026' AND tblTEMain1.Corpcode <> 'GLC2' AND tblTEMain1.Corpcode <> 'ACME' AND tbltemain1.DisplayType = 'C'; --Get Dest referral INSERT INTO @tblTERptTempFile ( CorpCode, TEFileno, EstCOE, LineItem, Type, CompleteDate, ExpenseCol1, ExpenseCol2 ) SELECT tblTEmain1.CorpCode, TblTEMain1.TEFileNo, vwPurchases.EstCOE, vwPurchases.SalePrice, 'DEST', vwPurchases.ActualCOE, ReferralFeePercent, AgentCommission FROM dbo.tblTEMain1 INNER JOIN dbo.tblCorpClient ON tblTEMain1.CorpCode = tblCorpClient.CorpCode LEFT OUTER JOIN vwPurchases ON tblTEMain1.TEFileno = vwPurchases.TEFileno WHERE vwPurchases.CurrentSale = 1 AND vwPurchases.EstCOE IS NOT NULL AND EstCOE >= '1/1/2025' AND EstCOE <= '1/1/2026' AND tblTEMain1.Corpcode <> 'GLC2' AND tblTEMain1.Corpcode <> 'ACME' AND tbltemain1.DisplayType = 'C'; UPDATE @tblTERptTempFile SET ExpenseCol3 = ((LineItem * (ExpenseCol2 / 100)) * (ExpenseCol1 / 100)); UPDATE @tblTERptTempFile SET EstimatedReferral = CASE WHEN ExpenseCol1 = 0 THEN 0 ELSE ((LineItem * 0.03) * (ExpenseCol1 / 100.0)) END; UPDATE t SET MonthToCOE = CASE WHEN t.EstCOE >= m.AddDate THEN ROUND(DATEDIFF(DAY, m.AddDate, t.EstCOE) / 31.0, 1) ELSE 0 END FROM @tblTERptTempFile t INNER JOIN dbo.tblTEMain1 m ON t.TEFileno = m.TEFileno; -- Crystal DOTAReferral: SUM of DOTA fee amounts; Date Received = latest TrxDate UPDATE t SET ActualReferralAmount = d.ActualReferralAmount, DateReceived = d.DateReceived FROM @tblTERptTempFile t INNER JOIN ( SELECT TEFileNo, SUM(Amount) AS ActualReferralAmount, MAX(TrxDate) AS DateReceived FROM DatawarehouseNCM.dbo.FactEstReferralDOTA GROUP BY TEFileNo ) d ON d.TEFileNo = t.TEFileno; SELECT CorpCode, TEFileno, EstCOE, LineItem as ResaleAmount, Type, CompleteDate as ActualCOE, ExpenseCol1 as ReferralPercentage, ExpenseCol2 as AgentCommission, ExpenseCol3 as CalculatedReferral, MonthToCOE, EstimatedReferral, ActualReferralAmount, DateReceived FROM @tblTERptTempFile;