--/*
DECLARE 
	@Period VARCHAR(20)='202406',
	@Facility NVARCHAR(20)='',
	@ShowFacility BIT=0,
	@GroupBy NVARCHAR(20)='Lineslip',
	@FrontingCarrier NVARCHAR(256)='',
	@PlacingBroker NVARCHAR(256)='',
	@Carrier NVARCHAR(256)='Certus',
	@LineslipReference NVARCHAR(30) = '',
	@BinderUMR NVARCHAR(30)='',
	@IndemnityClient NVARCHAR(65) ='',
	@EventName NVARCHAR(50)='',
	@CatCode NVARCHAR(50)='',
	@UMR NVARCHAR(20)='',
	@ClaimReference NVARCHAR(20)='',
	@Threshold VARCHAR(10)='',
	@Class NVARCHAR(256)='',
	@ExClass NVARCHAR(256)='Aviation',
	@ProductType NVARCHAR(256)='',
	@ExProductType NVARCHAR(256)='',
	@PolicyInceptionDateFrom VARCHAR(8)='',
	@PolicyInceptionDateTo VARCHAR(8)='',
	@ZeroClosed NCHAR(1)='',
	@LegacyBackload NCHAR(1)=''
--*/

SELECT
	CarrierKey
INTO #CarrierList
FROM tblDimCarrier c
INNER JOIN (
	SELECT CarrierName=Field2
	FROM IC.tblLookup
	WHERE LookupFilter='CarrierList'
	AND Field1=@Carrier
) l ON l.CarrierName=c.CarrierName


CREATE TABLE #FrontingOverride (
	PolicyKey INT,
	Line DECIMAL(16,8)
)

IF @FrontingCarrier='xMitsui' BEGIN
	INSERT INTO #FrontingOverride
	SELECT
		PolicyKey,
		Line=CONVERT(DECIMAL(16,8),Field7)/100
	FROM IC.tblLookup l
	INNER JOIN vwDimPolicy p 
		ON p.PolicyReference=Field1
		AND p.SectionCode=Field2
	WHERE LookupFilter LIKE 'MitsuiPolicySection%'
	AND ISNULL(Field7,'')<>''

	CREATE INDEX ix1 ON #FrontingOverride (PolicyKey)
END

SELECT
	pm.PolicyMarketKey
INTO #TPM
FROM tblDimPolicyMarket pm
INNER JOIN vwDimCarrier c ON c.CarrierKey=pm.CarrierKey
LEFT OUTER JOIN #CarrierList cl ON cl.CarrierKey=pm.CarrierKey
LEFT OUTER JOIN vwDimFrontingCarrier fc1 ON fc1.FrontingCarrierkey=pm.L1Carrierkey
LEFT OUTER JOIN vwDimFrontingCarrier fc2 ON fc2.FrontingCarrierkey=pm.L2Carrierkey
LEFT OUTER JOIN vwDimFrontingCarrier fc3 ON fc3.FrontingCarrierkey=pm.L3Carrierkey
LEFT OUTER JOIN vwDimPlacingBroker pb ON pb.PlacingBrokerKey=pm.PlacingBrokerKey
WHERE 1=1
AND (@FrontingCarrier='' OR @FrontingCarrier IN(fc1.FrontingCarrierShortName,fc2.FrontingCarrierShortName,fc3.FrontingCarrierShortName,fc1.FrontingCarrierName,fc2.FrontingCarrierName,fc3.FrontingCarrierName))
AND (@Carrier='' OR @Carrier IN(c.RSCarrierName,c.CarrierShortName) OR cl.CarrierKey IS NOT NULL)
AND (@LineslipReference='' OR pm.LineslipReference LIKE '%' +@LineslipReference +'%')
AND (@PlacingBroker='' OR @PlacingBroker IN(pb.PlacingBrokerName,pb.PlacingBrokerShortName))

