= Sql.Database("rocktopanalytics.public.eed85c94293a.database.windows.net,3342", "products", [Query="#(lf)#(lf)#(lf)#(lf)drop table if exists #pop#(lf)drop table if exists #data#(lf)drop table if exists #roll#(lf)drop table if exists #grades#(lf)drop table if exists #title#(lf)drop table if exists #legal#(lf)drop table if exists #docs#(lf)drop table if exists #property#(lf)drop table if exists #sd#(lf)drop table if exists #voms#(lf)drop table if exists #temp#(lf)drop table if exists #cred#(lf)#(lf)#(lf)select LoanID=l.Id, t_p.Name ,l.ServicerLoanNumber, l.LoanNumber, l.LoanFundingDate, l.currentassetstatus, tl_p.Status, l.CurrentAssetType, l.UPBAmount, Value=pv.Value, loc.State,#(lf)TitleProductId=(select top 1 tp.Id from products.OrderLines ol inner join products.TitleProducts tp on ol.Id=tp.OrderLineId inner join products.Match m on tp.Id=m.TitleProductId and m.Match='Order Status' and m.MatchDesc like '%Delivered%' where l.Id=ol.LoanID)#(lf)#(lf)into #pop#(lf)from products.Loans l (NOLOCK) #(lf)  inner join products.TradeLoans tl_p (NOLOCK) on#(lf)    l.Id=tl_p.LoanId and tl_p.Type='Purchase'#(lf)  inner join products.Trades t_p (NOLOCK) on#(lf)    tl_p.Tradeid=t_p.Id and t_p.Type='Purchase'#(lf)  inner join products.Properties p (NOLOCK) on#(lf)    l.PropertyId=p.Id#(lf)  inner join products.Locations loc (NOLOCK) on#(lf)    p.AddressID=loc.Id#(lf)  left outer join reports.PropertyValues pv (NOLOCK) on#(lf)    pv.Id=(select TOP 1 pv.Id from reports.PropertyValues pv (NOLOCK) where l.Id=pv.LoanId order by case when pv.ValueType like '%Seller%' then 1 else 2 end)#(lf)where  t_p.Name in ('"&DealName&"')#(lf)#(lf)--and tl_p.Status='Due Diligence' #(lf)--and l.id in (2643265)#(lf)ORDER BY L.Id#(lf)--select* from #pop#(lf)-----------------------------ISSUES #(lf)select l.LoanNumber,LoanID=l.id,l.ServicerLoanNumber,l.CurrentAssetStatus,IssueMainId=im.Id,im.ReferenceTable,#(lf)Status=t.status,Module='Title',im.IssueCategory,PID=c.id,c.IssueType,c.IssueSubType,OriginalGrade=isnull(im.Grade,c.pidgrade),c.CurrentGrade,#(lf)MK_Tier=case when c.CurrentGrade='NI' then 1 else mk.MK_Tier end,Urgent=isnulL(imon.UrgentFlag, icur.UrgentFlag), imon.TransferFlag, PIDStatus=c.status,c.DateClosed,#(lf)OpenClosed=case when (isnull(c.ClosedReason,'x')='Opened in Error' or isnull(c.ResolutionObtained,'x')='Opened in Error') then 'Opened in Error'#(lf)#(tab) when  isnull(c.status,'x')  in ('Doc Curative Complete','Compliance Curative Complete','Title Curative Complete', 'Title Claim Complete', 'Title Claim Billed','Title Curative Liquidated') then 'Complete'#(lf)#(tab) when isnull(c.status,'x')  in ('Close - Standard Servicing','Closed - Standard Servicing','Closed','Closed - All Leads Exhausted') and isnull(c.ClosedReason,'x')<>'Opened in Error' then 'Closed'#(lf)#(tab) when isnull(c.status,'x') in ('Active Litigation', 'Sent to Litigation') then 'Curative Litigation'#(lf)#(tab) else 'Open' end,#(lf)c.ClosedReason,#(lf)c.ResolutionObtained,#(lf)IssueComplexity=upper(replace(c.IssueComplexity,' Complexity','')),#(lf)c.IssueDescription,im.DescriptionModifier,#(lf)c.ClosingComment,#(lf)Message1=m1.Subject + ' || ' + replace(replace(m1.Text,char(10),''),char(13),''),#(lf)Message2=m2.Subject + ' || ' + replace(replace(m2.Text,char(10),''),char(13),''),#(lf)Message3=m3.Subject + ' || ' + replace(replace(m3.Text,char(10),''),char(13),''),#(lf)HeavyLift=cast(NULL as varchar(5))#(lf)into #data#(lf)from products.loans l (NOLOCK)#(lf)inner join #pop p on l.Id= p.LoanId#(lf)join products.titles t (NOLOCK) on t.loanid=l.id#(lf)join products.titlecurative c (NOLOCK) on c.titleid=t.id #(lf)join products.issuemain im (NOLOCK) on im.titlecurativeid=c.id --and im.IssueStatus<>'Resolved at Diligence'#(lf)left outer join products.IssueMonitoring imon on im.Id=imon.IssueMainId#(lf)left outer join products.IssueCurative icur on im.Id=icur.IssueMainId#(lf)left outer join products.IssueMK_Tiers mk on#(lf) mk.Module='Title' and#(lf) ((c.ResolutionObtained is not null and c.ResolutionObtained=mk.ResolutionType and left(c.CurrentGrade,1)=isnull(mk.CurrentGrade,left(c.CurrentGrade,1))) or#(lf)  (c.ResolutionObtained is null and isnull(c.IssueType,im.IssueType)=mk.IssueType and isnull(c.IssueSubType,im.IssueSubType)=mk.IssueSubType and left(coalesce(c.CurrentGrade,im.Grade),1)=isnull(mk.CurrentGrade,left(coalesce(c.CurrentGrade,im.Grade),1))))#(lf) left outer join products.Messages m1 (NOLOCK) on#(lf)   m1.Id=(select TOP 1 m1.Id from products.Messages m1 (NOLOCK) where c.Id=m1.ParentId and m1.AppliesTo='Title Curative' order by m1.UpdatedAt desc)#(lf) left outer join products.Messages m2 (NOLOCK) on#(lf)   m2.Id=(select TOP 1 m2.Id from products.Messages m2 (NOLOCK) where c.Id=m2.ParentId and m2.AppliesTo='Title Curative' and m1.Id<>m2.Id order by m2.UpdatedAt desc)#(lf) left outer join products.Messages m3 (NOLOCK) on#(lf)   m3.Id=(select TOP 1 m3.Id from products.Messages m3 (NOLOCK) where c.Id=m3.ParentId and m3.AppliesTo='Title Curative' and m1.Id<>m3.Id and m2.Id<>m3.Id order by m3.UpdatedAt desc)#(lf)#(lf)union all#(lf)#(lf)#(lf)select l.LoanNumber,LoanID=l.id,l.ServicerLoanNumber,l.CurrentAssetStatus,IssueMainId=im.Id,im.ReferenceTable,#(lf)Status=t.status,Module='Compliance',im.IssueCategory,PID=c.id,c.IssueType,c.IssueSubType,OriginalGrade=im.Grade,c.CurrentGrade,#(lf)MK_Tier=case when c.CurrentGrade='NI' then 1 else mk.MK_Tier end,Urgent=isnulL(imon.UrgentFlag, icur.UrgentFlag), imon.TransferFlag, PIDStatus=c.status,c.DateClosed,#(lf)OpenClosed=case when (isnull(c.ClosedReason,'x')='Opened in Error' or isnull(c.ResolutionObtained,'x')='Opened in Error') then 'Opened in Error'#(lf)#(tab) when  isnull(c.status,'x')  in ('Doc Curative Complete','Compliance Curative Complete','Title Curative Complete', 'Title Claim Complete', 'Title Claim Billed','Title Curative Liquidated') then 'Complete'#(lf)#(tab) when isnull(c.status,'x')  in ('Close - Standard Servicing','Closed - Standard Servicing','Closed','Closed - All Leads Exhausted') and isnull(c.ClosedReason,'x')<>'Opened in Error' then 'Closed'#(lf)#(lf)#(tab) when isnull(c.status,'x') in ('Active Litigation', 'Sent to Litigation') then 'Curative Litigation'#(lf)#(tab) else 'Open' end,#(lf)c.ClosedReason,#(lf)c.ResolutionObtained,#(lf)IssueComplexity=upper(replace(c.IssueComplexity,' Complexity','')),#(lf)c.IssueDescription,im.DescriptionModifier,#(lf)c.ClosingComment,#(lf)Message1=m1.Subject + ' || ' + replace(replace(m1.Text,char(10),''),char(13),''),#(lf)Message2=m2.Subject + ' || ' + replace(replace(m2.Text,char(10),''),char(13),''),#(lf)Message3=m3.Subject + ' || ' + replace(replace(m3.Text,char(10),''),char(13),''),#(lf)HeavyLift=cast(NULL as varchar(5))#(lf)from products.loans l (NOLOCK)#(lf)inner join #pop p on l.Id=p.LoanId#(lf)join products.titles t (NOLOCK) on t.loanid=l.id#(lf)join products.Compliances cm (NOLOCK) on cm.LoanId=l.Id#(lf)join products.complianceCuratives c (NOLOCK) on c.complianceId=cm.id#(lf)left outer join products.issuemain im (NOLOCK) on im.ComplianceCurativeId=c.id #(lf)--and im.IssueStatus<>'Resolved at Diligence'#(lf)left outer join products.IssueMonitoring imon on im.Id=imon.IssueMainId#(lf)left outer join products.IssueCurative icur on im.Id=icur.IssueMainId#(lf)left outer join products.IssueMK_Tiers mk on#(lf) mk.Module='Compliance' and#(lf) ((c.ResolutionObtained is not null and c.ResolutionObtained=mk.ResolutionType and left(c.CurrentGrade,1)=isnull(mk.CurrentGrade,left(c.CurrentGrade,1))) or#(lf)  (c.ResolutionObtained is null and isnull(c.IssueType,im.IssueType)=mk.IssueType and isnull(c.IssueSubType,im.IssueSubType)=mk.IssueSubType and left(coalesce(c.CurrentGrade,im.Grade),1)=isnull(mk.CurrentGrade,left(coalesce(c.CurrentGrade,im.Grade),1))))#(lf) left outer join products.Messages m1 (NOLOCK) on#(lf)   m1.Id=(select TOP 1 m1.Id from products.Messages m1 (NOLOCK) where c.Id=m1.ParentId and m1.AppliesTo='Compliance Curative' order by m1.UpdatedAt desc)#(lf) left outer join products.Messages m2 (NOLOCK) on#(lf)   m2.Id=(select TOP 1 m2.Id from products.Messages m2 (NOLOCK) where c.Id=m2.ParentId and m2.AppliesTo='Compliance Curative' and m1.Id<>m2.Id order by m2.UpdatedAt desc)#(lf) left outer join products.Messages m3 (NOLOCK) on#(lf)   m3.Id=(select TOP 1 m3.Id from products.Messages m3 (NOLOCK) where c.Id=m3.ParentId and m3.AppliesTo='Compliance Curative' and m1.Id<>m3.Id and m2.Id<>m3.Id order by m3.UpdatedAt desc)#(lf)#(lf)#(lf)union all#(lf)#(lf)select l.LoanNumber,LoanID=l.id, l.ServicerLoanNumber,l.CurrentAssetStatus,IssueMainId=im.Id,im.ReferenceTable,#(lf)Status=t.status,Module='Docs',im.IssueCategory,PID=c.id,c.IssueType,c.IssueSubType,OriginalGrade=im.Grade,c.CurrentGrade,#(lf)MK_Tier=case when c.CurrentGrade='NI' then 1 else mk.MK_Tier end,Urgent=isnulL(imon.UrgentFlag, icur.UrgentFlag), imon.TransferFlag, PIDStatus=c.status,c.DateClosed,#(lf)OpenClosed=case when (isnull(c.ClosedReason,'x')='Opened in Error' or isnull(c.ResolutionObtained,'x')='Opened in Error') then 'Opened in Error'#(lf)#(tab) when  isnull(c.status,'x')  in ('Doc Curative Complete','Compliance Curative Complete','Title Curative Complete', 'Title Claim Complete', 'Title Claim Billed','Title Curative Liquidated') then 'Complete'#(lf)#(tab) when isnull(c.status,'x')  in ('Close - Standard Servicing','Closed - Standard Servicing','Closed','Closed - All Leads Exhausted') and isnull(c.ClosedReason,'x')<>'Opened in Error' then 'Closed'#(lf)#(lf)#(tab) when isnull(c.status,'x') in ('Active Litigation', 'Sent to Litigation') then 'Curative Litigation'#(lf)#(tab) else 'Open' end,#(lf)c.ClosedReason,#(lf)c.ResolutionObtained,#(lf)IssueComplexity=upper(replace(c.IssueComplexity,' Complexity','')),#(lf)c.IssueDescription,im.DescriptionModifier,#(lf)c.ClosingComment,#(lf)Message1=m1.Subject + ' || ' + replace(replace(m1.Text,char(10),''),char(13),''),#(lf)Message2=m2.Subject + ' || ' + replace(replace(m2.Text,char(10),''),char(13),''),#(lf)Message3=m3.Subject + ' || ' + replace(replace(m3.Text,char(10),''),char(13),''),#(lf)HeavyLift=cast(NULL as varchar(5))#(lf)from products.loans l (NOLOCK)#(lf)inner join #pop p on l.Id= p.LoanId#(lf)join products.titles t (NOLOCK) on t.loanid=l.id#(lf)join products.DocumentManagements dm (NOLOCK) on dm.LoanId=l.Id#(lf)join products.DocumentCuratives c (NOLOCK) on c.DocumentManagementId=dm.id#(lf)left outer join products.issuemain im (NOLOCK) on im.documentcurativeid=c.id --and im.IssueStatus<>'Resolved at Diligence'#(lf)left outer join products.IssueMonitoring imon on im.Id=imon.IssueMainId#(lf)left outer join products.IssueCurative icur on im.Id=icur.IssueMainId#(lf)left outer join products.IssueMK_Tiers mk on#(lf) mk.Module='Docs' and#(lf) ((c.ResolutionObtained is not null and c.ResolutionObtained=mk.ResolutionType and left(c.CurrentGrade,1)=isnull(mk.CurrentGrade,left(c.CurrentGrade,1))) or#(lf)  (c.ResolutionObtained is null and isnull(c.IssueType,im.IssueType)=mk.IssueType and isnull(c.IssueSubType,im.IssueSubType)=mk.IssueSubType and left(coalesce(c.CurrentGrade,im.Grade),1)=isnull(mk.CurrentGrade,left(coalesce(c.CurrentGrade,im.Grade),1))))#(lf) left outer join products.Messages m1 (NOLOCK) on#(lf)   m1.Id=(select TOP 1 m1.Id from products.Messages m1 (NOLOCK) where c.Id=m1.ParentId and m1.AppliesTo='Document Curative' order by m1.UpdatedAt desc)#(lf) left outer join products.Messages m2 (NOLOCK) on#(lf)   m2.Id=(select TOP 1 m2.Id from products.Messages m2 (NOLOCK) where c.Id=m2.ParentId and m2.AppliesTo='Document Curative' and m1.Id<>m2.Id order by m2.UpdatedAt desc)#(lf) left outer join products.Messages m3 (NOLOCK) on#(lf)   m3.Id=(select TOP 1 m3.Id from products.Messages m3 (NOLOCK) where c.Id=m3.ParentId and m3.AppliesTo='Document Curative' and m1.Id<>m3.Id and m2.Id<>m3.Id order by m3.UpdatedAt desc)#(lf)#(lf)union all#(lf)#(lf)select l.LoanNumber,LoanID=l.id, l.ServicerLoanNumber,l.CurrentAssetStatus,IssueMainId=im.Id,im.ReferenceTable,#(lf)Status='Non-Curative',Module=IssueModule,im.IssueCategory,PID=NULL,im.IssueType,im.IssueSubType,OriginalGrade=im.Grade,CurrentGrade=isnull(im.CurrentGrade,im.Grade),#(lf)MK_Tier=case when isnull(im.CurrentGrade,im.Grade)='NI' then 1 else mk.MK_Tier end, Urgent=isnulL(imon.UrgentFlag, icur.UrgentFlag), imon.TransferFlag, PIDStatus=im.IssueStatus,DateClosed=NULL,#(lf)OpenClosed=case when isnull(im.IssueStatus,'x')='Removed' then 'Opened in Error'#(lf)#(tab) when  isnull(im.IssueStatus,'x')  in ('Complete', 'Liquidated') then 'Complete'#(lf)#(tab) when isnull(im.IssueStatus,'x')  in ('Closed') then 'Closed'#(lf)#(tab) else 'Open' end,#(lf)ClosedReason=NULL,#(lf)ResolutionObtained=NULL,#(lf)IssueComplexity=NULL,#(lf)IssueDescription=im.IssueDescription,im.DescriptionModifier,#(lf)ClosingComment=NULL,#(lf)Message1=NULL,#(lf)Message2=NULL,#(lf)Message3=NULL,#(lf)HeavyLift=cast(NULL as varchar(5))#(lf)from products.loans l (NOLOCK)#(lf)inner join #pop p on l.Id=P.LoanId#(lf)inner join products.IssueMain im (NOLOCK) on#(lf) l.Id=im.LoanId and im.TitleCurativeID is null and im.ComplianceCurativeID is null and im.DocumentCurativeId is null --and im.IssueStatus='Resolved at Diligence'#(lf)left outer join products.IssueMonitoring imon on im.Id=imon.IssueMainId#(lf)left outer join products.IssueCurative icur on im.Id=icur.IssueMainId#(lf)left outer join products.IssueMK_Tiers mk on#(lf) mk.Module=im.IssueModule and#(lf) ((im.IssueType=mk.IssueType and im.IssueSubType=mk.IssueSubType and left(im.Grade,1)=isnull(mk.CurrentGrade,left(im.Grade,1))))#(lf)order by l.id#(lf)#(lf)--delete from #data where ReferenceTable='Seller Disclosure' and Module='Compliance'--#(lf)#(lf)alter table #data add Id int identity(1,1)#(lf)create unique index#(tab)q on #data (Id)#(lf)create index r on #data (LoanId)#(lf)create index s on #data (OpenClosed)#(lf)#(lf)update #data set HeavyLift='Y' where CurrentGrade<>'NI'#(lf)update #data set HeavyLift='N' where CurrentGrade='NI'#(lf)#(lf)select  #(lf)LoanID=l.ID, l.LoanNumber, l.ServicerLoanNumber#(lf),HeavyLift=case when (select count(1) from #data d where #pop.LoanId=d.LoanId and HeavyLift='Y')>0 then 'Y' else 'N' end#(lf),CurrentCombinedGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentCombinedMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf),CurrentTitleGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and p.Module='Title' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentTitleMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and p.Module='Title' and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and p.Module='Title' and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf),CurrentDocsGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and p.Module='Docs' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentDocsMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and p.Module='Docs' and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and p.Module='Docs' and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf),CurrentComplianceGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and p.Module='Compliance' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentComplianceMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and p.Module='Compliance' and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and p.Module='Compliance' and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf),CurrentLegalGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and p.Module='Legal' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentLegalMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and p.Module='Legal' and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and p.Module='Legal' and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf),CurrentPropertyGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(CurrentGrade)) from #data p where p.LoanId=l.Id and p.Module='Property' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),CurrentPropertyMKTier=case when (select count(1) from #data p where p.LoanId=l.Id and p.Module='Property' and isnull(p.OpenClosed,'x')<>'Opened in Error')=0 then 1 else (select max(MK_Tier) from #data p where p.LoanId=l.Id and p.Module='Property' and isnull(p.OpenClosed,'x')<>'Opened in Error') end#(lf)#(lf),OriginalCombinedGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),OriginalTitleGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and p.Module='Title' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),OriginalDocsGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and p.Module='Docs' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),OriginalComplianceGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and p.Module='Compliance' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),OriginalLegalGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and p.Module='Legal' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf),OriginalPropertyGrade=isnull(dbo.GradeRankGrade((select max(dbo.GradeRank(OriginalGrade)) from #data p where p.LoanId=l.Id and p.Module='Property' and isnull(p.OpenClosed,'x')<>'Opened in Error')),'NI')#(lf)#(lf)into #roll#(lf)from products.loans l (NOLOCK)#(lf)inner join #pop on l.Id=#pop.LoanId#(lf)------------------------------------------------#(lf)#(lf)#(lf)#(lf)#(lf)#(lf)select p.LoanID,p.Name, p.LoanNumber, p.LoanFundingDate, p.CurrentAssetStatus, p.CurrentAssetType, p.UPBAmount, p.State, r.HeavyLift,#(lf)[Combined Due Diligence Grade]=r.CurrentCombinedGrade,#(lf)[TItle Grade]=r.CurrentTitleGrade,#(lf)[Legal Grade]=r.CurrentLegalGrade,#(lf)[Docs Grade]=r.CurrentDocsGrade,#(lf)[Property Grade]=r.CurrentPropertyGrade,#(lf)[Compliance Grade]=r.CurrentComplianceGrade,#(lf)[Indicative Grade]=ig.IndicativeSubType#(lf)into #grades#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join products.SellerDisclosures ig on#(lf)   l.Id=ig.LoanID and ig.IndicativeType='Indicative Grade'#(lf)order by r.LoanID#(lf)#(lf)select p.LoanID,p.Name, p.LoanNumber, p.CurrentAssetType, p.UPBAmount, p.State,#(lf)[Indicative Grade]=ig.IndicativeSubType,#(lf)[Seller Disclosure - Title]=sd_t1.IndicativeSubType,#(lf)[Seller Disclosure - Docs]=sd_co1.IndicativeSubType,#(lf)[Seller Disclosure - Compliance]=sd_c1.IndicativeSubType,#(lf)[Seller Disclosure - Legal]=sd_l1.IndicativeSubType,#(lf)[Seller Disclosure - Property]=sd_p1.IndicativeSubType,#(lf)[Seller Disclosed Summary]=sd_sd1.IndicativeSubType#(lf)into #sd#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join products.SellerDisclosures ig on#(lf)   l.Id=ig.LoanID and ig.IndicativeType='Indicative Grade'#(lf) left outer join products.SellerDisclosures sd_c1 on#(lf)   sd_c1.Id=(select TOP 1 sd_c1.Id from products.SellerDisclosures sd_c1 where l.Id=sd_c1.LoanID and sd_c1.IndicativeType='Compliance' order by sd_c1.Id)#(lf) left outer join products.SellerDisclosures sd_co1 on#(lf)   sd_co1.Id=(select TOP 1 sd_co1.Id from products.SellerDisclosures sd_co1 where l.Id=sd_co1.LoanID and sd_co1.IndicativeType='Collateral' order by sd_co1.Id)#(lf) left outer join products.SellerDisclosures sd_l1 on#(lf)   sd_l1.Id=(select TOP 1 sd_l1.Id from products.SellerDisclosures sd_l1 where l.Id=sd_l1.LoanID and sd_l1.IndicativeType='Legal' order by sd_l1.Id)#(lf) left outer join products.SellerDisclosures sd_t1 on#(lf)   sd_t1.Id=(select TOP 1 sd_t1.Id from products.SellerDisclosures sd_t1 where l.Id=sd_t1.LoanID and sd_t1.IndicativeType='Title' order by sd_t1.Id)#(lf) left outer join products.SellerDisclosures sd_p1 on#(lf)   sd_p1.Id=(select TOP 1 sd_p1.Id from products.SellerDisclosures sd_p1 where l.Id=sd_p1.LoanID and sd_p1.IndicativeType='Property' order by sd_p1.Id)#(lf) left outer join products.SellerDisclosures sd_sd1 on#(lf)   sd_sd1.Id=(select TOP 1 sd_sd1.Id from products.SellerDisclosures sd_sd1 where l.Id=sd_sd1.LoanID and sd_sd1.IndicativeType='Seller Disclosure' order by sd_sd1.Id)#(lf)#(lf)#(lf)#(lf)select p.LoanID,P.Name, p.LoanNumber, p.CurrentAssetType, p.UPBAmount, p.State,#(lf)[Combined Title Grade]=r.CurrentTitleGrade, #(lf)[Number of Title Issues Identified]=(select count(1) from #data d where d.Module='Title' and p.LoanID=d.LoanId and d.CurrentGrade<>'NI'),#(lf)[Number of Title PID's]=(select count(1) from #data d inner join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId where d.Module='Title' and p.LoanID=d.LoanId and d.CurrentGrade<>'NI' and (d.IssueCategory='Curative' or ic.CurativeManualFlag=0)),#(lf)[Title Insurance]=(select TOP 1 case when cd.TitlePolicyPresent='Found' then 'Y' else 'N' end from products.CollateralData cd where p.LoanId=cd.LoanId and cd.CompletionDate is not null order by cd.CompletionDate desc),#(lf)[Title 1st Issue Type]=d1.IssueType, [Title 1st Issue Description]=d1.IssueDescription, [Title 1st Issue Category]=d1.DescriptionModifier, #(lf)[Title 1st Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d1.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Title 2nd Issue Type]=d2.IssueType, [Title 2nd Issue Description]=d2.IssueDescription, [Title 2nd Issue Category]=d2.DescriptionModifier, #(lf)[Title 2nd Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d2.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Title 3rd Issue Type]=d3.IssueType, [Title 3rd Issue Description]=d3.IssueDescription, [Title 3rd Issue Category]=d3.DescriptionModifier, #(lf)[Title 3rd Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d3.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Title 4th Issue Type]=d4.IssueType, [Title 4th Issue Description]=d4.IssueDescription, [Title 4th Issue Category]=d4.DescriptionModifier, #(lf)[Title 4th Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d4.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Title 5th Issue Type]=d5.IssueType, [Title 5th Issue Description]=d5.IssueDescription, [Title 5th Issue Category]=d5.DescriptionModifier, #(lf)[Title 5th Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d5.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Annual Taxes]=isnull((select sum(ta.TotalAnnualTax) from products.TaxAssessments ta where ta.TitleProductId=p.TitleProductid),(select TOP 1 sum(ti.TaxesAmount) from products.TaxInsurances ti where ti.TitleProductId=p.TitleProductId group by ti.YearsDelinquent order by ti.YearsDelinquent desc)),#(lf)[Delq Taxes]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='DQ Taxes' and im.CurrentGrade<>'NI')>0 then (select isnull(DqTxSum,0) from dbo.uPPR_SummaryData(p.TitleProductId)) else 0 end,#(lf)[Muni Liens/Tax Certs (Sr)]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Muni Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Muni Lien' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)    case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType like 'Mech%Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Mechanic''s Lien' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType like 'State/Local Tax Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(state.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens state on m.PrimaryObjectId=state.Id where m.Match='Issue' and m.MatchType='Tax Lien' and m.MatchDesc='State/Local Tax Lien' and m.TitleProductId=p.TitleProductId),0) else 0 end,#(lf)[Muni Liens/Tax Certs (Jr)]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Muni Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Muni Lien' and m.MatchScore=0 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)    case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType like 'Mech%Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Mechanic''s Lien' and m.MatchScore=0 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)                            case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Muni Lien' and im.CurrentGrade<>'NI')=0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Muni Lien' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)    case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType like 'Mech%Lien' and im.CurrentGrade<>'NI')=0 then #(lf)                                isnull((select sum(muni.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens muni on m.PrimaryObjectId=muni.Id where m.Match='Issue' and m.MatchType='Mechanic''s Lien' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType like 'State/Local Tax Lien' and im.CurrentGrade<>'NI')=0 then #(lf)                                isnull((select sum(state.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens state on m.PrimaryObjectId=state.Id where m.Match='Issue' and m.MatchType='Tax Lien' and m.MatchDesc='State/Local Tax Lien' and m.TitleProductId=p.TitleProductId),0) else 0 end#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab),#(lf)[HOA Liens (Super)]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='HOA Lien' and im.CurrentGrade<>'NI')>0 then #(lf)              isnull((select sum(hoa.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens hoa on m.PrimaryObjectId=hoa.Id where m.Match='Issue' and m.MatchType='HOA Lien' and m.TitleProductId=p.TitleProductId and (m.MatchScore=1 or p.State in (select State from dd.StateMatrix where HOASuperLien='Y'))),0) else 0 end,#(lf)[HOA Liens (Jr)]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='HOA Lien' and im.CurrentGrade<>'NI')>0 then #(lf)              isnull((select sum(hoa.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens hoa on m.PrimaryObjectId=hoa.Id where m.Match='Issue' and m.MatchType='HOA Lien' and m.TitleProductId=p.TitleProductId and m.MatchScore=0 and p.State not in (select State from dd.StateMatrix where HOASuperLien='Y')),0) else 0 end+#(lf)#(tab)#(tab)#(tab)  case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='HOA Lien' and im.CurrentGrade<>'NI')=0 then #(lf)              isnull((select sum(hoa.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens hoa on m.PrimaryObjectId=hoa.Id where m.Match='Issue' and m.MatchType='HOA Lien' and m.TitleProductId=p.TitleProductId),0) else 0 end,#(lf)[Sr Mortgage]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Prior Mortgage' and im.CurrentGrade<>'NI')>0 then #(lf)                                 isnull((select sum(lt.FinanceLoanAmount)#(lf)                                 from products.TitleProducts t1 (NOLOCK)#(lf)                                  inner join products.Mortgages lt (NOLOCK) on#(lf)                                                          t1.id=lt.TitleProductId #(lf)                                                         left outer join products.match sm (NOLOCK) on#(lf)                                   sm.MatchID=(select TOP 1 sm.MatchID from products.match sm (NOLOCK) where l.Id=sm.PrimaryObjectID and sm.PrimaryObject='Loan' and sm.MatchedObject='Mortgages' and sm.MatchType='LoanToMortgages' order by sm.MatchID desc)#(lf)                                  left outer join products.match smlp (NOLOCK) on#(lf)                                   smlp.MatchID=(select TOP 1 smlp.MatchID from products.match smlp (NOLOCK) where sm.MatchedObjectID=smlp.PrimaryObjectID and smlp.PrimaryObject='Mortgages' and smlp.Match='LienPosition' and smlp.MatchType='LienPosition' order by smlp.MatchID desc)#(lf)                                  left outer join products.match mlp1 (NOLOCK) on#(lf)                                   mlp1.MatchID=(select TOP 1 mlp1.MatchID from products.match mlp1 (NOLOCK) where lt.ID=mlp1.PrimaryObjectID and mlp1.PrimaryObject='Mortgages' and mlp1.Match='LienPosition' and mlp1.MatchType='LienPosition' order by mlp1.MatchID desc)#(lf)                                  where t1.id=p.TitleProductId and mlp1.MatchScore<smlp.MatchScore),0) else 0 end + #(lf)#(tab)#(tab)#(tab)#(tab)case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Texas Tax Loan Lien' and im.CurrentGrade<>'NI')>0 then#(tab)#(tab)#(tab)#(tab)  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)  isnull((select sum(ttl.FinanceLoanAmount) from products.Match m inner join products.Mortgages ttl on m.MatchedObject='Mortgages' and m.MatchedObjectID=ttl.Id where m.Match='Issue' and m.MatchType='TTL' and m.TitleProductID=p.TitleProductId),0) +#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)  isnull((select sum(ttl.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens ttl on m.MatchedObject='InvolPart2untaryLiens' and m.PrimaryObjectId=ttl.Id where m.Match='Issue' and m.MatchType='TTL' and m.TitleProductID=p.TitleProductId),0) else 0 end,#(lf)[Sr Lien]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Prior Judgment' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(pj.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens pj on m.PrimaryObjectId=pj.Id where m.Match='Issue' and m.MatchType='Judgment' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end,#(lf)[Jr Mortgage]=isnull((select sum(lt.FinanceLoanAmount)#(lf)                                 from products.TitleProducts t1 (NOLOCK)#(lf)                                  inner join products.Mortgages lt (NOLOCK) on#(lf)                                                          t1.id=lt.TitleProductId#(lf)                                                         left outer join products.match sm (NOLOCK) on#(lf)                                   sm.MatchID=(select TOP 1 sm.MatchID from products.match sm (NOLOCK) where l.id=sm.PrimaryObjectID and sm.PrimaryObject='Loan' and sm.MatchedObject='Mortgages' and sm.MatchType='LoanToMortgages' order by sm.MatchID desc)#(lf)                                  left outer join products.match smlp (NOLOCK) on#(lf)                                   smlp.MatchID=(select TOP 1 smlp.MatchID from products.match smlp (NOLOCK) where sm.MatchedObjectID=smlp.PrimaryObjectID and smlp.PrimaryObject='Mortgages' and smlp.Match='LienPosition' and smlp.MatchType='LienPosition' order by smlp.MatchID desc)#(lf)                                  left outer join products.match mlp1 (NOLOCK) on#(lf)                                   mlp1.MatchID=(select TOP 1 mlp1.MatchID from products.match mlp1 (NOLOCK) where lt.ID=mlp1.PrimaryObjectID and mlp1.PrimaryObject='Mortgages' and mlp1.Match='LienPosition' and mlp1.MatchType='LienPosition' order by mlp1.MatchID desc)#(lf)                                  where t1.id=p.TitleProductId and mlp1.MatchScore>smlp.MatchScore),0),#(lf)[Jr Lien]=isnull((select sum(pj.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens pj on m.PrimaryObjectId=pj.Id where m.Match='Issue' and m.MatchType='Judgment' and m.MatchScore=0 and m.TitleProductID=p.TitleProductId),0) +#(lf)          case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Prior Judgment' and im.CurrentGrade<>'NI')=0 then #(lf)                                isnull((select sum(pj.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens pj on m.PrimaryObjectId=pj.Id where m.Match='Issue' and m.MatchType='Judgment' and m.MatchScore=1 and m.TitleProductID=p.TitleProductId),0) else 0 end,#(lf)[IRS Liens]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueSubType='Federal Tax/DOJ Lien' and im.CurrentGrade<>'NI')>0 then #(lf)                                isnull((select sum(fed.BalanceAmount) from products.Match m inner join products.InvoluntaryLiens fed on m.PrimaryObjectId=fed.Id where m.Match='Issue' and m.MatchType='Tax Lien' and m.MatchDesc='Federal Tax Lien' and m.TitleProductId=p.TitleProductId),0) else 0 end,#(lf)[AOM Summary]=case when (select count(1) from #data im where l.Id=im.LoanId and im.IssueType='Missing/Incomplete AOM' and im.CurrentGrade<>'NI')>0 then 'AOM Chain Break' else NULL end,#(lf)[Assignee]=dbo.ContactPull(aom.MatchedObjectId)#(lf)into #title#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) left outer join products.Match aom on#(lf)   aom.MatchID=(select TOP 1 aom.MatchID from products.Match aom where p.TitleProductId=aom.TitleProductId and aom.Match='LoanAssignmentChain' order by aom.MatchID desc)#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join #data d1 on#(lf)   d1.Id=(select TOP 1 d1.Id from #data d1 where p.LoanID=d1.LoanID and d1.Module='Title' and d1.CurrentGrade<>'NI' order by dbo.GradeRank(d1.CurrentGrade) desc)#(lf) left outer join #data d2 on#(lf)   d2.Id=(select TOP 1 d2.Id from #data d2 where p.LoanID=d2.LoanID and d2.Module='Title' and d2.CurrentGrade<>'NI' and d1.Id<>d2.Id order by dbo.GradeRank(d2.CurrentGrade) desc)#(lf) left outer join #data d3 on#(lf)   d3.Id=(select TOP 1 d3.Id from #data d3 where p.LoanID=d3.LoanID and d3.Module='Title' and d3.CurrentGrade<>'NI' and d1.Id<>d3.Id and d2.Id<>d3.Id order by dbo.GradeRank(d3.CurrentGrade) desc)#(lf) left outer join #data d4 on#(lf)   d4.Id=(select TOP 1 d4.Id from #data d4 where p.LoanID=d4.LoanID and d4.Module='Title' and d4.CurrentGrade<>'NI' and d1.Id<>d4.Id and d2.Id<>d4.Id and d3.Id<>d4.Id order by dbo.GradeRank(d4.CurrentGrade) desc)#(lf) left outer join #data d5 on#(lf)   d5.Id=(select TOP 1 d5.Id from #data d5 where p.LoanID=d5.LoanID and d5.Module='Title' and d5.CurrentGrade<>'NI' and d1.Id<>d5.Id and d2.Id<>d5.Id and d3.Id<>d5.Id and d4.Id<>d5.Id order by dbo.GradeRank(d5.CurrentGrade) desc)#(lf)order by r.LoanID#(lf)#(lf)#(lf)#(lf)#(lf)select p.LoanID,p.Name, p.LoanNumber, p.CurrentAssetType, p.UPBAmount, p.State,#(lf)[Combined Legal Grade]=r.CurrentLegalGrade, #(lf)[Number of Legal Review Tasks]=(select count(1) from #data d inner join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Module='Legal' and p.LoanID=d.LoanId and isnull(imon.ReviewTask,'No Risk Items') not in ('N','No Risk Items')),#(lf)[Current Legal Process]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active' then 'Bankruptcy' when (l.CurrentAssetStatus in ('BK','Bankruptcy') or l.CurrentAssetStatus like '%Bankruptcy%') then 'Bankruptcy' when fc.ForeclosureRiskID is not null then 'Foreclosure' when (l.CurrentAssetStatus in ('FC','Foreclosure') or l.CurrentAssetStatus like '%Foreclosure%') then 'Foreclosure' else NULL end,#(lf)[Current Legal Sub Process]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active'  then 'Chapter ' + convert(varchar(20),bk.BKTypeChapter) when (l.CurrentAssetStatus in ('BK','Bankruptcy') or l.CurrentAssetStatus like '%Bankruptcy%') then 'Chapter Unknown' when (fc.ForeclosureRiskID is not null or l.CurrentAssetStatus in ('FC','Foreclosure') or l.CurrentAssetStatus like '%Foreclosure%') then fca.FCLType + ' FC' else NULL end,#(lf)#(lf)[Firm/Attorney Name]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active'  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)then isnull((select top 1 b.AttorneyName from reports.SVC_AIS_BKScrub b where l.Id=b.LoanId and b.CaseStatus='OPEN'),'Unknown') #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when fc.ForeclosureRiskID is not null then isnull(fc.PlaintiffsCounselofRecord,'Unknown') else NULL end,#(lf)#(lf)[Case Number]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active'  #(lf)#(tab)#(tab)#(tab)#(tab)then coalesce(bk.BKCaseNumberServicingData,bk.BKCaseNumber,'Unknown') #(lf)#(tab)#(tab)#(tab)#(tab)when fc.ForeclosureRiskID is not null then coalesce(fc.UpdatedForeclosureCaseNumber,fc.ForeclosureCaseNumberServicingData,'Unknown') else NULL end,#(lf)#(lf)[Court Name]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active'  then isnull(bk.Court,'Unknown') #(lf)#(tab)#(tab)#(tab)#(tab)when fc.ForeclosureRiskID is not null then coalesce(fc.County,'Unknown') + case when fc.County is not null then ' County' else '' end else NULL end,#(lf)#(lf)#(lf)[Docket Last Entry Date]=case when bk.BankruptcyRiskID is not null and bk.BKStatus='Active' then isnull(convert(varchar(10),bk.DateofLastDocketEntry,101),'Unknown') #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when fc.ForeclosureRiskID is not null then coalesce(convert(varchar(10),fc.DateofLastDocketEntry,101),'Unknown') else NULL end,#(lf)#(lf)[Process Monitoring]=case when (d1.IssueSubType like '%Bankruptcy%' or d1.IssueSubType like '%BK%' or d1.ReferenceTable in ('TerminalBankruptcy')) then 'Bankruptcy' #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when (d1.IssueSubType like '%Foreclosure%' or d1.IssueSubType like '%FC%' or d1.ReferenceTable in ('TerminalForeclosure')) then 'Foreclosure' #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)else d1.IssueSubType end,#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)--else NULL end,#(lf)#(lf)[Current Milestone]=case when (d1.IssueSubType like '%Bankruptcy%' or d1.IssueSubType like '%BK%' or d1.ReferenceTable in ('TerminalBankruptcy')) then NULL #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when (d1.IssueSubType like '%Foreclosure%' or d1.IssueSubType like '%FC%' or d1.ReferenceTable in ('TerminalForeclosure')) then fc.CurrentMilestone #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)else d1.IssueSubType end,#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)--else NULL end,#(lf)#(lf)[Legal Risk Type]=case when (d1.IssueSubType like '%Bankruptcy%' or d1.IssueSubType like '%BK%' or d1.ReferenceTable in ('TerminalBankruptcy')) then bk.RiskLogic #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when (d1.IssueSubType like '%Foreclosure%' or d1.IssueSubType like '%FC%' or d1.ReferenceTable in ('TerminalForeclosure')) then fc.RiskLogic#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)else d1.IssueSubType end,#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)--else NULL end,#(lf)#(lf)[Legal Risk Summary]=(select TOP 1 imon.CombinedInitialMessage from products.IssueMonitoring imon (NOLOCK) where d1.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)#(lf)[Legal Risk Review Task]=(select TOP 1 case when isnull(imon.ReviewTask,'No Risk Items') in ('N','No Risk Items') then 'No Risk Items' else imon.ReviewTask end from products.IssueMonitoring imon where d1.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)#(lf)[Legal Risk Urgent Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d1.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[SOL Issue]=d2.IssueSubType,#(lf)[SOL Summary]=(select TOP 1 imon.CombinedInitialMessage from products.IssueMonitoring imon (NOLOCK) where d2.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[SOL Review Task]=(select TOP 1 case when isnull(imon.ReviewTask,'No Risk Items') in ('N','No Risk Items') then 'No Risk Items' else imon.ReviewTask end from products.IssueMonitoring imon where d2.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[SOL Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d2.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[Claim Risk (FLA) Issue]=d5.IssueSubType,#(lf)[Claim Risk (FLA) Summary]=(select TOP 1 imon.CombinedInitialMessage from products.IssueMonitoring imon (NOLOCK) where d5.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Claim Risk (FLA) Review Task]=(select TOP 1 case when isnull(imon.ReviewTask,'No Risk Items') in ('N','No Risk Items') then 'No Risk Items' else imon.ReviewTask end from products.IssueMonitoring imon where d5.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Claim Risk (FLA) Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d5.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[Loan Type Issue]=d3.IssueSubType,#(lf)[Loan Type Summary]=(select TOP 1 imon.CombinedInitialMessage from products.IssueMonitoring imon (NOLOCK) where d3.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Loan Type Review Task]=(select TOP 1 case when isnull(imon.ReviewTask,'No Risk Items') in ('N','No Risk Items') then 'No Risk Items' else imon.ReviewTask end from products.IssueMonitoring imon where d3.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Loan Type Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d3.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[Legal Keyword Issue]=d4.IssueSubType,#(lf)[Legal Keyword Summary]=(select TOP 1 imon.CombinedInitialMessage from products.IssueMonitoring imon (NOLOCK) where d4.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Legal Keyword Review Task]=(select TOP 1 case when isnull(imon.ReviewTask,'No Risk Items') in ('N','No Risk Items') then 'No Risk Items' else imon.ReviewTask end from products.IssueMonitoring imon where d4.IssueMainId=imon.IssueMainId order by imon.Id desc),#(lf)[Legal Keyword Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d4.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc)#(lf)into #legal#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join reports.BankruptcyRiskBestX bk (NOLOCK) on#(lf)   bk.BankruptcyRiskID=(select TOP 1 bk.BankruptcyRiskID from reports.BankruptcyRiskBestX bk (NOLOCK) where l.Id=bk.LoanId order by bk.BankruptcyRiskID desc)#(lf) left outer join reports.ForeclosureRiskBestX fc (NOLOCK) on#(lf)   fc.ForeclosureRiskID=(select TOP 1 fc.ForeclosureRiskID from reports.ForeclosureRiskBestX fc (NOLOCK) where l.Id=fc.LoanId order by fc.ForeclosureRiskID desc)#(lf) left outer join dd.FCAssumptionsMatrix fca (NOLOCK) on#(lf)   p.State=fca.State#(lf)left outer join #data d1 on#(lf)   d1.Id=(select TOP 1 d1.Id from #data d1 where p.LoanID=d1.LoanID and d1.Module='Legal' and d1.CurrentGrade<>'NI' #(lf)   and d1.IssueType='Legal Risk' and (d1.IssueSubType like '%Bankruptcy%' or d1.IssueSubType like '%Foreclosure%' #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)or d1.IssueSubType like '%BK%' or d1.IssueSubType like '%FC%' #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)or d1.ReferenceTable in ('TerminalBankruptcy','TerminalForeclosure')#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)or d1.IssueSubType like '%Contest%' or d1.IssueSubType like '%Dormancy%') #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)order by dbo.GradeRank(d1.CurrentGrade) desc)#(lf)  left outer join #data d2 on#(lf)   d2.Id=(select TOP 1 d2.Id from #data d2 where p.LoanID=d2.LoanID and d2.Module='Legal' and d2.CurrentGrade<>'NI' and d2.IssueSubType like '%SOL%' order by dbo.GradeRank(d2.CurrentGrade) desc)#(lf) left outer join #data d3 on#(lf)   d3.Id=(select TOP 1 d3.Id from #data d3 where p.LoanID=d3.LoanID and d3.Module='Legal' and d3.CurrentGrade<>'NI' and d3.IssueType='Loan Type' order by dbo.GradeRank(d3.CurrentGrade) desc)#(lf) left outer join #data d4 on#(lf)   d4.Id=(select TOP 1 d4.Id from #data d4 where p.LoanID=d4.LoanID and d4.Module='Legal' and d4.CurrentGrade<>'NI' and d4.ReferenceTable='TerminalKeywordLegal' order by dbo.GradeRank(d4.CurrentGrade) desc)#(lf) left outer join #data d5 on#(lf)   d5.Id=(select TOP 1 d5.Id from #data d5 where p.LoanID=d5.LoanID and d5.Module='Legal' and d5.CurrentGrade<>'NI' and (d5.ReferenceTable='TerminalFLA' or d5.IssueSubType='FHA Claims Risk (FLA)') order by dbo.GradeRank(d5.CurrentGrade) desc)#(lf)#(lf)order by r.LoanID#(lf)#(lf)#(lf)#(lf)#(lf)select p.LoanID,P.Name, p.LoanNumber, p.CurrentAssetType, p.UPBAmount, p.State,#(lf)[Combined Docs Grade]=r.CurrentDocsGrade, #(lf)#(lf)[Number of Docs Review Tasks]=(select count(1) from #data d inner join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Module='Docs' and p.LoanID=d.LoanId and isnull(imon.ReviewTask,'No Risk Items') not in ('N','No Risk Items')),#(lf)[Number of Trailing Docs]=(select sum(case when idm.TrailingDocs='Y' then 1 else 0 end) from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where p.LoanId=d.LoanId and d.CurrentGrade<>'NI'),#(lf)#(lf)[Docs 1st Issue Type]=d1.IssueSubType, [Docs 1st Issue Description]=d1.IssueDescription, --[1st Issue Category]=d1.IssueCategory, #(lf)[Docs 1st Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d1.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Docs 1st Issue Trailing Docs]=(select TOP 1 idm.TrailingDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d1.Id order by idm.ID desc),#(lf)[Docs 1st Issue Custodian Recon]=(select TOP 1 idm.CustodianDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d1.Id order by idm.ID desc),#(lf)[Docs 2nd Issue Type]=d2.IssueSubType, [Docs 2nd Issue Description]=d2.IssueDescription, --[2nd Issue Category]=d2.IssueCategory, #(lf)[Docs 2nd Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d2.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Docs 2nd Issue Trailing Docs]=(select TOP 1 idm.TrailingDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d2.Id order by idm.ID desc),#(lf)[Docs 2nd Issue Custodian Recon]=(select TOP 1 idm.CustodianDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d2.Id order by idm.ID desc),#(lf)[Docs 3rd Issue Type]=d3.IssueSubType, [Docs 3rd Issue Description]=d3.IssueDescription, --[3rd Issue Category]=d3.IssueCategory, #(lf)[Docs 3rd Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d3.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Docs 3rd Issue Trailing Docs]=(select TOP 1 idm.TrailingDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d3.Id order by idm.ID desc),#(lf)[Docs 3rd Issue Custodian Recon]=(select TOP 1 idm.CustodianDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d3.Id order by idm.ID desc),#(lf)[Docs 4th Issue Type]=d4.IssueSubType, [Docs 4th Issue Description]=d4.IssueDescription, --[4th Issue Category]=d4.IssueCategory, #(lf)[Docs 4th Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d4.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Docs 4th Issue Trailing Docs]=(select TOP 1 idm.TrailingDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d4.Id order by idm.ID desc),#(lf)[Docs 4th Issue Custodian Recon]=(select TOP 1 idm.CustodianDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d4.Id order by idm.ID desc),#(lf)[Docs 5th Issue Type]=d5.IssueSubType, [Docs 5th Issue Description]=d5.IssueDescription, --[5th Issue Category]=d5.IssueCategory, #(lf)[Docs 5th Issue Critical Flag]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d5.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)[Docs 5th Issue Trailing Docs]=(select TOP 1 idm.TrailingDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d5.Id order by idm.ID desc),#(lf)[Docs 5th Issue Custodian Recon]=(select TOP 1 idm.CustodianDocs from #data d left outer join products.IssueDocManagement idm on d.IssueMainId=idm.IssueMainId where d.Id=d5.Id order by idm.ID desc)#(lf)into #docs#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join #data d1 on#(lf)   d1.Id=(select TOP 1 d1.Id from #data d1 where p.LoanID=d1.LoanID and d1.Module='Docs' and d1.CurrentGrade<>'NI' order by dbo.GradeRank(d1.CurrentGrade) desc)#(lf) left outer join #data d2 on#(lf)   d2.Id=(select TOP 1 d2.Id from #data d2 where p.LoanID=d2.LoanID and d2.Module='Docs' and d2.CurrentGrade<>'NI' and d1.Id<>d2.Id order by dbo.GradeRank(d2.CurrentGrade) desc)#(lf) left outer join #data d3 on#(lf)   d3.Id=(select TOP 1 d3.Id from #data d3 where p.LoanID=d3.LoanID and d3.Module='Docs' and d3.CurrentGrade<>'NI' and d1.Id<>d3.Id and d2.Id<>d3.Id order by dbo.GradeRank(d3.CurrentGrade) desc)#(lf) left outer join #data d4 on#(lf)   d4.Id=(select TOP 1 d4.Id from #data d4 where p.LoanID=d4.LoanID and d4.Module='Docs' and d4.CurrentGrade<>'NI' and d1.Id<>d4.Id and d2.Id<>d4.Id and d3.Id<>d4.Id order by dbo.GradeRank(d4.CurrentGrade) desc)#(lf) left outer join #data d5 on#(lf)   d5.Id=(select TOP 1 d5.Id from #data d5 where p.LoanID=d5.LoanID and d5.Module='Docs' and d5.CurrentGrade<>'NI' and d1.Id<>d5.Id and d2.Id<>d5.Id and d3.Id<>d5.Id and d4.Id<>d5.Id order by dbo.GradeRank(d5.CurrentGrade) desc)#(lf)order by r.LoanID#(lf)#(lf)#(lf)select p.LoanID, p.LoanNumber, p.CurrentAssetType, p.UPBAmount, p.State,#(lf)#(lf)[Combined Property Grade]=r.CurrentPropertyGrade, #(lf)[Number of Property Review Tasks]=(select count(1) from #data d inner join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Module='Property' and p.LoanID=d.LoanId and isnull(imon.ReviewTask,'No Risk Items') not in ('N','No Risk Items')),#(lf)SellerValue=sv.Value, SellerValueDate=sv.Valuedate, AVMValue=avm.Value,#(lf)#(lf)[Property Type Issue]=case when isnull(d1.CurrentGrade,'NI')<>'NI' then 'Y' else 'N' end,#(lf)[Property Type Summary]=d1.IssueDescription,#(lf)[Property Type Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d1.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[Property Condition Issue]=case when isnull(d2.CurrentGrade,'NI')<>'NI' then 'Y' else 'N' end,#(lf)[Property Condition Summary]=d2.IssueDescription,#(lf)[Property Condition Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d2.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[Value Discrepancy Issue]=case when isnull(d3.CurrentGrade,'NI')<>'NI' then 'Y' else 'N' end,#(lf)[Value Discrepancy Summary]=d3.IssueDescription,#(lf)[Value Discrepancy Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d3.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc),#(lf)#(lf)[DQ Tax Issue]=case when isnull(d4.CurrentGrade,'NI')<>'NI' then 'Y' else 'N' end,#(lf)[DQ Tax Summary]=d4.IssueDescription,#(lf)[DQ Tax Urgent]=(select TOP 1 case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end from #data d left outer join products.IssueCurative ic on d.IssueMainId=ic.IssueMainId left outer join products.IssueMonitoring imon on d.IssueMainId=imon.IssueMainId where d.Id=d4.Id order by case when (isnull(ic.UrgentFlag,0)=1 or isnull(imon.UrgentFlag,0)=1) then 'Y' else 'N' end desc)#(lf)into #property#(lf)from #pop p #(lf) inner join products.Loans l on#(lf)   p.LoanId=l.Id#(lf) inner join #roll r on#(lf)   p.LoanID=r.LoanID#(lf) left outer join reports.PropertyValues sv on#(lf)   l.Id=sv.LoanId and sv.ValueType like '%Seller%'#(lf) left outer join reports.PropertyValues avm on#(lf)   l.Id=avm.LoanId and sv.ValueType like '%AVM%'#(lf) left outer join #data d1 on#(lf)   d1.Id=(select TOP 1 d1.Id from #data d1 where p.LoanID=d1.LoanID and d1.Module='Property' and d1.CurrentGrade<>'NI' and d1.IssueType='Property Type' order by dbo.GradeRank(d1.CurrentGrade) desc)#(lf) left outer join #data d2 on#(lf)   d2.Id=(select TOP 1 d2.Id from #data d2 where p.LoanID=d2.LoanID and d2.Module='Property' and d2.CurrentGrade<>'NI' and d2.IssueSubType like '%Prop%Condition%' order by dbo.GradeRank(d2.CurrentGrade) desc)#(lf) left outer join #data d3 on#(lf)   d3.Id=(select TOP 1 d3.Id from #data d3 where p.LoanID=d3.LoanID and d3.Module='Property' and d3.CurrentGrade<>'NI' and d3.IssueSubType='Value Discrepancy' order by dbo.GradeRank(d3.CurrentGrade) desc)#(lf) left outer join #data d4 on#(lf)   d4.Id=(select TOP 1 d4.Id from #data d4 where p.LoanID=d4.LoanID and d4.Module='Property' and d4.CurrentGrade<>'NI' and d4.IssueSubType='DQ Taxes' order by dbo.GradeRank(d4.CurrentGrade) desc)#(lf)order by r.LoanID#(lf)#(lf)#(lf)#(lf)   select #(lf)   LoanID=l.id, l.LoanNumber, l.CurrentAssetType,#(lf)   [B1FirstName]=b1.FirstName, [B1MiddleName]=b1.MiddleName, [B1LastName]=b1.LastName,  #(lf)   [B2FirstName]=b2.FirstName, [B2MiddleName]=b2.MiddleName, [B2LastName]=b2.LastName,#(lf)   l.CurrentBalance, l.NextPaymentDue,  #(lf)   #(lf)   B1_FICO=f1.ScoreInt, [B1_BirthDate]=cf1.BirthDate,#(lf)   [B1_DeathFlag]=CASE WHEN ca1.CategoryType IS NOT NULL THEN 'Y' ELSE NULL END, #(lf)   [B1_FraudFlag]=CASE WHEN ca3.CategoryType IS NOT NULL THEN 'Y' ELSE NULL END,#(lf)   [B1_Employer]=ce1.Name, [B1_EmplCurrentInd]=ce1.EmploymentCurrentIndicator, [B1_EmplPosition]=ce1.EmploymentPositionDescription, [B1_EmplReportedDate]=ce1.EmploymentReportedDate,#(lf)   [B1_ActiveMilitaryOnStatusDate]=m1.ActiveOnStatusDate, [B1_ActiveDutyBeginDate]=m1.ActiveDutyBeginDate, [B1_ActiveDutyEndDate]=m1.ActiveDutyEndDate,#(lf)#(lf)   B2_FICO=f2.ScoreInt, [B2_BirthDate]=cf2.BirthDate, #(lf)   [B2_DeathFlag]=CASE WHEN ca2.CategoryType IS NOT NULL THEN 'Y' ELSE NULL END, #(lf)   [B2_FraudFlag]=CASE WHEN ca4.CategoryType IS NOT NULL THEN 'Y' ELSE NULL END,#(lf)   [B2_Employer]=ce2.Name, [B2_EmplCurrentInd]=ce2.EmploymentCurrentIndicator, [B2_EmplPosition]=ce2.EmploymentPositionDescription, [B2_EmplReportedDate]=ce2.EmploymentReportedDate,#(lf)   [B2_ActiveMilitaryOnStatusDate]=m2.ActiveOnStatusDate, [B2_ActiveDutyBeginDate]=m2.ActiveDutyBeginDate, [B2_ActiveDutyEndDate]=m2.ActiveDutyEndDate#(lf)into #voms#(lf)from #pop#(lf) inner join products.Loans l (NOLOCK) on#(lf)   #pop.LoanId=l.Id#(lf) inner join products.parties b1 (NOLOCK) on#(lf)   l.Borrower1Id=b1.Id#(lf) left outer join products.parties b2 (NOLOCK)on #(lf)   l.Borrower2Id=b2.Id#(lf) left outer join products.CreditFiles cf1 (NOLOCK) on#(lf)   b1.USSSN=cf1.SSN#(lf) left outer join products.CreditFiles cf2 (NOLOCK) on#(lf)   b2.USSSN=cf2.SSN#(lf) #(lf) left outer join products.CreditReports cr (NOLOCK) on#(lf)   cr.Id=(select TOP 1 cr.Id from products.CreditReports cr (NOLOCK) where l.Id=cr.ObjectId and cr.Object='Loan' order by cr.Id desc)#(lf) left outer join products.Scores f1 on#(lf)   f1.Id=(select TOP 1 f1.Id from products.Scores f1 (NOLOCK) where cr.Id=f1.CreditReportId order by f1.Id)#(lf) left outer join products.Scores f2 on#(lf)   f2.Id=(select TOP 1 f2.Id from products.Scores f2 (NOLOCK) where cr.Id=f2.CreditReportId and f1.Id<>f2.ID and cr.Name like 'Joint%' order by f2.Id)#(lf)#(lf) left outer join products.CreditBorrowerEmployers ce1 (NOLOCK) on #(lf)#(tab)ce1.Id=(select TOP 1 ce1.Id from products.CreditBorrowerEmployers ce1 (NOLOCK) where cf1.Id=ce1.CreditFileId order by ce1.Id )#(lf) left outer join products.CreditBorrowerEmployers ce2 (NOLOCK) on #(lf)#(tab)ce2.Id=(select TOP 1 ce2.Id from products.CreditBorrowerEmployers ce2 (NOLOCK) where cf2.Id=ce2.CreditFileId order by ce2.Id )#(lf)#(lf) left outer join products.CreditFileAlerts ca1 (NOLOCK) on #(lf)#(tab)ca1.Id=(select TOP 1 ca1.Id from products.CreditFileAlerts ca1 (NOLOCK) where cf1.Id=ca1.CreditFileId and ca1.AdverseIndicator='Y' and ca1.CategoryType like ('Death%') order by ca1.Id )#(lf) left outer join products.CreditFileAlerts ca2 (NOLOCK) on #(lf)#(tab)ca2.Id=(select TOP 1 ca2.Id from products.CreditFileAlerts ca2 (NOLOCK) where cf2.Id=ca2.CreditFileId and ca2.AdverseIndicator='Y' and ca2.CategoryType like ('Death%') order by ca2.Id )#(lf)#(lf) left outer join products.CreditFileAlerts ca3 (NOLOCK) on #(lf)#(tab)ca3.Id=(select TOP 1 ca3.Id from products.CreditFileAlerts ca3 (NOLOCK) where cf1.Id=ca3.CreditFileId and ca3.AdverseIndicator='Y' and ca3.CategoryType like ('%Fraud%') order by ca3.Id )#(lf) left outer join products.CreditFileAlerts ca4 (NOLOCK) on #(lf)#(tab)ca4.Id=(select TOP 1 ca4.Id from products.CreditFileAlerts ca4 (NOLOCK) where cf2.Id=ca4.CreditFileId and ca4.AdverseIndicator='Y' and ca4.CategoryType like ('%Fraud%') order by ca4.Id )#(lf)#(lf)  left outer join products.MilitaryStatus m1 on#(lf)   m1.Id=(select TOP 1 m1.Id from products.MilitaryStatus m1 where l.LoanNumber=m1.LoanNumber and b1.USSSN=m1.USSSN order by m1.UpdatedAt desc)#(lf) left outer join products.products.MilitaryStatus m2 on#(lf)   m2.Id=(select TOP 1 m2.Id from products.MilitaryStatus m2 where l.LoanNumber=m2.LoanNumber and b2.USSSN=m2.USSSN order by m2.UpdatedAt desc)#(lf)#(lf)#(lf)#(lf)#(lf)select LoanId=l.Id, Type='All', Count=count(1), UPB=sum(cl.UnpaidBalanceAmount), Pmt=sum(cl.MonthlyPaymentAmount), MaxBal=max(cl.UnpaidBalanceAmount), LastActivityDate=max(cl.LastActivityDate),#(lf)WorstCurrentRatingCode=max(cl.CurrentRatingCode), WorstEverRatingCode=max(cl.HighestAdverseRating), PctDQ=(sum(case when cl.CurrentRatingCode IN ('1','2') then 0 else 1 end) * 1.00)/(count(1) * 1.00)#(lf)into #temp#(lf)from #pop p#(lf) inner join products.Loans l (NOLOCK) on#(lf)   p.LoanId=l.Id#(lf) inner join products.TradeLoans tl (NOLOCK) on#(lf)   l.Id=tl.LoanId#(lf) inner join products.Trades t (NOLOCK) on#(lf)   tl.TradeId=t.Id#(lf) inner join products.CreditReports cr (NOLOCK) on#(lf)   cr.Id=(select TOP 1 cr.Id from products.CreditReports cr where l.Id=cr.ObjectId and cr.Object='Loan' order by cr.Id desc)#(lf) inner join products.CreditLiabilities cl (NOLOCK) on#(lf)   cr.Id=cl.CreditReportId and dbo.CreditIsLatest(cl.Id)='Y' and dbo.CreditIsActive(cl.Id) in ('Hard Open','Soft Open')#(lf)group by l.id#(lf)#(lf)union all#(lf)#(lf)select LoanId=l.Id, Type=dbo.CreditLoanType(cl.Id), Count=count(1), UPB=sum(cl.UnpaidBalanceAmount), Pmt=sum(cl.MonthlyPaymentAmount), MaxBal=max(cl.UnpaidBalanceAmount), LastActivityDate=max(cl.LastActivityDate),#(lf)WorstCurrentRatingCode=max(cl.CurrentRatingCode), WorstEverRatingCode=max(cl.HighestAdverseRating), PctDQ=(sum(case when cl.CurrentRatingCode IN ('1','2') then 0 else 1 end) * 1.00)/(count(1) * 1.00)#(lf)from #pop#(lf) inner join products.Loans l (NOLOCK) on#(lf)   #pop.LoanId=l.Id#(lf) inner join products.TradeLoans tl (NOLOCK) on#(lf)   l.Id=tl.LoanId#(lf) inner join products.Trades t (NOLOCK)on#(lf)   tl.TradeId=t.Id#(lf) inner join products.CreditReports cr (NOLOCK) on#(lf)   cr.Id=(select TOP 1 cr.Id from products.CreditReports cr where l.Id=cr.ObjectId and cr.Object='Loan' order by cr.Id desc)#(lf) inner join products.CreditLiabilities cl (NOLOCK) on#(lf)   cr.Id=cl.CreditReportId and dbo.CreditIsLatest(cl.Id)='Y' and dbo.CreditIsActive(cl.Id) in ('Hard Open','Soft Open')#(lf)group by l.id, dbo.CreditLoanType(cl.Id)#(lf)------9 MINUTES TO RUN#(lf)#(lf)CREATE INDEX t ON #TEMP (LoanID)#(lf)CREATE INDEX s ON #TEMP (Type)#(lf)CREATE INDEX p ON #POP (LoanID)#(lf)#(lf)drop table if exists #code#(lf)select distinct CurrentRatingType, CurrentRatingCode, HighestAdverseType, HighestAdverseRating#(lf)into #code#(lf)from products.CreditLiabilities (NOLOCK)#(lf)#(lf)#(lf)select LoanID=l.Id, l.LoanNumber,#(lf)cl_s.AccountType, Creditor=cl_s.Name, cl_s.AccountIdentifier, cl_s.CreditLoanType, cl_s.AccountOwnershipType, cl_s.AccountOpenedDate, cl_s.AccountReportedDate, #(lf)cl_s.HighBalanceAmount, cl_s.MonthlyPaymentAmount, cl_s.PastDueAmount, cl_s.UnpaidBalanceAmount, #(lf)cl_s.CurrentRatingType, cl_s.HighestAdverseType, cl_s.MostRecentAdverseType, cl_s.MostRecentAdverseDate,#(lf)cl_s.PaymentPatternData, cl_s.PaymentPatternStartDate,#(lf)IsActive=dbo.CreditIsActive(cl_s.Id), Message=dbo.CreditMessage(cl_s.Id),#(lf)#(lf)All_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)All_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)All_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)All_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)All_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)All_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='All')),#(lf)All_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='All')),#(lf)All_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='All'),#(lf)#(lf)Mortgage_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)Mortgage_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)Mortgage_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)Mortgage_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)Mortgage_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)Mortgage_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage')),#(lf)Mortgage_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage')),#(lf)Mortgage_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='Mortgage'),#(lf)#(lf)CreditCard_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)CreditCard_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)CreditCard_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)CreditCard_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)CreditCard_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)CreditCard_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card')),#(lf)CreditCard_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card')),#(lf)CreditCard_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='Credit Card'),#(lf)#(lf)Auto_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)Auto_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)Auto_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)Auto_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)Auto_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)Auto_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Auto')),#(lf)Auto_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Auto')),#(lf)Auto_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='Auto'),#(lf)#(lf)Student_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)Student_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)Student_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)Student_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)Student_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)Student_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Student')),#(lf)Student_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Student')),#(lf)Student_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='Student'),#(lf)#(lf)Other_TradeLineCount=(select Count from #temp where #temp.LoanId=l.Id and #temp.Type='Other'),#(lf)Other_UPB=(select UPB from #temp where #temp.LoanId=l.Id and #temp.Type='Other'),#(lf)Other_Pmt=(select Pmt from #temp where #temp.LoanId=l.Id and #temp.Type='Other'),#(lf)Other_MaxBal=(select MaxBal from #temp where #temp.LoanId=l.Id and #temp.Type='Other'),#(lf)Other_LastActivityDate=(select LastActivityDate from #temp where #temp.LoanId=l.Id and #temp.Type='Other'),#(lf)Other_WorstCurrentRating=(select TOP 1 CurrentRatingType from #code (NOLOCK) where CurrentRatingCode=(select WorstCurrentRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Other')),#(lf)Other_WorstEverRating=(select TOP 1 HighestAdverseType from #code (NOLOCK) where HighestAdverseRating=(select WorstEverRatingCode from #temp where #temp.LoanId=l.Id and #temp.Type='Other')),#(lf)Other_PctDQ=(select PctDQ from #temp where #temp.LoanId=l.Id and #temp.Type='Other')#(lf)into #cred#(lf)from #pop#(lf) inner join products.Loans l (NOLOCK) on#(lf)   #pop.LoanId=l.Id#(lf) left outer join products.CreditReports cr (NOLOCK) on#(lf)   cr.Id=(select TOP 1 cr.Id from products.CreditReports cr (NOLOCK) where l.Id=cr.ObjectId and cr.Object='Loan' order by cr.Id desc)#(lf)#(lf) left outer join products.Match m (NOLOCK) on#(lf)   l.Id=m.PrimaryObjectID and m.PrimaryObject='Loan' and m.MatchedObject='CreditLiabilities' and m.MatchType='LoanToCreditLiabilities' and m.Match='CreditMatch'#(lf) left outer join products.CreditLiabilities cl_s (NOLOCK) on#(lf)   m.MatchedObjectID=cl_s.Id and cr.Id=cl_s.CreditReportId#(lf)#(lf)#(lf)#(lf)#(lf)drop table if exists #fc_b#(lf)drop table if exists #fc_s#(lf)drop table if exists #status#(lf)select LoanId=l.Id, Stage=case when f.CurrentMilestone in ('First Legal','Service Complete','Judgment Hearing Scheduled') then 'F2' #(lf)                                    when f.CurrentMilestone='Judgment Entered' then 'F3'#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when f.CurrentMilestone='Sale Scheduled' then 'F5' #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when f.CurrentMilestone='Sale Held' then 'F6'#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)when f.CurrentMilestone='Case Dismissed' then 'F0' else 'Unk' end#(lf)into #fc_b#(lf)from #pop#(lf) inner join products.Loans l on#(lf)   #pop.LoanId=l.Id #(lf) inner join products.TradeLoans tl on#(lf)   l.Id=tl.LoanId and tl.Type='Purchase' --and tl.Status='Due Diligence'#(lf) inner join products.Trades t on#(lf)   tl.TradeId=t.Id and t.Type='Purchase'#(lf) inner join reports.ForeclosureRiskBestX f on#(lf)   l.Id=f.Loanid#(lf)#(lf)select LoanId=l.Id, Stage=ld.fc_stage#(lf)into #fc_s#(lf)from #pop#(lf) inner join products.Loans l on#(lf)   #pop.LoanId=l.Id#(lf) inner join products.TradeLoans tl on#(lf)   l.Id=tl.LoanId and tl.Type='Purchase' --and tl.Status='Due Diligence'#(lf) inner join products.Trades t on#(lf)   tl.TradeId=t.Id and t.Type='Purchase'#(lf) left outer join reports.PricingLoanDetail ld on#(lf)   l.LoanNumber=ld.Loan and ld.DetailType='Clean' and ld.DealName='SIT087_'#(lf)   --and ld.CreatedDate=(select max(ld1.CreatedDate) from reports.PricingLoanDetail ld1 where ld.Loan=ld1.Loan)#(lf)#(lf)--SELECT * FROM PRODUCTS.TradeLoans WHERE LoanId IN ('3378198569')#(lf)#(lf)--SELECT * FROM REPORTS.PricingLoanDetail WHERE #(lf)--DetailType='CLEAN' #(lf)--AND LOAN='2000114151'#(lf)#(lf)--AND DealName='RAM177' #(lf)#(lf)select LoanId=l.Id, Status=case when ais.ID is not null then 'Active BK' when ld.bk_flag='Y' and ld.bk_file_date>'1/1/2020' then 'Active BK' when isnull(case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end,'F0')<>'F0' then 'Active FC' else l.CurrentAssetStatus end,#(lf)FC_Stage=case when case when ais.ID is not null then 'Active BK' when ld.bk_flag='Y' and ld.bk_file_date>'1/1/2020' then 'Active BK' when isnull(case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end,'F0')<>'F0' then 'Active FC' else l.CurrentAssetStatus end='Active FC' then #(lf)              case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end end, #(lf)FC_Stage_Source=case when  case when ais.ID is not null then 'Active BK' when ld.bk_flag='Y' and ld.bk_file_date>'1/1/2020' then 'Active BK' when isnull(case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end,'F0')<>'F0' then 'Active FC' else l.CurrentAssetStatus end='Active FC' then#(lf)              case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then 'Oversite' else 'Seller' end end,#(lf)FC_TimeRem=tc.Timeline,#(lf)FC_TermRem=case when tc.Timeline<=180 then 'Short' when tc.Timeline<=540 then 'Moderate' when tc.Timeline<=1080 then 'Long' else 'Extended' end#(lf),ld.current_rate_type ,ld.property_type#(lf)#(lf)into #status#(lf)from #pop#(lf) inner join products.Loans l on#(lf)   #pop.LoanId=l.Id#(lf) inner join products.TradeLoans tl on#(lf)   l.Id=tl.LoanId and tl.Type='Purchase' --and tl.Status='Due Diligence'#(lf) inner join products.Trades t on#(lf)   tl.TradeId=t.Id and t.Type='Purchase'#(lf) inner join products.Properties p on#(lf)   l.PropertyId=p.Id#(lf) inner join products.Locations loc on#(lf)   p.AddressId=loc.Id#(lf) left outer join #fc_b b on#(lf)   l.Id=b.LoanId#(lf) left outer join #fc_s s on#(lf)   l.Id=s.LoanId#(lf) left outer join reports.SVC_AIS_BKScrub ais on#(lf)   ais.ID=(select TOP 1 ais.ID from reports.SVC_AIS_BKScrub ais where l.Id=ais.Loanid and ais.CaseStatus='OPEN')#(lf) left outer join reports.PricingLoanDetail ld on#(lf)   l.LoanNumber=ld.Loan and ld.DetailType='Clean' and ld.DealName='SIT087_'#(lf)   --and ld.CreatedDate=(select max(ld1.CreatedDate) from reports.PricingLoanDetail ld1 where ld.Loan=ld1.Loan)#(lf) left outer join dd.FCTimeAndCost tc on#(lf)   loc.State=tc.State and case when case when ais.ID is not null then 'Active BK' when ld.bk_flag='Y' and ld.bk_file_date>'1/1/2020' then 'Active BK' when isnull(case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end,'F0')<>'F0' then 'Active FC' else l.CurrentAssetStatus end='Active FC' then #(lf)              case when convert(int,replace(b.Stage,'F',''))>=convert(int,replace(s.Stage,'F','')) then b.Stage else s.Stage end end=tc.Stage#(lf)#(lf)#(lf) drop table if exists #values#(lf) select LoanID=l.Id, BPOValue=pv.Value, BPODate=pv.ValueDate, AVM=avm.Value#(lf) into #values#(lf) from #pop#(lf)  inner join products.Loans l on#(lf)    #pop.LoanId=l.Id#(lf)  left outer join reports.PropertyValues pv on#(lf)    pv.Id=(select TOP 1 pv.Id from reports.PropertyValues pv where l.Id=pv.LoanID and pv.ValueType like '%BPO%')#(lf)  left outer join reports.PropertyValues avm on#(lf)    avm.Id=(select TOP 1 avm.Id from reports.PropertyValues avm where l.Id=avm.LoanID and avm.ValueType like '%AVM%')#(lf)#(lf)#(lf)#(lf)select #(lf) g.[LoanID],g.Name, g.[LoanNumber], g.[LoanFundingDate], Year='Formula', YearSummary='Formula'#(lf) , ld.LoanType, g.[CurrentAssetType]#(lf) , g.CurrentAssetStatus, LoanStatusDetails='Lookup'#(lf) , [Current Balance]=ld.CurrentUnpaidBalance, [Deferred Balance]=ld.CurrentDeferredBalance, [Total Balance]=ld.CurrentTotalBalance, ld.InterestRate#(lf) #(lf) ,RatexUPB=ld.InterestRate * ld.CurrentTotalBalance#(lf) #(lf) , ld.NextPaymentDue, #(lf) DPD='Manual'#(lf) --, [Pmt Due Sequence]='Manual', [MBA Paystring]='Manual'#(lf) , g.[State], [Top States]='',#(lf)#(lf) #(lf) g.[Combined Due Diligence Grade], g.[Title Grade], g.[Legal Grade], g.[Docs Grade], g.[Property Grade], g.[Compliance Grade], #(lf) #(lf) t.[Combined Title Grade], t.[Number of Title Issues Identified], t.[Number of Title PID's], t.[Title Insurance], #(lf) t.[Title 1st Issue Type], t.[Title 1st Issue Description], t.[Title 1st Issue Category], t.[Title 1st Issue Critical Flag], #(lf) t.[Title 2nd Issue Type], t.[Title 2nd Issue Description], t.[Title 2nd Issue Category], t.[Title 2nd Issue Critical Flag], #(lf) t.[Title 3rd Issue Type], t.[Title 3rd Issue Description], t.[Title 3rd Issue Category], t.[Title 3rd Issue Critical Flag], #(lf) t.[Title 4th Issue Type], t.[Title 4th Issue Description], t.[Title 4th Issue Category], t.[Title 4th Issue Critical Flag], #(lf) t.[Title 5th Issue Type], t.[Title 5th Issue Description], t.[Title 5th Issue Category], t.[Title 5th Issue Critical Flag], #(lf) t.[Annual Taxes], t.[Delq Taxes], t.[Muni Liens/Tax Certs (Sr)], t.[Muni Liens/Tax Certs (Jr)], t.[HOA Liens (Super)], t.[HOA Liens (Jr)], t.[Sr Mortgage], t.[Sr Lien], t.[Jr Mortgage], t.[Jr Lien], t.[IRS Liens], t.[AOM Summary], t.[Assignee], #(lf) #(lf) l.[Combined Legal Grade], l.[Number of Legal Review Tasks], l.[Current Legal Process], l.[Current Legal Sub Process], [FC Term Remaining]=case when l.[Current Legal Process]='Foreclosure' then st.FC_TermRem end, #(lf) [Contested FC]='Manual', [BK Serial Filer]='Manual', l.[Firm/Attorney Name], l.[Case Number], l.[Court Name], l.[Docket Last Entry Date], #(lf) l.[Process Monitoring], l.[Current Milestone], l.[Legal Risk Type], l.[Legal Risk Summary], l.[Legal Risk Review Task], l.[Legal Risk Urgent Flag], #(lf) l.[SOL Issue], l.[SOL Summary], l.[SOL Review Task], l.[SOL Urgent], #(lf) l.[Claim Risk (FLA) Issue], l.[Claim Risk (FLA) Summary], l.[Claim Risk (FLA) Review Task], l.[Claim Risk (FLA) Urgent], #(lf) l.[Loan Type Issue], l.[Loan Type Summary], l.[Loan Type Review Task], l.[Loan Type Urgent], #(lf) l.[Legal Keyword Issue], l.[Legal Keyword Summary], l.[Legal Keyword Review Task], l.[Legal Keyword Urgent], #(lf) #(lf) d.[Combined Docs Grade], d.[Number of Docs Review Tasks], d.[Number of Trailing Docs], #(lf) d.[Docs 1st Issue Type], d.[Docs 1st Issue Description], d.[Docs 1st Issue Critical Flag], d.[Docs 1st Issue Trailing Docs], d.[Docs 1st Issue Custodian Recon], #(lf) d.[Docs 2nd Issue Type], d.[Docs 2nd Issue Description], d.[Docs 2nd Issue Critical Flag], d.[Docs 2nd Issue Trailing Docs], d.[Docs 2nd Issue Custodian Recon], #(lf) d.[Docs 3rd Issue Type], d.[Docs 3rd Issue Description], d.[Docs 3rd Issue Critical Flag], d.[Docs 3rd Issue Trailing Docs], d.[Docs 3rd Issue Custodian Recon], #(lf) d.[Docs 4th Issue Type], d.[Docs 4th Issue Description], d.[Docs 4th Issue Critical Flag], d.[Docs 4th Issue Trailing Docs], d.[Docs 4th Issue Custodian Recon], #(lf) d.[Docs 5th Issue Type], d.[Docs 5th Issue Description], d.[Docs 5th Issue Critical Flag], d.[Docs 5th Issue Trailing Docs], d.[Docs 5th Issue Custodian Recon], #(lf) #(lf) p.[Combined Property Grade], p.[Number of Property Review Tasks]#(lf) , [Vacant Flag]='Manual', [Active Listing Flag]='Manual', [Listing Source]='Manual', [Listing Status]='Manual', [Listing Price]='Manual', [Listing Date]='Manual'#(lf) , [Seller Value]='' ,[Seller Value Date]=''#(lf) , [BPO Value]=pv.BPOValue, [BPO Date]=pv.BPODate, [AVM Value]=pv.AVM, #(lf) [Final Value Type]='', [Final Value]='', [Value Bucket]='', [LTV]='', [LTV Bucket]='', [LTVxUPB]='',#(lf) p.[Property Type Issue], p.[Property Type Summary], p.[Property Type Urgent], #(lf) p.[Property Condition Issue], p.[Property Condition Summary], p.[Property Condition Urgent], #(lf) p.[Value Discrepancy Issue], p.[Value Discrepancy Summary], p.[Value Discrepancy Urgent], #(lf) p.[DQ Tax Issue], p.[DQ Tax Summary], p.[DQ Tax Urgent]#(lf)#(lf) , [Disclosed Issue Type]=''#(lf) , [Disclosed Issue Sub-Type]=''#(lf) , [RT Results]=''#(lf) , [Issue Category]=''#(lf) , [Issue Type]=''#(lf) , [Issue Sub-Type]=''#(lf) , [Issue Description]=''#(lf) , [Issue Grade]='',#(lf)#(lf) --s.[Indicative Grade], s.[Seller Disclosure - Title], s.[Seller Disclosure - Docs], s.[Seller Disclosure - Compliance], s.[Seller Disclosure - Legal], s.[Seller Disclosure - Property], s.[Seller Disclosed Summary],#(lf)#(lf) v.B1_FICO, v.B1_BirthDate, v.B1_DeathFlag, v.B1_FraudFlag, v.B1_Employer, v.B1_EmplCurrentInd, v.B1_EmplPosition, v.B1_EmplReportedDate, v.B1_ActiveMilitaryOnStatusDate, v.B1_ActiveDutyBeginDate, v.B1_ActiveDutyEndDate, #(lf) v.B2_FICO, v.B2_BirthDate, v.B2_DeathFlag, v.B2_FraudFlag, v.B2_Employer, v.B2_EmplCurrentInd, v.B2_EmplPosition, v.B2_EmplReportedDate, v.B2_ActiveMilitaryOnStatusDate, v.B2_ActiveDutyBeginDate, v.B2_ActiveDutyEndDate, #(lf) #(lf) c.AccountType, c.Creditor, c.AccountIdentifier, c.CreditLoanType, c.AccountOwnershipType, c.AccountOpenedDate, c.AccountReportedDate, c.HighBalanceAmount, c.MonthlyPaymentAmount, c.PastDueAmount, c.UnpaidBalanceAmount, c.CurrentRatingType, c.HighestAdverseType, c.MostRecentAdverseType, c.MostRecentAdverseDate, c.PaymentPatternData, c.PaymentPatternStartDate, c.IsActive, c.Message, #(lf) All_TradeLineCount, All_UPB, All_Pmt, All_MaxBal, All_LastActivityDate, All_WorstCurrentRating, All_WorstEverRating, All_PctDQ, Mortgage_TradeLineCount, #(lf) Mortgage_UPB, Mortgage_Pmt, Mortgage_MaxBal, Mortgage_LastActivityDate, Mortgage_WorstCurrentRating, Mortgage_WorstEverRating, Mortgage_PctDQ, #(lf) CreditCard_TradeLineCount, CreditCard_UPB, CreditCard_Pmt, CreditCard_MaxBal, CreditCard_LastActivityDate, CreditCard_WorstCurrentRating, CreditCard_WorstEverRating, CreditCard_PctDQ, #(lf) Auto_TradeLineCount, Auto_UPB, Auto_Pmt, Auto_MaxBal, Auto_LastActivityDate, Auto_WorstCurrentRating, Auto_WorstEverRating, Auto_PctDQ, #(lf) Student_TradeLineCount, Student_UPB, Student_Pmt, Student_MaxBal, Student_LastActivityDate, Student_WorstCurrentRating, Student_WorstEverRating, Student_PctDQ, #(lf) Other_TradeLineCount, Other_UPB, Other_Pmt, Other_MaxBal, Other_LastActivityDate, Other_WorstCurrentRating, Other_WorstEverRating, Other_PctDQ#(lf)#(lf),[FICO Status]='', [FICO UPB]='', [Min Fico]='', [MinxUPB]='', [Max Fico]='', [Max FicoxUPB]='', [Forbearance_Flag]='', [Mod]='', #(lf)[ExceptionDate]='', [ExceptionType]='', [ExceptionDate]='', [ExceptionType]='', [ExceptionCategory]='', [ExceptionSubCat]='', [ExceptionDescription]='', [Resolution]='', [REPRICE AMT]='', #(lf)[CurRateType]=st.current_rate_type, [PropertyType]=st.property_type, [Originator]='', [DocsSummary]='', [TitleSummary]='', [LegalCount]='', [LegalSummary]=''#(lf), [PropertyCount]='', [PropertySummary]='', [JudicialvsNonJudicial]=''#(lf)#(lf)from #grades g#(lf) left outer join #title t on#(lf)   g.LoanId=t.LoanId#(lf) left outer join #legal l on#(lf)   g.LoanId=l.LoanId#(lf) left outer join #docs d on#(lf)   g.LoanId=d.LoanId#(lf) left outer join #property p on#(lf)   g.LoanID=p.LoanId#(lf) left outer join #sd s on#(lf)   g.LoanId=s.LoanId#(lf) left outer join #voms v on#(lf)   g.LoanId=v.LoanId#(lf) left outer join #cred c on#(lf)   g.LoanId=c.LoanId#(lf) left outer join products.Loans ld on#(lf)   g.LoanId=ld.Id#(lf) left outer join #status st on#(lf)   ld.Id=st.LoanID#(lf) left outer join #values pv on#(lf)   ld.Id=pv.LoanId#(lf)order by g.LoanNumber#(lf)"])