declare @CompanyName Varchar (20)
set @CompanyName = '2-G9BF'
SELECT PolicyID, Client_Full_Name
INTO #Stage1
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'Y'
SELECT PolicyID, Client_Full_Name
INTO #Stage2
FROM DWH.dbo.dim_PolicyWhosWho WITH (NOLOCK)
WHERE IFA_Company_ID = @CompanyName
AND Master_Client = 'N'
-- get the maximum investment holding per policy into a temp table
SELECT IH.policy_id, MAX(IH.inv_holding) as [max_INV_Holding]
INTO #MaxInvestHoldingPerPolicy
FROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_Sold_Prod SP WITH (NOLOCK)
JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDING IH WITH (NOLOCK)
ON IH.policy_id = SP.policy_id AND IH.product_code = SP.product_code AND IH.instance = SP.instance
JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON FPB.policy_id = IH.POLICY_ID AND FPB.product_code = IH.product_code AND FPB.instance = IH.instance
WHERE FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'
GROUP BY IH.policy_id
SELECT
t1.PolicyID
,t1.Client_Full_Name
,ISNULL (t2.Client_Full_Name,'')SecondInvestor
INTO #Stage3
FROM #Stage1t1
LEFT JOIN #Stage2t2
ON t1.PolicyId = t2.PolicyID
ORDER BY t1.PolicyID
DROP TABLE #Stage1
DROP TABLE #Stage2
-- Get the first fields
SELECT
pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
IB.POLICY_ID ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
FROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_BASIS IB WITH (NOLOCK)
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.pr_ih_fund_spd FS WITH (NOLOCK)
ON FS.POLICY_ID = IB.POLICY_ID
AND FS.INV_HOLDING = IB.INV_HOLDING
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.s_asset SA WITH (NOLOCK)
ON SA.ASSET_NUM = IB.POLICY_ID
LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.pr_inv_ptf IP WITH (NOLOCK)
ON IP.INV_PTF_NAME = FS.INV_PTF_NAME
LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON FP.INV_PTF_NAME = IP.INV_PTF_ALIAS
AND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))
LEFT JOIN DWH.dbo.dim_policywhoswho PWH WITH (NOLOCK)
ON PWH.POLICYID = IB.POLICY_ID
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)
ON SP.POLICY_ID = IB.POLICY_ID AND SP.PRODUCT_CODE = IB.PRODUCT_CODE AND SP.INSTANCE = IB.INSTANCE
LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_INV_Holding IH WITH (NOLOCK)
ON IH.POLICY_ID = IB.POLICY_ID AND IH.PRODUCT_CODE = IB.PRODUCT_CODE AND IH.INSTANCE = IB.INSTANCE AND IH.INV_HOLDING = IB.INV_HOLDING
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON FPB.POLICY_ID = IH.POLICY_ID AND FPB.PRODUCT_CODE = IH.PRODUCT_CODE AND FPB.INSTANCE = IH.INSTANCE AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'
LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)
ON SF.POLICY_ID = IB.POLICY_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
AND SF.IBU_COMM_CLASS = IB.IBU_COMM_CLASS
LEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)
ON MH.POLICY_ID = SP.POLICY_ID
-- get the ServicingContact (amended for Flare 450396)
LEFT JOIN (
SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_ID
WHERE a.X_INVSTR_FLG = 'Y'
UNION
SELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID
WHERE a.INVSTR_FLG = 'Y'
) UN
ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT
LEFT JOIN #Stage3t3
ON pwh.PolicyID = t3.PolicyID
WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND pwH.Master_Client = 'Y'
AND pwH.status IN ('VERI','INNF')
AND SP.IFA_REMUNE_TYPE = 'COMMISSION'
---then get the second fields
UNION
SELECT
pwh.IFA_Company_Name as IFA_Company_Name,
t3.Client_Full_Name as Client_Full_Name,
t3.SecondInvestor as SecondInvestor,
PIH.POLICY_ID ASPolicy,
CAST(sum(FS.VALUE * (FP.FUND_PRICE/100)) AS DECIMAL(15,2)) as IH_Value
FROM SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_HOLDINGPIH WITH (NOLOCK)
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_IH_FUND_SPD FS WITH (NOLOCK)
ON PIH.POLICY_ID = FS.POLICY_ID
AND PIH.INV_HOLDING = FS.INV_HOLDING
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.S_ASSET SA WITH (NOLOCK)
ON PIH.POLICY_ID = SA.ASSET_NUM
LEFT Join SELESTIA_BI_STAGING_SOURCE.dbo.PR_INV_PTF IP WITH (NOLOCK)
ON FS.INV_PTF_NAME = IP.INV_PTF_NAME
LEFT JOIN DWH.dbo.fact_FundPrices FP WITH (NOLOCK)
ON IP.INV_PTF_ALIAS = FP.INV_PTF_NAME
AND EFFECTIVE_DATE = (SELECT MAX (EFFECTIVE_DATE) FROM DWH..FACT_FUNDPRICES WITH (NOLOCK))
LEFT JOIN DWH.dbo.DIM_POLICYWHOSWHO PWH WITH (NOLOCK)
ON PIH.POLICY_ID = PWH.POLICYID
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_SOLD_PROD SP WITH (NOLOCK)
ON PIH.POLICY_ID = SP.POLICY_ID
AND PIH.PRODUCT_CODE = SP.PRODUCT_CODE
AND PIH.INSTANCE = SP.INSTANCE
LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_IH_BASIS IB WITH (NOLOCK)
ON PIH.POLICY_ID = IB.POLICY_ID
AND PIH.INV_HOLDING = IB.INV_HOLDING
AND PIH.PRODUCT_CODE = IB.PRODUCT_CODE
AND PIH.INSTANCE = IB.INSTANCE
LEFT JOIN SELESTIA_BI_STAGING_SOURCE.dbo.PR_FEES_PAYBASIS FPB WITH (NOLOCK)
ON PIH.POLICY_ID = FPB.policy_id
AND PIH.product_code = FPB.product_code
AND PIH.instance = FPB.instance
AND FPB.IBU_COMM_TYPE = 'REGULAR_CHRG'
LEFT JOIN [Selestia_BI_Staging_Source].[dbo].PR_SPLIT_FEES SF WITH (NOLOCK)
ON PIH.POLICY_ID = SF.POLICY_ID
AND SF.IBU_COMM_TYPE = FPB.IBU_COMM_TYPE
--AND SF.IBU_COMM_CLASS = PIH.IBU_COMM_CLASS
LEFT JOIN #MaxInvestHoldingPerPolicy MH WITH (NOLOCK)
ON MH.POLICY_ID = SP.POLICY_ID
-- get the ServicingContact (amended for Flare 450396)
LEFT JOIN (
SELECT a.INTEGRATION_ID , a.FST_NAME + ' ' + a.LAST_NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_CONTACT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_SER_CON_ID = b.ROW_ID
WHERE a.X_INVSTR_FLG = 'Y'
UNION
SELECT a.INTEGRATION_ID , a.NAME [CLIENT_NAME] , b.FST_NAME + ' ' + b.LAST_NAME [ServicingContact]
FROM Selestia_BI_Staging_Source..S_ORG_EXT a WITH (NOLOCK)
LEFT JOIN Selestia_BI_Staging_Source..S_CONTACT b WITH (NOLOCK) ON a.X_PR_SER_PER_ID = b.ROW_ID
WHERE a.INVSTR_FLG = 'Y'
) UN
ON PWH.ClientID = UN.INTEGRATION_ID COLLATE DATABASE_DEFAULT
LEFT JOIN #Stage3t3
ON pwh.PolicyID = t3.PolicyID
WHERE fs.fund_code = 'FV'
AND FS.Value >= 1
AND SA.owner_accnt_id = @CompanyName
AND pwH.Master_Client = 'Y'
AND pwH.status IN ('VERI','INNF')
AND SP.IFA_REMUNE_TYPE = 'FEES'
GROUP BY
pwh.IFA_Company_Name
,t3.Client_Full_Name
,t3.SecondInvestor
,IB.POLICY_ID
,PIH.POLICY_ID
,FS.VALUE
,FP.FUND_PRICE
ORDER BY IB.POLICY_ID
DROP TABLE #Stage3
DROP TABLE #MaxInvestHoldingPerPolicy