Helpful information regarding Banner extracts for PortalGuard as of 8/15/2019 (for term 202010).


The Student Admissions Application has undergone a number of changes over the last 6 months, and one of the criteria for pulling PortalGuard was adversely affected. There was a new flood of calls from students who were created in AD, but couldn't log in to RaiderNet because their PortalGuard info was sent too soon, and not re-sent after their Student records/AD accounts were created.

*ADDITIONAL TWEAK IN AUGUST 2019 TO ACCOUNT FOR DELAYED MIM AD CREATION.


Originally, the extract was run a few times per day, and included 5 sections:

1. Registered Students

2. Admitted Students

3. Employees

4. New FlexReg Students (not yet registered)

5. Old Former Students (based on new GOBTPAC activity)


Several sections have been removed (commented out) to exclude functions that we no longer use (e.g., FlexReg).

By the end of summer 2019, there had been several updates to the baseline Banner Admissions process, including several new codes affecting the PortalGuard extract script. 


As a result, section 2 needed to be modified to account for the new codes; rather than increasing the list of APDC codes to look for, I flipped the code to exclude the 2 "Not accepted/Not Eligible" codes.


--PortalGuard Incremental (hourly) Load v2.4.2

--1. Registered Students

SELECT DISTINCT

gobtpac_external_user AS "Username",

TO_CHAR(SPBPERS_BIRTH_DATE, 'YYYYMMDD') AS "MandAns1",

spriden_id AS "MandAns2"

FROM

SPRIDEN

JOIN GOBTPAC ON spriden.spriden_pidm = gobtpac.gobtpac_pidm

JOIN SPBPERS ON spriden.spriden_pidm = spbpers.spbpers_pidm

LEFT OUTER JOIN SFRSTCA ON spriden.spriden_pidm = sfrstca.sfrstca_pidm

--LEFT OUTER JOIN SARAPPD ON spriden.spriden_pidm = sarappd.sarappd_pidm AND sarappd.sarappd_apdc_code IN ('AD','FA')

JOIN SGBSTDN ON spriden.spriden_pidm = sgbstdn.sgbstdn_pidm

WHERE spriden_change_ind IS NULL

AND SPBPERS_DEAD_IND IS NULL

AND sfrstca.sfrstca_term_code >= 201930

AND SGBSTDN.SGBSTDN_TERM_CODE_EFF >=201830

--AND sfrstca.sfrstca_rsts_code LIKE 'R%'

AND (GOBTPAC.GOBTPAC_ACTIVITY_DATE >= TRUNC(SYSDATE-1)

          OR

          SGBSTDN.SGBSTDN_ACTIVITY_DATE >= TRUNC(SYSDATE-1))

AND GOBTPAC.GOBTPAC_EXTERNAL_USER NOT LIKE '%\_dup%' ESCAPE '\'

AND SPRIDEN.SPRIDEN_PIDM NOT IN (1996966,1999360,1999017,2000441,1999239)

UNION

--2. Admitted Students (not yet registered)

SELECT DISTINCT

gobtpac_external_user AS "Username",

TO_CHAR(SPBPERS_BIRTH_DATE, 'YYYYMMDD') AS "MandAns1",

spriden_id AS "MandAns2"

FROM

SPRIDEN

JOIN GOBTPAC ON spriden.spriden_pidm = gobtpac.gobtpac_pidm

JOIN SPBPERS ON spriden.spriden_pidm = spbpers.spbpers_pidm

--UPDATED FOR NEW APPLICATION CODES 8/15/2019

--JOIN SARAPPD ON spriden.spriden_pidm = sarappd.sarappd_pidm AND sarappd.sarappd_apdc_code IN ('AD','FA')

JOIN SARAPPD ON spriden.spriden_pidm = sarappd.sarappd_pidm AND sarappd.sarappd_apdc_code NOT IN ('NA','NE')

WHERE spriden_change_ind IS NULL

AND SPBPERS_DEAD_IND IS NULL

--AND GOBTPAC.GOBTPAC_ACTIVITY_DATE >= TRUNC(SYSDATE)

AND sarappd_term_code_entry >= 201930

AND SARAPPD_ACTIVITY_DATE >= TRUNC(SYSDATE-1)

AND GOBTPAC.GOBTPAC_EXTERNAL_USER NOT LIKE '%\_dup%' ESCAPE '\'

AND SPRIDEN.SPRIDEN_PIDM NOT IN (1996966,1999360,1999017,2000441,1999239)

UNION

--3. Employees

SELECT DISTINCT

gobtpac_external_user AS "Username",

TO_CHAR(SPBPERS_BIRTH_DATE, 'YYYYMMDD') AS "MandAns1",

spriden_id AS "MandAns2"

FROM

SPRIDEN

JOIN GOBTPAC ON spriden.spriden_pidm = gobtpac.gobtpac_pidm

JOIN SPBPERS ON spriden.spriden_pidm = spbpers.spbpers_pidm

JOIN PEBEMPL ON spriden.spriden_pidm = pebempl.pebempl_pidm AND pebempl.pebempl_empl_status = 'A'

WHERE spriden_change_ind IS NULL

AND SPBPERS_DEAD_IND IS NULL

AND GOBTPAC.GOBTPAC_ACTIVITY_DATE >= TRUNC(SYSDATE-1)

AND GOBTPAC.GOBTPAC_EXTERNAL_USER NOT LIKE '%\_dup%' ESCAPE '\'

AND SPRIDEN.SPRIDEN_PIDM NOT IN (1996966,1999360,1999017,2000441,1999239)

/*

--sections 4 and 5 commented out on 8/5/2019 to account for MIM processing

UNION

--4 New FlexReg Students (not yet registered)

SELECT DISTINCT

gobtpac_external_user AS "Username",

TO_CHAR(SPBPERS_BIRTH_DATE, 'YYYYMMDD') AS "MandAns1",

spriden_id AS "MandAns2"

FROM

SPRIDEN

JOIN GOBTPAC ON spriden.spriden_pidm = gobtpac.gobtpac_pidm

JOIN SPBPERS ON spriden.spriden_pidm = spbpers.spbpers_pidm

WHERE spriden_change_ind IS NULL

AND SPBPERS_DEAD_IND IS NULL

AND SPRIDEN.SPRIDEN_CREATE_DATE >= TRUNC(SYSDATE-1)

AND SPRIDEN.SPRIDEN_CREATE_USER = 'FLEXREG_USER'

AND GOBTPAC.GOBTPAC_EXTERNAL_USER NOT LIKE '%\_dup%' ESCAPE '\'

AND SPRIDEN.SPRIDEN_PIDM NOT IN (1996966,1999360,1999017,2000441,1999239)

UNION

--5. Old Former Students (GOBTPAC Activity)

SELECT DISTINCT

gobtpac_external_user AS "Username",

TO_CHAR(SPBPERS_BIRTH_DATE, 'YYYYMMDD') AS "MandAns1",

spriden_id AS "MandAns2"

FROM

SPRIDEN

JOIN GOBTPAC ON spriden.spriden_pidm = gobtpac.gobtpac_pidm

JOIN SPBPERS ON spriden.spriden_pidm = spbpers.spbpers_pidm

WHERE spriden_change_ind IS NULL

AND SPBPERS_DEAD_IND IS NULL

AND GOBTPAC.GOBTPAC_ACTIVITY_DATE >= TRUNC(SYSDATE-1)

AND GOBTPAC.GOBTPAC_EXTERNAL_USER NOT LIKE '%\_dup%' ESCAPE '\'

AND SPRIDEN.SPRIDEN_PIDM NOT IN (1996966,1999360,1999017,2000441,1999239)

*/