Extraction filters TODO

1. The input is a list of ‘enabled specialties’: (‘103’,’120’,’144’,’145’,’160’,’130’,’330’,’361’,’400’,’655’).

2. We ask for an ‘initial load’ of the last 5 years of data, and a ‘daily load’ any data which has changed in the last 10 weeks. For the initial load we ask for all data which has not been archived. For daily loads we want any rows which have changed, whether or not that data has been archived.

3. A list of Patient IDs is created – including every patient who has an open referral to one or more of our ‘enabled specialties’ .

4. Each dataset is filtered by the patient IDs form step 1 – excluding test patients, and also by the filters below:

Data set	Source system	Data wareshouse Table	Additional filter
Referrals	MBI	REFERRALS	Referral not closed, and specialty referred to in ‘enabled specialties’ list
Bluespier theatre cases	Bluespier	SURGICAL_EVENTS	None
CRIS Requests/Exams	CRIS		SPECIALTY_CODE IN enabled specialties OR 810/812 (diagnostic specialties)
MBI Outpatient appointment	MBI	OP_APPOINTMENTS	Referral ID is contained within referral IDS from the referral extract (a)
MBI Pre-op assessment appointments	MBI	OP_APPOINTMENTS	None
MBI Orders	MBI	ORDERS	ORDERS.[ORDER_SPECIALTY_CODE] IN ‘enabled specialties’
MBI Order actions	MBI	ORDER_ACTION	order id is contained in order table
MBI Results	MBI	RESULTS	order id is contained in order table
MBI Waiting list (PREOP_ASSESSMENT)	MBI	WLENTRY	None
MBI_PTL Extract	MBI	RTT_EVENTS	Referral ID is contained within referral IDS from the referral extract (a)
MBI_RTT Extract	MBI	RTT_EPISODES	Referral ID is contained within referral IDS from the referral extract (a)
MBI_SPELL Extract	MBI	SPELL	None
MBI_EPISODES EXTRACT	MBI	IP_EPISODES	None