set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO


ALTER  PROCEDURE [dbo].[usp_MLSReport_LRR] 
    @UserId int,
    @ReportGroupId int = null,
    @BranchTranType varchar(50) = null,
    @BranchDivision varchar(50) = null,
    @BranchRegion varchar(50) = null,
    @BranchNbr int = null,
    @BranchSatId varchar(1) = null,
    @BeginDate datetime,
    @EndDate datetime 
AS

-- DECLARE  @UserId int,
--  @ReportGroupId int,
--  @BranchTranType varchar(50),
--  @BranchDivision varchar(50),
--  @BranchRegion varchar(50),
--  @BranchNbr int,
--  @BranchSatId varchar(1),
--  @BeginDate datetime,
--  @EndDate datetime 
-- 
-- SET  @UserId = 4602
-- --SET    @ReportGroupId = 46
-- SET  @BranchDivision = 'DENVER DIRECT'
-- SET  @BeginDate = '4/27/05'
-- SET  @EndDate = '9/30/05'

SET NOCOUNT ON

DECLARE @UseCTD int

SET @UseCTD = CASE WHEN @BranchTranType IS NULL  
            AND @BranchDivision IS NULL 
            AND @BranchRegion IS NULL 
            AND @BranchNbr IS NULL 
            AND @BranchSatId IS NULL THEN 1 ELSE 0 END


 

SELECT  DISTINCT
    L.ProviderId,
    L.OriginalProviderId,
    S.SourceCode,
    SG.SourceGroupCode,
    SGDescription=SG.Description,
    RG.ReportGroupId ,
    ReportGroupDesc=RG.Description ,
    L.VendorId,
    VEN.VendorName,
    B2.BranchTranType,
    B2.BranchDivision,
    B2.BranchRegion,
    B2.BranchName,
    L.BranchNbr,
    L.BranchSatId ,
    Branch=CONVERT(varchar, L.BranchNbr) + L.BranchSatId,
    LoNbr = ISNULL(L.LONbr, 9999),
    LoName = ISNULL(LO.LOName, ' - NOT ASSIGNED -') ,
    LoEmploymentStatus = LO.EmploymentStatus,
    LeadCount = 1,
    Completed = CASE WHEN L.RecCompleted = 'C' THEN 1 ELSE 0 END,
    InCompleted = CASE WHEN L.RecCompleted = 'I' THEN 1 ELSE 0 END ,
    NoContact = CASE WHEN LD.Disposition = 16 THEN 1 ELSE 0 END,
    Contacted = CASE WHEN LD.Disposition <> 16 THEN 1 ELSE 0 END,
    Pending = CASE WHEN LD.Disposition IS NULL THEN 1 ELSE 0 END,
    NoSale  = CASE WHEN LD.Disposition <> 1 THEN 1 ELSE 0 END,
    Apps1003 = CASE WHEN LD.Disposition = 1 THEN 1 ELSE 0 END ,
    AppraisalsOrdered = CASE WHEN ISNULL(T3._@8034, 0) = 0 THEN 0 ELSE 1 END,
    ClosedApps= CASE WHEN (@UseCTD = 1 AND SMT2._H0770 IS NOT NULL) OR SMT._H0770 IS NOT NULL THEN 1 ELSE 0 END,    
    LoanVolume = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(T1._@2026, 0) ELSE ISNULL(T1._@2026, 0) END,
    OrigLoanBalance = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2._H0360, 0) ELSE ISNULL(SMT._H0360, 0) END,
    Revenue = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2.calc_TotalIncomeDollars, 0) ELSE ISNULL(SMT.calc_TotalIncomeDollars, 0) END,
    MarketingCost = CASE WHEN L.VendorId IN (59, 60) THEN 0 ELSE ISNULL(S.Cost, 0) END,
    CAM.LeadTypeId,
    SMT.calc_TotalIncomeDollars,
    SP.SubjectAddrState,
    SP.SubjectSCF,
    S.LeadFormLabel
FROM    MLS..tbl_MLS_Leads L (NOLOCK)
    INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId)
    INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId)
    INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId)
    INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr)
    INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId)
    LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr)
    LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN)
    LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr)
    LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr)
    LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM)
    LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM)
    LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId)
    LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId) 
    LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID) 
    LEFT JOIN (
                            SELECT
                                LONbr,
                                LOName = FullName,
                                EmploymentStatus,
                                ReHireDate,
                                HireDate,
                                TermDate

                            FROM    MLS..vw_MLS_UsersWithLONbrs (NOLOCK)
                        ) LO ON (LO.LONbr = L.LONbr)
    LEFT JOIN MLS..tbl_MLS_Campaigns CAM (NOLOCK) ON (CAM.CampaignId = SG.CampaignId)
    LEFT JOIN MLS..tbl_MLS_Vendors VEN (NOLOCK) ON (VEN.VendorId = L.VendorId)
     
WHERE   L.DateReceived BETWEEN @BeginDate AND @EndDate + ' 23:59:59'
AND (CASE WHEN @ReportGroupId IS NULL THEN 1 WHEN @ReportGroupId = RG.ReportGroupId THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchTranType IS NULL THEN 1 WHEN @BranchTranType = B2.BranchTranType THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchDivision IS NULL THEN 1 WHEN @BranchDivision = B2.BranchDivision THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchRegion IS NULL THEN 1 WHEN @BranchRegion = B2.BranchRegion THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchNbr IS NULL THEN 1 WHEN @BranchNbr = L.BranchNbr THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchSatId IS NULL THEN 1 WHEN @BranchSatId = L.BranchSatId THEN 1 ELSE 0 END) = 1
AND L.ProviderId = L.OriginalProviderId
AND L.VendorId NOT IN (59, 60, 131)         -- Exclude Turndown or Ghost leads
AND L.Deleted = 0

FROM    MLS..tbl_MLS_Leads L (NOLOCK)
    INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId)
    INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId)
    INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId)
    INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr)
    INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId)
    INNER JOIN MLS..vw_MLS_Leads.DateReceived
	LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr)
    LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN)
    LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr)
    LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr)
    LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM)
    LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM)
    LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId)
    LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId) 
    LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID) 

quote:

---

It is not in the table only in the view.

---