ACCESS Accommodations for WIDA/AMS v4 ACCESS Accommodations for WIDA/AMS v4

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,
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,
s.state_studentnumber State_Student_ID,
s.id District_Student_ID,
s.grade_level Grade,
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,
'' Length_Time_LEP,
'' Title_III_Status,
'' Migrant,
decode(ilx.iep,1,'Y') IEP_Status,
'' Plan504,
'' Primary_Disability,
'' Secondary_Disability,
'' LIEP_Classification,
'' LIEP_Parental_Refusal,
'' LIEP_Optional_Data,
case when ac.access_mc = 1 then 'Y' else '' end MC_Accommodation,
case when ac.access_ra = 1 then 'Y' else '' end RA_Accommodation,
case when ac.access_es = 1 then 'Y' else '' end ES_Accommodation,
case when ac.access_alt = 1 then '' when ac.access_lp = 1 then 'Y' else '' end LP_Accommodation,
case when ac.access_alt = 1 then '' else upper(ac.access_br) end BR_Accommodation,
case when ac.access_sd = 1 then 'Y' else '' end SD_Accommodation,
case when ac.access_ir = 1 then 'Y' else '' end IR_Accommodation,
case when ac.access_rp = 1 then 'Y' else '' end RP_Accommodation,
case when ac.access_sr = 1 then 'Y' else '' end SR_Accommodation,
case when ac.access_wd = 1 then 'Y' else '' end WD_Accommodation,
case when ac.access_rd = 1 then 'Y' else '' end RD_Accommodation,
case when ac.access_ns = 1 then 'Y' else '' end NS_Accommodation,
case when ac.access_em = 1 then 'Y' else '' end EM_Accommodation,
'' State_Defined_Op_Data,
'' District_Defined_Op_Data,
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,
'' Student_Type,
'' 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 NumberStudent Last NameStudent First NameStudent Middle NameBirth DateGenderState Student IDDistrict Student IDGradeEthnicity - Hispanic/LatinoRace - American Indian/Alaskan NativeRace - AsianRace - Black/African AmericanRace - Pacific Islander/HawaiianRace - WhiteNative LanguageDate First Enrolled US SchoolLength of Time in LEP/ELL Program Title III StatusMigrantIEP Status504 PlanPrimary DisabilitySecondary DisabilityLIEP ClassificationLIEP - Parental RefusalLIEP - Optional DataAccommodation - MCAccommodation - RAAccommodation - ESAccommodation - LPAccommodation - BRAccommodation - SDAccommodation - IRAccommodation - RPAccommodation - SRAccommodation - WDAccommodation - RDAccommodation - NSAccommodation - EMState Defined Optional DataDistrict Defined Optional DataMode of AdministrationPaper TierAlternate ACCESS for ELLs TesterStudent TypeAdditional 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