DARTS : Applicant Extract SQR Procedures

UM_APPL_EXTRACT Notes:

The following are the list of procedures in Applicant Extract:

• Begin Setup
  • defining and declaring variables
  • SQL variables defined
• Proc Options(Main)
  • Main procedure that invokes other procedures 
• Page Heading
• Procedure List_Applicants
  • Tables
    • PS_PERSONAL_DATA A
    • PS_ADM_APPL_DATA B
  • Existing logic
    • Define all the SQR string variables to a space
    • use nvl() to check if null values exists and if does, replace them with a space
    • record min_acad_plan and acad_plan. min_acad_plan is the first row that you can that replaces the space, acad_plan (max_acad_plan) is the most recently acad_plan data that exists for most recent term effective date
    • max value gets overwritten each time
    • symbol " ( + ) " denotes right outer join (ANSI 89 standards) 
  • Talend logic
    • join ADM_APPL_PROG and ADM_APPL_DATA to obtain a list of rows (inner join)
    • Compare that list of rows with rows retrieved from subselect procedure's 
      • if matches, throw them away
      • if it does not match, retain them 
    • union with ADM_APPL_PROG
• Select Parameters
• Get Input for Terms 
  • STRM?
• Get Previous Name
• Get Preferred email address
• Get Preferred Phone Number
• Get NID for USA and Canada
• Get Ethnic Data
• Get Country Data
• Get Calculated Noel Levitz Index
• Get Residency Data
• Get Acad Prog Descr
  • Academic Program Description
• Get Last School Info
• Get UM_ACAD_PROG_APPL, UM_ACAD_APPL_PLAN from applied row
• Get Description for Program Reason
• Get Application PlanData
  • Does SQR compiler compares ASCII value of a space with characters (acad_plan)
  • returns all the rows first and executes if condition row by row to evaluate the min and max
  • ( + ) is the legacy ANSI symbol for right outer join (mid 1980s)
  • max(effective date) is to retrieve most recent effective date
  • acad plan, acad sub plan need more clarification – it looks unclear at the moment
  • the business logic and requirements need to be gathered for this procedure as it remains unclear why the max-min procedure is employed
• Get Student PlanData
• Get Student Response
• Get External Organization Table

  • The procedure runs iteratively for list of last schools attended - if there were 4 schools then the procedure runs 4 times.

  • If the student list of previous schools with their from and to date being populated then the procedure runs and updates the list of schools sorting by populating the most recent school on top followed by second most recent school and so on.

  • If to_date was not available then the variable woodoo will sort the tables. If to_date is available then bucket 1 is populated (the order in which the last schools populated happens) randomly. If to_date is identical then bucket 1 is populated randomly.

  • The case statement is primarily used for sorting in descending order based on the to_date.

• SQL insert rows
• Scan Student Career Term Table
• Scan Student and Admissions Stack from old to new
• Get xlatlongname from good ol PSXLATITEM
• Move data from UM_APPL_EXT_BLD to UM_APPL_EXTRACT
• SQL-Recover Procedure
• SQC Include Files

Things for discussion:

• SQR Compiler may compare a condition using ASCII chart (clarification needed)
• SQR does not need commas when selecting multiple fields (it can execute without an error)
• What is plan sequence?
• Student Career Number is a unique number per student per diploma
  • if a student pursues 2 bachelors degrees then the student will have 2 different student career numbers
  • if in case of a double major then the student will have 1 student career number and dual major as acad_plan_type
• acad_plan is major of a student
  • acad_sub_plan is whether that major is dual major, or a certificate or minor or etc...
• academic_career is classification of diploma: bachelors, masters, certificate, JD, etc...