TVF_LearnerMainApp_PRG

[robinwilson16]

Returns a learner's main application at the programme level
Will always return a single line for each learner, useful for learner lists including main application information

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerMainApp_PRG] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN 

	SELECT TOP 1
		Value = PRG.m_id
	FROM ulive.dbo.capd_module PRG
	INNER JOIN ulive.dbo.capd_moduleapplication MAP
		ON MAP.ma_module = PRG.m_id
	INNER JOIN ulive.dbo.capd_studentapplication SA
		ON SA.sa_id = MAP.ma_studentapplication
	INNER JOIN ulive.dbo.capd_offering LNKPQ 
		ON LNKPQ.o_source = PRG.m_id
	INNER JOIN ulive.dbo.capd_module Q
		ON Q.m_id = LNKPQ.o_destination
	INNER JOIN ulive.dbo.capd_offering LNKQY 
		ON LNKQY.o_source = LNKPQ.o_destination
	INNER JOIN ulive.dbo.capd_moduleisr ISRY
		ON ISRY.mi_id = LNKQY.o_destination
	INNER JOIN ulive.dbo.capd_student S
		ON S.s_id = SA.sa_student
	CROSS APPLY GC.dbo.TVF_GetCodeRef ( SA.sa_status, 'applicstatus') STREF
	WHERE
		PRG.m_type = 'P'
		AND PRG.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND PRG.m_end >= LEFT ( @Session, 4 ) + '-08-01'​​
		AND PRG.m_start >= LEFT ( @Session, 4 ) + '-08-01'​​ -- Limit to programmes starting this year
		AND S.s_studentreference = @StudentRef
	ORDER BY
		CASE 
			WHEN STREF.Value = 'WDN' THEN 2 
			WHEN STREF.Value = 'TRF' THEN 3
			ELSE 1
		END ASC,--App Status
		CASE WHEN Q.m_programmearea = 'CA' THEN 1 ELSE 2 END ASC,--AimType
		CASE WHEN ISRY.mi_ilraimtype = '2' THEN '4.5' ELSE ISRY.mi_ilraimtype END DESC,--AimType -Moved here as not always filled in correctly
		COALESCE ( ISRY.mi_ilrplanlearnhours, 0 ) + COALESCE ( ISRY.mi_ilrplaneephours, 0 ) DESC,--Hours
		PRG.m_start ASC --Prog Start Date