SELECT 
	c.ClaimKey,
	IncurredCnvUSD=ISNULL(t.IncurredCnvUSD,0)*-1
INTO #CT
FROM tblDimClaim c
LEFT OUTER JOIN (
	SELECT
		ClaimKey,
		IncurredCnvUSD=SUM(ISNULL(t.ValueCnvUSD1,0))*-1
	FROM vwFactTransaction t 
	INNER JOIN tblDimAccount a ON a.AccountKey=t.AccountKey
	INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey
	INNER JOIN #TPM tpm ON tpm.PolicyMarketKey=t.PolicyMarketKey
	WHERE AccountGroupCode IN('ClmOs','ClmPay')
	AND ISNULL(t.OutwardsRITypeKey,0)=0
	GROUP BY ClaimKey
) t ON t.ClaimKey=c.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimPolicyHeader ph ON ph.PolicyHeaderKey=p.PolicyHeaderKey
LEFT OUTER JOIN vwDimBinder b ON b.BinderKey=p.BinderKey
LEFT OUTER JOIN tblDimEvent e ON e.EventKey=c.EventKey
LEFT OUTER JOIN tblDimCatastrophe ecat ON ecat.CatastropheKey=e.CatastropheKey
LEFT OUTER JOIN tblDimClient cc ON cc.ClientKey=c.IndemnityClientKey
LEFT OUTER JOIN tblDimClass cl ON cl.ClassKey=p.ClassKey
LEFT OUTER JOIN tblDimProduct pr ON pr.ProductKey=p.ProductKey
WHERE (@UMR='' OR p.UniqueMarketReference=@UMR)
AND (@ClaimReference='' OR c.ClaimReference=@ClaimReference)
AND (@EventName='' OR e.EventName LIKE '%' + @EventName + '%')
AND (@CatCode='' OR ecat.CatastropheCode IN(SELECT TRIM(Value) FROM string_split(@CatCode,'')))
AND (@Threshold='' OR IncurredCnvUSD>=@Threshold)
AND (@IndemnityClient='' OR cc.ClientName LIKE '%' + @IndemnityClient + '%')
AND (@BinderUMR='' OR b.BinderUMR =@BinderUMR)
AND (ISNULL(@Class,'')='' OR cl.ClassName IN(SELECT TRIM(value) FROM string_split(@Class,',')))
AND (ISNULL(@ExClass,'')='' OR ISNULL(cl.ClassName,'') NOT IN(SELECT TRIM(value) FROM string_split(@ExClass,',')))
AND (ISNULL(@ProductType,'')='' OR pr.ProductName IN(SELECT TRIM(value) FROM string_split(@ProductType,',')))
AND (ISNULL(@ExProductType,'')='' OR ISNULL(pr.ProductName,'') NOT IN(SELECT TRIM(value) FROM string_split(@ExProductType,',')))
AND (@PolicyInceptionDateFrom='' OR ph.PolicyInceptionDate>=@PolicyInceptionDateFrom)
AND (@PolicyInceptionDateTo='' OR ph.PolicyInceptionDate<=@PolicyInceptionDateTo)
AND ISNULL(c.OutwardsRITypeKey,0)=0
--Remove 0 incurred claims from list
AND (ISNULL(@ZeroClosed,'')=''
OR (ISNULL(@ZeroClosed,'')='N' AND NOT (ISNULL(t.IncurredCnvUSD,0)=0 AND c.ClaimStatus='Closed'))
OR (ISNULL(@ZeroClosed,'')='Y' AND (ISNULL(t.IncurredCnvUSD,0)=0 AND c.ClaimStatus='Closed')))
--Only include claims that have a backlaod movement
AND (@LegacyBackload='' OR @LegacyBackload='N' 
OR (@LegacyBackload='Y' AND EXISTS (
	SELECT 1
	FROM tblFactTransactionHeader th
	INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=th.TransactionGroupKey
	INNER JOIN tblFactTransactionLine tl ON tl.TransactionKey=th.TransactionKey
	WHERE tg.TransactionGroupCode IN('ClmMoveLegacy','ClmMoveBackload')
	AND tl.ClaimKey=c.ClaimKey)
))

