Versions Compared

Old Version 2

changes.mady.by.user Jordan Lamb

Saved on

compared with

New Version 3

changes.mady.by.user Jordan Lamb

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Data Source

PROD_DB

Job/Process Source (Refresh Interval)

Application

...

Job, Baseline Banner Tables

(Live Data)

Functional User(s)

Elise Garcia (Director Student Success)

Report Owner

Elise Garcia (Director Student Success)

Contains PII

Yes

🛠 What It’s Used For

Undocumented students often do not offer up residency information on their application because of misuse and mistreatment. To connect qualifying students to AB 540 services we first need to identify them. Student Success spends time each semester trying to identify qualifying students who have a high dollar balance on their account and providing services.

🔘 Options

Options

Description

Term Selection

Code Block
languagesql
SELECT

...

  TERM AS "TERM_CODE",

...

        SEMESTER AS "DESC",

...

TERM + ' - ' + SEMESTER AS DISPLAY
FROM	cos_argos_term_dw_vw

Allows one term selection from a list of the last 10 years. That Term Selection is used for how far back to look at registered students. It includes the term selected.

Term defined as:

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
nameterm
nopaneltrue

Minimum Balance Owed

Allows for user entry where only numerical values will allow the report to work. This value is used to return only student records that have

...

a Current Account Balance greater than or equal to that.

📄 Results

Field Name

Data Source/Logic

Description (sample)

...

NAME

...

BANINST1.GOKNAME.F_GET_NAME (SPRIDEN_PIDM, 'PFML')

Uses Preferred First Name if it exists. If it doesn’t exist, it uses Legal First Name.

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
nameFirstName
nopaneltrue

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
nameMIDINIT
nopaneltrue

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
nameLASTNAME
nopaneltrue

ACCOUNT

...

BALANCE

...

Code Block
languagesql
     INNER JOIN
     (  SELECT TBRACCD_PIDM                         AS ACCOUNT_PIDM,
               SUM (
                   DECODE (TBBDETC_TYPE_IND,
                           'C', TBRACCD_AMOUNT,
                           TBRACCD_AMOUNT * -1))    AS ACCOUNT_BALANCE
          FROM TBRACCD
               INNER JOIN TBBDETC
                   ON TBBDETC_DETAIL_CODE = TBRACCD_DETAIL_CODE
      GROUP BY TBRACCD_PIDM)
         ON ACCOUNT_PIDM = REGISTERED_PIDM

Grabs the student’s current Account Balance as detailed by the Cashier’s department.

Formats that numerical value as currency.

HIGHEST REGISTERED TERM

Code Block
languagesql
(SELECT SGBSTDN_PIDM                AS RECENT_PIDM,
    MAX (SGBSTDN_TERM_CODE_EFF)     AS RECENT_TERM_CODE
    FROM SGBSTDN

Lists the furthest out term code the student was/is registered for.

...

PERSONAL_EMAIL

GIF of Dr. Emmett Brown (played by Christopher Lloyd) saying 'Great Scott'. From the 'Back to the Future' film.Image Added

Can be in the future.

RACE/ETHNICITY

REPORTSNET.f_get_race_ethn_desc

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
nameRACE_ETHN_CDE
nopaneltrue

PERSONAL EMAIL

BANINST1.Cos_f_Get_Email_Personal (SPRIDEN_PIDM)

Insert excerpt
DOC:Common Definitions
DOC:Common Definitions
namePERSONAL_EMAIL
nopaneltrue

🗺 Report Domain

Returns

...

the most recent student information where the report options are met.

🔀 Examples

Options:

Term Selection: “202120 - Spring 2021”

Minimum Balance Owed: “500”

👨‍⚕️ Student 1:

Last registered for classes in “202020 - Spring 2020”

Current Balance Owed: “$1000”

⛔ Would Not Show Up

👷‍♀️ Student 2:

Last registered for classes in “202120 - Spring 2021”

Current Balance Owed: “$499”

⛔ Would Not Show Up

👮‍♂️ Student 3:

Last registered for classes in “202220 - Spring 2022”

Owed “$1000” in “202120 - Spring 2021”

Current Balance Owed: “$499”

⛔ Would Not Show Up

👨‍🏭 Student 4:

Last registered for classes in “202120 - Spring 2021”

Owed “$60” in “202120 - Spring 2021”

Current Balance Owed: “$750”

(plus) Would Show Up

🕵️‍♀️ Student 5:

Last registered for classes in “202220 - Spring 2022”

Current Balance Owed: “$500”

(plus) Would Show Up

Change History