Notes

/****** Object: StoredProcedure [pric].[usp_GetPrivateFISummaryData] Script Date: 8/29/2024 2:05:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [pric].[usp_GetPrivateFISummaryData]
(
@DEAL_ID NUMERIC(22,0)
)
WITH RECOMPILE
AS
BEGIN

DECLARE @PUBDNO_DEAL_ID_CURR INT,
@CYBER_DEAL_ID_CURR INT,
@DEAL_ID_PRIOR INT,
@PUBDNO_DEAL_ID_PRIOR INT,
@CYBER_DEAL_ID_PRIOR INT

SELECT TOP 1 @PUBDNO_DEAL_ID_CURR = PDI_CURR.DEAL_ID
FROM PRIC.PT_DEAL_INFO PDI WITH(NOLOCK)
INNER JOIN PRIC.ACCOUNT_INFORMATION AI WITH(NOLOCK) ON AI.ACCOUNT_ID = PDI.ACCOUNT_ID
INNER JOIN PRIC.ACCOUNT_INFORMATION AI_CURR WITH(NOLOCK) ON AI_CURR.OPPORTUNITY_ID = AI.OPPORTUNITY_ID
INNER JOIN PRIC.PT_DEAL_INFO PDI_CURR WITH(NOLOCK) ON PDI_CURR.ACCOUNT_ID = AI_CURR.ACCOUNT_ID
AND PDI_CURR.POLICY_END_DATE = PDI.POLICY_END_DATE
AND PDI_CURR.POLICY_EFFECTIVE_DATE = PDI.POLICY_EFFECTIVE_DATE
AND PDI.DEAL_ID = @DEAL_ID
AND AI_CURR.PROCESS_TYPE = 'PBDO'
AND PDI_CURR.PROCESS_TYPE = 'PBDO'
AND PDI_CURR.DEAL_ID <> @DEAL_ID

SELECT TOP 1 @CYBER_DEAL_ID_CURR = PDI_CURR.DEAL_ID
FROM PRIC.PT_DEAL_INFO PDI WITH(NOLOCK)
INNER JOIN PRIC.ACCOUNT_INFORMATION AI WITH(NOLOCK) ON AI.ACCOUNT_ID = PDI.ACCOUNT_ID
INNER JOIN PRIC.ACCOUNT_INFORMATION AI_CURR WITH(NOLOCK) ON AI_CURR.OPPORTUNITY_ID = AI.OPPORTUNITY_ID
INNER JOIN PRIC.PT_DEAL_INFO PDI_CURR WITH(NOLOCK) ON PDI_CURR.ACCOUNT_ID = AI_CURR.ACCOUNT_ID
AND PDI_CURR.POLICY_END_DATE = PDI.POLICY_END_DATE
AND PDI_CURR.POLICY_EFFECTIVE_DATE = PDI.POLICY_EFFECTIVE_DATE
AND PDI.DEAL_ID = @DEAL_ID
AND AI_CURR.PROCESS_TYPE = 'CRRM'
AND PDI_CURR.PROCESS_TYPE = 'CRRM'
AND PDI_CURR.DEAL_ID <> @DEAL_ID

SELECT TOP 1 @DEAL_ID_PRIOR = PDI.DEAL_ID
FROM PRIC.PT_DEAL_INFO PDI WITH(NOLOCK)
INNER JOIN (SELECT POLICY_NUMBER, POLICY_EFFECTIVE_DATE FROM PRIC.PT_DEAL_INFO WITH(NOLOCK) WHERE DEAL_ID = @DEAL_ID) PDI_DEAL
ON PDI.POLICY_NUMBER = PDI_DEAL.POLICY_NUMBER
AND PDI.POLICY_EFFECTIVE_DATE BETWEEN PDI_DEAL.POLICY_EFFECTIVE_DATE
AND DATEADD(YEAR,-5,PDI_DEAL.POLICY_EFFECTIVE_DATE)
AND PDI.DEAL_ID <> @DEAL_ID

SELECT TOP 1 @PUBDNO_DEAL_ID_PRIOR = PDI_PRIOR.DEAL_ID
FROM PRIC.PT_DEAL_INFO PDI WITH(NOLOCK)
INNER JOIN PRIC.ACCOUNT_INFORMATION AI WITH(NOLOCK) ON AI.ACCOUNT_ID = PDI.ACCOUNT_ID
INNER JOIN PRIC.ACCOUNT_INFORMATION AI_CURR WITH(NOLOCK) ON AI_CURR.OPPORTUNITY_ID = AI.OPPORTUNITY_ID
INNER JOIN PRIC.PT_DEAL_INFO PDI_PRIOR WITH(NOLOCK) ON PDI_PRIOR.ACCOUNT_ID = AI_CURR.ACCOUNT_ID
AND PDI_PRIOR.POLICY_END_DATE = PDI.POLICY_END_DATE
AND PDI_PRIOR.POLICY_EFFECTIVE_DATE = PDI.POLICY_EFFECTIVE_DATE
AND PDI.DEAL_ID = @DEAL_ID_PRIOR
AND AI_CURR.PROCESS_TYPE = 'PBDO'
AND PDI_PRIOR.PROCESS_TYPE = 'PBDO'
AND PDI_PRIOR.DEAL_ID <> @DEAL_ID_PRIOR

SELECT TOP 1 @CYBER_DEAL_ID_PRIOR = PDI_PRIOR.DEAL_ID
FROM PRIC.PT_DEAL_INFO PDI WITH(NOLOCK)
INNER JOIN PRIC.ACCOUNT_INFORMATION AI WITH(NOLOCK) ON AI.ACCOUNT_ID = PDI.ACCOUNT_ID
INNER JOIN PRIC.ACCOUNT_INFORMATION AI_CURR WITH(NOLOCK) ON AI_CURR.OPPORTUNITY_ID = AI.OPPORTUNITY_ID
INNER JOIN PRIC.PT_DEAL_INFO PDI_PRIOR WITH(NOLOCK) ON PDI_PRIOR.ACCOUNT_ID = AI_CURR.ACCOUNT_ID
AND PDI_PRIOR.POLICY_END_DATE = PDI.POLICY_END_DATE
AND PDI_PRIOR.POLICY_EFFECTIVE_DATE = PDI.POLICY_EFFECTIVE_DATE
AND PDI.DEAL_ID = @DEAL_ID_PRIOR
AND AI_CURR.PROCESS_TYPE = 'CRRM'
AND PDI_PRIOR.PROCESS_TYPE = 'CRRM'
AND PDI_PRIOR.DEAL_ID <> @DEAL_ID_PRIOR



SELECT CURR_DATA.*, PRIOR_DATA.* FROM
(SELECT
policyNumber = PDI_CURR.POLICY_NUMBER
,insuredName = PDI_CURR.NAMED_INSURED
,insuredState = PDI_CURR.State
,insuredZip = PDI_CURR.INSURED_ZIP
,sicCode = PDI_CURR.SIC_CODE
,sicDescription = PDI_CURR.SIC_DESCRIPTION
,currentEffectiveDate = PDI_CURR.POLICY_EFFECTIVE_DATE
,accessPrimary = PDI_CURR.EXC_OR_PRIM
,currentExpirationDate = PDI_CURR.POLICY_END_DATE
,currentCommision = PDI_CURR.COMMISSION
,productName = AM_CURR.AM_PRODUCT



,currentPVTDnOTechnicalPremium = CONVERT(DECIMAL(20,2),PVTDNO_CURR.TECH_PREM)
,currentPVTDnOSharedLimit = CONVERT(DECIMAL(20,3),PVTDNO_CURR.SHARED_LIMIT_FCT)
,currentPVTDnOFinalTechnicalPremium = CONVERT(DECIMAL(20,2),PVTDNO_CURR.FINAL_TECH_PREM)
,currentPVTDnOCharedPremium = CONVERT(DECIMAL(20,2),PVTDNO_CURR.CHRG_PREM)
,currentPVTDnOSoldToTechnical = CONVERT(DECIMAL(20,4),PVTDNO_CURR.SOLD_TO_TECH)


,currentEPLTechnicalPremium = CONVERT(DECIMAL(20,2),EPL_CURR.TECH_PREM)
,currentEPLSharedLimit = CONVERT(DECIMAL(20,3),EPL_CURR.SHARED_LIMIT_FCT)
,currentEPLFinalTechnicalPremium = CONVERT(DECIMAL(20,2),EPL_CURR.FINAL_TECH_PREM)
,currentEPLCharedPremium = CONVERT(DECIMAL(20,2),EPL_CURR.CHRG_PREM)
,currentEPLSoldToTechnical = CONVERT(DECIMAL(20,4),EPL_CURR.SOLD_TO_TECH)


,currentPBTDnOTechnicalPremium = CONVERT(DECIMAL(20,2),PUBDNO_CURR.TECH_PREM)
,currentPBTDnOSharedLimit = CONVERT(DECIMAL(20,3),PUBDNO_CURR.SHR_LIMIT_FCT)
,currentPBTDnOFinalTechnicalPremium = CONVERT(DECIMAL(20,2),PUBDNO_CURR.FI_DnO_TECH_PREM)
,currentPBTDnOCharedPremium = CONVERT(DECIMAL(20,2),PUBDNO_CURR.CHRG_PREM)
,currentPBTDnOSoldToTechnical = CONVERT(DECIMAL(20,4),PUBDNO_CURR.SOLD_TO_TECH)


,currentAMTechnicalPremium = CONVERT(DECIMAL(20,2),AM_CURR.TECH_PREM)
,currentAMSharedLimit = CONVERT(DECIMAL(20,3),AM_CURR.SHARED_LIMIT_FCT)
,currentAMFinalTechnicalPremium = CONVERT(DECIMAL(20,2),AM_CURR.FINAL_TECH_PREM)
,currentAMCharedPremium = CONVERT(DECIMAL(20,2),AM_CURR.CHRG_PREM)
,currentAMSoldToTechnical = CONVERT(DECIMAL(20,4),AM_CURR.SOLD_TO_TECH)


,currentFiduciaryTechnicalPremium = CONVERT(DECIMAL(20,2),FI_CURR.TECH_PREM)
,currentFiduciarySharedLimit = CONVERT(DECIMAL(20,3),FI_CURR.SHARED_LIMIT_FCT)
,currentFiduciaryFinalTechnicalPremium = CONVERT(DECIMAL(20,2),FI_CURR.FINAL_TECH_PREM)
,currentFiduciaryCharedPremium = CONVERT(DECIMAL(20,2),FI_CURR.CHRG_PREM)
,currentFiduciarySoldToTechnical = CONVERT(DECIMAL(20,4),FI_CURR.SOLD_TO_TECH)


,currentICPLTechnicalPremium = CONVERT(DECIMAL(20,2),ICPL_CURR.TECH_PREM)
,currentICPLSharedLimit = CONVERT(DECIMAL(20,3),ICPL_CURR.SHARED_LIMIT_FCT)
,currentICPLFinalTechnicalPremium = CONVERT(DECIMAL(20,2),ICPL_CURR.FINAL_TECH_PREM)
,currentICPLCharedPremium = CONVERT(DECIMAL(20,2),ICPL_CURR.CHRG_PREM)
,currentICPLSoldToTechnical = CONVERT(DECIMAL(20,4),ICPL_CURR.SOLD_TO_TECH)


,currentFIBondTechnicalPremium = CONVERT(DECIMAL(20,2),FBR_CURR.TECH_PREM)
,currentFIBondSharedLimit = CONVERT(DECIMAL(20,3),FBR_CURR.SHARED_LIMIT_FCT)
,currentFIBondFinalTechnicalPremium = CONVERT(DECIMAL(20,2),FBR_CURR.FINAL_TECH_PREM)
,currentFIBondCharedPremium = CONVERT(DECIMAL(20,2),FBR_CURR.CHRG_PREM_PRE_BLEND)
,currentFIBondSoldToTechnical = CONVERT(DECIMAL(20,4),FBR_CURR.SOLD_TO_TECH)


,currentCommercialCrimeTechnicalPremium = CONVERT(DECIMAL(20,2),CCR_CURR.TECH_PREM)
,currentCommercialCrimeSharedLimit = CONVERT(DECIMAL(20,3),CCR_CURR.SHARED_LIMIT_FCT)
,currentCommercialCrimeFinalTechnicalPremium = CONVERT(DECIMAL(20,2),CCR_CURR.FINAL_TECH_PREM)
,currentCommercialCrimeCharedPremium = CONVERT(DECIMAL(20,2),CCR_CURR.CHRG_PREM_PRE_BLEND)
,currentCommercialCrimeSoldToTechnical = CONVERT(DECIMAL(20,4),CCR_CURR.SOLD_TO_TECH)


,currentBPLTechnicalPremium = CONVERT(DECIMAL(20,2),BPL_CURR.TECH_PREM)
,currentBPLSharedLimit = CONVERT(DECIMAL(20,3),BPL_CURR.SHARED_LIMIT_FCT)
,currentBPLFinalTechnicalPremium = CONVERT(DECIMAL(20,2),BPL_CURR.FINAL_TECH_PREM)
,currentBPLCharedPremium = CONVERT(DECIMAL(20,2),BPL_CURR.CHRG_PREM)
,currentBPLSoldToTechnical = CONVERT(DECIMAL(20,4),BPL_CURR.SOLD_TO_TECH)
,isbplcoverage = BPL_CURR.BPL_COVERAGE



,currentCyberTechnicalPremium = CONVERT(DECIMAL(20,2), CCC_CURR.TECH_PREM)
,currentCyberSharedLimit = CONVERT(DECIMAL(20,3),CCC_CURR.SHARED_LIMIT_FCT)
,currentCyberFinalTechnicalPremium = CONVERT(DECIMAL(20,2), PDI_CYBER_CURR.TOTAL_TECH_PREM)
,currentCyberLCharedPremium = CONVERT(DECIMAL(20,2), CCC_CURR.CHRG_PREM)
,currentCyberSoldToTechnical = CONVERT(DECIMAL(20,4), PDI_CYBER_CURR.TOTAL_SOLD_TO_TECH)

--TECHNICAL PREMIUM
,currentTotalTechnicalPremium = PDI_CURR.TOTAL_TECH_PREM
--CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.TECH_PREM,0) + ISNULL(EPL_CURR.TECH_PREM,0) + ISNULL(PUBDNO_CURR.FI_DnO_TECH_PREM,0) + ISNULL(AM_CURR.TECH_PREM,0) + ISNULL(FI_CURR.TECH_PREM,0) + ISNULL(ICPL_CURR.TECH_PREM,0) + ISNULL(FBR_CURR.TECH_PREM,0) + ISNULL(CCR_CURR.TECH_PREM,0) + ISNULL(BPL_CURR.TECH_PREM,0) + ISNULL(CCC_CURR.TECH_PREM,0) )

--TOTAL LAYER LIMIT
,currentTotalLayerLimit = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.TOTAL_LYR_LIMIT,0) + ISNULL(EPL_CURR.TOTAL_LYR_LIMIT,0) + ISNULL(PUBDNO_CURR.LYR_LIMIT,0) + ISNULL(AM_CURR.TOTAL_LYR_LIMIT,0) + ISNULL(FI_CURR.TOTAL_LYR_LIMIT,0) + ISNULL(ICPL_CURR.TOTAL_LYR_LIMIT,0) + ISNULL(BPL_CURR.TOTAL_LYR_LIMIT,0) )

--FINAL TECHNICAL PREMIUM
,currentTotalFinalTechnicalPremium = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.FINAL_TECH_PREM,0) + ISNULL(EPL_CURR.FINAL_TECH_PREM,0) + ISNULL(PUBDNO_CURR.FI_DnO_TECH_PREM,0) + ISNULL(AM_CURR.FINAL_TECH_PREM,0) + ISNULL(FI_CURR.FINAL_TECH_PREM,0) + ISNULL(ICPL_CURR.FINAL_TECH_PREM,0) + ISNULL(FBR_CURR.FINAL_TECH_PREM,0) + ISNULL(CCR_CURR.FINAL_TECH_PREM,0) + ISNULL(BPL_CURR.FINAL_TECH_PREM,0) + ISNULL(PDI_CYBER_CURR.TOTAL_TECH_PREM,0) )

--CHARGED PREMIUM
,currentTotalLCharedPremium = PDI_CURR.TOTAL_CHRG_PREM
--CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.CHRG_PREM,0) + ISNULL(EPL_CURR.CHRG_PREM,0) + ISNULL(PUBDNO_CURR.CHRG_PREM,0) + ISNULL(AM_CURR.CHRG_PREM,0) + ISNULL(FI_CURR.CHRG_PREM,0) + ISNULL(ICPL_CURR.CHRG_PREM,0) + ISNULL(FBR_CURR.CHRG_PREM_PRE_BLEND,0) + ISNULL(CCR_CURR.CHRG_PREM_PRE_BLEND,0) + ISNULL(BPL_CURR.CHRG_PREM,0) + ISNULL(CCC_CURR.CHRG_PREM,0) )

--SOLD TO TECHNICAL
,currentTotalSoldToTechnical = PDI_CURR.TOTAL_SOLD_TO_TECH
--(CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.CHRG_PREM,0) + ISNULL(EPL_CURR.CHRG_PREM,0) + ISNULL(PUBDNO_CURR.CHRG_PREM,0) + ISNULL(AM_CURR.CHRG_PREM,0) + ISNULL(FI_CURR.CHRG_PREM,0) + ISNULL(ICPL_CURR.CHRG_PREM,0) + ISNULL(FBR_CURR.CHRG_PREM_PRE_BLEND,0) + ISNULL(CCR_CURR.CHRG_PREM_PRE_BLEND,0) + ISNULL(BPL_CURR.CHRG_PREM,0) + ISNULL(CCC_CURR.CHRG_PREM,0) )) / ( CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.FINAL_TECH_PREM,0) + ISNULL(EPL_CURR.FINAL_TECH_PREM,0) + ISNULL(PUBDNO_CURR.FI_DnO_TECH_PREM,0) + ISNULL(AM_CURR.FINAL_TECH_PREM,0) + ISNULL(FI_CURR.FINAL_TECH_PREM,0) + ISNULL(ICPL_CURR.FINAL_TECH_PREM,0) + ISNULL(FBR_CURR.FINAL_TECH_PREM,0) + ISNULL(CCR_CURR.FINAL_TECH_PREM,0) + ISNULL(BPL_CURR.FINAL_TECH_PREM,0) + ISNULL(PDI_CYBER_CURR.TOTAL_TECH_PREM,0) ))

--STANDARD PREMIUM
,currentStandardPremium = PDI_CURR.TOTAL_STND_PREM
--CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.STND_PREM,0) + ISNULL(EPL_CURR.STND_PREM,0) + ISNULL(PUBDNO_CURR.STND_PREM,0) + ISNULL(AM_CURR.STND_PREM,0) + ISNULL(FI_CURR.STND_PREM,0) + ISNULL(ICPL_CURR.STND_PREM,0) + ISNULL(BPL_CURR.STND_PREM,0) )

--QBE LIMIT
,currentQbeLimit = CONVERT(DECIMAL(20,2), ISNULL(AM_CURR.QBE_LIMIT,0) + ISNULL(BPL_CURR.QBE_LIMIT,0) + ISNULL(EPL_CURR.QBE_LIMIT,0) + ISNULL(FI_CURR.QBE_LIMIT,0) + ISNULL(PVTDNO_CURR.QBE_LIMIT,0) + ISNULL(ICPL_CURR.QBE_LIMIT,0) + ISNULL(CCO_CURR.QBE_SHARES,0) )


--SIR
,currentSelfinsuredRetentionSIR = CONVERT(DECIMAL(20,2), ISNULL(AM_CURR.SIR,0) + ISNULL(BPL_CURR.SIR,0) + ISNULL(EPL_CURR.SIR,0) + ISNULL(FI_CURR.SIR,0) + ISNULL(PVTDNO_CURR.SIR,0) + ISNULL(ICPL_CURR.SIR,0) )

--UNDERLYING LIMIT
,currentUnderlyingLimit = CONVERT(DECIMAL(20,2), ISNULL(AM_CURR.UNLY_LIMIT,0) + ISNULL(BPL_CURR.UNLY_LIMIT,0) + ISNULL(EPL_CURR.UNLY_LIMIT,0) + ISNULL(FI_CURR.UNLY_LIMIT,0) + ISNULL(PVTDNO_CURR.UNLY_LIMIT,0) + ISNULL(ICPL_CURR.UNLY_LIMIT,0) + ISNULL(CCO_CURR.ATTACHMENT_UNDERLYING_LIMIT,0) )

--SOLD TO STANDARD
,currentSoldToStandard = PDI_CURR.TOTAL_SOLD_TO_STND
--CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.SOLD_TO_STND,0) + ISNULL(EPL_CURR.SOLD_TO_STND,0) + ISNULL(AM_CURR.SOLD_TO_STND,0) + ISNULL(FI_CURR.SOLD_TO_STND,0) + ISNULL(ICPL_CURR.SOLD_TO_STND,0) + ISNULL(BPL_CURR.SOLD_TO_STND,0) )

--EPL WEIGHTED TOTAL EMPLOYEES
,currentEPLWeightedTotalEmployees = CONVERT(DECIMAL(20,2), EPL_CURR.FTE_WGT)

--DNO PREMIUM WEIGHT COMPARED TO EPL
--,currentDnOPremiumWeightComparedToEPL = CONVERT(DECIMAL(20,2), EPL_CURR.FTE_WGT)

,currentDnOPremiumweightComparedToEPL = CONVERT(decimal(20,2),PVTDNO_CURR.STND_PREM) / CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_CURR.STND_PREM,0) + ISNULL(EPL_CURR.STND_PREM,0) + ISNULL(PUBDNO_CURR.STND_PREM,0) + ISNULL(AM_CURR.STND_PREM,0) + ISNULL(FI_CURR.STND_PREM,0) + ISNULL(ICPL_CURR.STND_PREM,0) + ISNULL(BPL_CURR.STND_PREM,0)+ISNULL(PDI_CYBER_CURR.TOTAL_TECH_PREM,0) )
-- DNO LIMIT
,currentDnOLimit = CONVERT(DECIMAL(20,2), PVTDNO_CURR.QBE_LIMIT)

-- DNO RETENTION
,currentDnORetention = CONVERT(DECIMAL(20,2), PVTDNO_CURR.SIR)

--DNO UNDERLYING LIMIT
,currentDnOUnderlyingLimit = CONVERT(DECIMAL(20,2), PVTDNO_CURR.UNLY_LIMIT)

-- EPL LIMIT
,currentEPLLimit = CONVERT(DECIMAL(20,2), EPL_CURR.QBE_LIMIT)

-- EPL RETENTION
,currentEPLRetention = CONVERT(DECIMAL(20,2), EPL_CURR.SIR)

-- EPL UNDERLYING LIMIT
,currentEPLUnderlyingLimit = CONVERT(DECIMAL(20,2), EPL_CURR.UNLY_LIMIT)

--SHARED LIMIT FACTOR
,currentTotalSharedLimit = CONVERT(DECIMAL(20,3), ISNULL(CCC_CURR.SHARED_LIMIT_FCT,0) + ISNULL(BPL_CURR.SHARED_LIMIT_FCT,0) + ISNULL(CCR_CURR.SHARED_LIMIT_FCT,0) + ISNULL(FBR_CURR.SHARED_LIMIT_FCT,0) + ISNULL(ICPL_CURR.SHARED_LIMIT_FCT,0) + ISNULL(FI_CURR.SHARED_LIMIT_FCT,0) + ISNULL(AM_CURR.SHARED_LIMIT_FCT,0) + ISNULL(PUBDNO_CURR.SHR_LIMIT_FCT,0) + ISNULL(EPL_CURR.SHARED_LIMIT_FCT,0) + ISNULL(PVTDNO_CURR.SHARED_LIMIT_FCT,0))

FROM
--CURR
PRIC.PT_DEAL_INFO PDI_CURR WITH(NOLOCK)
INNER JOIN PRIC.AMRater AM_CURR WITH(NOLOCK) ON AM_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.BPLRater BPL_CURR WITH(NOLOCK) ON BPL_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.EPLRater EPL_CURR WITH(NOLOCK) ON EPL_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.PrivateDnORater PVTDNO_CURR WITH(NOLOCK) ON PVTDNO_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.FiduciaryRater FI_CURR WITH(NOLOCK) ON FI_CURR.DEAL_ID = PDI_CURR.DEAL_ID
LEFT JOIN PRIC.PublicDnORater PUBDNO_CURR WITH(NOLOCK) ON PUBDNO_CURR.DEAL_ID = @PUBDNO_DEAL_ID_CURR
INNER JOIN PRIC.FIICPLRater ICPL_CURR WITH(NOLOCK) ON ICPL_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.CommercialCrimeRater CCR_CURR WITH(NOLOCK) ON CCR_CURR.DEAL_ID = PDI_CURR.DEAL_ID
INNER JOIN PRIC.FIBondRater FBR_CURR WITH(NOLOCK) ON FBR_CURR.DEAL_ID = PDI_CURR.DEAL_ID
LEFT JOIN PRIC.PT_DEAL_INFO PDI_CYBER_CURR WITH(NOLOCK) ON PDI_CYBER_CURR.DEAL_ID = @CYBER_DEAL_ID_CURR
LEFT JOIN PRIC.CyberCoverageCommon CCC_CURR WITH(NOLOCK) ON CCC_CURR.DEAL_ID = @CYBER_DEAL_ID_CURR
LEFT JOIN (SELECT CCO.*
FROM pric.CyberCoverageCommon CCC
INNER JOIN PRIC.CyberCoverageOption CCO ON CCO.DEAL_ID = CCC.DEAL_ID
WHERE ISNULL(CCC.SELECTED_COVERAGE_OPT,'') <> ''
AND CCO.CYBERCOVERAGE_ID = 0
AND TRIM(CCO.OPTION_TYPE) = 'Option ' + SUBSTRING(TRIM(CCC.SELECTED_COVERAGE_OPT), LEN(TRIM(CCC.SELECTED_COVERAGE_OPT)), 1)
AND CCC.DEAL_ID = @CYBER_DEAL_ID_CURR) CCO_CURR ON CCO_CURR.DEAL_ID = @CYBER_DEAL_ID_CURR
WHERE PDI_CURR.DEAL_ID = @DEAL_ID
) CURR_DATA

LEFT JOIN (SELECT

priorEffectiveDate = PDI_PRIOR.POLICY_EFFECTIVE_DATE
,priorExpirationDate = PDI_PRIOR.POLICY_END_DATE
,PriorCommision = PDI_PRIOR.COMMISSION

--TECHNICAL PREMIUM
,priorTechnicalPremium = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.TECH_PREM,0) + ISNULL(EPL_PRIOR.TECH_PREM,0) + ISNULL(PUBDNO_PRIOR.TECH_PREM,0) + ISNULL(AM_PRIOR.TECH_PREM,0) + ISNULL(FI_PRIOR.TECH_PREM,0) + ISNULL(ICPL_PRIOR.TECH_PREM,0) + ISNULL(FBR_PRIOR.TECH_PREM,0) + ISNULL(CCR_PRIOR.TECH_PREM,0) + ISNULL(BPL_PRIOR.TECH_PREM,0) + ISNULL(CCC_PRIOR.TECH_PREM,0) )

--TOTAL LAYER LIMIT
,priorTotalLayerLimit = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.TOTAL_LYR_LIMIT,0) + ISNULL(EPL_PRIOR.TOTAL_LYR_LIMIT,0) + ISNULL(PUBDNO_PRIOR.LYR_LIMIT,0) + ISNULL(AM_PRIOR.TOTAL_LYR_LIMIT,0) + ISNULL(FI_PRIOR.TOTAL_LYR_LIMIT,0) + ISNULL(ICPL_PRIOR.TOTAL_LYR_LIMIT,0) + ISNULL(BPL_PRIOR.TOTAL_LYR_LIMIT,0) )

--CHARGED PREMIUM
,priorChargedPremium = CONVERT(DECIMAL(20,2), ISNULL(AM_PRIOR.CHRG_PREM,0) + ISNULL(BPL_PRIOR.CHRG_PREM,0) + ISNULL(EPL_PRIOR.CHRG_PREM,0) + ISNULL(FI_PRIOR.CHRG_PREM,0) + ISNULL(PVTDNO_PRIOR.CHRG_PREM,0) + ISNULL(ICPL_PRIOR.CHRG_PREM,0) + ISNULL(PUBDNO_PRIOR.CHRG_PREM,0) + ISNULL(CCR_PRIOR.CHRG_PREM_PRE_BLEND,0) + ISNULL(FBR_PRIOR.CHRG_PREM_PRE_BLEND,0) + ISNULL(CCC_PRIOR.CHRG_PREM,0) )

--SOLD TO TECHNICAL
,priorSoldToTechnical = CONVERT(DECIMAL(20,4), ISNULL(PVTDNO_PRIOR.SOLD_TO_TECH,0) + ISNULL(EPL_PRIOR.SOLD_TO_TECH,0) + ISNULL(PUBDNO_PRIOR.SOLD_TO_TECH,0) + ISNULL(AM_PRIOR.SOLD_TO_TECH,0) + ISNULL(FI_PRIOR.SOLD_TO_TECH,0) + ISNULL(ICPL_PRIOR.SOLD_TO_TECH,0) + ISNULL(FBR_PRIOR.SOLD_TO_TECH,0) + ISNULL(CCR_PRIOR.SOLD_TO_TECH,0) + ISNULL(BPL_PRIOR.SOLD_TO_TECH,0) + ISNULL(CCC_PRIOR.TECHNICAL_ADEQUACY,0) )

--STANDARD PREMIUM
,priorStandardPremium = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.STND_PREM,0) + ISNULL(EPL_PRIOR.STND_PREM,0) + ISNULL(PUBDNO_PRIOR.STND_PREM,0) + ISNULL(AM_PRIOR.STND_PREM,0) + ISNULL(FI_PRIOR.STND_PREM,0) + ISNULL(ICPL_PRIOR.STND_PREM,0) + ISNULL(BPL_PRIOR.STND_PREM,0) )

--QBE LIMIT
,priorQBELimit = CONVERT(DECIMAL(20,2), ISNULL(AM_PRIOR.QBE_LIMIT,0) + ISNULL(BPL_PRIOR.QBE_LIMIT,0) + ISNULL(EPL_PRIOR.QBE_LIMIT,0) + ISNULL(FI_PRIOR.QBE_LIMIT,0) + ISNULL(PVTDNO_PRIOR.QBE_LIMIT,0) + ISNULL(ICPL_PRIOR.QBE_LIMIT,0) + ISNULL(CCO_PRIOR.QBE_SHARES,0) )

--SIR
,priorSelfinsuredRetentionSIR = CONVERT(DECIMAL(20,2), ISNULL(AM_PRIOR.SIR,0) + ISNULL(BPL_PRIOR.SIR,0) + ISNULL(EPL_PRIOR.SIR,0) + ISNULL(FI_PRIOR.SIR,0) + ISNULL(PVTDNO_PRIOR.SIR,0) + ISNULL(ICPL_PRIOR.SIR,0) )

--UNDERLYING LIMIT
,priorUnderlyingLimit = CONVERT(DECIMAL(20,2), ISNULL(AM_PRIOR.UNLY_LIMIT,0) + ISNULL(BPL_PRIOR.UNLY_LIMIT,0) + ISNULL(EPL_PRIOR.UNLY_LIMIT,0) + ISNULL(FI_PRIOR.UNLY_LIMIT,0) + ISNULL(PVTDNO_PRIOR.UNLY_LIMIT,0) + ISNULL(ICPL_PRIOR.UNLY_LIMIT,0) + ISNULL(CCO_PRIOR.ATTACHMENT_UNDERLYING_LIMIT,0) )

--SOLD TO STANDARD
,priorSoldToStandard = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.SOLD_TO_STND,0) + ISNULL(EPL_PRIOR.SOLD_TO_STND,0) + ISNULL(AM_PRIOR.SOLD_TO_STND,0) + ISNULL(FI_PRIOR.SOLD_TO_STND,0) + ISNULL(ICPL_PRIOR.SOLD_TO_STND,0) + ISNULL(BPL_PRIOR.SOLD_TO_STND,0) )

--EPL WEIGHTED TOTAL EMPLOYEES
,priorEPLWeightedTotalEmployees = CONVERT(DECIMAL(20,2), EPL_PRIOR.FTE_WGT)

--DNO PREMIUM WEIGHT COMPARED TO EPL
--,priorDnOPremiumWeightComparedToEPL = CONVERT(DECIMAL(20,2), EPL_PRIOR.FTE_WGT)
,priorDnOPremiumWeightComparedToEPL = CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.SOLD_TO_STND,0)) / CONVERT(DECIMAL(20,2), ISNULL(PVTDNO_PRIOR.SOLD_TO_STND,0) + ISNULL(EPL_PRIOR.SOLD_TO_STND,0) + ISNULL(AM_PRIOR.SOLD_TO_STND,0) + ISNULL(FI_PRIOR.SOLD_TO_STND,0) + ISNULL(ICPL_PRIOR.SOLD_TO_STND,0) + ISNULL(BPL_PRIOR.SOLD_TO_STND,0)+ISNULL(PDI_CYBER_PRIOR.TOTAL_TECH_PREM,0) )

-- DNO LIMIT
,priorDnOLimit = CONVERT(DECIMAL(20,2), PVTDNO_PRIOR.QBE_LIMIT)

-- DNO RETENTION
,priorDnORetention = CONVERT(DECIMAL(20,2), PVTDNO_PRIOR.SIR)

--DNO UNDERLYING LIMIT
,priorDnOUnderlyingLimit = CONVERT(DECIMAL(20,2), PVTDNO_PRIOR.UNLY_LIMIT)

-- EPL LIMIT
,priorEPLLimit = CONVERT(DECIMAL(20,2), EPL_PRIOR.QBE_LIMIT)

-- EPL RETENTION
,priorEPLRetention = CONVERT(DECIMAL(20,2), EPL_PRIOR.SIR)

-- EPL UNDERLYING LIMIT
,priorEPLUnderlyingLimit = CONVERT(DECIMAL(20,2), EPL_PRIOR.UNLY_LIMIT)


FROM
--PRIOR
PRIC.PT_DEAL_INFO PDI_PRIOR WITH(NOLOCK)
INNER JOIN PRIC.AMRater AM_PRIOR WITH(NOLOCK) ON AM_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.BPLRater BPL_PRIOR WITH(NOLOCK) ON BPL_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.EPLRater EPL_PRIOR WITH(NOLOCK) ON EPL_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.PrivateDnORater PVTDNO_PRIOR WITH(NOLOCK) ON PVTDNO_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.FiduciaryRater FI_PRIOR WITH(NOLOCK) ON FI_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
LEFT JOIN PRIC.PublicDnORater PUBDNO_PRIOR WITH(NOLOCK) ON PUBDNO_PRIOR.DEAL_ID = @PUBDNO_DEAL_ID_PRIOR
INNER JOIN PRIC.FIICPLRater ICPL_PRIOR WITH(NOLOCK) ON ICPL_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.CommercialCrimeRater CCR_PRIOR WITH(NOLOCK) ON CCR_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
INNER JOIN PRIC.FIBondRater FBR_PRIOR WITH(NOLOCK) ON FBR_PRIOR.DEAL_ID = PDI_PRIOR.DEAL_ID
LEFT JOIN PRIC.PT_DEAL_INFO PDI_CYBER_PRIOR WITH(NOLOCK) ON PDI_CYBER_PRIOR.DEAL_ID = @CYBER_DEAL_ID_PRIOR
LEFT JOIN PRIC.CyberCoverageCommon CCC_PRIOR WITH(NOLOCK) ON CCC_PRIOR.DEAL_ID = @CYBER_DEAL_ID_PRIOR
LEFT JOIN (SELECT CCO.*
FROM pric.CyberCoverageCommon CCC
INNER JOIN PRIC.CyberCoverageOption CCO ON CCO.DEAL_ID = CCC.DEAL_ID
WHERE ISNULL(CCC.SELECTED_COVERAGE_OPT,'') <> ''
AND CCO.CYBERCOVERAGE_ID = 0
AND TRIM(CCO.OPTION_TYPE) = 'Option ' + SUBSTRING(TRIM(CCC.SELECTED_COVERAGE_OPT), LEN(TRIM(CCC.SELECTED_COVERAGE_OPT)), 1)
AND CCC.DEAL_ID = @CYBER_DEAL_ID_PRIOR) CCO_PRIOR ON CCO_PRIOR.DEAL_ID = @CYBER_DEAL_ID_PRIOR
WHERE PDI_PRIOR.DEAL_ID = @DEAL_ID_PRIOR
) PRIOR_DATA
ON 1=1


END


SET QUOTED_IDENTIFIER ON


The calculation of Total tech, total charged, and total sold to tech premiums is being performed in 3 locations : backend API, save/update SP, and the GET SP. we need to consolidate it to a single location.