CREATE INDEX ix1 ON #CT (ClaimKey)

SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig,
	ClmOsFeeOrig,
	ClmPayIndmOrig,
	ClmPayFeeOrig,
	ClmOsIndmSett,
	ClmOsFeeSett,
	ClmPayIndmSett,
	ClmPayFeeSett,
	ClmOsIndmCnvUSD,
	ClmOsFeeCnvUSD,
	ClmPayIndmCnvUSD,
	ClmPayFeeCnvUSD,
	ClmReceivableCnvUSD,
	ClmReceivedCnvUSD,
	ClmReceivableBackloadCnvUSD,
	ClmReceivedBackloadCnvUSD
INTO #FacilityTotals
FROM (
SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,

	ClmOsIndmOrig,
	ClmOsFeeOrig,
	ClmPayIndmOrig,
	ClmPayFeeOrig,

	ClmOsIndmSett,
	ClmOsFeeSett,
	ClmPayIndmSett,
	ClmPayFeeSett,

	ClmOsIndmCnvUSD,
	ClmOsFeeCnvUSD,
	ClmPayIndmCnvUSD,
	ClmPayFeeCnvUSD,
	ClmReceivableCnvUSD,
	ClmReceivedCnvUSD,
	ClmReceivableBackloadCnvUSD,
	ClmReceivedBackloadCnvUSD
	
FROM (
SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig=SUM(ClmOsIndmOrig),
	ClmOsFeeOrig=SUM(ClmOsFeeOrig),
	ClmPayIndmOrig=SUM(ClmPayIndmOrig),
	ClmPayFeeOrig=SUM(ClmPayFeeOrig),
	ClmOsIndmSett=SUM(ClmOsIndmSett),
	ClmOsFeeSett=SUM(ClmOsFeeSett),
	ClmPayIndmSett=SUM(ClmPayIndmSett),
	ClmPayFeeSett=SUM(ClmPayFeeSett),
	ClmOsIndmCnvUSD=SUM(ClmOsIndmCnvUSD),
	ClmOsFeeCnvUSD=SUM(ClmOsFeeCnvUSD),
	ClmPayIndmCnvUSD=SUM(ClmPayIndmCnvUSD),
	ClmPayFeeCnvUSD=SUM(ClmPayFeeCnvUSD),
	ClmReceivableCnvUSD=SUM(ClmReceivableCnvUSD),
	ClmReceivedCnvUSD=SUM(ClmReceivedCnvUSD),
	ClmReceivableBackloadCnvUSD=SUM(ClmReceivableBackloadCnvUSD),
	ClmReceivedBackloadCnvUSD=SUM(ClmReceivedBackloadCnvUSD)
FROM (
SELECT
	tl.ClaimKey,
	CarrierKey=IIF(@ShowFacility='True' OR @GroupBy IN('Carrier','Lineslip','Contract Year'),tl.CarrierKey,null),
	PolicyMarketKey=IIF(@ShowFacility='True' OR @GroupBy IN('Carrier','Lineslip','Contract Year'),tl.PolicyMarketKey,null),
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig=IIF(AccountCode LIKE 'ClmOsIndm%',ValueOrig,0)*-1,
	ClmOsFeeOrig=IIF(AccountCode LIKE 'ClmOsFee%',ValueOrig,0)*-1,
	ClmPayIndmOrig=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueOrig,0)*-1,
	ClmPayFeeOrig=IIF(AccountCode LIKE 'ClmPayFee%',ValueOrig,0)*-1,
	ClmOsIndmSett=IIF(AccountCode LIKE 'ClmOsIndm%',ValueSett,0)*-1,
	ClmOsFeeSett=IIF(AccountCode LIKE 'ClmOsFee%',ValueSett,0)*-1,
	ClmPayIndmSett=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueSett,0)*-1,
	ClmPayFeeSett=IIF(AccountCode LIKE 'ClmPayFee%',ValueSett,0)*-1,
	ClmOsIndmCnvUSD=IIF(AccountCode LIKE 'ClmOsIndm%',ValueCnvUSD1,0)*-1,
	ClmOsFeeCnvUSD=IIF(AccountCode LIKE 'ClmOsFee%',ValueCnvUSD1,0)*-1,
	ClmPayIndmCnvUSD=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueCnvUSD1,0)*-1,
	ClmPayFeeCnvUSD=IIF(AccountCode LIKE 'ClmPayFee%',ValueCnvUSD1,0)*-1,
	ClmReceivableCnvUSD=IIF(AccountCode='ClmReceivableUW',ValueCnvUSD1,0),
	ClmReceivedCnvUSD=IIF(AccountCode='ClmReceivedUW',ValueCnvUSD1,0)*-1,
	ClmReceivableBackloadCnvUSD=IIF(AccountCode='ClmReceivableUWBackload',ValueCnvUSD1,0),
	ClmReceivedBackloadCnvUSD=IIF(AccountCode='ClmReceivedUWBackload',ValueCnvUSD1,0)*-1

