INSERT INTO @MIADetails      
SELECT
	CASE       
		WHEN InsurenceType = 'DE' AND CoverageType = 'DF' THEN  @DD_Number  /*PeckingOrderForPremiumDeductionsDefault*/      
		WHEN InsurenceType = 'DE' AND CoverageType = 'AD' THEN @AD_Number  /*PeckingOrderForPremiumDeductionsAdditional*/      
		WHEN InsurenceType = 'TP' THEN @TPD_Number  /*PeckingOrderForPremiumDeductions*/      
		WHEN InsurenceType = 'IP' THEN @IP_Number /*PeckingOrderForPremiumDeductions*/
	END AS Sequence_Number
	, InsurenceType
	, CoverageType
	, CoverType
	, MI.EffectiveFromDate Period_StartDate

	, CASE
		WHEN @PREMIUM_DEDUCTION_METHOD = 'AR' AND CONVERT(DATE, ECR.Payroll_End_Date) >= CONVERT(DATE, dbo.GetCoreDBReferenceDate(DEFAULT))      
			THEN dbo.GetCoreDBReferenceDate(DEFAULT)      
		WHEN @PREMIUM_DEDUCTION_METHOD = 'AR' AND MI.EffectiveToDate IS NULL      
			THEN ECR.Payroll_End_Date      
		WHEN @PREMIUM_DEDUCTION_METHOD = 'AR' AND MI.EffectiveToDate > ECR.Payroll_End_Date
			THEN ECR.Payroll_End_Date      
		WHEN @PREMIUM_DEDUCTION_METHOD = 'AR' AND MI.EffectiveToDate < ECR.Payroll_End_Date
			THEN MI.EffectiveToDate
		WHEN @PREMIUM_DEDUCTION_METHOD = 'AD'
			THEN ECR.Payroll_End_Date
	END AS Period_EndDate

	, CASE
		WHEN MI.InsurenceType = 'IP' THEN (
			(
				SELECT NUMERIC_VALUE
				FROM
					STRING_ARGUMENT ST
					JOIN RULE_TABLE RT ON RT.RULE_TABLE_ID = ST.RULE_TABLE_ID
				WHERE
					TABLE_CODE = 'ruletable_IPFormulaPremiumrate'
					AND LINK_ID = R.LINK_ID      
					--AND TABLE_ARGUMENT = 'Weekly Employer SG/Award Contribution Amount'      
			) *          
			(
				SELECT SUPER_GUARANTEE_AMOUNT
				FROM Return_detail
				WHERE
					CONTRIBUTION_RETURN_ID = ECR.CONTRIBUTION_RETURN_ID
					AND LINK_ID = ECR.LINKID
			)
			)        
		ELSE PA.TotalPremium      
	END AS PremiumAmount

	--,TotalPremium
	--, FM.FrequencyOfPremiumCalculations PremiumFrequency
	, 0 NumberOfWeeks
	, 0 Calculated_Tax_Rebate
	, MIAStatus
	, MI.LinkID
	, ECR.Cut_OffDate
	, ECR.Last_COD
	, ECR.Member_LCOD
