Applicant Enrolment and Withdrawal Report

This page refers to this Tableau report → https://tableau.york.ac.uk/#/workbooks/3602/views
Original wiki page → https://uoy.atlassian.net/wiki/spaces/SRASystems/pages/39563893
V_SEL_CONF_RPT_WDN_DEF
Base population sources:
T_SEL_CONF_YEARS
CAP
V_SEL_CONF_RO_MCR
UDD (SEL_CONF_REC, SEL_CONF_NOT)
DRL
Withdrawn and Deferred applications and students (also the inverse population of non-Withdrwan and non-Deferred), sources:
CAP
Base
SCE
The end product is a list of all applications, with a flag to indicate Withdrawn and Deferred that is consistent with other BIU sources.
CREATE OR REPLACE FORCE EDITIONABLE VIEW "UOY"."V_SEL_CONF_RPT_WDN_DEF" ("ACADEMIC_YEAR", "CAP_STUC", "CAP_PK", "ADMISSIONS_ENTITY", "ADMISSIONS_ENTITY_NAME", "FACULTY_NAME", "CLEARING_FLAG", "HOME/OVERSEAS", "MEASURE", "OCCURRED", "HEADCOUNT") AS
  with
DATES as
(
    --PCWDR DATES DEFINITION
    --Definition of confirmation dates for different academic years used in the below queries
    select
        ACADEMIC_YEAR,
        FOLLOWING_YEAR,
        UCAS_CYCLE,
        CONFIRMATION_CUTOFF,
        FIRST_DECEMBER,
        START_TERM
    from
        T_SEL_CONF_YEARS
    where 1=1
    and ACTIVE = 'Y'
)
,
GETBASE as
(
    --PCWDR BASE POPULATION
    --Definition of the base population
    --Everyone with a UG CAP record who had at some point after confirmation (defined in the dates table) had a DRL record indicating they were UF for the academic year in question
    --Extract fields needed to check if clearing student (includes lob), flag set in separate statement below because of this.
    select
        DRLF.DRL_AYRC as AYRC,
        CAP.CAP_AYRC,
        CAP.CAP_STUC,
        CAP.CAP_APFS,
        CAP.CAP_SEQN,
        CAP.CAP_ROUC,
        UDD1.UDD_NAME ADMISSIONS_ENTITY_NAME,
        UDD1.UDD_CODE ADMISSIONS_ENTITY,
        UDD2.UDD_UDFK FACULTY_NAME,
        CAP.CAP_UCHO, -- clearing fields
        CAP.CAP_ERND,
        CAP.CAP_RSD3,
        CAP.CAP_RSD2,
        CAP.CAP_CYCL,
        DRLF.DRL_CHGD||' '||to_char(TO_TIMESTAMP(to_char(DRLF.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLF.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3') as DRLF_PRIMARY_KEY--converts the time to timestamp and adds the milliseconds to the time
    from SRS_CAP CAP
    left join
        V_SEL_CONF_RO_MCR
    on
        V_SEL_CONF_RO_MCR.CYCLE_YEAR = CAP.CAP_CYCL
        and V_SEL_CONF_RO_MCR.MCR_CODE = CAP.CAP_MCRC
        and V_SEL_CONF_RO_MCR.ROUTE_CODE = CAP.CAP_ROUC
    left join
        MEN_UDD UDD1
    on
        UDD1.UDD_UDVC = 'SEL_CONF_REC'
        and UDD1.UDD_CODE = V_SEL_CONF_RO_MCR.ADMISSIONS_ENTITY
    left join
        MEN_UDD UDD2
    on
        UDD2.UDD_UDVC = 'SEL_CONF_NOT'
        and UDD2.UDD_CODE = V_SEL_CONF_RO_MCR.ADMISSIONS_ENTITY
    left join
        SRS_DRL DRLF
    on
        DRLF.DRL_STUC = CAP.CAP_STUC
        and DRLF.DRL_APFS = CAP.CAP_APFS
        and DRLF.DRL_CAPS = CAP.CAP_SEQN
    where 1=1
    and CAP.CAP_AESC = 'U'
    and CAP.CAP_MCRC like 'UU%'
    and DRLF.DRL_AYRC in (select ACADEMIC_YEAR from DATES)
    and DRLF.DRL_UNDC = 'U'
    and DRLF.DRL_UNRC = 'F'
    and DRLF.DRL_STAC in ('A', 'T', 'TAP', 'ADR')
 
    --and cap_stuc = '205000090'--testing
 
    and not exists
    ( --Exclude duplicate DRLFs for each applicant.  Ensure this is the latest DRLF.
        select
            'Has later DRLF'
        from
            SRS_DRL DRLF2
        where 1=1
        and DRLF2.DRL_STUC = CAP.CAP_STUC
        and DRLF2.DRL_APFS = CAP.CAP_APFS
        and DRLF2.DRL_CAPS = CAP.CAP_SEQN
        and DRLF2.DRL_AYRC = DRLF.DRL_AYRC
        and DRLF2.DRL_UNDC = 'U'
        and DRLF2.DRL_UNRC = 'F'
        and DRLF2.DRL_STAC in ('A', 'T', 'TAP', 'ADR')
        and to_char(DRLF2.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLF2.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLF2.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3') > to_char(DRLF.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLF.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLF.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3')
    )
    and not exists
    ( --Applicant has not withdrawn or deferred at the time of confirmation
        select
            'Has a withdrawn/deferred DRL after DRLF but before confirmation'
        from
            SRS_DRL DRLFX
        where 1=1
        and DRLFX.DRL_STUC = CAP.CAP_STUC
        and DRLFX.DRL_APFS = CAP.CAP_APFS
        and DRLFX.DRL_CAPS = CAP.CAP_SEQN
        and not
        (
            DRLFX.DRL_AYRC = DRLF.DRL_AYRC
            and DRLFX.DRL_UNDC = 'U'
            and DRLFX.DRL_UNRC = 'F'
            and DRLFX.DRL_STAC in ('A', 'T', 'TAP', 'ADR')
        )
        --and to_char(drlfx.DRL_CHGD, 'YYYYMMDD')||to_char(DRLFX.DRL_CHGT, 'HH24MISS') > to_char(DRLF.DRL_CHGD, 'YYYYMMDD')||to_char(DRLF.DRL_CHGT, 'HH24MISS')
        and to_char(DRLFX.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLFX.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLFX.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3') > to_char(DRLF.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLF.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLF.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3')
        and DRLFX.DRL_CHGD <= (select CONFIRMATION_CUTOFF from DATES where ACADEMIC_YEAR = DRLF.DRL_AYRC)
    )
    order by
        CAP.CAP_STUC
)
,
BASE as (
    -- finish base population by adding clearing flag. 
    -- Due to lobs in remote table need second step to set flag.
    select
        AYRC,
        CAP_STUC,
        CAP_APFS,
        CAP_SEQN,
        CAP_ROUC,
        ADMISSIONS_ENTITY_NAME,
        ADMISSIONS_ENTITY,
        FACULTY_NAME,
        case
        when   
            (
                CAP_UCHO in ('0', '6', '7')
                and CAP_ERND is null
                and nvl(CAP_RSD3,CAP_RSD2) >= (select CONFIRMATION_CUTOFF from DATES where ACADEMIC_YEAR = AYRC)
                and CAP_CYCL = (select UCAS_CYCLE from DATES where ACADEMIC_YEAR = AYRC)
            ) then
            'CLEARING'
        else
            'MAIN SCHEME'
        end as CLEARING_FLAG
    from GETBASE
)
,
WDN_DEF_APP as
(
    --APPLICANTS: WITHDRAWN AND DEFERRED
    --Withdrawn and Deferred Applicants in Base Population
    --Defines those applicants in the base population which currently have non base academic year UF CAP records - hence deferred or withdrawn
    select
        BASE.AYRC,
        BASE.ADMISSIONS_ENTITY,
        BASE.ADMISSIONS_ENTITY_NAME,
        BASE.FACULTY_NAME,
        BASE.CLEARING_FLAG,
        CAP.CAP_STUC,
        CAP.CAP_APFS,
        CAP.CAP_SEQN
    from
        SRS_CAP CAP
    inner join
        BASE
    on
        BASE.CAP_STUC = CAP.CAP_STUC
        and BASE.CAP_APFS = CAP.CAP_APFS
        and BASE.CAP_SEQN = CAP.CAP_SEQN
    where 1=1
    and not
        (
            CAP.CAP_AYRC = BASE.AYRC
            and CAP.CAP_IDRC like '%UF'
            and CAP.CAP_STAC in ('A', 'T', 'TAP', 'ADR')
        )
)
,
NON_WDN_DEF_APP as
(
    --NONE WITHDRAWN AND DEFERRED APPLICANTS
    --Defines those applicants with current UF CAP records for 2017/8 - hence not withdrawn or deferred.
    --The number returned by this query, added to the number retruned by the "Withdrawn and
    --Deferred Applicants in Base Population" query should add up to the base population, as a consistency check.
    select
        BASE.AYRC,
        BASE.ADMISSIONS_ENTITY,
        BASE.ADMISSIONS_ENTITY_NAME,
        BASE.FACULTY_NAME,
        BASE.CLEARING_FLAG,
        CAP.CAP_STUC,
        CAP.CAP_APFS,
        CAP.CAP_SEQN
    from
        SRS_CAP CAP
    inner join
        BASE
    on
        BASE.CAP_STUC = CAP.CAP_STUC
        and BASE.CAP_APFS = CAP.CAP_APFS
        and BASE.CAP_SEQN = CAP.CAP_SEQN
    where 1=1
    and CAP.CAP_AESC = 'U'
    and CAP.CAP_MCRC like 'UU%'
    and CAP.CAP_AYRC = BASE.AYRC
    and CAP.CAP_IDRC like '%UF'
    and CAP.CAP_STAC in ('A', 'T', 'TAP', 'ADR')
)
,
WDN_STU as (
    --STUDENTS: WITHDRAWN
    --Defines those students in the base population which currently have 2017/8 UF CAP records but the corresponding 2017/8 SCE record is not pending or registered.
    select
        BASE.AYRC,
        BASE.ADMISSIONS_ENTITY,
        BASE.ADMISSIONS_ENTITY_NAME,
        BASE.FACULTY_NAME,
        BASE.CLEARING_FLAG,
        CAP.CAP_STUC,
        CAP.CAP_APFS,
        CAP.CAP_SEQN
    from
        SRS_CAP CAP
    inner join
        BASE
    on
        BASE.CAP_STUC = CAP.CAP_STUC
        and BASE.CAP_APFS = CAP.CAP_APFS
        and BASE.CAP_SEQN = CAP.CAP_SEQN
    left join
        SRS_SCE SCE
    on
        SCE.SCE_SCJC = CAP.CAP_SCJC
        and SCE.SCE_AYRC = BASE.AYRC
        and SCE.SCE_CRSC like 'U%'
    where 1=1
    and SCE.SCE_STAC like '%WDN%'
)
,
NON_WDN_STU as
(
    --STUDENTS: NOT WITHDRAWN
    --Defines those students in the base population which currently have 2017/8 UF CAP records and the corresponding 2017/8 SCE record is pending or registered.
    select
        BASE.AYRC,
        BASE.ADMISSIONS_ENTITY,
        BASE.ADMISSIONS_ENTITY_NAME,
        BASE.FACULTY_NAME,
        BASE.CLEARING_FLAG,
        CAP.CAP_STUC,
        CAP.CAP_APFS,
        CAP.CAP_SEQN
    from
        SRS_CAP CAP
    inner join
        BASE
    on
        BASE.CAP_STUC = CAP.CAP_STUC
        and BASE.CAP_APFS = CAP.CAP_APFS
        and BASE.CAP_SEQN = CAP.CAP_SEQN
    left join
        SRS_SCE SCE
    on
        SCE.SCE_SCJC = CAP.CAP_SCJC
        and SCE.SCE_AYRC = BASE.AYRC
        and SCE.SCE_CRSC like 'U%'
    where 1=1
    and
        (
            SCE.SCE_STAC not like '%WDN%'
            or
            SCE.SCE_SCJC is null
        )
)
,
WDN_DEF_STU_APP as
(
    --STUDENTS AND APPLICANTS: WITHDRAWN OR DEFERRED
    --Defines those students who have withdrawn, deferred or transferred
    select
        WDN_DEF_APP.*
    from
        WDN_DEF_APP
    union
    select
        WDN_STU.*
    from
        WDN_STU
)
,
NON_WDN_DEF_STU_APP as
(
    --STUDENTS AND APPLICANTS: NOT WITHDRAWN OR DEFERRED
    --Defines those students who have not withdrawn or deferred
    select
        NON_WDN_DEF_APP.*
    from
        NON_WDN_DEF_APP
    intersect
    select
        NON_WDN_STU.*
    from
        NON_WDN_STU
)
,
REPORT_WDN_DEF as
(
    --REPORT COLUMNS - STUDENTS AND APPLICANTS: WITHDRAWN OR DEFERRED
    select
        WDN_DEF_STU_APP.AYRC,
        WDN_DEF_STU_APP.ADMISSIONS_ENTITY,
        WDN_DEF_STU_APP.ADMISSIONS_ENTITY_NAME,
        WDN_DEF_STU_APP.FACULTY_NAME,
        WDN_DEF_STU_APP.CLEARING_FLAG,
        to_char(CAP.CAP_APCC) "HOME/OVERSEAS",
 
        case 
        when ((CAP.CAP_STAC != 'A' and CAP.CAP_STAC != 'T' and CAP.CAP_STAC != 'TAP') or (SCE.SCE_STAC like '%WDN%') or (CAP.CAP_IDRC not like '%UF')) then 'WDN'
        when CAP.CAP_AYRC >= DATES.FOLLOWING_YEAR then 'DEF'
        else 'ERR'
        end as measure,
 
        DRLW.DRL_CHGD || ' ' || to_char(TO_TIMESTAMP(to_char(DRLW.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLW.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3') as DRLF_PRIMARY_KEY, -- testing
        SCE.SCE_STAD, -- testing
        CAP.CAP_STUC,
        CAP.CAP_STUC ||'#'||CAP.CAP_APFS || '#' || CAP.CAP_SEQN as CAP_PK, -- testing
 
        case
        when (to_char(SCE.SCE_STAD, 'YYYYMMDD') >= to_char(DATES.FIRST_DECEMBER, 'YYYYMMDD')
        or to_char(DRLW.DRL_CHGD, 'YYYYMMDD') >= to_char(DATES.FIRST_DECEMBER, 'YYYYMMDD')) then
            'Post 1st December'
        else
            case
            when (to_char(SCE.SCE_STAD, 'YYYYMMDD') >= to_char(DATES.START_TERM, 'YYYYMMDD')
            or to_char(DRLW.DRL_CHGD, 'YYYYMMDD') >= to_char(DATES.START_TERM, 'YYYYMMDD')) then
                'Post-Start of Term'
            else
                'Pre-Start of Term'
            end
        end as OCCURRED  
 
    from
        WDN_DEF_STU_APP
    left join
        SRS_CAP CAP
    on
        CAP.CAP_STUC = WDN_DEF_STU_APP.CAP_STUC
        and CAP.CAP_APFS = WDN_DEF_STU_APP.CAP_APFS
        and CAP.CAP_SEQN = WDN_DEF_STU_APP.CAP_SEQN
    left join
        SRS_SCE SCE
    on
        SCE.SCE_SCJC = CAP.CAP_SCJC
        and SCE.SCE_AYRC = WDN_DEF_STU_APP.AYRC
        and SCE.SCE_CRSC like 'U%'
    left join
        DATES
    on
        DATES.ACADEMIC_YEAR = WDN_DEF_STU_APP.AYRC
    left join
        SRS_DRL DRLW
    on
        DRLW.DRL_STUC = CAP.CAP_STUC
        and DRLW.DRL_APFS = CAP.CAP_APFS
        and DRLW.DRL_CAPS = CAP.CAP_SEQN
        and not
            (
                DRLW.DRL_AYRC = WDN_DEF_STU_APP.AYRC
                and DRLW.DRL_UNDC = 'U'
                and DRLW.DRL_UNRC = 'F'
                and DRLW.DRL_STAC in ('A', 'T', 'TAP', 'ADR')
            )
        and
            (
                DRLW.DRL_CHGD > DATES.CONFIRMATION_CUTOFF --This should retreive the first DRL record after confirmation where we thought they were no longer coming
                or
                DRLW.DRL_CHGD is null
            )
    where 1=1
    and not exists
        (
            select
                'Has earlier DRLW'
            from
                SRS_DRL DRLW2
            where 1=1
            and DRLW2.DRL_STUC = CAP.CAP_STUC
            and DRLW2.DRL_APFS = CAP.CAP_APFS
            and DRLW2.DRL_CAPS = CAP.CAP_SEQN
            and not
                (
                    DRLW2.DRL_AYRC = WDN_DEF_STU_APP.AYRC
                    and DRLW2.DRL_UNDC = 'U'
                    and DRLW2.DRL_UNRC = 'F'
                    and DRLW2.DRL_STAC in ('A', 'T', 'TAP', 'ADR')
                )
            and DRLW2.DRL_CHGD > (select CONFIRMATION_CUTOFF from DATES where ACADEMIC_YEAR = WDN_DEF_STU_APP.AYRC)
            --and to_char(drlw2.drl_chgd, 'YYYYMMDD')||to_char(drlw2.drl_chgt, 'HH24MISS') < to_char(drlw.drl_chgd, 'YYYYMMDD')||to_char(drlw.drl_chgt, 'HH24MISS')
            and to_char(DRLW2.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLW2.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLW2.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3') < to_char(DRLW.DRL_CHGD, 'YYYYMMDD')||to_char(TO_TIMESTAMP(to_char(DRLW.DRL_CHGT, 'HH24:MI:SS'),'HH24:MI:SS') + NUMTODSINTERVAL( DRLW.DRL_CHTI / 100, 'SECOND' ),'HH24:MI:SS.FF3')
        )
)
,
REPORT_WDN_DEF_GROUP as
(
    select
        AYRC as ACADEMIC_YEAR,
        REPORT_WDN_DEF.CAP_STUC,
        CAP_PK,
        ADMISSIONS_ENTITY,
        ADMISSIONS_ENTITY_NAME,
        FACULTY_NAME,
        CLEARING_FLAG,
        "HOME/OVERSEAS",
        measure,
        OCCURRED,
        1 as HEADCOUNT
    from
        REPORT_WDN_DEF
)
,
REPORT_NON_WDN_DEF as
(
    --REPORT COLUMNS - STUDENTS AND APPLICANTS: NOT WITHDRAWN OR DEFERRED
    select
        NON_WDN_DEF_STU_APP.AYRC ACADEMIC_YEAR,
        NON_WDN_DEF_STU_APP.CAP_STUC,
        NON_WDN_DEF_STU_APP.CAP_STUC||'#'||NON_WDN_DEF_STU_APP.CAP_APFS||'#'||NON_WDN_DEF_STU_APP.CAP_SEQN CAP_PK,
        NON_WDN_DEF_STU_APP.ADMISSIONS_ENTITY,
        NON_WDN_DEF_STU_APP.ADMISSIONS_ENTITY_NAME,
        NON_WDN_DEF_STU_APP.FACULTY_NAME,
        NON_WDN_DEF_STU_APP.CLEARING_FLAG,
        to_char(CAP.CAP_APCC) "HOME/OVERSEAS",
 
        case
        when SCE.SCE_STAC like 'P%' then 'PND'
        when SCE.SCE_STAC like 'HUP%' then 'PND'
        else 'RSR' end as measure,
        to_char('') as OCCURRED,
 
        1 as HEADCOUNT
    from
        NON_WDN_DEF_STU_APP
    left join
        SRS_CAP CAP
    on
        CAP.CAP_STUC = NON_WDN_DEF_STU_APP.CAP_STUC
        and CAP.CAP_APFS = NON_WDN_DEF_STU_APP.CAP_APFS
        and CAP.CAP_SEQN = NON_WDN_DEF_STU_APP.CAP_SEQN
    left join
        SRS_SCE SCE
    on
        SCE.SCE_SCJC = CAP.CAP_SCJC
        and SCE.SCE_AYRC = NON_WDN_DEF_STU_APP.AYRC
        and SCE.SCE_CRSC like 'U%'
)
,
REPORT_BASE as
(
    --REPORT COLUMNS - BASE POPULATION
    select
        BASE.AYRC ACADEMIC_YEAR,
        BASE.CAP_STUC,
        BASE.CAP_STUC||'#'||BASE.CAP_APFS||'#'||BASE.CAP_SEQN CAP_PK,
        BASE.ADMISSIONS_ENTITY,
        BASE.ADMISSIONS_ENTITY_NAME,
        BASE.FACULTY_NAME,
        BASE.CLEARING_FLAG,
        to_char(CAP.CAP_APCC) "HOME/OVERSEAS",
        to_char('Base') MEASURE,
        to_char('') OCCURRED,
        1 as HEADCOUNT
    from
        BASE
    left join
        SRS_CAP CAP
    on
        CAP.CAP_STUC = BASE.CAP_STUC
        and CAP.CAP_APFS = BASE.CAP_APFS
        and CAP.CAP_SEQN = BASE.CAP_SEQN
    left join
        SRS_SCE SCE
    on
        SCE.SCE_SCJC = CAP.CAP_SCJC
        and SCE.SCE_AYRC = BASE.AYRC
        and SCE.SCE_CRSC like 'U%'
)
--Main Query
select
    REPORT_WDN_DEF_GROUP.ACADEMIC_YEAR
    ,REPORT_WDN_DEF_GROUP.CAP_STUC
    ,REPORT_WDN_DEF_GROUP.CAP_PK
    ,REPORT_WDN_DEF_GROUP.ADMISSIONS_ENTITY
    ,REPORT_WDN_DEF_GROUP.ADMISSIONS_ENTITY_NAME
    ,REPORT_WDN_DEF_GROUP.FACULTY_NAME
    ,REPORT_WDN_DEF_GROUP.CLEARING_FLAG
    ,REPORT_WDN_DEF_GROUP."HOME/OVERSEAS"
    ,REPORT_WDN_DEF_GROUP.MEASURE
    ,REPORT_WDN_DEF_GROUP.OCCURRED
    ,REPORT_WDN_DEF_GROUP.HEADCOUNT
from
    REPORT_WDN_DEF_GROUP
--where academic_year >= '2018/9' -- JB added filter to use in snapshot.
union all
select
    REPORT_NON_WDN_DEF.ACADEMIC_YEAR
    ,REPORT_NON_WDN_DEF.CAP_STUC
    ,REPORT_NON_WDN_DEF.CAP_PK
    ,REPORT_NON_WDN_DEF.ADMISSIONS_ENTITY
    ,REPORT_NON_WDN_DEF.ADMISSIONS_ENTITY_NAME
    ,REPORT_NON_WDN_DEF.FACULTY_NAME
    ,REPORT_NON_WDN_DEF.CLEARING_FLAG
    ,REPORT_NON_WDN_DEF."HOME/OVERSEAS"
    ,REPORT_NON_WDN_DEF.MEASURE
    ,REPORT_NON_WDN_DEF.OCCURRED
    ,REPORT_NON_WDN_DEF.HEADCOUNT
from
    REPORT_NON_WDN_DEF
--where academic_year >= '2018/9' -- JB added filter to use in snapshot.
union all
select
    REPORT_BASE."ACADEMIC_YEAR"
    ,REPORT_BASE.CAP_STUC
    ,REPORT_BASE.CAP_PK
    ,REPORT_BASE."ADMISSIONS_ENTITY"
    ,REPORT_BASE."ADMISSIONS_ENTITY_NAME"
    ,REPORT_BASE."FACULTY_NAME"
    ,REPORT_BASE."CLEARING_FLAG"
    ,REPORT_BASE."HOME/OVERSEAS"
    ,REPORT_BASE."MEASURE"
    ,REPORT_BASE."OCCURRED"
    ,REPORT_BASE."HEADCOUNT"
from
    REPORT_BASE
--where academic_year >= '2018/9' -- JB added filter to use in snapshot.;;
 
 
  GRANT SELECT ON "UOY"."V_SEL_CONF_RPT_WDN_DEF" TO "SITS_READONLY";
  GRANT SELECT ON "UOY"."V_SEL_CONF_RPT_WDN_DEF" TO "UOY_ROLE";