FROM vwFactTransaction tl
INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=tl.TransactionGroupKey
INNER JOIN #CT ct ON ct.Claimkey=tl.ClaimKey
INNER JOIN #TPM tpm ON tpm.PolicyMarketKey=tl.PolicyMarketKey
INNER JOIN tblDimClaim c ON c.ClaimKey=tl.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimAccount a ON a.AccountKey=tl.AccountKey
INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey 
--INNER JOIN tblDimCarrier c0 ON c0.CarrierKey=tl.CarrierKey
WHERE AccountGroupCode IN('ClmOs','ClmPay','ClmReceivableUW','ClmReceivableUWBackload') 
AND (@Period='' OR tl.AccountingPeriod<=@Period)
AND ISNULL(tl.OutwardsRITypeKey,0)=0
) qry
GROUP BY 
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClaimKey
) qry2
) qry3

CREATE INDEX ix1 ON #FacilityTotals (ClaimKey)

SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig,
	MktClmOsFeeOrig,
	MktClmPayIndmOrig,
	MktClmPayFeeOrig,

	MktClmOsIndmSett,
	MktClmOsFeeSett,
	MktClmPayIndmSett,
	MktClmPayFeeSett,

	MktClmOsIndmCnvUSD,
	MktClmOsFeeCnvUSD,
	MktClmPayIndmCnvUSD,
	MktClmPayFeeCnvUSD

