Page 1 of 1

TVF_FundingModel

Posted: Thu Mar 02, 2017 10:58 pm
by robinwilson16
Returns the funding model of the selected course - note that your setup may differ so you may need to adapt

Code: Select all

CREATE FUNCTION [dbo].[TVF_FundingModel]
(
	@Session VARCHAR(7),
	@EnrolID numeric(16,0)
)
RETURNS TABLE
AS RETURN 

	/*
	DECLARE @Session VARCHAR(7) = '2016-17'
	DECLARE @EnrolID numeric(16, 0) = 727000120745400
	*/

	SELECT	       
		/*QualCode = Q.m_reference,
		QualTitle = Q.m_name,
		FundStream = EIQ.ei_ilra10,
		FundStreamMapped = A10.vc_national3,
		FundSource = EIQ.ei_ilra11x1,
		WPL = EIQ.ei_ilrworkplacelearning,
		AdvLearningLoan = EIQ.ei_ilradvlearningloan,
		FrameworkCode = EIQ.ei_ilra26,
		ProgType = EIQ.ei_ilra15,
		CourseFund = MCQ.mc_code1,*/
		Value = 
			CASE 
				WHEN FC.FeeConcessionType = 'OS' THEN 'OS'
				WHEN FC.FeeConcessionType = 'INT' THEN 'INT'
				WHEN COALESCE ( A10.vc_national3, '99' ) = '10' THEN 'ASL'
				WHEN COALESCE ( A10.vc_national3, '99' ) = '25' THEN 'EFA'
				WHEN COALESCE ( A10.vc_national3, '99' ) = '35' AND EIQ.ei_ilrworkplacelearning IS NULL THEN 'SFA_CL'
				WHEN COALESCE ( A10.vc_national3, '99' ) = '35' AND EIQ.ei_ilrworkplacelearning = -1 THEN 'APP'
				WHEN COALESCE ( A10.vc_national3, '99' ) = '35' AND EIQ.ei_ilra15 > 0 THEN 'WPL'
				WHEN EIQ.ei_ilra11x1 = '001' THEN 'HE'
				WHEN COALESCE ( A10.vc_national3, '99' ) IN ( '99', '' ) AND EIQ.ei_ilradvlearningloan = -1 THEN 'LOAN_24'
				WHEN COALESCE ( A10.vc_national3, '99' ) IN ( '99', '' ) AND EIQ.ei_ilradvlearningloan IS NULL THEN 'NO_FUND'
				WHEN EIQ.ei_ilra10 = '99s' THEN 'SCHOOL'
				WHEN MCQ.mc_code1 = 'S' THEN 'SCHOOL'
				ELSE 'ERROR'
			END
	FROM ulive.dbo.capd_module Q
	INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
		ON MEQ.e_module = Q.m_id
	INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
		ON EIQ.ei_id = MEQ.e_id
	LEFT JOIN ulive.dbo.capd_modulecustom MCQ
		ON MCQ.mc_custommodule = Q.m_id
	LEFT JOIN ulive.dbo.caps_valid_codes A10
		ON A10.vc_code = EIQ.ei_ilra10
		AND A10.vc_domain = 'ilra10'
	LEFT JOIN (
		SELECT DISTINCT --Need to complete this for international fee band - see Dan
			QualID = MEY.e_parent,
			StudentID = MEY.e_student,
			FeeConcessionType = 
				CASE
					WHEN FEEC.fc_name = 'Overseas' THEN 'OS'
					WHEN FEE.f_feeband = 'EFL' THEN 'INT'
					ELSE NULL
				END
		FROM ulive.dbo.capd_moduleenrolment MEY
		INNER JOIN ulive.dbo.capd_enrolmentfee EFEE
			ON EFEE.ef_feeenrolment = MEY.e_id
		INNER JOIN ulive.dbo.capd_fee FEE
			ON FEE.f_id = EFEE.ef_id
		LEFT JOIN ulive.dbo.capd_concessionmapping FEECM
			ON FEECM.cm_id = EFEE.ef_concessionmapping
		LEFT JOIN ulive.dbo.capd_feeconcession FEEC
			ON FEEC.fc_id = FEECM.cm_feeconcession
		WHERE
			(
				FEE.f_feeband = 'STN'
				AND FEEC.fc_name = 'Overseas'
			)
			OR FEE.f_feeband = 'EFL'
	) FC ON FC.QualID = Q.m_id
		AND FC.StudentID = MEQ.e_student
	WHERE
		Q.m_type = 'Q'
		AND Q.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND Q.m_end >= LEFT ( @Session, 4 ) + '-08-01'​​
		AND MEQ.e_id = @EnrolID