Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
CREATE VIEW [dbo].[vPVTDetail] AS
SELECT A1.EntityID,[PVT Loan Name],ParentEntityID,[Base],[Loan Date],[Finance Terms],[Principal],[DSRF Rate],[Amortization Term],[DSCR],[Year Sized],[Int. Only Period Info],[Int. Only Period - Yrs],[Benchmark Index (Period)],[Credit Spread],[Underlying Rate Per],[As of Date Per],[Index Information],[Benchmark Index],[Underlying Rate In],[As of Date Ind],[BPS Spread / Index],[Credit Spread - bps],[Lockbox/Servicing Fee],[Credit Enhancement/Insu],[Forward Premium - bps],[Other - bps],0 AS [Tot. Interest Rate],[Fees, Costs, Exp. & Res],[Underwriting Fee%],0 AS [Underwriting Fee Amt],[Debt. Serv. Reserve - mo],[Debt Serv. Reserve $],[Surety Policy Cost $],[Max Annual Debt Serv.],[Annual Reg. Costs],[Annual Trustee Fees],[Annual Surveillance Fees],[Legal Expenses $],[Rating Agency $],[Transfer Taxes $],[Title Insurance $],[Surveys $],[Bond Fee $],[Commitment Fee $],[Other Third Party $],[Environmental Fee $],[Appraisals $],[Other Expenses, Costs and Fees],0 AS [Total Exp. $],0 AS [Total Exp. % of Princ],0 AS [Principal Minus Expenses],[All-In Finance Percent],[Interest/Loan Life],[All-In Finance Cost],[Spec. Prov. (PEP 74,83)],[Min DCR(IDP)],[Min DCR(Post IDP)]
FROM ( SELECT EntityID,EntityName AS [PVT Loan Name]
FROM dbo._EntityName WHERE EntityType = 105 AND IsDeleted = 0) A1
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS int) AS ParentEntityID,EntityName AS [Base]
FROM dbo._DetailsEntity WHERE FieldID = 9320) A2 ON A1.EntityID = A2.EntityID
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS datetime) AS [Loan Date]
FROM dbo._DetailsVarchar WHERE FieldID = 9438) A3 ON A1.EntityID = A3.EntityID
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS money) AS [Finance Terms]
FROM dbo._DetailsVarchar WHERE FieldID = 9336) A4 ON A1.EntityID = A4.EntityID
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS decimal(19,4)) AS [Principal]
CREATE VIEW [dbo].[vPVTDetail] AS
SELECT A1.EntityID, A1.EntityName [PVT Loan Name]
, CAST(A2.DetailValue AS int) ParentEntityID
, A2.EntityName [Base]
, CAST(A3.DetailValue AS datetime) [Loan Date]
... etc .
FROM ( SELECT EntityID, AS [PVT Loan Name]
FROM dbo._EntityName A1
LEFT JOIN dbo._DetailsEntity A2
ON A1.EntityID = A2.EntityID
AND A2.FieldID = 9320
LEFT JOIN dbo._DetailsVarchar A3
ON A1.EntityID = A3.EntityID
AND A3.FieldID = 9438
... etc ...
WHERE A1.EntityType = 105
AND A1.IsDeleted = 0
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.