INTO #MarketTotals
FROM (
SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig,
	MktClmOsFeeOrig,
	MktClmPayIndmOrig,
	MktClmPayFeeOrig,

	MktClmOsIndmSett,
	MktClmOsFeeSett,
	MktClmPayIndmSett,
	MktClmPayFeeSett,

	MktClmOsIndmCnvUSD,
	MktClmOsFeeCnvUSD,
	MktClmPayIndmCnvUSD,
	MktClmPayFeeCnvUSD
	
FROM (
SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	--TransactionDate,
	MktClmOsIndmOrig=SUM(MktClmOsIndmOrig),
	MktClmOsFeeOrig=SUM(MktClmOsFeeOrig),
	MktClmPayIndmOrig=SUM(MktClmPayIndmOrig),
	MktClmPayFeeOrig=SUM(MktClmPayFeeOrig),

	MktClmOsIndmSett=SUM(MktClmOsIndmSett),
	MktClmOsFeeSett=SUM(MktClmOsFeeSett),
	MktClmPayIndmSett=SUM(MktClmPayIndmSett),
	MktClmPayFeeSett=SUM(MktClmPayFeeSett),

	MktClmOsIndmCnvUSD=SUM(MktClmOsIndmCnvUSD),
	MktClmOsFeeCnvUSD=SUM(MktClmOsFeeCnvUSD),
	MktClmPayIndmCnvUSD=SUM(MktClmPayIndmCnvUSD),
	MktClmPayFeeCnvUSD=SUM(MktClmPayFeeCnvUSD)
FROM (
SELECT
	tl.ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueOrig,0)*-1,
	MktClmOsFeeOrig=IIF(AccountCode LIKE 'MktClmOsFee%',ValueOrig,0)*-1,
	MktClmPayIndmOrig=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueOrig,0)*-1,
	MktClmPayFeeOrig=IIF(AccountCode LIKE 'MktClmPayFee%',ValueOrig,0)*-1,

	MktClmOsIndmSett=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueSett,0)*-1,
	MktClmOsFeeSett=IIF(AccountCode LIKE 'MktClmOsFee%',ValueSett,0)*-1,
	MktClmPayIndmSett=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueSett,0)*-1,
	MktClmPayFeeSett=IIF(AccountCode LIKE 'MktClmPayFee%',ValueSett,0)*-1,

	MktClmOsIndmCnvUSD=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueCnvUSD1,0)*-1,
	MktClmOsFeeCnvUSD=IIF(AccountCode LIKE 'MktClmOsFee%',ValueCnvUSD1,0)*-1,
	MktClmPayIndmCnvUSD=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueCnvUSD1,0)*-1,
	MktClmPayFeeCnvUSD=IIF(AccountCode LIKE 'MktClmPayFee%',ValueCnvUSD1,0)*-1
FROM vwFactTransaction tl
INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=tl.TransactionGroupKey
INNER JOIN #CT ct ON ct.Claimkey=tl.ClaimKey
INNER JOIN tblDimClaim c ON c.ClaimKey=tl.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimAccount a ON a.AccountKey=tl.AccountKey
INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey 
WHERE AccountGroupCode IN(
	'MktClmOs',
	'MktClmPay'
)
AND ISNULL(tl.OutwardsRITypeKey,0)=0
AND (@Period='' OR tl.AccountingPeriod<=@Period)
AND (@UMR='' OR p.UniqueMarketReference=@UMR)
AND (@ClaimReference='' OR @ClaimReference=c.ClaimReference)
) qry
GROUP BY 
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey

) qry2
) qry3

CREATE INDEX #ix1 ON #MarketTotals (ClaimKey)

SELECT 
	Carrier=ISNULL(c0.CarrierName,''),
	LineslipReference=ISNULL(pm.LineslipReference,''),
	GroupBy=CASE @GroupBy
		WHEN 'Lineslip' THEN IIF(ISNULL(pm.LineslipReference,'')='',c0.CarrierShortName,pm.LineslipReference)
		WHEN 'Event' THEN ISNULL(e.EventName,' No Event')
		WHEN 'Product' THEN pr.ProductName
		WHEN 'Carrier' THEN c0.CarrierShortName
		WHEN 'Contract Year' THEN 
			CASE WHEN ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate) IS NULL THEN ISNULL(c0.CarrierShortName,'Unknown')
			ELSE CAST(YEAR(ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate)) AS NVARCHAR)
		END
		ELSE ''
	END,
	ContractInceptionDate=ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate),
	ReinsuredName=p.ReinsuredName,
	UMR=p.UniqueMarketReference,
	AccountName=c.ClaimInsuredName,
	ROKPolicyRef=ph.PolicyReference,
	p.SectionCode,
	UW=u.UnderwriterName,
	c.UniqueClaimReference,
	ClaimReference=c.ClaimReference,
	BrokerUCR=BrokerReference,
	Client=cc.ClientName,
	IndustryCode=pr.ProductName,
	Class=cl.ClassName,
	InceptionDate=CONVERT(DATE,ph.PolicyInceptionDate),
	ExpiryDate=CONVERT(DATE,ph.PolicyExpiryDate),
	Location=c.LossLocation,
	Country='',
	OpenedDate=CONVERT(DATE,c.ClaimStatusOpened),
	DateOfLoss=CONVERT(DATE,c.LossDateFrom),
	DateReportedToRUw=CONVERT(DATE,c.ClaimNotificationDate),
	ClaimStatusToROK=ISNULL(c.ClaimStatus,''),
	LossName=c.LossName,
	LossCircumstances=c.ClaimDescription,
	EventCat=ISNULL(ISNULL(ecat.CatastropheCode,e.EventName),''),
	OriginalCurrency=oc.CurrencyCode,
	SettlementCurrency=sc.CurrencyCode,
	LastOSMovementDate=c.LastMovementDateOS,
	LastPaidMovementDate=c.LastMovementDatePaid,

