ACCESS Accommodations for WIDA/AMS v5 ACCESS Accommodations for WIDA/AMS v5

SELECT
S.DCID,
'IL' State_Name_Abbreviation,
'' District_Name,
'IL' || case when ilx.RCDTS_Home_School is null then substr(schx.RCDTS_Home_School, 1, 11) else substr(ilx.RCDTS_Home_School, 1, 11) end District_Number,
'' School_Name,
case when ilx.RCDTS_Home_School is null then substr(schx.RCDTS_Home_School, -4) else substr(ilx.RCDTS_Home_School, -4) end School_Number,
s.state_studentnumber State_Student_ID,
s.id District_Student_ID,
case when ild.Legal_Last_Name is not null then ild.Legal_Last_Name else s.last_name end Student_Last_Name,
case when ild.Legal_First_Name is not null then ild.Legal_First_Name else s.first_name end Student_First_Name,
s.middle_name Student_Middle_Name,
to_char(s.dob, 'MM/DD/YYYY') Birth_Date,
UPPER(s.gender) Gender,
'0' || s.grade_level Grade,
'' Primary_Disability,
'' Secondary_Disability,
'' Student_Type,
'' LIEP_Classification,
'' LIEP_Parental_Refusal,
'' LIEP_Optional_Data,
'' Length_Time_LEP,
decode(s.fedethnicity,1,'Y') Ethnicity_Hispanic,
decode(ild.fer,12,'Y') American_Indian,
decode(ild.fer,13,'Y') Asian,
decode(ild.fer,14,'Y') Black,
decode(ild.fer,15,'Y') Pacific_Islander,
decode(ild.fer,16,'Y') White,
ild.nelb Native_Language,
'' Date_Enrolled_US_School,
'' Title_III_Status,
'' Migrant,
decode(ilx.iep,1,'Y') IEP_Status,
'' Plan504,
case when ac.access_mc = 1 then 'MC' || chr(124) end ||
case when ac.access_ra = 1 then 'RA' || chr(124) end ||
case when ac.access_es = 1 then 'ES' || chr(124) end ||
case when ac.access_sd = 1 then 'SD' || chr(124) end ||
case when ac.access_ir = 1 then 'IR' || chr(124) end ||
case when ac.access_rp = 1 then 'RP' || chr(124) end ||
case when ac.access_sr = 1 then 'SR' || chr(124) end ||
case when ac.access_wd = 1 then 'WD' || chr(124) end ||
case when ac.access_rd = 1 then 'RD' || chr(124) end ||
case when ac.access_ns = 1 then 'NS' || chr(124) end ||
case when ac.access_em = 1 then 'EM' || chr(124) end ||
case when (ac.access_alt = 0 or ac.access_alt is null) and ac.access_lp = 1 then 'LP' || chr(124) end ||
case when (ac.access_alt = 0 or ac.access_alt is null) and ac.access_br is not null then upper(ac.access_br) || chr(124) end Accommodations,
case when s.grade_level = 0 then '' when ac.access_alt = 1 then '' when ac.access_mode is null then 'O' else upper(ac.access_mode) end Mode_of_Administration,
case when ac.access_alt = 1 then '' else upper(ac.access_paper_tier) end Paper_Tier,
case when ac.access_alt = 1 then 'Y' else '' end Alternate_ACCESS,
'' State_Defined_Op_Data,
'' District_Defined_Op_Data,
'' Additional_field
FROM students s
JOIN s_il_stu_demographics_x ild ON s.dcid = ild.studentsdcid
JOIN s_il_stu_x ilx ON s.dcid = ilx.studentsdcid
JOIN U_ACCESS_ACCOM ac ON s.dcid = ac.studentsdcid
JOIN schools sch ON s.schoolid = sch.school_number
LEFT OUTER JOIN S_IL_SCH_X schx ON sch.dcid = schx.schoolsdcid
WHERE s.enroll_status = 0
AND s.grade_level > -1
AND ilx.lep = 1
AND ac.access_accommodations = 1

Student DCIDState Name AbbreviationDistrict NameDistrict NumberSchool NameSchool NumberState Student IDDistrict Student IDStudent Last NameStudent First NameStudent Middle NameBirth DateGenderGradePrimary DisabilitySecondary DisabilityStudent TypeLIEP ClassificationLIEP - Parental RefusalLIEP - Optional DataLength of Time in LEP/ELL ProgramEthnicity - Hispanic/LatinoRace - American Indian/Alaskan NativeRace - AsianRace - Black/African AmericanRace - Pacific Islander/HawaiianRace - WhiteNative LanguageDate First Enrolled US SchoolTitle III StatusMigrantIEP Status504 PlanAccommodationMode of AdministrationPaper TierAlternate ACCESS for ELLs TesterState Defined Optional DataDistrict Defined Optional DataAdditional field to be used by a state if needed 0 1 0 0 1 1 1 1 1 1 1 1 Opt1 0 1 1 0 10