text: if object_id('[Rulebase].[ALB_Get_Cases]','p') is not null
  	drop procedure [Rulebase].[ALB_Get_Cases]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_Get_Cases] as
  
  	begin
  		set nocount on
  		select
  			CaseData
  		from
  			[Rulebase].[ALB_Cases]
  	end
  GO
  if object_id('[Rulebase].[ALB_Insert_Cases]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_Cases]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_Insert_Cases] as
  
  	begin
  		set nocount on
  
  		insert into
  			[Rulebase].[ALB_Cases]
  			(
  				[LearnRefNumber],
  				CaseData
  			)
  		select
  			ControllingTable.[LearnRefNumber],
  			convert(xml,
  				(
  					select
  						[LARS_Current_Version].[CurrentVersion] as [@LARSVersion],
  						[LearningProvider].[UKPRN] as [@UKPRN],
  						(
  							select
  								[Learner].[LearnRefNumber] as [@LearnRefNumber],
  								(
  									select
  										[LearningDelivery].[AimSeqNumber] as [@AimSeqNumber],
  										[LearningDelivery].[CompStatus] as [@CompStatus],
  										[LearningDelivery].[LearnActEndDate] as [@LearnActEndDate],
  										[LARS_LearningDelivery].[LearnAimRefType] as [@LearnAimRefType],
  										[LearningDelivery].[LearnPlanEndDate] as [@LearnPlanEndDate],
  										[LearningDelivery].[LearnStartDate] as [@LearnStartDate],
  										adl.LearnDelFAMCode as [@LrnDelFAM_ADL],
  										res.LearnDelFAMCode as [@LrnDelFAM_RES],
  										[LARS_LearningDelivery].[NotionalNVQLevelv2] as [@NotionalNVQLevelv2],
  										[LearningDelivery].[OrigLearnStartDate] as [@OrigLearnStartDate],
  										[LearningDelivery].[OtherFundAdj] as [@OtherFundAdj],
  										[LearningDelivery].[Outcome] as [@Outcome],
  										[LearningDelivery].[PriorLearnFundAdj] as [@PriorLearnFundAdj],
  										[LARS_LearningDelivery].[RegulatedCreditValue] as [@RegulatedCreditValue],
  										(
  											select
  												[LearningDeliveryFAM].[LearnDelFAMCode] as [@LearnDelFAMCode],
  												[LearningDeliveryFAM].[LearnDelFAMDateFrom] as [@LearnDelFAMDateFrom],
  												[LearningDeliveryFAM].[LearnDelFAMDateTo] as [@LearnDelFAMDateTo],
  												[LearningDeliveryFAM].[LearnDelFAMType] as [@LearnDelFAMType]
  											from
  												[Valid].[LearningDeliveryFAM]
  											where
  												[LearningDeliveryFAM].[LearnRefNumber] = [LearningDelivery].[LearnRefNumber]
  												and [LearningDeliveryFAM].[AimSeqNumber] = [LearningDelivery].[AimSeqNumber]
  											for xml path ('LearningDeliveryFAM'), type
  										),
  										(
  											select
  												[SFA_PostcodeAreaCost].[EffectiveFrom] as [@AreaCosEffectiveFrom],
  												[SFA_PostcodeAreaCost].[EffectiveTo] as [@AreaCosEffectiveTo],
  												[SFA_PostcodeAreaCost].[AreaCostFactor] as [@AreaCosFactor]
  											from
  												[$(PostcodeFactors)].[$(PostcodeFactors_Schema)].[SFA_PostcodeAreaCost]
  											where
  												[SFA_PostcodeAreaCost].[Postcode]=[LearningDelivery].[DelLocPostCode]
  											for xml path ('SFA_PostcodeAreaCost'), type
  										),
  										(
  											select
  												[LARS_Funding].[FundingCategory] as [@LARSFundCategory],
  												[LARS_Funding].[EffectiveFrom] as [@LARSFundEffectiveFrom],
  												[LARS_Funding].[EffectiveTo] as [@LARSFundEffectiveTo],
  												[LARS_Funding].[RateWeighted] as [@LARSFundWeightedRate],
  												[LARS_Funding].[WeightingFactor] as [@LARSFundWeightingFactor]
  											from
  												[$(LARS)].[$(LARS_Schema)].[LARS_Funding]
  											where
  												[LARS_Funding].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  											for xml path ('LearningDeliveryLARS_Funding'), type
  										)
  									from
  										[Valid].[LearningDelivery]
  										left join [$(LARS)].[$(LARS_Schema)].[LARS_LearningDelivery]
  											on [LARS_LearningDelivery].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  										left join Valid.LearningDeliveryFAM as res
  											on res.LearnRefNumber = LearningDelivery.LearnRefNumber
  											and res.AimSeqNumber = LearningDelivery.AimSeqNumber
  											and res.LearnDelFAMType = 'RES'
  										left join Valid.LearningDeliveryFAM as adl
  											on adl.LearnRefNumber = LearningDelivery.LearnRefNumber
  											and adl.AimSeqNumber = LearningDelivery.AimSeqNumber
  											and adl.LearnDelFAMType = 'ADL'
  									where
  										[LearningDelivery].[LearnRefNumber] = [Learner].[LearnRefNumber]
  										and [LearningDelivery].[FundModel]=99
  									for xml path ('LearningDelivery'), type
  								)
  							from
  								[Valid].[Learner]
  							where
  								[Learner].[LearnRefNumber] = [ControllingTable].[LearnRefNumber]
  							for xml path ('Learner'), type
  						)
  					from
  						[Valid].[LearningProvider]
  						cross join
  						(
  							select top 1 
  								concat
  								(
  									cast(lv.MajorNumber as varchar(5))
  									,'.'
  									,cast(lv.MinorNumber as varchar(5))
  									--,'.'
  									--,cast(lv.MaintenanceNumber as varchar(5))
  								) as [CurrentVersion]
  							from
  								[$(LARS)].[$(LARS_Schema)].LARS_Version as lv
  							order by 
  								lv.MajorNumber
  								,lv.MinorNumber
  								--,lv.MaintenanceNumber
  							desc
  						) as LARS_Current_Version
  					for xml path ('global'), type
  				)
  			)
  		from
  			[Valid].[Learner] ControllingTable
  			inner join
  				(
  					select distinct
  						[LearningDelivery].[LearnRefNumber]
  					from
  						[Valid].[LearningDelivery]
  					where
  						[LearningDelivery].[FundModel]=99
  				) [Filter_LearningDelivery]
  				on [Filter_LearningDelivery].[LearnRefNumber]=[ControllingTable].[LearnRefNumber]
  	end
  GO
  if object_id('[Rulebase].[ALB_Insert_global]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_global]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  
  create procedure [Rulebase].[ALB_Insert_global]
  	(
  		@UKPRN int,
  		@LARSVersion varchar(100),
  		@PostcodeAreaCostVersion varchar(20),
  		@RulebaseVersion varchar(10)
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[ALB_global]
  		values (
  			@UKPRN,
  			@LARSVersion,
  			@PostcodeAreaCostVersion,
  			@RulebaseVersion
  		)
  	end
  go
  if object_id('[Rulebase].[ALB_Insert_Learner]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_Learner]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  
  create procedure [Rulebase].[ALB_Insert_Learner]
  	(
  		@LearnRefNumber varchar(12),
  		@ALBSeqNum int
  	)
  as
  	begin
  		set nocount on
  	end
  go
  if object_id('[Rulebase].[ALB_Insert_Learner_PeriodisedValues]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_Learner_PeriodisedValues]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_Insert_Learner_PeriodisedValues]
  	(
  		@LearnRefNumber varchar(12),
  		@AttributeName varchar(100),
  		@Period_1 decimal(15,5),
  		@Period_2 decimal(15,5),
  		@Period_3 decimal(15,5),
  		@Period_4 decimal(15,5),
  		@Period_5 decimal(15,5),
  		@Period_6 decimal(15,5),
  		@Period_7 decimal(15,5),
  		@Period_8 decimal(15,5),
  		@Period_9 decimal(15,5),
  		@Period_10 decimal(15,5),
  		@Period_11 decimal(15,5),
  		@Period_12 decimal(15,5)
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[ALB_Learner_PeriodisedValues]
  				(
  					LearnRefNumber,
  					AttributeName,
  					Period_1,
  					Period_2,
  					Period_3,
  					Period_4,
  					Period_5,
  					Period_6,
  					Period_7,
  					Period_8,
  					Period_9,
  					Period_10,
  					Period_11,
  					Period_12
  				)
  		values
  			(
  				@LearnRefNumber,
  				@AttributeName,
  				@Period_1,
  				@Period_2,
  				@Period_3,
  				@Period_4,
  				@Period_5,
  				@Period_6,
  				@Period_7,
  				@Period_8,
  				@Period_9,
  				@Period_10,
  				@Period_11,
  				@Period_12
  			)
  	end
  GO
  if object_id('[Rulebase].[ALB_PivotTemporals_Learner]','p') is not null
  	drop procedure [Rulebase].[ALB_PivotTemporals_Learner]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_PivotTemporals_Learner] as
  	begin
  		truncate table [Rulebase].[ALB_Learner_Period]
  		insert into
  			[Rulebase].[ALB_Learner_Period]
  		select
  			LearnRefNumber,
  			Period,
  			max(case AttributeName when 'ALBSeqNum' then Value else null end) ALBSeqNum
  		from
  			(
  				select
  					LearnRefNumber,
  					AttributeName,
  					cast(substring(PeriodValue.Period,8,2) as int) Period,
  					PeriodValue.Value
  				from
  					[Rulebase].[ALB_Learner_PeriodisedValues]
  					unpivot (Value for Period in (Period_1,Period_2,Period_3,Period_4,Period_5,Period_6,Period_7,Period_8,Period_9,Period_10,Period_11,Period_12)) as PeriodValue
  			) Bob
  		group by
  			LearnRefNumber,
  			Period
  	end
  GO
  if object_id('[Rulebase].[ALB_Insert_LearningDelivery]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_LearningDelivery]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  
  create procedure [Rulebase].[ALB_Insert_LearningDelivery]
  	(
  		@LearnRefNumber varchar(12),
  		@AimSeqNumber int,
  		@Achieved bit,
  		@ActualNumInstalm int,
  		@AdvLoan bit,
  		@ALBCode int,
  		@ALBSupportPayment decimal(10,5),
  		@ApplicFactDate date,
  		@ApplicProgWeightFact varchar(1),
  		@AreaCostFactAdj decimal(10,5),
  		@AreaCostInstalment decimal(10,5),
  		@AreaUpliftBalPayment decimal(10,5),
  		@AreaUpliftOnProgPayment decimal(10,5),
  		@FundLine varchar(50),
  		@FundStart bit,
  		@LiabilityDate date,
  		@LoanBursAreaUplift bit,
  		@LoanBursSupp bit,
  		@OutstndNumOnProgInstalm int,
  		@PlannedNumOnProgInstalm int,
  		@WeightedRate decimal(10,4)
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[ALB_LearningDelivery]
  		values (
  			@LearnRefNumber,
  			@AimSeqNumber,
  			@Achieved,
  			@ActualNumInstalm,
  			@AdvLoan,
  			@ApplicFactDate,
  			@ApplicProgWeightFact,
  			@AreaCostFactAdj,
  			@AreaCostInstalment,
  			@FundLine,
  			@FundStart,
  			@LiabilityDate,
  			@LoanBursAreaUplift,
  			@LoanBursSupp,
  			@OutstndNumOnProgInstalm,
  			@PlannedNumOnProgInstalm,
  			@WeightedRate
  		)
  	end
  go
  if object_id('[Rulebase].[ALB_Insert_LearningDelivery_PeriodisedValues]','p') is not null
  	drop procedure [Rulebase].[ALB_Insert_LearningDelivery_PeriodisedValues]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_Insert_LearningDelivery_PeriodisedValues]
  	(
  		@LearnRefNumber varchar(12),
  		@AimSeqNumber int,
  		@AttributeName varchar(100),
  		@Period_1 decimal(15,5),
  		@Period_2 decimal(15,5),
  		@Period_3 decimal(15,5),
  		@Period_4 decimal(15,5),
  		@Period_5 decimal(15,5),
  		@Period_6 decimal(15,5),
  		@Period_7 decimal(15,5),
  		@Period_8 decimal(15,5),
  		@Period_9 decimal(15,5),
  		@Period_10 decimal(15,5),
  		@Period_11 decimal(15,5),
  		@Period_12 decimal(15,5)
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[ALB_LearningDelivery_PeriodisedValues]
  				(
  					LearnRefNumber,
  					AimSeqNumber,
  					AttributeName,
  					Period_1,
  					Period_2,
  					Period_3,
  					Period_4,
  					Period_5,
  					Period_6,
  					Period_7,
  					Period_8,
  					Period_9,
  					Period_10,
  					Period_11,
  					Period_12
  				)
  		values
  			(
  				@LearnRefNumber,
  				@AimSeqNumber,
  				@AttributeName,
  				@Period_1,
  				@Period_2,
  				@Period_3,
  				@Period_4,
  				@Period_5,
  				@Period_6,
  				@Period_7,
  				@Period_8,
  				@Period_9,
  				@Period_10,
  				@Period_11,
  				@Period_12
  			)
  	end
  GO
  if object_id('[Rulebase].[ALB_PivotTemporals_LearningDelivery]','p') is not null
  	drop procedure [Rulebase].[ALB_PivotTemporals_LearningDelivery]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 19 July 2016 08:41
  -- Profile: DCSS Calculation
  -- Rulebase Version: ILR 24+ Loans Calculation 1617, Drop 000, Version 1617.03
  -- =====================================================================================================
  create procedure [Rulebase].[ALB_PivotTemporals_LearningDelivery] as
  	begin
  		truncate table [Rulebase].[ALB_LearningDelivery_Period]
  		insert into
  			[Rulebase].[ALB_LearningDelivery_Period]
  		select
  			LearnRefNumber,
  			AimSeqNumber,
  			Period,
  			max(case AttributeName when 'ALBCode' then Value else null end) ALBCode,
  			max(case AttributeName when 'ALBSupportPayment' then Value else null end) ALBSupportPayment,
  			max(case AttributeName when 'AreaUpliftBalPayment' then Value else null end) AreaUpliftBalPayment,
  			max(case AttributeName when 'AreaUpliftOnProgPayment' then Value else null end) AreaUpliftOnProgPayment
  		from
  			(
  				select
  					LearnRefNumber,
  					AimSeqNumber,
  					AttributeName,
  					cast(substring(PeriodValue.Period,8,2) as int) Period,
  					PeriodValue.Value
  				from
  					[Rulebase].[ALB_LearningDelivery_PeriodisedValues]
  					unpivot (Value for Period in (Period_1,Period_2,Period_3,Period_4,Period_5,Period_6,Period_7,Period_8,Period_9,Period_10,Period_11,Period_12)) as PeriodValue
  			) Bob
  		group by
  			LearnRefNumber,
  			AimSeqNumber,
  			Period
  	end
  GO
  