---- Market Movements
---- Paid
	MktPdIndmSCC=mt.MktClmPayIndmSett,
	MktPdFeesSCC=mt.MktClmPayFeeSett,
	MktPdTotlSCC=mt.MktClmPayFeeSett+mt.MktClmPayIndmSett,
---- OS
	MktOSIndmSCC=mt.MktClmOSIndmSett,
	MktOSFeesSCC=mt.MktClmOSFeeSett,
	MktOSTotlSCC=mt.MktClmOSFeeSett+mt.MktClmOSIndmSett,
---- Incurred
	MktIncSCC=mt.MktClmOsFeeSett+mt.MktClmOsIndmSett+mt.MktClmPayFeeSett+mt.MktClmPayIndmSett,

	ROKSignedLine=CASE
		WHEN fo.PolicyKey IS NULL THEN CONVERT(DECIMAL(16,8),p.PolicyLine*ISNULL(pm.Line,1))
		ELSE fo.Line
	END,
---- Paid
	PdIndmSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayIndmSett
		ELSE mt.MktClmPayIndmSett*fo.Line
	END,
	PdFeesSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeSett
		ELSE mt.MktClmPayFeeSett*fo.Line
	END,
	PdTotlSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeSett+ct.ClmPayIndmSett
		ELSE (mt.MktClmPayFeeSett+mt.MktClmPayIndmSett)*fo.Line
	END,
---- OS
	OSIndmSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSIndmSett
		ELSE mt.MktClmOSIndmSett*fo.Line
	END,
	OSFeesSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSFeeSett
		ELSE mt.MktClmOSFeeSett*fo.Line
	END,
	OSTotlSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSFeeSett+ct.ClmOSIndmSett
		ELSE (mt.MktClmOSFeeSett+mt.MktClmOSIndmSett)*fo.Line
	END,
---- Incurred
	IncSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeSett+ct.ClmOsIndmSett+ct.ClmPayFeeSett+ct.ClmPayIndmSett
		ELSE (mt.MktClmOsFeeSett+mt.MktClmOsIndmSett+mt.MktClmPayFeeSett+mt.MktClmPayIndmSett)*fo.Line
	END,

	OSTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeCnvUSD+ct.ClmOsIndmCnvUSD
		ELSE (mt.MktClmOSFeeCnvUSD+mt.MktClmOSIndmCnvUSD)*fo.Line
	END,
	PdTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeCnvUSD+ct.ClmPayIndmCnvUSD
		ELSE (mt.MktClmPayFeeCnvUSD+mt.MktClmPayIndmCnvUSD)*fo.Line
	END,

	IncTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeCnvUSD+ct.ClmOsIndmCnvUSD+ct.ClmPayFeeCnvUSD+ct.ClmPayIndmCnvUSD
		ELSE (mt.MktClmOsFeeCnvUSD+mt.MktClmOsIndmCnvUSD+mt.MktClmPayFeeCnvUSD+mt.MktClmPayIndmCnvUSD)*fo.Line
	END,

	ReceivableCnvUSD=ct.ClmReceivableCnvUSD,
	ReceivedCnvUSD=ct.ClmReceivedCnvUSD,
	DueCnvUSD=ct.ClmReceivableCnvUSD-ct.ClmReceivedCnvUSD,

	ReceivableBackloadCnvUSD=ct.ClmReceivableBackloadCnvUSD,
	ReceivedBackloadCnvUSD=ct.ClmReceivedBackloadCnvUSD,
	DueBackloadCnvUSD=ct.ClmReceivableBackloadCnvUSD-ct.ClmReceivedBackloadCnvUSD,
   	  
	DayBookReference,
	n.Narrative,
	c.SharePointURL,
	WatchList=CASE WHEN c.WatchList=1 THEN 'Yes' END,
	c.ComplexTriageNames,
	ss.SourceSystemName
