CU_BAR_SR_00071_1

Background:

Patrick proposed to modify CU_BAR_SR_00071 to add missing students email. However, we create a new query based on the requirements.

Proposed Requirements:

Enrollment greater than 0 for specified term
Not active eff date in “PI” student group between specified term dates (provided and confirmed by Registrar)
CAMP email is null or not @baruchmail/@BARUCHMAIL

Final Report Delivery with requirements:

The query for

Enrl Stdnt NOCamp Email not PI CU_BAR_SR_00071_1 has been shared with Patrick Ackerman and the query is working fine.

CU_BAR_SR_00071-1 Prompts are below:

Institution: Bar01
Term: pick any term

Query Development:

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

Records	Description of fields
STDNT_CAR_TERM	ID
SCC_EMAIL_QVW
SCC_EMAIL_QVW	Campus Email

Expressions:

Few 'Expressions' have been added to find 'campus' or 'othr' email address. The value of 'CAMP' and 'OTHR' define email address for Permit In student who does not have Baruch email address.

Prompt:

Institution, Term have been mandatory for an user to input.

Criteria:

ACAD_PROG_STATUS is 'AC' because the report is based on active students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration.

Query Output Image:

Query SQL:

SELECT A.EMPLID, C.EMAIL_ADDR
FROM PS_STDNT_CAR_TERM A, PS_SCC_EMAIL_QVW C, PS_SCC_EMAIL_QVW D
WHERE A.INSTITUTION = :1
     AND A.STRM = :2
     AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE B.EFFDT BETWEEN TO_DATE('2015-05-30','YYYY-MM-DD') AND TO_DATE('2015-08-14','YYYY-MM-DD')
     AND A.EMPLID = B.EMPLID
     AND A.INSTITUTION = B.INSTITUTION
     AND B.EFF_STATUS = 'A'
     AND substr( B.STDNT_GROUP,1,2) IN ('PI'))
     AND A.UNT_TAKEN_PRGRSS > 0
     AND A.EMPLID = C.EMPLID (+)
     AND 'CAMP' = C.E_ADDR_TYPE (+)
     AND A.EMPLID = D.EMPLID (+)
     AND 'OTHR' = D.E_ADDR_TYPE (+)
     AND ( NOT EXISTS (SELECT E.EMPLID
FROM PS_SCC_EMAIL_QVW E
WHERE E.EMPLID = A.EMPLID
     AND E.E_ADDR_TYPE = 'CAMP')
     OR EXISTS (SELECT F.EMPLID
FROM PS_SCC_EMAIL_QVW F
WHERE F.EMPLID = A.EMPLID
     AND F.E_ADDR_TYPE = 'CAMP'
     AND lower ( F.EMAIL_ADDR) NOT LIKE '%@baruchmail%'))

Page tree