text: use [Intrajob_1516]
  GO
  if object_id('[Rulebase].[SFA_Get_Cases]','p') is not null
  	drop procedure [Rulebase].[SFA_Get_Cases]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:41
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_Get_Cases] as
  
  	begin
  		set nocount on
  		select
  			CaseData
  		from
  			[Rulebase].[SFA_Cases]
  	end
  GO
  if object_id('[Rulebase].[SFA_Insert_Cases]','p') is not null
  	drop procedure [Rulebase].[SFA_Insert_Cases]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:41
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_Insert_Cases] as
  
  	begin
  		set nocount on
  
  		insert into
  			[Rulebase].[SFA_Cases]
  			(
  				[LearnRefNumber],
  				CaseData
  			)
  		select
  			ControllingTable.[LearnRefNumber],
  			convert(xml,
  				(
  					select
  						case when [ORG_Details].[DirectFundedEmpIndicator]=1 then 'true' when  [ORG_Details].[DirectFundedEmpIndicator]=0 then 'false' else null end as [@DirectFundEmp],
  						[LARS_Current_Version].[CurrentVersion] as [@LARSVersion],
  						case when [ORG_Details].[LongTermResid]=1 then 'true' when  [ORG_Details].[LongTermResid]=0 then 'false' else null end as [@LTRC],
  						case when [ORG_Funding].[FundingFactorValue] = '1' then 'true' else 'false' end as [@SpecialistResources],
  						[LearningProvider].[UKPRN] as [@UKPRN],
  						(
  						select
  							[Learner].[DateOfBirth] as [@DateOfBirth],
  							[PostcodeDisadvantage].[SFA_Uplift] as [@DisadvantageUplift],
  							[Learner].[LearnRefNumber] as [@LearnRefNumber],
  							(
  							select
  								[LearningDelivery].[LearnRefNumber] as [@LearnRefNumber],
  								[LearningDelivery].[AchDate] as [@AchDate],
  								[LearningDelivery].[AddHours] as [@AddHours],
  								[LearningDelivery].[AimSeqNumber] as [@AimSeqNumber],
  								[LearningDelivery].[AimType] as [@AimType],
  								[PostcodeAreaCost].[SFA_AreaCostFactor] as [@AreaCostFactor],
  								[LearningDelivery].[CompStatus] as [@CompStatus],
  								[LearningDelivery].[EmpOutcome] as [@EmpOutcome],
  								[LARS_LearningDelivery].[EnglandFEHEStatus] as [@EnglandFEHEStatus],
  								[LARS_LearningDelivery].[EnglPrscID] as [@EnglPrscID],
  								[LARS_LearningDelivery].[FrameworkCommonComponent] as [@FrameworkCommonComponent],
  								[LARS_FrameworkAims].[FrameworkComponentType] as [@FrameworkComponentType],
  								[LearningDelivery].[FworkCode] as [@FworkCode],
  								[LearningDelivery].[LearnActEndDate] as [@LearnActEndDate],
  								[LearningDelivery].[LearnPlanEndDate] as [@LearnPlanEndDate],
  								[LearningDelivery].[LearnStartDate] as [@LearnStartDate],
  								[LearningDelivery].[LrnDelFAM_EEF] as [@LrnDelFAM_EEF],
  								[LearningDelivery].[LrnDelFAM_FFI] as [@LrnDelFAM_FFI],
  								[LearningDelivery].[LrnDelFAM_LDM1] as [@LrnDelFAM_LDM1],
  								[LearningDelivery].[LrnDelFAM_LDM2] as [@LrnDelFAM_LDM2],
  								[LearningDelivery].[LrnDelFAM_LDM3] as [@LrnDelFAM_LDM3],
  								[LearningDelivery].[LrnDelFAM_LDM4] as [@LrnDelFAM_LDM4],
  								[LearningDelivery].[LrnDelFAM_RES] as [@LrnDelFAM_RES],
  								[LearningDelivery].[LrnDelFAM_WPL] as [@LrnDelFAM_WPL],
  								[LearningDelivery].[OrigLearnStartDate] as [@OrigLearnStartDate],
  								[LearningDelivery].[OtherFundAdj] as [@OtherFundAdj],
  								[LearningDelivery].[Outcome] as [@Outcome],
  								[LearningDelivery].[PriorLearnFundAdj] as [@PriorLearnFundAdj],
  								[LearningDelivery].[ProgType] as [@ProgType],
  								[LearningDelivery].[PwayCode] as [@PwayCode],
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateUnWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateUnWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('AdultLRUnweightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('AdultLRWeightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[WeightingFactor]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[WeightingFactor],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ADULT_LR'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('AdultLRWeightingFactor'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'APP_ACT_COST'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'APP_ACT_COST'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('ERAppWeightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[WeightingFactor]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'APP_ACT_COST'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[WeightingFactor],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'APP_ACT_COST'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('ERAppWeightingFactor'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateUnWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateUnWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('EROtherUnweightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('EROtherWeightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[WeightingFactor]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[WeightingFactor],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'ER_OTHER'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('EROtherWeightingFactor'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateUnWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'Matrix'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateUnWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'Matrix'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('MatrixUnweightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'Matrix'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'Matrix'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('MatrixWeightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[WeightingFactor]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'Matrix'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[WeightingFactor],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'Matrix'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('MatrixWeightingFactor'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateUnWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateUnWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('OLASSUnweightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[RateWeighted]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[RateWeighted],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('OLASSWeightedRate'), type
  									)
  								),
  								convert(xml,
  									(	select
  											convert(xml,
  												(	
  													select
  														X.[Date] AS [@Date],
  														X.[Value] AS [@Value]
  													from
  														(
  															select
  																[LARS_Funding].[EffectiveFrom] AS [Date], 
  																CONVERT(VARCHAR(25),[LARS_Funding].[WeightingFactor]) AS [Value] 
  															from
  																[$(LARS)].[Version_003].[LARS_Funding]
  															where
  																[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  															union all select
  																dateadd(d,1,X.[EffectiveTo]) as [Date],
  																'unknown-val' as [Value]
  															from
  																(
  																	select
  																		[LearnAimRef], 
  																		[EffectiveFrom], 
  																		[EffectiveTo], 
  																		[WeightingFactor],
  																		dense_rank() over (partition by [LearnAimRef] order by [EffectiveFrom] desc) as RecordRank
  																	from
  																		[$(LARS)].[Version_003].[LARS_Funding]
  																	where
  																		[LARS_Funding].[LearnAimRef] = [LearningDelivery].[LearnAimRef]
  																		and [LARS_Funding].[FundingCategory] = 'OLASS_ADULT'			
  																) as X
  															where
  																RecordRank=1
  																and X.[EffectiveTo] is not null
  														) as X for xml path('ChangePoint')
  												)
  											) for xml path('OLASSWeightingFactor'), type
  									)
  								),
  								(
  								select
  									[LearningDeliveryFAM].[LearnDelFAMCode] as [@LearnDelFAMCode],
  									[LearningDeliveryFAM].[LearnDelFAMDateFrom] as [@LearnDelFAMDateFrom],
  									[LearningDeliveryFAM].[LearnDelFAMDateTo] as [@LearnDelFAMDateTo],
  									[LearningDeliveryFAM].[LearnDelFAMType] as [@LearnDelFAMType]
  								from
  									[Intrajob_1516].[Valid].[LearningDeliveryFAM]
  								where
  									[LearningDeliveryFAM].[LearnRefNumber] = [LearningDelivery].[LearnRefNumber]
  									and [LearningDeliveryFAM].[AimSeqNumber] = [LearningDelivery].[AimSeqNumber]
  								for xml path ('LearningDeliveryFAM'), type
  								),
  								(
  								select
  									[LARS_LearningDeliveryCategory].[EffectiveFrom] as [@LearnDelCatDateFrom],
  									[LARS_LearningDeliveryCategory].[EffectiveTo] as [@LearnDelCatDateTo],
  									[LARS_LearningDeliveryCategory].[CategoryRef] as [@LearnDelCatRef]
  								from
  									[$(LARS)].[Version_003].[LARS_LearningDeliveryCategory]
  								where
  									[LARS_LearningDeliveryCategory].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  								for xml path ('LearningDeliveryLARSCategory'), type
  								),
  								(
  								select
  									[LARS_AnnualValue].[BasicSkillsType] as [@LearnDelAnnValBasicSkillsTypeCode],
  									[LARS_AnnualValue].[EffectiveFrom] as [@LearnDelAnnValDateFrom],
  									[LARS_AnnualValue].[EffectiveTo] as [@LearnDelAnnValDateTo]
  								from
  									[$(LARS)].[Version_003].[LARS_AnnualValue]
  								where
  									[LARS_AnnualValue].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  								for xml path ('LearningDeliveryAnnualValue'), type
  								)
  							from
  								[Intrajob_1516].[Valid].[LearningDelivery]
  								left join [$(PostcodeFactors)].[RefData].[PostcodeAreaCost]
  									on [PostcodeAreaCost].[Postcode]=[LearningDelivery].[DelLocPostCode]
  								left join [$(LARS)].[Version_003].[LARS_LearningDelivery]
  									on [LARS_LearningDelivery].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  								left join [$(LARS)].[Version_003].[LARS_FrameworkAims]
  									on [LARS_FrameworkAims].[FworkCode]=[LearningDelivery].[FworkCode]
  									and [LARS_FrameworkAims].[ProgType]=[LearningDelivery].[ProgType]
  									and [LARS_FrameworkAims].[PwayCode]=[LearningDelivery].[PwayCode]
  									and [LARS_FrameworkAims].[LearnAimRef]=[LearningDelivery].[LearnAimRef]
  							where
  								[LearningDelivery].[LearnRefNumber] = [Learner].[LearnRefNumber]
  								and [LearningDelivery].[FundModel]=35
  							for xml path ('LearningDelivery'), type
  							),
  							(
  							select
  								[LearnerEmploymentStatus].[DateEmpStatApp] as [@DateEmpStatApp],
  								[LearnerEmploymentStatus].[EmpId] as [@EmpId],
  								case when [LargeEmployers].[ERN] is not null then 'true' else 'false' end as [@LargeEmployer]
  							from
  								[Intrajob_1516].[Valid].[LearnerEmploymentStatus]
  								left join [$(LargeEmployers)].[RefData].[LargeEmployers]
  									on [LargeEmployers].[ERN]=[LearnerEmploymentStatus].[EmpId]
  							where
  								[LearnerEmploymentStatus].[LearnRefNumber] = [Learner].[LearnRefNumber]
  							for xml path ('LearnerEmploymentStatus'), type
  							)
  						from
  							[Intrajob_1516].[Valid].[Learner]
  							left join [$(PostcodeFactors)].[RefData].[PostcodeDisadvantage]
  								on [PostcodeDisadvantage].[Postcode]=[Learner].[HomePostcode]
  						where
  							[Learner].[LearnRefNumber] = [ControllingTable].[LearnRefNumber]
  						for xml path ('Learner'), type
  						)
  					from
  						[Intrajob_1516].[Valid].[LearningProvider]
  						cross join [$(LARS)].[Version_003].[LARS_Current_Version]
  						left join [$(Org)].[1415].[ORG_Details]
  							on [ORG_Details].[UKPRN]=[LearningProvider].[UKPRN]
  						left join [$(Org)].[1415].[ORG_Funding]
  							on [ORG_Funding].[UKPRN]=[LearningProvider].[UKPRN]
  							and [ORG_Funding].[FundingFactorType]='ADULT SKILLS'
  							and [ORG_Funding].[FundingFactor]='SPECIALIST RESOURCES'
  					for xml path ('global'), type
  				)
  			)
  		from
  			[Valid].[Learner] ControllingTable
  			inner join
  				(
  					select distinct
  						[LearningDelivery].[LearnRefNumber]
  					from
  						[Valid].[LearningDelivery]
  					where
  						[LearningDelivery].[FundModel]=35
  				) [Filter_LearningDelivery]
  				on [Filter_LearningDelivery].[LearnRefNumber]=[ControllingTable].[LearnRefNumber]
  	end
  GO
  if object_id('[Rulebase].[SFA_Insert_global]','p') is not null
  	drop procedure [Rulebase].[SFA_Insert_global]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:41
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_Insert_global]
  	(
  		@UKPRN varchar(8),
  		@CurFundYr varchar(9),
  		@LARSVersion varchar(10),
  		@OrgVersion varchar(50),
  		@PostcodeDisadvantageVersion varchar(50),
  		@RulebaseVersion varchar(10)
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[SFA_global]
  		values (
  			@UKPRN,
  			@CurFundYr,
  			@LARSVersion,
  			@OrgVersion,
  			@PostcodeDisadvantageVersion,
  			@RulebaseVersion
  		)
  	end
  GO
  if object_id('[Rulebase].[SFA_Insert_LearningDelivery]','p') is not null
  	drop procedure [Rulebase].[SFA_Insert_LearningDelivery]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:41
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_Insert_LearningDelivery]
  	(
  		@LearnRefNumber varchar(12),
  		@AimSeqNumber int,
  		@AchApplicDate date,
  		@Achieved bit,
  		@AchieveElement decimal(10,5),
  		@AchievePayElig bit,
  		@AchievePayment decimal(10,5),
  		@AchievePayPct decimal(10,5),
  		@AchievePayPctPreTrans decimal(10,5),
  		@AchievePayPctTrans decimal(10,5),
  		@AchPayTransHeldBack decimal(10,5),
  		@ActualDaysIL int,
  		@ActualNumInstalm int,
  		@ActualNumInstalmPreTrans int,
  		@ActualNumInstalmTrans int,
  		@AdjLearnStartDate date,
  		@AdltLearnResp bit,
  		@AgeAimStart int,
  		@AimValue decimal(10,5),
  		@AppAdjLearnStartDate date,
  		@AppAgeFact decimal(10,5),
  		@AppATAGTA bit,
  		@AppCompetency bit,
  		@AppFuncSkill bit,
  		@AppFuncSkill1618AdjFact decimal(10,5),
  		@AppKnowl bit,
  		@AppLearnStartDate date,
  		@ApplicFundRateDate date,
  		@ApplicProgWeightFact varchar(1),
  		@ApplicUnweightFundRate decimal(10,5),
  		@ApplicWeightFundRate decimal(10,5),
  		@AppNonFund bit,
  		@AreaCostFactAdj decimal(10,5),
  		@BalancePayment decimal(10,5),
  		@BalancePaymentUncapped decimal(10,5),
  		@BalancePct decimal(10,5),
  		@BalancePctTrans decimal(10,5),
  		@BalInstalmPreTrans int,
  		@BaseValueUnweight decimal(10,5),
  		@CapFactor decimal(10,5),
  		@ClassRm bit,
  		@DisUpFactAdj decimal(10,4),
  		@EmpOutcomePay decimal(10,5),
  		@EmpOutcomePayElig bit,
  		@EmpOutcomePct decimal(10,5),
  		@EmpOutcomePctHeldBackTrans decimal(10,5),
  		@EmpOutcomePctPreTrans decimal(10,5),
  		@EmpOutcomePctTrans decimal(10,5),
  		@EmpRespOth bit,
  		@ESOL bit,
  		@FullyFund bit,
  		@FundLine varchar(100),
  		@FundStart bit,
  		@InstPerPeriod int,
  		@LargeEmployerID int,
  		@LargeEmployerSFAFctr decimal(10,2),
  		@LargeEmployerStatusDate date,
  		@LearnSuppFund bit,
  		@LearnSuppFundCash decimal(10,5),
  		@LTRCUpliftFctr decimal(10,5),
  		@NonGovCont decimal(10,5),
  		@OLASSCustody bit,
  		@OnProgPayment decimal(10,5),
  		@OnProgPaymentUncapped decimal(10,5),
  		@OnProgPayPct decimal(10,5),
  		@OnProgPayPctPreTrans decimal(10,5),
  		@OnProgPayPctTrans decimal(10,5),
  		@OutstndNumOnProgInstalm int,
  		@OutstndNumOnProgInstalmTrans int,
  		@PlannedNumOnProgInstalm int,
  		@PlannedNumOnProgInstalmTrans int,
  		@PlannedTotalDaysIL int,
  		@PlannedTotalDaysILPreTrans int,
  		@PropFundRemain decimal(10,2),
  		@PropFundRemainAch decimal(10,2),
  		@PrscHEAim bit,
  		@Residential bit,
  		@Restart bit,
  		@SpecResUplift decimal(10,5),
  		@StartPropTrans decimal(10,5),
  		@ThresholdDays int,
  		@Traineeship bit,
  		@Trans bit,
  		@TransInstPerPeriod int,
  		@TrnAdjLearnStartDate date,
  		@TrnWorkPlaceAim bit,
  		@TrnWorkPrepAim bit,
  		@UnWeightedRateFromESOL decimal(10,5),
  		@UnweightedRateFromLARS decimal(10,5),
  		@WeightedRateFromESOL decimal(10,5),
  		@WeightedRateFromLARS decimal(10,5),
  		@WPLProv bit
  	)
  as
  	begin
  		set nocount on
  		insert into
  			[Rulebase].[SFA_LearningDelivery]
  		values (
  			@LearnRefNumber,
  			@AimSeqNumber,
  			@AchApplicDate,
  			@Achieved,
  			@AchieveElement,
  			@AchievePayElig,
  			@AchievePayPctPreTrans,
  			@AchPayTransHeldBack,
  			@ActualDaysIL,
  			@ActualNumInstalm,
  			@ActualNumInstalmPreTrans,
  			@ActualNumInstalmTrans,
  			@AdjLearnStartDate,
  			@AdltLearnResp,
  			@AgeAimStart,
  			@AimValue,
  			@AppAdjLearnStartDate,
  			@AppAgeFact,
  			@AppATAGTA,
  			@AppCompetency,
  			@AppFuncSkill,
  			@AppFuncSkill1618AdjFact,
  			@AppKnowl,
  			@AppLearnStartDate,
  			@ApplicFundRateDate,
  			@ApplicProgWeightFact,
  			@ApplicUnweightFundRate,
  			@ApplicWeightFundRate,
  			@AppNonFund,
  			@AreaCostFactAdj,
  			@BalInstalmPreTrans,
  			@BaseValueUnweight,
  			@CapFactor,
  			@ClassRm,
  			@DisUpFactAdj,
  			@EmpOutcomePayElig,
  			@EmpOutcomePctHeldBackTrans,
  			@EmpOutcomePctPreTrans,
  			@EmpRespOth,
  			@ESOL,
  			@FullyFund,
  			@FundLine,
  			@FundStart,
  			@LargeEmployerID,
  			@LargeEmployerSFAFctr,
  			@LargeEmployerStatusDate,
  			@LTRCUpliftFctr,
  			@NonGovCont,
  			@OLASSCustody,
  			@OnProgPayPctPreTrans,
  			@OutstndNumOnProgInstalm,
  			@OutstndNumOnProgInstalmTrans,
  			@PlannedNumOnProgInstalm,
  			@PlannedNumOnProgInstalmTrans,
  			@PlannedTotalDaysIL,
  			@PlannedTotalDaysILPreTrans,
  			@PropFundRemain,
  			@PropFundRemainAch,
  			@PrscHEAim,
  			@Residential,
  			@Restart,
  			@SpecResUplift,
  			@StartPropTrans,
  			@ThresholdDays,
  			@Traineeship,
  			@Trans,
  			@TrnAdjLearnStartDate,
  			@TrnWorkPlaceAim,
  			@TrnWorkPrepAim,
  			@UnWeightedRateFromESOL,
  			@UnweightedRateFromLARS,
  			@WeightedRateFromESOL,
  			@WeightedRateFromLARS,
  			@WPLProv
  		)
  	end
  GO
  if object_id('[Rulebase].[SFA_Insert_LearningDelivery_PeriodisedValues]','p') is not null
  	drop procedure [Rulebase].[SFA_Insert_LearningDelivery_PeriodisedValues]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:41
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_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].[SFA_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].[SFA_PivotTemporals_LearningDelivery]','p') is not null
  	drop procedure [Rulebase].[SFA_PivotTemporals_LearningDelivery]
  GO
  -- =====================================================================================================
  -- Generated by Data Dictionary Version 1.0.0.0
  -- Date: 21 July 2015 09:42
  -- Profile: 1516 Test Harness Calculation
  -- Rulebase Version: DCSS 1516 SFA Calculation 1516, Drop 000, Version 1516.03
  -- =====================================================================================================
  create procedure [Rulebase].[SFA_PivotTemporals_LearningDelivery] as
  	begin
  		truncate table [Rulebase].[SFA_LearningDelivery_Period]
  		insert into
  			[Rulebase].[SFA_LearningDelivery_Period]
  		select
  			LearnRefNumber,
  			AimSeqNumber,
  			Period,
  			max(case AttributeName when 'AchievePayment' then Value else null end) AchievePayment,
  			max(case AttributeName when 'AchievePayPct' then Value else null end) AchievePayPct,
  			max(case AttributeName when 'AchievePayPctTrans' then Value else null end) AchievePayPctTrans,
  			max(case AttributeName when 'BalancePayment' then Value else null end) BalancePayment,
  			max(case AttributeName when 'BalancePaymentUncapped' then Value else null end) BalancePaymentUncapped,
  			max(case AttributeName when 'BalancePct' then Value else null end) BalancePct,
  			max(case AttributeName when 'BalancePctTrans' then Value else null end) BalancePctTrans,
  			max(case AttributeName when 'EmpOutcomePay' then Value else null end) EmpOutcomePay,
  			max(case AttributeName when 'EmpOutcomePct' then Value else null end) EmpOutcomePct,
  			max(case AttributeName when 'EmpOutcomePctTrans' then Value else null end) EmpOutcomePctTrans,
  			max(case AttributeName when 'InstPerPeriod' then Value else null end) InstPerPeriod,
  			max(case AttributeName when 'LearnSuppFund' then Value else null end) LearnSuppFund,
  			max(case AttributeName when 'LearnSuppFundCash' then Value else null end) LearnSuppFundCash,
  			max(case AttributeName when 'OnProgPayment' then Value else null end) OnProgPayment,
  			max(case AttributeName when 'OnProgPaymentUncapped' then Value else null end) OnProgPaymentUncapped,
  			max(case AttributeName when 'OnProgPayPct' then Value else null end) OnProgPayPct,
  			max(case AttributeName when 'OnProgPayPctTrans' then Value else null end) OnProgPayPctTrans,
  			max(case AttributeName when 'TransInstPerPeriod' then Value else null end) TransInstPerPeriod
  		from
  			(
  				select
  					LearnRefNumber,
  					AimSeqNumber,
  					AttributeName,
  					cast(substring(PeriodValue.Period,8,2) as int) Period,
  					PeriodValue.Value
  				from
  					[Rulebase].[SFA_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
  