FROM #FacilityTotals ct
INNER JOIN #MarketTotals mt
	ON mt.ClaimKey=ct.ClaimKey
	AND mt.OrigCurrencyKey=ct.OrigCurrencyKey
	AND mt.SettCurrencyKey=ct.SettCurrencyKey
INNER JOIN tblDimCurrency oc ON oc.CurrencyKey=ct.OrigCurrencyKey
INNER JOIN tblDimCurrency sc ON sc.CurrencyKey=ct.SettCurrencyKey
INNER JOIN tblDimClaim c ON c.ClaimKey=ct.ClaimKey
LEFT OUTER JOIN #FrontingOverride fo ON fo.PolicyKey=c.PolicyKey
LEFT OUTER JOIN tblDimClient cc ON cc.ClientKey=c.IndemnityClientKey
LEFT OUTER JOIN tblDimPolicyMarket pm ON pm.PolicyMarketKey=ct.PolicyMarketKey
LEFT OUTER JOIN vwDimCarrier c0 ON c0.CarrierKey=ct.CarrierKey
LEFT OUTER JOIN tblDimEvent e ON e.EventKey=c.EventKey
LEFT OUTER JOIN tblDimCatastrophe ecat ON ecat.CatastropheKey=e.CatastropheKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimSourceSystem ss ON ss.SourceSystemKey=p.SourceSystemKey
INNER JOIN tblDimPolicyHeader ph ON ph.PolicyHeaderKey=p.PolicyHeaderKey
LEFT OUTER JOIN tblDimProduct pr ON pr.ProductKey=p.ProductKey
LEFT OUTER JOIN tblDimClass cl ON cl.ClassKey=p.ClassKey
LEFT OUTER JOIN tblDimUnderwriter u ON u.UnderwriterKey=p.UnderwriterKey
LEFT OUTER JOIN vwDimContract cnt ON cnt.ContractKey=pm.ContractKey
LEFT OUTER JOIN vwDimContract ncnt ON ncnt.ContractKey=pm.NewContractKey
LEFT OUTER JOIN (
	SELECT
		ClaimKey,
		Narrative=STRING_AGG(Narrative,',') WITHIN GROUP (ORDER BY EntryDate DESC)
	FROM (
		SELECT
			ClaimKey,
			EntryDate,
			Narrative=CONVERT(NVARCHAR(MAX),FORMAT(EntryDate,'dd-MMM-yyyy HH:mm') + ' ' + EntryUser + ': ' + Narrative)
		FROM tblDimClaimNarrative
	) cn
	GROUP BY ClaimKey
) n ON n.ClaimKey=c.ClaimKey

WHERE 1=1

ORDER BY GroupBy,UMR,ClaimReference,OriginalCurrency,SettlementCurrency,Carrier

DROP TABLE IF EXISTS #TPM
DROP TABLE IF EXISTS #CT
DROP TABLE IF EXISTS #FacilityTotals
DROP TABLE IF EXISTS #MarketTotals
DROP TABLE IF EXISTS #FrontingOverride
DROP TABLE IF EXISTS #CarrierList