FROM
	MEMBER_INSURANCE MI      
	JOIN @EmployerContributionRecords ECR ON
		MI.LinkID = ECR.LINKID      
		AND MI.InsurenceType = ECR.Insurance_Type      
		AND CONVERT(DATE, MI.EffectiveFromDate) >= CONVERT(DATE, ECR.Payroll_Start_Date)      
		AND CONVERT(DATE, ISNULL(MI.EffectiveToDate, ECR.Payroll_End_Date)) <= CONVERT(DATE, ECR.Payroll_End_Date)      
		AND MI.MIAStatus IN ('APR', 'AL', 'DF')

	--JOIN FORMULA  FM      
	--ON FM.PolicyID = (SELECT TOP 1 PolicyID from dbo.GetPolicyDetails(MI.InsurenceType, MI.CoverType, dbo.GetCoreDBReferenceDate(DEFAULT), @ProductID, MI.LinkID))      
	--AND CONVERT(DATE, dbo.GetCoreDBReferenceDate(DEFAULT)) >= CONVERT(DATE, FM.EffectiveFromDate)      
	--AND CONVERT(DATE, dbo.GetCoreDBReferenceDate(DEFAULT)) <= ISNULL(CONVERT(DATE, FM.EffectiveToDate), CONVERT(DATE, dbo.GetCoreDBReferenceDate(DEFAULT)))      

	JOIN RELATIONSHIP R ON
		R.CLIENT_ID = (SELECT TOP 1 PolicyID from dbo.GetPolicyDetails(MI.InsurenceType, MI.CoverType, dbo.GetCoreDBReferenceDate(DEFAULT), @ProductID, MI.LinkID))      
		AND R.RELATIONSHIP_TYPE IN ('GP', 'IP')

	CROSS APPLY [dbo].[CalculateInsurancePremiumAmount](MI.InsurenceType, MI.CoverType, MI.CoverageType, dbo.GetCoreDBReferenceDate(DEFAULT), MI.EventID, MI.LINKID, @AccountNumber,MI.ApprovedUnits,MI.LoadingPercentage,MI.LoadingDollar,MI.InsVarRequestID) AS PA             
END

INSERT INTO @MIAFinalDetails      
SELECT
	Sequence_Number
	, InsurenceType
	, CoverageType
	, Period_StartDate
	, Period_EndDate
	, (PremiumAmount * NumberOfWeeks) PremiumAmount
	--, PremiumFrequency
	, CASE WHEN @APPLY_TAX_REBATE = 'Y' THEN ((-@TAX_REBATE_PERCENTAGE / 100) * (PremiumAmount * NumberOfWeeks)) END Calculated_Tax_Rebate
	, MIAStatus
	, LinkID
	, Cut_OffDate
	, Last_COD
	, Member_LCOD
FROM      
	(
	SELECT DISTINCT
		Sequence_Number
		, InsurenceType
		, CoverageType
		, Period_StartDate
		, Period_EndDate
		, PremiumAmount
		--, PremiumFrequency

		, CASE WHEN @NUMBER_OF_DAYS = 'ND' THEN (
				SELECT [dbo].[GetWeekdaysInInterval](Period_StartDate, Period_EndDate, @GIVEN_DAY)
				)      
			ELSE (
				SELECT (DATEDIFF(day, Period_StartDate, Period_EndDate) + 1) / 7.00
				)
		END AS NumberOfWeeks

		, Calculated_Tax_Rebate
		, MIAStatus
		, LinkID
		, Cut_OffDate
		, Last_COD
		, Member_LCOD
	FROM
		@MIADetails
	)
	TEMP      

INSERT INTO @MIATEMPFINAL      
SELECT  
	Sequence_Number
	, InsurenceType
	, Period_StartDate
	, Period_EndDate
	, SUM(PremiumAmount) PremiumAmount
	, SUM(Calculated_Tax_Rebate) Calculated_Tax_Rebate
	, AB.AccountBalance
	, LinkID
	, Cut_OffDate
	, Last_COD
	, Member_LCOD
	, 0 Calculated_InsurerSplitAmt
	, 0 Calculated_FundSplitAmt
	, 0 Actual_PremiumDeducted
	, 0 Applied_TaxRebate
	, 0 Applied_InsurerSplitAmt
	, 0 Applied_FundSplitAmt
	, dbo.GetCoreDBReferenceDate(DEFAULT)
FROM
	@MIAFinalDetails      
	CROSS APPLY [dbo].[TransactionGetAccountBalances] (LinkID, dbo.GetCoreDBReferenceDate(DEFAULT), @ProductID) AS AB      
WHERE
	AB.AccountType = 'MA'      
GROUP BY
	LinkID
	, Sequence_Number
	, InsurenceType
	, AB.AccountBalance
	, Period_StartDate
	, Period_EndDate
	, Cut_OffDate
	, Last_COD
	, Member_LCOD